Skjemavaliditet for XLSX pivot-felt i Delphi med HotXLS

HotXLS skriver XLSX-pivottabelldefinisjoner der pivotField- og cacheField-elementene validerer mot skjemaet i ECMA-376 Part 1 §18.10: akse-attributter bruker ST_Axis-tokens axisRow, axisCol og axisPage, felt i verdiområdet bærer dataField="1", item-lister er aldri tomme, og cache-felter lagrer en numerisk numFmtId. Siden v2.384.33 ærer leseren også skjema-standardverdiene den pleide å ta feil på

Feilene bak denne oppryddingen deler et lite smigrende trekk: ingen av dem feilet noensinne en test. HotXLS skrev en pivot, HotXLS leste den tilbake, hvert felt landet på riktig akse, og rundtur-pakken forble grønn i årevis. Problemet var at writer og leser hadde blitt enige om en privat dialekt. En pivot bygget fra Delphi så fin ut for komponenten som lagde den, mens en sjekk mot CT_PivotField og CT_CacheField avdekket ugyldige enumereringstokens, et tomt element skjemaet forbyr og flagg Excel forventet men aldri fikk. Genererer du pivoter på en server og sender dem til folk som åpner dem i Excel eller mater dem inn i sine egne parsere, er den eneste kontrakten som teller skjemaet, ikke hva din egen leser tilfeldigvis tilgir

Hvorfor fanget HotXLS-rundturene aldri de gale akestokensene?

HotXLS-rundturer fanget aldri de gale akestokensene fordi leseren godtok begge stavemåtene. Den gamle XlsxPivotAxisAttr skrev axis="rowAxis", colAxis og pageAxis, som leser naturlig på engelsk men ikke finnes i skjemaet; ST_Axis definerer nøyaktig fire verdier, axisRow, axisCol, axisPage og axisValues. Samtidig matchet PivotAxisFromToken i lxPivotXml.pas både skjema-tokenen og den oppdiktede, så hver selvtest passerte. Writeren skriver nå bare skjema-tokens, og leseren fortsetter å godta de gamle stavemåtene, slik at filer lagret av tidligere HotXLS-versjoner fortsatt lastes med oppsettet intakt

<!-- før v2.384.33: ugyldig ST_Axis-verdi, tom 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>

Hva krever CT_PivotField som den gamle writeren hoppet over?

CT_PivotField krever tre ting den gamle BuildPivotTableXml utelot eller fikk feil. For det første må et felt aggregert i verdiområdet si så på sin egen definisjon med dataField="1"; writeren setter nå det flagget på hvert felt referert av en oppføring i DataFields, ikke bare i <dataFields>-listen. For det andre trenger CT_Items minst ett item, så et felt uten items får ikke lenger et tomt <items count="0">, og hele elementet utelates rett og slett. For det tredje beholder hvert item tilstanden sin: h="1" for et skjult item (TXLSPivotItem.IsHidden) og sd="0" for sammenlagte detaljer (IsDetailHidden), som begge ble sluppet av den gamle writeren ved hver lagring

Den subtile delen er de etterfølgende subtotal-items. Har et felt items, lister Excel ett ekstra item per subtotalfunksjon etter data-items, typet med ST_ItemType: <item t="default"/> for den automatiske subtotalen, så sum, countA, avg, max, min, product, count, stdDev, stdDevP, var og varP for de eksplisitte. HotXLS utleder disse oppføringene fra TXLSPivotField.Subtotals ved lagring og teller dem inn i items count. Felter laget av AddPivotTable starter med et tomt Subtotals-sett, som skriver defaultSubtotal="0" og ingen etterfølgende item, så be om subtotals eksplisitt når rapporten trenger dem. Merk navnefellene: xlpsCount mapper til countA (alle oppføringer) og xlpsCountNums mapper til count (bare tall)

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-basert, 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 ikke noe slikt felt finnes
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // flagger Revenue dataField="1"

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

Hvordan leser HotXLS subtotal-items og skjema-standardverdier nå?

HotXLS-leseren hopper nå over ethvert item hvis t-attributt er til stede og ikke er data, for subtotal-, grand total- og tomme-oppføringer bærer ingen cache-indeks. Før v2.384.34 ble disse oppføringene lastet som ordinære items med CacheItemIndex satt til -1, så en Excel-laget pivot kom tilbake med fantommedlemmer som pekte ingensteds, og enhver kode som gikk gjennom Items, måtte filtrere dem ut for hånd. Etter som writeren bygger opp de etterfølgende oppføringene fra Subtotals på nytt, er leserens jobb å oversette dem til det settet, ikke å beholde dem som data

Den andre leserfiksen handler om attributter som mangler. I skjemaet har defaultSubtotal på CT_PivotField og containsString på CT_SharedItems begge standardverdi true, og Excel utelater dem når de holder den standardverdien. HotXLS pleide å lese en manglende attributt som false, noe som betydde at hver pivot lagret av Excel stille mistet sin default-subtotal ved lesing, og et rent tekst-cachefelt ble klassifisert som blandet i stedet for streng. Dette er speilbildet av aksefeilen: en writer som alltid staver ut hver attributt, trener aldri standardveien, så bare filer fra en annen produsent avslører den

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

Verdien numFmtId="General" var ugyldig fordi ST_NumFmtId er et heltall uten fortegn, ikke et formatnavn. Den gamle cache-writeren hardkodet den strengen på hver cacheField, med navnet brukere ser i Format Cells-dialogen. HotXLS skriver nå cachefeltets NumberFormat som et tall, som er 0 (det innebygde General-formatet) med mindre noe har satt det. En streng parser som typer attributter fra skjemaet, avviser den gamle verdien kontant, og det er nøyaktig den feilklassen som blir til en reparasjonsdialog; artikkelen om OPC- og markup-reglene bak Excel-reparasjonsmeldingen dekker hvordan de dialogene utløses

Hvorfor ble pivottabeller på rad 65535 og lavere klippet av?

XLSX-pivottabeller plassert på rad 65536 eller lavere ble klippet av fordi den delte pivotmodellen lagret FirstRow, LastRow, FirstHeaderRow, FirstDataRow og kolonne-motpartene som Word, og radskiftkoden klemte dem med Min(.., High(Word)). Det er en rest av BIFF8 SxView-recorden, der 16 biter er nok, men et XLSX-ark går til 1 048 576 rader. Siden v2.384.37 er de egenskapene på TXLSPivotTable Integer, klemmene er borte, og bare BIFF8-writeren innsnevrer verdiene. TXLSXWorksheet.AddPivotTable og AddPivotTableCopy gir nå nil for et anker utenfor 1..1048576 x 1..16384, eller for en kopi hvis utstrekning ville løpe av rutenettet

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Rad 70001 pleide å wrappe inn i 16-bits-området; nå overlever den lagring og lesing
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // anker utenfor arket eller uoppløselig 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 fikk den matchende fiksen i v2.384.38. Modellen dens pleide å lagre de rå 0-baserte SxView- og DConRef-verdiene og sende AddPivotTable-ankere rett gjennom, mens dokumentasjonen, demoen og XLSX-motoren alle brukte 1-baserte celler som Cells[Row, Col]. Begge motorer beholder nå 1-baserte posisjoner i modellen, BIFF8-leseren legger til 1 og writeren trekker fra 1 ved record-grensen, så kode som ankret ved (0, 0), må flytte til (1, 1), for classic AddPivotTable gir nå nil for et anker utenfor 1..65536 x 1..256; det nye kallet skriver de samme bytene som det gamle. Record-oppsettet i seg selv er uendret og er beskrevet i BIFF8 SX-recordene bak classic .xls-pivottabeller

Valider mot skjemaet, ikke din egen leser

Lærdommen generaliserer utover pivoter: en ettergivende leser skjuler writer-brudd, så en rundtur gjennom din egen kode beviser konsistens, ikke korrekthet. Hver feil her overlevde fordi den tolerante siden og den feilfulle siden bodde i samme bibliotek. Kontrollene som faktisk fanger denne feilklassen, er en skjemavalidering av de genererte delene, filer produsert av Excel matet gjennom leseren din med attributter utelatt på standardverdiene sine, og fixtures som fester den eksakte tokenen i stedet for det parsede resultatet. Pivoter du bygger gjennom API-en, inkludert beregnede felt, beregnede items og prosent-av-total-oppsettene vist i å bygge og oppfriske XLSX-pivottabeller med beregnede felt, får den korrigerte XML-en uten kodeendring, mens pivoter lastet fra Excel-filer fortsetter å spille av sine originale deler til du endrer dem

Alle disse fiksene følger med i dagens HotXLS Delphi regnearkkomponent, som leser og skriver XLS, XLSX og pivottabeller fra Delphi og C++Builder uten Excel eller COM automation på maskinen