如何用Python获取Excel文件中嵌入式图片的单元格位置？

问题分析 #

你提到的「单元格内的小图片」通常属于单元格内嵌锚定图片，这类图片的位置信息无法通过openpyxl的常规图片提取方法捕获，需要直接解析Excel底层XML文件，重点关注工作表的绘图关联文件和VML绘图文件。

具体步骤与代码实现 #

1. 定位图片与工作表的关联关系 #

解压.xlsx文件后，每个工作表的xl/worksheets/sheetN.xml会关联对应的绘图文件，通过xl/worksheets/_rels/sheetN.xml.rels可获取具体的drawingN.xml路径：

import zipfile
import xml.etree.ElementTree as ET

def get_drawing_rels(sheet_rel_file):
    tree = ET.parse(sheet_rel_file)
    root = tree.getroot()
    ns = {'r': 'http://schemas.openxmlformats.org/package/2006/relationships'}
    for rel in root.findall('.//r:Relationship', ns):
        if rel.get('Type') == 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing':
            return rel.get('Target')
    return None

# 示例：获取第一个工作表的drawing文件路径
with zipfile.ZipFile('your_file.xlsx', 'r') as zf:
    sheet_rel_path = 'xl/worksheets/_rels/sheet1.xml.rels'
    drawing_path = get_drawing_rels(zf.open(sheet_rel_path))

2. 解析Drawing.xml提取锚定单元格 #

drawingN.xml中，图片位置由<twoCellAnchor>（跨单元格）或<oneCellAnchor>（单单元格）标签定义，通过子标签<from>/<to>可提取单元格坐标：

def parse_drawing(drawing_file):
    ns = {
        'a': 'http://schemas.openxmlformats.org/drawingml/2006/main',
        'xdr': 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing'
    }
    tree = ET.parse(drawing_file)
    root = tree.getroot()
    image_locations = []

    # 处理单单元格锚定图片
    for anchor in root.findall('.//xdr:oneCellAnchor', ns):
        from_cell = anchor.find('.//xdr:from', ns)
        col, row = int(from_cell.find('.//xdr:col', ns).text), int(from_cell.find('.//xdr:row', ns).text)
        blip = anchor.find('.//a:blip', ns)
        rel_id = blip.get('{http://schemas.openxmlformats.org/officeDocument/2006/relationships}embed')
        image_locations.append({
            'cell': f'{chr(65 + col)}{row + 1}',  # 转换为A1格式
            'rel_id': rel_id
        })

    # 处理跨单元格锚定图片
    for anchor in root.findall('.//xdr:twoCellAnchor', ns):
        from_cell = anchor.find('.//xdr:from', ns)
        col_from, row_from = int(from_cell.find('.//xdr:col', ns).text), int(from_cell.find('.//xdr:row', ns).text)
        to_cell = anchor.find('.//xdr:to', ns)
        col_to, row_to = int(to_cell.find('.//xdr:col', ns).text), int(to_cell.find('.//xdr:row', ns).text)
        blip = anchor.find('.//a:blip', ns)
        rel_id = blip.get('{http://schemas.openxmlformats.org/officeDocument/2006/relationships}embed')
        image_locations.append({
            'cell_range': f'{chr(65 + col_from)}{row_from + 1}:{chr(65 + col_to)}{row_to + 1}',
            'rel_id': rel_id
        })
    return image_locations

# 继续示例：解析drawing文件
if drawing_path:
    with zf.open(f'xl/{drawing_path}') as f:
        locs = parse_drawing(f)
        for loc in locs:
            print(loc)

3. 处理VML格式的内嵌小图片 #

部分单元格内的小图片会用VML格式存储，对应文件为xl/worksheets/vmlDrawingN.xml，解析<x:Anchor>标签获取位置：

def parse_vml_drawing(vml_file):
    ns = {
        'v': 'urn:schemas-microsoft-com:vml',
        'x': 'urn:schemas-microsoft-com:office:excel'
    }
    tree = ET.parse(vml_file)
    root = tree.getroot()
    vml_image_locs = []
    for shape in root.findall('.//v:shape', ns):
        anchor = shape.get('{urn:schemas-microsoft-com:office:excel}Anchor')
        if anchor:
            # Anchor格式："Left,Top,Right,Bottom,Col1,Row1,Col2,Row2"
            parts = anchor.split(',')
            col1, row1, col2, row2 = int(parts[4]), int(parts[5]), int(parts[6]), int(parts[7])
            cell = f'{chr(65 + col1)}{row1 + 1}' if col1 == col2 and row1 == row2 else f'{chr(65 + col1)}{row1 + 1}:{chr(65 + col2)}{row2 + 1}'
            img_data = shape.find('.//v:imagedata', ns)
            rel_id = img_data.get('{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id')
            vml_image_locs.append({
                'cell': cell,
                'rel_id': rel_id
            })
    return vml_image_locs

# 解析VML绘图文件
vml_path = 'xl/worksheets/vmlDrawing1.xml'
if vml_path in zf.namelist():
    with zf.open(vml_path) as f:
        vml_locs = parse_vml_drawing(f)
        for loc in vml_locs:
            print(loc)

4. 关联图片文件与单元格位置 #

通过xl/drawings/_rels/drawingN.xml.rels文件，可将rel_id映射到实际的xl/media/imageN.png文件路径，完成图片与单元格位置的对应。

补充说明 #

• openpyxl对VML格式图片支持有限，需手动解析XML
• 少数Excel文件会将小图片直接嵌入单元格属性中，需检查工作表XML的<c>标签是否包含<v:shape>相关内容

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