LlosLab 軟體開發部落格面向 PDF、Delphi 與文件軟體開發者的工程筆記

技術文章

在 Delphi 讓圖表資料標籤取值自 Excel 儲存格

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 所定內容的程式，在重新產生圖表 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 列舉列出九個位置 token，且任何圖表都接受任何一個，所以驗證器不會對折線數列上的 outEnd 有意見。哪些位置合理取決於圖表類型：橫條與直條數列用 ctr、inBase、inEnd 與 outEnd，圓形圖多一個 bestFit，折線與散佈圖數列用 l、r、t 與 b。HotXLS 把選定的 TXLSXChartDataLabelPos 值照其 token 寫出，判斷交給 Excel，所以 LabelPos 應該從屬於你圖表類型的那個子集裡挑。指派 xlsxDlblPosDefault 會清除 LabelPosSet，完全不寫 dLblPos，讓 Excel 自己放置標籤。就上面的範例而言，Expected：每根橫條的標籤出現在末端外側，內容是儲存格文字加上一位小數的值，兩部分以 | 串接

HotXLS for Delphi 中 Excel 資料標籤位置 token 依圖表族群的決策矩陣：橫條與直條、圓形圖、折線與散佈圖，各自可用的 dLblPos token
結構描述在任何圖表上都接受任何 dLblPos token——從符合圖表類型的子集裡挑，放置交給 Excel 判斷

單點標籤得重複寫哪些東西？

數列標籤講的每一句話。結構描述讓單點覆寫看起來很便宜：CT_DLbl 就是一個 idx，後面接 delete 或標籤群組（選配的 layout 與 tx，再接同一段 numFmt 到 separator 的成員），外加選配的擴充清單，而群組裡每個成員都是選配。因此一個只裝 idx 與 showDataLabelsRange 擴充的 <c:dLbl> 完全合於結構描述。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>。讀取時，開關以數列層級為準，所以單點標籤裡的副本永遠不會覆寫它們。下面的程式碼對營收數列設兩個覆寫，其後的 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 裡沒問題」的圖表照樣能把下游管線弄斷。產生的圖表 part 要用 OpenXmlValidator 驗證；在 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；折線數列在橫條數列放 invertIfNegative 與 pictureOptions 的位置放 marker，在放 shape 的位置放 smooth
  • 散佈圖、區域圖與泡泡圖數列維持同一副骨架（標籤、趨勢線、誤差線與資料，按此順序），再帶各自的成員：散佈圖與泡泡圖資料用 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> 時前綴跟著走、宣告卻沒跟著走，於是重建的圖表 part 帶著未宣告的命名空間前綴，任何支援命名空間的 XML 解析器都不肯載入。找版面要按命名空間、按「身為直接子項」的位置來配對，永遠別只看區域名稱

HotXLS 只從 <c:dLbls> 或 <c:dLbl> 的直接 <c:layout> 子項取標籤版面，絕不從擴充裡面拿，並按點索引原樣保存。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、單點覆寫呼叫與保留版面屬性的背後