使用openpyxl加载含大量绘图的Excel文件时触发TypeError异常的解决方案求助

First off, let's tackle your problem step by step—those DrawingML errors from openpyxl are definitely a pain when dealing with complex Excel reports. Here are a few actionable solutions, ordered by simplicity:

1. Try Openpyxl's Read-Only Mode (Quickest Fix) #

Openpyxl's read_only=True mode skips loading graphical elements like charts, images, and text boxes entirely. Since you only need to read cell content for your table of contents, this might bypass the error completely:

import openpyxl as pyxl
# Enable read-only mode to skip drawing/chart processing
wbook = pyxl.load_workbook(path, data_only=True, read_only=True)

Note: Read-only mode doesn't allow modifying the file, but for reading cell values (which is all you need for generating a TOC), this should work perfectly.

2. Create a Cleaned Copy of the Excel File (Cross-Platform) #

If read-only mode still throws errors, you can manually strip out all drawing/chart-related components from the Excel file (since Excel files are just zip archives under the hood). This script will create a copy of your file without any graphical elements:

import zipfile
import xml.etree.ElementTree as ET
import os

def clean_excel_file(input_path, output_path):
    # Define XML namespaces used in Excel files
    ns = {
        'main': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
        'r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
    }

    with zipfile.ZipFile(input_path, 'r') as in_zip, zipfile.ZipFile(output_path, 'w', zipfile.ZIP_DEFLATED) as out_zip:
        for item in in_zip.infolist():
            # Skip the entire drawings folder and its contents
            if item.filename.startswith('xl/drawings/'):
                continue
            
            # Skip worksheet relationship files linked to drawings
            if item.filename.startswith('xl/worksheets/_rels/') and 'drawing' in item.filename:
                continue
            
            content = in_zip.read(item)
            
            # Remove drawing references from worksheet XMLs
            if item.filename.startswith('xl/worksheets/sheet') and item.filename.endswith('.xml'):
                tree = ET.fromstring(content)
                for drawing in tree.findall('.//main:drawing', ns):
                    parent = drawing.findall('..')[0]
                    parent.remove(drawing)
                content = ET.tostring(tree, encoding='utf-8')
            
            # Remove drawing entries from workbook relationships
            if item.filename == 'xl/_rels/workbook.xml.rels':
                tree = ET.fromstring(content)
                for rel in tree.findall('.//r:Relationship', ns):
                    if rel.get('Type') == 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing':
                        parent = rel.findall('..')[0]
                        parent.remove(rel)
                content = ET.tostring(tree, encoding='utf-8')
            
            out_zip.writestr(item, content)

# Usage example
input_file = 'path/to/your/report.xlsx'
cleaned_file = 'path/to/cleaned_report.xlsx'
clean_excel_file(input_file, cleaned_file)

# Now load the cleaned file normally
wbook = pyxl.load_workbook(cleaned_file, data_only=True)

This script:

• Deletes the entire xl/drawings folder (where charts/images are stored)
• Removes references to drawings from worksheet XML files
• Cleans up workbook relationship entries linked to drawings

3. Use Excel Automation (Windows-Only, Requires Excel Installed) #

If you're on Windows and have Microsoft Excel installed, you can use win32com to automate Excel to remove all charts and shapes directly. This is reliable because it uses Excel's own logic:

import win32com.client as win32
import os

def remove_excel_elements(input_path, output_path):
    excel = win32.Dispatch('Excel.Application')
    excel.Visible = False  # Run in background to avoid popping up Excel
    
    try:
        workbook = excel.Workbooks.Open(os.path.abspath(input_path))
        
        # Remove all charts and shapes from each worksheet
        for sheet in workbook.Sheets:
            # Delete chart objects
            while sheet.ChartObjects().Count > 0:
                sheet.ChartObjects(1).Delete()
            # Delete shapes (images, text boxes, etc.)
            while sheet.Shapes.Count > 0:
                sheet.Shapes(1).Delete()
        
        workbook.SaveAs(os.path.abspath(output_path))
    finally:
        workbook.Close()
        excel.Quit()

# Usage example
input_file = 'path/to/your/report.xlsx'
cleaned_file = 'path/to/cleaned_report.xlsx'
remove_excel_elements(input_file, cleaned_file)

# Load the cleaned file with openpyxl
wbook = pyxl.load_workbook(cleaned_file, data_only=True)

Recommendation #

Start with the read-only mode—it's the quickest and simplest solution. If that doesn't work, use the zip modification script (it's cross-platform and doesn't require Excel). If you're on Windows and have Excel installed, the automation approach is also a solid choice.

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