BUG: pandas.read_excel方法，在读取有公式的格子时，读取值为0

[wangxiya]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

pandas.read_excel(
            'test.xlsx'
            , sheet_name=0
            , header=None
            , usecols='A:E'
            , skiprows=1
            , names=['A', 'B', 'C', 'D', 'E']
        )

Issue Description

当excel文件中含有公式时，则对应格子的值，在dataframe中是0

Expected Behavior

openpyxl.load_workbook(file_name , data_only=True)
才能解决读取excel时，将公式计算的结果提取出来
建议将loadexcel的参数，加入read_excel方法中

Installed Versions

pandas v2.0.3

[rhshadrach]

I may be incorrect, but I believe Excel calculates these upon opening the sheet at runtime. They are not stored in the excel files themselves.

[rhshadrach]

I am seeing similar behavior with openpyxl:

df = pd.DataFrame(
    {
        'a': [1, 2],
        'b': [3, 4],
        'c': ["=SUM(A1:B1)", "=SUM(A2:B2)"]
    }
)
df.to_excel('test.xlsx', engine="openpyxl", index=False)
wb = openpyxl.load_workbook('test.xlsx', data_only=True)
ws = wb.get_sheet_by_name('Sheet1')
for e in ws.values:
    print(e)

# ('a', 'b', 'c')
# (1, 3, None)
# (2, 4, None)

[jesse-sealand]

The 'data_only' flag will read cached values in formula cells (if they exist). For instance, Miscrosoft Excel will cache the formula result, so if the file was create in Excel, the value was cached and reading it with 'data_only=True' will return the expected value. Openpyxl does not cache the value, so in this case, saving the DataFrame using the openpyxl engine does not cause the value to be cached because it openpyxl does not do that. Unfortunately, the other engine option 'XlsxWriter' does the same as openpyxl. It seems, at present, the only good way to get that value is to load it from a file that was saved by Excel.

[rhshadrach]

Thanks @jesse-sealand - confirmed. If you take my example above, open in e.g. Libre Office and save, and then run the loading bit, with data_only=True you get the cached value and data_only=False you get the formula as a string (e.g. '=SUM(A1:B1)').

So with data_only=True in some cases you get what I believe is the more expected result, but you also get behavior that depends on the nature of the Excel file. With data_only=False you get consistent behavior but never what the most expected behavior is.

Looking at other readers, I haven't been able to write a file for pyxlsb to read just yet. odf doesn't support any arguments and behaves like a mixture of data_only=True and data_only=False. It behaves like True when the spreadsheet has been saved in Libre Office and like False when it hasn't.

@dimastbk - Would you be able to report how calamine handles this?

[dimastbk]

python-calamine always extracts only values. calamine (rust lib) can extract some formulae (but can't evaluate it).

[yuenherny]

@rhshadrach Is it possible to allow users to determine if pandas needs to return the cached value, if it exists? Like an extra argument for pandas.read_excel() , e.g.:

df = pandas.read_excel(
    ...,
    data_only=True,
    engine='openpyxl',
    ...
)

I'd be more than happy to whip up a PR, if your plates are full.