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
0 answers

Using openpyxl to average the value of cells from mutiple workbooks

I am new to python and openpyxl. I have the beginning of a script that will loop through a directory and subdirectories and find any xlsx file that starts with "BEAR". Each of these files are in the same exact format. What I am trying to do is find…
Lord Beerus
  • 69
  • 1
  • 1
  • 5
1
vote
0 answers

Python and openpyxl is running slowly

I am trying to process a 10,000+ line spreadsheet, and even when I narrow it to ~3,000 lines, Python is running slowly (about 1 line per second). I'm comparing each entry in the third column; if I find a match, I check the value in the fifth column.…
Blacksheep391
  • 31
  • 1
  • 5
1
vote
1 answer

openpyxl Check if row contains two cells of seperate values

I can iterate through cells in rows to check for cells containing a certain value. Now I'm trying to check if the row also contains second value. target = input("Input cell value: ") target2 = input("Input second cell value ") wb =…
GTA.sprx
  • 817
  • 1
  • 8
  • 24
1
vote
1 answer

What does this python openpyxl "ValueError" mean?

I cannot identify why I am getting this error. Does anybody know what is the reason for such error? Traceback (most recent call last): File "c:/Users/g401428/Documents/Visual Studio Code/jackpotCalc/calculationFGTriggerProb.py", line 49, in…
g.bg.18
  • 43
  • 1
  • 7
1
vote
1 answer

i want to make my excel file read and write fast with openpyxl

my code is to slow I want to make is more faster for example: the code is supposed to take the value of in cell a1 and change its value and rewrite again in the same cell can you help me with it ? import openpyxl row = 1 counter = 0 while row <=…
Hosam Gamal
  • 163
  • 1
  • 2
  • 12
1
vote
1 answer

How to average across excel rows for multiple rows using Openpyxl in Python?

I am currently trying to average 3 excel columns(Col C to E) into and new 4th column (Col F). When I look up how to do this with openpyxl the code looks like this: from openpyxl import load_workbook wb = load_workbook('PythontestAvg.xlsx') sheet1 =…
Houman Qadir
  • 11
  • 1
1
vote
1 answer

Export data to empty cell row Openpyxl

I am trying to automate number groupings of several lists by exporting the data to ms excel using openpyxl. The output is a list of lists with two sets of numbers per element, the first set being the matched number (0 to 99), and the second is the…
Marcky
  • 13
  • 5
1
vote
2 answers

How to iterate through an excel sheet based on whether the cell value contains a certain character

I am trying to write a script that will delete rows based on whether or not the row's corresponding cell value in the first column contains a specific character, in this case a '#'. I have tried writing the following function that would hopefully…
ZzZylo
  • 31
  • 5
1
vote
1 answer

How to return column letters instead of column numbers with openpyxl?

I'm working on Chapter 12 of Automate the Boring Stuff with Python and it is about working with spreadsheets using openpyxl. I have an object called 'c' that is 'B1' of the spreadsheet. Whenever I use 'c.column' it returns the number '2' instead of…
n_b
  • 173
  • 2
  • 4
  • 12
1
vote
0 answers

openpyxl - default a template column to be text

I'm using openpyxl to build a data-entry template to distribute to users. Some of these columns will have the possible values of ("true", "false", "unknown"), so I would like Excel to treat these columns as text instead of Boolean, even if the…
sea_nw
  • 23
  • 5
1
vote
1 answer

How do I create a table using Openpyxl's table module?

I'm attempting to create a script to process several Excel sheets at once and one of the steps i'm trying to get Python to handle is to create a table using data passed from a pandas data frame. Creating a table seems pretty straightforward looking…
joelime
  • 35
  • 1
  • 7
1
vote
0 answers

Python/openpyxl script to detect cell value and print output from cells next to it

I am having issues creating a script using openpyxl - in short what this script is meant to do is read an excel sheets cell value and print CLI commands for Juniper CLI. My problem is i want to automate the process of the script finding specific…
zkwert
  • 11
  • 2
1
vote
1 answer

How to write to an excel with multiple worksheets using "xlsxwriter" and not "openpyxl"?

I am looking to store some texts into an excel with multiple work-sheets. I tried to do it using openpyxl and I am able to achieve it but I am not able to do the same using xlsxwriter. I cannot use openpyxl due to an IllegalCharacterException…
Ronnie
  • 483
  • 1
  • 5
  • 18
1
vote
5 answers

how do I change date format on a list of dates (and retain that date format when saving to excel via openpyxl)?

This code: dateCol = [] for row in ws3.iter_rows(min_col=1, max_col=1, min_row=7, max_row = None): for cell in row: dateCol.append((ws3.cell(row=cell.row, column=1).value)) dateCol Produces this list: [datetime.datetime(2019,…
Kierk
  • 476
  • 6
  • 23
1
vote
0 answers

Openpyxl Reading Non-empty Cells as None

I have an .xlsx file which I edit in another Python script which inputs formulas into blocks of cells. When I view the sheet with Excel, I have no problem seeing the values which result from the formulas. When I try to access it again,…
Chas F
  • 11
  • 2
1 2 3
…
99
100