4

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.

Brian Kim
  • 65
  • 1
  • 2
  • 8
  • `xlutils` only works with the `xls` format... (it uses the `xlrd`/`xlwt` libraries...) – Jon Clements Aug 17 '17 at 17:42
  • 1
    `xlutils.copy` uses `xlrd` for reading and `xlwt` for writing. `xlrd` can read both `.xls` and `.xlsx` formats, but `xlwt` only writes to `.xls` format which is limited to 65536 (2**16) rows. – Steven Rumbalski Aug 17 '17 at 17:43

2 Answers2

5

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')
Munosphere
  • 174
  • 1
  • 12
  • I tried writing in openpyxl, but continued to get the MemoryError when saving. I tried read/write only formatting but wasn't sure how to apply the write only tag to existing excel files. Can the write_only = True tag also be applied to existing excel files? Basically, I was unclear on how to solve the MemoryError so rewrote my code in xlwt. – Brian Kim Aug 17 '17 at 17:48
  • Read/write only formatting does not allow you to edit existing files. You would have to load the workbook with openpxl and save it after making your changes. – Munosphere Aug 17 '17 at 18:00
  • I see. Then, there would be no pay to edit on existing excel files in my situation using openpyxl or xlwt? I have a MemoryError when trying to save my Excel file with openpyxl; probably due to the large file size. – Brian Kim Aug 17 '17 at 18:01
  • I've made some edits to my answer. You can give it a try. – Munosphere Aug 17 '17 at 18:09
  • @BrianKim: [Read up](https://openpyxl.readthedocs.io/en/default/optimized.html#read-only-mode) on `openpyexcel`'s `read_only` and `write_only` modes. They are designed to save on memory consumption but come with particular constraints (such as in a write-only workbook rows can only be added with `append()`). – Steven Rumbalski Aug 17 '17 at 18:12
  • Thanks for all the help. After loading my workbook and applying the write_only = True tag, wouldn't that reset the workbook and create a workbook without any sheets? I'm unable to grab the sheet from my original workbook with sheet = wb.active and get a NoneType error when trying to use the sheet to perform any action. – Brian Kim Aug 17 '17 at 18:23
3

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.

Skippy le Grand Gourou
  • 6,976
  • 4
  • 60
  • 76