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
49
votes
2 answers

How to freeze entire header row in openpyxl?

How to freeze entire header row in openpyxl? So far I can only freeze the column: # only freeze the column (freeze vertically) cell = ws.cell('{}{}'.format(col, row_idx+1)) worksheet.freeze_panes = cell
Yudhistira Arya
  • 3,491
  • 6
  • 25
  • 42
48
votes
15 answers

xlsx and xlsm files return badzipfile: file is not a zip file

I'm trying to open both an xlsx file and an xlsm file both give me the same error badzipfile: file is not a zip file here is what I'm typing: import openpyxl wb=openpyxl.load_workbook('c:\\users\\me\\documents\\filename.xlsm',…
zw1ck
  • 493
  • 1
  • 5
  • 10
47
votes
7 answers

How to deal with warning : "Workbook contains no default style, apply openpyxl's default "

I have the -current- latest version of pandas, openpyxl, xlrd. openpyxl : 3.0.6. pandas : 1.2.2. xlrd : 2.0.1. I have a generated excel xlsx- file (export from a webapplication). I read it in pandas: myexcelfile = pd.read_excel(easy_payfile,…
Philippe
  • 707
  • 1
  • 5
  • 9
46
votes
6 answers

How to find the last row in a column using openpyxl normal workbook?

I'm using openpyxl to put data validation to all rows that have "Default" in them. But to do that, I need to know how many rows there are. I know there is a way to do that if I were using Iterable workbook mode, but I also add a new sheet to the…
human
  • 735
  • 2
  • 8
  • 17
41
votes
5 answers

Saving openpyxl file via text and filestream

I'm building OpenPyXL into an application that expects a string containing the content of the excel file, for it to write via file stream. From my investigation into the OpenPyXL source code, it doesn't look like it supports this kind of output.…
Nelson Shaw
  • 1,103
  • 2
  • 10
  • 10
41
votes
6 answers

Applying borders to a cell in OpenPyxl

I am trying to use Openpyxl to apply a border to a cell, but I have failed on the most basic "apply any kind of border to any cell anywhere" task. I tried copying from the Openpyxl documentation…
user2961794
  • 411
  • 1
  • 4
  • 3
41
votes
4 answers

copy cell style openpyxl

I am trying to copy a sheet, default_sheet, into a new sheet new_sheet in the same workbook. I did managed to create a new sheet and to copy the values from default sheet. How can I also copy the style of each cell into the new_sheet…
FotisK
  • 1,055
  • 2
  • 13
  • 28
38
votes
4 answers

Writing multi-line strings into cells using openpyxl

I'm trying to write data into a cell, which has multiple line breaks (I believe \n), the resulting .xlsx has line breaks removed. Is there a way to keep these line breaks?
user1514631
  • 1,183
  • 1
  • 9
  • 14
37
votes
4 answers

Openpyxl setting number format

Could please someone show an example of applying the number format to the cell. For example, I need scientific format, form would be like '2.45E+05' but I couldn't figure a way how to do that in openpyxl. I tried in several ways but they are all…
Renesis
  • 881
  • 2
  • 9
  • 16
36
votes
1 answer

Differences between xlwings vs openpyxl Reading Excel Workbooks

I've mostly only used xlwings to open (read-write) workbooks (since the workbooks I read have complicated macros). But I've recently begun using openpyxl to open (read-only) workbooks when I've needed to read thousands of workbooks to scrape some…
Jon
  • 2,373
  • 1
  • 26
  • 34
34
votes
1 answer

How to rename an Excel sheet?

I have a scenario where I wanted to change the name of a sheet in the spread-sheet. I tried creating a spread-sheet using ss = Workbook(). I think this is creating the spread-sheet with a sheet named "Sheet". I tried changing the name of the sheet…
Vimo
  • 1,061
  • 3
  • 12
  • 22
34
votes
6 answers

Horizontal text alignment in openpyxl

I'm trying to change the text alignment to the center of 2 merged cells. I've found some answers that didn't work for my case: currentCell = ws.cell('A1') currentCell.style.alignment.horizontal = 'center' #TypeError: cannot set horizontal…
Pythonizer
  • 1,080
  • 4
  • 15
  • 25
33
votes
4 answers

Insert image in openpyxl

Is it possible to insert an image (jpeg, png, etc) using openpyxl? Basically I want to place a generated image with a chart below it. I don't see anything in the documentation, which seems to be a little lacking compared to the maturity of the code.
tomc
  • 555
  • 2
  • 6
  • 11
32
votes
5 answers

Copy pandas dataframe to excel using openpyxl

I have some complicated formating saved in a template file into which I need to save data from a pandas dataframe. Problem is when I use pd.to_excel to save to this worksheet, pandas overwrites the formatting. Is there a way to somehow 'paste…
blitz009
  • 321
  • 1
  • 3
  • 3
31
votes
4 answers

Openpyxl 1.8.5: Reading the result of a formula typed in a cell using openpyxl

I am printing some formula in one of the Excel sheets: wsOld.cell(row = 1, column = 1).value = "=B3=B4" But I cannot use its result in implementing some other logic, as: if((wsOld.cell(row=1, column=1).value)='true'): # copy the 1st row to…
ravikant
  • 415
  • 1
  • 5
  • 13
1
2
3
…
99 100