XLSX pivot-felt-skemavaliditet i Delphi med HotXLS

HotXLS skriver XLSX-pivottabeldefinitioner, hvis pivotField- og cacheField-elementer validerer mod ECMA-376 Part 1 §18.10-skemaet: axis-attributter bruger ST_Axis-tokens axisRow, axisCol og axisPage, værdiområdefelter bærer dataField="1", item-lister er aldrig tomme, og cache-felter gemmer et numerisk numFmtId. Siden v2.384.33 respekterer readeren også de skemadefaults, den plejede at tage fejl af

Bugsene bag denne oprydning deler et ubehageligt træk: ingen af dem fejlede nogensinde en test. HotXLS skrev en pivot, HotXLS læste den tilbage, hvert felt landede på den rigtige akse, og round-trip-suiten forblev grøn i årevis. Problemet var, at writer og reader stille var blevet enige om en privat dialekt. En pivot bygget fra Delphi så fin ud for komponenten, der lavede den, mens et tjek mod CT_PivotField og CT_CacheField afslørede ugyldige enumereringstokens, et tomt element, skemaet forbyder, og flag, Excel forventer men aldrig fik. Genererer du pivots på en server og sender dem til folk, der åbner dem i Excel eller føder dem til deres egne parsere, er den eneste kontrakt, der tæller, skemaet — ikke hvad din egen reader tilfældigvis tilgiver

Hvorfor fangede HotXLS' round trips aldrig de forkerte axis-tokens?

HotXLS' round trips fangede aldrig de forkerte axis-tokens, fordi readeren accepterede begge stavemåder. Den gamle XlsxPivotAxisAttr udsendte axis="rowAxis", colAxis og pageAxis, som læser naturligt på engelsk, men ikke findes i skemaet; ST_Axis definerer præcis fire værdier, axisRow, axisCol, axisPage og axisValues. I mellemtiden matchede PivotAxisFromToken i lxPivotXml.pas både skema-tokenet og det opdigtede, så hver selvværditest bestod. Writeren udsender nu kun skema-tokens, og readeren fortsætter med at acceptere de gamle stavemåder, så filer gemt af tidligere HotXLS-versioner stadig indlæses med deres layout intakt

<!-- før v2.384.33: ugyldig ST_Axis-værdi, tomt CT_Items -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- siden 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>

Hvad kræver CT_PivotField, som den gamle writer sprang over?

CT_PivotField kræver tre ting, som den gamle BuildPivotTableXml udelod eller fik forkert. For det første skal et felt, der aggregeres i værdiområdet, sige det på sin egen definition med dataField="1"; writeren sætter nu det flag på hvert felt, der refereres af en entry i DataFields, ikke kun i <dataFields>-listen. For det andet kræver CT_Items mindst ét item, så et felt uden items får ikke længere et tomt <items count="0">, og hele elementet udelades simpelthen. For det tredje beholdes hvert items tilstand: h="1" for et skjult item (TXLSPivotItem.IsHidden) og sd="0" for sammenklappede detaljer (IsDetailHidden), begge ting den gamle writer kasserede ved hver gemning

Den subtile del er de afsluttende subtotal-items. Når et felt har items, lister Excel ét ekstra item pr. subtotalfunktion efter data-items, typet med ST_ItemType: <item t="default"/> for den automatiske subtotal, derefter sum, countA, avg, max, min, product, count, stdDev, stdDevP, var og varP for de eksplicitte. HotXLS afleder de entries fra TXLSPivotField.Subtotals ved gemning og tæller dem med i items count. Felter oprettet af AddPivotTable starter med et tomt Subtotals-sæt, hvilket skriver defaultSubtotal="0" og intet afsluttende item, så bed om subtotals eksplicit, når rapporten har brug for dem. Bemærk navnefælden: xlpsCount mapper til countA (alle entries), og xlpsCountNums mapper til count (kun tal)

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-baseret, som XLS-motoren
    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, hvis feltet ikke findes
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // markerer Revenue som dataField="1"

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

Hvordan læser HotXLS subtotal-items og skemadefaults nu?

HotXLS-readeren springer nu ethvert item over, hvis t-attribut er til stede og ikke er data, for subtotal-, grand total- og tomme-entries bærer intet cacheindeks. Før v2.384.34 blev de entries indlæst som ordinære items med CacheItemIndex sat til -1, så en Excel-lavet pivot kom tilbage med fantommedlemmer, der pegede ingen vegne, og al kode, der gik igennem Items, måtte filtrere dem fra i hånden. Da writeren genopbygger de afsluttende entries fra Subtotals, er readerens opgave at oversætte dem til det sæt, ikke at beholde dem som data

Det andet reader-fix handler om attributter, der er fraværende. I skemaet har defaultSubtotal på CT_PivotField og containsString på CT_SharedItems begge default true, og Excel udelader dem, når de holder den default. HotXLS læste tidligere en manglende attribut som false, hvilket betød, at hver pivot gemt af Excel stille mistede sin default-subtotal ved indlæsning, og et almindeligt tekst-cachefelt blev klassificeret som mixed i stedet for string. Dette er spejlbilledet af axis-bugen: en writer, der altid staver alle attributter ud, øver aldrig default-vejen, så kun filer fra en anden producent afslører den

Hvorfor var numFmtId="General" ugyldigt på cache-felter?

Værdien numFmtId="General" var ugyldig, fordi ST_NumFmtId er et unsigned integer, ikke et formatnavn. Den gamle cache-writer hardkodede den streng på hvert cacheField og lånte navnet, brugerne ser i Format Cells-dialogen. HotXLS skriver nu cache-feltets NumberFormat som et tal, hvilket er 0 (det indbyggede General-format), medmindre noget har sat det. En streng parser, der typer attributter fra skemaet, afviser den gamle værdi på stedet, og det er netop den fejlklasse, der ender som en reparationsdialog; artiklen om the OPC and markup rules behind the Excel repair prompt gennemgår, hvordan de dialoger udløses

Hvorfor blev pivottabeller under række 65535 klippet af?

XLSX-pivottabeller placeret på eller under række 65536 blev klippet af, fordi den delte pivotmodel gemte FirstRow, LastRow, FirstHeaderRow, FirstDataRow og kolonnemodstykkerne som Word, og rækkeforskud-koden klemte dem med Min(.., High(Word)). Det er en rest af BIFF8 SxView-recorden, hvor 16 bit er nok, men et XLSX-ark løber til 1.048.576 rækker. Siden v2.384.37 er de egenskaber på TXLSPivotTable Integer, klemmerne er væk, og kun BIFF8-writeren indsnævrer værdierne. TXLSXWorksheet.AddPivotTable og AddPivotTableCopy returnerer nu nil for et anker uden for 1..1048576 gange 1..16384, eller for en kopi, hvis udstrækning ville løbe af gitteret

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Række 70001 wrap'ede tidligere ind i 16-bit-intervallet; nu overlever den gemning og indlæsning
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // anker uden for arket eller uopløseligt kildeområde
  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-motoren fik det matchende fix i v2.384.38. Dens model gemte tidligere de rå 0-baserede SxView- og DConRef-værdier og lod AddPivotTable-ankere passere direkte igennem, mens dokumentationen, demoerne og XLSX-motoren alle brugte 1-baserede celler som Cells[Row, Col]. Begge motorer holder nu 1-baserede positioner i modellen, BIFF8-readeren lægger 1 til, og writeren trækker 1 fra ved record-grænsen, så kode, der ankrede på (0, 0), skal flytte til (1, 1), for classic AddPivotTable returnerer nu nil for et anker uden for 1..65536 gange 1..256; det nye kald skriver samme bytes som det gamle. Record-layoutet selv er uændret og er beskrevet i de BIFF8 SX-records, der ligger bag classic .xls-pivottabeller

Validér mod skemaet, ikke din egen reader

Lærdommen generaliserer ud over pivots: en flink reader skjuler writer-overtrædelser, så en round trip gennem din egen kode beviser konsistens, ikke korrekthed. Enhver bug her overlevede, fordi den tolerante side og den fejlbehæftede side boede i samme bibliotek. De kontroller, der faktisk fanger denne defectklasse, er en skemavalidering af de genererede parts, filer produceret af Excel ført igennem din reader med attributter udeladt ved deres defaults, og fixtures, der fastlåser den eksakte token frem for det parsede resultat. Pivots, du bygger gennem API'en, inklusive de beregnede felter, beregnede items og percent-of-total-layouts vist i building and refreshing XLSX pivot tables with calculated fields, får den rettede XML uden kodeændring, mens pivots indlæst fra Excel-filer fortsætter med at afspille deres originale parts, til du ændrer dem

Alle disse fixes skibes i den nuværende HotXLS Delphi spreadsheet component, som læser og skriver XLS, XLSX og pivottabeller fra Delphi og C++Builder uden Excel eller COM automation på maskinen