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

python xlwt xlutils
Brian Kim picture Brian Kim · Aug 17, 2017 · Viewed 11.6k times · Source

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

Munosphere picture Munosphere · Aug 17, 2017

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')

Related questions

python xlutils : formatting_info=True not yet implemented

I've got simple code to copy files with xlutils, xlrd, xlwt (downloaded new libraries from python-excel.org) with not loosing formatting. I've got an error as below: from xlwt.Workbook import * from xlwt.Style import * from xlrd import open_workbook …

Read More
Accessing worksheets using xlwt 'get_sheet' method

I would like to access worksheets of a spreadsheet. I've copied the main workbook to another workbook using xlutils.copy(). But don't know the right way to access worksheets using xlwt module. My sample code: import xlrd import xlwt from …

Read More
Python Excel template read and re-write, maintaining formulae and formatting

I've run the gamut and can't seem to find what I'm looking for. All threads I found here end up in dead ends for me. xlrd, xlwt, and xlutils almost do what I need, but… The basic idea is that …

Read More