BUG: engine calamine lost 0 when read_excel from vlookup cell

[ryjfgjl]

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

df = pd.read_excel(r'C:\Users\ryjfgjl\Desktop\汽车计提1-2月明细(1).xlsx', sheet_name=3, na_filter=False, engine='calamine', dtype=object)
print(df)

Issue Description

Excel data

df:

Expected Behavior

change engine to openpyxl is correct

Installed Versions

2.2.3

[asishm]

Thanks for the report. Are you able to share the excel file (preferably without the vlookups) or code to generate the excel file?

[ryjfgjl]

import pandas as pd

file = r'C:\Users\ryjfgjl\Desktop\test.xlsx'
df_without_vlookup = pd.read_excel(file, engine='calamine', sheet_name='Sheet1', dtype=object)
print(df_without_vlookup)
df_with_vlookup = pd.read_excel(file, engine='calamine', sheet_name='Sheet2', dtype=object)
print(df_with_vlookup)

test.xlsx

[dimastbk]

Hi. It has been fixed in upstream tafia/calamine#472, but it hasn't been released yet.

UPD: Should be fixed in https://github.com/dimastbk/python-calamine/releases/tag/v0.3.2.

[chilin0525]

@dimastbk You're right — the bug was resolved in version 0.3.2.

• 0.3.1:

    id      code
  0  1  05291912
  1  2  05291913
  2  3  05291914
  3  4  05291915
    id     code
  0  1  5291912
  1  2  5291913
  2  3  5291914

• 0.3.2

    id      code
  0  1  05291912
  1  2  05291913
  2  3  05291914
  3  4  05291915
    id      code
  0  1  05291912
  1  2  05291913
  2  3  05291914

[chilin0525]

take