Broken Excel output: Openpyxl formula settings?

Question

I am creating some Excel spreadsheets from pandas DataFrames using the pandas.ExcelWriter().

Issue:

• For some string input, this creates broken .xlsx files that need to be repaired. (problem with some content --- removed formula, cf error msg below)

• I assume this happens because Excel interprets the cell content not as a string, but a formula which it cannot parse, e.g. when a string value starts with "="

Question:

• When using xlsxwriter as engine, I can solve this issue by setting the argument options = {"strings_to_formulas" : False }

• Is there a similar argument for openpyxl?

Troubleshooting:

• I found the data_only argument to Workbook, but it only seems to apply to reading files / I cannot get it to work with ExcelWriter().

• Not all output values are strings / I'd like to avoid converting all output to str

• Could not find an applicable question on here

Any hints are much appreciated, thanks!

---

Error messages:

We found a problem with some content in 'file.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes

The log after opening says:

[...] summary="Following is a list of removed records:">Removed Records: Formula from /xl/worksheets/sheet1.xml part [...]

Code

import pandas
excelout = pandas.ExcelWriter(output_file, engine = "openpyxl")
df.to_excel(excelout)
excelout.save()

Versions:

pandas @0.24.2 openpyxl @2.5.6

Excel 2016 for Mac (but replicates on Win)

Answer 1

I spent FAR too long trying to figure out this error.

Turned out I had an extra bracket, so the formula wasn't valid.

I know 99% of people will read this and say "thats not the issue" and move on, but take your formula and paste it into excel if you can (replacing dynamic values as best you can) and see if excel accepts it.

If it accepts it fine, move on and find whatever the other cause it, but if you find it doesn't like the formula, maybe I just saved you a couple of hours....

My command: f'''=IF(ISBLANK(E{row}),FALSE," "))''' Tiny command, could not understand what was wrong with it. :facepalm:

Answer 2

I've struggled of this issue too. I have found a strange solution for formulas. I had to replace all ; (semicolon) signs with , (comma) in the formulas. When I opened the result xlsx file with Excel, this error didn't rise and the formula in Excel had usual ;.