Zenn
🙆‍♀️

OpenPyXLなしでPythonでExcelファイルをいじり倒す（シート追加編）

2023/04/12に公開
Excel
Python
tech

はじめに

この記事の続きです。Excel の xlsx ファイルの構造はおおむね分かったので、実際に Python で xlsx の中身をいじっていきます。最終的にやりたいのは、ブラウザ上で excel の加工を行うようなアプリケーションで

  • ブラウザから excel ファイルと画像をアップロードする
  • ユーザ設定に従ってその excel ファイルに新しいシートを追加
  • 新しく追加されたシートにはアップロードした画像が指定したレイアウトで埋め込まれる
  • 画像と一緒にテキストボックスや矢印が配置されるなどを載せる

という Web アプリケーションです。これを実現しようと思うと

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

という機能が少なくとも必要です。これはやはり OpenPyXl や xlwing では難しいです。
なので自作してみます。

今回は 1 と 2 の新シート追加です。

準備：xlsx を扱うための Workbook クラスの定義

まず excel 取り扱うために以下の Workbook クラスを定義します。xml ファイルをきちんとパースしようと思うと膨大なコードを書くことになるので、既存の xml は触らず、そこにデータを追加してくい作戦をとってます。

そのため、OpenPyXL などと違いかなり簡素な作りです。ns とか types のプロパティはなんやねん、と感じると思いますが後で解説するのでスルーしてください。

import shutil
import os
import zipfile
from lxml import etree as ET
import shutil

class Workbook:

    def __init__(self, filename):

        self.basename = os.path.splitext(os.path.basename(filename))[0]
        zip_filename = self.basename+".zip"
        shutil.copyfile(filename, zip_filename)

        def _rename(info: zipfile.ZipInfo):
            LANG_ENC_FLAG = 0x800
            encoding = 'utf-8' if info.flag_bits & LANG_ENC_FLAG else 'cp437'
            info.filename = info.filename.encode(encoding).decode('cp932')

        os.makedirs(self.basename, exist_ok=True)
        with zipfile.ZipFile(zip_filename) as zfile:
            for info in zfile.infolist():
                _rename(info)
                zfile.extract(info, self.basename)

        self.ns = {
            "main": "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}",
            "sheet": "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}",
            "sheet_rel": "{http://schemas.openxmlformats.org/package/2006/relationships}",
            "drawing": "{http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing}",
            "types":"{http://schemas.openxmlformats.org/package/2006/content-types}",
            "dmain":"{http://schemas.openxmlformats.org/drawingml/2006/main}",
            "draw_rel": "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}"
        }

        self.types = {
            "worksheet": "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet",
            "image": "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image",
            "drawing":"http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"
        }
        self.BASE_DIR = "./pages/img2report/api/assets"
        self.templates = {
            "sheet": os.path.join(self.BASE_DIR, "new_sheet.xml"),
            "sheet_rels": os.path.join(self.BASE_DIR, "new_sheet.xml.rels"),
            "shared_strings": os.path.join(self.BASE_DIR, "new_sharedStrings.xml"),
            "drawing_rels": os.path.join(self.BASE_DIR, "new_drawing.xml.rels"),
            "drawing": os.path.join(self.BASE_DIR, "new_drawing.xml"),
            "sheet_rels": os.path.join(self.BASE_DIR, "new_sheet.xml.rels"),
            "image": os.path.join(self.BASE_DIR, "new_image.xml"),
            "textbox": os.path.join(self.BASE_DIR, "new_textbox.xml"),
            "arrow": os.path.join(self.BASE_DIR, "new_arrow.xml")
        }

    def _write_xml(self, root, filename):
        ET.ElementTree(root).write(filename, encoding="UTF-8", xml_declaration=True)

    def write_as_xlsx(self, filename=None, delete_wokdir=True):

        shutil.make_archive(os.path.splitext(filename)[0], format='zip', root_dir=self.basename)
        zipfile = os.path.splitext(filename)[0]+ ".zip"
        os.rename(zipfile, filename)

        if delete_wokdir:
            shutil.rmtree(self.basename)
            os.remove(self.basename+".zip")

この Workbook クラスからインスタンスを生成し、write_as_xlsxメソッドで xlsx ファイルとしての書き出しができます。

wb = Workbook("test.xlsx")
wb.write_xlsx("test_modified.xlsx)

解決方法：xml を加工して新しいシートを追加するメソッド

結論から言うと、この Workbook クラスに以下のメソッドを追加すれば OK です。

def _get_next_relation_id(self, filename):
    max_relation_id = 0
    tree = ET.parse(filename)
    root = tree.getroot()
    for child in root:
        if int(child.attrib["Id"].lstrip("rId")) > max_relation_id:
            max_relation_id = int(child.attrib["Id"].lstrip("rId"))
    return f"rId{max_relation_id + 1}"

def add_sheet(self, sheet_name=None):

    def _get_next_sheet_id(self):
        max_sheet_id = 0
        tree = ET.parse(os.path.join(self.basename, "xl", "workbook.xml"))
        root = tree.getroot()
        for sheets in root.findall(f"{self.ns['main']}sheets"):
            for sheet in sheets:
                if int(sheet.attrib["sheetId"]) > max_sheet_id:
                    max_sheet_id = int(sheet.attrib["sheetId"])

        return str(max_sheet_id + 1)

    def _get_next_sheet_filename(self):
        files = os.listdir(os.path.join(self.basename, "xl", "worksheets"))
        xml_sheets_nums = [ int(i.lstrip("sheet").rstrip(".xml")) for i in files if i.endswith(".xml")]
        next_sheet_num = max(xml_sheets_nums)+1
        return f"sheet{next_sheet_num}.xml"

    # get params for adding new sheets
    next_sheet_id = _get_next_sheet_id(self)
    next_relation_id = self._get_next_relation_id(os.path.join(self.basename, "xl", "_rels", "workbook.xml.rels"))
    next_sheet_filename = _get_next_sheet_filename(self)

    # add new sheet
    shutil.copy(self.templates["sheet"], os.path.join(self.basename, "xl", "worksheets", next_sheet_filename))

    # edit workbook.xml
    tree = ET.parse(os.path.join(self.basename, "xl", "workbook.xml"))
    root = tree.getroot()
    for sheets in root.findall(f"{self.ns['main']}sheets"):
        new_sheet = ET.SubElement(
            sheets, f"{self.ns['main']}sheet",
            attrib={"name":sheet_name, "sheetId": next_sheet_id, f"{self.ns['sheet']}id": next_relation_id}
        )
    self._write_xml(root, os.path.join(self.basename, "xl", "workbook.xml"))

    # edit workbook.xml.rels
    tree = ET.parse(os.path.join(self.basename, "xl", "_rels", "workbook.xml.rels"))
    root = tree.getroot()
    new_relation = ET.SubElement(
        root, f"{self.ns['sheet_rel']}Relationship",
        attrib={"Id": next_relation_id, "Type": self.types["worksheet"], "Target": f"worksheets/{next_sheet_filename}"}
    )
    self._write_xml(root, os.path.join(self.basename, "xl", "_rels", "workbook.xml.rels"))


    # 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/worksheets/{next_sheet_filename}",
            "ContentType":"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"
        }
    )
    self._write_xml(root, os.path.join(self.basename, "[Content_Types].xml"))

    return next_sheet_filename

Workbook クラスに対し次のように add_sheet メソッドを実行することでシートが追加されます。

wb = Workbook("test.xlsx")
wb.add_sheet(sheet_name="新しいシート")
wb.write_xlsx("test_modified.xlsx)

解説：add_sheet メソッドの中身

よく知られているように xlsx の実態は zip です。拡張子を変えて解凍すると次のような構造になっています。実際に上の Workbook クラスのコンストラクタでは xlsx の解凍を行っており、作業中にはこれらのファイルが生成されています。

.
├── [Content_Types].xml
├── _rels
├── docProps
│   ├── app.xml
│   └── core.xml
└── xl
    ├── _rels
    │   └── workbook.xml.rels
    ├── drawings
    │   ├── drawing1.xml
    │   └── drawing2.xml
    ├── sharedStrings.xml
    ├── styles.xml
    ├── theme
    │   └── theme1.xml
    ├── workbook.xml
    └── worksheets
        ├── _rels
        │   ├── sheet1.xml.rels
        │   └── sheet2.xml.rels
        ├── sheet1.xml
        └── sheet2.xml

シートの実態はxl/worksheets以下にあるsheet1.xmlです。つまり、新たに sheet3 を追加したい場合、同じディレクトリにsheet3.xmlを追加すればよい・・・わけではありません。xlsx 内部の xml は相互に参照がなされており、これらの参照関係を解決していく必要があります。この参照関係がおかしくなった xlsx を excel で開こうとすると、

"ファイルが破損しているから修復するね"

的なダイアログが起動時に表示されます。参照関係を解決するには次のファイルを編集し、excel ブックとして認識できるようにする必要があります。

  • xl/sheet*.xml
  • [Content_Types].xml
  • workbook.xml
  • _rels/workbook.xml.rels

以下にそれぞれのファイルの編集に関するメモを書いていきます。

sxl/sheet*.xml

コードのこの部分です。予め空っぽのシートに相当する xml を抜き出しておいて、
それをxl/worksheetsにコピーしているだけです。

# add new sheet
shutil.copy(self.templates["sheet"], os.path.join(self.basename, "xl", "worksheets", next_sheet_filename))

[Content_Types].xml

xlsx で扱われる xml ファイルはその xml の種類がそれぞれ定義されています。例えば、workbook.xmlにはContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"というものが割り当てられています。
先ほど追加したシートにも ContentsType を割り当てる必要があります。

# 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/worksheets/{next_sheet_filename}",
        "ContentType":"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"
    }
)
self._write_xml(root, os.path.join(self.basename, "[Content_Types].xml"))

workbook.xml

excel では複数のシートで構成されるファイル全体のことをブックといいますが、ブックとシートの依存関係を管理しているのがworkbook.xmlです。xl/worksheets以下にある xml はこのファイルで関連付ける必要があります。なお、workbook.xmlで参照づけられていないシートはファイルとして存在していても excel 上からは認識されなくなります。

# edit workbook.xml
tree = ET.parse(os.path.join(self.basename, "xl", "workbook.xml"))
root = tree.getroot()
for sheets in root.findall(f"{self.ns['main']}sheets"):
    new_sheet = ET.SubElement(
        sheets, f"{self.ns['main']}sheet",
        attrib={"name":sheet_name, "sheetId": next_sheet_id, f"{self.ns['sheet']}id": next_relation_id}
    )
self._write_xml(root, os.path.join(self.basename, "xl", "workbook.xml"))

_rels/workbook.xml.rels

workbook.xmlではブックに紐づくシートを ID で参照しています。workbook.xml.relsではその ID のファイルへのリンクが記載されているので、ここも修正します。これで終わりです。excel ファイルが破損した状態はいろいろな理由がありますが、その一つに.rels に記載しているファイルが存在しないというものがあります。

# edit workbook.xml.rels
tree = ET.parse(os.path.join(self.basename, "xl", "_rels", "workbook.xml.rels"))
root = tree.getroot()
new_relation = ET.SubElement(
    root, f"{self.ns['sheet_rel']}Relationship",
    attrib={"Id": next_relation_id, "Type": self.types["worksheet"], "Target": f"worksheets/{next_sheet_filename}"}
)
self._write_xml(root, os.path.join(self.basename, "xl", "_rels", "workbook.xml.rels"))

終わりに

どうでしょうか。xml の中身を見てなんとかなるやろと思ってましたが想像以上に大変でした・・・。次は追加したシートに値を追加するメソッドについて書いていきます。

Discussion