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]} を見ます。


