读取Excel单元格时整数转浮点数问题求助：输出需与输入一致

Hey there! I’ve dealt with this exact frustration before—you pull numbers from Excel, and suddenly nice whole integers like 5 turn into 5.0, or big integers like 4699123912 show up as 4.699123912E9. Let’s get your output matching the original input perfectly.

The Root Cause #

Excel stores numbers as floats by default (even whole integers), so most Python libraries (like pandas or openpyxl) will read them as float types. Big integers might trigger scientific notation when printed as floats, but they’re still whole numbers under the hood.

Solution 1: Using openpyxl #

If you’re using openpyxl to read Excel files, you can check each cell’s value and convert integer-like floats back to integers:

from openpyxl import load_workbook

def clean_excel_value(cell_value):
    # Convert float integers to int, leave other values as-is
    if isinstance(cell_value, float):
        if cell_value.is_integer():
            return int(cell_value)
    return cell_value

# Load your workbook
wb = load_workbook("your_file.xlsx")
ws = wb.active

# Process each row
for row in ws.iter_rows(values_only=True):
    cleaned_row = [clean_excel_value(val) for val in row]
    print(cleaned_row)

This will turn 5.0 back to 5 and 4.699123912E9 into 4699123912—exactly what you entered in Excel.

Solution 2: Using pandas #

For pandas users, you have a couple of options to preserve integer formatting:

Option A: Use converters during reading

Define a conversion function to clean values as pandas reads them:

import pandas as pd

def convert_float_to_int(value):
    if isinstance(value, float) and value.is_integer():
        return int(value)
    return value

# Apply the converter to all columns
df = pd.read_excel(
    "your_file.xlsx",
    converters={col: convert_float_to_int for col in pd.read_excel("your_file.xlsx").columns}
)

print(df)

Option B: Post-read conversion (for full integer columns)

If you know an entire column should be integers (no missing values), you can convert it after reading:

df = pd.read_excel("your_file.xlsx")

for column in df.columns:
    # Check if the column is float type and all values are integers
    if df[column].dtype == "float64" and df[column].dropna().apply(lambda x: x.is_integer()).all():
        df[column] = df[column].astype(int)

print(df)

Key Notes #

• If your Excel cells are formatted as text instead of numbers, these libraries will read them as strings directly—no conversion needed.
• For integers longer than 15 digits, Excel loses precision (since it uses 64-bit floats), so those can’t be fully recovered. But your example 4699123912 is only 10 digits, so this won’t be an issue.

内容的提问来源于stack exchange，提问作者Senthil