python获取excel中行号对应的底层图片文件名

[rungod]

代码： 全选

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

# 1. 自动获取当前脚本所在的文件夹路径
current_dir = os.path.dirname(os.path.abspath(__file__))
excel_file = os.path.join(current_dir, 'data.xlsx') # 请确保这里是你真实的Excel文件名
output_file = os.path.join(current_dir, '全表图片映射结果.txt')

# 2. 检查文件是否存在
if not os.path.exists(excel_file):
    print(f"错误：在当前文件夹下找不到 {excel_file}")
    print("请将你的原始 .xlsx 文件放到当前文件夹，并修改代码第8行的文件名！")
    input("按回车键退出...")
else:
    try:
        all_results = [] # 用于存储所有工作表的结果
        
        # 3. 将 xlsx 当作 zip 读取
        with zipfile.ZipFile(excel_file, 'r') as z:
            # 自动查找所有的 drawingX.xml 文件
            drawing_files = [f for f in z.namelist() if re.match(r'xl/drawings/drawing\d+\.xml', f)]
            
            if not drawing_files:
                print("❌ 未在文件中找到任何绘图文件(drawing)，可能该Excel没有图片。")
            else:
                for drawing_path in drawing_files:
                    # 从路径中提取数字，例如 drawing1.xml -> 1
                    sheet_num = re.search(r'drawing(\d+)\.xml', drawing_path).group(1)
                    rels_path = f'xl/drawings/_rels/drawing{sheet_num}.xml.rels'
                    
                    # 解析对应的 rels 文件
                    rid_to_media = {}
                    if rels_path in z.namelist():
                        rels_xml = z.read(rels_path)
                        root = ET.fromstring(rels_xml)
                        for rel in root:
                            rid = rel.get('Id')
                            target = rel.get('Target') 
                            if target and 'media/' in target:
                                rid_to_media[rid] = target.split('/')[-1]
                    
                    # 解析 drawing 文件
                    row_to_media = {}
                    drawing_xml = z.read(drawing_path)
                    root = ET.fromstring(drawing_xml)
                    
                    ns = {
                        'xdr': 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing',
                        'a': 'http://schemas.openxmlformats.org/drawingml/2006/main',
                        'r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
                    }
                    
                    for anchor in root.findall('.//xdr:twoCellAnchor', ns):
                        from_row = anchor.find('xdr:from/xdr:row', ns)
                        blip = anchor.find('.//a:blip', ns)
                        
                        if from_row is not None and blip is not None:
                            row_num = int(from_row.text) + 1 
                            embed_rid = blip.get('{http://schemas.openxmlformats.org/officeDocument/2006/relationships}embed')
                            
                            if embed_rid in rid_to_media:
                                row_to_media[row_num] = rid_to_media[embed_rid]
                    
                    # 将当前表的结果存入总列表
                    all_results.append((sheet_num, row_to_media))
        
        # 4. 将结果写入 TXT 文件
        with open(output_file, 'w', encoding='utf-8') as f:
            for sheet_num, row_dict in all_results:
                f.write(f"========== 工作表 {sheet_num} ==========\n")
                f.write("Excel行号\t对应的底层图片文件名\n")
                for row_num in sorted(row_dict.keys()):
                    f.write(f"{row_num}\t{row_dict[row_num]}\n")
                f.write("\n\n") # 表与表之间空两行
                
        print("✅ 所有工作表映射完成！")
        print(f"📁 结果已保存在：{output_file}")
        
    except Exception as e:
        print(f"❌ 发生错误：{e}")
        
    input("\n按回车键退出...")