Metoda AddCopy v HotXLS kopíruje list z jednoho sešitu Excelu do jiného tak, že dekompiluje každý vzorec na tomto listu do textu ve stylu A1 a tento text znovu zkompiluje uvnitř cílového sešitu, místo aby zkopírovala zkompilovaný strom vzorce přímo, protože odkazy řad grafu, indexy fontů formátovaného textu a číslování externích odkazů jsou v každém souboru sešitu přiřazovány nezávisle
Selhání se projeví přesně v tom sešitu, který byste čekali: úloha na konci měsíce, která z reportu každé pobočky vytáhne jeden list a připojí jej do souhrnného souboru. Otevřete výsledek a graf mezisoučtu vykresluje úplně jiné číslo jiné pobočky, poznámka, která byla ve zdroji tučná a červená, je zpátky prostý černý text a vzorec, který dřív čerpal daňovou sazbu z doprovodného vyhledávacího sešitu, teď ukazuje zamrzlé číslo, které nikdo nedokáže vysvětlit. Nic tady nevyvolá výjimku — soubor se otevře, čísla vypadají věrohodně, a škoda tam sedí, dokud si někdo nevšimne grafu se špatným titulkem vedle ní
Proč AddCopy prostě nezkopíruje zkompilovaný strom vzorce?
AddCopy nemůže přesunout zkompilovaný strom vzorce beze změny, protože zkompilovaný vzorec BIFF není samostatně stojící text — je to sekvence tokenů a několik z těchto tokenů jsou malá celá čísla, která se správně rozřeší jen uvnitř sešitu, který je vyprodukoval. 3D odkaz jako Sheet2!A1:A10 po zkompilování nenese doslovné jméno Sheet2; nese pole, které specifikace BIFF nazývá ixti (HotXLS drží stejnou hodnotu ve vlastním zkompilovaném stromu pod jménem pole FExternID), index do soukromé tabulky EXTERNSHEET tohoto sešitu, očíslovaný podle toho, jak zrovna tento konkrétní sešit náhodou registroval své listy a externí sešity. Přesuňte token beze změny do sešitu, jehož tabulka EXTERNSHEET byla sestavena v jiném pořadí, a index 3 už neznamená Sheet2 — znamená cokoli, co tam náhodou obsazuje slot 3, a Excel nemá způsob, jak tuto chybu označit, protože z hlediska formátu souboru je vzorec dokonale správně formovaný. Přesně tomuto selhání se snaží zabránit TXLSWorksheets.AddCopy: volaná z kolekce listů kteréhokoli sešitu v kódu Delphi nebo C++Builderu zkopíruje list — hodnoty buněk, formáty, vzorce, grafy, komentáře, sloučení, nastavení stránky a další — ze zdrojového sešitu, který nemusí být tím, na kterém ji voláte, a připojí výsledek do cíle pod jménem podle vaší volby nebo pod jednoznačnou kopií originálu
var
Summary, Branch: IXLSWorkbook; // interface-counted: do not Free
begin
Summary := TXLSWorkbook.Create;
Branch := TXLSWorkbook.Create;
Branch.Open('branch-east.xls');
// Appends a copy of Branch's first sheet onto Summary, renamed to
// stay unique inside the destination workbook
Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
Summary.SaveAs('consolidated.xls');
end;Řešení: dekompilovat na text, znovu zkompilovat v cíli
HotXLS řeší problém indexování tak, že nikdy nenechá samotný zkompilovaný strom překročit hranici sešitu. Pro každou vzorcovou buňku při kopírování mezi sešity AddCopy dekompiluje zdrojový vzorec do stejného textu ve stylu A1, jaký by uživatel viděl v řádku vzorců Excelu, a pak tento text předá cílovému sešitu, který jej znovu naparsuje do stromu pomocí vlastních tabulek od nuly — odkaz kvalifikovaný listem, jako Data!D2:D100, je v tu chvíli jen řetězec, a řetězec znamená totéž v jakémkoli sešitu, takže pokud cíl už má list jménem Data, odkaz se správně rozřeší úplně bez jakéhokoli překladu indexu, protože žádný surový index nikdy nebyl v pohybu k překladu. HotXLS za tento okružní výlet platí jen tehdy, když musí: kopírování listu uvnitř stejného sešitu jde levnější cestou, kde se zkompilovaný strom prostě zduplikuje v paměti, protože každý index uvnitř něj je už platný tam, kde zůstává, a text objížďka běží jen ve chvíli, kdy AddCopy zjistí, že zdroj a cíl jsou skutečně odlišné instance sešitu. Vyplatí se také přesně říct, čím tento přepis není. Nemá nic společného s posunem řádků a sloupců, který běží, když vkládáte nebo mažete řádky uvnitř jednoho listu, což podrobně popisuje doprovodný článek — ten engine přepisuje text A1 na místě, aby sledoval buňky, které se posunuly o pár řádků nahoru nebo dolů uvnitř jednoho sešitu, zatímco tento běží, když vzorec úplně opustí sešit, který jej zkompiloval, kde posunuté řádky nejsou problém a soukromé číslování sešitu ano
// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);Co když cíl ještě nemá tento list, nebo toto jméno?
Rekompilace v AddCopy uspěje jen tehdy, když cílový sešit už má vše, na co text vzorce odkazuje, a dvě mezery, které se v praxi objevují, jsou stejnojmenný list, který ještě nebyl v této dávce zkopírován, a jméno definované v rozsahu sešitu, které v cíli nikdy neexistovalo. HotXLS při selhání rekompilace uprostřed kopírování listu nevyvolá výjimku — přiřazení Value buňky místo toho tiše uloží text vzorce jako obyčejný řetězec, záměrný, kontrolovatelný režim selhání místo tichého, protože vzorcová buňka, která nečekaně ukazuje doslovný text jako =SUM(Q1!B2:B12) místo spočítaného čísla, je znamení, že se něco proti proudu v kopírování nerozřešilo. Než se vzdá, AddCopy zkusí jednu opravu: projde strom syntaxe selhavšího vzorce a posbírá každé ID definovaného jména, kterého se vzorec dotýká, a pro každé jméno v rozsahu sešitu, které existuje ve zdroji, ale ještě ne v cíli, jej zkopíruje přes a podruhé rekompiluje stejný text. Jména v rozsahu listu sedí mimo to, co tato oprava dokáže opravit, protože jméno viditelné jen pro vzorce na jednom listu zdrojového sešitu nemá ekvivalentní slot, do kterého by migrovalo, a cíl, který už vlastní jméno se stejným pravopisem, se ponechá nedotčený místo přepsání, na základě předpokladu, že jméno, které volající záměrně předvytvořil, je to, které chce respektovat. Uvnitř jednoho sešitu vyhledávání jména vzorce napříč listy prochází z rozsahu listu nahoru do rozsahu sešitu automaticky, což je mechanismus, který popisuje článek HotXLS o definovaných jménech a vzorcích napříč listy; překročení skutečné hranice sešitu tuto záchrannou síť úplně odstraní, a jméno musí být buď záměrně přeneseno přes, nebo vzorec, který na něm závisí, degraduje na text
Odkazy řad grafu potřebují stejnou opravu, ale jinou cestu kódu
Řada grafu HotXLS, která vykresluje rozsah buněk, naráží přesně na stejný problém číslování jako obyčejný vzorec buňky, protože odkaz datového rozsahu grafu je také proud tokenů zkompilovaného vzorce — specifikace BIFF nazývá záznam, který jej nese, BRAI ([MS-XLS] sekce 2.4.51) — ale AddCopy to nemůže opravit opakovaným použitím normální cesty načítání grafu, protože právě tato cesta chybu vytváří. Když se záznam grafu parsuje z disku v běžném průběhu otevírání souboru, jeho strom vzorce se sestaví překladem surových bajtů přes jakoukoli instanci kalkulátoru, která zrovna parsuje; nakrmíte-li surové bajty BRAI zdrojového grafu přes vlastní běžný nakladač záznamů cílového sešitu místo toho, ixti vložené do těchto bajtů se rozřeší proti tabulce EXTERNSHEET cíle, takže řada tiše ukáže na jakýkoli list, který tam obsazuje tento slot — stejná třída chyby jako kopírování zkompilovaného stromu buňky beze změny, jen hůř postřehnutelná, protože nikdo nečte vzorce řad grafu tak, jako čte vzorce buněk. HotXLS se této pasti vyhýbá vyhrazenou klonovací cestou místo toho: TXLSCustomChart.AssignFrom zkopíruje vlastní nevzorcové bajty hlavičky každého záznamu grafu doslovně, pak přestaví připojený rozsah přes stejný primitiv dekompilace-a-rekompilace použitý pro obyčejné buňky, takže se nový strom sestaví proti tabulce EXTERNSHEET cíle od nuly, místo aby se proti ní dodatečně reinterpretoval
Stejný problém číslování, po jednom indexu fontu
Ne každé číslo místní pro sešit uvnitř grafu nebo buňky s formátovaným textem je vzorec, a index fontu je stejná třída problému v miniatuře. Běhy formátovaného textu, spolu s dalšími dvěma typy záznamů grafu, které nesou popisek nebo font osy, ukládají odkaz na font jako surové celé číslo indexu do vlastní tabulky fontů vlastnícího sešitu, a tento index neznamená nic v tabulce jiného sešitu — mohl by tam stejně snadno ukazovat na úplně jiné písmo, velikost, nebo barvu. HotXLS to řeší podle hodnoty, ne podle čísla: vyhledá skutečné atributy fontu na tomto indexu ve zdrojové tabulce, najde nebo vytvoří odpovídající záznam v cílové tabulce fontů a přepíše uložený index tak, aby ukazoval na tento nový slot. Jedna zvláštnost formátu dělá samotné vyhledávání zdlouhavým — index v souboru přeskakuje slot 4, mezeru v číslování, kterou dokumentuje [MS-XLS] sekce 2.5.339, takže kód musí posunout index o jednu dolů, než porovná fonty, a zpátky o jednu nahoru, než zapíše výsledek
// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
Inc(Ifnt);Co se stane se vzorcem, který už ukazuje mimo sešit?
Vzorec, který sahá do třetího sešitu ještě dřív, než vůbec zavoláte AddCopy, je ten jeden případ, který textový okružní výlet nedokáže unést, protože vlastní dekompilátor vzorce na text v HotXLS záměrně nesyntetizuje závorkový text [Book]Sheet! pro externí odkaz, a kompilátor na druhém konci tuto syntaxi jako vstup také nepřijímá — takže tento jeden případ prochází druhým mechanismem, který se textu vůbec nedotýká. Když oprava migrace jmen popsaná výše pořád ponechá buňku jako řetězec a zdrojový sešit má skutečné jméno souboru, AddCopy přepne strategii: hluboce zkopíruje samotný zkompilovaný strom vzorce místo jeho textu, pak předá kopii vyhrazenému průchodu přepojení, RebindExternRefsInTree, který jej prochází uzel po uzlu. Pro každý odkaz na rozsah, který najde, tento průchod rozřeší záznam EXTERNSHEET zdroje zpátky na dvojici jmen listů a zaregistruje, nebo znovu použije, ekvivalentní záznam ve vlastních tabulkách externích odkazů cíle, a vytvoří zbrusu nový odkaz na externí sešit, pokud cíl na tento zdrojový soubor nikdy dřív neodkazoval
Zde je problém soukromého číslování sešitu ve své nejdoslovnější podobě, protože token externího odkazu sdružuje tři samostatné souřadnice do jednoho pole a každá z nich je soukromá sešitu, který ji zapsal: který externí sešit, slot ve vlastním seznamu externích sešitů cíle přiřazený v jakémkoli pořadí, ve kterém je tento sešit náhodou registroval; který list uvnitř vlastního seznamu listů tohoto externího sešitu, uložený jako index počítaný od 1 v rozsahu specificky externího sešitu, úplně jiná doména číslování než vlastní interní ID listů cíle; a samotný rozsah buněk, obyčejné souřadnice řádku a sloupce, které nepotřebují žádný překlad, protože nikdy nebyly relativní vůči sešitu. Spleťte si kterékoli z prvních dvou a Excel soubor pořád otevře, pořád ukáže vzorec, a vyhodnotí jej proti špatným externím buňkám bez stížnosti. Jeden druh uzlu porazí i toto přepojení na úrovni stromu: odkaz na definované jméno, index do vlastní soukromé tabulky jmen sešitu přesně tak, jako je index listu soukromý vlastnímu EXTERNSHEET, bez dostupné ekvivalentní opravy na úrovni stromu — v okamžiku, kdy průchod přepojení narazí kdekoli ve stromu na odkaz na jméno, opustí celý vzorec, místo aby zapsal částečně správný. I když se přepojení podaří, cílová buňka neukáže čerstvě přepočítané číslo; ukáže hodnotu, kterou zdrojová buňka už držela v okamžiku kopírování, uloženou v cachovaném slotu stejným způsobem, jakým sám Excel cachuje poslední známou hodnotu jakéhokoli externího odkazu, dokud výslovně neobnovíte odkazy, což je správné výchozí chování, protože přepočítávat přes živý odkaz do jiného souboru je přesně ten druh operace, kterou chcete vyvolat jednou, záměrně, ne při každém otevření
Co vás tento návrh stojí
Mechanismus dekompilace-a-rekompilace v AddCopy není zadarmo, a náklad se vyplatí naplánovat dřív, než naskriptujete velkou konsolidační úlohu, ne po. Kopírování listu uvnitř stejného sešitu jde levnou cestou, přímou duplikaci zkompilovaného stromu v paměti, protože každý index uvnitř něj je už platný v sešitu, ve kterém zůstává; kopírování mezi sešity místo toho platí za skutečné parsování na každé vzorcové buňce, dekompilaci na text a pak zkompilování tohoto textu znovu od nuly, a zatímco ten rozdíl se nevyplatí měřit na listu s pár desítkami vzorců, zdrojový sešit s desítkami tisíc vzorcových buněk, kopírovaný jako jeden list mezi tucty v dávkové úloze, by měl očekávat, že rekompilace ovládne dobu běhu spíš než I/O souboru kolem ní. Pořadí kopírování je důležité z druhého důvodu nad rámec rychlosti: vzorec, který odkazuje na list, ke kterému se AddCopy v této dávce ještě nedostal, selže v rekompilaci ze stejného důvodu jako vzorec odkazující na skutečně neexistující list, takže úloha, která zkopíruje list B dřív než vzorec listu A, který na něm závisí, uvidí tento vzorec degradovat přesně tak, jak je popsáno výše, textový řetězec nebo náhrada externího odkazu ukazující rovnou zpátky na zdrojový soubor, ze kterého právě přišel. A protože je každý zdrojový sešit v konsolidační dávce obvykle autorován nezávisle, vyplatí se výslovně otestovat na ten jeden režim selhání, na který by vás žádný jednotlivý zdrojový soubor nikdy nemohl varovat — pět sešitů poboček, které každý sečte čísla partnerské pobočky, se mohou zkombinovat do skutečného kruhového odkazu uvnitř souhrnného sešitu, aniž by kterýkoli jednotlivý zdrojový soubor kdy obsahoval nějaký, cyklu, který existuje teprve tehdy, když každý list přistane na stejném místě a přepočet běží přes kombinovanou množinu
Kopírování listů mezi sešity se dodává jako standardní chování AddCopy v komponentě HotXLS Delphi Excel pro Delphi a C++Builder; stránka produktu nese úplnou referenci API pro listy a sešity, včetně zde popsaného chování grafů, formátovaného textu a externích odkazů