Silnik formuł HotXLS i funkcje własne w Delphi

Biblioteka arkuszowa, która tylko przechowuje ciągi formuł, i biblioteka z działającym silnikiem formuł to dwa różne produkty, wyglądające identycznie aż do chwili, gdy poprosisz jedną z nich o liczbę. Większość kodu arkuszowego w Delphi nigdy nie zauważa tej luki, bo Excel ją zakleja: wpisz SUM(B2:B501) do komórki, zapisz, a Excel przeliczy sumę w chwili, gdy człowiek otworzy plik. Wyjmij człowieka z pętli, przepuść ten sam skoroszyt przez potok serwerowy eksportujący wprost do CSV, a różnica przestaje być akademicka. CSV niesie dosłowny tekst =SUM(B2:B501) tam, gdzie należała się liczba, bo w żadnym momencie nic naprawdę nie wyliczyło formuły

To ta granica, po której właściwej stronie stoi HotXLS. Traktuje formułę tak, jak robią to formaty plików — jako przechowywany tekst plus opcjonalny buforowany wynik — więc goły eksport CSV odtwarza przepis, a nie danie. Ale niesie też silnik obliczeń, który możesz wołać wprost, ten sam w fasadach XLS i XLSX, plus zaczep do rozwiązywania nazw funkcji, o których silnik nigdy nie słyszał. HotXLS to natywna biblioteka w Object Pascalu, która czyta i zapisuje XLS oraz XLSX z Delphi i C++Buildera bez automatyzacji Excela, a jej połowa obliczeniowa jest tym, co na żądanie zamienia przechowywane formuły z powrotem w wartości

Formuły są przechowywane, nie wyliczane zachłannie

Wpisanie formuły do komórki niczego nie liczy. Przy zapisie skoroszyt notuje tekst formuły. Po stronie XLS notuje też flagi rządzone przez RecalcOnSave, które domyślnie ma True i każe Excelowi przeliczyć przy otwarciu. Ten model jest poprawny dla plików przeznaczonych do Excela i błędny dla potoków konsumujących wartości komórek wprost, czy to eksportu CSV, eksportu HTML, czy twojego własnego kodu czytającego komórki z powrotem. Dla nich wyliczaj jawnie przez Calculate. Istnieje ono w czterech punktach wejścia: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook i TXLSXWorksheet wystawiają function Calculate(const Formula: WideString): Variant

// wylicz w procesie, potem wyślij wartość, a nie przepis
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // CSV niesie teraz liczbę

Wyrażenie podane do Calculate to zwykły tekst formuły Excela. Referencje międzyarkuszowe, nazwy zdefiniowane i funkcje zagnieżdżone rozwiązują się na bieżącym skoroszycie w pamięci, co czyni to wywołanie użytecznym daleko poza łataniem eksportów CSV. Traktuj je jak mechanizm asercji. Generator, który właśnie zapisał pięćset wierszy szczegółów, może zapytać skoroszyt o jego własną sumę końcową i porównać ją z liczbą policzoną niezależnie w Pascalu, łapiąc błąd zakresu o jeden, zanim zrobi to audytor klienta

To zarysowuje też właściwą strategię testowania dla wyjścia bogatego w formuły. Excel pozostaje referencyjną implementacją języka formuł, więc dla garstki formuł niosących konsekwencje biznesowe trzymaj zatwierdzony plik wzorcowy, którego oczekiwane wartości wyprodukował sam Excel, i niech potok budowania wylicza formuły wygenerowanego skoroszytu przez Calculate wobec tych wzorców. Różnice wychodzą wtedy jako oblane testy w Delphi, a nie jako rozbieżności odkryte przez klienta porównującego dwa raporty

Dodawanie funkcji biznesowych przez OnUserFunction

Kiedy silnik napotka nazwę funkcji, której nie rozpoznaje, podnosi zdarzenie zamiast zawodzić wprost. Przypisz OnUserFunction na którejkolwiek klasie skoroszytu, a możesz rozwiązać to wywołanie sam:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args przychodzi jako tablica Variant
    Handled := True;
  end;
end;

// podpięcie i użycie
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Trzy szczegóły zasługują na uwagę. Po pierwsze, ustawiaj Handled := True tylko wtedy, gdy naprawdę rozpoznałeś nazwę. Zostawienie False pozwala silnikowi kontynuować normalną obsługę nieznanych funkcji, więc jedna procedura obsługi może służyć kilku skoroszytom, nie przejmując wszystkiego, co przez nią przechodzi. Po drugie, porównuj nazwy bez rozróżniania wielkości liter przez SameText, bo autorzy formuł piszą discount( i DISCOUNT( wymiennie. Po trzecie, argumenty przychodzą wcześniej wyliczone: DISCOUNT(A1) podaje ci wartość A1, a nie referencję, więc funkcja nie potrafi powiedzieć, skąd wzięły się jej wejścia. Ten ostatni punkt przygotowuje ograniczenie, o którym jest następna sekcja

Traktuj ciało procedury obsługi z taką samą ostrożnością jak każdy zewnętrzny punkt wejścia. Tablica Args odzwierciedla to, co wpisał autor formuły, więc waliduj liczbę i typy argumentów przed indeksowaniem w nią i zdecyduj z góry, co zwraca niepoprawne wywołanie: wartość błędu w Variancie czy podniesiony wyjątek. Wybór ma znaczenie, bo wyjątek rzucony wewnątrz procedury obsługi propaguje na zewnątrz przez wywołanie Calculate, które wyzwoliło wyliczanie. To akceptowalne w ściśle kontrolowanym generatorze i niegrzeczne w usłudze wyliczającej skoroszyty pisane przez użytkowników, gdzie jedna zła formuła położyłaby żądanie. W takim ustawieniu łap wyjątek wewnątrz procedury obsługi i zwracaj wartownika, którego otaczający przepływ pracy rozpozna i zaloguje

Funkcje świadome pozycji potrzebują wariantu Ex

Niektóre funkcje słusznie zależą od tego, gdzie są wyliczane. Stawka różniąca się per arkusz, wyszukiwanie względem wiersza, mnożnik regionalny stosujący się tylko na arkuszach regionalnych: na żadne z tych pytań nie odpowiedzą same wartości argumentów. Zwykłe zdarzenie tego nie wyrazi, więc silnik oferuje OnUserFunctionEx, identyczne poza jednym dodatkowym parametrem:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // ta sama formuła daje inną stawkę na każdym arkuszu regionalnym
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext niesie SheetIndex, Row i Col wyliczanej komórki. Jeśli wynik funkcji zależy od jej położenia choćby odrobinę, podepnij zdarzenie Ex od początku. Doszywanie kontekstu do procedury obsługi, którą woła już trzydzieści formuł, jest o wiele bardziej upaćkane niż wybór właściwej sygnatury pierwszego dnia, a oba zdarzenia są poza tym tak podobne, że nie ma większego powodu, by zaczynać od węższego

Funkcje własne nie podróżują do Excela

Funkcja własna żyje w całości wewnątrz twojego procesu. Nazwa DISCOUNT znaczy coś tylko, dopóki działa twój kod w Delphi i jego procedura obsługi zdarzenia. Otwórz zapisany plik w Excelu, a DISCOUNT jest tylko nierozpoznaną nazwą; komórka pokazuje #NAME?, chyba że na maszynie użytkownika akurat istnieje pasująca funkcja VBA albo dodatek. To fakt projektowy, który oddziela demo od produktu do wysyłki, i wymusza wybór, którego musisz dokonać świadomie, zamiast odkryć go później

Zdecyduj dla każdej komórki, który z dwóch kontraktów wysyłasz. Komórki, które użytkownik ma zobaczyć przeliczane wewnątrz Excela, muszą być zbudowane ze słownika funkcji samego Excela i z niczego więcej. Komórki, których logika jest zastrzeżona, powinny być wyliczone w procesie przez Calculate i utrwalone jako zwykłe wartości, żeby funkcja własna zachowywała się jak wewnętrzna reguła obliczeniowa, a nie jak treść pliku. Tryb awarii niezawodnie generujący zgłoszenia do wsparcia to grunt pośredni: utrwalenie formuły z funkcją własną i oczekiwanie, że Excel ją uszanuje

Kontrakt oparty na samych wartościach ma cichą zaletę: chroni własność intelektualną. Reguła cenowa wyliczona w twoim procesie Delphi i wysłana jako liczba nie da się odtworzyć ze skoroszytu tak, jak widoczna formuła, a użytkownik nie zepsuje jej edycją komórki pośredniej. Generatory faktur, zestawienia prowizji i taryfy prawie zawsze należą do tego obozu. Przypadkiem, który naprawdę potrzebuje żywych formuł, jest interaktywny model co-jeśli, gdzie klient ma zmieniać wejścia i patrzeć, jak sumy się ruszają, a te muszą być zbudowane ze słownika samego Excela plus nazw zdefiniowanych

Tryby obliczeń, iteracja i R1C1: pokrętła fasady XLS

Fasada XLS wystawia ustawienia obliczeń na poziomie BIFF, które Excel czyta z pliku. CalculationMode przyjmuje xlCalcManual, xlCalcAutomatic (wartość domyślna) albo xlCalcAutomaticExceptTables i decyduje o zachowaniu Excela po otwarciu pliku. Skoroszyt modelowy z tysiącami formuł bywa przyjaźniejszy dostarczony w trybie ręcznym, żeby to odbiorca decydował, kiedy nastąpi burza przeliczeń. EnableIteration (domyślnie False), razem z MaxIterations (domyślnie 100) i MaxIterationChange (domyślnie 0.001), odblokowuje celowe odwołania cykliczne typu zbieżności iteracyjnej, które pojawiają się w niektórych modelach finansowych. ReferenceStyle przełącza między wyświetlaniem A1 i R1C1, a UseFullPrecision odzwierciedla excelową opcję precyzji zgodnej z wyświetlaniem

Te właściwości żyją na fasadzie XLS, bo mapują się na rekordy BIFF; przy generowaniu .xlsx planuj formuły tak, by nie zależały od ustawień iteracyjnych, albo policz wartości zbieżne w Delphi i zapisz wyniki

Formuły tablicowe: publiczny punkt wejścia to XLSX

Starsze formuły tablicowe w stylu CSE tworzy się przez TXLSXRange.SetArrayFormula:

// jedna formuła tablicowa rozpięta na A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Równoważna metoda istnieje w hierarchii klas XLS, ale siedzi w sekcji prywatnej, więc nie ma wspieranego sposobu, by tworzyć nowe formuły tablicowe w plikach .xls. Istniejące w otwieranych plikach przechodzą podróż w obie strony nietknięte; czego nie możesz, to ich utworzyć. Wynikająca z tego reguła jest dość prosta: kiedy semantyka tablicowa jest częścią wymagania, celuj w .xlsx. Jeśli starszy produkt .xls naprawdę potrzebuje zachowania tablicowego, pragmatyczną drogą jest policzenie wyniku tablicowego w Delphi i wpisanie poszczególnych wartości do komórek

Dwie powiązane lektury na tej stronie: nazwy zdefiniowane i formuły międzyarkuszowe omawia rozwiązywanie nazw, które wykonuje silnik, a artykuł o eksporcie CSV i TSV opisuje zachowanie eksportu, które czyni jawne obliczenie koniecznym. Pełna referencja silnika, wraz ze wspieranym zestawem funkcji, przychodzi z pakietem HotXLS Delphi Component