LlosLabソフトウェア開発ブログPDF、Delphi、およびドキュメントソフトウェア開発者向けのエンジニアリングノート。

技術記事

Delphiにおける HotXLS のワークブック間コピーと数式再バインド

HotXLS Component

HotXLSのAddCopyメソッドは、あるExcelワークブックのワークシートを別のワークブックへコピーする際、コンパイル済みの数式ツリーを直接コピーするのではなく、そのシート上のすべての数式をA1形式のテキストへ逆コンパイルし、そのテキストを目的地ワークブックの内部で再コンパイルする。なぜならチャートの系列参照、リッチテキストのフォントインデックス、外部リンクの番号付けは、すべて各ワークブックファイル内部で独立に割り当てられているからだ

この失敗はまさに想像通りのワークブックで現れる:各支店事務所のレポートから1枚のシートを引き抜き、サマリーファイルに追加する月末のジョブである。結果を開くと、小計チャートは全く別の支店の数字をプロットしており、ソースでは太字で赤だったメモは平坦な黒テキストに戻っており、かつては連携先のルックアップワークブックから税率を引いていた数式は、今や誰も説明できない凍り付いた数値を表示している。ここでは何も例外を投げない——ファイルは開き、数値はもっともらしく見え、誰かが隣に間違ったタイトルのチャートがあることに気づくまでその損傷はそこに座り続ける

なぜAddCopyはコンパイル済み数式ツリーを単純にコピーできないのか

AddCopyがコンパイル済みの数式ツリーを変更せずに移動できないのは、コンパイル済みのBIFF数式が独立したテキストではないからである——それはトークンの並びであり、そのトークンのいくつかは、それを生成したワークブックの内部でしか正しく解決されない小さな整数である。Sheet2!A1:A10のような3D参照は、コンパイルされるとSheet2というリテラルな名前は運ばない。それはBIFF仕様がixtiと呼ぶフィールド(HotXLSは自身のコンパイル済みツリー内で同じ値をFExternIDというフィールド名で保持している)を運ぶ。これは、そのワークブック固有のEXTERNSHEETテーブルへのインデックスであり、そのワークブックがたまたま自身のシートと外部ブックを登録した順序で番号付けされている。そのトークンを、異なる順序で構築されたEXTERNSHEETテーブルを持つワークブックへ変更せずに移動すると、インデックス3はもはやSheet2を意味しなくなる——それはあちら側のスロット3にたまたま位置しているどのシートでも意味することになり、Excelにはその間違いにフラグを立てる方法がない、なぜならファイル形式の観点からは、その数式は完全に整形式だからだ。これはまさにTXLSWorksheets.AddCopyが回避するために存在する失敗である:DelphiまたはC++Builderのコードでどちらかのワークブック自身のシートコレクションから呼び出され、それはあなたがそれを呼び出しているワークブックであってもなくてもよいソースワークブックから、セル値・書式・数式・チャート・コメント・結合・ページ設定などを含むワークシートをコピーし、その結果を、あなたが選んだ名前か元の名前を曖昧さなくした複製として目的地に追加する

var
  Summary, Branch: IXLSWorkbook;   // interface-counted: do not Free
begin
  Summary := TXLSWorkbook.Create;
  Branch := TXLSWorkbook.Create;
  Branch.Open('branch-east.xls');

  // Appends a copy of Branch's first sheet onto Summary, renamed to
  // stay unique inside the destination workbook
  Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
  Summary.SaveAs('consolidated.xls');
end;

その解決策:テキストへ逆コンパイルし、目的地で再コンパイルする

HotXLSは、コンパイル済みのツリー自体が決してワークブックの境界を越えないようにすることでこのインデックス問題を解決する。ワークブック間コピーにおけるすべての数式セルについて、AddCopyはソースの数式を、ユーザーがExcelの数式バーで見るのと同じA1形式のテキストへ逆コンパイルし、そのテキストを目的地ワークブックに渡す。目的地ワークブックはそれをゼロから自身のテーブルを使ってツリーへと解析し直す——Data!D2:D100のようなシート修飾付きの参照は、その時点では単なる文字列であり、文字列はどのワークブックでも同じことを意味する。そのため目的地にすでにDataという名前のシートがあれば、その参照はインデックス変換なしに正しく解決される。なぜなら変換すべき生のインデックスがそもそも一切飛び交っていなかったからだ。HotXLSはこの往復のコストを必要なときにだけ払う:同じワークブック内でシートをコピーすることは、コンパイル済みツリーが単純にメモリ上で複製されるより安価な経路をたどる。なぜならその内部のすべてのインデックスは、それが留まる場所ですでに有効だからだ。テキストによる迂回路は、AddCopyがソースと目的地が本当に異なるワークブックのインスタンスであると検出したときにだけ実行される。この書き換えが何ではないかについても正確を期す価値がある。それは、単一シート内で行を挿入・削除するときに実行される行・列のシフトとは何の関係もない。これは関連記事で詳しく扱われている——そのエンジンは、1つのワークブック内で数行上下に動いたセルを追跡するためにA1テキストをその場で書き換えるが、こちらは数式がそれをコンパイルしたワークブックを完全に離れるときに実行され、そこでは動いた行が問題なのではなくワークブック固有の番号付けが問題なのである

// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);

目的地にまだそのシート、あるいはその名前が存在しない場合はどうなるのか

AddCopyの再コンパイルが成功するのは、目的地ワークブックがすでに数式テキストが参照するすべてを持っている場合だけであり、実際に現れる2つのギャップは、このバッチでまだコピーされていない同名のシートと、目的地に一度も存在したことのないワークブックスコープの定義済み名前である。HotXLSは、シートコピーの途中で再コンパイルが失敗しても例外を発生させない——セルのValue代入は代わりに静かにその数式テキストを単なる文字列として保存する。これは静かな失敗モードではなく意図的で検査可能な失敗モードである。なぜなら、計算された数値ではなく=SUM(Q1!B2:B12)のようなリテラルなテキストを予期せず表示する数式セルは、コピーの上流で何かが解決されなかったことの手がかりだからだ。あきらめる前に、AddCopyは一つの修復を試みる:失敗した数式の構文木を歩き、その数式が触れるすべての定義済み名前のIDを収集し、ソースには存在するが目的地にはまだ存在しないワークブックスコープの各名前について、その名前を向こう側へコピーし、同じテキストをもう一度再コンパイルする。シートスコープの名前は、この修復が直せる範囲の外にある。なぜなら、ソースワークブックの1シート上の数式にしか見えない名前には、移行先となる同等のスロットが存在しないからだ。そして、すでに同じ綴りの名前を持っている目的地は、上書きされるのではなく手つかずのまま残される。これは、呼び出し元が意図的に事前作成した名前が尊重してほしいものだという前提に基づく。単一のワークブック内では、シート間数式の名前検索はシートスコープからワークブックスコープへ自動的に昇っていく。これはHotXLSの定義済み名前とシート間数式の記事で扱われている仕組みである。実際のワークブック境界を越えることはそのセーフティネットを完全に取り除き、名前は意図的に運ばれなければならない、さもなければそれに依存する数式はテキストへと劣化する

チャート系列参照も同じ修正が必要だが、経路は異なる

セル範囲をプロットするHotXLSのチャート系列は、通常のセル数式と全く同じ番号付けの問題にぶつかる。なぜならチャートのデータ範囲参照もコンパイル済み数式トークンストリームだからだ——BIFF仕様はそれを運ぶレコードをBRAIと呼ぶ([MS-XLS] 2.4.51節)——しかしAddCopyは通常のチャート読み込み経路を再利用してそれを修正することはできない。なぜならその経路こそがまさにそのバグを生み出すものだからだ。ファイルをオープンする通常の過程でチャートレコードがディスクから解析されるとき、その数式ツリーは、解析を行っている何らかの計算機インスタンスを通じて生のバイトを変換することで構築される。代わりにソースチャートの生のBRAIバイトを目的地ワークブック自身の通常のレコードローダーに通すと、そのバイト列に埋め込まれたixtiは目的地のEXTERNSHEETテーブルに対して解決され、その系列は静かにあちら側のそのスロットを占めるどんなシートでも指すことになる——これはセルのコンパイル済みツリーを変更せずにコピーするのと同じ種類の間違いであり、ただ誰もセルの数式を読むようにはチャートの系列数式を読まないため気づきにくいだけである。HotXLSは代わりに専用のクローン経路でこの罠を回避する:TXLSCustomChart.AssignFromは各チャートレコード自身の数式以外のヘッダーバイトをそのままコピーし、その後、通常のセルに使われるのと同じ逆コンパイル・再コンパイルのプリミティブを通じて添付された範囲を再構築する。そのため新しいツリーは、事後にそれに対して再解釈されるのではなく、目的地のEXTERNSHEETテーブルに対してゼロから構築される

同じ番号付けの問題、フォントインデックスを1つずつ

チャートやリッチテキストセルの内部にあるワークブックローカルな数値のすべてが数式というわけではなく、フォントインデックスは同じ種類の問題の縮小版である。リッチテキストのラン、そしてキャプションや軸のフォントを運ぶさらに2種類のチャートレコードタイプは、フォント参照を所有ワークブック自身のフォントテーブルへの生の整数インデックスとして保存しており、そのインデックスは別のワークブックのテーブルでは何も意味しない——それはあちら側では全く異なる書体・サイズ・色を指しているかもしれない。HotXLSはこれを番号ではなく値によって解決する:ソーステーブルのそのインデックスにある実際のフォント属性を検索し、目的地のフォントテーブル内で一致するエントリを見つけるか作成し、保存されているインデックスをその新しいスロットを指すように書き換える。1つのフォーマットの癖がこのルックアップ自体を厄介にする——ファイル上のインデックスはスロット4をスキップする。これは[MS-XLS] 2.5.339節が文書化している番号の隙間であり、そのためコードはフォントを比較する前にインデックスを1つ下にシフトし、結果を書き込む前に1つ上に戻さなければならない

// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
  Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
  Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
  Inc(Ifnt);

すでにワークブックの外を指している数式はどうなるのか

AddCopyを呼ぶ前にすでに3つ目のワークブックへ到達している数式は、テキストの往復では運べない唯一のケースである。なぜならHotXLS自身の数式・テキスト逆コンパイラは、外部参照のために[Book]Sheet!という角括弧付きのテキストを意図的に合成せず、反対側のコンパイラもその構文を入力として受け付けないからだ——そのためこの一つのケースは、テキストに一切触れない第二の仕組みを通る。上述の名前移行の修復を経てもセルが依然として文字列のままであり、ソースワークブックが実在のファイル名を持っている場合、AddCopyは戦略を切り替える:そのテキストではなくコンパイル済みの数式ツリー自体をディープコピーし、そのコピーを、ノードごとにそれを歩く専用の再バインドパスであるRebindExternRefsInTreeに渡す。それが見つけたすべての範囲参照について、そのパスはソースのEXTERNSHEETエントリを一対のシート名へ解決し戻し、目的地自身の外部参照テーブル内に同等のエントリを登録するか再利用する。目的地がそのソースファイルを一度も参照したことがなければ、真新しい外部ワークブックリンクを作成する

ここでワークブックローカルな番号付けの問題が最も文字通りの形で現れる。なぜなら外部参照トークンは3つの別個の座標を1つのフィールドにまとめており、そのそれぞれがそれを書いたワークブックに固有だからだ:どの外部ワークブックか——目的地自身の外部ブックのリスト内のスロットであり、そのワークブックがそれらをたまたま登録した順序で割り当てられている;その外部ワークブック自身のシートリスト内のどのシートか——外部ブックに特有にスコープされた1始まりのインデックスとして保存され、目的地自身の内部シートIDとは全く異なる番号空間である;そしてセル範囲自体——単純な行・列座標であり、そもそもワークブック相対ではなかったため変換を一切必要としない。最初の2つのどちらかを間違えても、Excelはそれでもファイルを開き、それでも数式を表示し、文句を言うことなく間違った外部セルに対してそれを評価する。ある種のノードはこのツリーレベルの再バインドさえも打ち負かす:定義済み名前への参照であり、それはまさにシートインデックスがそのEXTERNSHEETに固有であるのと同じように、それ自身のワークブックのプライベートな名前テーブルへのインデックスであり、利用可能な同等のツリーレベルの修復は存在しない——再バインドの走査がツリーのどこかで名前参照に出会った瞬間、それは部分的に正しいものを書き出すのではなく、数式全体を放棄する。再バインドが成功したときでさえ、目的地のセルは新たに再計算された数値を表示しない;それはコピー時にソースセルがすでに保持していた値を表示し、それはユーザーが明示的にリンクを更新するまで、Excel自身が外部参照の最後に既知の値をキャッシュするのと同じ方法でキャッシュされたスロットに保持される。これは正しい既定動作である、なぜなら別のファイルへのライブなリンクを越えて再計算することは、まさに開くたびにではなく意図的に一度だけ引き起こしたい種類の操作だからだ

この設計が課すコスト

AddCopyの逆コンパイル・再コンパイルの仕組みは無料ではなく、そのコストは大規模な統合ジョブをスクリプト化した後ではなく前に計画しておく価値がある。同じワークブック内でシートをコピーすることは安価な経路をたどる——コンパイル済みツリーの純粋なメモリ内複製である、なぜならその内部のすべてのインデックスは、それが留まるワークブック内ですでに有効だからだ。ワークブック間コピーは代わりに、すべての数式セルについて本物の解析のコストを払う——テキストへ逆コンパイルし、そのテキストをゼロからもう一度コンパイルする。数十個の数式しかないシートではこの違いは測定する価値もないが、数万個の数式セルを持つソースワークブックが、バッチジョブの中で数十枚のうちの1枚としてコピーされる場合、その周りのファイルI/Oではなく再コンパイルが実行時間を支配することを予期すべきである。コピーの順序は速度以外の理由でも重要である:このバッチの中でまだAddCopyが到達していないシートを参照する数式は、本当に存在しないシートを参照する数式と同じ理由で再コンパイルに失敗する。そのため、シートBに依存する数式を持つシートAより先にシートBをコピーするジョブは、上記で説明したのと全く同じようにその数式が劣化するのを目にすることになる——文字列テキストになるか、たった今来たばかりのソースファイルをまさに指し示す外部リンクのフォールバックになるかのどちらかだ。そして統合バッチの中の各ソースワークブックは通常独立して作成されているため、単独のソースファイルが決して警告できなかったであろう唯一の失敗モードを明示的にテストする価値がある——それぞれが仲間の支店の数字を合計する5枚の支店ワークブックは、個々のソースファイルのどれ一つとしてそれを含んでいなくても、サマリーワークブックの内部で本物の循環参照へと組み合わさりうる。これはすべてのシートが同じ場所に着地し、結合されたセットに対して再計算が実行されて初めて存在するようになるサイクルである

ワークブック間のワークシートコピーは、DelphiおよびC++Builder向けHotXLS Delphi ExcelコンポーネントのAddCopyの標準動作として出荷される。製品ページには、ここで説明したチャート・リッチテキスト・外部参照の挙動を含む、ワークシートとワークブックのAPIの全リファレンスが掲載されている