Tworzenie i odświeżanie tabel przestawnych Excela w Delphi za pomocą HotXLS

HotXLS tworzy i odświeża natywne tabele przestawne XLSX z poziomu Delphi i C++Builder bez konieczności instalowania programu Excel i bez automatyzacji COM na maszynie. Wywołujesz AddPivotTable z zakresem źródłowym, upuszczasz pola na osie wierszy, kolumn, stron i danych, nakładasz obliczane elementy lub wyświetlanie procentowe sumy, a komponent zapisuje części pivotCacheDefinition and pivotTableDefinition, które Excel otwiera jako aktywną, odświeżalną tabelę przestawną

Scenariuszem, który sprawia, że warto zadać sobie ten trud, jest serwer raportowania. Generujesz setki skoroszytów każdej nocy, z których każdy zawiera tabelę przestawną podsumowującą jedno konto, a w kolejnym miesiącu liczby się zmieniają i każdy plik musi odzwierciedlać nowe wiersze źródłowe. Sterowanie programem Excel z poziomu usługi systemu Windows jest niestabilne i nielicencjonowane do użytku serwerowego, a ręczne pisanie kodu XML tabeli przestawnej to niekończący się projekt archeologii specyfikacji. HotXLS plasuje się pomiędzy tymi dwoma ślepymi zaułkami: typowany model obiektowy nad częściami tabeli przestawnej OOXML, dzięki czemu ten sam kod Pascala, który wypełnia komórki, deklaruje również tabelę przestawną i odbudowuje jej pamięć podręczną w tym samym procesie

Jak utworzyć tabelę przestawną w Delphi bez programu Excel?

Tworzysz ją za pomocą jednego wywołania, a następnie umieszczasz pola na osiach. AddPivotTable przyjmuje zakres źródłowy w notacji A1, komórkę docelową, w której tabela jest zakotwiczona, oraz nazwę; analizuje zakres, skanuje każdą kolumnę w celu określenia jej typu danych, buduje pamięć podręczną tabeli przestawnej (lub ponownie używa pamięci podręcznej już powiązanej z tym samym zakresem) i zwraca obiekt TXLSPivotTable, którego wszystkie pola zaczynają się poza osią. Stamtąd wygodne metody AddRowField, AddColumnField, AddPageField oraz AddDataFieldByName łączą układ, a każde pole danych przyjmuje jedną z jedenastu agregacji z wyliczenia TXLSPivotAggregation (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP)

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // report sheet (1-based in the XLSX engine)

    // Source A1:E500 on the 'Data' sheet; anchor the pivot at row 3, col 1.
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

Dlaczego pamięć podręczna i tabela to dwie osobne części

Tabela przestawna to w rzeczywistości dwa artefakty, które odwołują się do siebie nawzajem, a zrozumienie tego podziału pozwala zachować jasność w dalszej części. pivotCacheDefinition to migawka danych: worksheetSource wskazujący na zakres źródłowy oraz jedno pole cacheField na kolumnę przechowujące unikalne wartości tej kolumny (jej sharedItems) oraz pochodne granice. pivotTableDefinition to widok: które pole pamięci podręcznej znajduje się na której osi, pola danych i ich agregacje oraz przełączniki układu. Widok wiąże się z pamięcią podręczną przez cacheId za pośrednictwem relacji pivotCaches skoroszytu, dokładnie tak, jak opisano to w ECMA-376 Część 1 §18.10 i [MS-XLSX]

Ta pośredniość to nie biurokracja, daje ona dwie rzeczy. Jedna pamięć podręczna może obsługiwać kilka tabel, so odświeżenie pamięci podręcznej raz aktualizuje każdy widok z niej narysowany. Ponadto pamięć podręczna przechowuje wartości każdej kolumny po usunięciu duplikatów za pomocą tabeli indeksów dla każdego rekordu, a nie surowej siatki, dlatego budowanie tabeli przestawnej oznacza skanowanie źródła, a nie kopiowanie komórek. HotXLS modeluje to w ten sam sposób dla obu silników, więc powyższy kod jest niemal identyczny z klasyczną ścieżką udokumentowaną wraz z binarnym układem rekordów SX stojącym za klasycznymi tabelami przestawnymi .xls. Jeśli źródło znajduje się na innym arkuszu lub odwołujesz się do niego za pomocą nazwy, przedrostek zakresu oraz zdefiniowane nazwy i odwołania między arkuszami są zgodne ze zwykłymi regułami A1, przy czym dozwolone są ujęte w cudzysłów nazwy arkuszy zawierające spacje

Elementy obliczane to nie to samo co pola obliczane

Te trzy terminy nazywają trzy różne rzeczy, a ich pomylenie jest klasycznym błędem przy tworzeniu tabel przestawnych. Obliczany element żyje wewnątrz jednego pola i łączy własne elementy tego pola według nazwy, więc w polu Region można zdefiniować syntetyczny wiersz CoreMarkets równy wartościom North plus South; HotXLS udostępnia go jako TXLSPivotField.AddCalculatedItem i zapisuje element <calculatedItem> w sekcji <calculatedItems> tego pola. Obliczane pole jest inne: jest to nowa wartość pochodząca z innych kolumn, jak Margin z Revenue i Cost, i jest przenoszona jako formuła w polu pamięci podręcznej za pośrednictwem TXLSPivotCacheField.Formula. Obliczany członek, dodawany za pomocą TXLSPivotTable.AddCalculatedMember, to niestandardowy członek na poziomie tabeli, który może działać jako miara (typ członka data) lub członek wymiaru, interesujący głównie w przypadku tabel przestawnych o strukturze OLAP

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // A calculated ITEM combines items of a single field by name.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // A calculated MEMBER is declared at the table level. Member type
  // 'data' marks it as a measure; an empty type is a dimension member.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Jedna rzeczywista granica dotyczy wszystkich trzech. HotXLS emituje tekst formuły do XML definicji; nie oblicza go. Excel oblicza obliczany element, pole lub członka podczas otwierania pliku, w ten sam sposób, w jaki oblicza każdą agregację w siatce. HotXLS zapisuje instrukcje, a no wyniki, więc dostarczane formuły muszą być prawidłowymi formułami tabeli przestawnej we własnym dialekcie programu Excel, odwołującymi się do nazw pól i elementów tak, jak robiłby to Excel

Jak wyświetlić wartości jako procent sumy?

Ustawiasz tryb wyświetlania wartości w polu danych, zamiast samodzielnie przekształcać liczby. Właściwość TXLSPivotDataField.ShowDataAs przyjmuje wyliczenie TXLSPivotShowDataAs, które odzwierciedla wartości OOXML ST_ShowDataAs: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal oraz xlpsdaIndex. Powszechną sztuczką jest dwukrotne umieszczenie tej samej kolumny źródłowej na osi danych, raz jako surowej sumy, a raz jako udziału w sumie końcowej, dzięki czemu raport pokazuje zarówno liczbę, jak i jej wagę

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // Item-relative modes need a base to compare against. A running total
  // down 'Quarter' (cache-field index 3) would read:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // cache-field index the transform runs over
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

Wartość xlpsdaPercentOfTotal nie wymaga bazy, ponieważ odnosi się do sumy końcowej, ale tryby powiązane z elementami tego wymagają. Metody xlpsdaDifference, xlpsdaPercentDiff oraz xlpsdaRunTotal wymagają właściwości BaseField (indeksu pola pamięci podręcznej, na którym przeprowadzane jest porównanie) oraz, w przypadku form zakotwiczonych w elementach, indeksu BaseItem, gdzie wartość $7FFD oznacza strażnika (All). HotXLS zapisuje je jako atrybuty <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> i podobnie jak w przypadku obliczanych formuł, pozostawia arytmetykę programowi Excel

Grupowanie dat i liczb oraz filtry pól stron

Grupowanie jest konfigurowane w polu pamięci podręcznej, a nie w polu tabeli przestawnej, ponieważ zmienia sposób podziału domeny źródłowej na koszyki. Ustaw właściwość HasGroup := True w polu pamięci podręcznej zwróconym przez Cache.FindFieldByName, a następnie wybierz pokrętła zakresu liczbowego (GroupStartNum, GroupEndNum, GroupInterval) lub flagi hierarchii dat (GroupByDate z wartościami GroupMonths, GroupQuarters, GroupYears oraz przedziałem GroupStartDate / GroupEndDate). HotXLS emituje pasujący element <fieldGroup><rangePr groupBy="months"/>, dzięki czemu pole zgrupowane według miesięcy lub 1000-szerokiego pasma liczbowego otwiera się w taki sposób, w jaki pogrupowałby je Excel

Pola stron to rozwijane listy filtrów nad tabelą. Metoda AddPageField umieszcza pole na osi strony, a właściwość PageItemIndex wstępnie wybiera pojedynczy element pamięci podręcznej, domyślnie xlPageItemAll (wartość $7FFD oznaczająca (All)). Aby pozwolić czytelnikowi na zaznaczenie kilku elementów naraz, ustaw właściwość MultipleItemSelectionAllowed := True, co HotXLS zapisuje jako <pivotField multipleItemSelectionAllowed="1"/>. W przypadku kryteriów wykraczających poza ręczny wybór każde pole zawiera typowaną kolekcję Filters obejmującą rodziny OOXML ST_FilterType — filtry liczby, procentu, sumy, podpisu, wartości i daty — przy czym każdy wpis paruje typ filtra z jego wartościami porównawczymi

Jak odświeżyć pamięć podręczną tabeli przestawnej, gdy zmienia się źródło?

Metoda RefreshPivotCache w klasie TXLSXWorkbook ponownie skanuje zakres źródłowy i odbudowuje pamięć podręczną na miejscu, co jest potrzebne w potoku wsadowym po edycji wierszy bazowych. Przekaż identyfikator pamięci podręcznej, a metoda ponownie odczyta każdą komórkę w zakresie źródłowym (nagłówek w pierwszym wierszu, dane z drugiego), odbuduje domenę współdzielonych elementów każdego pola przez ponowne usunięcie duplikatów wartości i ponowne wyprowadzenie granic dla poszczególnych typów, a także przepisze indeksy elementów dla poszczególnych rekordów. Zwraca wartość 1 w przypadku sukcesu i -1, gdy identyfikator pamięci podręcznej jest nieznany lub brakuje arkusza źródłowego

var
  Rc: Integer;
begin
  // ...the source data has changed since the pivot was built...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // a corrected Revenue figure

  // Rebuild the cache's shared items and record indices from the source.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = refreshed, -1 = cache/source missing
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

Granica semantyczna jest tutaj warta jasnego wyjaśnienia. Zanim ta metoda powstała, komponent HotXLS polegał na fladze refreshOnLoad="1" i pozostawiał całą pracę związaną z odświeżaniem programowi Excel przy następnym otwarciu, co jest w porządku, gdy plik otwiera człowiek, ale bezużyteczne w autonomicznym potoku przetwarzania, który musi przekazać poprawne dane. Metoda RefreshPivotCache aktualizuje zapisaną pamięć podręczną w procesie, a ponieważ tabele wiążą się z pamięcią podręczną poprzez identyfikator, każda tabela przestawna pobrana z tej pamięci podręcznej widzi aktualizację. Nie robi natomiast układania ani agregowania widocznej siatki — Excel nadal ponownie oblicza wyrenderowaną tabelę z odświeżonej pamięci podręcznej podczas otwierania pliku

Co zapisuje HotXLS, a co oblicza Excel

Pamiętaj o podziale pracy, a nic Cię nie zaskoczy. HotXLS jest modułem zapisu definicji: tworzy elementy pivotCacheDefinition, rekordy pamięci podręcznej oraz pivotTableDefinition, wraz z osiami, agregacjami, obliczanymi elementami i członkami, wyświetlaniem wartości, grupowaniem i filtrami. Excel jest kalkulatorem: przy otwarciu materializuje koszyki grup, oblicza formuły, stosuje przekształcenia showDataAs i agreguje treść. Wartości widoczne dla użytkownika należą do programu Excel i są generowane na podstawie instrukcji zapisanych przez HotXLS, dlatego każda formuła i indeks bazowy muszą być poprawne w momencie zapisu, a nie sprawdzane podczas renderowania

Przed zaprojektowaniem czegokolwiek wokół tej funkcji warto poznać dwa ograniczenia. Metoda AddPivotTable analizuje prostokątny zakres A1 z opcjonalnym prefiksem arkusza; źródła z zakresów nazwanych i zewnętrznych skoroszytów wykraczają poza to, co rozwiązuje kreator, chociaż pamięci podręczne odczytane z pliku utworzonego przez program Excel zachowują swoje źródło z zakresów nazwanych w obie strony. Tabele przestawne utworzone w programie Excel, które HotXLS odczytuje z dysku, są odtwarzane bajt po bajcie przy zapisie, więc opisane tutaj edycje typów mają zastosowanie czyste do tabel przestawnych budowanych w kodzie, podczas gdy istniejące tabele przestawne pozostają bezstratne. Informacje na temat wprowadzania danych do raportu — zwalidowanych komórek i filtrowanych tabel, które podsumowuje tabela przestawna — można znaleźć w artykule opisującym poprawność danych, autofiltr i tabele strukturalne

Pokrętło tabeli przestawnej pokazane tutaj jest częścią standardowego komponentu HotXLS Delphi Excel dla Delphi i C++Builder, który odczytuje i zapisuje zarówno typowane części tabeli przestawnej XLSX, jak i klasyczne rekordy BIFF8 z tego samego modelu obiektowego