Odkazy ve vzorcích při vkládání řádků v HotXLS

HotXLS automaticky upravuje odkazy ve vzorcích, když v listu XLSX vkládáte nebo mažete řádky či sloupce. Metody enginu InsertRows, DeleteRows, InsertCols a DeleteCols přepíší každý přeživší vzorec tak, aby jeho odkazy A1 — relativní, absolutní i oblastní — po strukturální úpravě dál ukazovaly na tatáž data, a odkazy do smazaného bloku se změní na #REF!, přesně jako v Excelu

Chyba, které tím předcházíte, patří k nejtišším v generování reportů. Generátor zapíše denní hodnoty do C2:C9 a pod ně =SUM(C2:C9), poté pozdější krok vloží nahoru řádek záhlaví. Pokud engine přesune jen hodnoty buněk a text vzorců nechá být, ten SUM stále čte C2:C9, zatímco data teď leží v C3:C10 — součet tedy tiše vynechá poslední den a záhlaví započítá dvakrát. Nic nevyvolá výjimku, soubor se otevře bez problémů a číslo je prostě špatně. Před verzí 2.160 nechával engine XLSX v HotXLS vzorce při strukturálních úpravách nedotčené; od verze 2.160 je přepis automatický a není potřeba nastavovat žádný příznak

Co se stane se vzorci, když v Excelu vložíte řádek?

Pravidlo Excelu zní: odkazy sledují data, ne adresy. Při vložení řádku se každý odkaz, jehož index řádku leží na místě vložení nebo pod ním, posune dolů o počet vložených řádků; odkazy zcela nad místem vložení zůstávají nedotčeny. Mazání řádků uplatňuje totéž pravidlo obráceně: odkazy pod smazaným blokem se posunou nahoru a odkazy do samotného smazaného bloku se změní na #REF!, protože buňky, které pojmenovávaly, už neexistují. Sloupce se chovají totožně podél druhé osy. Tabulková knihovna, která chce, aby její výstup přežil kontakt s uživateli Excelu, musí tuto mechaniku reprodukovat přesně, protože uživatelé o svých vzorcích v těchto pojmech uvažují, aniž by o tom kdy přemýšleli

Vývojáře překvapí, že se posouvají i absolutní odkazy. Kotvy $ v $B$2 řídí, co se stane, když se vzorec kopíruje nebo vyplňuje do jiné buňky — při strukturálních úpravách nedělají nic. Vložte řádek nad řádek 2 a Excel přepíše $B$2 na $B$3, dolary zůstanou, protože hodnota, na které vzorec závisí, se fyzicky přesunula do řádku 3. Engine, který by posouval jen relativní odkazy, by poškodil přesně ty vzorce, které lidé kotví nejzáměrněji. HotXLS posouvá obě formy a v přepsaném textu zachovává značky $

Jak HotXLS posouvá odkazy ve vzorcích automaticky?

Všechny čtyři metody strukturálních úprav na TXLSXWorksheet delegují na jediný geometrický engine: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) jej volá s kladnou deltou řádků, DeleteRows(StartRow, Count) se zápornou a sloupcové metody dělají totéž na ose sloupců. Rutina nejprve přemístí samotné buňky — každou buňku, která padne do smazaného bloku, zahodí — a poté projde každou přeživší buňku se vzorcem a její text prožene funkcí XlsxAdjustFormulaRowColRefs, skenerem, který najde odkazy ve stylu A1 a přepíše jejich řádkovou a sloupcovou složku podle posunu. Tentýž průchod přemístí sloučené oblasti, hypertextové odkazy, komentáře, obrázky, grafy, podmíněné formáty, ověření dat a oblasti tabulek, takže se celý list pohne jako jeden celek

// Rozvržení před úpravou:
//   C2..C9  denní hodnoty
//   C10     =SUM(C2:C9)
Sheet.InsertRows(2, 1);      // jeden prázdný řádek před řádkem 2
// Rozvržení po volání:
//   C3..C10 denní hodnoty
//   C11     =SUM(C3:C10)    -- oblast se posunula s daty

Několik detailů skeneru stojí za to znát. XlsxAdjustFormulaRowColRefs rozpoznává odkazy na jednu buňku ve všech čtyřech formách ukotvení (A1, $A1, A$1, $A$1) a dvourohové oblasti jako A1:B3, přičemž každý krajní bod upravuje nezávisle. Vzorce, jejichž text už začíná znakem # — značka chyby z dřívější úpravy — se přeskakují, místo aby se znovu skenovaly. A InsertCols přidává jednu vlastní drobnost pro paritu s Excelem: nově vložené sloupce zdědí šířku svého levého souseda, což je to, co dělá příkaz Excelu Vložit sloupce listu

Kdy se smazaný odkaz změní na #REF!?

Pravidlo posunu pro jediný index řádku nebo sloupce má tři výsledky. Index před bodem úpravy se nemění. Index na bodu úpravy nebo za ním se posune o deltu. A při mazání nemá index, který padne dovnitř smazaného bloku, žádnou smysluplnou novou hodnotu — buňka je pryč — takže skener přepíše celý odkaz na #REF!. U oblastního odkazu projdou oba krajní body týmž pravidlem, a pokud kterýkoli z nich přistane uvnitř smazaného bloku, odkaz se přepíše na #REF!, místo aby zůstal napůl platný

// A12 obsahuje  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // smaže řádky 5..7
// Vzorec, nyní v A9, zní  =A4+#REF!+A7
//   A4  : nad smazaným blokem, beze změny
//   A6  : uvnitř řádků 5..7, pryč     -> #REF!
//   A10 : pod blokem, posune se nahoru -> A7

Vytvořit hlasité #REF! místo tichého přesměrování je správná volba a je to ta, kterou dělá Excel. Vzorec, který by po smazání svého skutečného vstupu ukazoval na sousední buňku, by vrátil věrohodně vypadající číslo; #REF! se šíří závislými vzorci a vyplave při prvním smoke testu. Stejná konverze platí na ose sloupců

// E1 obsahuje  =B1*$C$1
Sheet.DeleteCols(3, 1);      // odstraní sloupec C
// Vzorec, nyní v D1, zní  =B1*#REF!
// Absolutní ukotvení $C$1 neochránilo -- samotná buňka je pryč

Které formy odkazů se nepřepisují?

Skener cílí na odkazy A1 v rámci téhož listu s explicitním tvarem písmeno sloupce plus číslo řádku a stojí za to přesně říci, co mimo to spadá. Odkazy na celé sloupce jako A:A a na celé řádky jako 1:1 postrádají jednu ze dvou složek, takže je skener nechá tak, jak jsou napsány. Strukturované odkazy na tabulky (Table1[Amount]) rovněž projdou nedotčené. Přepis také pracuje výhradně s notací A1 — pokud váš kód sestavuje vzorce ve stylu R1C1, převeďte je před strukturální úpravou na A1, jak popisuje doprovodný článek o notaci vzorců R1C1 v Delphi

Konstrukce napříč listy a na úrovni sešitu zpracovávají samostatné průchody, ne skener textu buněk. Po úpravě editovaného listu ShiftSheetGeometry propaguje tutéž změnu geometrie do vzorců na jiných listech, které na editovaný list odkazují, do oblastí řad grafů, do cílů interních hypertextových odkazů a do definovaných názvů. Definované názvy mají další ochranu na úrovni životního cyklu listu: od verze 2.150 přepíše smazání listu každý kvalifikátor SheetN! uvnitř vzorce definovaného názvu na #REF! a přejmenování listu přepíše kvalifikátor na nový název, takže názvy nikdy neukazují na list, který už neexistuje. Jak do sebe zapadají názvy a vzorce napříč listy, popisuje článek o definovaných názvech a vzorcích napříč listy

Po posunu přepočítejte

Úprava odkazů přepisuje text vzorců; výsledky nepřepočítává. Po strukturální úpravě popisují hodnoty uložené v cache vedle vzorců starou geometrii, takže spolehlivá posloupnost zní: nejprve proveďte všechna vložení a mazání, poté jednou spusťte přepočet a pak uložte. Spuštění posunu před přepočtem také udržuje informace o závislostech poctivé — každý přepsaný odkaz jmenuje svého skutečného předchůdce, což je přesně to, co engine inkrementálního přepočtu a jeho graf závislostí potřebují k přepočítání minimální množiny dotčených buněk. Pokud přepsaný vzorec nyní obsahuje #REF!, přepočet chybovou hodnotu vyplaví okamžitě, místo aby v souboru nechal zastaralé číslo

Úprava odkazů ve vzorcích je standardním chováním enginu XLSX v komponentě HotXLS pro Excel v Delphi pro Delphi a C++Builder; produktová stránka obsahuje úplnou referenci API pro úpravy listů, včetně zde ukázaných metod pro vkládání a mazání