

# 使用提醒:
# 1. xbot包提供软件自动化、数据表格、Excel、日志、AI等功能
# 2. package包提供访问当前应用数据的功能,如获取元素、访问全局变量、获取资源文件等功能
# 3. 当此模块作为流程独立运行时执行main函数
# 4. 可视化流程中可以通过"调用模块"的指令使用此模块
import xbot
from xbot import print, sleep
from .import package
from .package import variables as glv
import zipfile
import os
import xml.etree.ElementTree as ET
import openpyxl
from typing import *
def extract_images_from_excel_with_simple_formula_keys(xlsx_file_path, output_directory):
# 检查输入文件是否存在
if not os.path.exists(xlsx_file_path):
raise FileNotFoundError(f"Excel文件不存在: {xlsx_file_path}")
# 创建输出目录
if not os.path.exists(output_directory):
os.makedirs(output_directory)
def _read_excel_data(filename_path):
formula_to_id_map = {}
workbook = openpyxl.load_workbook(filename_path, data_only=False)
sheet = workbook.active
for row in sheet.iter_rows(min_row=1, values_only=False):
for cell in row:
if cell.value and isinstance(cell.value, str) and '=_xlfn.DISPIMG(' in cell.value:
# 提取嵌入的图片 ID
formula = cell.value
start = formula.find('"') + 1
end = formula.find('"', start)
image_id = formula[start:end]
# 将公式简化:去掉 "_xlfn."
simplified_formula = formula.replace('=_xlfn.DISPIMG(', '=DISPIMG(')
# 以简化公式为键,图片ID为值
formula_to_id_map[simplified_formula] = image_id
return formula_to_id_map
def _get_xml_id_image_map(xlsx_file_path):
try:
# 打开 XLSX 文件
with zipfile.ZipFile(xlsx_file_path, 'r') as zfile:
# 直接读取 XML 文件内容
with zfile.open('xl/cellimages.xml') as file:
xml_content = file.read()
with zfile.open('xl/_rels/cellimages.xml.rels') as file:
relxml_content = file.read()
except KeyError as e:
raise ValueError(f"Excel文件中未找到图片相关的XML文件,可能不包含嵌入图片: {e}")
# 将读取的内容转换为 XML 树
root = ET.fromstring(xml_content)
# 初始化映射字典
name_to_embed_map = {}
# 命名空间
namespaces = {
'xdr': 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing',
'a': 'http://schemas.openxmlformats.org/drawingml/2006/main'
}
# 遍历所有 pic 元素
for pic in root.findall('.//xdr:pic', namespaces=namespaces):
name = pic.find('.//xdr:cNvPr', namespaces=namespaces).attrib['name']
embed = pic.find('.//a:blip', namespaces=namespaces).attrib[
'{http://schemas.openxmlformats.org/officeDocument/2006/relationships}embed']
name_to_embed_map[name] = embed
root1 = ET.fromstring(relxml_content)
# 命名空间字典,根据 XML 中定义的命名空间进行设置
namespaces = {'r': 'http://schemas.openxmlformats.org/package/2006/relationships'}
# 创建 ID 和 Target 的映射
id_target_map = {child.attrib['Id']: child.attrib.get('Target', 'No Target Found') for child in
root1.findall('.//r:Relationship', namespaces=namespaces)}
# 使用字典推导构建新的映射表
name_to_target_map = {name: id_target_map[embed] for name, embed in name_to_embed_map.items() if
embed in id_target_map}
return name_to_target_map
def _sanitize_filename(filename):
"""清理文件名中的非法字符"""
# Windows文件系统中不允许的字符
invalid_chars = '<>:"/\\|?*'
for char in invalid_chars:
filename = filename.replace(char, '_')
return filename
# 读取Excel数据获取简化公式到图片ID的映射
formula_to_id_map = _read_excel_data(xlsx_file_path)
if not formula_to_id_map:
raise ValueError("Excel文件中未找到包含DISPIMG函数的单元格")
# 获取图片ID到路径的映射关系
name_to_target_map = _get_xml_id_image_map(xlsx_file_path)
# 实际路径映射字典(以简化公式为键)
formula_path_map = {}
# 打开xlsx文件(即Zip文件)
extracted_count = 0
with zipfile.ZipFile(xlsx_file_path, 'r') as zfile:
for simplified_formula, image_id in formula_to_id_map.items():
# 检查该图片ID是否在XML映射中
if image_id in name_to_target_map:
image_path = name_to_target_map[image_id]
# 构建实际的图片路径
actual_image_path = f'xl/{image_path}'
if actual_image_path in zfile.namelist():
# 读取图片内容
with zfile.open(actual_image_path) as image_file:
image_content = image_file.read()
# 获取原始文件扩展名
original_ext = os.path.splitext(image_path)[1] or '.png'
# 使用图片ID作为文件名(避免非法字符)
safe_filename = _sanitize_filename(image_id)
new_file_path = os.path.join(output_directory, f"{safe_filename}{original_ext}")
# 转换为绝对路径
absolute_file_path = os.path.abspath(new_file_path)
with open(new_file_path, 'wb') as new_file:
new_file.write(image_content)
# 添加到实际路径映射,使用简化公式作为键
formula_path_map[simplified_formula] = absolute_file_path
extracted_count += 1
else:
print(f"警告: 在压缩包中未找到文件 {actual_image_path}")
else:
print(f"警告: 图片ID {image_id} 在XML映射中未找到")
return formula_path_map
def dqzdgtp(excel_path,out_dir,cell):
return extract_images_from_excel_with_simple_formula_keys(excel_path, out_dir)[cell]
def main(args):
pass