ValueError: row index was 65536, not allowed by .xls format

Question

Currently, I am trying to edit an existing excel file using xlwt. I do not want to edit directly on the excel, so I first make a copy.

new_wb = xlutils.copy(file_location)

From this copy, I used the xlwt module to write a new column into the newly copied excel file and save. However, I get an error when I try to copy:

ValueError: row index was 65536, not allowed by .xls format

I am a little confused because the file I duplicate is a xlsx file, not xls. I never use the xls format in my code.

Any guidance would be greatly appreciated.

Answer 1

Try openpyxl instead. It support .xlsx files.

The row limit of .xls files is 65,536. xlsutils might not be supporting .xlsx files.

You can try doing this to see if it works:

from openpyxl import Workbook, load_workbook

wb = load_workbook('filename.xlsx')
wb = Workbook(write_only=True)
.
.
.
(make your edits)
.
.
.
wb.save('new_filename.xlsx')

Answer 2

Short solution for people encountering the same issue with pandas’ DataFrame.to_excel() : if you are saving into a .xls extension, simply change it for .xlsx extension.