Artykuł techniczny

Pola pivot XLSX zgodne ze schematem w Delphi z HotXLS

Atrybut axis="rowAxis" na elemencie pivotField czyta się jak całkiem sensowny XML, a czytnik, który mapuje go na „pole wiersza”, wczyta pivot z każdym polem na właściwej osi. Przepuść tę samą część przez walidator zbudowany ze schematu ECMA-376 Part 1 §18.10, a zawiedzie on na pierwszym atrybucie: ST_Axis ma dokładnie cztery tokeny, axisRow, axisCol, axisPage i axisValues, a rowAxis nie jest żadnym z nich. Pisarz pivotów musi zadowolić schemat, a nie własnego czytnika, bo ludzie otwierający plik używają Excela i własnych parserów

Definicje pól pivot są pełne reguł jak ta. Trzy potrafią się przemyć przez test round-tripu: tokeny enumeracji, wymóg, by element items nigdy nie był pusty, i końcowe wpisy subtotal, które Excel wymienia po itemach danych. Dwie kolejne siedzą po stronie czytającej, w atrybutach, które Excel pomija, bo trzymają domyślną wartość ze schematu. Reszta artykułu przechodzi przez każdą regułę, pokazuje, jak musi wyglądać XML, i pokazuje wywołania Delphi, które go produkują

Dlaczego round-trip nie złapie złego tokenu osi

Round-trip dowodzi, że pisarz i czytnik zgadzają się ze sobą, i nic więcej. Jeśli czytnik akceptuje i axisRow, i rowAxis, pisarz emitujący zły przechodzi każdą kontrolę zapisz-i-wczytaj, jaką da się napisać, bo czytnik naprawia pomyłkę w drodze powrotnej. Wada wychodzi dopiero, gdy ostrzejszy konsument otwiera część: walidator schematu, parser wygenerowany z XSD albo Excel rozstrzygający, że część potrzebuje naprawy

HotXLS rozdziela te dwie strony celowo. Pisarz XLSX emituje tylko cztery tokeny ST_Axis, podczas gdy czytnik pivotów mapuje też anglojęzyczne pisownie rowAxis, colAxis, pageAxis i dataAxis na pasującą oś, więc pliki od producentów, którzy ich używają, nadal wczytują się z nietkniętym układem. Wyjście jest trzymane przy schemacie; wejście musi być tylko rozpoznawalne. Odwrotność — surowy czytnik z brudnym pisarzem — przynajmniej głośno zawiedzie we własnych testach; pobłażliwy czytnik z brudnym pisarzem zawiedzie nigdzie, gdzie widać

<!-- niepoprawne: rowAxis nie jest wartością ST_Axis, a CT_Items nie może być pusty -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- poprawne: token ST_Axis, prawdziwe itemy, końcowy subtotal domyślny -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>
Niepoprawne kontra poprawne XML pivotField: wymyślona wartość osi rowAxis i pusty element items naruszają CT_PivotField, podczas gdy HotXLS emituje tokeny ST_Axis, takie jak axisRow, z prawdziwymi wpisami itemów, zachowaną flagą ukrycia i końcowym subtotal default, które schemat akceptuje
Pobłażliwy czytnik akceptujący obie pisownie przepuszcza każdy round-trip, podczas gdy plik wywala każdą surową kontrolę schematu — zapisuj tylko cztery tokeny ST_Axis i dawaj CT_Items co najmniej jeden item

Czego CT_PivotField oczekuje poza osią

CT_PivotField ma jeszcze trzy wymagania, łatwe do zgubienia. Pole agregowane w obszarze wartości musi powiedzieć o tym na własnej definicji przez dataField="1", choć lista <dataFields> już się do niego odwołuje. HotXLS ustawia tę flagę na każdym polu pivotu, na które wskazuje wpis w DataFields, więc pole dodane przez AddDataFieldByName jest oznaczone w obu miejscach

Druga reguła jest strukturalna. CT_Items wymaga co najmniej jednego dziecka item, co czyni <items count="0"></items> niepoprawnym, choć to poprawnie sformowany XML parsowany wszędzie. Gdy pole nie ma itemów, HotXLS zostawia cały element items poza zapisem. Trzecia reguła dotyczy stanu na każdym itemie: ukryty członek niesie h="1", a członek ze zwiniętymi szczegółami niesie sd="0". Oba pochodzą z TXLSPivotItem.IsHidden i TXLSPivotItem.IsDetailHidden i są zapisywane przy każdym zapisie, więc filtr ustawiony w kodzie przeżywa do Excela

Końcowe itemy subtotal i pułapka licznika

Gdy pole ma itemy, Excel podąża za itemami danych z jednym dodatkowym item na funkcję subtotal, typowanym przez ST_ItemType. Subtotal automatyczny to <item t="default"/>; funkcje jawne używają sum, countA, avg, max, min, product, count, stdDev, stdDevP, var i varP. Te wpisy nie niosą indeksu x do cache i wliczają się do atrybutu count elementu items. Pisarz, który je pominie, produkuje listę items, która już nie zgadza się z flagami subtotal na tym samym elemencie

HotXLS wyprowadza ogon z TXLSPivotField.Subtotals przy zapisie: jeden item t="…" na funkcję w zbiorze, w kolejności enumeracji, dodany do licznika, plus pasujący atrybut w stylu sumSubtotal="1" na polu. Pola stworzone przez AddPivotTable startują z pustym zbiorem, co zapisuje defaultSubtotal="0" i żaden końcowy item w ogóle. Dla generowanych raportów zostaw zbiór pusty: wiersze subtotal zmieniają wysokość pivotu i przesuwają wszystko położone pod nim. Dodawaj funkcje do Subtotals tylko na polach, gdzie czytający potrzebuje dodatkowych wierszy

Nazewnictwo zasługuje na drugie spojrzenie, zanim wybierzesz funkcję. xlpsCount mapuje się na countA, który liczy wszystkie niepuste wpisy, a xlpsCountNums mapuje się na count, który liczy tylko liczby. Ktokolwiek przeczyta „count” w XML i sięgnie po xlpsCount, dostanie drugą funkcję

Anatomia listy itemów pivot w HotXLS: po wpisach itemów danych następują końcowe itemy subtotal wyprowadzone z TXLSPivotField.Subtotals, jak t=default i t=avg, liczone do items count, z rozpisaną pułapką nazewniczą xlpsCount na countA i xlpsCountNums na count
Pola z AddPivotTable startują z pustym zbiorem Subtotals, co zapisuje defaultSubtotal=0 i żaden końcowy item — zażądaj funkcji, których chcesz, a pisarz wyprowadzi jeden item na funkcję do licznika

Program poniżej buduje pivot z istniejącego arkusza danych z polem wiersza, polem kolumny i jednym polem wartości. Pole Region dostaje subtotal automatyczny plus średnią, co produkuje dwa końcowe itemy po jego itemach danych oraz dataField="1" na Revenue

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // Sheets numeruje od 1
    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);      // Revenue dostaje dataField="1"

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

Czytanie tego, co Excel pomija

Czytnik ma problem w lustrzanym odbiciu. Pole pivotu z Excela wymienia swoje wpisy subtotal w items, a wczytanie ich jako zwykłych członków dałoby polu fantomowe itemy wskazujące w próżnię cache. HotXLS pomija każdy item, którego atrybut t jest obecny i nie jest data, co pokrywa wpisy subtotal, grand-total i puste. Funkcje subtotal przychodzą przez własne flagi pola i lądują w Subtotals, więc kod chodzący po Items widzi tylko prawdziwych członków, a każdy CacheItemIndex odwołuje się do wartości cache

Brakujące atrybuty to druga połowa. W schemacie defaultSubtotal na CT_PivotField i containsString na CT_SharedItems oba mają domyślnie true, i Excel je pomija, gdy trzymają tę wartość. HotXLS czyta nieobecne defaultSubtotal jako true i dodaje xlpsDefault do Subtotals, gdy pole nie niesie jawnych flag subtotal — tak właśnie Excel sam wyświetla takie pole. Nieobecne containsString też czyta się jako true, więc zwykłe pole tekstowe cache jest klasyfikowane jako dane napisowe. Odczytanie któregokolwiek atrybutu jako false sprawia, że każdy pivot zapisany przez Excela traci automatyczne subtotale przy wczytaniu, a pola tekstowe zamieniają się w inny typ. Pisarz, który zawsze rozpisuje każdy atrybut, nigdy nie dotyka tej ścieżki; ćwiczą ją tylko pliki od innego producenta

Załadowany skoroszyt z Excela to właściwe wejście testowe, bo to jedyny rodzaj pliku, który pomija te atrybuty. Kontrola niżej otwiera jeden i przechodzi pole wiersza:

var
  Book : TXLSXWorkbook;
  Pivot: TXLSPivotTable;
  Fld  : TXLSPivotField;
  I, J : Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('saved-by-excel.xlsx');
    Pivot := Book.Sheets[1].PivotTables.FindByName('PivotTable1');
    if Pivot = nil then
      Exit;
    for I := 0 to Pivot.FieldCount - 1 do
    begin
      Fld := Pivot.Fields[I];
      if Fld.Axis <> xlpfaRowField then
        Continue;
      // Pole Excela bez atrybutów subtotal nadal raportuje default
      if xlpsDefault in Fld.Subtotals then
        Writeln(Fld.Name, ': automatic subtotal');
      // Wpisy subtotal były pomijane, więc każdy item mapuje się na wartość cache
      for J := 0 to Fld.ItemCount - 1 do
        Assert(Fld.Items[J].CacheItemIndex >= 0);  // kontrola sugerowana do twoich własnych testów
    end;
  finally
    Book.Free;
  end;
end;

Dlaczego numFmtId="General" jest niepoprawne na polach cache

numFmtId="General" jest niepoprawne, bo ST_NumFmtId jest liczbą całkowitą bez znaku, jakkolwiek naturalnie brałoby się nazwę, którą użytkownicy widzą w oknie Format Cells. Parser typujący atrybuty ze schematu odrzuca nazwę w progu, a ta klasa awarii to dokładnie to, co zamienia się w dialog naprawy w Excelu; artykuł o regułach OPC i markupu za monitem naprawy Excela omawia, jak te dialogi są wyzwalane. HotXLS zapisuje każdy cacheField z numerycznym numFmtId wziętym z NumberFormat pola cache, czyli 0, wbudowany format General, chyba że coś go ustawiło

Tabele pivot poniżej wiersza 65535 potrzebują pozycji 32-bitowych

Model pivotu współdzielony przez silniki XLS i XLSX kusi, by pożyczyć szerokości pól z rekordu BIFF8 SxView, gdzie 16 bitów pokrywa każdy wiersz, jaki klasyczny arkusz może trzymać. Arkusz XLSX biegnie do 1 048 576 wierszy. Trzymaj FirstRow, LastRow, FirstHeaderRow, FirstDataRow i odpowiedniki kolumnowe jako Word, docinaj je przez Min(Value, High(Word)), a pivot zakotwiczony w wierszu 70001 zostaje ucięty na 65535

Na TXLSPivotTable te właściwości są Integer, i tylko pisarz BIFF8 zawęża je na granicy rekordu. TXLSXWorksheet.AddPivotTable zwraca nil dla kotwicy poza 1..1048576 na 1..16384, a AddPivotTableCopy zwraca nil, gdy rozpiętość kopii zbiegłaby poza siatkę

Kotwica pivot w wierszu 70001 wobec sufitu 16 bitów: FirstRow i LastRow trzymane jako Word i docinane przez Min względem High(Word) na 65535 ucinały pivotty na linii i poniżej, podczas gdy HotXLS trzyma je jako pola Integer z powrotem nil poza siatką
Pola wierszowe szerokości Worda pasują do rekordów BIFF8 SxView, ale nie do arkusza, który sięga 1048576 wierszy — z nimi kotwica za wierszem 65535 jest docinana do 65535, a pivot nie siedzi już tam, gdzie go położono
var
  Book, Check: TXLSXWorkbook;
  Pivot      : TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    // Wiersz 70001 jest poza zakresem 16 bitów
    Pivot := Book.Sheets[1].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');
  finally
    Book.Free;
  end;

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    // Assert to kontrola sugerowana do twoich własnych testów
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

Klasyczny silnik XLS podąża za tą samą konwencją od 1. Rekordy SxView i DConRef trzymają wartości od 0, podczas gdy model, jak silnik XLSX i Cells[Row, Col], trzyma pozycje od 1: czytnik BIFF8 dodaje 1, a pisarz odejmuje 1 na granicy rekordu. Klasyczne AddPivotTable zwraca nil dla kotwicy poza 1..65536 na 1..256, więc kotwicz pivotty w (1, 1), a nie w (0, 0). Sam układ rekordów jest opisany w rekordach SX BIFF8 za klasycznymi pivotami .xls

Testowanie względem schematu zamiast własnego czytnika

Każda pułapka wyżej przeżywa round-trip, gdy pobłażliwa strona i wadliwa strona mieszkają w tej samej bibliotece. Trzy rodzaje kontroli je łapią. Walidacja schematu wygenerowanych części pivotTableN.xml i pivotCacheDefinitionN.xml łapie wymyślone tokeny, puste elementy items i nazwane identyfikatory formatów. Pliki zapisane przez Excel, przepuszczone przez twój czytnik, ćwiczą atrybuty, które Excel pomija przy ich domyślnych wartościach. Asercje przypinające dokładny token w zapisanym XML, jak axis="axisRow", łapią pisarza dryfującego ku pisowni, którą rozumie tylko twój czytnik; asercja na sparsowanej osi nie

Pivotty budowane przez API, włą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, wychodzą z tymi regułami zastosowanymi i bez dodatkowego kodu po twojej stronie. Robota, która zostaje tobie, to wybranie, które pola dostają subtotale i którą funkcję liczenia masz na myśli

Komponent arkuszy HotXLS dla Delphi buduje, czyta i zapisuje tabele pivot XLS i XLSX z kodu Delphi i C++Builder, bez instalacji Excela ani automatyzacji COM na maszynie, na której biegnie