Excel嵌入图片至单元格
from PIL import Image
import os
import zipfile
import tempfile
import xml.etree.ElementTree as ET
import secrets
class ExcelHandler:
def __init__(self):
self.map_img = {}
self.xml_declaration = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
self.cell_images_rels_path = "xl/_rels/cellimages.xml.rels"
self.cell_images_path = "xl/cellimages.xml"
self.sheet_path = "xl/worksheets/{}.xml"
def add_img(self, zip_file, img_paths):
"""添加图片"""
def generate_random_filename():
filename = secrets.token_bytes(16).hex().upper() + ".png"
return filename
def get_img_size(path):
# 打开图像并获取大小
with Image.open(path) as im:
width, height = im.size
return width, height
for img_path in img_paths:
img_name = os.path.basename(img_path)
new_img_name = generate_random_filename()
zip_file.write(img_path, arcname=f"xl/media/{new_img_name}")
size = get_img_size(img_path)
self.map_img[img_name] = {"path": f"media/{new_img_name}", "size": size}
@staticmethod
def create_id(ids, pre=""):
new_id = pre + "1"
num = 1
while new_id in ids:
num += 1
new_id = f"{pre}{num}"
return new_id
def write_cell_images_rels(self, z_in, z_out):
if self.cell_images_rels_path not in z_in.namelist():
raise Exception("请先在表格中任意嵌入一张图片")
else:
root = ET.fromstring(z_in.read(self.cell_images_rels_path))
relationships = root.findall(
'{http://schemas.openxmlformats.org/package/2006/relationships}Relationship')
rids = list(map(lambda node: node.get('Id'), relationships))
rid = self.create_id(rids, pre="rId")
rids.append(rid)
for img_name, info in self.map_img.items():
new_element = ET.Element('ns0:Relationship')
new_element.set('Id', rid)
new_element.set('Type', "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image")
new_element.set('Target', info["path"])
root.insert(-1, new_element)
info["rid"] = rid
self.map_img[img_name] = info
xml_str = ET.tostring(root)
z_out.writestr(self.cell_images_rels_path, self.xml_declaration + xml_str.decode())
def write_cell_images(self, z_in, z_out):
def create_cell_image(_id, img_uuid, descr, rid, cx, cy):
cell_image = ET.SubElement(root, 'ns0:cellImage')
pic = ET.SubElement(cell_image, 'ns1:pic')
nvPicPr = ET.SubElement(pic, 'ns1:nvPicPr')
cNvPr = ET.SubElement(nvPicPr, 'ns1:cNvPr', attrib={
'id': _id,
'name': img_uuid,
'descr': descr
})
cNvPicPr = ET.SubElement(nvPicPr, 'ns1:cNvPicPr')
picLocks = ET.SubElement(cNvPicPr, 'ns2:picLocks', attrib={
'noChangeAspect': '1'
})
blipFill = ET.SubElement(pic, 'ns1:blipFill')
blip = ET.SubElement(blipFill, 'ns2:blip', attrib={
'ns3:embed': rid
})
stretch = ET.SubElement(blipFill, 'ns2:stretch')
fillRect = ET.SubElement(stretch, 'ns2:fillRect')
spPr = ET.SubElement(pic, 'ns1:spPr')
xfrm = ET.SubElement(spPr, 'ns2:xfrm')
off = ET.SubElement(xfrm, 'ns2:off', attrib={
'x': '66',
'y': '66'
})
ext = ET.SubElement(xfrm, 'ns2:ext', attrib={
'cx': cx,
'cy': cy
})
prstGeom = ET.SubElement(spPr, 'ns2:prstGeom', attrib={
'prst': 'rect'
})
avLst = ET.SubElement(prstGeom, 'ns2:avLst')
if self.cell_images_rels_path not in z_in.namelist():
raise Exception("请先在表格中任意嵌入一张图片")
else:
root = ET.fromstring(z_in.read(self.cell_images_path))
namespaces = {'xdr': 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing'}
ids = [c_nv_pr.get('id') for c_nv_pr in root.findall('.//xdr:cNvPr', namespaces)]
for img_name, info in self.map_img.items():
w, h = info["size"]
uuid = f'ID_{secrets.token_bytes(16).hex().upper()}'
create_cell_image(self.create_id(ids), uuid, img_name, info["rid"], str(w * 9525), str(h * 9525))
info["img_uuid"] = uuid
self.map_img[img_name] = info
xml_str = ET.tostring(root)
z_out.writestr(self.cell_images_path, self.xml_declaration + xml_str.decode())
def write_sheet(self, z_in, z_out, sheet_path, row, col, img_uuid):
"""
插入图片
"""
root = ET.fromstring(z_in.read(sheet_path))
pre = ""
row_node = ET.Element(f'{pre}row')
row_node.set('r', row)
row_node.set('spans', '1:1')
row_node.set('customHeight', '1')
cell_node = ET.SubElement(row_node, f"{pre}c")
cell_node.set("r", f"{col.upper()}{row}")
cell_node.set("t", "str")
f_node = ET.SubElement(cell_node, f"{pre}f")
v_node = ET.SubElement(cell_node, f"{pre}v")
f_node.text = f'_xlfn.DISPIMG("{img_uuid}",1)'
v_node.text = f'=DISPIMG("{img_uuid}",1)'
sheet_data = root.find('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetData')
sheet_data.append(row_node)
xml_str = ET.tostring(root)
z_out.writestr(sheet_path, self.xml_declaration + xml_str.decode())
def embed_img_to_cell(self, excel_path, sheet_name, img_path, row, col):
"""
嵌入图片至单元格
:param excel_path: 表格路径
:param sheet_name: 工作表名称
:param img_path: 图片路径
:param row: 行
:param col: 列
:return:
"""
temp_path = ""
sheet_name = sheet_name.lower()
sheet_path = self.sheet_path.format(sheet_name)
row = str(row)
col = str(col).upper()
try:
temp_fd, temp_path = tempfile.mkstemp(dir=os.path.dirname(excel_path))
os.close(temp_fd)
with zipfile.ZipFile(excel_path, 'r') as z_in, zipfile.ZipFile(temp_path, 'a') as z_out:
if sheet_path not in z_in.namelist():
raise Exception(f"工作表{sheet_name}不存在")
z_out.comment = z_in.comment
self.add_img(z_out, [img_path])
self.write_cell_images_rels(z_in, z_out)
self.write_cell_images(z_in, z_out)
self.write_sheet(z_in, z_out, sheet_path, row, col, self.map_img[img_path]["img_uuid"])
for item in z_in.infolist():
filename = item.filename
if filename == self.cell_images_rels_path:
continue
if filename == self.cell_images_path:
continue
if filename == sheet_path:
continue
z_out.writestr(item, z_in.read(filename))
os.remove(excel_path)
os.rename(temp_path, excel_path)
finally:
if os.path.exists(temp_path):
os.remove(temp_path)
if __name__ == '__main__':
execl_handler = ExcelHandler()
execl_handler.embed_img_to_cell("新建 XLSX 工作表.xlsx", "sheet2", "150x150.png", 7, 2)
