ENH: to_excel: warning/conversion of text values starting with '='

[mhooreman]

Feature Type

• Adding new functionality to pandas

• Changing existing functionality in pandas

• Removing existing functionality in pandas

Problem Description

Excel interprets text values starting with '=' as formula.
It can happen that values in a pandas Series (col of data frame) might start with '=' (for example '=== Attention ===').
If this is faced by excel:

• In case of valid formula, it is interpreted
• In case of invalid formula, the file is corrupted and the values are null after attempt to fix
  We should handle this better

Feature Description

If the value starts with '=', issue a warning and add a single quote prefix to the value:
Before: === Attention ===
After: '=== Attention ==='

Alternative Solutions

For columns with object dtypes, or categories with underlying object dtype, for values which are strings, force the excel format as text.

Additional Context

• The proposed ' prefix is not 100% correct: when we prefix any text with a ' in excel, it is interpreted as text. Unfortunatety, the proposal will show the ' (see example code below)
• It might be problematic if people wants to clearly use formula, but I don't think that's really meaningful. Maybe an option to avoid that conversion (but issue the warning anyway) can be a good option
• Example code:

import pandas as pd

df = pd.DataFrame([
    "foo",
    "bar",
    "baz",
    "=== Attention ===",  # Problem
    "'===Attention ==="   # OK but the single quote will be displayed
])

df.to_excel('demo.xlsx')

[rhshadrach]

Thanks for the report.

If the value starts with '=', issue a warning and add a single quote prefix to the value:
Before: === Attention ===
After: '=== Attention ==='

I'm negative on this approach. As you mentioned, if you supply a valid formula, it works. So this could break some workflows. Also, in a bit more generality, if you ask pandas to write some value to a cell, I do not think pandas should write a different value to that cell.

Users can add quotes to their own data prior to writing to Excel if they so desire.

For columns with object dtypes, or categories with underlying object dtype, for values which are strings, force the excel format as text.

This again seems to me like it would break desired workflows, namely when the user wants the formula.

[mhooreman]

Thanks @rhshadrach
Your arguments are perfectly true. Unfortunately, when this happens, it is very difficult to investigate in Excel. I think that we should at least issue a warning, with potentially an option which can disable that warning.

[rhshadrach]

I do not think we should issue a warning if we do not know have good confidence something unintended is happening.

[attack68]

In Excel, when typing an an '=' into a cell it will automatically read it as a formula. This is Excel's default behaviour. If you want to display that formula instead, you prefix the '=' with an apostrophe "'".
Is it not sensible to expect pandas users to parse their own file and insert apostrophes where they are required and where they are not? I cannot see anything wrong with pandas behaviour, except it could be better documented (as always).

[mroeschke]

Agreed that it's probably not worth the maintenance burden to manage a warning for this specific use case. Closing.

[mhooreman]

Ok. Thanks for the invested time and the discussion. I finally agreed with you.
Adding a space prefix in my specific case solved the issue😎