Živé mezisešitové vzorce v Delphi s HotXLS

HotXLS umí vyhodnotit vzorec jako =[Rates.xlsx]FX!B4*C10 proti jinému sešitu, který je otevřený v paměti, ne proti zastaralé mezipaměťové hodnotě. Druhý sešit zaregistrujete jménem přes RegisterExternalWorkbook a každý přepočet čte referencované buňky přímo z tohoto objektu. Zrušte registraci a vyhodnocení se vrátí k mezipaměti externích odkazů, kterou soubor už nese

Tím se uzavírá konkrétní a otravná mezera. Sešity Excelu na sebe běžně odkazují navzájem: tabulka kurzů, účtová osnova, ceník udržovaný jiným týmem. Když takový soubor otevře knihovna místo Excelu, tyto odkazy se normálně rozřeší na jakékoli hodnoty, které byly v mezipaměti při posledním přepočtu v Excelu, což mohou být měsíce staré hodnoty, a žádný počet přepočtů to nespraví, protože ta data tam jednoduše nejsou

Jak vypadá mezisešitový odkaz uvnitř souboru?

Externí odkaz se neukládá jako text, který vidíte v řádku vzorců. Sešit udržuje tabulku podpůrných sešitů, každý token vzorce ukazuje na index do této tabulky a samostatná část s mezipaměťovými hodnotami drží poslední známý výsledek pro každou referencovanou buňku. Proto soubor otevřený bez svých společníků stále zobrazuje čísla

Parsování textové formy má vlastní úskalí. Lexer zachází s názvem sešitu v hranatých závorkách jako se součástí jediného identifikátoru, takže [Book.xlsx]Sheet!A1 se stane jedním tokenem místo nevyváženou závorkou následovanou odkazem na list. Názvy se pak normalizují pro vyhledávání: uvozovky, prefixy cest, přípona souboru i číselná indexová forma se sbalí do jednoho velkými písmeny psaného základního jména, takže 'C:\Rates\[Rates.xlsx]FX'!B4 a [rates.xlsx]FX!B4 se rozřeší na stejný zaregistrovaný sešit

Registrace sešitu a přepočet

Registrace probíhá jménem, a tím jménem je to, které se objevuje ve vzorcích sešitu, jenž odkazuje:

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;

    // Vzorce v valuation.xlsx, které říkají [Rates.xlsx]..., nyní čtou
    // z živého objektu místo z mezipaměťových externích hodnot
    Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
    Main.Recalculate;

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

    // Změňte zdroj a přepočítejte znovu: nová hodnota se propíše dál
    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;

Registrace drží slabý odkaz. HotXLS nepřebírá vlastnictví zaregistrovaného sešitu a neuvolní ho, což znamená, že životnost spravujete vy a na pořadí záleží: zrušte registraci před uvolněním zdrojového sešitu, nebo přinejmenším po jeho uvolnění nepřepočítávejte

Co se stane, když sešit není zaregistrován?

Vyhodnocení se vrátí k mezipaměti externích odkazů uložené v souboru, což je přesně chování sešitu otevřeného bez svých společníků. Tato záložní varianta je důvodem, proč je zrušení registrace bezpečné, ne destruktivní

Chování po zrušení registrace stojí za přesné vyjádření, protože lidi překvapí. Když odkaz nelze rozřešit ani z registrovaného sešitu, ani z mezipaměti, vyhodnocení vyprodukuje chybu a tato chyba se nezapíše zpět do buňky. Buňka si podrží svou poslední vypočtenou hodnotu. Znovu zaregistrujte zdroj a přepočítejte a hodnota se opět aktualizuje

Toto pravidlo zachování při selhání je záměrné. Alternativa, zápis chybové hodnoty do každé závislé buňky v okamžiku, kdy zdroj přestane být dostupný, by proměnila dočasně chybějící tabulku kurzů v sešit plný chybových značek, které by musel opravit až pozdější úspěšný přepočet. Zachování poslední dobré hodnoty znamená, že přerušený pipeline zanechá dokument, který se dá stále smysluplně číst

Šíření příznaku "špinavý" se zastaví na hranici sešitu

Úprava buňky v registrovaném zdroji neoznačí závislé buňky v hlavním sešitu jako špinavé. Inkrementální přepočet sleduje závislosti uvnitř jednoho sešitu; přes hranici nehlídá. Excel se chová stejně, což je důvod, proč se jeho externí odkazy aktualizují při otevření nebo při explicitním obnovení, ne průběžně

Praktickým důsledkem je, že změna zdrojové hodnoty vyžaduje explicitní přepočet sešitu, který odkazuje, jako ve výše uvedeném příkladu. Pokud vaše aplikace upravuje oba sešity ve smyčce, přepočítejte jednou na konci, ne po každé úpravě; inkrementální engine popsaný v článku inkrementální přepočet a graf závislostí pak uvnitř každého sešitu odvede minimum práce

Tři podoby, do kterých tato funkce zapadá

Konsolidace je ta zjevná. Nadřazený sešit odkazuje na dvanáct měsíčních souborů; zaregistrujte všech dvanáct, přepočítejte jednou a čtěte konsolidované hodnoty, aniž byste kdy otevřeli Excel. Registrační jméno pro každý z nich se musí shodovat s tím, co ve vzorcích skutečně říká nadřazený sešit, což se vyplatí ověřit proti textu vzorce, ne jen předpokládat, že se shoduje s názvem souboru

Scénářová analýza je druhá. Zaregistrujte zdroj, zapište do něj sadu předpokladů, přepočítejte závislý model, zaznamenejte výstupy a opakujte. Protože zdroj je živý objekt, mezi iteracemi se nezapisuje žádný soubor, a sto scénářů stojí sto přepočtů místo sta cyklů uložení a znovunačtení

Validace před dodáním je třetí a nejméně zjevná. Otevřete sešit, zaregistrujte jeho společníky, přepočítejte a porovnejte výsledky s hodnotami, které soubor už nesl. Neshoda znamená, že mezipaměťové hodnoty jsou zastaralé, což je defekt, který stojí za odhalení dřív, než soubor dostane klient. Toto porovnání je přirozeným společníkem porovnávání sešitů popsaného v článku porovnávání dvou sešitů Excel

Hygiena odkazů, než se na ni spolehnete

Nejprve spusťte průchod analýzy vzorců. V jednom průchodu hlásí chybějící funkce, externí odkazy a komponenty cyklických závislostí, což vám přesně řekne, jaká jména sešitů dokument očekává, ještě než začnete pátrat po souborech:

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;

Dva další zvyky ušetří čas. Udržujte registrační jméno kanonické — registrujte pod prostým názvem souboru a nechte normalizaci vypořádat se s citovanými a cestou prefixovanými variantami ve vzorcích. A se sešitem, který odkazuje na soubory, jež nemůžete dodat, zacházejte jako s artefaktem jen pro čtení: přepočet ho zachová v mezipaměťových hodnotách, ale jakákoli úprava, která změní závislou buňku, zanechá dokument ve stavu, kdy jsou některé hodnoty aktuální a jiné historické, aniž by v souboru bylo cokoli, co by řeklo, které jsou které. Kopírování vzorců mezi sešity je jiná operace s vlastními pravidly přeadresování, popsaná v článku mezisešitové kopírování a přeadresování vzorců, a pojmenované oblasti přidávají další vrstvu popsanou v článku definované názvy a vzorce napříč listy

Živé mezisešitové vyhodnocování, inkrementální přepočet a analýza vzorců běží na stejném enginu pro Delphi a C++Builder; úplný seznam funkcí najdete na stránce komponenty HotXLS Delphi spreadsheet component