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

HotXLS пишет определения сводных таблиц XLSX, чьи элементы pivotField и cacheField проходят валидацию по схеме ECMA-376 Part 1 §18.10: атрибуты оси используют токены ST_Axis — axisRow, axisCol и axisPage, поля области значений несут dataField="1", списки items никогда не пусты, а cache-поля хранят числовой numFmtId. С v2.384.33 читатель ещё и уважает умолчания схемы, которые раньше понимал неверно

Баги, стоящие за этой чисткой, делят нелицеприятную черту: ни один не провалил ни единого теста. HotXLS писал сводную, HotXLS читал её обратно, каждое поле оказывалось на своей оси, и round-trip-набор оставался зелёным годами. Проблема была в том, что писатель и читатель тихо договорились о частном диалекте. Сводная, собранная из Delphi, выглядела прекрасно для компонента, который её сделал, а проверка по CT_PivotField и CT_CacheField находила невалидные токены перечислений, пустой элемент, запрещённый схемой, и флаги, которых Excel ждёт, но не получает. Если вы генерируете сводные на сервере и отгружаете их людям, которые открывают их в Excel или скармливают собственным парсерам, единственный контракт, который имеет значение, — это схема, а не то, что прощает ваш собственный читатель

Почему round-trip HotXLS никогда не ловил неверные токены осей?

Round-trip HotXLS не ловил неверные токены осей, потому что читатель принимал оба написания. Старый XlsxPivotAxisAttr выпускал axis="rowAxis", colAxis и pageAxis — по-английски читается естественно, но в схеме таких значений нет: ST_Axis определяет ровно четыре, axisRow, axisCol, axisPage и axisValues. При этом PivotAxisFromToken в lxPivotXml.pas матчил и токен схемы, и выдуманный, так что каждый самотест проходил. Писатель теперь выпускает только токены схемы, а читатель продолжает принимать старые написания, чтобы файлы, сохранённые прежними версиями HotXLS, по-прежнему грузились с целой раскладкой

<!-- до v2.384.33: невалидное значение ST_Axis, пустой CT_Items -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- начиная с v2.384.33 -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>

Что требует CT_PivotField, чего старый писатель не делал?

CT_PivotField требует трёх вещей, которые старый BuildPivotTableXml упускал или делал неверно. Во-первых, поле, агрегируемое в области значений, обязано сказать это в собственном определении через dataField="1"; писатель теперь ставит тот флаг на каждом поле, на которое ссылается запись в DataFields, а не только в списке <dataFields>. Во-вторых, CT_Items нужен хотя бы один item, так что поле без items больше не получает пустой <items count="0">, а весь элемент просто опускается. В-третьих, каждый item сохраняет своё состояние: h="1" для скрытого item (TXLSPivotItem.IsHidden) и sd="0" для свёрнутых деталей (IsDetailHidden) — оба флага старый писатель терял при каждом сохранении

Тонкая часть — хвостовые items промежуточных итогов. Когда у поля есть items, Excel перечисляет по одному дополнительному item на каждую функцию итога после data-элементов, типизированных через ST_ItemType: <item t="default"/> для автоматического итога, затем sum, countA, avg, max, min, product, count, stdDev, stdDevP, var и varP для явных. HotXLS выводит эти записи из TXLSPivotField.Subtotals в момент сохранения и считает их в items count. Поля, созданные через AddPivotTable, стартуют с пустым набором Subtotals, что пишет defaultSubtotal="0" без хвостового item, так что запрашивайте итоги явно, когда отчёту они нужны. Заметьте ловушку именования: xlpsCount мапится в countA (все записи), а xlpsCountNums — в count (только числа)

uses
  lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // с единицы, как в XLS-движке
    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;

Как HotXLS теперь читает items итогов и умолчания схемы?

Читатель HotXLS теперь пропускает любой item, чей атрибут t присутствует и не равен data, потому что записи итогов, общих итогов и пустышек не несут индекса кэша. До v2.384.34 те записи грузились как обычные items с CacheItemIndex равным -1, поэтому сводная, сделанная Excel, возвращалась с фантомными членами, не указывающими никуда, и любому коду, идущему по Items, приходилось отфильтровывать их вручную. Раз писатель перестраивает хвостовые записи из Subtotals, задача читателя — перевести их в тот набор, а не хранить как данные

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

Почему numFmtId="General" был невалиден на cache-полях?

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

Почему сводные таблицы ниже строки 65535 обрезались?

Сводные таблицы XLSX, поставленные на строку 65536 или ниже, обрезались, потому что общая pivot-модель хранила FirstRow, LastRow, FirstHeaderRow, FirstDataRow и их столбцовые аналоги как Word, а код сдвига строк зажимал их через Min(.., High(Word)). Это наследник записи BIFF8 SxView, где 16 битов хватает, но лист XLSX тянется до 1 048 576 строк. С v2.384.37 те свойства на TXLSPivotTable стали Integer, зажимы исчезли, и сужает значения только BIFF8-писатель. TXLSXWorksheet.AddPivotTable и AddPivotTableCopy теперь возвращают nil для якоря вне 1..1048576 по 1..16384 или для копии, чей охват выехал бы за сетку

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Строка 70001 раньше заворачивалась в 16 бит; теперь переживает save и load
  Pivot := Sheet.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');

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

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

Валидируйте по схеме, а не по собственному читателю

Урок обобщается за пределы сводных: снисходительный читатель прячет нарушения писателя, так что round-trip через собственный код доказывает согласованность, а не корректность. Каждый здешний баг выжил потому, что терпимая сторона и сломанная сторона жили в одной библиотеке. Проверки, которые реально ловят этот класс дефектов, — это валидация сгенерированных частей по схеме, файлы, произведённые Excel, прогнанные через ваш читатель с опущенными на умолчаниях атрибутами, и фикстуры, пришпиливающие точный токен, а не распарсенный результат. Сводные, которые вы строите через API, включая calculated fields, calculated items и раскладки percent-of-total из статьи о построении и обновлении сводных таблиц XLSX с calculated fields, получают исправленный XML без единой правки кода, а сводные, загруженные из файлов Excel, продолжают воспроизводить свои оригинальные части, пока вы их не измените

Все эти исправления едут в текущем HotXLS Delphi spreadsheet component, который читает и пишет XLS, XLSX и сводные таблицы из Delphi и C++Builder без Excel и COM automation на машине