pd.read_excel无法读取xlsm文件，求转为DataFrame的解决方案

Hey there! Let's tackle your problems one by one:

First: Confirming Pandas Support for XLSM Files #

You're absolutely right — Pandas does support XLSM files, but there's a key detail about the underlying engine. The xlrd library (once the default engine for Pandas) dropped support for XLSX/XLSM files starting from version 2.0.0. That’s almost certainly why you hit the XLRDError: Can't find workbook in OLE2 compound document error — your Pandas setup is trying to use xlrd, which can’t handle XLSM anymore.

Solution 1: Use openpyxl as the Reading Engine #

This is the simplest and most reliable fix. openpyxl is a library built for reading/writing modern Excel formats (including XLSM), and Pandas integrates with it seamlessly.

Step 1: Install openpyxl #

If you don’t have it installed yet, run this command in your terminal:

pip install openpyxl

Step 2: Read the XLSM File with the openpyxl Engine #

Update your code to explicitly specify the engine:

import pandas as pd

df = pd.read_excel(filepath, sheet_name=target_worksheet, engine='openpyxl')

This should resolve the OLE2 error and load your XLSM data into a DataFrame without hassle.

Solution 2: Check for Corrupted XLSM Files #

Sometimes the error stems from a damaged file, not a code issue. Try these quick checks:

• Open the XLSM file manually in Microsoft Excel to see if it loads without warnings or repair prompts.
• If Excel asks to repair the file, follow the steps, save a fresh copy of the XLSM, then try reading the new version with Pandas.

Solution 3: Convert Win32com Extracted Data to DataFrame #

If you need to use win32com (e.g., to handle macros or complex Excel features), here’s how to turn the raw cell data into a proper DataFrame:

import win32com.client as win32
import pandas as pd

# Initialize Excel (keep it hidden to avoid pop-ups)
excel_app = win32.gencache.EnsureDispatch('Excel.Application')
excel_app.Visible = False

# Open the workbook and target worksheet
workbook = excel_app.Workbooks.Open(filepath)
worksheet = workbook.Worksheets(target_worksheet)

# Grab all used data from the sheet
used_range = worksheet.UsedRange
raw_data = used_range.Value

# Convert to DataFrame: first row becomes column names, rest are data rows
df = pd.DataFrame(raw_data[1:], columns=raw_data[0])

# Clean up to avoid lingering Excel processes
workbook.Close(SaveChanges=False)
excel_app.Quit()

Pro Tip: Always make sure to quit the Excel application explicitly — otherwise, background Excel processes might stay running on your system.

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