Be wary of setting Cell values text starting with "=".

Issue #442 resolved
John Sivak
created an issue

I don't believe this is a bug, but more of data sanitation issue. I was bit by this over the past 2 months and after finally debugging it I wanted to share the learning:

I had a string (from user-provided data) that was "==--" (two equal signs and 2 minus signs)

Using

worksheet.cell(row=row_count, column=col_count).value = "==--"

works just fine. The resulting .xslx file opens without issue/error in LibreOffice Calc.

However, when opening with Excel the user will get the following dialog message:

Excel found unreadable content in 'filename.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

This is due to openpyxl auto-guessing the data type for the cell value based on the leading "=" in "==--" (the cell type is set to "f" for forumla, when I was expecting/hoping for it to be "s" for string). "==--" however is not a valid Excel formula and causes Excel to display the error message (and scare my end-users).

The lesson learned is to use cell.set_explicit_value("==--", data_type="s") when storing text that can possibly start with "=".

I don't believe this is a bug in openpyxl, but didn't find any other references to this "phenomena" on the web, so I'll post the info here for other to find.

Comments (4)

  1. CharlieC

    The behaviour in openpyxl mirrors that in Excel where prefixing a value with = will cast it to a formula. There are bound to be edge cases where this isn't desired. An alternative to explicitly setting the type is to prefix the value with a single quote.

    Thanks for the describing the situation but the mailing list is probably a better place to do this than a bug report.

  2. Log in to comment