Техническая статья

Валидность полей сводных XLSX по схеме в Delphi с HotXLS

Атрибут axis="rowAxis" на элементе pivotField читается как совершенно разумный XML, и читатель, смапивший его в «поле строки», загрузит сводную со всеми полями на правой оси. Прогоните ту же часть через валидатор, собранный из схемы ECMA-376 Part 1 §18.10, — он споткнётся о первый же атрибут: у ST_Axis ровно четыре токена, axisRow, axisCol, axisPage и axisValues, и rowAxis среди них нет. Писателю сводных приходится ублажать схему, а не собственного читателя: файл открывают люди с Excel и их собственными парсерами

Определения полей сводных полны правил вроде этого. Три имеют обыкновение проскальзывать мимо round-trip-теста: токены перечислений, требование, чтобы элемент items никогда не был пуст, и хвостовые записи промежуточных итогов, которые Excel перечисляет после data-записей. Ещё две сидят на читающей стороне — в атрибутах, которые Excel не пишет, потому что они держат своё схемное значение по умолчанию. Дальше в статье каждое правило разбирается, показано, как обязан выглядеть XML, и показаны вызовы Delphi, которые его порождают

Почему round trip не ловит неверный токен оси

Round trip доказывает, что писатель и читатель согласны друг с другом — и больше ничего. Если читатель принимает и axisRow, и rowAxis, писатель, выдающий неверный, пройдёт любую проверку «сохранил-загрузил», какую вы ни напишете: читатель чинит ошибку на пути назад. Бедствие всплывает, когда часть открывает потребитель построже — валидатор схемы, парсер, сгенерированный из XSD, или Excel, решивший, что часть подлежит починке

HotXLS разводит две стороны намеренно. XLSX-райтер выдаёт только четыре токена ST_Axis, а читатель сводных вдобавок мапит англоязычные на вид написания rowAxis, colAxis, pageAxis и dataAxis на соответствующую ось, так что файлы производителей, употребляющих их, грузятся с целой раскладкой. К выводу предъявляется схема; вход достаточно распознаваем. Обратная пара — строгий читатель с неряхой-писателем — хотя бы громко падала бы в ваших тестах; снисходительный читатель с неряхой-писателем не падает нигде, где вы видите

<!-- невалидно: rowAxis не является значением ST_Axis, и CT_Items не может быть пустым -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- валидно: токен ST_Axis, настоящие items, хвостовой промежуточный итог default -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>
Невалидный против валидного XML pivotField: выдуманное значение оси rowAxis и пустой элемент items нарушают CT_PivotField, тогда как HotXLS выпускает токены ST_Axis вроде axisRow с настоящими item-записями, сохранённым флагом скрытости и хвостовым промежуточным итогом default, который схема принимает
Снисходительный читатель, принимающий оба написания, пропускает каждый round trip, тогда как файл проваливает любую строгую проверку схемы — пишите только четыре токена ST_Axis и пусть CT_Items несёт хотя бы один item

Чего CT_PivotField ждёт помимо оси

У CT_PivotField есть ещё три требования, которые легко уронить. Поле, агрегируемое в области значений, обязано сказать это в собственном определении флагом dataField="1", хотя список <dataFields> уже ссылается на него. HotXLS ставит этот флаг каждому полю сводной, на которое указывает запись в DataFields, так что поле, добавленное через AddDataFieldByName, помечено в обоих местах

Второе правило структурное. CT_Items требует хотя бы одного ребёнка item, что делает <items count="0"></items> невалидным, хотя это well-formed XML и парсится везде. Когда у поля нет items, HotXLS не пишет весь элемент items целиком. Третье правило — о состоянии на каждом item: скрытый член несёт h="1", а член со свёрнутыми деталями — sd="0". Оба берутся из TXLSPivotItem.IsHidden и TXLSPivotItem.IsDetailHidden и пишутся при каждом сохранении, так что фильтр, выставленный в коде, доживает до Excel

Хвостовые items промежуточных итогов и ловушка count

Когда у поля есть items, Excel ведёт за data-записями по лишнему item на каждую функцию промежуточного итога, типизированную ST_ItemType. Автоматический итог — это <item t="default"/>; явные функции употребляют sum, countA, avg, max, min, product, count, stdDev, stdDevP, var и varP. Эти записи не несут индекса x в кэш, но входят в атрибут count у items. Писатель, их выбросивший, выдаёт список items, уже не совпадающий с флагами итогов на том же элементе

HotXLS выводит хвост из TXLSPivotField.Subtotals на момент сохранения: по одному item t="…" на функцию из набора, в порядке перечисления, с добавлением в count, плюс соответствующий атрибут вида sumSubtotal="1" на поле. Поля, созданные AddPivotTable, стартуют с пустым набором — пишется defaultSubtotal="0" и вовсе без хвостового item. Для генерируемых отчётов оставляйте набор пустым: строки итогов меняют высоту сводной и сдвигают всё, что лежит ниже. Добавляйте функции в Subtotals только на полях, где читателю нужны лишние строки

Именование заслуживает второго взгляда, прежде чем выбирать функцию. xlpsCount мапится в countA, считающий все непустые записи, а xlpsCountNums — в count, считающий одни числа. Кто прочтёт в XML «count» и потянется к xlpsCount, получит другую функцию

Анатомия списка items в HotXLS, где за data-записями следуют хвостовые items промежуточных итогов, выведенные из TXLSPivotField.Subtotals, вроде t=default и t=avg, и посчитанные в items count, с разъяснённой ловушкой именования xlpsCount в countA и xlpsCountNums в count
Поля из AddPivotTable стартуют с пустым набором Subtotals, что пишет defaultSubtotal=0 и без хвостового item — попросите нужные функции, и писатель выведет по одному item на функцию в count

Программа ниже строит сводную по существующему листу данных со строчным полем, колонковым полем и одним полем значений. Поле Region получает автоматический итог плюс среднее — это даёт два хвостовых items после его data-записей — и dataField="1" на Revenue

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // Sheets считается от 1
    Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 3, 6, 'RegionTotals');
    if Pivot = nil then
      raise Exception.Create('Bad source range or anchor');

    Region := Pivot.AddRowField('Region');    // nil, если поля нет
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // Revenue получает dataField="1"

    Book.SaveAs('orders-pivot.xlsx');
  finally
    Book.Free;
  end;
end;

Чтение того, что Excel не пишет

У читателя зеркальная беда. Поле сводной от Excel перечисляет свои записи итогов в items, и загрузить их обычными членами — значит подарить полю фантомные items, не указывающие ни на одно кэш-значение. HotXLS пропускает любой item, чей атрибут t присутствует и не равен data, — это покрывает записи промежуточных и общих итогов и пустые. Функции итогов приезжают через собственные флаги поля и оседают в Subtotals, так что код, гуляющий по Items, видит одних реальных членов, и каждый CacheItemIndex указывает на кэш-значение

Отсутствующие атрибуты — вторая половина. В схеме defaultSubtotal у CT_PivotField и containsString у CT_SharedItems по умолчанию true, и Excel их опускает, когда они держат это значение. HotXLS читает отсутствующий defaultSubtotal как true и добавляет xlpsDefault в Subtotals, когда поле не несёт явных флагов итогов, — ровно как само Excel показывает такое поле. Отсутствующий containsString тоже читается как true, так что кэш-поле простого текста классифицируется как строковые данные. Прочтите любой из атрибутов как false — и каждая сводная, сохранённая Excel, теряет при загрузке автоматические итоги, а текстовые поля обращаются в какой-то другой тип. Писатель, всегда выписывающий каждый атрибут, этого пути не касается; проходят его только файлы чужих производителей

Загруженная книга Excel — правильный тестовый вход здесь, ибо это единственный сорт файлов, оставляющих те атрибуты за бортом. Проверка ниже открывает такую и проходит строчное поле:

var
  Book : TXLSXWorkbook;
  Pivot: TXLSPivotTable;
  Fld  : TXLSPivotField;
  I, J : Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('saved-by-excel.xlsx');
    Pivot := Book.Sheets[1].PivotTables.FindByName('PivotTable1');
    if Pivot = nil then
      Exit;
    for I := 0 to Pivot.FieldCount - 1 do
    begin
      Fld := Pivot.Fields[I];
      if Fld.Axis <> xlpfaRowField then
        Continue;
      // Поле Excel без атрибутов итогов всё равно репортит дефолт
      if xlpsDefault in Fld.Subtotals then
        Writeln(Fld.Name, ': automatic subtotal');
      // Записи итогов пропущены, так что каждый item мапится в кэш-значение
      for J := 0 to Fld.ItemCount - 1 do
        Assert(Fld.Items[J].CacheItemIndex >= 0);  // проверка — предложение для ваших тестов
    end;
  finally
    Book.Free;
  end;
end;

Почему numFmtId="General" невалиден на кэш-полях

numFmtId="General" невалиден, потому что ST_NumFmtId — беззнаковое целое, как бы естественно ни одолжить имя, которое пользователи видят в диалоге Format Cells. Парсер, типизирующий атрибуты по схеме, отвергнет это имя сходу, а именно такой сорт отказа оборачивается в Excel диалогом починки; как вызываются эти диалоги, разобрано в статье правила OPC и разметки за приглашением Excel к починке. HotXLS пишет каждый cacheField с числовым numFmtId, взятым из NumberFormat кэш-поля, — это 0, встроенный формат General, если что-то не поставило иное

Сводным ниже строки 65535 нужны 32-битные позиции

Модель сводной, разделяемая движками XLS и XLSX, соблазняется одолжить ширины полей у записи BIFF8 SxView, где 16 бит покрывают любую строку классического листа. Лист XLSX дотягивает до 1 048 576 строк. Храните FirstRow, LastRow, FirstHeaderRow, FirstDataRow и колонковые двойни как Word да зажимайте через Min(Value, High(Word)) — и сводная, заякоренная на строке 70001, обрежется на 65535

На TXLSPivotTable эти свойства — Integer, и только BIFF8-райтер сужает их на границе записи. TXLSXWorksheet.AddPivotTable возвращает nil для якоря вне 1..1048576 по 1..16384, а AddPivotTableCopy — nil, когда протяжённость копии сбежала бы с сетки

Якорь сводной в HotXLS на строке 70001 против 16-битного потолка, где FirstRow и LastRow, хранимые как Word и зажимаемые через Min против High(Word) на 65535, обрезали сводные на этой линии и ниже, тогда как HotXLS держит их полями Integer с возвратом nil вне сетки
Строчные поля шириной Word подходят записям BIFF8 SxView, но не листу, тянущемуся до 1048576 строк — с ними якорь за строкой 65535 зажимается в 65535, и сводная уже не сидит там, куда её положили
var
  Book, Check: TXLSXWorkbook;
  Pivot      : TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    // Строка 70001 за пределами 16-битного диапазона
    Pivot := Book.Sheets[1].AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
    if Pivot = nil then
      Exit;  // якорь вне листа или неразрешимый исходный диапазон
    Pivot.AddRowField('Region');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);
    Book.SaveAs('late.xlsx');
  finally
    Book.Free;
  end;

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    // Assert — проверка, предложенная для ваших тестов
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

Классический XLS-движок держится той же конвенции от единицы. Записи SxView и DConRef хранят значения с нуля, тогда как модель, подобно XLSX-движку и Cells[Row, Col], держит позиции от единицы: BIFF8-читатель прибавляет 1, райтер отнимает 1 на границе записи. Классический AddPivotTable возвращает nil для якоря вне 1..65536 по 1..256, так что якорьте сводные в (1, 1), а не в (0, 0). Сама раскладка записей разобрана в статье записи BIFF8 SX за классическими сводными .xls

Тестировать против схемы, а не собственного читателя

Каждая ловушка выше переживает round trip, когда снисходительная сторона и виноватая живут в одной библиотеке. Ловят их три сорта проверок. Валидация по схеме порождённых частей pivotTableN.xml и pivotCacheDefinitionN.xml ловит выдуманные токены, пустые элементы items и именованные id форматов. Файлы, сохранённые Excel и прогнанные через вашего читателя, упражняют атрибуты, которые Excel опускает на дефолтах. Ассерты, прибивающие точный токен в записанном XML — вроде axis="axisRow", — ловят писателя, дрейфующего к написанию, которое понимает только ваш читатель; ассерт на распарсенной оси — нет

Сводные, построенные через API — включая вычисляемые поля, вычисляемые items и раскладки «процент от итога», показанные в статье построение и обновление сводных XLSX с вычисляемыми полями, — выходят с этими правилами, уже применёнными, и без лишнего кода на вашей стороне. Остаётся вам работа попроще: выбрать, каким полям положены итоги, и какую из функций count вы имеете в виду

HotXLS Delphi spreadsheet component строит, читает и сохраняет сводные XLS и XLSX из кода Delphi и C++Builder — без установки Excel и COM-автоматизации на машине, где работает