I'm working on a application that can read out a large .xlsx (more than 20000 rows) with Openpyxl and can make some basic calculations with this data. I try to read out 20000 rows in the example below. When I run this code in the command promt of Windows the output is fine, but when I run it in a linux terminal or in the terminal of PyCharm the output after 10000 changes to None.

I already tried to change the load_workbook read_only parameter to True, but that completely destroyed the performance of the application

from openpyxl import *

wb = load_workbook(filename="wind_data.xlsx", read_only=False)
ws = wb.active

output_data = [ws.cell(row=i, column=2).value for i in range(2, 20002)]

for i in range(0, len(output_data)):
    print(str(i+1) + " : " + str(output_data[i]))

The output I expect from this code would end with:

19998 : 6
19999 : 6
20000 : 6

But instead I get:

19998 : None
19999 : None
20000 : None
  • Have you double-checked that the file really is identical on both computers? openpyxl does not use platform-specific code so behaviour will be the same. Also, using ws.cell() is your problem in read-only mode. Always use ws.iter_rows() for this kind of task. – Charlie Clark Jun 7 at 13:01

I suggest using generator comprehension for really big collections.

output_data = (ws.cell(row=i, column=2).value for i in range(2, 20002))

This might help in your case. I test it on my linux and it's works fine on excel with over 20k rows. Secondly when you want to iterate thru such collection and also have indexes use function:



for i, val in enumerate(output_data):
    print('{} : {}'.format(i+1, val)
  • I tried this method but it didn't make any difference, both in PyCharm and on the linux machine. But thanks for the tips. – EKSE Jun 7 at 11:36
  • I ran it on both a virtual machine with Ubuntu and a laptop with Manjaro and both returned the same output. While the windows terminal ran it without issues. – EKSE Jun 7 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.