Is there any way to edit an existing Excel file using Python preserving formulae?

Question

I am trying to edit several excel files (.xls) without changing the rest of the sheet. The only thing close so far that I've found is the xlrd, xlwt, and xlutils modules. The problem with these is it seems that xlrd evaluates formulae when reading, then puts the answer as the value of the cell. Does anybody know of a way to preserve the formulae so I can then use xlwt to write to the file without losing them? I have most of my experience in Python and CLISP, but could pick up another language pretty quick if they have better support. Thanks for any help you can give!

Yes, but I am running it on a Mac. I also have OpenOffice – user934001 Oct 07 '11 at 14:04 — user934001, Oct 07 '11 at 14:04

score 10 · Answer 1 · answered Mar 25 '14 at 15:23

10

I had the same problem... And eventually found the next module:

from openpyxl import load_workbook
def Write_Workbook():
    wb = load_workbook(path)
    ws = wb.get_sheet_by_name("Sheet_name")
    c = ws.cell(row = 2, column = 1)
    c.value = Some_value
    wb.save(path)

==> Doing this, my file got saved preserving all formulas inserted before.

Hope this helps!

answered Mar 25 '14 at 15:23

Peter Vanvoorden

181
1
2
11

This is a solution as long as the files are `.xlsx` or can be converted to that. OpenPyXL will not work with `.xls` files. – John Y Mar 27 '14 at 22:14

score 1 · Answer 2 · edited May 23 '17 at 12:15

We've just had this problem and the best we can do is to manually re-write the formulas as text, then convert them to proper formulas on output.

So open Excel and replace =SUM(C5:L5) with "=SUM(C5:L5)" including the quotes. If you have a double quote in your formula, replace it with 2 double quotes, as this will escape it, so = "a" & "b" becomes "= ""a"" & ""b"" ")

Then in your Python code, loop over every cell in the source and output sheets and do:

output_sheet.write(row, col, xlwt.ExcelFormula.Formula(source_cell[1:-1]))

We use this SO answer to make a copy of the source sheet to be the output sheet, which even preserves styles, and avoids overwriting the hand written text formulas from above.

score 1 · Answer 3 · answered Oct 05 '11 at 17:57

1

I've used the xlwt.Formula function before to be able to get hyperlinks into a cell. I imagine it will also work with other formulas.

Update: Here's a snippet I found in a project I used it in:

link = xlwt.Formula('HYPERLINK("%s";"View Details")' % url)
sheet.write(row, col, link)

answered Oct 05 '11 at 17:57

Dan Breen

12,626
4
38
49

1

You seem to have missed the question. The OP's problem is that xlrd doesn't read formulas. Therefore, he doesn't have the formulas to write out with xlwt. – John Y Oct 05 '11 at 21:12

score 1 · Answer 4 · answered Oct 05 '11 at 21:20

As of now, xlrd doesn't read formulas. It's not that it evaluates them, it simply doesn't read them.

For now, your best bet is to programmatically control a running instance of Excel, either via pywin32 or Visual Basic or VBScript (or some other Microsoft-friendly language which has a COM interface). If you can't run Excel, then you may be able to do something analogous with OpenOffice.org instead.

Is there any way to edit an existing Excel file using Python preserving formulae?

4 Answers4