How to save in openpyxl without losing formulae?

Question

Because I need to parse and then use the actual data in cells, I open an xlsm in openpyxl with data_only = True.

This has proved very useful. Now though, having the same need for an xlsm that contains formuale in cells, when I then save my changes, the formulae are missing from the saved version.

Are data_only = True and formulae mutually exclusive? If not, how can I access the actual value in cells without losing the formulae when I save?

When I say I lose the formulae, it seems that the results of the formulae (sums, concatenattions etc.) get preserved. But the actual formulaes themselves are no longer displayed when a cell is clicked.

UPDATE:

To confirm whether or not the formulaes were being preserved or not, I've re-opened the saved xlsm, this time with data_only left as False. I've checked the value of a cell that had been constructed using a formula. Had formulae been preserved, opening the xlsm with data_only set to False should have return the formula. But it returns the actual text value (which is not what I want).

Tandie

Python: 2.7.10. Openpyxl: latest. – Tandie

Clicking on Show Formulas in Excel has no effect for such a saved spreadsheet. – Tandie

Answer 1

If you want to preserve the integrity of the workbook, ie. retain the formulae, the you cannot use data_only=True. The documentation makes this very clear.

Subglacial

The thing is: the integrity does seem to get maintained i.e. the cells contain the proper results of their original formulae. It's just that the formalue themselves don't get displayed when a cell is clicked on. – Tandie

You have to choose: either the formulae or the values that result from their calculation. By integrity I mean that there is no guarantee that the values are still true because openpyxl itself never evaluates formulae. – Subglacial

Fair enough, and thanks for the additional clarification. Keep up the good work with this awesome library. – Tandie

Answer 2

Part of your question was: Are data_only = True and formulae mutually exclusive?

The answer to that, in openpyxl, is yes.

But this is not intrinsic to Excel. You could have a library like openpyxl which gives you access to both the formulas and their results. This is unlikely to happen, since the maintainer(s) of openpyxl are philosophically opposed to this idea.

So, how you're expected to handle your kind of situation in openpyxl is to load the workbook twice: once with data_only=True just to read the data (which you keep in memory), then load it again as a "different" workbook with data_only=False to get a writable version.

The "canonical" way of modifying an existing workbook with Python while preserving everything else (including formatting, formulas, charts, macros, etc.) is to use a COM interface (such as PyWin32, or higher-level wrappers like pywinauto or xlwings) to control a running instance of Excel. Of course, this is only possible if you are running on a machine with Excel installed.

Kanara