如何通过XML提取Excel形状的颜色？附现有文本提取代码

我有一段可完美提取Excel文件中所有形状文本的代码，针对包含2个工作表、每个工作表1个形状的简单xlsx文件，返回结果为['Text box 2']、['Sheet2 ellipse text 3']。但现在需要额外提取形状的填充颜色，且不能使用xlwings或pywin32（处理速度过慢），同时确认openpyxl无此功能。现有代码如下：

from zipfile import ZipFile
from lxml import etree

def main():
    z = ZipFile('/home/luis/tmp/shapes.xlsx')
    drawings = [ drw for drw in z.filelist if "drawings" in drw.filename.split('/') ]
    for drw in drawings:
        with z.open(drw.filename) as sstr:
            ssdata = sstr.read()
            
            tree = etree.fromstring(ssdata)
            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"
                  }
            shapes = tree.xpath('//xdr:txBody[preceding-sibling::xdr:spPr]/a:p/a:r/a:t/text()', namespaces = ns)
            
            print(shapes)

if __name__ == '__main__':
    main()

解决方案 #

Excel的xlsx本质是压缩包，工作表中的形状定义存储在drawings/目录下的XML文件中。每个形状的样式（包括填充颜色）在xdr:spPr节点内，文本内容在同级的xdr:txBody节点内。通过XPath关联这两个节点，即可同时提取文本和对应的填充颜色。

修改后的代码如下：

from zipfile import ZipFile
from lxml import etree

def main():
    z = ZipFile('/home/luis/tmp/shapes.xlsx')
    drawings = [drw for drw in z.filelist if "drawings" in drw.filename.split('/')]
    for drw in drawings:
        with z.open(drw.filename) as sstr:
            ssdata = sstr.read()
            tree = etree.fromstring(ssdata)
            ns = {
                "xdr": "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing",
                "a": "http://schemas.openxmlformats.org/drawingml/2006/main"
            }
            # 遍历每个包含文本和样式的形状节点
            shape_nodes = tree.xpath('//xdr:sp[./xdr:txBody and ./xdr:spPr]', namespaces=ns)
            for shape in shape_nodes:
                # 提取并拼接形状文本
                text_fragments = shape.xpath('./xdr:txBody/a:p/a:r/a:t/text()', namespaces=ns)
                shape_text = ''.join(text_fragments) if text_fragments else ''
                # 提取纯色填充的十六进制颜色值
                color_value = shape.xpath('./xdr:spPr/a:solidFill/a:srgbClr/@val', namespaces=ns)
                shape_color = color_value[0] if color_value else None
                # 输出配对结果
                print(f"形状文本: '{shape_text}', 填充颜色: {shape_color}")

if __name__ == '__main__':
    main()

代码说明 #

• 定位同时包含xdr:txBody（文本容器）和xdr:spPr（样式容器）的形状节点xdr:sp，确保文本与样式一一对应
• 文本提取：将形状内的多个文本片段（a:t节点）拼接成完整内容
• 颜色提取：从xdr:spPr下的a:solidFill/a:srgbClr节点获取val属性，该值为不带#的十六进制颜色码；若形状无纯色填充，返回None
• 自动处理多工作表：每个工作表对应的drawings文件会被单独遍历，无需额外区分工作表

内容的提问来源于stack exchange，提问作者Hooded 0ne