HotXLS Delphi Component: Delphi での workbook audit and conversion workbench

大量のスプレッドシート正規化ジョブは、1 枚のコートを着た 3 つの問題です。混在フォーマットのアーカイブがあります。BIFF 時代の .xls、現代の .xlsx、どこかの LibreOffice の実験から出てきた散らばった .ods、そして退職した元従業員と一緒にパスワードが持ち去られてしまったせいで誰も開けないファイルが何個か。目標はすべてを XLSX と CSV に変換することです。ほとんどの人が書くこの種のジョブのバージョンは、各ファイルを開いて新しい拡張子で保存するループであり、誰かが「どのファイルがチャートを失ったか」「どのファイルがマクロを落としたか」「そもそも開けなかったファイルはどれか」と尋ねてくるまでは、それでうまくいきます。ループには答えがありません。変換だけでは記録が一切残らないからです。ワークベンチは違います。まず棚卸しし、次に変換し、最後に検証します。この 3 つの段階が情報を共有して初めて、それは信頼できるものになります

Delphi または C++Builder でそのワークベンチを組み立てるということは、4 つの HotXLS の機能を配線でつなぐことを意味し、そのどれもパイプラインのどこにも Excel をインストールしている必要はありません。2 つのネイティブエンジンがあります。.xls 向けの BIFF8 ファサードと、.xlsx および .ods 向けの OOXML ファサードです。ファイル全体をパースすることなくメタデータを読む、安価な探索呼び出しがあります。ワークブックが実際に何を保持しているかを教えてくれる、シート単位の監査カウンターがあります。そして、各経路について文書化された忠実度プロファイルを持つ変換マトリクスがあります。作業の本質は、それぞれのどこに鋭いエッジがあるかを知ることにあります。どれもすべてエッジを持っており、そのエッジこそが、きれいな夜間バッチを月曜の朝のインシデントに変えてしまうものだからです

読み込む前に探る: シート名と暗号化の検出

200 MB のワークブックを開いてみて初めて暗号化されていることに気づくのは、1 ファイルあたり数分の無駄であり、大きなアーカイブ全体では数日分の無駄になります。両方のファサードは GetSheetNames を公開しており、これはワークブックを populate することなくシートのメタデータを読みます。BIFF の実装はストリームの先頭にある BoundSheet レコードだけを走査し、OOXML の実装は zip の中の workbook.xml だけを読みます。それに加えて、CanReadEncrypted は復号を試みることなく暗号化コンテナを検出します

var
  Probe: TXLSXWorkbook;
  Names: TStringList;
begin
  Names := TStringList.Create;
  Probe := TXLSXWorkbook.Create;
  try
    if Probe.CanReadEncrypted(FileName) then
    begin
      Writeln(FileName + ': encrypted container - route to manual handling');
      Exit;
    end;
    if Probe.GetSheetNames(FileName, Names) <= 0 then
      Writeln(FileName + ': unreadable - quarantine')
    else
      Writeln(Format('%s: %d sheet(s), first "%s"',
        [FileName, Names.Count, Names[0]]));
  finally
    Probe.Free;
    Names.Free;
  end;
end;

この 2 つの運用上の細部が、このループを安価にしてくれます。GetSheetNames はワークブックインスタンスをリセットしたり populate したりしないため、単一のプローブオブジェクトを再作成することなく数千のファイルを分類できます。そして、同じ呼び出しの XLS ファサード版は .xlsx パッケージも理解するため、拡張子が信頼できない場合(それだけ古いアーカイブではめったに信頼できませんが)に便利な単一のプローブになります。ロード前のトリアージはそれ自体で扱う価値があります。軽量な検査の仕組みは、シート一覧と軽量なワークブック検査に関する記事にあります

ワークブックが実際に何を含んでいるかを数える

ファイルがトリアージを通過すると、監査パスがその変換経路を決めます。XLSX ファサードは、忠実度の判断に関わるすべての機能ファミリー、結合セル、チャート、画像、条件付き書式、データの入力規則、テーブル、ハイパーリンク、コメントに対するカウンターに加え、マクロ、保護、ソースフォーマットに関するワークブックレベルのフラグも公開しています。ファイルの変換経路は、これらのうちどれが 0 以外の値で返ってくるかにほぼ完全に依存します

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then Exit;
    for I := 0 to Book.Sheets.Count - 1 do
    begin
      Sheet := Book.Sheets[I];
      Writeln(Format('%s: cells=%d merges=%d charts=%d cf=%d dv=%d protected=%s',
        [Sheet.Name, Sheet.Cells.Count, Sheet.MergedCells.Count,
         Sheet.Charts.Count, Sheet.ConditionalFormats.Count,
         Sheet.DataValidations.Count, BoolToStr(Sheet.IsProtected, True)]));
    end;
    if Book.HasVbaProject then
      Writeln('  contains VBA project - macro policy applies');
    if Book.ExternalLinks.Count > 0 then
      Writeln(Format('  %d external link(s)', [Book.ExternalLinks.Count]));
  finally
    Book.Free;
  end;
end;

Cells.Count は 1 つの注意点を念頭に置いて読んでください。セルストアは疎であるため、この数は使用範囲の矩形面積ではなく、実体化されたセルの数を数えています。A1 に 1 つの値、ZZ9999 にもう 1 つの値を持つシートは、その間にある 100万個近いセルではなく、2 個のセルを報告します。BIFF 側での同等のスキャンは UsedRange の境界と ForEachCell を組み合わせて使用し、ほぼ誰もが最初に一度はつまずくオフバイワンを抱えています。UsedRange.FirstRow とそのきょうだいは 0 始まりですが、Cells.Item[Row, Col] は 1 始まりです。それぞれの境界に 1 を足すのを忘れたトラバーサルは、間違った矩形を監査し、それについて何も語りません

大きなレガシーファイルに対する監査専用パスのコストを削る 2 つのレバーがあります。.xls を開く前に _DisableGraphics を true に設定すると、OfficeArt の描画レイヤーのパースを完全にスキップし、図形が密集したワークブックで実際の時間を節約できます。ただしこれは厳密に読み取り専用の最適化です。その方法で開いたインスタンスから保存すると、一度もパースしなかった描画が落ちてしまうため、このフラグはファイルを書き戻すことが決してない経路にのみ属します。監査がカウントではなくセル単位の内容を必要とするときは、ForEachCell コールバックが実体化されたセルを直接歩き、インデックス付きのセルプロパティが読み取りのたびに支払う、アクセスごとの Variant オーバーヘッドを回避します。これは数百万セルにわたると急速に積み上がっていくものです

不揃いな戻りコードを早いうちに正規化する

HotXLS の I/O 呼び出しは例外ではなく整数の結果でエラーを報告し、その慣習は API 全体で統一されていません。ほとんどの open と save の呼び出しは、成功時に 1、失敗時に -1 を返します。GetSheetNames はシート数を返すか、リストがクリアされた状態で -1 を返します。XLSX の SaveAsHTML は再びそのパターンを破り、成功時に 0、シートインデックスが範囲外のときに -1 を返します。あらゆる場所で = 1 をテストするワークベンチは、別の方法で成功を知らせる呼び出しを静かに誤って分類してしまい、<> -1 をテストするワークベンチは、別のコードで失敗する呼び出しを飲み込んでしまいます

API 全体との接触を生き延びるルールは、見た目より狭いものです。件数を返す呼び出しに対しては <= 0 を失敗として扱い、実際に使う保存ルーチンそれぞれについて文書化された成功値を確認し、その両方を 1 つの小さな結果チェック関数の背後に置いて、慣習がちょうど 1 か所にだけ生きるようにしてください。バッチパイプラインは、奇抜なパーサーのバグよりも、チェックされていない戻りコードのじわじわとした積み重ねによって失敗することのほうがはるかに多く、これを間違えたコストは、4万ファイル後になって、実際にどの変換が成功していたのか誰も覚えていない、という形で現れます

変換マトリクスと、それぞれの経路がどこでデータを失うか

この 2 つのファサードは変換作業を分担しています。TXLSXWorkbook は XLSX、ODS、CSV を開き、XLSX、ODS、CSV、HTML、RTF、AES 暗号化された XLSX を保存します。TXLSWorkbook は BIFF を開いて保存し、HTML、RTF、CSV をエクスポートします。有用なのは、それぞれの経路が、正しさに関する曖昧な約束ではなく、文書化された忠実度プロファイルを伴っている点です。そのため、どの経路がどのファイルにとって安全かを事前に決めることができます

CSV エクスポートは、BOM 付きの UTF-8、CRLF の改行、RFC 4180 の引用ルールで書き込みます。行わないのは数式の評価です。=SUM(...) を保持するセルは、そのままの数式テキストとしてエクスポートされるため、値を先に計算しない限り、数式のシートは文字列のシートになってしまいます。HTML エクスポートは 1 つのテーブルを生成し、colspan と rowspan が結合セルの代わりとなり、基本スタイルはインラインで埋め込まれます。RTF エクスポートにはより厳しい制限があります。結合セルを列方向にスパンさせることができないため、結合の継続セルは空として出力されます。ODS のインポートは、ライブラリ自身のドキュメントによれば意図的に軽量です。スカラー値とキャッシュされた数式の結果は引き継がれますが、スタイル、生きた ODF の数式表現、描画は引き継がれません。これは、アーカイブが OASIS ODF 1.3 に準拠した本物の OpenDocument ファイルを含んでいる瞬間に問題になります。視覚的に忠実な変換に近いものが必要な場合、このインポートパスが運ぶように作られている以上のものが必要になるからです。そして、そうしたファイルが存在することをバッチが静かにそれらをフラット化してしまう前に教えてくれるのが、監査パスです

SaveXLSWorkbookAsXLSX はデータの橋であって、レイアウトの橋ではない

BIFF ファサードは OOXML を直接書き込めないため、.xls から .xlsx への横断は lxXlsxExport ユニットの SaveXLSWorkbookAsXLSX 関数を経由します。この橋の忠実度は、はっきりと述べておく価値があります。名前が示唆する以上のことをしていると思われがちだからです。それは値、数式、数値書式、塗りつぶしの色、コアなフォント属性、列幅、そして枠線の表示などのビュー設定をコピーします。罫線、結合範囲、コメント、チャート、条件付き書式はコピーしません。下流のシステムが結果をパースするだけで、誰も書式を見ないようなデータグレードの正規化にとっては、それでちょうど十分であり、誰かが必要とするものが失われることはありません。人が読むことを意図した書式付きの役員報告書にとっては、それでは不十分であり、まさにここで監査カウンターがその存在価値を発揮します。監査でチャートと条件付き書式を保持しているとフラグが立てられたファイルは、両方を何も告げずに落としてしまう橋を通すのではなく、手動キューへルーティングすべきです

var
  Legacy: IXLSWorkbook;        // インターフェース参照: Free してはいけない
  Modern: TXLSXWorkbook;
begin
  if SameText(ExtractFileExt(FileName), '.xls') then
  begin
    Legacy := TXLSWorkbook.Create;
    if Legacy.Open(FileName) <= 0 then Exit;
    if SaveXLSWorkbookAsXLSX(Legacy,
         ChangeFileExt(FileName, '.xlsx')) <= 0 then
      Writeln('bridge failed: ' + FileName);
  end
  else
  begin
    Modern := TXLSXWorkbook.Create;
    try
      Modern.StreamingWrite := True;     // シート XML を zip へストリーミングする
      if Modern.Open(FileName) = 1 then
        Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
    finally
      Modern.Free;
    end;
  end;
end;

上のループは、OOXML 側のスループットレバーも示しています。StreamingWrite を true に設定すると、ワークシート XML を、メモリ上で 1 つの巨大な文字列としてステージングするのではなく、出力パッケージへ直接ストリーミングします。これは、ファイルの行数が数十万に達したときに、快適な実行とメモリ不足のクラッシュの違いを生みます。そのモードのサイジングとメモリの挙動については、サーバーバッチジョブ向けストリーミング書き込みの記事で個別に扱っています。すべてのコアを使いたいバッチにとってもう1つ重要な性質があります。どちらのファサードもスレッドセーフではありませんが、どちらもグローバルな状態を共有しないため、並列変換でサポートされるパターンは、ワーカースレッドごとに 1 つのワークブックインスタンスを持ち、それらの間に一切のロックを置かないというものです

パスワード付きファイルと、それらへの対処法

アーカイブのロックされたファイルはフォーマットによってきれいに分かれており、その分かれ方が行き先を決めます。レガシーな .xls の暗号化は、RC4 であれ、CryptoAPI 経由の RC4 であれ、古い XOR 難読化であれ、読み取り可能です。パスワードを Open に渡せば、そのファイルは他のファイルと同じように変換されます。暗号化された .xlsx パッケージは事情が異なります。HotXLS は CanReadEncrypted でそれらを検出できますが復号はできないため、唯一の正直な対処法は、パイプラインに戻る前に人間が Excel で一つ一つ開いて再保存するキューへルーティングすることです。この非対称性は事前に設計しておく価値があります。暗号化された XLSX ファイルこそが、誰かが実際に気にかけている記録である可能性が最も高いファイルだからです

検証でループを閉じる

3 番目の段階は省略されがちな段階であり、それを省くことこそが、大量変換を負債に変えてしまう原因です。HotXLS のどの保存経路も数式を評価しません。Excel はファイルを開くときに再計算するため、XLSX から XLSX への変換は正しいままですが、CSV 出力は、パイプラインが先にセルに対して Calculate を実行し、結果を書き戻さない限り、数式のテキストをそのまま受け取ります。それを事前に知っていることが、数字で満たされた CSV と、下流のインポートがそれらに詰まるまで誰も気づかない =SUM(...) という文字列で満たされた CSV との違いになります

検証そのものは、省く言い訳が立たないほど安価です。変換されたすべてのファイルを同じライブラリで再度開き、監査カウンターを再度実行し、それらを、棚卸しパスがすでに記録していた変換前の数値と比較してください。減ったシート数、ソースが 3 つ持っていたのに 0 になったチャート数、崖から落ちたようなセル数、それぞれが、2 回目のオープンのコストだけで捕まえられる静かな損失です。それに加えて Excel や LibreOffice で目視によるサンプルチェックを行えば、この組み合わせは変換による損傷の圧倒的多数を出荷前に捕捉します。これこそが、棚卸し段階が検証段階に情報を供給する理由のすべてです。事前の数値がなければ、事後の数値は何も証明しません

監査を最初に行うワークベンチは、危険な大量変換を、きれいに通過できないファイル向けの検疫レーンを備えた、測定可能なプロセスへと変えます。ここで示したすべての探索、カウント、変換の呼び出しは、HotXLS Delphi Component の一部であり、Excel の自動化なしにネイティブにインプロセスで実行されます