OpenPyXLなしでPythonでExcelファイルをいじり倒す（テキストボックス、画像、矢印の追加)

はじめに

この記事の続きです。
今回は、4のセルへの値追加です。今回が最後です！

1. 既存の excel ファイルをテキストボックスや罫線などを破壊せずに読み込む
2. 読み込んだシートに新しいシートを追加できる
3. シート上のセルに値を追加できる
4. 読み込んだシートにテキストボックスなどの新しい要素を追加できる
5. Linux 上で動作する

解決方法1：シートに画像を追加するメソッド

前回のWorkbookクラスに以下のメソッドを追加すればOKです。

    def _update_draw_rels(self, drawing_rel_path, image_name, draw_rel_id):
        draw_rels = ET.parse(drawing_rel_path)
        draw_rels_root = draw_rels.getroot()
        new_relation = ET.SubElement(
            draw_rels_root,
            f"{self.ns['sheet_rel']}Relationship",
            attrib={
                "Id": draw_rel_id,
                "Type": self.types["image"],
                "Target": f"../media/{image_name}",
            },
        )
        self._write_xml(draw_rels_root, drawing_rel_path)
	
    def _create_newdraw(self, draw_path, draw_name):
        shutil.copy(self.templates["drawing"], draw_path)
        # update Content Type
        tree = ET.parse(os.path.join(self.basename, "[Content_Types].xml"))
        root = tree.getroot()
        types = ET.SubElement(
            root,
            f"{self.ns['types']}Override",
            attrib={
                "PartName": f"/xl/drawings/{draw_name}",
                "ContentType": "application/vnd.openxmlformats-officedocument.drawing+xml",
            },
        )
        self._write_xml(root, os.path.join(self.basename, "[Content_Types].xml"))

    def _update_image(self, draw_path, draw_rel_id, anchor):
        # set image props
        dr = ET.parse(draw_path).getroot()
        image = ET.parse(self.templates["image"]).getroot()
        next_item_id = len(dr) + 1
        points = ["from", "to"]
        props = ["col", "colOff", "row", "rowOff"]

        # set image Id and position
        for item in image.findall(f"{self.ns['drawing']}twoCellAnchor"):
            for c in item.iter(f"{self.ns['drawing']}cNvPr"):
                c.set("id", str(next_item_id))
            for c in item.iter(f"{self.ns['dmain']}blip"):
                c.set(f"{self.ns['draw_rel']}embed", draw_rel_id)
            for p in points:
                for c in item.findall(f"{self.ns['drawing']}{p}"):
                    for prop in props:
                        for gc in c.findall(f"{self.ns['drawing']}{prop}"):
                            gc.text = str(anchor[p][prop])

        # add image tag to drawing
        dr = ET.parse(draw_path).getroot()
        for child1 in image.findall(f"{self.ns['drawing']}twoCellAnchor"):
            child2 = ET.Element(child1.tag, child1.attrib)
            child2.text = child1.text
            dr.append(child2)
            self._copy_subtree(child1, child2)
        self._write_xml(dr, draw_path)

    def add_image(self, sheet_name, image, anchor={}):
        sheet_id = sheet_name.lstrip("sheet").rstrip(".xml")
        sheet_rels_path = os.path.join(
            self.basename, "xl", "worksheets", "_rels", sheet_name + ".rels"
        )
        draw_name = f"drawing{sheet_id}.xml"
        draw_path = os.path.join(self.basename, "xl", "drawings", draw_name)

        # copy media
        current_image_id = self._get_current_image_id()
        image_name = f"image{current_image_id}.jpeg"
        shutil.copy(image, os.path.join(self.basename, "xl", "media", image_name))

        # update drawing rels file
        draw_rel_path = os.path.join(
            self.basename, "xl", "drawings", "_rels", f"drawing{sheet_id}.xml.rels"
        )

        if not os.path.exists(draw_rel_path):
            shutil.copy(self.templates["drawing_rels"], draw_rel_path)
        draw_rel_id = self._get_next_relation_id(draw_rel_path)
        self._update_draw_rels(draw_rel_path, image_name, draw_rel_id)
        # update drawing
        if not os.path.exists(draw_path):
            self._create_newdraw(draw_path, draw_name)
        self._update_image(draw_path, draw_rel_id, anchor)

        # update sheet rels
        if not os.path.exists(sheet_rels_path):
            shutil.copy(self.templates["sheet_rels"], sheet_rels_path)
            self._create_sheet_rels(sheet_rels_path, sheet_name)

Workbook クラスに対し次のように add_image メソッドを実行することでセルに値が追加されます。画像の挿入位置はanchorで指定可能です。以下の例の場合、A2セルからC4セルの範囲に画像が拡大・縮小されます。

wb = Workbook("test.xlsx")
wb.add_sheet(sheet_name="新しいシート")
wb.add_image(
    sheet_name="新しいシート",
    image="hello.jpeg",
    image_anchor={
    anchor = {
        "from": {
            "col": 1,
            "row": 2,
            "colOff": 0,
            "rowOff": 0,
        },
        "to": {
            "col": 3,
            "row": 4,
            "colOff": 0,
            "rowOff": 0,
        },
    }
)
wb.write_xlsx("test_modified.xlsx)

add_imageの仕組みは前回のadd_cell_valueメソッドとよく似ています。画像やテキストボックス、図形などシートの上に配置される要素はdrawing*.xmlというファイルにまとめられています。また、画像ファイルの実態はxl/media内に配置されています。そのため流れとしては① drawing*.xmlに新しい要素のIDとその配置場所を追加し、②埋め込みたい画像をxl/mediaにコピーする、というものになります。

画像の場合は要素の配置方法としてtwoCellAnchorというタグを使用します。これは左上と右下のセルの番地を指定するという方法です。セルの番地に対して画像を張り付けるため、アスペクト比を維持したりするには適切な数値を計算する必要があります。

解決方法2：シートにテキストボックスを追加するメソッド

    def _update_textbox(self, draw_path, text, anchor):
        dr = ET.parse(draw_path).getroot()
        image = ET.parse(self.templates["textbox"]).getroot()
        points = ["from"]
        props = ["col", "colOff", "row", "rowOff"]

        # set image Id and position
        for item in image.findall(f"{self.ns['drawing']}oneCellAnchor"):
            for p in points:
                for c in item.findall(f"{self.ns['drawing']}{p}"):
                    for prop in props:
                        for gc in c.findall(f"{self.ns['drawing']}{prop}"):
                            gc.text = str(anchor[p][prop])
            for c in item.iter(f"{self.ns['dmain']}t"):
                c.text = text

        # add image tag to drawing
        dr = ET.parse(draw_path).getroot()
        for child1 in image.findall(f"{self.ns['drawing']}oneCellAnchor"):
            child2 = ET.Element(child1.tag, child1.attrib)
            child2.text = child1.text
            dr.append(child2)
            self._copy_subtree(child1, child2)
        self._write_xml(dr, draw_path)

    def add_textbox(self, sheet_name, text, anchor={}):
        sheet_id = sheet_name.lstrip("sheet").rstrip(".xml")
        sheet_rels_path = os.path.join(
            self.basename, "xl", "worksheets", "_rels", sheet_name + ".rels"
        )
        draw_name = f"drawing{sheet_id}.xml"
        draw_path = os.path.join(self.basename, "xl", "drawings", draw_name)

        # update drawing
        draw_rel_path = os.path.join(
            self.basename, "xl", "drawings", "_rels", f"drawing{sheet_id}.xml.rels"
        )
        if not os.path.exists(draw_path):
            self._create_newdraw(draw_path, draw_name)
        self._update_textbox(draw_path, text, anchor)

        # update drawing rels file
        draw_rel_path = os.path.join(
            self.basename, "xl", "drawings", "_rels", f"drawing{sheet_id}.xml.rels"
        )
        if not os.path.exists(draw_rel_path):
            shutil.copy(self.templates["drawing_rels"], draw_rel_path)

        # update sheet rels
        if not os.path.exists(sheet_rels_path):
            shutil.copy(self.templates["sheet_rels"], sheet_rels_path)
            self._create_sheet_rels(sheet_rels_path, sheet_name)

wb = Workbook("test.xlsx")
wb.add_sheet(sheet_name="新しいシート")
wb.add_textbox(
    sheet_name="新しいシート",
    text="こんにちは",
    anchor = {
	"from": {"col": 2, "row": 0, "colOff": 0, "rowOff": 250000},
	"to": {"col": 3, "row": 0, "colOff": 0, "rowOff": 250000},
    }    
)
wb.write_xlsx("test_modified.xlsx)

解決方法3: シートに矢印を追加するメソッド

    def _update_arrow(self, draw_path, arrow_props, anchor):
        dr = ET.parse(draw_path).getroot()
        image = ET.parse(self.templates["arrow"]).getroot()
        next_item_id = len(dr) + 1
        points = ["from", "to"]
        props = ["col", "colOff", "row", "rowOff"]

        # set image Id and position
        for item in image.findall(f"{self.ns['drawing']}twoCellAnchor"):
            for p in points:
                for c in item.findall(f"{self.ns['drawing']}{p}"):
                    for prop in props:
                        for gc in c.findall(f"{self.ns['drawing']}{prop}"):
                            gc.text = str(anchor[p][prop])

        # add image tag to drawing
        dr = ET.parse(draw_path).getroot()
        for child1 in image.findall(f"{self.ns['drawing']}twoCellAnchor"):
            child2 = ET.Element(child1.tag, child1.attrib)
            child2.text = child1.text
            dr.append(child2)
            self._copy_subtree(child1, child2)
        self._write_xml(dr, draw_path)
	
    def add_arrow(self, sheet_name, arrow_props, anchor={}):
        sheet_id = sheet_name.lstrip("sheet").rstrip(".xml")
        sheet_rels_path = os.path.join(
            self.basename, "xl", "worksheets", "_rels", sheet_name + ".rels"
        )
        draw_name = f"drawing{sheet_id}.xml"
        draw_path = os.path.join(self.basename, "xl", "drawings", draw_name)

        # update drawing
        draw_rel_path = os.path.join(
            self.basename, "xl", "drawings", "_rels", f"drawing{sheet_id}.xml.rels"
        )
        if not os.path.exists(draw_path):
            self._create_newdraw(draw_path, draw_name)
        self._update_arrow(draw_path, arrow_props, anchor)

        # update drawing rels file
        draw_rel_path = os.path.join(
            self.basename, "xl", "drawings", "_rels", f"drawing{sheet_id}.xml.rels"
        )
        if not os.path.exists(draw_rel_path):
            shutil.copy(self.templates["drawing_rels"], draw_rel_path)

        # update sheet rels
        if not os.path.exists(sheet_rels_path):
            shutil.copy(self.templates["sheet_rels"], sheet_rels_path)
            self._create_sheet_rels(sheet_rels_path, sheet_name)

wb = Workbook("test.xlsx")
wb.add_sheet(sheet_name="新しいシート")
wb.add_textbox(
    sheet_name="新しいシート",
    arrow_props=None,
    anchor = {
	"from": {"col": 2, "row": 0, "colOff": 0, "rowOff": 250000},
	"to": {"col": 3, "row": 0, "colOff": 0, "rowOff": 250000},
    }
)
wb.write_xlsx("test_modified.xlsx)

最後に

これでPythonで神Excelも作り放題です！どうしてもPythonでexcelを触りたいときの参考にしてもらえれば幸いですが、XMLを直接いじる部分が多いのでコードが複雑になりがちでまた、デバッグも難しい場面が多かったです。やはり、そもそもExcelを直接いじるのはアンチパターンですね・・・。