XLSX pivot-kenttien skeemanmukaisuus Delphissä HotXLS:llä

HotXLS kirjoittaa XLSX-pivot-taulukoiden määritelmät joiden pivotField- ja cacheField-elementit valideeravat ECMA-376 Part 1 §18.10-skeemaa vasten: akseliattribuutit käyttävät ST_Axis-tokeneja axisRow, axisCol ja axisPage, arvoalueen kentät kantavat dataField="1"ia, itemilistat eivät ole koskaan tyhjiä, ja cache-kentät tallentavat numeerisen numFmtIdin. Versiosta v2.384.33 alkaen lukija kunnioittaa myös niitä skeeman oletuksia joita se sai aiemmin väärin

Tämän siivouksen taustalla olevilla bugeilla on imartelematon piirre: yksikään niistä ei koskaan kaatanut testiä. HotXLS kirjoitti pivotin, HotXLS luki sen takaisin, jokainen kenttä laskeutui oikealle akselille, ja round trip -testisarja pysyi vihreänä vuosia. Ongelma oli, että kirjoittaja ja lukija olivat hiljaisena sopineet omasta murteesta. Delphistä rakennettu pivot näytti hyvältä sen komponentin silmiin joka sen teki, kun taas tarkistus CT_PivotFieldia ja CT_CacheFieldia vasten paljasti virheellisiä luettelointitokeneja, tyhjän elementin jonka skeema kieltää ja lippuja joita Excel odotti mutta ei koskaan saanut. Jos generoit pivoteja palvelimella ja toimitat niitä ihmisille jotka avaavat ne Excelissä tai syöttävät niitä omiin jäsentimiinsä, ainoa sopimus joka merkitsee on skeema, ei se mitä oma lukijasi sattuu anteeksi antamaan

Miksi HotXLS:n round tripit eivät koskaan nähneet vääriä akselitokeneja?

HotXLS:n round tripit eivät koskaan nähneet vääriä akselitokeneja, koska lukija hyväksyi molemmat kirjoitusasut. Vanha XlsxPivotAxisAttr emittoi muodot axis="rowAxis", colAxis ja pageAxis, jotka luettiin luontevasti englanniksi mutta joita ei ole skeemassa; ST_Axis määrittelee täsmälleen neljä arvoa, axisRow, axisCol, axisPage ja axisValues. Samaan aikaan tiedoston lxPivotXml.pas PivotAxisFromToken täsmäsi sekä skeematokenin että keksityn, joten jokainen itsetesti meni läpi. Kirjoittaja emittoi nyt vain skeematokenit, ja lukija hyväksyy yhä vanhat kirjoitusasut, jotta aiempien HotXLS-versioiden tallentamat tiedostot latautuvat yhä asettelun ehjänä

<!-- ennen v2.384.33:a: virheellinen ST_Axis-arvo, tyhjä CT_Items -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- v2.384.33:stä alkaen -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>

Mitä CT_PivotField vaatii mitä vanha kirjoittaja ohitti?

CT_PivotField vaatii kolme asiaa jotka vanha BuildPivotTableXml jätti pois tai sai väärin. Ensiksi, arvoalueella agregatoituvan kentän on sanottava se omassa määritelmässään dataField="1"illa; kirjoittaja asettaa nyt kyseisen lipun jokaiselle kentälle johon DataFieldsin merkintä viittaa, ei vain <dataFields>-listassa. Toiseksi, CT_Items tarvitsee vähintään yhden itemin, joten itemitön kenttä ei enää saa tyhjää <items count="0">a vaan koko elementti jätetään yksinkertaisesti pois. Kolmanneksi, jokainen item säilyttää tilansa: h="1" piilotetulle itemille (TXLSPivotItem.IsHidden) ja sd="0" supistetuille yksityiskohdille (IsDetailHidden), molempia joita vanha kirjoittaja pudotti jokaisella tallennuksella

Hienovarainen osa ovat häntävälisumma-itemit. Kun kentällä on itemeitä, Excel listaa yhden ylimääräisen itemin jokaista välisummafunktiota kohden data-itemien jälkeen, tyypitettynä ST_ItemTypeilla: <item t="default"/> automaattiselle välisumalle, sitten sum, countA, avg, max, min, product, count, stdDev, stdDevP, var ja varP eksplisiittisille. HotXLS johtaa kyseiset merkinnät TXLSPivotField.Subtotalsista tallennushetkellä ja laskee ne items countin sisään. AddPivotTablein luomat kentät alkavat tyhjällä Subtotals-joukolla, mikä kirjoittaa defaultSubtotal="0"in eikä häntäitemiä, joten pyydä välisummat eksplisiittisesti kun raportti tarvitsee niitä. Huomaa nimennän ansa: xlpsCount mapautuu arvoon countA (kaikki merkinnät) ja xlpsCountNums arvoon count (vain numerot)

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-pohjainen, kuten XLS-koneessa
    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 jos kenttää ei ole
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // asettaa Revenuelle dataField="1":n

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

Miten HotXLS lukee välisumma-itemit ja skeeman oletukset nyt?

HotXLS:n lukija ohittaa nyt jokaisen itemin jonka t-attribuutti on läsnä ja ei ole data, koska välisumma-, kokonaissumma- ja tyhjämerkinnät eivät kanna cache-indeksiä. Ennen v2.384.34:ää kyseiset merkinnät ladattiin tavallisina itemeina CacheItemIndex arvolla -1, joten Excelillä tehty pivot palasi aavejäsenien kanssa jotka eivät osoittaneet mihinkään, ja kaiken Itemsia kävelevän koodin piti suodattaa ne käsin. Koska kirjoittaja rakentaa häntämerkinnät uudelleen Subtotalsista, lukijan tehtävä on kääntää ne kyseiseksi joukoksi, ei pitää niitä datana

Toinen lukijakorjaus koskee attribuutteja jotka ovat poissa. Skeemassa defaultSubtotal arvolla CT_PivotField ja containsString arvolla CT_SharedItems kumpikin oletuvat arvoon true, ja Excel jättää ne pois kun ne kantavat kyseistä oletusta. HotXLS luki puuttuvan attribuutin arvona false, mikä tarkoitti että jokainen Excelin tallentama pivot menetti oletusvälisummansa hiljaisena ladattaessa, ja pelkkää tekstiä kantava cache-kenttä luokiteltiin miksatuksi eikä merkkijonoksi. Tämä on akselibugin peilikuva: kirjoittaja joka kirjoittaa aina jokaisen attribuutin auki ei koskaan harjoita oletuspolkua, joten vain toisen tuottajan tiedostot paljastavat sen

Miksi numFmtId="General" oli virheellinen cache-kentillä?

Arvo numFmtId="General" oli virheellinen koska ST_NumFmtId on etumerkitön kokonaisluku, ei muodon nimi. Vanha cache-kirjoittaja kovakoodasi kyseisen merkkijonon jokaiselle cacheFieldille, lainaten nimen jonka käyttäjät näkevät Muotoile solut -dialogissa. HotXLS kirjoittaa nyt cache-kentän NumberFormatin numerona, joka on 0 (sisäänrakennettu General-muoto) ellei jokin asettanut sitä. Tiukka jäsentin joka tyypittää attribuutit skeemasta hylkää vanhan arvon suoraan, ja se on täsmälleen se virheluokka joka muuttuu korjausdialogiksi; artikkeli Excelin korjauskehotteen takana olevista OPC- ja merkkaussäännöistä kattaa miten kyseiset dialogit laukeavat

Miksi rivin 65535 alapuolella olleet pivot-taulukot leikkautuivat?

Riville 65536 tai sitä alemmas sijoitetut XLSX-pivot-taulukot leikkautuivat, koska jaettu pivot-malli tallensi FirstRowin, LastRowin, FirstHeaderRowin, FirstDataRowin ja sarakevastineet tyypin Word arvoina, ja rivisiirtokoodi lukitsi ne funktiolla Min(.., High(Word)). Se on BIFF8:n SxView-tietueen jäänne, jossa 16 bittiä riittää, mutta XLSX-taulukko jatkuu 1 048 576 riviin. Versiosta v2.384.37 alkaen kyseiset propertyt TXLSPivotTableissa ovat tyyppiä Integer, lukitukset on poistettu, ja vain BIFF8-kirjoittaja kaventaa arvot. TXLSXWorksheet.AddPivotTable ja AddPivotTableCopy palauttavat nyt nilin ankkurille alueen 1..1048576 kertaa 1..16384 ulkopuolella tai kopiolle jonka laajuus juoksisi ruudukon yli

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Rivi 70001 kiertyi aiemmin 16-bittiselle alueelle; nyt se selviää tallennuksesta ja latauksesta
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // ankkuri taulukon ulkopuolella tai selvittämätön lähdealue
  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;

Klassinen XLS-kone sai vastaavan korjauksen versiossa v2.384.38. Sen malli tallensi raakat 0-pohjaiset SxView- ja DConRef-arvot ja päästi AddPivotTable-ankkurit suoraan läpi, kun taas dokumentaatio, demot ja XLSX-kone käyttivät kaikkia 1-pohjaisia soluja kuten Cells[Row, Col]. Molemmat koneet pitävät nyt 1-pohjaisia positioita mallissa, BIFF8-lukija lisää 1:n ja kirjoittaja vähentää 1:n tietuerajalla, joten kohta (0, 0) ankkuroinut koodi on siirryttävä kohtaan (1, 1), koska klassinen AddPivotTable palauttaa nyt nilin ankkurille alueen 1..65536 kertaa 1..256 ulkopuolella; uusi kutsu kirjoittaa samat tavut kuin vanha. Tietueasettelu sinänsä on muuttumaton ja on kuvattu artikkelissa klassisten .xls-pivot-taulukoiden BIFF8 SX -tietueista

Validoi skeemaa vasten, ei omaa lukijaasi

Oppi yleistyy pivotien ulkopuolelle: salliva lukija kätkii kirjoittajan rikkeet, joten round trip oman koodin läpi todistaa yhtenäisyyttä, ei oikeellisuutta. Jokainen tämän artikkelin bugi selvisi hengissä koska salliva puoli ja viallinen puoli asuivat samassa kirjastossa. Tarkistukset jotka oikeasti saavat kiinni tämän virheluokan ovat generoitujen osien skeemavalidointi, Excelin tuottamien tiedostojen syöttäminen lukijasi läpi attribuuteilla jotka on jätetty pois oletusarvoissaan, ja fixtuurit jotka lukitsevat tarkan tokenin jäsennetyn tuloksen sijaan. API:n kautta rakentamasi pivotit, mukaan lukien lasketut kentät, lasketut itemit ja prosenttia-yhteensä-asettelut jotka artikkeli XLSX-pivot-taulukoiden rakentamisesta ja päivittämisestä lasketuilla kentillä näyttää, saavat korjatun XML:n ilman koodimuutoksia, kun taas Excel-tiedostoista ladatut pivotit toistavat alkuperäisiä osiaan kunnes muokkaat niitä

Kaikki nämä korjaukset kulkevat mukana nykyisessä HotXLS Delphi Excel -komponentissa, joka lukee ja kirjoittaa XLS-, XLSX- ja pivot-taulukoita Delphistä ja C++Builderista ilman Exceliä tai COM automationia koneella