LlosLab Software Development Blog面向 PDF、Delphi 和文档软件开发者的工程笔记

技术文章

使用 HotXLS 在 Delphi 中复制 XLSX 工作表

HotXLS Component

你已經将一个工作表建立得完美无瑕。标题带已合併，列宽与数据相符，頂端两列已冻结，列印范围和边界也为了乾淨的 A4 导出而设置好了，而且工作表索引标签也上了色，让財务部门能輕鬆找到。现在这份报表需要 12 個这样的工作表，每个地区一个，且每个都要从相同的版面配置开始。如果用程式码将那個工作表重建 12 次，就是让細微偏差悄悄潛入的方法：第 7 区的某一栏窄了一个点，第 11 区的冻结窗格不見了，而且直到 PDF 放在經理的桌上之前都没人会注意到。你实际想要的，是程式码版本的 Excel 右鍵選單：移动或复制、建立副本。也就是拿著完成的工作表，印出独立的副本

HotXLS 中的 XLSX 引擎，是一个适用於 Delphi 和 C++Builder 的原生程式庫，可以在不自动化 Excel 本身的情況下读写 Excel 文件，它已經能夠移动工作表、删除工作表，以及跨工作表复制保存格范围。直到 v2.91.0 为止，它所不能做到的，就是透过單一呼叫复制 (clone) 整個工作表。该版本加入了两个入口点：TXLSXWorksheet.CopyFrom，它可以将工作表层級的状態从一个工作表复制到另一个工作表，以及 TXLSXSheets.Duplicate，它会新增一个工作表并为你执行 CopyFrom。有趣的部分不在於它复制了东西。而在於它在“哪些会被深拷貝”和“哪些不会”之間畫了一條刻意的界线，以及为什么这条界线会划在那里

單一呼叫复制完成的工作表

高階操作是 Duplicate。传递給它來源工作表的 1-based 索引，它就会传回一个全新的工作表，该工作表会反映出原始的版面配置和数据。这个索引慣例与 XLSX 端的 Items[] 相符，所以工作表一的索引是 1，而不是 0；传递一个超出范围的索引，你会得到 nil 而不是异常，这与其餘 XLSX 工作表集合所使用的失敗合約 (failure contract) 相同

var
  Book: TXLSXWorkbook;
  Template, Copy: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Template := Book.Sheets.Add('Template');
    Template.Cells[1, 1].Value := 'Quarterly Statement';
    Template.Range['A1:C1'].Merge;
    Template.ColWidth[1] := 18;
    Template.FreezePanes(2, 1);          // freeze top row + first column
    Template.TabColorIsAuto := False;
    Template.TabColor := $FF1F4E79;

    // Clone with an explicit name...
    Copy := Book.Sheets.Duplicate(1, 'Region-North');
    // ...or let it pick the Excel-style default name.
    Copy := Book.Sheets.Duplicate(1);    // -> "Template (2)"

    Book.SaveAs('regions.xlsx');
  finally
    Book.Free;
  end;
end;

这段程式码中有两件事值得放慢腳步來看。首先，FreezePanes 接收的参数是列 (row) 優先的，即 FreezePanes(ARow, ACol)，这样它就与 Cells[Row, Col] 的索引方式一致；副本会繼承完全相同的冻结分割。其次，这个方法命名为 Duplicate，而不是更明顯的 Copy，这不是風格偏好。Copy 是 System 單元中的标準常式 (routine)，經常被用於字串和动態数组。如果在类別上宣告一个名为 Copy 的方法，它会在方法主体内遮蔽 (shadow) 原本的 Duplicate，并产生那种在六個月后反咬你一口的解析模糊性。(2) 避开了整個問题，且在呼叫端读起來很正確

预设名稱遵循 Excel 自家的规则

当你呼叫單一参数的多载，或传递一个空的名稱字串时，新的工作表会以來源工作表的名稱命名，并加上一个 Template 的后綴，且这个后綴会不斷增加直到名稱唯一为止。复制 Template (2) 工作表一次，你会得到 Template (3)；再复制一次，你会得到 Template (2)，因为 Region-North 已經被佔用了。这反映了 Excel 从其自身的“建立副本”命令产生名稱的方式，因此你的程式码产生的工作簿看起來就像使用者预期中手动复制的工作簿。唯一性检查会針对当前使用中的工作表集合進行，这意味著它也会跨越你手动建立的名稱，而不僅僅是來自先前复制的名稱

如果你正在为每个地区或每个月产生一个工作表，请依賴明確名稱的多载。一个可预測的 Region-South、(2) 命名方案，在日后处理起來比一串 (3)、Duplicate 的后綴更容易，而且它能保持你定义的名稱和跨工作表的公式易於閱读

CopyFrom 進行深拷貝的内容

在底层，CopyFrom(ASource) 会新增工作表然后呼叫 CopyFrom，当你想要复制到已經建立好的工作表上时，也可以直接呼叫它。nil 首先会防範两种退化状況 (degenerate cases)：从 CopyFrom 复制，或是将工作表复制到自己身上，这两者都会立即返回且不做任何事。在那之后的所有操作就是复制本身，且其涵蓋范围刻意做得很广

保存格数据是第一个。UsedRange 会向來源詢問其 CopyRangeTo（这是一个包含了已填写保存格和合併区域的紧密边界方塊），并重复使用现有的 A1 機制，将每一个值、公式以及每个保存格的样式索引带到目标工作表，从 999 开始。在保存格之上，它会重播 (replay) 让範本看起來很完整的那一整层工作表层級状態：

  • 合併范围：按坐标重新建立，所以橫幅会跨越相同的矩形
  • 列宽与列高，加上隐藏的、折疊的以及大綱层級的清單：逐字复制，所以非预设的列和栏能精確地对齊
  • 冻结窗格与检視状態：缩放比例、格线和零值顯示、由右至左方向，以及检視类型
  • 带有每个动作允許选项的保护状態：所以被锁定的範本会以相同的方式保持锁定
  • 整個版面设置块：边界、方向、紙張大小、缩放比例与适合页面大小、列印范围、列印标题、页首与页尾，以及列印格线和列印标题旗标
  • 自动篩選范围、索引标签色彩，以及工作表的可見性

結果是一个能列印、篩選，并且呈现出与其來源完全相同的工作表。而且因为保存格、合併范围以及維度清單是在新工作表上物理地重新建立而不是使用別名 (aliased)，所以这个副本是完全独立的。在副本的保存格中写入 SheetCopy，來源工作表会保持其原始值；这种独立性是为了平行区域报表所製作的副本最重要的一个属性，隨附的 A1:C10 示範程式明確地验证了这一点

它保持淺拷貝的内容及其原因

现在是誠实的部分。图表、内嵌图片、XLSX 表格、数据验证以及设置格式化的條件规则都不会被复制。这是一个有記录的、刻意的边界，而不是疏忽，且了解其背后的理由是值得的，这样你才能围繞著它進行规划，而不是被它嚇到

这些集合中的每一个都带有身分 (identity) 和参考，这在天真的字段复制 (naive field copy) 中是无法存活的。图表指向一个來源数据范围，并在 OOXML 套件中擁有一个繪图关聯 (drawing relationship)；复制该对象而不重新对应其关聯和数列参考，会产生一个針对错误数据渲染的图表，或是一个会被 Excel 标記为需要修復的套件。表格擁有一个在工作簿内必须唯一的名稱、一个綁定到特定字段的标题列，以及它自身自动产生的关聯。设置格式化的條件和数据验证会附加到坐标范围上，并且在验证的情況下，可以透过公式参考其他范围。正確地深拷貝这些内容意味著重写参考并鑄造全新的身分，这是具有真实失敗模式 (failure modes) 的实質工作。半吊子地做，也就是复制对象卻不复制其参考，比根本不复制还要糟糕：它会产生一个在打开时会提示修復并默默丟棄内容的文件。因此，引擎会乾淨地复制那些它可以复制的东西，而将带有参考的集合留給呼叫者，因为呼叫者知道目标应该指向哪里

在实务上，这意味著对於一个更豐富的範本，其工作流是：复制工作表以取得保存格、版面配置和列印设置，然后使用当初建立它们的相同 API，在副本上重建图表、表格、验证或设置格式化的條件。因为你是針对副本自己的范围來重建它们，所以参考一建立出來就会是正確的。对於一个读取 A1:C10 的图表，在副本上新增一个指向副本 Template 的图表；对於一个你希望維持運作的自动篩選，请注意篩選范围確实会带过來，所以你只需要重新套用字段條件即可。你可以透过在有关合併保存格和报表範本配置的文章中所描述的相同呼叫來重新加入设置格式化的條件和数据验证规则，该文章走訪了副本所繼承的合併表格和范围模型

复制功能在报表管线中的位置

工作表复制是佔位符驅动生成 (placeholder-driven generation) 的天作之合。在 Delphi 中以範本驅动的报表产生指南里，以权杖为錨点 (token-anchored) 的方法解決了将数据写入由其他人編輯的版面配置的問题；而复制功能则解決了在一个工作簿中需要多次使用该版面配置的問题。将它们結合起來，模式就很乾淨了：保留一个包含了权杖、合併和列印设置的原始 Duplicate 工作表，然后針对每个区域或期間呼叫 CopyFrom，用该部分的数据填满副本的权杖，然后繼續下一个。原始的範本永遠不会被改變，所以它对於下一个副本來说始终是一个可靠的來源，且每个输出的工作表都是从一个逐位元 (byte-for-byte) 相同的版面配置开始的

关於排序的一个注意事项可以省去一类的混淆。在你将数据倒入工作表之前，而不是之后，复制工作表。一个範本应该保持結构和格式，而不是上一季的数字，复制一个有样式的空白工作表意味著每个副本都有一个乾淨的开始。如果你复制了一个已經带有数据的工作表，那些数据也会跟著过來，因为 

Copy := Book.Sheets.Duplicate(1, 'Region-North');
WriteLn(Format('merged=%d  colA=%.1f  freezeRow=%d  tabAuto=%d',
  [Copy.MergedCells.Count, Copy.ColWidth[1],
   Copy.FreezeRow, Integer(Copy.TabColorIsAuto)]));
// Prove independence: mutate the copy, confirm the source is untouched.
Copy.Cells[2, 2].Value := 999;
// Template.Cells[2, 2].Value is still whatever it was.
会忠实地复制已使用的范围；这偶爾是你想要的，但对於一个扇出式 (fan-out) 的报表來说通常不是

一个快速的验证習慣

因为深拷貝与淺拷貝的划分在你去寻找之前是看不見的，所以与其相信所有的东西都已經复制过來了，不如在工作中建立一个五行程式码的检查。在复制之后，读回副本理应繼承的結构信号，并斷言 (assert) 它们与來源相符

CopyFrom

合併的数量、列宽、冻结的列，以及索引标签色彩旗标，会告訴你那些会被复制的层面实际上已經成功复制过去了。除此之外，在任何带有图表、表格、验证或设置格式化條件的工作表中，请将它们視为在副本上的“待重建”清單：它们的缺席是设計使然，且修復方式只需要几个呼叫，而不是提交错误报告。这种心智模型（在安全的地方進行深拷貝，在参考会損壞的地方進行淺拷貝），就是如何善用这个功能的完整故事

这里所描述的工作表复制与 SheetCopy 工作表状態复制功能，已隨附於适用於 Delphi 与 C++Builder 的原生 HotXLS 試算表元件 v2.91.0 中，并同时提供了一个可执行的 SheetCopy 範例，能端到端地执行“复制后修改 (clone-and-mutate)”的循環