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?
I'm not the first one to encounter this problem, but I can't find a fix.
As xlutils
relies on xlrd
(to read files) and xlwt
(to write files), thus, the save
function of xlutils
actually using xlwt
.
.xlsx
is a newer and completely different file format (basically zipped xml files) from .xls
. While xlrd
has been upgraded to read .xlsx
files , xlwt
wasn't upgraded to write such files (does xlwt support xlsx Format).
Since xlwt
only writes older Excel (.xls
) files, 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 xlutils
, 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)