Поля pivot XLSX за схемою ECMA-376 у Delphi з HotXLS

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

Баги за цим чищенням ділять невтішну рису: жоден із них ніколи не провалював тест. HotXLS писав pivot, HotXLS читав його назад, кожне поле приземлялося на правильну вісь, і набір round-trip тестів був зеленим роками. Проблема була в тому, що writer і reader тихо змовилися на приватний діалект. Pivot, зібраний з Delphi, видавався гаразд компоненту, що його створив, тоді як перевірка проти CT_PivotField і CT_CacheField виявляла невалідні токени енумерації, порожній елемент, який схема забороняє, і прапорці, яких Excel чекає, але не отримував. Якщо ви генеруєте pivots на сервері й відправляєте їх людям, що відкривають їх в Excel чи згодовують власним парсерам, єдиний контракт, що має значення, — схема, а не те, що ваш власний читач випадково пробачає

Чому round trips HotXLS ніколи не ловили неправильні токени осей?

Round trips HotXLS ніколи не ловили неправильні токени осей, бо читач приймав обидва написання. Старий XlsxPivotAxisAttr емітував axis="rowAxis", colAxis і pageAxis, що природно читаються англійською, але не існують у схемі; ST_Axis визначає рівно чотири значення: axisRow, axisCol, axisPage і axisValues. Тим часом PivotAxisFromToken у lxPivotXml.pas матчив і токен схеми, і вигаданий, тож кожен self-test проходив. Письменник тепер емітує лише токени схеми, а читач досі приймає старі написання, щоб файли, збережені ранніми версіями HotXLS, досі завантажувалися з неушкодженим layout

<!-- до 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), — і те, й інше старий письменник викидував при кожному збереженні

Тонка частина — хвостові subtotal items. Коли поле має items, Excel перелічує один додатковий item на кожну функцію підсумків після записів даних, типізований через 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];                  // нумерація від 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 тепер читає subtotal items і усталені значення схеми?

Читач HotXLS тепер пропускає будь-який item, у якого атрибут t присутній і не дорівнює data, бо записи subtotal, grand total і blank не несуть індексу кешу. До v2.384.34 ті записи завантажувалися як звичайні items з CacheItemIndex = -1, тож pivot, зроблений Excel, повертався з фантомними членами, що нікуди не вказували, і будь-який код, що обходив Items, мусив відфільтровувати їх руками. Оскільки письменник перебудовує хвостові записи з Subtotals, робота читача — перевести їх у ту множину, а не тримати їх як дані

Друге виправлення читача — про атрибути, які відсутні. У схемі defaultSubtotal на CT_PivotField і containsString на CT_SharedItems обидва мають усталене значення true, і Excel опускає їх, коли вони тримають це усталене. HotXLS читав відсутній атрибут як false, через що кожен pivot, збережений Excel, мовчки втрачав свій subtotal за замовчуванням при завантаженні, а звичайне текстове cache field класифікувалося як mixed замість string. Це дзеркальне відображення бага осей: письменник, що завжди виписує кожен атрибут, ніколи не тренує шлях усталеного значення, тож його виставляють лише файли від іншого продюсера

Чому numFmtId="General" був невалідним на cache fields?

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

Чому pivot table нижче рядка 65535 обрізалися?

XLSX pivot table, поставлені на рядок 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. Його модель зберігала сирі 0-based значення SxView і DConRef і пропускала якорі AddPivotTable наскрізь, тоді як документація, демо і XLSX рушій усі використовували комірки від 1 на кшталт Cells[Row, Col]. Обидва рушії тепер тримають позиції від 1 у моделі, BIFF8 читач додає 1, а письменник віднімає 1 на межі запису, тож код, який якорився в (0, 0), мусить перейти на (1, 1), бо класичний AddPivotTable тепер повертає nil для якоря поза 1..65536 на 1..256; новий виклик пише ті самі байти, що й старий. Сам layout запису не змінився і описаний у статті про записи BIFF8 SX за класичними pivot table у .xls

Валідуйте за схемою, а не за власним читачем

Урок узагальнюється за межі pivots: поблажливий читач ховає порушення письменника, тож round trip крізь власний код доводить узгодженість, а не коректність. Кожен баг тут вижив, бо толерантний бік і дефектний бік жили в одній бібліотеці. Перевірки, що справді ловлять цей клас дефектів, — це валідація згенерованих частин за схемою, файли, зроблені Excel, прогнані крізь ваш читач із пропущеними атрибутами на усталених значеннях, і фікстури, що пришпилюють точний токен, а не результат розбору. Pivots, які ви будуєте через API, зокрема calculated fields, calculated items і layout-и «відсоток від суми», показані в побудові й оновленні XLSX pivot table з calculated fields, отримують виправлений XML без жодної зміни коду, тоді як pivots, завантажені з файлів Excel, досі програють свої оригінальні частини, поки ви їх не зміните

Усі ці виправлення виходять у поточному компоненті електронних таблиць HotXLS для Delphi, який читає і пише XLS, XLSX і pivot table з Delphi та C++Builder без Excel чи COM automation на машині