技术文章

用 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,在给单元格定样式之前先解析这个转义,所以格式索引需要超过六位的单元格照样拿到自己的格式

Delphi 中 HotXLS 读取的 BIFF2 与 BIFF3/BIFF4 单元格记录布局对比:各代记录号、LABEL 字段布局差异、误读失败模式,以及平行的公式与字符串记录编号
BIFF2 的体带三字节属性块,BIFF3 和 BIFF4 则复用 BIFF5 号加两字节 XF 索引;混用两种布局会让记录之后的一切悄悄错位

三个世代的文本都是不带任何 Unicode 的普通字节字符串。编码来自文件的 CODEPAGE 记录,在这类文件里它就在那唯一的 worksheet 流中,因为没有工作簿级流可放。HotXLS 经该代码页解码标签和字符串结果,记录缺席时回落到 Windows-1252——这覆盖了这些文件大多数写作时的西方默认值。缺 CODEPAGE 记录的日文或中欧工作簿因此会解码成拉丁文本,唯一的解法是你得知道文件来自哪里

从 Delphi 打开一份

检测发生在 Open 内部,所以调用代码与打开任何工作簿相同。下面的循环走一遍已用区域,对含公式的单元格把每个单元格打印两遍:翻译后的表达式,以及按单元格数字格式显示的值

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 包(通常是有人把 .xlsx 改名成 .xls)时,
      // Open 会抛异常
      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 为兼容既有代码保留了这一不一致。忘了调整,循环审计的就是另一个矩形,还不报任何异常。这条路径接受的文件只带单个 worksheet、chart 或宏表子流,所以 Sheets[1] 是你能找到的唯一工作表;Excel 4.0 绑定工作簿(BOF 类型 $0100)不在此列,Open 返回错误码。完全不加载文件就能做的廉价预检见 轻量级工作簿检视

密码保护的文件遵循同一条单流规则。FILEPASS 记录紧跟 worksheet 流中的 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

Delphi 中 HotXLS 读取的 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', ...) 测试刻意粗糙,只为跳过格式里已带日令牌的单元格;归档很大时,对着你自己已知的日期格式清单做更严格的检查,值得多写那几行

写出迁移副本

从 BIFF2 到 BIFF4 文件打开的工作簿经 BIFF5/BIFF8 或 XLSX 写出器保存,没必要生成一个现版 Excel 都封杀的格式的新文件。Book.SaveAs('migrated\1992-inventory.xls', xlExcel97) 写出一份携带值、翻译后公式和转换后样式的 BIFF8 副本,管线里后续的每个工具都能无需特殊处理地打开它。原件留在旁边:两年后某个单元格冒出疑问时,1992 年的那些字节是唯一的权威

遗留读取、现代写出、重算和格式都住在面向 Delphi 和 C++Builder 的同一套库里;格式覆盖清单见 HotXLS Delphi 电子表格组件页面