使用Python 3.6最新版openpyxl读取复杂Excel触发TypeError

Hey there, let's break down why you're hitting this error when switching to the more complex apqptest.xlsx file—even though it has the same sheet1 as your working test.xlsx. Here are the most likely fixes to try:

1. Try OpenPyXL's Read-Only or Data-Only Modes #

Complex files often have hidden formatting, formulas, or internal structures that trip up the default load mode. Give these alternative load options a shot:

Read-Only Mode (great for large/complex files) #

This mode skips loading some non-essential metadata that might be corrupted:

from openpyxl import load_workbook
wb = load_workbook('apqptest.xlsx', read_only=True)
ws = wb['sheet1']

Data-Only Mode (skip formula parsing) #

If the error is tied to formula parsing (common in files with advanced formulas), this mode loads only the computed values instead of formula expressions:

wb = load_workbook('apqptest.xlsx', data_only=True)
ws = wb['sheet1']

2. Check for Corrupted or Non-Standard Cell Structures #

Complex Excel files sometimes have:

• Merged cells with broken references
• Named ranges using non-integer indices
• Conditional formatting or macros leaving leftover invalid data

A quick test:

• Open apqptest.xlsx in Excel, create a brand new blank workbook
• Copy only the data (not formatting/macros) from sheet1 into the new workbook's sheet1
• Save this new file and try reading it with your code. If it works, the original file has corrupted non-data elements.

3. Verify OpenPyXL & Python 3.6 Compatibility #

Python 3.6 reached end-of-life in 2021, and newer versions of OpenPyXL might have dropped full support for it. If you're using the absolute latest OpenPyXL, try downgrading to a version confirmed to work with 3.6:

pip install openpyxl==3.0.10

This version is a stable release that fully supports Python 3.6.

4. Get a Detailed Error Traceback #

If none of the above works, capture the full error stack to pinpoint exactly where the issue is occurring. Add this to your code:

import traceback
try:
    wb = load_workbook('apqptest.xlsx')
    ws = wb['sheet1']
except TypeError as e:
    traceback.print_exc()

The traceback will show which line in OpenPyXL's code is throwing the error—this can reveal if it's tied to row/column indices, formula parsing, or something else entirely.

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