技術文章

用 HotXLS 在 Delphi 中讀取 Excel 2.0 到 4.0 檔案

Excel 4.0 在 1992 年存的活頁簿,開頭位元組是 09 04 06 00;Excel 5.0 之後的每一個 .xls 開頭都是複合檔簽章 D0 CF 11 E0。檢查這個簽章的讀取器,還沒讀到任何儲存格就拒收了舊檔案;跳過檢查、直接假定 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 的串流層還查兩件事:BOF 本體必須是四到六位元組,子串流類型字組必須是 $0010(工作表)、$0020(圖表)或 $0040(巨集表)。其他任何值都讓 Open 回傳錯誤碼

這些檢查比看上去重要。任意二進位資料裡,位移零處出現兩個位元組 $09 $00 並不罕見,而一個來路不明的 .xls 隔離資料夾裡,往往躺著改名的文字匯出、被截斷的副本,以及偶爾一份來自完全不同程式的檔案。三個獨立欄位同時吻合,是比單一欄位強得多的訊號

HotXLS 如何從 Delphi 偵測前 OLE Excel 檔案的圖解:一側是現代 OLE2 容器活頁簿,另一側是原始 BIFF2 到 BIFF4 記錄串流,並標出走上舊格式路徑前檢查的 BOF 記錄欄位
舊格式檔案沒有儲存外殼,所以開頭的 BOF 記錄本身就證明了世代、本體長度與子串流類型,之後才開始讀儲存格

三個世代,三種記錄版面

儲存格記錄是三個世代分歧最明顯的地方。BIFF2 佔用一段連續的低記錄號,$0001 到 $0005,對應空白、整數、數值、標籤與布林或錯誤儲存格。這些記錄的本體都帶一個三位元組屬性欄位,而後期版本在這裡放的是兩位元組延伸格式索引,所以每個值在記錄裡都往後挪一位元組。BIFF2 的 INTEGER 記錄另有怪癖:一個無小數部分的無號 16 位元值,這個記錄類型在 BIFF3 被 RK 取代

BIFF3 與 BIFF4 棄用低號,改用 BIFF5 的記錄 id 與版面:$0201、$0203、$0204、$0205 與 $027E,各帶兩位元組 XF 索引。這種重用導致一種特定且極易誤診的失敗。BIFF3 或 BIFF4 的 LABEL 記錄與 BIFF5 版本結構完全相同:列、欄、格式索引,然後是位移 6 處的兩位元組字元計數。把這個版面套到 BIFF2 標籤上,計數就由最後一個屬性位元組與真正的一位元組長度拼成,五個字元的標籤宣稱至少 1,280 個字元,讀取器衝出記錄末尾。反向套——BIFF2 版面套在 BIFF3 記錄上——長度則從計數的高位元組讀出,短字串的高位元組是零。兩個方向都不會拋任何例外。活頁簿帶著空標籤或看似合理的垃圾回來,HotXLS 兩者都避免:按記錄 id 加上從 BOF 取得的世代一起分派

BIFF2 屬性欄位還藏著一個陷阱。它的第一個位元組在低六個位元放格式索引,值 $3F 代表「真正的索引超出六個位元能表達的範圍,去看前一條 IXFE 記錄」。HotXLS 記住最近一條 IXFE,在指派樣式之前先解析這個逃脫,所以格式索引需要超過六個位元的儲存格,仍然拿到自己的格式

HotXLS 在 Delphi 中讀取的 BIFF2 與 BIFF3/BIFF4 儲存格記錄版面比較:各世代記錄號、LABEL 欄位版面差異、誤讀失敗模式,以及平行的公式與字串記錄編號
BIFF2 本體帶三位元組屬性區塊,BIFF3 與 BIFF4 則重用 BIFF5 號碼並帶兩位元組 XF 索引;混用兩種版面,記錄之後的一切悄悄錯位

三個世代的文字都是純位元組字串,完全沒有 Unicode。編碼來自檔案的 CODEPAGE 記錄,在這些檔案裡它就坐在唯一的工作表串流中,因為沒有活頁簿層級的串流可以放它。HotXLS 經由那個碼頁解碼標籤與字串結果,記錄缺席時回退到 Windows-1252,這涵蓋了這批檔案大多數使用的西文預設。缺 CODEPAGE 記錄的日文或中歐活頁簿會因此解成拉丁文字,唯一的解法是知道檔案從哪裡來

從 Delphi 開啟一份

偵測發生在 Open 內部,所以呼叫端程式碼與任何活頁簿相同。下面的迴圈走訪使用範圍,對帶公式的儲存格列印兩次:翻譯後的運算式,以及經儲存格數字格式呈現、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 會擲出例外,
      // 通常是有人把 .xlsx 改名成 .xls
      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 的邊界是 0 起算,而工作表集合與儲存格存取都是 1 起算——API 為相容既有程式碼保留了這個不一致。忘了調整,迴圈就稽核錯了矩形,卻不報告任何異常。這條路徑接受的檔案只帶單一工作表、圖表或巨集表子串流,所以 Sheets[1] 是你唯一找得到的工作表;Excel 4.0 繫結活頁簿(BOF 類型 $0100)不在這個集合裡,Open 回傳錯誤碼。能在載入前就把檔案擋掉的廉價預檢,見輕量活頁簿檢視一文

受密碼保護的檔案遵循同樣的單串流規則。FILEPASS 記錄緊跟在工作表串流的 BOF 之後,HotXLS 就地拾起並切換到這些世代使用的舊式 XOR 去混淆;密碼經 Open(FileName, APassWord) 多載傳入

BIFF2 到 BIFF4 檔案裡的公式會怎樣?

HotXLS 讀取舊式公式的兩半:Excel 上次計算時存的快取結果,以及權杖串流——載入時翻譯成當前的計算模型。公式記錄在三個世代間保持平行編號,$0006、$0206 與 $0406,但本體的差異恰恰落在 BIFF8 剖析器會絆倒的地方

快取結果在 BIFF2 從位元組 7 開始(因為屬性欄位),BIFF3 與 BIFF4 從位元組 6 開始。非數值結果在這八位元組槽的最後兩位元組用 $FFFF 標記字組,第一位元組放判別值:0 字串、1 布林、2 錯誤碼、3 空白。字串結果塞不進這個槽,文字由下一條記錄送達:BIFF2 是帶一位元組長度的 $0007,BIFF3 與 BIFF4 是帶兩位元組長度的 $0207

HotXLS 從 Delphi 讀取的 BIFF2 到 BIFF4 FORMULA 記錄快取結果剖析:值在 BIFF2 從位元組 7、BIFF3 與 BIFF4 從位元組 6 開始,字串、布林、錯誤與空白用 FFFF 標記字組
HotXLS 把 Excel 上次算出的值與翻譯後的運算式放在一起,非數值結果以標記字組加判別位元組送達

權杖串流是天真的實作產出胡言的地方。BIFF2 在位移 16 放一位元組長度,權杖從 17 開始;BIFF3 與 BIFF4 用兩位元組長度,從 18 開始。串流內部,BIFF5 之前的函式權杖帶一位元組函式索引,BIFF5 起是兩位元組。把 BIFF4 權杖原封不動餵給 BIFF8 翻譯器,每個函式呼叫都多消耗一位元組,結果不是差一點的公式,而是一條不相干的公式。HotXLS 翻譯器把 BIFF 版本當參數,按該世代的寬度讀每個權杖

兩半都在手上,實務問題隨之而來:遷移後的活頁簿該放哪個數字?快取值是 1992 年的使用者看到、列印、簽核的東西。在現代引擎上重算,算出的是公式今天的意義,而函式行為三十年來沒有原地踏步。對檔案庫遷移而言,快取值才是記錄,所以保留它,把重算當交叉檢查跑一遍,告訴你哪些儲存格值得人工看一眼:

// 交叉檢查:把儲存的結果與重新計算的結果比對。
// 這個輔助函式與它的 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 月的某一天而不是料號。這些儲存格會按原樣回來,不做日期系統校正。按你對來源的認識逐欄判斷,自己轉換那些欄:

// 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', ...) 這個測試刻意粗糙,只為跳過格式裡已經帶日 token 的儲存格;檔案庫很大時,對著自己的已知日期格式清單做更嚴格的檢查,多那幾行值得

寫出遷移後的副本

從 BIFF2 到 BIFF4 檔案開啟的活頁簿,經 BIFF5/BIFF8 或 XLSX 寫入器存檔,因為沒有理由用現行 Excel 封鎖的格式產生新檔案。Book.SaveAs('migrated\1992-inventory.xls', xlExcel97) 寫出一份帶著值、翻譯後公式與轉換樣式的 BIFF8 副本,管線裡其後的每個工具都能直接開啟。原件留在旁邊:兩年後若某個儲存格冒出疑問,1992 年的位元組是唯一的權威

舊格式讀取、現代寫出、重算與格式轉換,在 Delphi 與 C++Builder 中同屬一套函式庫;格式涵蓋範圍列於HotXLS Delphi 試算表元件頁面