Stack Overflow
Stack Overflow

Questions tagged [openpyxl]

Openpyxl is a Python library for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm files.

Openpyxl is a Python library to read/write Excel 2010 xlsx, xlsm, xltx, and xltm files.

It was born from lack of an existing library to read/write natively from Python the Office Open XML format.

All kudos to the PHPExcel team as openpyxl was initially based on PHPExcel.

Features:

  1. Tables
  2. Data validation
  3. Charts

About security:

By default Openpyxl does not guard against quadratic blowup or billion laughs XML attacks. To guard against these attacks, install defusedxml.

Project documentation can be found at openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files.

5440 questions
1
vote
2 answers

Remove existing data validation with openpyxl

I'm using openpyxl ws.add_data_validation to add some validation to a cell in Excel. However, I'm finding that if the cell has some previous validation in place then it doesn't seem to take the new validation when I subsequently open it in the…
Miff Jacobs
  • 11
  • 4
1
vote
2 answers

How to import data from .txt file to a specifc excel sheet with Python?

I am trying to automate a process that basically reads in values from text files into certain excel cells. I have a template in excel that will read data from various sheets under certain names. For example, the template will read in data from…
Alan Piggott
  • 13
  • 1
  • 6
1
vote
1 answer

Search and find values in two excel sheets(xlrd to openpyxl)

wrbk = xlrd.open_workbook("D:Book1.xlsx") idx = 0 book_1 = xlrd.open_workbook("D:Book2.xlsx") sh_1 = book_1.sheet_by_name('Sheet4') i = 0 for x in range(sh_1.nrows): i = i + 1 if i >= sh_1.nrows: break if sh_1.cell(i,…
kar_n
  • 78
  • 7
1
vote
0 answers

Saving xlsx files that arent corrupted via openpyxl

I am generating around 10000 xlsx files to run a Monte Carlo simulation using a program called AMPL. To generate these files I am using the below python script using openpyxl. The xlsx file that results needs to still be opened and "save as" and…
RG S
  • 33
  • 3
1
vote
1 answer

Python/Pandas: Iterate over Excel files and extract information

I founds threads on extracting info from various sheets of the same file and solutions to problems similar, but not exactly like mine. I have a several Excel workbooks each containing several sheets. I would like to iterate over each workbook and…
StatsScared
  • 517
  • 6
  • 20
1
vote
0 answers

Copy specific cell range from one wb to another based on specific criteria - Python

I have to select a fixed ranged ("H4":"AI4") from a list of sheets in a workbook and I have to paste that range in another workbook that has identical sheet names as the first wb. Now here is where it gets tricky (for me - I'm new to this): I have…
Vlad Nemes
  • 11
  • 2
1
vote
1 answer

Matching values in 2 excel sheets of a workbook using pandas and openpyxl

There are 2 sheets in my xlsx file. The file is stored locally. when I am printing the rows and columns,I am getting the output. what i want is if any value in first column (patient id) of "patient info" sheet matches with any value in first column…
pri
  • 23
  • 3
1
vote
0 answers

Copy entire row if column "B" contains %value (openpyxl)

I am trying to run through an Excel sheet and copy all rows where column "B" starts with value "A-P1" or "A-S1" and copy the row to two separate Excel files (one file for P1 and one for S1 values). Currently I have sort of achieved this with the…
suprimos
  • 33
  • 6
1
vote
1 answer

How to continuously update the empty rows within specific columns using pandas and openpyxl

Currently I'm running a live test that uses 3 variables data1, data2 and data 3. The Problem is that whenever I run my python code that it only writes to the first row within the respective columns and overwrites any previous data I had. import…
kfg456117
  • 21
  • 1
1
vote
1 answer

Formatting Excel files in openpyxl

In my project I create .xlsx file and fill it with data using ws.append([list]). Like that: for line in inf: current_line = line.strip().split(';') ws.append(current_line) Header row is also added using .append() method. The next thing I…
Outlaw
  • 307
  • 1
  • 3
  • 12
1
vote
1 answer

Excel (LibreOffice Calc) show blank cells but openpyxl shows values

I have a workbook sheet that shows the first 3 rows with data when opened with LibreOffice Calc. If I use conditional formatting to set cell background color to red if a cell is blank, all cells in rows 4 and following show red. When I read the…
uq3g1rbm
  • 41
  • 4
1
vote
1 answer

Robot Framework "Write Data By Coordinates"-keyword problem when used with "run keyword if"

I am trying to do a demo with Robot Framework which writes either Yes or No as a text to rows in Excel column number 31 (Excel-file containing a lot of music data on different columns) depending on whether a button is visible or not on a website…
jm90
  • 137
  • 11
1
vote
0 answers

What is difference between "anaconda prompt" and "IPython console"?

I installed "Spyder" to try to parse an excel file using openpyxl. First, I wrote a simple python code to read an excel file and print shell contents. from openpyxl import load_workbook testExcel=load_workbook('test.xlsx') sheet1 =…
BangYong Song
  • 35
  • 5
1
vote
0 answers

What are the uses of axis properties of openpyxl

I would like to know the use of below properties when drawing line charts with openpyxl, as I am unable to find explanation on the purpose of them. crossAx=100 c2.y_axis.axId = 200 c1.y_axis.crossAx = 500
udani
  • 1,243
  • 2
  • 11
  • 33
1
vote
1 answer

Using openpyxl, Is there a way to check if a cell of an excel file has conditional formatting?

I can find the list of conditional formatting available in the worksheet. However, how do I determine if a cell has any conditional formatting at all ? from openpyxl import load_workbook ws= load_workbook('Path to excel file') WS=ws.active cf_rules…
Doomski
  • 117
  • 1
  • 11
1 2 3
…
99 100