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

HotXLS записва XLSX дефиниции на pivot таблици, чиито елементи pivotField и cacheField валидират срещу схемата ECMA-376 Part 1 §18.10: axis атрибутите ползват token-ите ST_Axis axisRow, axisCol и axisPage, полетата в value зоната носят dataField="1", списъците с items никога не са празни, а cache полетата пазят числов numFmtId. От v2.384.33 reader-ът отчита и схемните default-и, които преди дървеше грешно

Бъговете зад тази почистка споделят неласкава черта: нито един не е провалил тест. HotXLS записва pivot, HotXLS го прочита обратно, всяко поле каца на правилния axis и round-trip suite-ът стои зелен години наред. Проблемът е, че writer и reader тихо се разбрали на собствен диалект. Pivot, построен от Delphi, изглежда наред на компонента, който го е направил, докато проверка срещу CT_PivotField и CT_CacheField показва невалидни enumeration token-и, празен елемент, който схемата забранява, и флагове, които Excel очаква, но никога не е получил. Ако генерирате pivot-и на сървър и ги изпращате на хора, които ги отварят в Excel или ги подават на собствени parser-и, единственият договор, който има значение, е схемата, а не онова, което вашият reader случайно прощава

Защо round trip-овете на HotXLS никога не хванаха грешните axis token-и?

Round trip-овете на HotXLS никога не хванаха грешните axis token-и, защото reader-ът приемаше и двете изписвания. Старият XlsxPivotAxisAttr излъчваше axis="rowAxis", colAxis и pageAxis, което се чете естествено на английски, но не съществува в схемата; ST_Axis дефинира точно четири стойности — axisRow, axisCol, axisPage и axisValues. Междувременно PivotAxisFromToken в lxPivotXml.pas съпоставяше и схемния token, и измисления, така че всеки self-test минаваше. Writer-ът вече излъчва само схемните token-и, а reader-ът продължава да приема старите изписвания, така че файлове, записани от по-ранни версии на 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, което старият writer прескачаше?

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

Финната част са опашните subtotal items. Когато поле има items, Excel изброява по един допълнителен item на subtotal функция след data items, типизирани с ST_ItemType: <item t="default"/> за автоматичния subtotal, после sum, countA, avg, max, min, product, count, stdDev, stdDevP, var и varP за изричните. HotXLS извежда тези записи от TXLSPivotField.Subtotals при запис и ги брои в items count. Полетата, създадени от AddPivotTable, тръгват с празно множество Subtotals, което записва defaultSubtotal="0" и без опашен item, така че искайте subtotal-ите изрично, когато отчетът има нужда от тях. Обърнете внимание на капана в имената: 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-based, като в XLS engine-а
    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 и схемните default-и сега?

Reader-ът на HotXLS вече прескача всеки item, чийто атрибут t е наличен и не е data, защото записите subtotal, grand-total и blank не носят cache индекс. Преди v2.384.34 тези записи се зареждаха като обикновени items с CacheItemIndex -1, така че pivot от Excel се връщаше с фантомни членове, които не сочеха никъде, а всеки код, минаващ през Items, трябваше да ги филтрира на ръка. Понеже writer-ът преизгражда опашните записи от Subtotals, работата на reader-а е да ги преведе обратно в това множество, а не да ги пази като данни

Вторият reader fix е за атрибути, които липсват. В схемата defaultSubtotal на CT_PivotField и containsString на CT_SharedItems имат и двата default true, а Excel ги пропуска, когато носят точно този default. HotXLS доскоро четеше липсващ атрибут като false, което означаваше, че всеки pivot, записан от Excel, тихо губи default subtotal-а си при зареждане, а обикновено текстово cache поле се класифицираше като mixed вместо string. Това е огледалният образ на axis бъга: writer, който винаги изписва всеки атрибут, никога не минава през пътя на default-а, така че само файлове от друг производител го показват

Защо numFmtId="General" беше невалидно на cache полетата?

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

Защо pivot таблиците под ред 65535 се отрязваха?

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

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Ред 70001 се обръщаше в 16-битовия диапазон; сега оцелява при запис и зареждане
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // котва извън листа или неустраним source диапазон
  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;

Classic XLS engine-ът получи съответния fix в v2.384.38. Моделът му пазеше суровите 0-based стойности на SxView и DConRef и пропускаше котвите на AddPivotTable направо, докато документацията, демотата и XLSX engine-ът ползваха 1-based клетки като Cells[Row, Col]. И двата engine-а вече държат 1-based позиции в модела, BIFF8 reader-ът прибавя 1, а writer-ът вади 1 на границата на record-а, така че код, котвен на (0, 0), трябва да се премести на (1, 1), защото класическият AddPivotTable вече връща nil за котва извън 1..65536 по 1..256; новото извикване записва същите байтове като старото. Самият record layout не се е променил и е описан в BIFF8 SX record-ите зад класическите .xls pivot таблици

Валидирайте срещу схемата, не срещу собствения ви reader

Урокът се обобщава отвъд pivot-ите: снизходителен reader крие нарушенията на writer-а, така че round trip през собствения ви код доказва съгласуваност, а не коректност. Всеки бъг тук оцеля, защото толерантната и виновната страна живееха в една и съща библиотека. Проверките, които реално хващат този клас дефекти, са schema валидация на генерираните части, файлове, произведени от Excel, пуснати през вашия reader с пропуснати атрибути на default-ите им, и fixtures, които заковат точния token, а не парснатия резултат. Pivot-ите, които строите през API-я, включително calculated полетата, calculated items и layout-ите процент-от-общо, показани в изграждането и refresh на XLSX pivot таблици с calculated полета, получават коригирания XML без промяна на кода, докато pivot-ите, заредени от Excel файлове, продължават да възпроизвеждат оригиналните си части, докато не ги промените

Всички тези fix-ове са в текущия HotXLS Delphi spreadsheet компонент, който чете и записва XLS, XLSX и pivot таблици от Delphi и C++Builder без Excel или COM автоматизация на машината