openpyxl で保存時に図形を復元する

概要

openpyxl でワークブックを保存すると、図形や画像が保存されないという制限があります。
つまり、既存の Excel ブックを Workbook として開いて、セルの値をちょっと変えて上書き保存、という使い方をしたい場合に、図形や画像が失われてしまう、ということです。

これを解決しているサンプルを GitHub で公開しています。
本記事ではそのコードについて荒っぽく解説します。
私も細かいところまで理解している訳ではないので、質問はお手柔らかにお願いします。

XML 構成

Excel ブック（.xlsx/.xlsm）は実際には ZIP アーカイブであり、主に XML ファイルを含んでいます。図形や画像に関連する主なファイル/フォルダは以下の通りです：

ファイル/フォルダ	内容
[Content_Types].xml	コンテンツタイプの定義
xl/worksheets/sheet*.xml	各ワークシートのデータ
xl/worksheets/_rels/sheet*.xml.rels	ワークシートと図形などの関連付け
xl/drawings/drawing*.xml	図形の定義
xl/drawings/vmlDrawing*.vml	コメントの図形定義
xl/comments*.xml	コメントの内容
xl/diagrams/ フォルダ	図形データ
xl/media/ フォルダ	画像ファイル

openpyxl はこれらの一部を読込まないため、Workbook を上書き保存すると図形や画像が失われるのです。

コード

コードは GitHub のリポジトリ satamame/openpyxl-save-drawings で公開しています。
この中の save_with_drawings.py が解決策を実装しているコードです。

save_with_drawings.py の内容は以下のようになっています。

1. XML ファイル等を操作する関数群
   1. restore_folder() 関数
   2. restore_comments() 関数
   3. restore_xl_worksheets() 関数
   4. restore_ext_lst() 関数
   5. adjust_content_types() 関数
2. save_with_drawings() 関数 (メインルーチン)
   1. 保存前の Workbook を一時ディレクトリに展開。
   2. openpyxl で Workbook を保存。
   3. 保存後の Workbook を一時ディレクトリに展開。
   4. 関数群を使って保存後のディレクトリに画像等を復元。
   5. 保存後のディレクトリを圧縮して Workbook を上書き。

以下、どのようにファイルを復元しているか、関数ごとに見ていきます。

restore_folder()

これはフォルダ単位で復元するための関数です。

def restore_folder(
        before_dir: Path, after_dir: Path, folder2restore: str,
        delete_first=False):
    '''folder2restore 引数で指定されたフォルダを復元する。
    '''
    src = before_dir / folder2restore
    dest = after_dir / folder2restore

    if not src.is_dir():
        return

    if delete_first:
        shutil.rmtree(dest, ignore_errors=True)

    if not os.path.exists(dest):
        shutil.copytree(src, dest)

メインルーチンである save_with_drawings() からは、以下のように呼ばれています。

def save_with_drawings(
        wb: Workbook, src: Path, dest: Path, temp_dir_args=None):
    # 中略

    with tempfile.TemporaryDirectory(**temp_dir_args) as temp_dir:
        # 中略

        # xl/diagrams/, xl/media/, xl/drawings/ フォルダを復元する。
        restore_folder(before_dir, after_dir, 'xl/diagrams/')
        restore_folder(before_dir, after_dir, 'xl/media/')
        restore_folder(
            before_dir, after_dir, 'xl/drawings/', delete_first=True)

xl/diagrams/ フォルダと xl/media/ は、上で見たように図形と画像が入っているフォルダです。
openpyxl はこれらを保存しないので、restore_folder() でフォルダごと復元しています。

xl/drawings/ フォルダは openpyxl によって保存はされるのですが、内容が変わってしまっています。
具体的には、図形の定義は失われ、セルのコメントが保存前とは違う形式で保存されています。
なので delete_first 引数を指定してフォルダごと削除してから復元しています。

restore_comments()

この関数はセルのコメントに関するファイルを復元します。

def restore_comments(before_dir: Path, after_dir: Path):
    '''コメントの xml ファイルを復元する。
    '''
    # openpyxl が作成した xl/comments/comment*.xml を削除する。
    shutil.rmtree(after_dir / 'xl/comments', ignore_errors=True)

    # 保存前の xl/comments*.xml を復元する。
    target_ptn = re.compile(r'comments[0-9]+.xml')
    for f in (before_dir / 'xl').iterdir():
        if target_ptn.fullmatch(f.name):
            shutil.copy2(f, after_dir / 'xl')

元の Excel ファイルでは、コメントは xl/comments*.xml というファイルに保存されていますが、openpyxl で保存すると xl/comments/comment*.xml という異なるファイルに保存されます。
また、元のファイル形式とは異なる形式で保存され、コメントの表示位置や書式が変わっています。

これらの違いを元に戻すことによって、上で復元した xl/drawings/ フォルダの内容とも整合性が取れるようになります。

restore_xl_worksheets()

この関数はワークシートと図形やコメントの関連付けを復元します。
関連付けの復元はファイル単位でコピーという訳には行かないため、複雑な処理になっています。

main_ns = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
rel_ns = 'http://schemas.openxmlformats.org/package/2006/relationships'

def restore_xl_worksheets(before_dir: Path, after_dir: Path):
    '''xl/worksheets/ フォルダ内の _rels/ フォルダと *.xml ファイルを復元する。
    '''
    src = before_dir / 'xl/worksheets/'
    dest = after_dir / 'xl/worksheets/'

    src_rels = src / '_rels/'
    dest_rels = dest / '_rels/'

    # 保存前の _rels/ フォルダ内の *.xml.rels ファイルについて処理する。
    for f in src_rels.iterdir():
        if not f.name.endswith('.xml.rels'):
            continue

        # 保存前の .xml.rels の root を取得する。
        before_tree = etree.parse(f)
        before_root = before_tree.getroot()

        # 以下の Relationship を取得する。
        # Target="../drawings/drawing*.xml"
        # Target="../drawings/vmlDrawing*.vml"
        # Target="../comments*.xml"
        namespaces = {'ns': before_root.nsmap[None]}
        rels = before_root.xpath('ns:Relationship', namespaces=namespaces)
        keep_drawings = []
        keep_vmls = []
        keep_comments = []
        for rel in rels:
            target: str = rel.get('Target')
            if not target:
                continue
            if target.startswith('../drawings/drawing'):
                keep_drawings.append(rel)
            elif target.startswith('../drawings/vmlDrawing'):
                keep_vmls.append(rel)
            elif target.startswith('../comments'):
                keep_comments.append(rel)

        # 保存後の .xml.rels の root を取得または作成する。
        after_path = dest_rels / f.name
        if after_path.exists():
            after_tree = etree.parse(after_path)
            after_root = after_tree.getroot()
        elif not (keep_comments or keep_vmls or keep_drawings):
            # 保存後のファイルもなく復元するものも無ければ、次のファイルへ。
            continue
        else:
            after_root = etree.Element("Relationships", nsmap={None: rel_ns})

        # 保存後の .xml.rels から openpyxl が追加した以下の Relationsip を削除。
        # Target="/xl/comments/comment*.xml"
        # Target="/xl/drawings/commentsDrawing*.vml"
        namespaces = {'ns': after_root.nsmap[None]}
        rels = after_root.xpath('ns:Relationship', namespaces=namespaces)
        for rel in rels:
            target: str = rel.get('Target')
            if not target:
                continue
            if target.startswith('/xl/comments/comment'):
                after_root.remove(rel)
            elif target.startswith('/xl/drawings/commentsDrawing'):
                after_root.remove(rel)

        # sheet*.xml.rels に対応する sheet*.xml ファイルの root を準備する。
        xml_path = dest / f.name[:-5]  # .rels を削ったファイル名
        xml_tree = etree.parse(xml_path)
        xml_root = xml_tree.getroot()
        # namespace がなければ追加しておく。
        ns = "http://schemas.openxmlformats.org/officeDocument/2006/"\
            "relationships"
        xml_root = add_ns(xml_root, 'r', ns)

        # drawing 要素を legacyDrawing 要素よりも前に挿入する必要があるが、
        # 最初に legacyDrawing を削除するので、自動的にそうなる。

        # 先に保存後の sheet*.xml から legacyDrawing を削除しておく。
        legacy_drws = xml_root.findall(f'.//{{{main_ns}}}legacyDrawing')
        for legacy_drw in legacy_drws:
            xml_root.remove(legacy_drw)

        # 保存後の .xml.rels の root に復元した Relationship を足していく。
        max_id = get_rel_max_id(after_root)
        for rel in keep_drawings:
            max_id += 1
            rel.set('Id', f'rId{max_id}')
            after_root.append(rel)

            # 対応する drawing 要素を sheet*.xml に足す。
            drw = etree.Element('drawing')
            drw.set(f'{{{ns}}}id', f'rId{max_id}')
            xml_root.append(drw)

        for rel in keep_vmls:
            max_id += 1
            rel.set('Id', f'rId{max_id}')
            after_root.append(rel)

            # 対応する legacyDrawing 要素を sheet*.xml に足す。
            ldrw = etree.Element('legacyDrawing')
            ldrw.set(f'{{{ns}}}id', f'rId{max_id}')
            xml_root.append(ldrw)

        for rel in keep_comments:
            max_id += 1
            rel.set('Id', f'rId{max_id}')
            after_root.append(rel)

            # comments は sheet*.xml に足さなくて良い。

        # sheet*.xml を保存する。
        xml_tree = etree.ElementTree(xml_root)
        xml_tree.write(xml_path, encoding='utf-8')

        # 保存する。
        after_tree = etree.ElementTree(after_root)
        after_tree.write(after_path, encoding='utf-8')

関連付けの復元は、ファイル単位でコピーという訳には行きません。
xl/worksheets/_rels/sheet*.xml.rels ファイルを一つずつ見ていって (一番外側の for ループ)、XML の要素単位で復元します。
sheet*.xml.rels ファイル自体が無くなっていれば、それも作り直す必要があります。

for ループの中では、ここまでで復元した xl/drawings/* や xl/comments*.xml の Relationship を、保存前のファイルから探してきて復元しています。
この時、Id を採番し直しています。
また、openpyxl が保存した余計な Relationship を削除したり、復元した Relationship への参照を xl/worksheets/sheet*.xml に追加したりしています。

restore_ext_lst()

この関数はワークシートの拡張リスト情報を復元します。
複雑な条件付き書式や入力規則、SmartArt などがこの拡張リストを使っているのですが、openpyxl はこれも保存してくれません。

def restore_ext_lst(before_dir: Path, after_dir: Path):
    '''xl/worksheets/*.xml ファイル内の <extLst> を復元する。
    '''
    src = before_dir / 'xl/worksheets/'
    dest = after_dir / 'xl/worksheets/'

    # 保存前の worksheets/ フォルダ内の *.xml ファイルについて処理する。
    for f in src.iterdir():
        if not f.name.endswith('.xml'):
            continue

        # 保存前の xml の root を取得する。
        before_tree = etree.parse(f)
        before_root = before_tree.getroot()

        # 保存前の xml から <extLst> を取得する。
        extLsts = before_root.findall(f".//{{{main_ns}}}extLst")
        if not extLsts:
            continue

        # 保存後の xml の root を取得する。
        after_tree = etree.parse(dest / f.name)
        after_root = after_tree.getroot()

        # すべての <extLst> を復元
        for extLst in extLsts:
            parent = extLst.getparent()
            parent_tag = parent.tag.split('}')[-1]

            # 保存前と同じ親を探し、その下に復元する。
            after_parent = after_root.find(f".//{{{main_ns}}}{parent_tag}")
            if after_parent:
                after_parent.append(extLst)
            else:
                # 同じ親がなければ root 直下に復元する。
                after_root.append(extLst)

        # 保存する。
        after_tree = etree.ElementTree(after_root)
        after_tree.write(dest / f.name, encoding='utf-8')

この関数では元のファイルから extLst 要素とその子要素を取得し、保存後のファイルに追加しています。

adjust_content_types()

この関数はコンテンツタイプ定義 XML ([Content_Types].xml) を調整します。

コンテンツタイプは Excel がファイルを解釈するために必要ですが、拡張子によって一律に決まらないものについては、個々のファイルについて登録しておく必要があります。

diagram_ctype_base = \
    'application/vnd.openxmlformats-officedocument.drawingml.{}+xml'
diagram_ctype_map = {
    'colors': diagram_ctype_base.format('diagramColors'),
    'data': diagram_ctype_base.format('diagramData'),
    'layout': diagram_ctype_base.format('diagramLayout'),
    'quickStyle': diagram_ctype_base.format('diagramStyle'),
    'drawing': "application/vnd.ms-office.drawingml.diagramDrawing+xml",
}
drawing_ctype = 'application/vnd.openxmlformats-officedocument.drawing+xml'
comments_ctype = 'application/vnd.openxmlformats-officedocument.spreadsheetml'\
    '.comments+xml'

def adjust_content_types(after_dir: Path):
    '''[Content_Types].xml の内容を調整する。
    '''
    file_path = after_dir / '[Content_Types].xml'
    tree = etree.parse(file_path)
    root = tree.getroot()

    ct_ns = 'http://schemas.openxmlformats.org/package/2006/content-types'

    # xl/comments/ フォルダ内のファイルに対する Override 要素を削除する。
    comments = root.findall(f'.//{{{ct_ns}}}Override')
    for comment in comments:
        if comment.get("PartName", "").startswith("/xl/comments/"):
            root.remove(comment)

    # 画像の拡張子一覧
    img_exts = {"png", "jpg", "jpeg", "gif", "bmp", "tiff", "tif"}

    # 既存の Default 要素の拡張子を取得する。
    namespaces = {'ns': ct_ns}
    defaults = root.xpath('ns:Default', namespaces=namespaces)
    def_exts = {elem.get("Extension") for elem in defaults}

    exts = set()
    for folder in ["xl/diagrams/", "xl/media/", "xl/drawings/"]:
        dir_path = after_dir / folder
        if not dir_path.is_dir():
            continue
        for file in dir_path.iterdir():
            ext = file.suffix.strip('.')
            if ext:
                exts.add(ext)

    # 既存にない拡張子を Default 要素として追加する。
    for ext in exts - def_exts:
        if ext in img_exts:
            content_type = f"image/{ext}"
        elif ext == "emf":
            content_type = "image/x-emf"
        else:
            content_type = f"application/{ext}"

        elem = etree.Element(
            "Default", Extension=ext, ContentType=content_type)
        root.append(elem)

    # xl/diagrams/ フォルダ内のファイルに対する Override 要素を追加する。
    dir_path = after_dir / 'xl/diagrams'
    if dir_path.is_dir():
        for file in dir_path.iterdir():
            if file.suffix == ".xml":
                part_name = f"/xl/diagrams/{file.name}"
                ctype = diagram_ctype_map[re.sub(r'\d+$', '', file.stem)]
                override_elem = etree.Element(
                    "Override", PartName=part_name, ContentType=ctype)
                root.append(override_elem)

    # xl/drawings/ フォルダ内のファイルに対する Override 要素を追加する。
    dir_path = after_dir / 'xl/drawings'
    if dir_path.is_dir():
        for file in dir_path.iterdir():
            if file.suffix == ".xml":
                part_name = f"/xl/drawings/{file.name}"
                override_elem = etree.Element(
                    "Override", PartName=part_name, ContentType=drawing_ctype)
                root.append(override_elem)

    # xl/ フォルダ内の comments*.xml ファイルに対する Override 要素を追加する。
    dir_path = after_dir / 'xl'
    for file in dir_path.iterdir():
        if file.name.startswith('comments') and file.suffix == ".xml":
            part_name = f"/xl/{file.name}"
            override_elem = etree.Element(
                "Override", PartName=part_name, ContentType=comments_ctype)
            root.append(override_elem)

    # 保存する。
    tree = etree.ElementTree(root)
    tree.write(file_path, encoding='utf-8')

画像など、拡張子によって一律に決まるコンテンツタイプは Default という要素になっています。
これも openpyxl から見て不要なものは削除されています。そもそも画像が削除されるのでほとんど削除されます。
この関数では、ファイルをひとつひとつ見るのは面倒なので、一般的な画像のタイプをひととおり登録しています。

拡張子によって一律に決まらないコンテンツタイプは Override 要素になります。
これはファイル単位ですべて登録する必要があり、この関数では復元した個々のファイルについて正しいタイプを登録しています。

使い方のコツ

save_with_drawings.py は openpyxl によって失われた情報を完全に復元するものではありません。
たとえば、ActiveX Control などは復元されないと思います。

何がどのように復元されたかを見るには、temp_dir_args 引数に {'delete': False} を指定して、展開されたフォルダを削除しないようにして、WinMerge で差分を見ると良いです。