Подписи Excel Value From Cells живут в расширении c15

После того как генератор переписывает диаграмму, подписи, которые должны показывать столбец 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. Почти каждый флажок в панели Format Data Labels 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, теряет обе половины, когда регенерирует часть диаграммы, — вот зачем 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.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. Для примера выше ожидаемый результат: каждая полоса подписана снаружи своего конца текстом ячейки и значением с одним знаком после запятой, обе части соединены через |

Что должна повторять поточечная подпись?

Всё, что говорит подпись ряда. Схема делает поточечное переопределение выглядящим дешёвым: CT_DLbl — это idx, за которым следует либо delete, либо группа подписи (необязательные layout и tx впереди того же прогона от numFmt до separator) и необязательный список расширений, и каждый член группы необязателен. Поэтому <c:dLbl>, держащий только idx и расширение showDataLabelsRange, валиден по схеме. Excel отрисовывает такую подпись как S1; 2; 20 вместо 20.0: каждый пропущенный переключатель show* он считает включённым, так что имя ряда и категория проявляются, а формат чисел ряда не наследуется. Поточечная подпись заменяет подпись ряда для своей точки, а не патчит её, — вот почему поточечные подписи, которые пишет сам Excel, несут полный набор переключателей. Генератор, выпускающий минимальные переопределения ради компактного XML, проходит валидацию и всё равно показывает неправильный текст

HotXLS пишет каждую поточечную подпись как полную замену. SetPointShowDataLabelsRange(APointIndex, AVisible) записывает включённое или выключенное значение текста диапазона для одной точки, с ключом по индексу точки с нуля, который становится idx. При сохранении каждая такая точка получает один <c:dLbl>: idx, формат чисел ряда, позицию и переключатели в порядке группы, а последним расширение {CE6537A1-…}, несущее значение этой точки. SetPointLabelText(APointIndex, AText) добавляет буквальный текст через <c:tx>; индекс с текстом, переопределением диапазона и сохранённым layout всё равно получает один <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;

<!-- Ожидаемый XML для точки 1 -->
<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. Другие потребители менее снисходительны. OpenXmlValidator из Open XML SDK сообщает о потомках не по порядку как об ошибках схемы, и инструменты, жёстко привязывающиеся к схеме, их отвергают, так что диаграмма, которая «работает в 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; линейный ряд имеет marker там, где полосной ряд имеет invertIfNegative и pictureOptions, и smooth там, где имеет shape
• Точечные, площадные и пузырьковые ряды сохраняют тот же скелет (подписи, тренды, планки погрешностей и данные, в этом порядке) со своими членами: xVal и yVal для данных точечных и пузырьковых, а bubbleSize и bubble3D после данных в пузырьковом ряде
• Обычные промахи уровня ряда — ссылки на данные, записанные перед dLbls, errBars впереди trendline и smooth впереди данных, — и ни на один из них Excel не жалуется

Ручные позиции подписей живут на поточечной подписи

Layout — то место, где интуиция и схема расходятся. Когда пользователь перетаскивает подпись в Excel, ручная позиция сохраняется как <c:layout> внутри <c:dLbl> той подписи, сразу после idx. У CT_DLbls потомка layout нет, поэтому генератор, пишущий его прямо под <c:dLbls> в расчёте расставить весь набор, производит файл, который валидатор помечает как недопустимого потомка. Excel открывает такой файл и делает то, о чём никто не просил: применяет layout только к первой подписи, кладя её туда, куда положила бы поточечная layout точки idx 0, и неверно читает ширину остальных подписей, так что ширина подписи 37.7 возвращается как 52.3. Диаграмма выглядит почти правильно, и ошибку легко пропустить на ревью

Layout, скопированный из расширения, способен сделать неречитаемой всю книгу. Excel может также держать c15:layout внутри списка расширений подписи, и префикс c15 объявлен на объемлющем <c:ext>, а не на самом фрагменте. Код, ищущий layout по локальному имени, подберёт и этот элемент, и вставка его в <c:dLbls> утащит префикс без его объявления, так что пересобранная часть диаграммы будет содержать необъявленный префикс пространства имён, и ни один XML-парсер с поддержкой пространств имён её не загрузит. Сопоставляйте layout по пространству имён и по положению прямого потомка, никогда по одному локальному имени

HotXLS берёт layout подписи только из прямого потомка <c:layout> у <c:dLbls> или у <c:dLbl>, никогда изнутри расширения, и хранит его дословно с ключом по индексу точки. LabelLayoutXml держит layout первой подписи, включая бродячий, найденный прямо под <c:dLbls>, — Excel именно так его и толкует; PointLabelLayoutCount, PointLabelLayoutIndexes и PointLabelLayoutXmls перечисляют остальные. При регенерации диаграммы тот layout попадает на <c:dLbl> с idx 0, а каждый прочий — на <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];               // индекс листа с единицы
    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, поточечными вызовами переопределений и свойствами сохранённого layout