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

Question

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 I need to use Python to write simple data (strings) to a particular sheet of an Excel template and write out that template to a new .xls file.

Reading in the template, copying it to a new workbook object, writing in the new values, and writing out the new .xls file is no problem. However, I cannot find a Python Excel module that maintains both formulae and formatting.

`

our = 'template.xls'

# open up read-only template with formatting maintained
rb = open_workbook(our,formatting_info=True)
r_sheet = rb.sheet_by_index(4)

# copy this to a new workbook object
wb = copy(rb)

# 5th sheet (index = 4) is where we need to drop the data we have
w_sheet = wb.get_sheet(4)

# populate with new data!
for row_index in range(0, r_sheet.nrows):
    w_sheet.write(row_index, 1, "some string that is our data")

# write it!
wb.save('new.xls')

`

When reading in the template, the formatting is maintained (slightly altered for some colors; meh!), but the formulae are not, so "new.xls" is has blanks all over the place where formulae once stood.

Any idea how to maintain both formatting and formulae?

Note, I'm locked in to Python 2.7.

Answer 1

You can use openpyxl for this - it only supports xlsx (and the related formats), and not xls (the old Excel format).

https://pypi.python.org/pypi/openpyxl/

http://openpyxl.readthedocs.org/en/latest/

https://bitbucket.org/openpyxl/openpyxl/src/2.2/doc/source/index.rst

This package does preserve formulas when you open/save the Excel sheet. Also it does preserve formatting, but there are some minor issues with it. Images from the original Excel file are not preserved, though.

Other cool features include support for conditional formatting, charts, sparklines, and many more.