手作業で保守される財務ワークブックには、ピボットキャッシュ、スライサー、条件付き書式、そして年月を重ねたセルスタイルが詰まっています。ところが夜間ジョブが差し替えたいのは、1枚のデータタブの数値だけです。フルの読み込みと保存は、すべてのパートをパースし、再構築し、再圧縮します。そのワークシートパートだけを書き直し、他のzipエントリをすべて無傷のまま持ち越せば、その仕事のほとんどを飛ばせます
これを可能にするのは、パッケージ形式そのものです。.xlsxファイルは、Open Packaging Conventions(ECMA-376 Part 2)に従って配置されたXMLパートのzipアーカイブであり、各zipエントリはローカルヘッダーとセントラルディレクトリの両方に、圧縮方式、CRC-32、圧縮後サイズを記録しています。したがって、この3つの値を生バイトと一緒に書き出すなら、エントリは展開されることなく、あるアーカイブから別のアーカイブへ移せます。ピボットキャッシュは、理解されなくても保持されます。コピーされさえすればいいのです
ワークブックを再保存する代わりに圧縮バイトをコピーする理由
圧縮レベルでのコピーは、パッチの大部分を単純なストリームコピーに変えます。HotXLSではTXLSDirectWriter.BeginPatchがソースパッケージを読み取り専用で開き、パッチが触らないエントリをすべてTZipArchive.CopyCompressedFromへ渡します。これはソースエントリの圧縮バイト、CRC、サイズをそのままターゲットアーカイブへ書き込みます。従来の書き込み経路は入り口でinflate、出口でdeflateを払い、そのうち高くつくのはdeflateの半分です。大半が大きなピボットキャッシュと数十枚の埋め込み画像であるワークブックでは、時間は実際に書いているシートへ向かいます
生コピーの対象は、非圧縮で保存されたエントリ(zip方式0)とdeflateされたエントリ(方式8)です。それ以外の圧縮方式を使うエントリも運ばれます。ライターは解凍ストリームを通じて読み、書き戻すため、パートはいずれにしてもターゲットに着地します。ディレクトリマーカーのエントリは内容を持たないためスキップされます。ライターが独自のものを生成するからです。決してコピーされないエントリがいくつかあります。再生成されるか編集されるものだからです。ターゲットのワークシートとその_relsパート、xl/calcChain.xml、xl/_rels/workbook.xml.rels、[Content_Types].xml、そしてシートを挿入するときのxl/workbook.xmlです
BeginPatchで1枚のシートをその場で置き換える
2引数のオーバーロードBeginPatch(FileName, SheetName)は既存のワークシートを置き換え、結果を同じパスへ書き戻します。xl/workbook.xmlのシートレジストリと、各シートをパートに結ぶリレーションシップマップを通じて名前を解決し、大文字小文字を区別せずに比較し、何も一致しなければ「worksheet not found」を含む例外を投げます。そこから先は、ストリーミングライターが新規ファイルに使うのと同じAddSheet、AddRow、Write*呼び出しでシートを組み立てます
var
W: TXLSDirectWriter;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch('monthly-dashboard.xlsx', 'Data'); // その場で置き換える
W.AddSheet('Data'); // パッチモードでは名前は情報提供にすぎない
W.AddRow(1);
W.WriteString(1, 'Region');
W.WriteString(2, 'Revenue');
W.AddRow(2);
W.WriteString(1, 'North');
W.WriteNumber(2, 184320.55);
W.AddRow(3);
W.WriteString(1, 'South');
W.WriteNumber(2, 97410.20);
W.AddRow(4);
W.WriteString(1, 'Total');
W.WriteFormula(2, 'SUM(B2:B3)'); // 先頭の=は付けない
W.Close;
finally
W.Free;
end;
end;
このリストには読み違えやすい細部が2つあります。AddSheetに渡す名前は何も改名しません。タブはxl/workbook.xmlで既に持っている名前、sheetId、位置を保つため、AddSheet('Totals')でも書き込み先はDataのままです。そして2回目のAddSheetは例外を投げます。1つのパッチが書くワークシートはちょうど1枚だからです。3枚のタブを更新するなら3回のパッチであり、それぞれがパッケージをもう一度コピーします
数式は等号なしで入ります。WriteFormulaはテキストをセルの<f>要素に直接置き、ECMA-376 Part 1 §18.3.1.40は数式をExcelが数式バーに見せる「=」なしでそこへ保存します。'=SUM(B2:B3)'と書くと、リテラルの等号がXMLに入ります。WriteFormulaはキャッシュ結果も出力しません。下流が再計算ではなくキャッシュ値を読むなら、WriteFormulaNumberが、評価結果として既知の数値と一緒に数式を書き込みます
インプレースのオーバーロードは、作業中に元のファイルへ書き込みません。パッチ済みパッケージは、ソースのファイル名に.hxlpatch.tmpを付けた兄弟ファイルに組み立てられます。Closeでソースのハンドルを解放し、ステージングファイルが1回のリネームで元のファイルを置き換えます。そのリネームが失敗した場合、Closeは両方のパスを名指す例外を投げ、ステージングコピーを保持し、元のファイルをその場所に残します。Closeの前にプロセスがkillされた場合は、元のファイルは無傷のまま、脇に迷子の.hxlpatch.tmpが残ります。次にそのファイルでBeginPatchを呼ぶと、開始前にこれを削除します
テンプレートのコピーに新しいワークシートを追加する
4引数のオーバーロードBeginPatch(SourceFileName, TargetFileName, SheetName, InsertSheet)はソースファイルに触れず、新しいパッケージを書きます。InsertSheet = Falseならコピー内の名前付きシートを置き換え、Trueならその名前で新しいワークシートを追加し、テンプレートに同名のタブが既にあれば「worksheet already exists」を投げます
// template.xlsxは無傷。output.xlsxにタブが1つ増える
W.BeginPatch('template.xlsx', 'output.xlsx', 'Extra', True);
W.AddSheet('Extra');
W.AddRow(1);
W.WriteString(1, 'Appended by the nightly job');
W.Close;
挿入は、パッケージの帳簿を編集する部分です。ライターは既存のxl/worksheets/sheetN.xmlエントリから最大のパート番号を、<sheet>要素から最大のsheetIdを、ワークブックのリレーションシップから最大のrIdを探し、それぞれの次の値を採ります。新しい<sheet>要素は</sheets>の直前に入るため、新しいタブは最後尾に現れます。リレーションシップ属性は、ソースのワークブックがシートに使っているプレフィックスをそのまま使い、通常はr:idですが、そうでなければならないわけではありません
リレーションシップ型もソースパッケージに従います。ワークブックのリレーションシップがすでにpurl.oclc.org配下のISO/IEC 29500 Strict名前空間を使っている場合、新しいワークシートのリレーションシップはStrictの型URIで書かれ、TransitionalのワークブックにはTransitionalのURIが付きます。したがって、パッチされたStrictワークブックの中に、1個だけTransitionalのリレーションシップが混ざるようなことはありません
スタイル、文字列、そしてパッチが触れないパート
パッチの内側では、新しいシートが指すものはすべて、ソースパッケージに既に存在していなければなりません。したがって文字列はxl/sharedStrings.xmlへではなく、シートにインラインで書き込まれます。共有文字列テーブルはバイト単位で持ち越されるため、育てられないからです。UseSharedStringsをTrueにすると、パッチモードではAddSheetが例外を投げます
同じ規則がStyleIndexの意味も変えます。パッチでは、これはソースワークブックのcellXfsリスト(ECMA-376 Part 1 §18.8.10)内の位置であり、ライターは0から65534の間にあることしか確認しません。その特定のファイルにインデックス14が存在するかまでは分からないため、間違った番号はそのまま出力に入ります。正しい番号を見つける最も確実な方法は、現行バージョンのシートをzip展開し、望む書式を既に載せているセルのs属性を読むことです。その番号がcellXfsの位置です。テンプレートで誰かがセルに書式を付けると、Excelはこのリストに項目を追加できるため、1年前に書いた定数を信用するのではなく、テンプレートを編集するたびにインデックスを調べ直してください
const
// このテンプレートでの日付書式のcellXfs位置。既存の日付セルの
// s="..."属性から取ったもの。テンプレート編集後は再確認する
TemplateDateXf = 5;
begin
...
W.AddRow(5);
W.WriteString(1, 'Refreshed');
W.WriteDateTime(2, Now, TemplateDateXf); // 明示インデックス:受け付けられる
// W.WriteDateTime(2, Now); はパッチモードで例外:暗黙の日付スタイルは
// パッチが書かないstylesパートの中にある
end;
新しいパッケージパートを追加することになるものは拒否されます。AddTable、AddChart、AddImageファミリー、AddComment、AddDefinedName、AddCellStyleは呼ばれた瞬間に例外を投げ、Closeはコミットの前にもう一度確認します。これにより、独自の塗りつぶし、フォント色、太字、斜体のスタイルを設定する条件付き書式も捕捉されます。それらはstyles.xmlに新しい項目を必要とするからです。誰も登録していないテーブルパートをシートが参照するパッケージより、拒否された呼び出しのほうがましです
置き換えモードでは、シートは無からのスタートになります。旧シートのリレーションシップパートはコピーされないため、前のバージョンに付いていた描画、コメント、外部ハイパーリンクは新しいシートには付かず、旧シートXMLに保存されていた列幅、フリーズペイン、結合範囲、データ入力規則も消えます。シートXMLに住んでいるものは、SetColumnWidth、FreezePanes、MergeCells、AddDataValidation、AddHyperlinkで書き直せます。データタブにアンカーされたチャートや画像はパッチでは戻りません。チャートは専用のシートに置き、その系列をデータタブへ向けてください
計算チェーンはどちらのモードでも落とされます。xl/calcChain.xmlは、Excelが最後に評価した順に数式セルを列挙したものです。置き換えの後では、その項目はその形ではもう存在しないセルを記述し、挿入の後では、それが参照するシートインデックスはずれています。このパートはコピーから外され、そのリレーションシップと[Content_Types].xml内のOverrideも削除されるため、パッケージが存在しないパートを参照することはありません。Excelは次の再計算でチェーンを組み立て直します
行ループが途中で例外を投げたとき
TXLSDirectWriterのデストラクタは、Closeが呼ばれなかったときにパッケージを仕上げます。そのため、Close忘れが半分書きかけのzipを放置することはありません。ライターが保持しているものは何でも仕上げるため、インプレースのオーバーロードで例外がFreeを通って巻き戻っても、そこまでに書かれた行はコミットされ、元のファイルと入れ替わります。行を取得して書くループが途中で例外を投げ、finallyブロックでライターを解放するという流れは、まさにそうなります
無人で走るものには、明示的なターゲットに書き、結果を開いて確認し、それから自分で実ファイルを置き換える方式を選んでください。行ループからの例外でもFreeはステージングファイルを仕上げますが、入れ替えは行われないため、実ワークブックは今日の数値の半分ではなく、昨日の数値を保ちます
uses
Windows, SysUtils, Variants, lxDirectWrite, lxHandleX;
procedure RefreshDashboard;
const
Live = 'monthly-dashboard.xlsx';
Staged = 'monthly-dashboard.next.xlsx';
var
W: TXLSDirectWriter;
Check: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch(Live, Staged, 'Data', False);
W.AddSheet('Data');
WriteRefreshedRows(W); // 独自の行ループで、HotXLSの一部ではない
W.Close;
finally
W.Free;
end;
Check := TXLSXWorkbook.Create;
try
if Check.Open(Staged) <> 1 then
raise Exception.Create('Staged workbook does not open');
Sheet := Check.Sheets.SheetByName('Data');
if (Sheet = nil) or (VarToStr(Sheet.Cells[2, 1].Value) <> 'North') then
raise Exception.Create('Data sheet did not come back as written');
finally
Check.Free;
end;
// Windows APIでHotXLSではない:検査が通ってから実ファイルを置き換える
if not MoveFileEx(PChar(Staged), PChar(Live), MOVEFILE_REPLACE_EXISTING) then
RaiseLastOSError;
end;
TXLSXWorkbook.Openは成功すると1を返し、SheetsもCellsも1始まりです。Check.Sheets[1]が最初のタブで、Cells[2, 1]はA2です。スポット値の確認を超えると、2つの構造検査がほとんどの誤りを捉えます。Check.Sheets.Countは、置き換えの後ではテンプレートの数と等しく、挿入の後では1つ多いはずです。そしてどんなzipツールでアーカイブを一覧しても、xl/calcChain.xmlがなく、同じ名前のエントリが二重に出ないはずです。重複名が問題になるのは、与えられた名前の最後のエントリを採用するリーダーもいるためで、2枚のシートが中身を入れ替えたように見えます
パッチか、ストリームか、ワークブック読み込みか
パッチが合うのは、ワークブックが大きく、変更が1枚のシートに限られ、ファイルの残りがビット単位で生き延びなければならないときです。変更が複数シートにまたがるとき、新しいデータがテンプレートにまだ定義されていない書式を必要とするとき、チャートやテーブルを作成しなければならないとき、そしてファイルが小さくて通常の読み込みと保存に計測に値するコストがないときには、間違った道具です。ゼロからの一括生成にはストリーミング直接ライターのほうが合っており、同じAddRowとWrite*呼び出しを共有しているため、両者の間のコード移動は、BeginFileをBeginPatchに取り替えるのがほとんどです
本当に対象モデル全体が必要なジョブでは、読み込み済みワークブック内のシートレベルの作業はXLSXパッケージ内のワークシート複製が扱います。そしてパッチが選択肢に上がった理由がワークブック全体処理の遅化だけなら、手法を決める前に大規模ワークブックのパフォーマンスが、読み込みと保存の時間とメモリが実際どこへ行くかを見ています
パッチライター、ストリーミングライター、そして完全なTXLSXWorkbookオブジェクトモデルは、DelphiとC++Builder向けの1つのライブラリに揃っています。エディションと完全な機能一覧はHotXLS Delphiスプレッドシートコンポーネントのページにあります