Excelの価格表に、作った人のフォルダ名が残っていた。外部リンクの検査を2回書き直した

Excelの価格表に、作った人のフォルダ名が残っていた。外部リンクの検査を2回書き直した

xlsxの中身を見ると、販売価格のセルの数式は [1]原価!$B$2*1.3 になっています。試しに組んだ価格表の話です。

[1] は、同じブックの中の 原価 シートではありません。別のExcelファイルの 原価 シートを指す印です。その別ファイルがどこにあるのかは、xlsxをZIPとして開くと読めます。

unzip -p linked.xlsx xl/externalLinks/_rels/externalLink1.xml.rels
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath" Target="file:///C:\Users\hanako\Documents\原価管理.xlsx" TargetMode="External"/></Relationships>

hanako は、この記事のために付けた名前です。実在の人のものではありません。ただ、同じことが本物のファイルで起きれば、購入者に渡るのは販売価格の数字だけではなくなります。作った人のユーザー名と、フォルダの構成まで付いてきます。

以前、Excel商品の作成者欄を空にしても個人情報が残った件を書きました。そのとき「外部リンクは別に確認する」と書いて、そのままにしていました。今回はその宿題です。Pythonの標準ライブラリだけで検査を作り、2回書き直しました。AIを使った実験ではなく、手で組んだ見本での確認です。

見本は9つ。Excelでは作っていません

xlsxの中身はZIPです。作り方は、zipfile でXMLを書き出しただけです。Excelで保存したファイルではないので、本物のExcelが書き出す細部とは違うかもしれません。Excel本体では1つも開いていません。

openpyxl 3.1.5 には読ませました。linked.xlsx では外部リンクが1件あると認識され、file-link.xlsx ではセルのハイパーリンクの宛先が file:///C:/Users/hanako/Desktop/仕入れメモ.xlsx と読めました。形式としては読めるファイルです。

  • clean.xlsx 数式 B2*1.3 だけの価格表。通したい側
  • web-link.xlsx 同じ表に https://hibanas.net/ へのハイパーリンクがある。通したい側
  • linked.xlsx 別ブックの 原価 シートを数式で参照する。止めたい側
  • file-link.xlsx セルのハイパーリンクが、作った人のデスクトップのファイルを指す。止めたい側
  • fixed.xlsx 数式を値の1274に置き換えた版。通したい側
  • table-ref.xlsx 数式が SUM(Table1[[#This Row],[原価]])*1.3。誤検出の確認用
  • literal-1.xlsx 数式が文字列の "[1]" を含む。同じく誤検出の確認用
  • no-sheet.xlsx シートが0枚。検査が空振りしないかの確認用
  • not-xlsx.xlsx 中身はCSVで、xlsxではない
見本を作るコード(make_samples.py、全文)
import zipfile

NS = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
R = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
PR = 'http://schemas.openxmlformats.org/package/2006/relationships'
HDR = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n'


def cell(ref, text=None, num=None, formula=None):
    if formula is not None:
        return f'<c r="{ref}"><f>{formula}</f><v>{num}</v></c>'
    if num is not None:
        return f'<c r="{ref}"><v>{num}</v></c>'
    return f'<c r="{ref}" t="inlineStr"><is><t>{text}</t></is></c>'


def sheet(price_formula, price_cached, hyperlink_rid=None):
    c2 = cell("C2", formula=price_formula, num=price_cached) if price_formula else cell("C2", num=price_cached)
    d2 = cell("D2", text="仕入れメモ") if hyperlink_rid else ""
    links = f'<hyperlinks><hyperlink ref="D2" r:id="{hyperlink_rid}"/></hyperlinks>' if hyperlink_rid else ""
    return (HDR + f'<worksheet xmlns="{NS}" xmlns:r="{R}"><sheetData>'
            f'<row r="1">{cell("A1", text="商品")}{cell("B1", text="原価")}{cell("C1", text="販売価格")}</row>'
            f'<row r="2">{cell("A2", text="請求書テンプレート")}{cell("B2", num=980)}{c2}{d2}</row>'
            f'</sheetData>{links}</worksheet>')


def rels(items):
    body = "".join(
        f'<Relationship Id="{i}" Type="{t}" Target="{tg}"' + (' TargetMode="External"' if ext else "") + "/>"
        for i, t, tg, ext in items)
    return HDR + f'<Relationships xmlns="{PR}">{body}</Relationships>'


def build(path, *, formula=None, cached=1274, ext_book=None, hyperlink=None, sheets=1):
    ct = [f'<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>']
    wb_rels = []
    sheet_tags = ""
    for n in range(1, sheets + 1):
        ct.append(f'<Override PartName="/xl/worksheets/sheet{n}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>')
        wb_rels.append((f"rId{n}", f"{R}/worksheet", f"worksheets/sheet{n}.xml", False))
        sheet_tags += f'<sheet name="価格表" sheetId="{n}" r:id="rId{n}"/>'
    ext_tag = ""
    if ext_book:
        ct.append('<Override PartName="/xl/externalLinks/externalLink1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.externalLink+xml"/>')
        wb_rels.append(("rId9", f"{R}/externalLink", "externalLinks/externalLink1.xml", False))
        ext_tag = '<externalReferences><externalReference r:id="rId9"/></externalReferences>'
    workbook = HDR + f'<workbook xmlns="{NS}" xmlns:r="{R}"><sheets>{sheet_tags}</sheets>{ext_tag}</workbook>'
    with zipfile.ZipFile(path, "w", zipfile.ZIP_DEFLATED) as z:
        z.writestr("[Content_Types].xml", HDR + '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/>' + "".join(ct) + "</Types>")
        z.writestr("_rels/.rels", rels([("rId1", f"{R}/officeDocument", "xl/workbook.xml", False)]))
        z.writestr("xl/workbook.xml", workbook)
        z.writestr("xl/_rels/workbook.xml.rels", rels(wb_rels))
        for n in range(1, sheets + 1):
            z.writestr(f"xl/worksheets/sheet{n}.xml", sheet(formula, cached, "rId1" if hyperlink else None))
            if hyperlink:
                z.writestr(f"xl/worksheets/_rels/sheet{n}.xml.rels", rels([("rId1", f"{R}/hyperlink", hyperlink, True)]))
        if ext_book:
            z.writestr("xl/externalLinks/externalLink1.xml", HDR + f'<externalLink xmlns="{NS}" xmlns:r="{R}"><externalBook r:id="rId1"><sheetNames><sheetName val="原価"/></sheetNames><sheetDataSet><sheetData sheetId="0"><row r="2"><cell r="B2"><v>980</v></cell></row></sheetData></sheetDataSet></externalBook></externalLink>')
            z.writestr("xl/externalLinks/_rels/externalLink1.xml.rels", rels([("rId1", f"{R}/externalLinkPath", ext_book, True)]))


build("clean.xlsx", formula="B2*1.3")
build("web-link.xlsx", formula="B2*1.3", hyperlink="https://hibanas.net/")
build("linked.xlsx", formula="[1]原価!$B$2*1.3", ext_book="file:///C:\\Users\\hanako\\Documents\\原価管理.xlsx")
build("file-link.xlsx", formula="B2*1.3", hyperlink="file:///C:/Users/hanako/Desktop/仕入れメモ.xlsx")
build("no-sheet.xlsx", sheets=0)
build("fixed.xlsx", formula=None)
build("table-ref.xlsx", formula="SUM(Table1[[#This Row],[原価]])*1.3")
build("literal-1.xlsx", formula='CONCATENATE("[1]",B2)')
open("not-xlsx.xlsx", "w").write("name,price\nA,980\n")
print("built")

作業用の一時ディレクトリ($TMPDIR の下に xlsx-link を作りました)の中で、次のように作ります。

cd "$TMPDIR/xlsx-link"
python3 make_samples.py
built

1回目: externalLinksフォルダだけを見た

最初の読み方は単純でした。別ブックへの参照は xl/externalLinks/ に入ります。Microsoft LearnのExternalBookの説明にも、外部ブックを表す要素として載っています。ならばそのフォルダの有無を見ればいい、と考えました。

import sys
import zipfile

bad = 0
for path in sys.argv[1:]:
    with zipfile.ZipFile(path) as z:
        links = [n for n in z.namelist() if n.startswith("xl/externalLinks/") and n.endswith(".xml")]
    if links:
        print(f"NG {path}: 外部ブック参照 {len(links)} 件")
        bad += 1
    else:
        print(f"OK {path}")
sys.exit(1 if bad else 0)

これを check_v1.py として、1つずつ当てました。

for f in clean web-link linked file-link no-sheet; do python3 check_v1.py $f.xlsx; echo "exit=$?"; done
python3 check_v1.py not-xlsx.xlsx 2>&1 | tail -1; echo "exit=${PIPESTATUS[0]}"
OK clean.xlsx
exit=0
OK web-link.xlsx
exit=0
NG linked.xlsx: 外部ブック参照 1 件
exit=1
OK file-link.xlsx
exit=0
OK no-sheet.xlsx
exit=0
zipfile.BadZipFile: File is not a zip file
exit=1

止めたかった linked.xlsx は、止まりました。ところが file-link.xlsx は OK で、終了コードも0です。作った人のデスクトップのパスが、そのまま通っています。

externalLinks に入るのは、数式が参照するほかのブックです。セルのハイパーリンクはどうか。見本では、シートごとの .rels に書かれていました。「別のPCにあるファイルを指す」という意味では同じ種類の問題なのに、置き場所が違うので見落としました。

もう2点、気になりました。シートが0枚の no-sheet.xlsx も OK と答えます。中身がCSVで拡張子だけxlsxの not-xlsx.xlsx は、BadZipFile の例外で落ちます。この落ち方の終了コードが1で、NGと同じです。「問題が見つかった」のか「検査が壊れた」のか、呼ぶ側には区別がつきません。

2回目: すべての.relsと、数式の中の[1]を見る

見る場所を広げました。

  • ZIPの中のすべての .rels を読み、TargetMode="External" の宛先を調べる。http://、https://、mailto: 以外はローカルの参照として止める
  • シートの数式と、ブックの定義名に [1] のような [数字] があれば、別ブックを指す式として止める
  • 次のどれかなら、終了コード2で「検査できない」と返す。ZIPとして開けない。xl/workbook.xml がない。シートが0枚

https:// は通しています。販売ページや説明書へのリンクまで、ローカルの参照と同じ基準で止めると、通したい側も落ちるためです。

2回目の検査(check_v2.py、全文)
import re
import sys
import zipfile
import xml.etree.ElementTree as ET

REL = "{http://schemas.openxmlformats.org/package/2006/relationships}Relationship"
SHEET = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheet"
WEB = ("http://", "https://", "mailto:")


def inspect(path):
    """問題の一覧を返す。xlsxとして読めないときは ValueError。"""
    try:
        z = zipfile.ZipFile(path)
    except zipfile.BadZipFile:
        raise ValueError("ZIPとして開けない(xlsxではない)")
    with z:
        names = z.namelist()
        if "xl/workbook.xml" not in names:
            raise ValueError("xl/workbook.xml がない(xlsxではない)")
        if not list(ET.fromstring(z.read("xl/workbook.xml")).iter(SHEET)):
            raise ValueError("シートが0枚")
        problems = []
        for name in names:
            if name.endswith(".rels"):
                for r in ET.fromstring(z.read(name)).iter(REL):
                    target = r.get("Target", "")
                    if r.get("TargetMode") == "External" and not target.lower().startswith(WEB):
                        problems.append(f"{name}: ローカル参照 {target}")
            elif name.startswith("xl/worksheets/sheet") or name == "xl/workbook.xml":
                text = z.read(name).decode("utf-8")
                for m in re.findall(r"<(?:f|definedName)[^>]*>([^<]*)</", text):
                    if re.search(r"\[\d+\]", m):
                        problems.append(f"{name}: 外部ブックを指す式 {m}")
        return problems


rc = 0
for path in sys.argv[1:]:
    try:
        problems = inspect(path)
    except ValueError as e:
        print(f"ERROR {path}: {e}")
        rc = max(rc, 2)
        continue
    if problems:
        print(f"NG {path}")
        for p in problems:
            print("   ", p)
        rc = max(rc, 1)
    else:
        print(f"OK {path}")
sys.exit(rc)

同じファイルに当て直します。

for f in clean web-link linked file-link fixed no-sheet not-xlsx; do python3 check_v2.py $f.xlsx; echo "exit=$?"; done
OK clean.xlsx
exit=0
OK web-link.xlsx
exit=0
NG linked.xlsx
    xl/worksheets/sheet1.xml: 外部ブックを指す式 [1]原価!$B$2*1.3
    xl/externalLinks/_rels/externalLink1.xml.rels: ローカル参照 file:///C:\Users\hanako\Documents\原価管理.xlsx
exit=1
NG file-link.xlsx
    xl/worksheets/_rels/sheet1.xml.rels: ローカル参照 file:///C:/Users/hanako/Desktop/仕入れメモ.xlsx
exit=1
OK fixed.xlsx
exit=0
ERROR no-sheet.xlsx: シートが0枚
exit=2
ERROR not-xlsx.xlsx: ZIPとして開けない(xlsxではない)
exit=2

file-link.xlsx が止まり、linked.xlsx は式とパスの2か所が出るようになりました。シートが0枚のファイルと、xlsxではないファイルは、終了コード2で別扱いです。複数のファイルを一度に渡したときは、いちばん重い終了コードを返します。

python3 check_v2.py clean.xlsx linked.xlsx file-link.xlsx; echo "exit=$?"
OK clean.xlsx
NG linked.xlsx
    xl/worksheets/sheet1.xml: 外部ブックを指す式 [1]原価!$B$2*1.3
    xl/externalLinks/_rels/externalLink1.xml.rels: ローカル参照 file:///C:\Users\hanako\Documents\原価管理.xlsx
NG file-link.xlsx
    xl/worksheets/_rels/sheet1.xml.rels: ローカル参照 file:///C:/Users/hanako/Desktop/仕入れメモ.xlsx
exit=1

まだ誤検出するもの

[数字] を探す正規表現が、通常の式を巻き込まないかも確かめました。

for f in table-ref literal-1; do python3 check_v2.py $f.xlsx; echo "exit=$?"; done
OK table-ref.xlsx
exit=0
NG literal-1.xlsx
    xl/worksheets/sheet1.xml: 外部ブックを指す式 CONCATENATE("[1]",B2)
exit=1

テーブルの列を指す Table1[[#This Row],[原価]] は通りました。[1] が数字だけで閉じていないからです。一方、文字列として "[1]" を書いた式は誤って止まります。

直さずに残しました。この見本では、販売する価格表に文字列の "[1]" を書き込む式は、対象から外しています。止める側に倒れるので、購入者に害は出ません。ただ、手元のファイルはたった9つです。実際の商品を何十本も通したら、思わぬ式で止まるかもしれません。そのときの直し方は、式の文字列リテラルを読み飛ばす処理の追加です。

直しかたと、確かめていないこと

見つかったときの直しかたは、Microsoft Supportの「Manage workbook links」にあります。リンクの解除(Break links)を実行すると、その参照を使っていた式は、現在の値に置き換わります。ページには元に戻せないと書いてあるので、実行前にバックアップを保存します。

fixed.xlsx は、この操作の結果ではありません。数式の代わりに値の1274を最初から書いたファイルです。ExcelのBreak linksは、この見本では実行していません。

この検査が見ていないものも書いておきます。

  • Excel本体で開いたときの警告の出かた。作ったファイルはExcelが保存したものではありません
  • .xls、.xlsm、.xlsb。見たのは、ZIPの中にXMLが入る形式のxlsxだけです
  • グラフが参照するデータ、外部データ接続、ピボットテーブルの元データ
  • 前回扱った作成者欄などのプロパティ

踏んだつまずき

1つめは、1回目の検査が終わったとき、BadZipFile で落ちても終了コードがNGと同じ1だったことです。呼び出す側から見ると、問題を見つけたのか検査が壊れたのか区別がつきません。2回目で終了コード2に分けました。

2つめは、確認の仕方そのものです。最初に全ファイルを流したとき、python3 check_v1.py $f.xlsx 2>&1 | tail -1; echo "exit=$?" と書きました。$? は tail の終了コードなので、linked.xlsx を含めて全部が exit=0 と出ました。検査が通ったと読みかけて、止めたいファイルまで0なのはおかしいと気づきました。パイプを使うなら、${PIPESTATUS[0]} を見ます。

出典

この記事をシェア