Schema-validiteit van XLSX-pivotvelden in Delphi met HotXLS

HotXLS schrijft XLSX-pivottabeldefinities waarvan de pivotField- en cacheField-elementen valideren tegen het schema van ECMA-376 Part 1 §18.10: axis-attributen gebruiken de ST_Axis-tokens axisRow, axisCol en axisPage, velden in het waardegebied dragen dataField="1", itemlijsten zijn nooit leeg, en cachefelden bewaren een numerieke numFmtId. Sinds v2.384.33 eert de reader ook de schema-standaardwaarden die hij voorheen verkeerd interpreteerde

De bugs achter deze opruiming delen een onflatteuze trek: geen enkele faalde ooit een test. HotXLS schreef een pivottabel, HotXLS las haar terug, elk veld belandde op de juiste as, en de round-trip-suite bleef jarenlang groen. Het probleem was dat writer en reader geruisloos een privé-dialect met elkaar waren overeengekomen. Een vanuit Delphi gebouwde pivottabel zag er prima uit voor de component die haar maakte, terwijl een toets aan CT_PivotField en CT_CacheField ongeldige enumeratietokens opleverde, een leeg element dat het schema verbiedt, en vlaggen die Excel verwacht maar nooit kreeg. Genereert u pivots op een server en stuurt ze naar mensen die ze in Excel openen of aan hun eigen parsers voeren, dan is het enige contract dat telt het schema, niet wat uw eigen reader toevallig vergeeft

Waarom vingen HotXLS-roundtrips de verkeerde axis-tokens nooit in?

HotXLS-roundtrips vingen de verkeerde axis-tokens nooit omdat de reader beide schrijfwijzen accepteerde. De oude XlsxPivotAxisAttr zond axis="rowAxis", colAxis en pageAxis uit, wat in het Engels natuurlijk leest maar in het schema niet bestaat; ST_Axis definieert precies vier waarden, axisRow, axisCol, axisPage en axisValues. Ondertussen matchte PivotAxisFromToken in lxPivotXml.pas zowel het schematoken als het verzonnen, dus elke zelftest slaagde. De writer zendt nu alleen de schematokens uit, en de reader blijft de oude schrijfwijzen accepteren zodat bestanden van oudere HotXLS-versies nog steeds met hun indeling intact laden

<!-- vóór v2.384.33: ongeldige ST_Axis-waarde, lege CT_Items -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

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

Wat eist CT_PivotField dat de oude writer oversloeg?

CT_PivotField eist drie dingen die de oude BuildPivotTableXml wegliet of verkeerd deed. Ten eerste moet een veld dat in het waardegebied wordt geaggregeerd dat op zijn eigen definitie zeggen met dataField="1"; de writer zet die vlag nu op elk veld waarnaar een entry in DataFields verwijst, niet alleen in de <dataFields>-lijst. Ten tweede heeft CT_Items minstens één item nodig, dus een veld zonder items krijgt geen lege <items count="0"> meer en het hele element wordt simpelweg weggelaten. Ten derde houdt elk item zijn staat vast: h="1" voor een verborgen item (TXLSPivotItem.IsHidden) en sd="0" voor ingeklapte details (IsDetailHidden), die de oude writer bij elke opslag liet vallen

Het subtiele zit in de achterste subtotaalitems. Heeft een veld items, dan zet Excel per subtotaalfunctie één extra item achter de data-items, getypeerd met ST_ItemType: <item t="default"/> voor het automatische subtotaal, daarna sum, countA, avg, max, min, product, count, stdDev, stdDevP, var en varP voor expliciete. HotXLS leidt die entries bij het opslaan af uit TXLSPivotField.Subtotals en telt ze mee in items count. Velden gemaakt door AddPivotTable beginnen met een lege Subtotals-set, wat defaultSubtotal="0" en geen achterste item schrijft, dus vraag expliciet om subtotalen als het rapport ze nodig heeft. Let op de naamval: xlpsCount mapt naar countA (alle entries) en xlpsCountNums mapt naar count (alleen getallen)

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, zoals de 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 als het veld niet bestaat
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // zet Revenue op dataField="1"

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

Hoe leest HotXLS subtotaalitems en schema-standaardwaarden nu?

De HotXLS-reader slaat nu elke item over waarvan het t-attribuut aanwezig is en niet data is, want subtotaal-, eindtotaal- en blanco-entries dragen geen cache-index. Vóór v2.384.34 werden die entries als gewone items geladen met CacheItemIndex op -1, dus een door Excel gemaakte pivottabel kwam terug met fantoomleden die nergens naartoe wezen, en elke code die Items afliep moest ze met de hand uitfilteren. Omdat de writer de achterste entries opnieuw opbouwt uit Subtotals, is de taak van de reader ze naar die set te vertalen, niet ze als data te houden

De tweede reader-fix gaat over afwezige attributen. In het schema gelden defaultSubtotal op CT_PivotField en containsString op CT_SharedItems beide standaard als true, en Excel laat ze weg wanneer ze die standaardwaarde dragen. HotXLS las een ontbrekend attribuut voorheen als false, wat betekende dat elke door Excel opgeslagen pivottabel bij het laden geruisloos zijn standaardsubtotaal verloor, en dat een puur tekstueel cachefeld als gemengd werd geclassificeerd in plaats van als string. Dit is de spiegel van de axis-bug: een writer die elk attribuut altijd uitschrijft oefent het standaardwaarden-pad nooit, dus alleen bestanden van een andere producent leggen het bloot

Waarom was numFmtId="General" ongeldig op cachefelden?

De waarde numFmtId="General" was ongeldig omdat ST_NumFmtId een unsigned integer is, geen formaatnaam. De oude cache-writer hardcodeerde die string op elke cacheField, lenend bij de naam die gebruikers in het dialoogvenster Cellen opmaken zien. HotXLS schrijft nu de NumberFormat van het cachefeld als getal, dus 0 (het ingebouwde General-formaat) tenzij er iets anders is ingesteld. Een strenge parser die attributen typt vanuit het schema wijst de oude waarde ronduit af, en dat is precies de soort fout die uitgroeit tot een hersteldialoog; het artikel over de OPC- en markup-regels achter de Excel-herstelvraag behandelt hoe die dialogs worden getriggerd

Waarom werden pivottabellen op of onder rij 65535 afgekapt?

XLSX-pivottabellen geplaatst op of onder rij 65536 werden afgekapt omdat het gedeelde pivotmodel FirstRow, LastRow, FirstHeaderRow, FirstDataRow en de kolom-tegenhangers als Word bewaarde, en de rij-verschuivingscode ze klemzette met Min(.., High(Word)). Dat is een restant van het BIFF8-SxView-record, waar 16 bits genoeg zijn, maar een XLSX-werkblad loopt tot 1.048.576 rijen. Sinds v2.384.37 zijn die eigenschappen op TXLSPivotTable Integer, de klemmen zijn weg, en alleen de BIFF8-writer vernauwt de waarden nog. TXLSXWorksheet.AddPivotTable en AddPivotTableCopy geven nu nil terug voor een anker buiten 1..1048576 bij 1..16384, of voor een kopie waarvan de omvang van het rooster af zou lopen

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Rij 70001 sloeg voorheen om in het 16-bits bereik; nu overleeft ze opslag en laden
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // anker buiten het werkblad of onherleidbaar bronbereik
  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;

De klassieke XLS-engine kreeg de bijpassende fix in v2.384.38. Zijn model bewaarde voorheen de ruwe zero-based SxView- en DConRef-waarden en gaf AddPivotTable-ankers ronduit door, terwijl de documentatie, de demo's en de XLSX-engine allemaal one-based cellen zoals Cells[Row, Col] gebruikten. Beide engines houden nu one-based posities in het model, de BIFF8-reader telt er 1 bij op en de writer telt er 1 af op de recordgrens, dus code die op (0, 0) ankerde moet naar (1, 1), want de klassieke AddPivotTable geeft nu nil terug voor een anker buiten 1..65536 bij 1..256; de nieuwe aanroep schrijft dezelfde bytes als de oude. De recordindeling zelf is onveranderd en staat beschreven in de BIFF8-SX-records achter klassieke .xls-pivottabellen

Valideer tegen het schema, niet tegen uw eigen reader

De les generaliseert voorbij pivots: een coulante reader verbergt schendingen van de writer, dus een round trip door uw eigen code bewijst consistentie, geen correctheid. Elke bug hier overleefde omdat de tolerante kant en de foutieve kant in dezelfde library woonden. De controles die deze klasse defecten werkelijk vangen zijn een schemavalidatie van de gegenereerde onderdelen, door Excel gemaakte bestanden die met op standaardwaarden weggelaten attributen door uw reader gaan, en fixtures die het exacte token vastpinten in plaats van het geparseerde resultaat. Pivots die u via de API bouwt, inclusief de berekende velden, berekende items en percent-van-totaal-indelingen uit het bouwen en verversen van XLSX-pivottabellen met berekende velden, krijgen de gecorrigeerde XML zonder codewijziging, terwijl pivots uit Excel-bestanden hun originele onderdelen blijven herafspelen tot u ze wijzigt

Al deze fixes zitten in de huidige HotXLS Delphi-spreadsheetcomponent, die vanuit Delphi en C++Builder XLS, XLSX en pivottabellen leest en schrijft zonder Excel of COM-automatisering op de machine