技术文章

从 Delphi 在大型 XLSX 中原地修补单个工作表

一份手工维护的财务工作簿积攒了数据透视缓存、切片器、条件格式和多年的单元格样式,而夜间作业只需要换掉一个数据页签上的数字。完整加载再保存会把每个部件重新解析、重建、压缩一遍。只重写那一个工作表部件、其他 zip 条目原样搬运,几乎省掉全部这些工作

让这成为可能的是包格式本身。一份 .xlsx 文件是按 Open Packaging Conventions(ECMA-376 第 2 部分)组织的 XML 部件 zip 归档,每个 zip 条目在本地头和中央目录里各记录一次自己的压缩方法、CRC-32 和压缩后尺寸。因此一个条目可以从一个归档搬进另一个归档而全程不解压,前提是搬运者把这三个值连同原始字节一起写过去。数据透视缓存不需要被理解才能保留,它只需要被复制

为什么复制压缩字节而不重新保存工作簿?

在压缩层上复制把补丁的大部分变成纯粹的流拷贝。在 HotXLS 里,TXLSDirectWriter.BeginPatch 以只读方式打开源包,把补丁不碰的每个条目交给 TZipArchive.CopyCompressedFrom,后者把源条目的压缩字节、CRC 和尺寸直接写进目标归档。常规写入路径要付一次解压(进)加一次压缩(出),而压缩是贵的那个半程。对一个主体是大块数据透视缓存加几十张内嵌图片的工作簿,时间会花在你真正要写的那张表上

原样拷贝覆盖未压缩存储的条目(zip 方法 0)和 deflate 压缩的条目(方法 8)。用其他压缩方法的条目也会被搬运:写入器经解压流读入再写出,部件照样落到目标里。目录标记条目没有内容,直接跳过,因为写入器会生成自己的。少数条目从不复制,因为它们要重新生成或编辑:目标工作表及其 _rels 部件、xl/calcChain.xml、xl/_rels/workbook.xml.rels 和 [Content_Types].xml,插入工作表时还有 xl/workbook.xml

HotXLS 从 Delphi 原地修补 XLSX 的示意:目标工作表经 AddSheet、AddRow 和 Write 调用重新生成,其他每个包部件经 CopyCompressedFrom 按压缩字节原样搬运,过期的 calcChain 部件被丢弃因为 Excel 会在重算时重建它
HotXLS 在压缩层复制未触碰的部件、只重新生成目标工作表,过期的计算链被移除而不是复制

用 BeginPatch 原地替换一张工作表

两参数重载 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;

这段代码里有两个细节容易误读。传给 AddSheet 的名字不改任何东西:页签保留它在 xl/workbook.xml 里已有的名字、sheetId 和位置,所以 AddSheet('Totals') 仍然写进 Data。第二次 AddSheet 会抛异常,因为一次补丁恰好写一张工作表。刷新三个页签意味着三次补丁,每次都再把包复制一遍

公式不带等号写入。WriteFormula 把文本直接放进单元格的 <f> 元素,而 ECMA-376 第 1 部分 §18.3.1.40 在这里存公式时不带 Excel 在编辑栏里显示的 "=";写 '=SUM(B2:B3)' 会把一个字面等号塞进 XML。WriteFormula 也不写出缓存结果。如果下游有东西读缓存值而不重算,WriteFormulaNumber 会把公式连同你已知的求值结果一起写入

原地重载工作时从不写原始文件。打好补丁的包先建在一个兄弟文件里,文件名在源文件名后追加 .hxlpatch.tmp。Close 时释放源文件句柄,暂存文件通过一次 rename 替换原始文件。rename 若失败,Close 抛出消息里带两个路径的异常,保留暂存副本、原始文件原地不动。进程在 Close 之前被杀,原始文件完好,旁边多出一个游离的 .hxlpatch.tmp,下次对该文件 BeginPatch 时会先删掉它

向模板副本追加一张新工作表

四参数重载 BeginPatch(SourceFileName, TargetFileName, SheetName, InsertSheet) 不动源文件、写出一个新包。InsertSheet = False 时替换副本里的同名表;True 时在该名字下追加一张新工作表,模板里已有同名页签则抛 "worksheet already exists"

  // template.xlsx 不动;output.xlsx 多出一个页签
  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 的。这样打过补丁的 Strict 工作簿不会在一堆 Strict 关系里混进一条 Transitional

HotXLS 在 Delphi 中两个 BeginPatch 重载的示意:原地形式把打好补丁的工作簿暂存在临时文件、最后通过 rename 原子替换原文件使崩溃时原文件完好,插入形式则在登记表上动过手术后写出新文件——改动工作表注册表和关系映射
原地补丁先把结果暂存进临时文件再做原子替换;插入则重写另一个目标文件的工作表注册表和关系映射

样式、字符串与补丁不碰的部件

补丁内部,新工作表指向的一切必须已存在于源包。因此字符串内联写进工作表,而不是写进 xl/sharedStrings.xml,因为共享字符串表逐字节搬运、无法增长。把 UseSharedStrings 设为 True 会让 AddSheet 在补丁模式下抛异常

同一条规则改变了 StyleIndex 的含义。补丁模式下它是源工作簿 cellXfs 列表里的位置(ECMA-376 第 1 部分 §18.8.10),写入器只检查它落在 0 到 65534 之间。它没法知道索引 14 在这个特定文件里是否存在,所以写错的数字会径直进入输出。找对编号最可靠的办法是解压当前版本的表,读一个已经带你要的格式的单元格上的 s 属性;那个数字就是 cellXfs 位置。只要有人在模板里给单元格设格式,Excel 就可能往这个列表里加条目,所以每次模板编辑之后都要重新查索引,别信一年前写死的常量

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。除了抽查一个值,两项结构检查能抓住大多数失误。Check.Sheets.Count 在替换后应等于模板的表数、插入后应多一;用任意 zip 工具列一下归档,应当看不到 xl/calcChain.xml,也没有出现两次的条目名。重名要紧,因为有些读取器取给定名字的最后一个条目,两张表会看起来像内容被对调了

补丁、流式还是整本加载?

工作簿很大、改动局限在一张表、文件其余部分必须逐位保留时,补丁合适。改动跨多张表、新数据需要模板尚未定义的格式、必须创建图表或表格、或文件小到正常加载保存的成本不值得测量时,它是错的工具。从零开始的批量生成,流式直写器更合适;由于两者共享同一批 AddRow 和 Write* 调用,代码在两者间挪动主要是把 BeginFile 换成 BeginPatch

当作业确实需要完整对象模型时,已加载工作簿内的表级操作在复制 XLSX 包中的工作表中有覆盖。而如果考虑补丁只是因为整本处理变慢了,大型工作簿性能会先讲清加载保存的时间和内存实际花在哪,再决定路线

补丁写入器、流式写入器和完整的 TXLSXWorkbook 对象模型同属一个面向 Delphi 与 C++Builder 的库;版本与完整功能列表见 HotXLS Delphi 电子表格组件页面