Pola pivot XLSX zgodne ze schematem w Delphi z HotXLS

HotXLS zapisuje definicje tabel przestawnych XLSX, których elementy pivotField i cacheField przechodzą walidację względem schematu ECMA-376 Part 1 §18.10: atrybuty osi używają tokenów ST_Axis axisRow, axisCol i axisPage, pola obszaru wartości niosą dataField="1", listy itemów nigdy nie są puste, a pola cache trzymają numeryczne numFmtId. Od v2.384.33 czytnik respektuje też domyślne wartości schematu, które dawniej czytał źle

Błędy stojące za tym sprzątaniem dzielą mało chwalebną cechę: żaden nigdy nie oblał testu. HotXLS zapisywał pivot, HotXLS czytał go z powrotem, każde pole lądowało na właściwej osi, a zestaw testów round-tripu był zielony latami. Problem w tym, że pisarz i czytnik po cichu uzgodnili między sobą prywatny dialekt. Pivot zbudowany z Delphi wyglądał dobrze w oczach komponentu, który go zrobił, podczas gdy kontrola względem CT_PivotField i CT_CacheField wypluwała niepoprawne tokeny enumeracji, pusty element zakazany przez schemat i flagi, których Excel oczekiwał, a nigdy nie dostał. Jeśli generujesz pivotty na serwerze i wysyłasz je ludziom, którzy otwierają je w Excelu albo podają własnym parserom, jedynym kontraktem, który się liczy, jest schemat, a nie to, co akurat wybacza twój własny czytnik

Dlaczego round-tripy HotXLS nigdy nie złapały złych tokenów osi?

Round-tripy HotXLS nigdy nie złapały złych tokenów osi, bo czytnik akceptował obie pisownie. Stary XlsxPivotAxisAttr emitował axis="rowAxis", colAxis i pageAxis, co czyta się naturalnie po angielsku, ale nie istnieje w schemacie; ST_Axis definiuje dokładnie cztery wartości, axisRow, axisCol, axisPage i axisValues. Tymczasem PivotAxisFromToken w lxPivotXml.pas dopasowywał i token ze schematu, i ten wymyślony, więc każdy samotest przechodził. Pisarz emituje teraz wyłącznie tokeny schematu, a czytnik nadal akceptuje stare pisownie, żeby pliki zapisane przez wcześniejsze wersje HotXLS wczytywały się z nietkniętym układem

<!-- przed v2.384.33: niepoprawna wartość ST_Axis, pusty CT_Items -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

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

Czego CT_PivotField wymaga, a stary pisarz pomijał?

CT_PivotField wymaga trzech rzeczy, które stary BuildPivotTableXml pomijał albo robił źle. Po pierwsze, pole agregowane w obszarze wartości musi samo o tym powiedzieć w swojej definicji przez dataField="1"; pisarz ustawia teraz tę flagę na każdym polu, na które powołuje się wpis w DataFields, a nie tylko na liście <dataFields>. Po drugie, CT_Items potrzebuje co najmniej jednego item, więc pole bez itemów nie dostaje już pustego <items count="0">, a cały element jest po prostu pomijany. Po trzecie, każdy item zachowuje swój stan: h="1" dla ukrytego itemu (TXLSPivotItem.IsHidden) i sd="0" dla zwiniętych szczegółów (IsDetailHidden), a oba stary pisarz wyrzucał przy każdym zapisie

Subtelna część to końcowe itemy subtotal. Gdy pole ma itemy, Excel wypisuje jeden dodatkowy item na funkcję subtotal po itemach danych, typowane przez ST_ItemType: <item t="default"/> dla subtotala automatycznego, potem sum, countA, avg, max, min, product, count, stdDev, stdDevP, var i varP dla jawnych. HotXLS wyprowadza te wpisy z TXLSPivotField.Subtotals w chwili zapisu i liczy je do items count. Pola stworzone przez AddPivotTable startują z pustym zbiorem Subtotals, co zapisuje defaultSubtotal="0" i żaden końcowy item, więc proś o subtotale jawnie, gdy raport ich potrzebuje. Uważaj na pułapkę nazewniczą: xlpsCount mapuje się na countA (wszystkie wpisy), a xlpsCountNums na count (tylko liczby)

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];                  // numeracja od 1, jak w silniku XLS
    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, gdy nie ma takiego pola
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // ustawia Revenue dataField="1"

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

Jak HotXLS czyta teraz itemy subtotal i domyślne wartości schematu?

Czytnik HotXLS pomija teraz każdy item, którego atrybut t jest obecny i różny od data, bo wpisy subtotal, grand total i blank nie niosą indeksu cache. Przed v2.384.34 te wpisy były ładowane jako zwykłe itemy z CacheItemIndex ustawionym na -1, więc pivot zrobiony w Excelu wracał z widmowymi członkami, które nigdzie nie celowały, a każdy kod chodzący po Items musiał je odfiltrowywać ręcznie. Skoro pisarz przebudowuje końcowe wpisy z Subtotals, zadaniem czytnika jest przetłumaczenie ich na ten zbiór, nie trzymanie ich jako danych

Druga poprawka czytnika dotyczy atrybutów, których nie ma. W schemacie defaultSubtotal na CT_PivotField i containsString na CT_SharedItems mają oba domyślnie true, a Excel pomija je, gdy trzymają tę wartość domyślną. HotXLS czytał brakujący atrybut jako false, przez co każdy pivot zapisany przez Excela tracił po cichu swój domyślny subtotal przy wczytaniu, a zwykłe tekstowe pole cache było klasyfikowane jako mieszane zamiast tekstowe. To lustrzane odbicie błędu osi: pisarz, który zawsze wypisuje każdy atrybut, nigdy nie ćwiczy ścieżki domyślnej, więc wystawia ją dopiero pliki od innego producenta

Dlaczego numFmtId="General" było niepoprawne na polach cache?

Wartość numFmtId="General" była niepoprawna, bo ST_NumFmtId to liczba całkowita bez znaku, nie nazwa formatu. Stary pisarz cache zaszywał ten łańcuch na sztywno w każdym cacheField, pożyczając nazwę, którą użytkownicy widzą w oknie Format Cells. HotXLS zapisuje teraz NumberFormat pola cache jako liczbę, czyli 0 (wbudowany format General), chyba że coś go ustawiło. Surowy parser typujący atrybuty ze schematu odrzuca starą wartość od razu, a to dokładnie ta klasa awarii, która zamienia się w okno naprawy; artykuł o regułach OPC i znaczników stojących za oknem naprawy Excela opisuje, jak te okna się uruchamiają

Dlaczego tabele pivot poniżej wiersza 65535 były ucinane?

Tabele pivot XLSX umieszczone w wierszu 65536 lub niżej były ucinane, bo wspólny model pivot trzymał FirstRow, LastRow, FirstHeaderRow, FirstDataRow i ich odpowiedniki kolumnowe jako Word, a kod przesuwający wiersze docinał je przez Min(.., High(Word)). To pozostałość po rekordzie BIFF8 SxView, gdzie 16 bitów wystarcza, ale arkusz XLSX sięga 1 048 576 wierszy. Od v2.384.37 te właściwości na TXLSPivotTable są Integer, docinki zniknęły, a zawęża tylko pisarz BIFF8. TXLSXWorksheet.AddPivotTable i AddPivotTableCopy zwracają teraz nil dla kotwicy poza 1..1048576 na 1..16384 albo dla kopii, której zasięg wypadłby poza siatkę

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Wiersz 70001 zawijał się dawniej w zakres 16 bitów; teraz przeżywa zapis i odczyt
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // kotwica poza arkuszem albo nierozwiązywalny zakres źródłowy
  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;

Klasyczny silnik XLS dostał pasującą poprawkę w v2.384.38. Jego model trzymał dotąd surowe wartości SxView i DConRef liczone od zera i przepuszczał kotwice AddPivotTable wprost, podczas gdy dokumentacja, dema i silnik XLSX używały wszędzie komórek liczonych od 1, jak Cells[Row, Col]. Oba silniki trzymają teraz pozycje od 1 w modelu, czytnik BIFF8 dodaje 1, a pisarz odejmuje 1 na granicy rekordu, więc kod kotwiczący w (0, 0) musi przejść na (1, 1), bo klasyczny AddPivotTable zwraca teraz nil dla kotwicy poza 1..65536 na 1..256; nowe wywołanie zapisuje te same bajty co stare. Sam układ rekordów się nie zmienił i opisuje go artykuł o rekordach SX BIFF8 za klasycznymi tabelami pivot .xls

Waliduj względem schematu, nie własnego czytnika

Lekcja uogólnia się poza pivotty: pobłażliwy czytnik ukrywa naruszenia pisarza, więc round-trip przez własny kod dowodzi spójności, nie poprawności. Każdy błąd tutaj przeżył, bo strona tolerancyjna i strona wadliwa mieszkały w tej samej bibliotece. Kontrole, które faktycznie łapią tę klasę defektów, to walidacja schematowa generowanych części, pliki wyprodukowane przez Excela przepuszczone przez twój czytnik z pominiętymi atrybutami na wartościach domyślnych oraz fixtury przypinające dokładny token, nie wynik parsowania. Pivotty budowane przez API, łącznie z polami wyliczanymi, itemami wyliczanymi i układami percent-of-total pokazanymi w budowaniu i odświeżaniu tabel pivot XLSX z polami wyliczanymi, dostają poprawiony XML bez zmiany kodu, podczas gdy pivotty wczytane z plików Excela wciąż odtwarzają swoje oryginalne części, dopóki ich nie zmodyfikujesz

Wszystkie te poprawki wchodzą w skład aktualnego arkuszowego komponentu HotXLS dla Delphi, który czyta i zapisuje XLS, XLSX i tabele pivot z Delphi i C++Buildera bez Excela i automatyzacji COM na maszynie