XLSX kimutatásmező-séma érvényessége Delphiben, HotXLS-sel

A HotXLS olyan XLSX kimutatásdefiníciókat ír, amelyek pivotField és cacheField elemei megfelelnek az ECMA-376 Part 1 §18.10 sémának: az axis attribútumok a ST_Axis axisRow, axisCol és axisPage tokenjeit használják, az értékterület mezői dataField="1"-et hordanak, az elemlisták sosem üresek, a cache mezők pedig numerikus numFmtId-t tárolnak. A v2.384.33 óta az olvasó aztán tiszteli azokat a séma-alapértelmezéseket is, amelyeket korábban félreolvasott

A takarítás mögötti hibák közös, kevésbé hízelgő vonást osztoznak: egyik sem buktatott meg soha tesztet. A HotXLS írt egy kimutatást, a HotXLS visszaolvasta, minden mező a helyes tengelyre landolt, és a round trip csomag éveken át zöld maradt. A baj az volt, hogy az író és az olvasó csendben megegyezett egy magándialektusban. A Delphiből rakott kimutatás annak a komponensnek rendben nézett ki, amely elkészítette, miközben a CT_PivotField-del és a CT_CacheField-del végzett ellenőrzés érvénytelen enumerációs tokeneket, egy sémában tiltott üres elemet és olyan jelzőket hozott felszínre, amelyeket az Excel vár, de sosem kapott. Ha kimutatásokat generál szerveren, és olyan embereknek szállítja, akik Excelben nyitják meg vagy saját parserüknek adják tovább, az egyetlen szerződés, amely számít, a séma, nem pedig az, amit a saját olvasója éppen megbocsát

Miért nem lepte meg soha a rossz axis tokeneket a HotXLS round tripje?

A HotXLS round tripjei azért nem buktatták meg soha a rossz axis tokeneket, mert az olvasó mindkét írásmódot elfogadta. A régi XlsxPivotAxisAttr axis="rowAxis"-t, colAxis-t és pageAxis-t bocsátott ki, amelyek angolul természetesen olvasnak, de a sémában nem léteznek; a ST_Axis pontosan négy értéket definiál: axisRow, axisCol, axisPage és axisValues. Közben a lxPivotXml.pas-ban élő PivotAxisFromToken a séma tokenre és a kitalált tokenre is egyezett, így minden önteszt átment. Az író ma már csak a séma tokenjeit bocsátja ki, az olvasó pedig továbbra is elfogadja a régi írásmódokat, hogy a korábbi HotXLS verziók által mentett fájlok elrendezésük épségében töltődjenek be

<!-- a v2.384.33 előtt: érvénytelen ST_Axis érték, üres CT_Items -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- a v2.384.33 óta -->
<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 követel meg a CT_PivotField, amit a régi író kihagyott?

A CT_PivotField három dolgot követel meg, amelyet a régi BuildPivotTableXml kihagyott vagy elrontott. Először: az értékterületen aggregált mezőnek ezt a saját definícióján kell kimondania dataField="1"-gyel; az író ma már minden olyan mezőre ráteszi ezt a jelzőt, amelyre a DataFields egy bejegyzése hivatkozik, nemcsak a <dataFields> listában. Másodszor: a CT_Items-nek legalább egy item-re van szüksége, így az elem nélküli mező már nem kap üres <items count="0">-t, hanem a teljes elem egyszerűen kimarad. Harmadszor: minden elem megőrzi az állapotát: h="1" a rejtett elemhez (TXLSPivotItem.IsHidden) és sd="0" a becsukott részletekhez (IsDetailHidden), mindkettőt a régi író minden mentésnél elhajított

A finom részletet a záró részösszeg-elemek jelentik. Ha egy mezőnek vannak elemei, az Excel minden részösszegfüggvényre egy plusz item-et sorol fel az adatelemek után, ST_ItemType típussal: <item t="default"/> az automatikus részösszeghez, majd sum, countA, avg, max, min, product, count, stdDev, stdDevP, var és varP az explicitokhoz. A HotXLS ezeket a bejegyzéseket mentéskor a TXLSPivotField.Subtotals-ből származtatja, és az items count-ba is beszámolja. Az AddPivotTable által kreált mezők üres Subtotals halmazzal indulnak, ami defaultSubtotal="0"-t ír és záró elemet sem, ezért ha a riportnak kellenek a részösszegek, explicit kérje őket. Ügyeljen az elnevezési csapdára: az xlpsCount a countA-ra képeződik le (minden bejegyzés), az xlpsCountNums pedig a count-ra (csak számok)

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-alapú, mint az XLS motornál
    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, ha nincs ilyen mező
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // a Revenue-ra dataField="1" jelzőt tesz

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

Hogyan olvassa ma a HotXLS a részösszeg-elemeket és a séma alapértelmezéseit?

A HotXLS olvasója ma már átugor minden olyan item-et, amelynek t attribútuma jelen van, és nem data, mert a részösszeg-, nagysumma- és üresbejegyzések nem hordanak cache indexet. A v2.384.34 előtt ezek a bejegyzések közönséges elemekként töltődtek be, -1-re állított CacheItemIndex-szel, így egy Excel-készítette kimutatás szellemtagokkal jött vissza, amelyek sehová nem mutattak, és minden Items-en végigsétáló kódnak kézzel kellett kiszűrnie őket. Mivel az író a záró bejegyzéseket a Subtotals-ből építi újra, az olvasó dolga az, hogy ezeket arra a halmazra fordítsa vissza, ne pedig adatként tartsa őket

A második olvasói javítás a hiányzó attribútumokról szól. A sémában a CT_PivotField defaultSubtotal-ja és a CT_SharedItems containsString-je egyaránt true-ba alapértelmezett, és az Excel akkor hagyja ki őket, ha ezt az alapértelmezést hordozzák. A HotXLS a hiányzó attribútumot false-ként olvasta, ami azt jelentette, hogy az Excel által mentett minden kimutatás csendben elvesztette az alapértelmezett részösszegét betöltéskor, és egy sima szöveges cache mező vegyes osztályba került, karakterlánc helyett. Ez az axis hiba tükörképe: egy író, amely mindig kiírja az összes attribútumot, sosem járja az alapértelmezési utat, így csak egy másik gyártótól származó fájlokon bukik elő

Miért volt érvénytelen a numFmtId="General" a cache mezőkön?

A numFmtId="General" azért volt érvénytelen, mert a ST_NumFmtId előjel nélküli egész, nem formátumnév. A régi cache író minden cacheField-re bedrótozta ezt a karakterláncot, kölcsönvéve a nevet, amelyet a felhasználók a Cellák formázása párbeszédben látnak. A HotXLS ma már a cache mező NumberFormat-ját számként írja, ami 0 (a beépített General formátum), hacsak valami nem állította be. Egy szigorú parser, amely az attribútumokat a séma szerint típusozza, a régi értéket lapból elutasítja, és pontosan ez az a hibaosztály, amelyből javító párbeszéd lesz; az Excel javítási felszólítása mögötti OPC és markup szabályokról szóló cikk bemutatja, hogyan keletkeznek ezek a párbeszédek

Miért vágták le a 65535. sor alatti kimutatásokat?

A 65536. sorra vagy alá helyezett XLSX kimutatások azért lettek levágva, mert a közös pivot modell a FirstRow-t, a LastRow-t, a FirstHeaderRow-t, a FirstDataRow-t és az oszlopmegfelelőiket Word-ként tárolta, a sorcsúsztató kód pedig Min(.., High(Word))-zel szorította őket. Ez a BIFF8 SxView rekordjának maradványa, ahol 16 bit elég, de egy XLSX munkalap 1 048 576 sorig fut. A v2.384.37 óta ezek a tulajdonságok a TXLSPivotTable-ön Integer-ek, a szorítások eltűntek, és csak a BIFF8 író szűkíti az értékeket. A TXLSXWorksheet.AddPivotTable és az AddPivotTableCopy ma már nil-t ad az 1..1048576 × 1..16384-en kívüli horgonyra, illetve olyan másolatra, amelynek kiterjedése lecsúszna a rácson

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // A 70001. sor korábban átfordult a 16 bites tartományba; ma már túléli a mentést és a betöltést
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // a munkalapon kívüli horgony vagy feloldhatatlan forrástartomány
  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;

A klasszikus XLS motor a v2.384.38-ban kapta meg a párhuzamos javítást. A modellje korábban a nyers, nullaalapú SxView és DConRef értékeket tárolta, és az AddPivotTable horgonyokat egyenesen átengedte, miközben a dokumentáció, a demók és az XLSX motor mind 1-alapú cellákat használt, mint a Cells[Row, Col]. Mindkét motor ma már 1-alapú pozíciókat tart a modellben, a BIFF8 olvasó hozzáad 1-et, az író levon 1-et a rekordhatáron, így a (0, 0)-ra horgonyzó kódnak át kell állnia (1, 1)-re, mert a klasszikus AddPivotTable ma már nil-t ad az 1..65536 × 1..256-on kívüli horgonyra; az új hívás ugyanazokat a bájtokat írja, mint a régi. Maga a rekordelrendezés változatlan, és a klasszikus .xls kimutatások mögötti BIFF8 SX rekordokról szóló cikk írja le

Ellenőrizzen a séma szerint, ne a saját olvasóján

A tanulság a kimutatásokon túl általánosít: egy elnéző olvasó elrejti az író szabálytalanságait, így a saját kódján átmenő round trip konzisztenciát bizonyít, nem helyességet. Az itt felsorolt minden hiba azért élte túl, mert a toleráns oldal és a hibás oldal ugyanabban a könyvtárban lakott. Az ellenőrzések, amelyek tényleg elkapják ezt a hibaosztályt: a generált részek sémaellenőrzése, az Excel által gyártott, az alapértelmezett értékű attribútumaiktól megfosztott fájlok áttolása a saját olvasón, és olyan tesztállományok, amelyek a pontos tokent szögezik le, nem az elemzett eredményt. Az API-n át épített kimutatások, a számított mezőkkel épülő és frissülő XLSX kimutatásokról szóló cikkben mutatott számított mezők, számított elemek és százalékos elrendezések is beleértve, kódmódosítás nélkül megkapják a javított XML-t, az Excel-fájlokból betöltött kimutatások pedig addig játsszák vissza az eredeti részeiket, amíg módosítani nem kezdi őket

Mindezek a javítások a jelenlegi HotXLS Delphi táblázatkezelő komponensben érhetők el, amely XLS-t, XLSX-et és kimutatásokat olvas és ír Delphi és C++Builder alól, a gépen Excel vagy COM automatizálás nélkül