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

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!

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!

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)

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.

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.