Python Forum
How can I speed up my openpyxl program reading Excel .xlsx files?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How can I speed up my openpyxl program reading Excel .xlsx files?
#1
Like many folks I need to read both .xls files (I call them S files, using xlrd) and .xlsx files (the X files, using openpyxl), in both cases files of about 30,000 rows. And in both cases I'm just copying all excel data read out to a .csv file, no other processing so just Input/Output.

But the X file operations are over 200 times slower than for .xls, for example reading a 30,000 row .xlsx file now takes 2 minutes compared to 1/2 second for .xls with xlrd. We have thousands of files to process so the time per file matters.

Is openpyxl that much slower or do I need to do something, like release some resource at the end of each row?

BTW, I have made several great improvements by using read_only=True and reading a row at a time instead of cell by cell
as shown in the following code segment. Thanks to blog.davep.org
https://blog.davep.org/2018/06/02/a_litt...npyxl.html


	wb = openpyxl.load_workbook("excel_file.xlsx",  data_only=True,  read_only=True)
	sheet = wb.active
	for row in sheet.rows:
	    for cell in row:
	        cell_from_excel = cell.value
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to append a value to specific excel cell using openpyxl hobbyist 0 560 Mar-05-2021, 07:14 PM
Last Post: hobbyist
  Python3 doesn't populate xlsx file with openpyxl Auldyin75 2 447 Feb-16-2021, 12:00 PM
Last Post: Auldyin75
  Trouble reading files using pyexcel codebeacon 2 368 Feb-08-2021, 05:53 AM
Last Post: codebeacon
  Append excel sheet using openpyxl TammyP 1 390 Feb-02-2021, 06:32 PM
Last Post: nilamo
  Extracting information from .xlsx files hobbyist 0 270 Jan-06-2021, 07:20 PM
Last Post: hobbyist
  code to read files in folders and transfer the file name, type, date created to excel Divya577 0 411 Dec-06-2020, 04:14 PM
Last Post: Divya577
  Openpyxl tkinter search a value in Excel column Heathcliff_1 0 578 Dec-02-2020, 04:35 PM
Last Post: Heathcliff_1
  Strange Problem when reading from excel faryad13 3 465 Nov-04-2020, 03:48 PM
Last Post: DeaD_EyE
  Reading from Excel: value not formula! faryad13 0 557 Oct-28-2020, 09:52 PM
Last Post: faryad13
  P3, openpyxl, csv to xlsx, cell is not number, problem with colorize genderbee 1 458 Sep-29-2020, 03:20 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020