Formuły między skoroszytami na żywo w Delphi z HotXLS

HotXLS potrafi obliczyć formułę taką jak =[Rates.xlsx]FX!B4*C10 względem innego skoroszytu otwartego w pamięci, a nie względem nieaktualnej wartości z pamięci podręcznej. Rejestrujesz drugi skoroszyt po nazwie za pomocą RegisterExternalWorkbook, a każde przeliczenie odczytuje przywoływane komórki bezpośrednio z tego obiektu. Wyrejestruj go, a obliczenia przejdą na pamięć podręczną odsyłaczy zewnętrznych, którą plik już niesie

To zamyka konkretną i frustrującą lukę. Skoroszyty Excela rutynowo odwołują się do siebie nawzajem: tabela kursów, plan kont, cennik utrzymywany przez inny zespół. Gdy taki plik jest otwierany przez bibliotekę, a nie przez Excela, te odwołania zwykle rozwiązują się do wartości zapisanych w pamięci podręcznej przy ostatnim przeliczeniu w Excelu, co może być sprzed miesięcy, a żadna liczba przeliczeń tego nie naprawi, ponieważ danych po prostu tam nie ma

Jak odwołanie między skoroszytami wygląda wewnątrz pliku?

Odwołanie zewnętrzne nie jest przechowywane jako tekst widoczny na pasku formuły. Skoroszyt utrzymuje tabelę powiązanych plików, każdy token formuły wskazuje indeks w tej tabeli, a osobna część z wartościami z pamięci podręcznej przechowuje ostatni znany wynik dla każdej przywoływanej komórki. Dlatego plik otwarty bez swoich towarzyszy nadal pokazuje liczby

Parsowanie formy tekstowej ma swoje subtelności. Lekser traktuje nazwę pliku w nawiasach kwadratowych jako część jednego identyfikatora, więc [Book.xlsx]Sheet!A1 staje się jednym tokenem, a nie niesparowanym nawiasem, po którym następuje odwołanie do arkusza. Nazwy są następnie normalizowane na potrzeby wyszukiwania: cudzysłowy, prefiksy ścieżek, rozszerzenie pliku i forma z indeksem liczbowym zwijają się do nazwy bazowej pisanej wielkimi literami, więc 'C:\Rates\[Rates.xlsx]FX'!B4 i [rates.xlsx]FX!B4 rozwiązują się do tego samego zarejestrowanego pliku

Rejestrowanie skoroszytu i przeliczanie

Rejestracja odbywa się po nazwie, a nazwą jest ta, która pojawia się w formułach skoroszytu dokonującego odwołania:

uses
  lxHandleX;

var
  Main, Rates: TXLSXWorkbook;
begin
  Main := TXLSXWorkbook.Create;
  Rates := TXLSXWorkbook.Create;
  try
    if (Main.Open('valuation.xlsx') <> 1) or
       (Rates.Open('rates.xlsx') <> 1) then
      Exit;

    // Formuły w valuation.xlsx zawierające [Rates.xlsx]... czytają teraz
    // z żywego obiektu zamiast wartości zewnętrznych z pamięci podręcznej
    Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
    Main.Recalculate;

    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));

    // Zmień źródło i przelicz ponownie: nowa wartość przepływa dalej
    Rates.Sheets[0].Cells[4, 2].Value := 1.0842;
    Main.Recalculate;
    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
  finally
    Main.UnregisterExternalWorkbook('Rates.xlsx');
    Rates.Free;
    Main.Free;
  end;
end;

Rejestracja utrzymuje słabą referencję. HotXLS nie przejmuje własności zarejestrowanego skoroszytu i go nie zwolni, co oznacza, że zarządzanie czasem życia należy do ciebie, a kolejność ma znaczenie: wyrejestruj przed zwolnieniem skoroszytu źródłowego, albo przynajmniej nie przeliczaj po jego zwolnieniu

Co się dzieje, gdy plik nie jest zarejestrowany?

Obliczenia przechodzą na pamięć podręczną odsyłaczy zewnętrznych zapisaną w pliku, co jest dokładnie zachowaniem skoroszytu otwartego bez swoich towarzyszy. To przejście zapasowe jest powodem, dla którego wyrejestrowanie jest bezpieczne, a nie destrukcyjne

Warto precyzyjnie opisać zachowanie po wyrejestrowaniu, ponieważ zaskakuje ludzi. Gdy odwołania nie da się rozwiązać ani z zarejestrowanego pliku, ani z pamięci podręcznej, obliczenie daje błąd, a ten błąd nie jest zapisywany z powrotem do komórki. Komórka zachowuje swoją ostatnią obliczoną wartość. Zarejestruj ponownie źródło i przelicz, a wartość znowu się zaktualizuje

Ta zasada zachowania wartości przy błędzie jest celowa. Alternatywa, czyli zapisanie wartości błędu w każdej zależnej komórce w chwili, gdy źródło jest niedostępne, zamieniłaby tymczasowo brakującą tabelę kursów w skoroszyt pełen znaczników błędów, które musiałoby naprawić późniejsze udane przeliczenie. Zachowanie ostatniej prawidłowej wartości oznacza, że przerwany potok pozostawia dokument, który nadal ma sensowną treść

Propagacja zmian zatrzymuje się na granicy skoroszytu

Edycja komórki w zarejestrowanym źródle nie oznacza zależnych komórek w głównym skoroszycie jako wymagających przeliczenia. Przyrostowe przeliczanie śledzi zależności wewnątrz jednego skoroszytu; nie obserwuje przez granicę. Excel zachowuje się tak samo, dlatego jego odwołania zewnętrzne aktualizują się przy otwarciu albo przy jawnym odświeżeniu, a nie w sposób ciągły

Praktyczną konsekwencją jest to, że zmiana wartości źródłowej wymaga jawnego przeliczenia skoroszytu odwołującego się do niej, jak w powyższym przykładzie. Jeśli twoja aplikacja edytuje oba pliki w pętli, przelicz raz na końcu, a nie po każdej edycji; przyrostowy silnik opisany w przyrostowym przeliczaniu i grafie zależności wykonuje wtedy minimum pracy wewnątrz każdego skoroszytu

Trzy przypadki, do których pasuje ta funkcja

Konsolidacja to oczywisty przypadek. Skoroszyt nadrzędny odwołuje się do dwunastu plików miesięcznych; zarejestruj wszystkie dwanaście, przelicz raz i odczytaj skonsolidowane liczby, nigdy nie otwierając Excela. Nazwa rejestracji dla każdego musi odpowiadać temu, co faktycznie mówią formuły nadrzędnego pliku, co warto zweryfikować względem tekstu formuły, a nie zakładać, że pasuje do nazwy pliku

Analiza scenariuszy to drugi przypadek. Zarejestruj źródło, zapisz w nim zestaw założeń, przelicz zależny model, zapisz wyniki, a następnie powtórz. Ponieważ źródło jest żywym obiektem, między iteracjami nie zapisuje się żaden plik, a sto scenariuszy kosztuje sto przeliczeń, a nie sto cykli zapisu i ponownego wczytania

Walidacja przed dostarczeniem to trzeci i najmniej oczywisty przypadek. Otwórz skoroszyt, zarejestruj jego towarzyszy, przelicz i porównaj wyniki z wartościami, które plik już niósł. Niezgodność oznacza, że wartości z pamięci podręcznej są nieaktualne, co jest defektem wartym wykrycia, zanim klient otrzyma plik. To porównanie naturalnie uzupełnia porównywanie skoroszytów opisane w porównywaniu dwóch skoroszytów Excela

Higiena odwołań, zanim zaczniesz na nich polegać

Najpierw uruchom przebieg analizy formuł. Zgłasza on brakujące funkcje, odwołania zewnętrzne i składowe zależności cyklicznych w jednym przejściu, co dokładnie mówi ci, jakich nazw plików oczekuje dokument, zanim zaczniesz szukać plików:

var
  Issues: TArray<TXLSXFormulaIssue>;
  I: Integer;
begin
  Issues := Main.AnalyzeFormulaIssues;
  for I := 0 to High(Issues) do
    if Issues[I].Kind = xlsxFormulaIssueExternalReference then
      Writeln(Format('sheet %d R%dC%d needs [%s] (%s)',
        [Issues[I].Sheet, Issues[I].Row, Issues[I].Col,
         Issues[I].ExternalWorkbook, Issues[I].SourceFileName]));
end;

Dwa inne nawyki oszczędzają czas. Utrzymuj kanoniczną nazwę rejestracji — rejestruj pod zwykłą nazwą pliku i pozwól normalizacji obsłużyć w formułach warianty w cudzysłowach i z prefiksem ścieżki. I traktuj skoroszyt odwołujący się do plików, których nie możesz dostarczyć, jako artefakt tylko do odczytu: przeliczenie go zachowa wartości z pamięci podręcznej, ale każda edycja zmieniająca zależną komórkę pozostawia dokument w stanie, w którym część wartości jest aktualna, a część historyczna, przy czym nic w pliku nie mówi, która jest która. Kopiowanie formuł między skoroszytami to inna operacja z własnymi regułami przewiązywania, opisana w kopiowaniu między skoroszytami i przewiązywaniu formuł, a nazwane zakresy dodają kolejną warstwę opisaną w nazwach zdefiniowanych i formułach między arkuszami

Obliczanie na żywo między skoroszytami, przyrostowe przeliczanie i analiza formuł działają na tym samym silniku dla Delphi i C++Builder; pełną listę funkcji znajdziesz na stronie komponentu arkusza kalkulacyjnego HotXLS dla Delphi