854 字
4 分钟
wps嵌入式图片获取指定单元格图片地址,传单元格内容即可获取
本文同步自影刀社区 RPA 板块(文章),作者 【耐家军】DC:原文链接
效果展示
可视化代码
📦 影刀指令文件:
py代码 代码参考https://www.yuque.com/pengzhiqiang999/xiaokenai/vppgp62x3ln148g5?singleDoc# 二次修改
# 使用提醒:# 1. xbot包提供软件自动化、数据表格、Excel、日志、AI等功能# 2. package包提供访问当前应用数据的功能,如获取元素、访问全局变量、获取资源文件等功能# 3. 当此模块作为流程独立运行时执行main函数# 4. 可视化流程中可以通过"调用模块"的指令使用此模块
import xbotfrom xbot import print, sleepfrom .import packagefrom .package import variables as glvimport zipfileimport osimport xml.etree.ElementTree as ETimport openpyxlfrom 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_mapdef dqzdgtp(excel_path,out_dir,cell): return extract_images_from_excel_with_simple_formula_keys(excel_path, out_dir)[cell]def main(args): pass wps嵌入式图片获取指定单元格图片地址,传单元格内容即可获取
https://dc6.com.cn/posts/wps-qian-ru-shi-tu-pian-huo-qu-zhi-ding-dan-yuan-ge-tu-pian/