xlutils only works on xls, not xlsx?

Question

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 1

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)