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

Knihovna HotXLS automaticky upravuje odkazy ve vzorcích při vkládání nebo mazání řádků či sloupců v listu XLSX. Metody jádra InsertRows, DeleteRows, InsertCols a DeleteCols přepisují každý zachovaný vzorec tak, aby jeho odkazy A1 — relativní, absolutní i rozsahy — po strukturální úpravě nadále ukazovaly na stejná data, a odkazy směřující do smazaného bloku se změnily na #REF!, což odpovídá chování Excelu

Chyba, které se tím předchází, patří k těm nejméně nápadným při generování reportů. Generátor zapíše denní údaje do oblasti C2:C9 se vzorcem =SUM(C2:C9) pod ní, a v pozdějším kroku vloží záhlaví na začátek. Pokud jádro pouze přesune hodnoty buněk a text vzorce ponechá beze změny, vzorec SUM bude stále sčítat C2:C9, zatímco data se nyní nacházejí v C3:C10 — výsledný součet tak tiše vynechá poslední den a záhlaví započítá dvakrát. Nic nevyvolá výjimku, soubor se bez problému otevře, ale číslo je prostě špatně. Před verzí 2.160 ponechávalo jádro HotXLS XLSX vzorce během strukturálních úprav beze změny; od verze 2.160 probíhá přepis automaticky a není třeba nastavovat žádné přepínače

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

Pravidlo Excelu zní, že odkazy následují data, nikoli adresy. Při vložení řádku se každý odkaz, jehož index řádku se nachází na úrovni nebo pod bodem vložení, posune dolů o počet vložených řádků; odkazů zcela nad bodem vložení se to nedotkne. Mazání řádků uplatňuje stejné pravidlo v opačném směru: odkazy pod smazaným blokem se posunou nahoru a odkazy směřující do samotného smazaného bloku se změnily na #REF!, protože buňky, které pojmenovávaly, již neexistují. Sloupce se chovají identicky podél druhé osy. Knihovna pro tabulkové procesory, která chce, aby její výstupy obstály před uživateli Excelu, musí tyto mechanismy přesně reprodukovat, protože uživatelé uvažují o svých vzorcích v těchto intencích, aniž by nad tím přemýšleli

Část, která vývojáře překvapuje, je, že se posouvají i absolutní odkazy. Znaky $ v $B$2 určují, co se stane, když se vzorec kopíruje nebo vyplňuje do jiné buňky — během strukturálních úprav nedělají nic. Vložte řádek nad řádek 2 a Excel přepíše $B$2 na $B$3 se zachováním dolarů, protože hodnota, na které vzorec závisí, se fyzicky přesunula na řádek 3. Jádro, které by posouvalo pouze relativní odkazy, by poškodilo právě ty vzorce, které lidé ukotvují nejzáměrněji. HotXLS posouvá obě formy a zachovává znaky $ v přepsaném textu

Jak HotXLS automaticky posouvá odkazy ve vzorcích?

Všechny čtyři metody strukturálních úprav v TXLSXWorksheet delegují práci na jediné geometrické jádro: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). Metoda InsertRows(BeforeRow, Count) jej volá s kladným delta řádku, DeleteRows(StartRow, Count) se záporným a metody sloupců provádějí totéž na ose sloupců. Procedura nejprve přemístí samotné buňky — přičemž zahodí ty, které spadají do smazaného bloku — a poté projde každou zachovanou buňku se vzorcem a předá její text funkci XlsxAdjustFormulaRowColRefs, což je scanner, který vyhledá odkazy ve stylu A1 a přepíše jejich řádkové a sloupcové komponenty podle posunu. Stejný průchod přemisťuje sloučené oblasti, odkazy, komentáře, obrázky, grafy, podmíněné formáty, validace dat a oblasti tabulek, so the whole sheet moves as one unit

// Layout before the edit:
//   C2..C9  daily figures
//   C10     =SUM(C2:C9)
Sheet.InsertRows(2, 1);      // one blank row before row 2
// Layout after the call:
//   C3..C10 daily figures
//   C11     =SUM(C3:C10)    -- the range moved with the data

Stojí za to znát několik detailů o tomto scanneru. Funkce XlsxAdjustFormulaRowColRefs rozpoznává odkazy na jednotlivé buňky ve všech čtyřech formách ukotvení (A1, $A1, A$1, $A$1) a dvourohé rozsahy jako A1:B3, přičemž každý koncový bod upravuje samostatně. Vzorce, jejichž text již začíná znakem # — chybovým markerem z dřívější úpravy —, se přeskakují, místo aby se znovu skenovaly. A metoda InsertCols přidává jednu vlastní drobnost pro shodu s Excelem: nově vložené sloupce dědí šířku svého levého souseda, což je to, co dělá příkaz Vložit sloupce listu v Excelu

Kdy se smazaný odkaz stane chybou #REF!?

Pravidlo posunu pro index jednoho řádku nebo sloupce má tři výsledky. Index před bodem úpravy se nemění. Index v bodě úpravy nebo za ním se posune o delta. A při mazání nemá index spadající do smazaného bloku žádnou smysluplnou novou hodnotu — buňka je pryč — takže scanner přepíše celý odkaz na #REF!. U odkazu na rozsah se obou koncových bodů týká stejné pravidlo, a pokud kterýkoli z koncových bodů přistane uvnitř smazaného bloku, odkaz se přepíše na #REF!, místo aby zůstal napůl platný

// A12 holds  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // delete rows 5..7
// The formula, now in A9, reads  =A4+#REF!+A7
//   A4  : above the deleted block, unchanged
//   A6  : inside rows 5..7, gone      -> #REF!
//   A10 : below the block, slides up  -> A7

Vygenerování zjevné chyby #REF! namísto tichého přesměrování je správný kompromis, a je to ten, který volí Excel. Vzorec, který by po smazání svého skutečného vstupu ukazoval na sousední buňku, by vracel věrohodně vypadající číslo; chyba #REF! se šíří závislými vzorci a objeví se hned při prvním testu. Stejný převod platí i na ose sloupců

// E1 holds  =B1*$C$1
Sheet.DeleteCols(3, 1);      // remove column C
// The formula, now in D1, reads  =B1*#REF!
// The absolute anchor did not protect $C$1 -- the cell itself is gone

Které formy odkazů se nepřepisují?

Scanner se zaměřuje na odkazy A1 na stejném listu s explicitním tvarem písmeno sloupce plus číslo řádku, a je dobré přesně vymezit, co stojí mimo to. Odkazům na celé sloupce jako A:A a na celé řádky jako 1:1 chybí jedna ze dvou komponent, takže je scanner ponechává v původním tvaru. Vyjmenované tabulkové odkazy (Table1[Amount]) procházejí rovněž beze změny. Přepis také funguje výhradně v notaci A1 — pokud váš kód sestavuje vzorce ve stylu R1C1, převeďte je před strukturální úpravou na A1, jak je popsáno v doprovodném článku o notaci vzorců R1C1 v Delphi

Konstrukce napříč listy a na úrovni sešitu

Konstrukce napříč listy a na úrovni sešitu se zpracovávají v samostatných průchodech, nikoli scannerem textu buněk. Po úpravě editovaného listu přenese metoda ShiftSheetGeometry stejnou geometrickou změnu do vzorců na ostatních listech, které na editovaný list odkazují, do rozsahů řad grafů, na cíle interních odkazů a na definované názvy. Definované názvy získávají další ochranu na úrovni životního cyklu listu: od verze 2.150 přepíše smazání listu každý kvalifikátor ListN! 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 neexistující list. Jak spolu souvisí názvy a vzorce napříč listy popisuje článek o definovaných názvech a vzorcích napříč listy

Přepočet po posunu

Úprava odkazů přepisuje text vzorce; nepřepočítává výsledky. Po strukturální úpravě popisují uložené hodnoty mezipaměti vedle vzorců starou geometrii, takže spolehlivým postupem je: nejprve provést všechna vložení a smazání, poté jednou spustit přepočet a teprve pak uložit. Provedení posunu před přepočtem také udržuje pravdivé informace o závislostech — každý přepsaný odkaz jmenuje svůj skutečný předchozí prvek, což je přesně to, co jádro pro inkrementální přepočet a jeho graf závislostí potřebují k přepočtení minimální sady dotčených buněk. Pokud přepsaný vzorec nyní obsahuje #REF!, přepočet okamžitě vynese chybovou hodnotu na povrch, místo aby v souboru ponechal neaktuální číslo

Úprava odkazů ve vzorcích se dodává jako standardní chování jádra XLSX v produktu HotXLS Delphi Excel Component pro Delphi a C++Builder; produktová stránka obsahuje kompletní referenční příručku pro úpravy listů, včetně zde popsaných metod vkládání a mazání