Formuły tablic dynamicznych (rozlewające się) w Delphi za pomocą HotXLS

Komponent HotXLS natywnie oblicza formuły tablic dynamicznych (rozlewające się/spill) programu Excel 365 w środowiskach Delphi i C++Builder: przekazujesz skoroszyt, którego komórki zawierają funkcje XLOOKUP lub FILTER, a silnik formuł oblicza zestaw wyników i „rozlewa” go na zakotwiczony prostokąt wyjściowy, zwracając dokładnie takie same wartości, jakie wygenerowałby program Excel. Komponent HotXLS dla Delphi modeluje formułę rozlewającą się jako jedną komórkę główną (root), która jest właścicielem obliczeń, oraz blok komórek zależnych, które pobierają z niej dane — co stanowi pełen obraz architektury niezbędny przed napisaniem kodu

Ten model jest ważny, ponieważ codzienne zadania nie mają charakteru abstrakcyjnego. Klient przesyła plik .xlsx utworzony w programie Excel 365, jego arkusze są pełne formuł typu =FILTER(...) oraz =XLOOKUP(...), a Twoja usługa musi odtworzyć dokładnie te same liczby w trybie bezgłowym (headless), na maszynie bez zainstalowanego programu Excel, a następnie odczytać rozlane wartości lub zapisać własny nowy obszar rozlewania. Tablice dynamiczne zmieniły kontrakt obliczeniowy z „jedna formuła, jedna komórka” na „jedna formuła, prostokąt komórek”, a silnik HotXLS realizuje ten kontrakt, zamiast symulować go za pomocą wstępnie rozszerzonej siatki

Jak obliczać funkcje XLOOKUP i FILTER w Delphi?

Komponent HotXLS przekazuje każdą funkcję tablicy dynamicznej przez jeden punkt wejściowy ewaluatora: CalcDynArrayFunc w module lxCalc.pas, dzięki czemu cała rodzina funkcji współdzieli tę samą ścieżkę kodu rozlewania. Obsługiwany zestaw obejmuje funkcje XLOOKUP i FILTER (dwie pierwotne), a także XMATCH, SORT, UNIQUE oraz SEQUENCE. Każda z nich zwraca dwuwymiarową tablicę typu Variant zamiast pojedynczej wartości: np. SEQUENCE(3;2;1;1) generuje siatkę 3 na 2, SORT zmienia kolejność wierszy według dowolnej kolumny klucza (rosnąco lub malejąco), UNIQUE usuwa duplikaty wierszy zachowując pierwsze wystąpienie, a XMATCH zwraca pozycję wartości (indeksowaną od 1) w trybach dopasowania dokładnego, wieloznacznego (wildcard), kolejnego mniejszego lub kolejnego większego — czyli dokładnie tak samo jak XLOOKUP. W przeciwieństwie do skalarnych funkcji rozkładu statystycznego, które zwracają jedną liczbę przy wywołaniu, funkcje te zwracają cały obszar danych

Aby przeliczyć skoroszyt zawierający już te formuły, otwórz go, wywołaj raz metodę Recalculate i odczytaj komórki, do których trafiły rozlane dane. TXLSXWorkbook.Recalculate przechodzi przez graf zależności i oblicza każdą nieaktualną formułę w porządku topologicznym, więc komórka główna rozlania jest obliczana tylko raz, a jej elementy są wpisywane bezpośrednio do komórek składowych

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // evaluate every formula, spills included
    Sheet := Book.Sheets[1];          // Sheets is 1-based
    // Read back the block the XLOOKUP / FILTER spilled into, say D2:F9.
    for r := 2 to 9 do
      for c := 4 to 6 do
        Writeln(Sheet.Cells[r, c].Text);
  finally
    Book.Free;
  end;
end;

Jeśli skoroszyt wymaga funkcji, której silnik nie obsługuje, ten sam ewaluator pozwala na wdrożenie własnej logiki poprzez punkt rozszerzenia dla niestandardowych funkcji arkusza, a funkcja niestandardowa może również zwracać tablicę typu Variant, dzięki czemu rozlewa się ona identycznie jak wbudowane odpowiedniki

Zakotwiczanie zakresu rozlewania za pomocą SetArrayFormula

HotXLS nigdy nie zgaduje, jak duży powinien być obszar rozlania: wskazujesz prostokąt wyjściowy, a metoda TXLSRange.SetArrayFormula zakotwicza w nim formułę. Metoda ta kompiluje formułę raz, przechowuje drzewo składniowe w lewej górnej komórce głównej wraz z zakotwiczonym zakresem, a każdej innej komórce w prostokącie przypisuje lekką formułę odwołującą się słabo (weak-reference) do korzenia. Przy ponownym obliczaniu korzeń jest ewaluowany raz, a elementy jego macierzy są rozsyłane bezpośrednio do każdej komórki składowej. W ten sposób korzeń rozlania jest reprezentowany jako pojedynczy węzeł w grafie zależności przyrostowego przeliczania. Tę ścieżkę należy wybrać, gdy zadaniem jest zapisanie obszaru rozlania do pliku, a nie jego odczyt

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Size the rectangle to the result: a 3-row by 2-column grid.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // fill A1:B3 with 1..6 down the rows
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

Konsekwencją jawnego zakotwiczania jest ograniczenie, o którym należy pamiętać: HotXLS nie zmienia automatycznie rozmiaru obszaru rozlania w sposób, w jaki robi to interaktywny program Excel. Excel rozszerza lub kurczy obszar rozlania w zależności od zmian danych wejściowych i zgłasza błąd #SPILL!, gdy komórki docelowe są zajęte. W komponencie HotXLS prostokąt przekazany do SetArrayFormula określa stałe wymiary, więc musisz dopasować go do oczekiwanego wyniku. Jeśli funkcja wygeneruje więcej wierszy niż zakotwiczono, nadmiarowe dane nie zostaną zapisane

Elementowe rozgłaszanie tablic w operatorach

HotXLS stosuje operatory arytmetyczne element po elemencie, gdy którakolwiek ze stron operacji jest tablicą. Funkcja pomocnicza ApplyArrayBinaryOp obsługuje operatory +, -, *, / oraz ^ dla jedno- i dwuwymiarowych tablic typu Variant. Operand skalarny jest rozgłaszany (broadcast) do każdego elementu, kształty obu tablic muszą być zgodne (w przeciwnym razie operacja jest odrzucana), a dzielenie przez zero propaguje się jako błąd komórki zamiast powodować awarię programu. Ścieżka arytmetyki skalarnej pozostaje nienaruszona, więc mechanizm ten aktywuje się tylko wtedy, gdy co najmniej jeden z operandów jest rzeczywiście tablicą (np. zakresem rozlania lub wynikiem funkcji macierzowej). Oznacza to, że formuła =D2:D13*1.1 zakotwiczona nad kolumną mnoży kolejno każdy jej element, a =D2:D13*E2:E13 mnoży dwie kolumny o tym samym kształcie pozycja po pozycji

// Scalar broadcast: every cell of the anchor gets D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Two arrays of identical shape multiply element by element.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

Co robi operator @ w porównaniu do tradycyjnej tablicy CSE?

HotXLS interpretuje jawny znak @ jako operator przecięcia referencji (reference-intersection), a nie jako niejawne przecięcie (implicit intersection) programu Excel wybierające wiersz. W silniku zapis A1:A3 @ B1:B3 zwraca pojedynczą komórkę w miejscu przecięcia obu zakresów, a operator ten wiąże silniej niż ^ i słabiej niż %. Warto również zauważyć, że niejawne przecięcie rozdzielane spacją nie jest jeszcze analizowane przez leksykalny parser z uwagi na pomijanie spacji, dlatego przecięcie uzyskasz wyłącznie przy użyciu jawnego symbolu @

Istotna różnica występuje między klasyczną formułą tablicową CSE a nowoczesną tablicą dynamiczną, lecz w HotXLS obie te struktury korzystają z tego samego mechanizmu zakotwiczania. Klasyczna formuła tablicowa to blok {=...} wprowadzany skrótem Ctrl+Shift+Enter nad wcześniej zaznaczonym obszarem, gdzie każda komórka współdzieli jedną skompilowaną formułę. Tablica dynamiczna to pojedyncza formuła, której wynik określa kształt obszaru. HotXLS jednoczy je w ramach SetArrayFormula: zawsze podajesz prostokąt wyjściowy, komórka główna przechowuje skompilowane drzewo, a komórki składowe odwołują się do niego — niezależnie od tego, czy formuła jest tradycyjnym wyrażeniem macierzowym, czy nową funkcją SORT. Nie uzyskasz tu jednak automatycznego rozszerzania obszaru znanego z programu Excel, a pamiętanie o tym ograniczeniu pozwala uniknąć zatarcia różnic między tymi dwoma modelami

Gdzie kończą się możliwości obsługi tablic dynamicznych

Znajomość granic możliwości komponentu pozwala zaoszczędzić czas przy debugowaniu. Funkcja SORT obsługuje kierunek sortowania dla każdego klucza (domyślnie rosnąco, a malejąco, gdy argument order jest ujemny). Kierunek porównania warto przetestować z wynikami w Excelu, ponieważ odwrócone porównanie po cichu zwraca wiersze w odwrotnej kolejności zamiast zgłaszać błąd. Funkcja UNIQUE zachowuje pierwsze wystąpienie każdego unikalnego wiersza, co silnik weryfikuje poprzez skanowanie wcześniejszych wierszy, a nie opierając się na prostym zliczaniu duplikatów. Funkcja TABLE (tabele danych analizy warunkowej) jest rozpoznawana i zachowywana w pliku, ale zwraca wartość zastępczą (placeholder), ponieważ rzeczywiste wyniki to zapamiętane wartości zapisane wcześniej przez Excel, a HotXLS nie przelicza siatki analizy warunkowej na nowo

Funkcja LET to najpoważniejsze ograniczenie. HotXLS rejestruje LET i posiada ścieżkę obliczeniową dla tej funkcji, jednak parser odrzuca nazwy zmiennych, które nie zostały wcześniej zdefiniowane globalnie, przez co formuła =LET(x;10;x) zawodzi na etapie analizy składniowej, zanim jeszcze uruchomiony zostanie ewaluator. Należy traktować LET jako nieobsługiwane, dopóki parser nie zyska obsługi zakresów widoczności zmiennych. Mała uwaga dotycząca przenośności: formuły w tych przykładach używają separatora listy silnika (średnika), więc dopasuj go do separatora oczekiwanego przez Twój projekt. Przedstawione tutaj funkcje tablic dynamicznych, od XLOOKUP i FILTER po SORT, UNIQUE, SEQUENCE i XMATCH, wchodzą w skład pakietu HotXLS Delphi Excel Component, którego dokumentacja zawiera pełną listę funkcji i obsługiwanych argumentów