技術記事

HotXLSでDelphiからExcel 2.0~4.0形式のファイルを読み込む

1992年にExcel 4.0が保存したワークブックはバイト09 04 06 00で始まり、Excel 5.0以降のすべての.xlsは複合ファイルシグネチャD0 CF 11 E0で始まります。そのシグネチャをチェックするリーダーは、1つのセルも読む前に古いファイルを拒否し、チェックを飛ばしてBIFF8を仮定するリーダーは、最初のレコードをそうではないものとしてパースします。どちらの失敗も同じ仮定から来ます。すべての.xlsにはコンテナがある、という仮定です。Excel 2.0、3.0、4.0のファイルにはコンテナがなく、どの世代が書いたかはレコードストリーム自身が語り、セルレコードはその世代のレイアウトで復号されなければなりません

これらのファイルは今でも出てきます。それがこの話が意味を持つ唯一の理由です。技術系アーカイブ、記録保存庫、制御ソフトウェアが1990年代初頭の計測器からの実験室エクスポート、長年走り続けている会計システムが、いずれもBIFF2からBIFF4のワークブックを後に残しました。現行のExcelビルドはFile Block設定でこれらの形式のいくつかをデフォルトでブロックするため、データセットは、手元のツールでは誰も開けないフォルダに座っていることがよくあります

OLE以前のワークブックの名乗り方

HotXLSは、OLE以前のワークブックを最初のレコードから認識します。すべての.xlsを読むのと同じTXLSWorkbook.Open呼び出しの内側です。Excel 5.0以降のすべての.xlsはOLE2複合ファイル、つまりファイルの中の小さなファイルシステムであり、ワークブックはWorkbookかBookという名前のストリームに住んでいます。パースはそのコンテナのパースから始まります。Pascalでの複合ファイルバイナリ形式で述べています

BIFF2からBIFF4はそのすべてを飛ばします。ファイルはBOFレコードで直接始まり、そのBOFのレコード番号が世代を符号化します。BIFF2は$0009、BIFF3は$0209、BIFF4は$0409です。後の形式が頼る本体の中のバージョンワードはこれらの世代には無視されるため、レコードIDだけが信頼できる信号です。素の経路にコミットする前に、HotXLSのストリーム層はもう2つ確認します。BOF本体は4から6バイトの長さでなければならず、サブストリーム型ワードは、ワークシートなら$0010、チャートなら$0020、マクロシートなら$0040でなければなりません。それ以外はOpenにエラーコードを返させます

それらのチェックは見た目以上に効いてきます。オフセットゼロの2バイト$09 $00は、任意のバイナリデータでも稀ではありません。未識別の.xlsの検疫フォルダには、改名されたテキストエクスポート、切断されたコピー、まったく別のプログラムのファイルが混ざりがちです。3つの独立したフィールドすべてが一致することは、1つよりはるかに強い信号です

HotXLSがDelphiからOLE以前のExcelファイルを検出する仕組みの図。片側に現行のOLE2コンテナワークブック、もう片側に素のBIFF2からBIFF4のレコードストリーム、そしてレガシー経路にコミットする前にチェックされるBOFレコードのフィールド
レガシーファイルは格納ラッパーを持たないため、先頭のBOFレコードそのものが、セルを1つも読む前に世代、本体長、サブストリーム型を証明する

3つの世代、3つのレコードレイアウト

セルレコードは、世代が最も目に見えて分かれる場所です。BIFF2は、空白、整数、数値、ラベル、真偽値/エラーのセルに対して、$0001から$0005までの低いレコード番号の連続したブロックを占有します。それらの本体はそれぞれ3バイトの属性フィールドを運んでおり、後のバージョンはそこへ2バイトの拡張書式インデックスを置くため、すべての値がレコードの1バイト奥に座ります。BIFF2のINTEGERレコードはそれ自体が奇物です。小数部のない符号なし16ビット値で、BIFF3がRKを好んで落としたレコード型です

BIFF3とBIFF4は低い番号を捨て、BIFF5のレコードIDとレイアウトを再利用します。$0201、$0203、$0204、$0205、$027Eで、それぞれ2バイトのXFインデックス付きです。この再利用は、特定の、そして誤診しやすい失敗を引き起こします。BIFF3やBIFF4のLABELレコードは、構造的にBIFF5の対応物と同一です。行、列、書式インデックス、そしてオフセット6の2バイト文字数です。そのレイアウトをBIFF2ラベルに適用すると、カウントは最後の属性バイトと本物の1バイト長から組み立てられ、5文字のラベルは少なくとも1,280文字を要求し、リーダーはレコードの末尾から飛び出します。逆方向、BIFF3レコードにBIFF2レイアウトだと、長さはカウントの上位バイトから読まれ、短い文字列ではゼロです。どちらの方向でも何も送出されません。ワークブックは空のラベルやそれらしいゴミとともに戻ってきます。HotXLSは、BOFから取った世代とともにレコードIDで振り分けることで、両方を避けています

BIFF2の属性フィールドにはもう1つ罠が隠れています。最初のバイトは下位6ビットに書式インデックスを保持し、値$3Fは「本物のインデックスは6ビットには大きすぎる。直前のIXFEレコードを見よ」を意味します。HotXLSは直近のIXFEを追跡し、セルにスタイルを割り当てる前にエスケープを解決するため、書式インデックスが6ビットを超える必要があるセルも、自分の書式を得られます

DelphiでHotXLSが読むBIFF2とBIFF3/BIFF4セルレコードレイアウトの比較。世代ごとのレコード番号、LABELフィールドレイアウトの差、誤読の失敗モード、そして並行する数式と文字列レコードの番号付けを示す
BIFF2の本体は3バイトの属性ブロックを運び、BIFF3とBIFF4は2バイトXFインデックス付きでBIFF5の番号を再利用する。レイアウトを混ぜると、レコードの後のすべてが静かにずれる

3世代すべてでテキストは、どこにもUnicodeのない素のバイト文字列です。符号化はファイルのCODEPAGEレコードから来ます。これらのファイルでは、それを保持するワークブックレベルのストリームがないため、唯一のワークシートストリームの内側に座っています。HotXLSはラベルと文字列結果をそのコードページで復号し、レコードがなければWindows-1252へフォールバックします。これらのファイルのほとんどが書かれた西欧のデフォルトをカバーします。したがって、CODEPAGEレコードを持たない日本や中欧のワークブックはラテン文字として復号され、唯一の修正はファイルの出身を知ることです

Delphiから1つ開く

検出はOpenの内側で起こるため、呼び出しコードはどのワークブックとも同じです。下のループは使用範囲を歩き、数式を持つセルを2度印字します。翻訳された式と、セルの数値書式を通してExcelが表示する値です

uses
  SysUtils, Variants, lxHandle;

var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  Cell: IXLSRange;
  R, C: Integer;
begin
  Book := TXLSWorkbook.Create;
  try
    try
      if Book.Open('archive\1992-inventory.xls') <> 1 then
      begin
        Writeln('not a readable workbook - quarantine for manual review');
        Exit;
      end;
    except
      // ファイルが本当にZIPパッケージの場合、Openは送出する。
      // 典型的には誰かが.xlsへ改名した.xlsx
      on E: Exception do
      begin
        Writeln('rejected: ', E.Message);
        Exit;
      end;
    end;

    Sheet := Book.Sheets[1];          // Sheets[]は1始まり
    for R := Sheet.UsedRange.FirstRow + 1 to Sheet.UsedRange.LastRow + 1 do
      for C := Sheet.UsedRange.FirstCol + 1 to Sheet.UsedRange.LastCol + 1 do
      begin
        Cell := Sheet.Cells[R, C];
        if VarIsEmpty(Cell.Value) or VarIsNull(Cell.Value) then
          Continue;
        if Cell.HasFormula then
          Writeln(Format('R%dC%d  %s  -> %s',
            [R, C, VarToStr(Cell.Formula), Cell.FormattedText]))
        else
          Writeln(Format('R%dC%d  %s', [R, C, Cell.FormattedText]));
      end;
  finally
    Book.Free;
  end;
end;

インデックス計算に注意してください。UsedRangeの境界はゼロ起点なのに、シートコレクションとセルアクセスはどちらも1起点です。既存コードとの互換のためにAPIが保っている不一致です。調整を忘れると、ループは何も異変を報告せずに、間違った矩形を監査します。この経路が受け付けるファイルは、単一のワークシート、チャート、マクロシートのサブストリームを運ぶため、Sheets[1]が見つかる唯一のシートです。Excel 4.0の外部参照ワークブック(BOF型$0100)はその集合の外で、Openはエラーコードを返します。そもそもファイルを読み込まない安い事前チェックは、軽量なワークブック検査で扱っています

パスワード保護されたファイルも同じ単一ストリームの規則に従います。FILEPASSレコードはワークシートストリームでBOFの直後に現れ、HotXLSはそれを拾い、これらの世代が使ったレガシーなXOR難読化解除を切り替えます。パスワードはOpen(FileName, APassWord)オーバーロードを通してください

BIFF2からBIFF4のファイルで数式はどうなるのか

HotXLSはレガシー数式の両半分を読みます。Excelが最後に計算したとき保存したキャッシュ結果と、トークンストリームで、後者はロード中に現行の計算モデルへ翻訳されます。数式レコードは3世代で並行した番号付けを共有します。$0006、$0206、$0406です。しかし本体は、BIFF8パーサーがつまずくまさにその場所で違います

キャッシュ結果は、属性フィールドのせいでBIFF2ではバイト7から、BIFF3とBIFF4ではバイト6から始まります。数値でない結果は、その8バイトスロットの最後の2バイトに$FFFFのマーカーワードを使い、判別子が最初のバイトに付きます。文字列は0、真偽値は1、エラーコードは2、空白は3です。文字列の結果はスロットにまったく収まらないため、テキストは次のレコードで届きます。BIFF2では1バイト長の$0007、BIFF3とBIFF4では2バイト長の$0207です

DelphiからHotXLSが読むBIFF2からBIFF4のFORMULAレコードにおけるキャッシュ数式結果の解剖。値はBIFF2ではバイト7、BIFF3とBIFF4ではバイト6から始まり、文字列、真偽値、エラー、空白にはFFFFのマーカーワードが付く
HotXLSはExcelが最後に計算した値を翻訳された式の隣に保持し、数値でない結果はマーカーワードと判別子として届く

トークンストリームは、素朴な実装が無意味なものを生む場所です。BIFF2は式をオフセット16の1バイト長で前置し、トークンは17から始めます。BIFF3とBIFF4は2バイト長を使い、18から始めます。ストリームの内側では、関数トークンはBIFF5より前では1バイトの関数インデックスを運び、BIFF5からは2バイトのインデックスです。BIFF4のトークンをそのままBIFF8翻訳器に流し込むと、すべての関数呼び出しが1バイト余分に消費し、結果はわずかに間違った数式ではなく、無関係な数式になります。HotXLSの翻訳器はBIFFバージョンをパラメータとして取り、各トークンをその世代の幅で読みます

両半分が手に入ると、実務的な問いが生じます。移行後のワークブックにはどちらの数を入れるのか。キャッシュ値は、ファイルの利用者が1992年に見て、印字して、署名したものです。現行エンジンでの再計算は、数式が今日意味するものを計算しますが、関数の挙動は30年間静止していません。アーカイブ移行ではキャッシュ値が記録なので、それを保持し、再計算は、どのセルが人間の目を値するかを教えるクロスチェックとしてだけ走らせます:

// クロスチェック:保存された結果と新しい再計算を比較する。
// このヘルパーとCachedText配列は自前のコード
procedure CompareWithRecalc(Book: TXLSWorkbook; Sheet: TXLSWorksheet);
var
  CachedText: array of array of string;
  Rows, Cols, R, C: Integer;
begin
  Rows := Sheet.UsedRange.LastRow + 1;
  Cols := Sheet.UsedRange.LastCol + 1;
  SetLength(CachedText, Rows + 1, Cols + 1);
  for R := 1 to Rows do
    for C := 1 to Cols do
      if Sheet.Cells[R, C].HasFormula then
        CachedText[R, C] := VarToStr(Sheet.Cells[R, C].Value);

  Book.Recalculate;

  for R := 1 to Rows do
    for C := 1 to Cols do
      if Sheet.Cells[R, C].HasFormula and
         (VarToStr(Sheet.Cells[R, C].Value) <> CachedText[R, C]) then
        Writeln(Format('R%dC%d  stored=%s  now=%s  formula=%s',
          [R, C, CachedText[R, C], VarToStr(Sheet.Cells[R, C].Value),
           VarToStr(Sheet.Cells[R, C].Formula)]));
end;

これはコピーに対して、あるいは保存の前に走らせてください。Recalculateはメモリ内の保存結果を置き換えるためです。空の報告が望む結果です。短い報告は、たいていNOW()のような揮発性関数か、元の作者がぶら下げたままにした参照を指します

書式、日付、そして標準書式の罠

HotXLSはこれらの世代の書式レコードを、現行の.xlsが生み出すのと同じワークブックモデルへ変換します。FONTレコード(独立したBIFF2のFONTCOLORレコードを折り込み)、FORMAT文字列、そして各世代のXFレコードは、塗りパターン、罫線スタイル、レガシーカラーインデックスとともに、Font、NumberFormatとその他の範囲プロパティで読み編集できる、普通のフォント、数値書式、セルスタイルになります

日付は直接の恩恵を受けます。数値セルが日付の数値書式を運ぶとき、リーダーはワークブックのDATEMODEフラグも適用するため、1904年システムのワークブックは1900年システムと同じTDateTime尺度に着地し、FormattedTextはExcelが示したのと同じ日付を示します。1462日のずれの背後の規則は、日付シリアル値、1904年システム、数値書式で扱っています

罠は、標準書式の下で日付を保持する列です。1990年代のワークブックの多くは、書式を気にせずシリアル値を書いたマクロやインポートで満たされており、ファイルは、33604が部品番号ではなく1992年1月の1日を意味するという信号を何も出しません。それらのセルは、保存のまま、日付システムの補正なしで戻ってきます。ソースについて知っていることから列ごとに決め、それらの列は自分で変換してください:

// ColumnHoldsDatesは自前のルックアップで、ソースシステムについての
// 知識から組み立てる。レガシーファイルはこの問いには答えてくれない。
// SerialはローカルのDouble。CellとCは最初のリストから
if ColumnHoldsDates(C) and (Pos('d', LowerCase(Cell.NumberFormat)) = 0) then
begin
  // 標準書式のシリアル:日付システム補正はまだ適用されていない
  Serial := Cell.Value;
  if Book.Date1904 then
    Serial := Serial + 1462;
  Writeln(DateToStr(TDateTime(Serial)))
end
else
  Writeln(Cell.FormattedText);

Pos('d', ...)テストは意図的に粗く、書式にすでに日トークンを運ぶセルを飛ばすだけのものです。アーカイブが大きいなら、既知の日付書式の自分のリストに対する厳密なチェックのほうが、追加の行の価値があります

移行したコピーを書く

BIFF2からBIFF4のファイルから開かれたワークブックは、BIFF5/BIFF8またはXLSXライタを通して保存されます。現行のExcelがブロックする形式で新しいファイルを作る理由はないからです。Book.SaveAs('migrated\1992-inventory.xls', xlExcel97)は、値、翻訳された数式、変換されたスタイルを運ぶBIFF8コピーを書き、パイプラインの後続のツールはどれも特別扱いなしに開けます。オリジナルは隣に保持してください。2年後にセルが問いを持ち出したとき、1992年のバイトだけが権威です

レガシーの読み、現行の書き、再計算、書式は、DelphiとC++Builder向けの1つのライブラリに住んでいます。形式カバレッジはHotXLS Delphiスプレッドシートコンポーネントのページに一覧があります