Stewie Griffin Stewie Griffin - 2 months ago 14
Python Question

xlutils only works on xls, not xlsx?

I have an existing Excel workbook,

Workbook_A
. I'm creating an identical workbook,
Workbook_B
, and then insert a few values into some of the cells in the new workbook.

A simplified version of what I'm doing:

from xlrd import open_workbook
from xlutils.copy import copy

rb = open_workbook(Workbook_A)
wb = copy(rb)
s = wb.get_sheet(0)
s.write(row, col, value)
wb.save(Workbook_B)


Workbook_A
can be an
xlsx
file here, but I must save it as an
xls
file,
Workbook_B.xls
. Otherwise the file becomes corrupt and impossible to open.

Is there a way to fix this? Can I use
xlutils
with
xlsx
, or isn't the module compatible with that Excel-format?

Is
openpyxl
the solution?


I'm not the first one to encounter this problem, but I can't find a fix.

Answer Source

xlsutils relies on xlrd (to read files) and xlwt (to write files) packages.

So the save function uses xlwt.

The bad news is while xlrd has been upgraded to work with newer, completely different .xlsx files (basically zipped xml files), xlwt wasn't upgraded to write such files (does xlwt support xlsx Format)

from https://xlwt.readthedocs.io/en/latest/

xlwt is a library for writing data and formatting information to older Excel files (ie: .xls)

So passing a .xlsx extension doesn't change a thing. The underlying format is still .xls (and is seen as corrupt by MS Excel because it relies on the extension, not on contents, to decide how to open the file)

So, either use openpyxl to do what you want (drop xlutil, xlrd, xlwt entirely since you don't care about legacy .xls format), or save as a temporary .xls file using your current process, then read it back sheet by using xlrd and write back to openpyxl.

Depending on the complexity of your current code, you may choose between a full rewrite or a dirty workaround involving much more packages (but avoiding to rewrite the current code)