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

技术文章

在 Delphi 里写 HotXLS 的 Value From Cells 数据标签

HotXLS Delphi Component

生成器重写过图表之后，本该显示 C 列的标签变回裸数字，或者某根柱子读出 S1; 2; 20 而不是 20.0，而 Excel 打开文件毫无怨言。Value From Cells 标签住在 Office 2013 的一个图表扩展里，HotXLS 经 TXLSXChartSeries.DataLabelsRange 写它，按架构顺序带上完整的逐点标签设置

Excel 把 Value From Cells 标签放在哪

ECMA-376 图表架构（Part 1，§21.2）里没有任何元素表示“这个标签的文字取自一个工作表区域”。它的 CT_DLbls 类型——系列内部的 <c:dLbls> 块——容纳任意多个单点 <c:dLbl> 覆盖，后面跟 <c:delete> 或共享标签组，末尾还有一个扩展列表。共享组是固定序列：numFmt、spPr、txPr、dLblPos、showLegendKey、showVal、showCatName、showSerName、showPercent、showBubbleSize、separator、showLeaderLines、leaderLines。Excel 设置数据标签格式窗格里的复选框几乎每个都映射到其中一个元素。Value From Cells 例外。Excel 2013 靠 c15 命名空间（http://schemas.microsoft.com/office/drawing/2012/chart）里的扩展列表把它加在标准之上，Microsoft 在 [MS-ODRAWXML] 中有文档，所以严格按 ECMA-376 架构构建的阅读器在那里只看到不透明的 <c:ext> 元素。只留要紧的部分，带单元格区域标签的系列长这样：

<c:ser>
  <c:idx val="0"/><c:order val="0"/>
  <c:tx>...</c:tx>
  <c:dLbls>
    <!-- 零个或多个 <c:dLbl> 单点覆盖打头 -->
    <c:showLegendKey val="0"/>
    <c:showVal val="1"/>
    <c:showCatName val="0"/>
    <c:showSerName val="0"/>
    <c:showPercent val="0"/>
    <c:showBubbleSize val="0"/>
    <c:extLst>
      <c:ext uri="{CE6537A1-D6FC-4f65-9D91-7224C49458BB}"
             xmlns:c15="http://schemas.microsoft.com/office/drawing/2012/chart">
        <c15:showDataLabelsRange val="1"/>
      </c:ext>
    </c:extLst>
  </c:dLbls>
  <c:cat>...</c:cat>
  <c:val>...</c:val>
  <c:extLst>
    <c:ext uri="{02D57815-91ED-43cb-92C2-25804820EDAC}"
           xmlns:c15="http://schemas.microsoft.com/office/drawing/2012/chart">
      <c15:datalabelsRange>
        <c15:f>Sales!$C$1:$C$3</c15:f>
        <c15:dlblRangeCache>
          <c:ptCount val="3"/>
          <c:pt idx="0"><c:v>Target met</c:v></c:pt>
          ...
        </c15:dlblRangeCache>
      </c15:datalabelsRange>
    </c:ext>
  </c:extLst>
</c:ser>

这个功能分居两处。区域归系列：带 URI {02D57815-91ED-43cb-92C2-25804820EDAC} 的系列级扩展装着 c15:datalabelsRange，其 c15:f 存引用，c15:dlblRangeCache 存文件保存时显示出来的标签文字。开关归标签：c15:showDataLabelsRange 待在一个带 URI {CE6537A1-D6FC-4f65-9D91-7224C49458BB} 的扩展里、位于 <c:dLbls> 末尾；同一个扩展也能给单个 <c:dLbl> 收尾，为那一个点开关区域文字

两半各自单独存在都没用。有区域没开关的文件不显示单元格文字，有开关没区域的文件没东西可显示。只建模 §21.2 所定义内容的程序，重生成 chart part 时两样全丢，这正是 HotXLS 在编辑中保留未建模 ChartML 的原因。缓存与引用同等要紧：解析不了区域的消费方——比如指向另一个工作簿的区域——照样需要文字可画

HotXLS for Delphi 里承载 Excel Value From Cells 标签的两个 c15 槽位示意图：系列上的 datalabelsRange 引用与缓存，加上 dLbls 内的 showDataLabelsRange 开关
区域住在系列级的 c15 扩展里，开关在 c:dLbls 内——两半缺任何一半都不显示单元格文字

给系列设置标签区域

在 HotXLS 里，区域是系列的属性。给 TXLSXChartSeries.DataLabelsRange 赋值会同时打开 ShowDataLabelsRange 并把它标记为显式设置——没有开关的区域永远不显示——赋空字符串则把它再关掉。单元格文字与常规开关（ShowValue、ShowCategoryName、ShowSeriesName、ShowPercent、ShowLegendKey、ShowBubbleSize）组合，用 Separator 连接，LabelNumFmt 负责值的部分。趋势线的公式标签不在这个块里；它待在 <c:trendline> 内，遵循Excel 自己的趋势线公式文字规则。保存图表时，ResolveDataLabelsRange 用单元格的格式化文字刷新 DataLabelsCache，图表只画可见单元格时跳过隐藏的行和列；指向另一个工作簿的区域保留打开时的缓存。工作簿与图表的创建遵循惯常的 HotXLS 图表模式：新工作簿在你添加之前没有工作表，Sheets.Items 从 1 起计

uses
  lxHandleX;

procedure BuildRevenueChart(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Chart: TXLSXChart;
  Series: TXLSXChartSeries;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Sheet.Cells[1, 1].Value := 'East';
    Sheet.Cells[1, 2].Value := 18.25;
    Sheet.Cells[1, 3].Value := 'Target met';
    Sheet.Cells[2, 1].Value := 'Central';
    Sheet.Cells[2, 2].Value := 20;
    Sheet.Cells[2, 3].Value := 'Restated';
    Sheet.Cells[3, 1].Value := 'West';
    Sheet.Cells[3, 2].Value := 14.6;
    Sheet.Cells[3, 3].Value := 'Below plan';

    Chart := Sheet.AddChart(xlsxChartBar, 'Revenue by region', 1, 5, 16, 12);
    Series := Chart.AddSeries('Revenue', 'Sales!$A$1:$A$3', 'Sales!$B$1:$B$3');

    // 同时把 ShowDataLabelsRange := True 并记录为已设置
    Series.DataLabelsRange := 'Sales!$C$1:$C$3';
    Series.ShowValue := True;           // 单元格文字加上值
    Series.Separator := ' | ';
    Series.LabelNumFmt := '0.0';        // 值的部分读作 20.0 而不是 20
    Series.LabelPos := xlsxDlblPosOutEnd;

    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

架构的 ST_DLblPos 枚举列出九个位置记号，任何图表上都接受其中任意一个，所以验证器不会对折线系列上的 outEnd 有意见。哪些位置有意义取决于图表种类：条形与柱形系列接受 ctr、inBase、inEnd 和 outEnd，饼图外加 bestFit，折线与散点系列用 l、r、t 和 b。HotXLS 把选定的 TXLSXChartDataLabelPos 值写成对应记号、把判断留给 Excel，所以 LabelPos 应该从属于你图表类型的子集里取。赋 xlsxDlblPosDefault 会清掉 LabelPosSet、完全不写 dLblPos，让 Excel 自己摆标签。对上面的例子，预期：每根柱子在末端外侧标出单元格文字和保留一位小数的值，两部分用 | 连接

HotXLS for Delphi 里 Excel 数据标签位置记号按图表族的判定矩阵：条形与柱形、饼图、折线与散点，以及各族接受的 dLblPos 记号
架构在任何图表上都接受任意 dLblPos 记号——从匹配图表种类的子集里挑，摆放交给 Excel 判断

单点标签必须复述哪些内容？

系列标签说的一切。架构让单点覆盖看起来很便宜：CT_DLbl 就是一个 idx，后面跟 delete 或一个标签组（可选的 layout 与 tx 排在同一条 numFmt 到 separator 的序列之前）加可选的扩展列表，组里每个成员都是可选的。于是，一个 <c:dLbl>——除 idx 与 showDataLabelsRange 扩展外别无他物——完全架构合规。Excel 把这种标签渲染成 S1; 2; 20 而不是 20.0：它把每个省略的 show* 开关当成开，系列名和类别名都冒出来，还不继承系列的数字格式。单点标签是替换该点的系列标签，不是打补丁——Excel 自己写的单点标签之所以带全套开关，原因就在这。为了让 XML 瘦下来而输出最小覆盖的生成器能过验证，文字照样是错的

最小 Excel 单点标签覆盖（渲染成 S1; 2; 20）与 HotXLS 从 Delphi 写出的完整替换标签（渲染成 20.0）对比图
单点标签替换该点的系列标签而非打补丁，所以每个覆盖都带上全套开关

HotXLS 把每个单点标签都写成完整替换。SetPointShowDataLabelsRange(APointIndex, AVisible) 为一个点记录区域文字的开或关，键是从 0 起计、将来变成 idx 的点索引。保存时每个这样的点得到一个 <c:dLbl>：idx、系列数字格式、位置和开关按组顺序，最后是携带该点取值的 {CE6537A1-…} 扩展。SetPointLabelText(APointIndex, AText) 经 <c:tx> 加字面文字；一个同时带文字、区域覆盖和保留布局的索引仍然只有一个 <c:dLbl>。读取时系列开关从系列级取，单点标签里的副本永不覆盖它们。下面的代码给 revenue 系列设两个覆盖，其后的 XML 是点 1 的预期标签

procedure OverridePoints(Series: TXLSXChartSeries);
var
  I: Integer;
  Visible: Boolean;
begin
  // 第二根柱（idx 1）：只显示值，不带单元格文字
  Series.SetPointShowDataLabelsRange(1, False);
  // 第三根柱（idx 2）：用字面文字替换整个标签
  Series.SetPointLabelText(2, 'Q3 pending audit');

  for I := 0 to Series.DataLabelRangeOverrideCount - 1 do
    Writeln('point ', Series.DataLabelRangeOverrideIndexes[I],
      ' range text: ', Series.DataLabelRangeOverrideValues[I]);

  if Series.TryGetPointShowDataLabelsRange(0, Visible) then
    Writeln('point 0 has its own override')
  else
    Writeln('point 0 follows ShowDataLabelsRange = ',
      Series.ShowDataLabelsRange);
end;
<!-- 点 1 的预期 XML -->
<c:dLbl>
  <c:idx val="1"/>
  <c:numFmt formatCode="0.0" sourceLinked="0"/>
  <c:dLblPos val="outEnd"/>
  <c:showLegendKey val="0"/>
  <c:showVal val="1"/>
  <c:showCatName val="0"/>
  <c:showSerName val="0"/>
  <c:showPercent val="0"/>
  <c:showBubbleSize val="0"/>
  <c:separator> | </c:separator>
  <c:extLst>
    <c:ext uri="{CE6537A1-D6FC-4f65-9D91-7224C49458BB}"
           xmlns:c15="http://schemas.microsoft.com/office/drawing/2012/chart">
      <c15:showDataLabelsRange val="0"/>
    </c:ext>
  </c:extLst>
</c:dLbl>

Excel 能打开图表，说明不了它的 XML 合规

Excel 能打开子元素乱序的图表，<c:dLbls> 里混着未知元素也不碍事，属性还能正确读回；val="banana" 这种非法属性值才会让 Workbooks.Open 失败。别的消费方没这么宽容。Open XML SDK 的 OpenXmlValidator 把乱序子元素报成架构错误，严格绑定架构的工具直接拒收，所以“在 Excel 里能打开”的图表仍可能弄挂下游管线。用 OpenXmlValidator 验证生成的 chart part；在 Excel 里双击一下只能说明 Excel 应付过去了。下面是 §21.2 架构里最容易搞错的顺序规则：

  • 在 <c:dLbls> 里，单点覆盖打头，共享组跟上，extLst 收尾；组内 dLblPos 先于每个 show* 开关，showLegendKey 先于 showVal
  • 单点标签内，layout 与 tx 跟在 idx 之后、先于 numFmt；该点的扩展列表收尾
  • 条形系列的顺序是 idx、order、tx、spPr、invertIfNegative、pictureOptions、dPt、dLbls、trendline、errBars、cat、val、shape、extLst；折线系列用 marker 顶替条形系列的 invertIfNegative 与 pictureOptions，用 smooth 顶替 shape
  • 散点、面积和气泡系列保持同一副骨架（标签、趋势线、误差线和数据，按此顺序）外加各自成员：散点与气泡数据用 xVal 和 yVal，气泡系列在数据之后是 bubbleSize 和 bubble3D
  • 系列级常见的手滑是数据引用写在 dLbls 之前、errBars 排到 trendline 前面、smooth 排到数据前面，Excel 对这些全都不吭声

手工标签位置落在单点标签上

布局是直觉与架构分道扬镳的地方。用户在 Excel 里拖动一个标签时，手工位置以 <c:layout> 存进那个标签的 <c:dLbl>、紧跟 idx。CT_DLbls 没有 layout 子元素，所以想给整组定位、把这样的元素直接写到 <c:dLbls> 底下的生成器，产出的是验证器标出非法子元素的文件。Excel 打开这种文件还会做没人要求的事：把布局只应用到第一个标签、放在 idx 0 点布局会放的位置，并且读错其余标签的宽度，37.7 的标签宽度回来变成 52.3。图表看起来几乎是对的，这种错误审阅时很容易漏

从扩展里拷出来的布局能让整个工作簿打不开。Excel 还可能把 c15:layout 存在标签的扩展列表里，而 c15 前缀声明在外层 <c:ext> 上、不在片段上。按本地名找布局的代码会把这个元素也捞进来，贴进 <c:dLbls> 时前缀没带声明，重建的 chart part 便含未声明的命名空间前缀，任何认命名空间的 XML 解析器都不肯加载。按命名空间、按直接子元素的位置匹配布局，绝不只按本地名

HotXLS 只取直接的 <c:layout> 子元素——属于 <c:dLbls> 或 <c:dLbl>——绝不从扩展内部取标签布局，并按点索引原样保存。LabelLayoutXml 存第一个标签的布局——包括直接发现于 <c:dLbls> 底下的走失者，因为 Excel 就是这么解读它的；PointLabelLayoutCount、PointLabelLayoutIndexes 和 PointLabelLayoutXmls 列出其余。图表重生成时，该布局落到 idx 0 的 <c:dLbl> 上，其余布局各自落到自己索引的 <c:dLbl> 上，每个都与该点的区域覆盖和文字合并成一个标签

procedure ReportLabelLayouts(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Series: TXLSXChartSeries;
  C, S, I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then
      Exit;
    Sheet := Book.Sheets[1];               // 工作表索引从 1 起计
    for C := 0 to Sheet.Charts.Count - 1 do
      for S := 0 to Sheet.Charts[C].SeriesCount - 1 do
      begin
        Series := Sheet.Charts[C].Series[S];
        Writeln('chart ', C, ' series ', S,
          ' label range: ', Series.DataLabelsRange,
          ' cached texts: ', Series.DataLabelsCache.Count);
        if Series.LabelLayoutXml <> '' then
          Writeln('  first label has a manual layout');
        for I := 0 to Series.PointLabelLayoutCount - 1 do
          Writeln('  point ', Series.PointLabelLayoutIndexes[I],
            ' layout: ', Series.PointLabelLayoutXmls[I]);
      end;
  finally
    Book.Free;
  end;
end;

Excel 把单点标签当作一个完整的标签来读，所以照整标签写它——手工位置放在同一个 <c:dLbl> 上——S1; 2; 20 文字和跑偏的第一个标签就都进不了你的图表。在 HotXLS 里，这个模型就坐在 DataLabelsRange、单点覆盖调用和保留布局属性的后面