XLSX pivot laukų schemos teisingumas Delphi su HotXLS

HotXLS rašo XLSX pivot lentelių apibrėžimus, kurių pivotField ir cacheField elementai praeina ECMA-376 Part 1 §18.10 schemą: ašių atributai naudoja ST_Axis tokenus axisRow, axisCol ir axisPage, reikšmių srityje agreguoti laukai neša dataField="1", item sąrašai niekada neužsitraukia tušti, o cache laukai saugo skaitinį numFmtId. Nuo v2.384.33 skaitytojas taip pat gerbia schemos numatytąsias reikšmes, kurias anksčiau suprasdavo klaidingai

Šio sutvarkymo bugai dalijasi nepageidaujamu bruožu: nė vienas kada nesugedino jokio testo. HotXLS parašo pivot, HotXLS jį perskaito, kiekvienas laukas atsiduria teisingoje ašyje, ir round-trip rinkinys metų metus lieka žalias. Bėda ta, kad rašytojas ir skaitytojas ramiai susitarė dėl savos tarmės. Iš Delphi sukurtas pivot komponentui, jį pagaminusiam, atrodė geras, o patikra prieš CT_PivotField ir CT_CacheField atkapstydavo neteisėtus enumerated tokenus, schemos draudžiamą tuščią elementą ir vėliavėles, kurių Excel tikisi, bet niekada negaudavo. Jei pivotus generuojate serveryje ir siunčiate žmonėms, kurie juos atveria Excel ar kiša į savo parserius, vienintelis skaitomas kontraktas yra schema, o ne tai, ką jūsų skaitytojas netyčia atleistų

Kodėl HotXLS round trip niekada nepagaudavo neteisėtų ašių tokenų?

HotXLS round trip niekada nepagaudavo neteisėtų ašių tokenų, nes skaitytojas priimdavo abiejų rašybų. Senas XlsxPivotAxisAttr išduodavo axis="rowAxis", colAxis ir pageAxis, kurie anglųkai skaitosi natūraliai, bet schemoje neegzistuoja; ST_Axis apibrėžia lygiai keturias reikšmes, axisRow, axisCol, axisPage ir axisValues. Tuo metu PivotAxisFromToken iš lxPivotXml.pas atpažindavo ir schemos tokeną, ir išgalvotąjį, tad kiekvienas savęs patikrinimas pereidavo. Rašytojas dabar išduoda tik schemos tokenus, o skaitytojas toliau priima senąsias rašybas, kad ankstesnių HotXLS versijų išsaugoti failai vis dar įkeltų su nepaliestu išdėstymu

<!-- iki v2.384.33: neteisėta ST_Axis reikšmė, tuščias CT_Items -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

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

Ko CT_PivotField reikalauja, o senas rašytojas praleisdavo?

CT_PivotField reikalauja trijų dalykų, kurių senas BuildPivotTableXml palikdavo nuošaly arba darydavo neteisingai. Pirma, reikšmių srityje agreguotas laukas privalo tai pasakyti savo pačio apibrėžime su dataField="1"; rašytojas dabar tą vėliavėlę stato kiekvienam laukui, kurį rodo DataFields įrašas, o ne tik <dataFields> sąraše. Antra, CT_Items reikia bent vieno item, todėl laukas be item daugiau negauna tuščio <items count="0">, o visas elementas tiesiog praleidžiamas. Trečia, kiekvienas item išlaiko savo būseną: h="1" paslėptam item (TXLSPivotItem.IsHidden) ir sd="0" suskleistoms detalėms (IsDetailHidden) — abi, kurias senas rašytojas išmesdavo kiekvieno įrašymo metu

Subtilioji dalis — galiniai subtotal item. Kai laukas turi item, Excel po duomenų item surašo po vieną papildomą item kiekvienai subtotal funkcijai, tipuotus ST_ItemType: <item t="default"/> automatiniam subtotal, tada sum, countA, avg, max, min, product, count, stdDev, stdDevP, var ir varP aiškiems subtotal. HotXLS tuos įrašus išveda iš TXLSPivotField.Subtotals įrašymo metu ir suskaičiuoja juos į items count. AddPivotTable sukurti laukai prasideda su tuščiu Subtotals rinkiniu, kuris rašo defaultSubtotal="0" ir jokio galinio item, tad kai ataskaitai subtotal reikia, paprašykite jų aiškiai. Pastebėkite vardų spąstus: xlpsCount atitinka countA (visi įrašai), o xlpsCountNums — count (tik skaičiai)

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];                  // nuo 1, kaip XLS variklyje
    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, jei tokio lauko nėra
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // pažymi Revenue dataField="1"

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

Kaip HotXLS dabar skaito subtotal item ir schemos numatytąsias reikšmes?

HotXLS skaitytojas dabar praleidžia bet kurį item, kurio t atributas yra ir nelygus data, nes subtotal, grand total ir tušti įrašai neneša jokio cache indekso. Iki v2.384.34 tie įrašai būdavo įkeliami kaip paprasti item su CacheItemIndex -1, tad Excel sukurtas pivot sugrįždavo su vaiduokliais nariais, kurie niekur nerodė, o bet kuris kodas, einantis per Items, turėdavo juos išsirinkti rankomis. Kadangi rašytojas galinius įrašus perkuria iš Subtotals, skaitytojo darbas — juos išversti atgal į tą rinkinį, o ne laikyti kaip duomenis

Antra skaitytojo pataisa — apie atributus, kurie nesami. Schema defaultSubtotal ties CT_PivotField ir containsString ties CT_SharedItems abiejų numatytoji reikšmė yra true, ir Excel juos praleidžia, kai jie laiko tą numatytąją. HotXLS nesantį atributą skaitė kaip false, todėl kiekvienas Excel išsaugotas pivot įkeliant tyliai prarasdavo savo numatytąjį subtotal, o paprastas tekstų cache laukas būdavo klasifikuojamas kaip mišrus vietoj string. Tai ašių bugo veidrodiškas atvaizdas: rašytojas, kuris visada išrašo kiekvieną atributą, niekada nepraeina numatytosios reikšmės kelio, tad jį atveria tik kito gamintojo failai

Kodėl numFmtId="General" buvo neteisėtas cache laukams?

Reikšmė numFmtId="General" buvo neteisėta, nes ST_NumFmtId yra beženklis sveikasis skaičius, o ne formato vardas. Senas cache rašytojas tą string reikšmę kietai įkalindavo kiekviename cacheField, pasiskolindamas vardą, kurį vartotojai mato Format Cells dialoge. HotXLS dabar rašo cache lauko NumberFormat kaip skaičių, kuris yra 0 (įtaisytasis General formatas), nebent kažkas jį būtų nustatęs. Griežtas parseris, tipuojantis atributus pagal schemą, seną reikšmę atmeta iš karto, ir būtent tokios rūšies nesėkmė virsta remonto dialogu; straipsnis apie OPC ir markup taisykles už Excel remonto raginimo pasakoja, kaip tie dialogai sužadinami

Kodėl pivot lentelės žemiau 65535 eilutės būdavo nukerpamos?

XLSX pivot lentelės, padėtos 65536 eilutėje ar žemiau, būdavo nukerpamos, nes bendras pivot modelis FirstRow, LastRow, FirstHeaderRow, FirstDataRow ir stulpelių atitikmenis saugojo kaip Word, o eilučių slinkimo kodas juos gniauždavo su Min(.., High(Word)). Tai BIFF8 SxView įrašo palikimas, kur 16 bitų užtenka, bet XLSX lapas eina iki 1 048 576 eilučių. Nuo v2.384.37 tos TXLSPivotTable savybės yra Integer, gniaužimų nebėra, ir tik BIFF8 rašytojas susiaurina reikšmes. TXLSXWorksheet.AddPivotTable ir AddPivotTableCopy dabar grąžina nil inkarui už 1..1048576 pagal 1..16384 ribų arba kopijai, kurios apimtis išbėgtų už tinklelio

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Eilutė 70001 anksčiau persisuodavo į 16 bitų diapazoną; dabar išgyvena įrašymą ir įkėlimą
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // inkaras už lapo ribų arba neišspręstas šaltinio diapazonas
  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;

Klasikinis XLS variklis atitinkamą pataisą gavo v2.384.38. Jo modelis anksčiau saugodavo žalias nuo nulio einančias SxView ir DConRef reikšmes ir AddPivotTable inkarus praleisdavo tiesiai, nors dokumentacija, demonstracijos ir XLSX variklis visi naudojo langelius nuo 1, tokius kaip Cells[Row, Col]. Abu varikliai dabar modelyje laiko pozicijas nuo 1, BIFF8 skaitytojas prideda 1, o rašytojas įrašo riboje atima 1, tad kodas, inkaravęs ties (0, 0), turi persikelti į (1, 1), nes klasikinis AddPivotTable dabar grąžina nil inkarui už 1..65536 pagal 1..256 ribų; naujas kvietimas rašo tuos pačius baitus kaip senasis. Pats įrašo išdėstymas nepakitęs ir aprašytas BIFF8 SX įrašuose už klasikinių .xls pivot lentelių

Tikrinkite prieš schemą, o ne prieš savąjį skaitytoją

Pamoka dėliojaosi plačiau už pivotus: kantrus skaitytojas slepia rašytojo pažeidimus, tad round trip per savą kodą įrodo nuoseklumą, o ne teisingumą. Kiekvienas čia esantis bugas išgyveno todėl, kad pakantioji ir sugedusioji pusės gyveno toje pačioje bibliotekoje. Patikros, kurios tokios rūšies defektus iš tiesų pagauna, yra sugeneruotų dalių schemos validacija, Excel pagaminti failai, praleisti per jūsų skaitytoją su atributais, praleistais ties numatytosiomis reikšmėmis, ir fixture, priseginantys tikslų tokeną, o ne išskaidytą rezultatą. Pivotai, sukurti per API, įskaitant calculated fields, calculated items ir percent-of-total išdėstymus, parodytus kaip kurti ir atnaujinti XLSX pivot lenteles su calculated fields, gauna pataisytą XML be jokio kodo pakeitimo, o pivotai, įkelti iš Excel failų, toliau atkartoja savo originalias dalis, kol jų nepaliesite

Visos šios pataisos išvyko į dabartinę HotXLS Delphi skaičiuoklės komponento versiją, kuri iš Delphi ir C++Builder skaito ir rašo XLS, XLSX bei pivot lenteles be Excel ir be COM automation mašinoje