simple spreadsheet with formula is not round-trippable

Issue #341 resolved
stringfellow
created an issue

See simple test with file attached.

In Excel, the formula shows fine on the write out to load_formula2.xlsx, however when reading it in again, openpyxl is not aware of it anymore (despite having written it fine originally).

In [1]: import openpyxl

In [2]: wb = openpyxl.load_workbook('load_formula.xlsx')

In [3]: wb.save('load_formula2.xlsx')

In [4]: fp1 = open('load_formula.xlsx', 'rb')

In [5]: fp2 = open('load_formula2.xlsx', 'rb')

In [6]: fp1.read() == fp2.read()
Out[6]: False

In [7]: wb.active['A1']
Out[7]: <Cell Sheet1.A1>

In [8]: wb.active['A1'].value
Out[8]: u'=IF(TRUE, "y", "n")'

In [9]: wb2 = openpyxl.load_workbook('load_formula2.xlsx')

In [10]: wb2.active['A1'].value

In [11]: wb2.active['A1'].value == None
Out[11]: True

Comments (8)

  1. CharlieC

    Thanks. It looks like checking for an empty cell short circuits formulae inference. We always store formulae without their evaluated value in case it has changed.

    BTW. comparing Excel files that were created by different programs directly with another will almost always fail.

  2. stringfellow reporter

    I realise that, but it does serve as a reasonable sense-check. The vast majority of XLSX users are Excel users (I have no numbers to back this up but common sense suggests it is so), and the specific use case I have is to present the result in Excel, so I use it as a quick check.

  3. CharlieC

    @stringfellow any file created by openpyxl will differ from the same file subsequently saved in Excel because Excel adds additional (often irrelevant and undocumented) tags and other stuff. For comparing files it's best using the tool in the SDK. That will at least ignore minor differences in the XML.

  4. AlSweigart

    I'm still seeing this bug in 2.0.5: with data_only=True return None for cell values with formulas instead of the calculated value. (I can't seem to find a way to re-open this issue. Should I file a new one?)

  5. Log in to comment