pandas.read_excel读取特殊格式批注单元格识别为NaN的解决请求

I get it, this is super frustrating—Excel auto-adding that equals sign turns your valid comments into broken formulas, and pandas just throws them out as NaN. Let's fix this without manual edits or converting to CSV.

Why Your Previous Attempts Didn't Work #

• data_only=True: Reads the displayed value of the cell, which is #NAME? (pandas converts this to NaN).
• data_only=False: Reads the formula (=-comment), but pandas recognizes it as an invalid formula and still turns it into NaN.
• dtype=str: Doesn't help because pandas already converts the error value to NaN before applying the string dtype.

The Solution: Use openpyxl to Preprocess the Formula Text #

We can use openpyxl (which pandas uses under the hood for .xlsx files) to directly access the formula in each cell, fix it by stripping the leading =, then load the cleaned data into pandas. Here's how:

Step 1: Install openpyxl (if you haven't already)

pip install openpyxl

Step 2: Code to Fix and Load the Data

import openpyxl
import pandas as pd
from io import BytesIO

# Load the workbook with data_only=False to access the formula text
wb = openpyxl.load_workbook("your_large_file.xlsx", data_only=False)
ws = wb["YourSheetName"]  # Replace with your actual sheet name

# Define which column has the user comments (1-based index; e.g., column B is 2)
comment_column_index = 2  # Adjust this to match your file

# Process each row starting from the second row (skip header if you have one)
for row in ws.iter_rows(min_row=2, min_col=comment_column_index, max_col=comment_column_index):
    cell = row[0]
    # Check if it's a formula starting with "=-" (the problematic case)
    if cell.data_type == "f" and cell.value.startswith("=-"):
        # Remove the leading "=" to get back the original comment text
        cell.value = cell.value[1:]

# Save the modified workbook to an in-memory buffer (no disk write needed for large files)
buffer = BytesIO()
wb.save(buffer)
buffer.seek(0)

# Load the cleaned data into pandas
df = pd.read_excel(buffer, engine="openpyxl", dtype=str)

# Verify the comments are now loaded correctly
print(df[df.columns[comment_column_index - 1]].head())  # 0-based index for pandas

How This Works #

1. Loading the Workbook: data_only=False lets us access the actual formula text (=-comment) instead of the displayed #NAME? error.
2. Processing Cells: We loop through the comment column, check for cells that are formulas starting with =-, and replace the cell value with everything after the = (so - comment).
3. In-Memory Buffer: Saving to a BytesIO buffer avoids writing a temporary file to disk, which is crucial for large files to save time and space.
4. Loading into Pandas: Now that the formulas are fixed to plain text, pandas reads them correctly as strings.

Optional: Handle Multiple Columns #

If you have multiple columns with this issue, just adjust the code to loop through all relevant columns:

# List of column indices (1-based) that have the problematic comments
comment_columns = [2, 5, 7]

for col_idx in comment_columns:
    for row in ws.iter_rows(min_row=2, min_col=col_idx, max_col=col_idx):
        cell = row[0]
        if cell.data_type == "f" and cell.value.startswith("=-"):
            cell.value = cell.value[1:]

This should get all those user comments loaded into your DataFrame as text, no manual cleanup required.

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