Delphi에서 Excel Value From Cells 데이터 레이블 구현

생성기가 차트를 다시 쓴 뒤, 열 C를 보여야 할 레이블이 맨 숫자로 돌아오거나 어떤 막대가 20.0 대신 S1; 2; 20으로 읽히는데도 Excel은 파일을 아무 불만 없이 엽니다. Value From Cells 레이블은 Office 2013 차트 확장에 앉아 있고, HotXLS는 TXLSXChartSeries.DataLabelsRange로 그것을 스키마 순서의 완전한 포인트별 레이블 설정으로 씁니다

Excel이 Value From Cells 레이블을 보관하는 곳

ECMA-376 차트 스키마(Part 1, §21.2)에는 워크시트 범위에서 이 레이블의 텍스트를 가져오라는 뜻의 요소가 없습니다. 계열 안의 <c:dLbls> 블록인 CT_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는 <c:dLbls> 끝의 URI {CE6537A1-D6FC-4f65-9D91-7224C49458BB} 확장에 앉고, 같은 확장이 개별 <c:dLbl>을 닫아 한 포인트의 범위 텍스트를 켜거나 끌 수도 있습니다

두 절반은 각자 따로 실패합니다. 범위만 있고 스위치가 없는 파일은 셀 텍스트를 보여 주지 않고, 스위치만 있고 범위가 없는 파일은 보여 줄 것이 없습니다. §21.2가 정의하는 것만 모델링하는 프로그램은 차트 파트를 다시 만들 때 둘 다 잃습니다. 그래서 HotXLS는 편집을 거쳐도 모델링하지 않은 ChartML을 지켜 냅니다. 캐시는 참조만큼 중요합니다. 범위를 해석하지 못하는 소비자, 가령 다른 통합 문서를 가리키는 범위의 경우에도, 그릴 텍스트는 필요하니까요

계열에 레이블 범위 설정하기

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이 아니라 20.0으로 읽힙니다
    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이 레이블을 제자리에 놓습니다. 위 예제에서 기대되는 것: 각 막대가 끝 바깥에 셀 텍스트와 소수 한 자리 값을 단 레이블을 달고, 두 부분은 | 로 이어집니다

포인트 단위 레이블은 무엇을 되풀이해야 하는가?

계열 레이블이 말하는 모든 것입니다. 스키마는 포인트 오버라이드를 값싸 보이게 만듭니다. CT_DLbl은 idx에, 그 뒤를 delete나 레이블 그룹(같은 numFmt부터 separator까지의 이어짐 앞에 선택적인 layout과 tx)과 선택적인 확장 목록이 따르는 꼴이고, 그룹의 모든 멤버가 선택적입니다. 고로 idx와 showDataLabelsRange 확장만 담은 <c:dLbl>은 스키마상 유효합니다. Excel은 그런 레이블을 20.0 대신 S1; 2; 20으로 렌더링합니다. 빠진 show* 스위치를 켜진 것으로 다루어 계열 이름과 범주가 나타나고, 계열 숫자 서식도 물려받지 않습니다. 포인트 레이블은 그 포인트의 계열 레이블을 고치는 게 아니라 대체합니다. Excel 자신이 쓰는 포인트 레이블이 스위치 전체 세트를 싣는 까닭이 여기 있습니다. XML을 작게 유지하려 최소 오버라이드만 내놓는 생성기는 검증을 통과하면서도 잘못된 텍스트를 보여 줍니다

HotXLS는 모든 포인트 레이블을 전체 대체로 씁니다. SetPointShowDataLabelsRange(APointIndex, AVisible)는 idx가 되는 0부터 셈하는 포인트 인덱스를 키로, 한 포인트의 범위 텍스트 켜짐과 꺼짐 값을 기록합니다. 저장 시점에 그런 포인트마다 <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에서는 잘 되는 차트가 다운스트림 파이프라인을 깨뜨릴 수 있다는 뜻입니다. 만들어진 차트 파트는 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 전에 쓰인 데이터 참조, trendline 앞의 errBars, 데이터 앞의 smooth이며, Excel은 어느 것에도 불평하지 않습니다

수동 레이블 위치는 포인트 레이블에 들어갑니다

레이아웃은 직관과 스키마가 갈라지는 지점입니다. 사용자가 Excel에서 레이블을 드래그하면 수동 위치는 그 레이블의 <c:dLbl> 안, idx 바로 뒤에 <c:layout>으로 저장됩니다. CT_DLbls에는 layout 자식이 없으므로, 전체 세트의 위치라고 기대하며 <c:dLbls> 바로 아래에 쓰는 생성기는 검증기가 잘못된 자식으로 지적하는 파일을 만들어 냅니다. Excel은 그 파일을 열고 아무도 부탁하지 않은 짓을 합니다. 레이아웃을 첫 레이블에만 적용해 idx 0 포인트 레이아웃이 놓을 자리에 놓고, 나머지 레이블들의 폭을 잘못 읽어 37.7이던 레이블 폭이 52.3으로 돌아옵니다. 차트는 거의 옳게 보이므로 검토에서 놓치기 쉬운 실수입니다

확장에서 복사해 온 레이아웃은 통합 문서 전체를 열 수 없게 만들 수 있습니다. Excel은 레이블의 확장 목록 안에 c15:layout을 담을 수도 있는데, c15 접두사는 그 조각이 아니라 감싸는 <c:ext>에 선언됩니다. 로컬 이름으로 레이아웃을 찾는 코드는 그 요소도 건져 올리고, 그것을 <c:dLbls>에 붙여 넣으면 선언 없는 접두사가 따라 들어와 재조립된 차트 파트가 선언되지 않은 네임스페이스 접두사를 지니게 되어, 네임스페이스를 아는 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와 포인트 오버라이드 호출, 보존된 레이아웃 속성 뒤에 앉아 있습니다