A HotXLS AddCopy metódusa úgy másol egy munkalapot egy Excel-munkafüzetből egy másikba, hogy minden képletet azon a lapon A1-stílusú szöveggé dekompilál, és a szöveget a célmunkafüzeten belül újrafordítja, ahelyett hogy közvetlenül másolná a lefordított képletfát, mert a diagramsorozat-hivatkozások, a formázottszöveg-betűtípus-indexek, és a külső-hivatkozás számozás mind függetlenül vannak hozzárendelve minden munkafüzet-fájlon belül
A hiba pontosan abban a munkafüzetben mutatkozik meg, amit várnál: egy hónapvégi feladat, amely kihúz egy lapot minden fióktelepi jelentésből, és hozzáfűzi egy összesítő fájlhoz. Nyisd meg az eredményt, és egy részösszeg-diagram teljesen más fióktelep számait ábrázolja, egy megjegyzés, amely félkövér és piros volt a forrásban, visszatért sima fekete szövegre, és egy képlet, amely egykor egy adókulcsot húzott ki egy társ keresőmunkafüzetből, most egy fagyott számot mutat, amit senki nem tud megmagyarázni. Semmi nem dob kivételt itt — a fájl megnyílik, a számok hihetőnek tűnnek, és a kár ott ül, amíg valaki észre nem veszi a rossz címmel rendelkező diagramot mellette
Miért nem tudja az AddCopy egyszerűen lemásolni a lefordított képletfát?
Az AddCopy nem tudja változatlanul áthelyezni a lefordított képletfát, mert egy lefordított BIFF-képlet nem önálló szöveg — egy tokensorozat, és e tokenek közül több kis egész szám, amelyek csak azon a munkafüzeten belül oldódnak fel helyesen, amely előállította azokat. Egy 3D-hivatkozás, mint a Sheet2!A1:A10, nem hordozza a szó szerinti Sheet2 nevet, miután lefordul; hordoz egy mezőt, amit a BIFF-specifikáció ixti-nek hív (a HotXLS ugyanezt az értéket tartja saját lefordított fájában az FExternID mezőnév alatt), egy indexet abba a munkafüzet saját, privát EXTERNSHEET táblájába, bárhogy is számozva, ahogyan az a konkrét munkafüzet éppen regisztrálta a lapjait és a külső könyveit. Mozgasd a tokent változatlanul egy olyan munkafüzetbe, amelynek EXTERNSHEET táblája más sorrendben épült fel, és a 3-as index többé nem a Sheet2-t jelenti — bármit jelent, ami ott a 3-as helyet foglalja el, és az Excelnek nincs módja jelezni a hibát, mert a fájlformátum szempontjából a képlet tökéletesen jólformáltnak tűnik. Pontosan ez az a hiba, amelynek elkerülésére a TXLSWorksheets.AddCopy létezik: bármelyik munkafüzet saját laprendszeréből meghívva Delphi vagy C++Builder kódból, lemásol egy munkalapot — cellaértékeket, formátumokat, képleteket, diagramokat, megjegyzéseket, összevonásokat, oldalbeállítást, és még sok mást — egy forrás-munkafüzetből, amely lehet, hogy nem is az, amelyiken meghívod, és hozzáfűzi az eredményt a célhoz egy általad választott néven, vagy az eredeti egyértelműsített másolataként
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;A megoldás: dekompilálás szöveggé, újrafordítás a célban
A HotXLS úgy oldja meg az indexelési problémát, hogy soha nem hagyja, hogy maga a lefordított fa átlépje a munkafüzet-határt. Minden képletcellánál egy munkafüzetek közötti másolásnál az AddCopy dekompilálja a forrásképletet ugyanolyan A1-stílusú szöveggé, amit egy felhasználó az Excel képletsávjában látna, majd átadja ezt a szöveget a célmunkafüzetnek, amely nulláról elemzi vissza egy fává a saját tábláit használva — egy laphoz kötött hivatkozás, mint a Data!D2:D100, ekkor csak egy sztring, és egy sztring ugyanazt jelenti bármely munkafüzetben, így ha a célnak már van egy Data nevű lapja, a hivatkozás helyesen oldódik fel index-fordítás nélkül teljesen, mert soha nem volt nyers index útban, amit lefordítanunk kellene. A HotXLS csak akkor fizet ezért az oda-vissza útért, amikor muszáj: egy lap másolása ugyanazon a munkafüzeten belül egy olcsóbb utat vesz, ahol a lefordított fa egyszerűen duplikálódik memóriában, mivel minden index benne már érvényes ott, ahol marad, és a szöveges kerülő út csak akkor fut, amikor az AddCopy felismeri, hogy a forrás és a cél valóban különböző munkafüzet-példányok. Érdemes precíznek lenni abban is, mi nem ez az átírás. Semmi köze ahhoz a sor- és oszlopeltoláshoz, amely akkor fut, amikor sorokat szúrsz be vagy törölsz egyetlen lapon belül, amit egy kísérőcikk tárgyal részletesen — az a motor A1-szöveget ír át a helyén, hogy nyomon kövesse azokat a cellákat, amelyek néhány sorral fel vagy le mozdultak egy munkafüzeten belül, míg ez akkor fut, amikor egy képlet teljesen elhagyja azt a munkafüzetet, amely lefordította, ahol a mozgott sorok nem a probléma, hanem a munkafüzet-privát számozás
// 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);Mi van, ha a célnak még nincs meg az a lap, vagy az a név?
Az AddCopy újrafordítása csak akkor sikerül, ha a célmunkafüzetnek már megvan mindene, amire a képletszöveg hivatkozik, és a két rés, amely a gyakorlatban megmutatkozik, egy azonos nevű lap, amelyet még nem másoltak át ebben a kötegben, és egy munkafüzet-hatókörű elnevezett tartomány, amely soha nem létezett a célban. A HotXLS nem dob kivételt, amikor az újrafordítás félúton elbukik egy lapmásolás közben — a cella Value hozzárendelése csendben egyszerű sztringként tárolja a képletszöveget ehelyett, egy szándékos, vizsgálható hibamód, nem egy csendes, mivel egy képletcella, amely váratlanul olyan szó szerinti szöveget mutat, mint a =SUM(Q1!B2:B12), egy kiszámított szám helyett, az a jel, hogy valami feljebb a másolásban nem oldódott fel. Feladás előtt az AddCopy megpróbál egy javítást: bejárja az elbukott képlet szintaxisfáját, összegyűjtve minden elnevezett-név azonosítót, amit a képlet érint, és minden munkafüzet-hatókörű névnél, amely létezik a forrásban, de még nem a célban, átmásolja a nevet, és másodszor is újrafordítja ugyanazt a szöveget. A lap-hatókörű nevek kívül esnek azon, amit ez a javítás meg tud oldani, mivel egy név, amely csak a forrás-munkafüzet egy lapjának képleteihez látható, nem rendelkezik ezzel egyenértékű hellyel, ahová migrálhatna, és egy cél, amely már birtokol egy azonos írásmódú nevet, érintetlenül marad ahelyett hogy felülíródna, azon feltevés alapján, hogy egy név, amelyet a hívó szándékosan előre létrehozott, az, amit tiszteletben akar tartani. Egyetlen munkafüzeten belül egy laponátívelő képlet névfeloldása automatikusan a lap-hatókörtől a munkafüzet-hatókörig sétál felfelé, ez az a mechanizmus, amelyet a HotXLS elnevezett tartományokról és laponátívelő képletekről szóló cikke tárgyal; egy valódi munkafüzet-határ átlépése teljesen eltávolítja ezt a biztonsági hálót, és egy nevet szándékosan át kell vinni, különben a rá támaszkodó képlet szöveggé degradálódik
A diagramsorozat-hivatkozásoknak ugyanarra a javításra van szükségük, de más kódútvonalon
Egy HotXLS diagramsorozat, amely egy cellatartományt ábrázol, pontosan ugyanabba a számozási problémába ütközik, mint egy közönséges cellaképlet, mert egy diagram adattartomány-hivatkozása is egy lefordított képlettoken-folyam — a BIFF-specifikáció BRAI-nak hívja azt a rekordot, amely hordozza ([MS-XLS] 2.4.51. szakasz) —, de az AddCopy nem tudja megjavítani a normál diagram-betöltési útvonal újrahasznosításával, mert pontosan az az útvonal az, ami a hibát létrehozza. Amikor egy diagramrekordot lemezről elemeznek egy fájl megnyitásának normál menetében, képletfáját úgy építik fel, hogy a nyers bájtokat átfordítják, bármelyik számológép-példány is végzi éppen az elemzést; táplálj egy forrásdiagram nyers BRAI-bájtjait a célmunkafüzet saját, közönséges rekordbetöltőjén keresztül ehelyett, és a bájtokba ágyazott ixti a cél EXTERNSHEET táblája ellen oldódik fel, így a sorozat csendben arra a lapra mutat, amelyik ott azt a helyet foglalja el — ugyanaz a hibaosztály, mint egy cella lefordított fájának változatlan másolása, csak nehezebb észrevenni, mert senki nem olvas diagramsorozat-képleteket úgy, ahogyan cellaképleteket olvasnak. A HotXLS egy dedikált klónozási útvonallal kerüli el a csapdát ehelyett: a TXLSCustomChart.AssignFrom szó szerint másolja minden diagramrekord saját, nem-képlet fejlécbájtjait, majd újraépíti a csatolt tartományt ugyanazon a dekompilálás-és-újrafordítás alapművelettel, amit közönséges celláknál használ, így az új fa a cél EXTERNSHEET táblája ellen épül fel nulláról, nem pedig utólag újraértelmezve ellene
Ugyanaz a számozási probléma, egy betűtípus-index egyszerre
Nem minden munkafüzet-lokális szám egy diagramon vagy egy formázottszöveg-cellán belül képlet, és egy betűtípus-index ugyanaz a probléma miniatűrben. A formázottszöveg-futamok, két további diagram-rekordtípussal együtt, amelyek egy feliratot vagy tengely-betűtípust hordoznak, betűtípus-hivatkozást tárolnak nyers egész számként a birtokló munkafüzet saját betűtípus-táblájába, és az index semmit nem jelent egy másik munkafüzet táblájában — ugyanolyan könnyen mutathat egy teljesen más betűtípusra, méretre, vagy színre ott. A HotXLS ezt érték szerint oldja meg, nem szám szerint: felkeresi a tényleges betűtípus-attribútumokat annál az indexnél a forrástáblában, megtalál vagy létrehoz egy egyező bejegyzést a cél betűtípus-táblájában, és átírja a tárolt indexet, hogy arra az új helyre mutasson. Egy formátum-furcsaság magát a keresést trükkössé teszi — a fájlbeli index kihagyja a 4-es helyet, egy számozási rés, amit az [MS-XLS] 2.5.339. szakasza dokumentál, így a kódnak eggyel lejjebb kell tolnia az indexet, mielőtt betűtípusokat hasonlít össze, és eggyel feljebb, mielőtt kiírja az eredményt
// 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);Mi történik egy képlettel, amely már a munkafüzeten kívülre mutat?
Egy képlet, amely egy harmadik munkafüzetbe nyúl, mielőtt egyáltalán meghívnád az AddCopy-t, az az egy eset, amit a szöveges oda-vissza út nem tud hordozni, mert a HotXLS saját képlet-szöveg dekompilálója szándékosan nem szintetizál [Book]Sheet! zárójeles szöveget egy külső hivatkozáshoz, és a fordító a másik oldalon sem fogadja el ezt a szintaxist bemenetként — így ez az egy eset egy második mechanizmuson fut át, amely soha nem érinti a szöveget. Amikor a fent leírt névmigrálási javítás még mindig sztringként hagy egy cellát, és a forrás-munkafüzetnek van valódi fájlneve, az AddCopy stratégiát vált: mélymásolja magát a lefordított képletfát a szövege helyett, majd átadja a másolatot egy dedikált újrakötési átfutásnak, a RebindExternRefsInTree-nek, amely csomópontonként bejárja azt. Minden tartományhivatkozásnál, amit talál, ez az átfutás visszaoldja a forrás EXTERNSHEET bejegyzését laponnevek párjává, és regisztrál, vagy újrahasznosít, egy egyenértékű bejegyzést a cél saját külső-hivatkozás tábláiban, egy vadonatúj külső-munkafüzet linket hozva létre, ha a cél soha nem hivatkozott még arra a forrásfájlra
Itt a legszó-szerintibb a munkafüzet-lokális számozási probléma, mert egy külső hivatkozás token három különálló koordinátát csomagol egyetlen mezőbe, és mindegyik privát abban a munkafüzetben, amely megírta: melyik külső munkafüzet, egy hely a cél saját, külső könyveinek listájában, bárhogy is számozva, ahogyan az a munkafüzet éppen regisztrálta őket; melyik lap azon a külső munkafüzet saját laplistáján belül, egy 1-alapú indexként tárolva, kifejezetten a külső könyvre korlátozva, teljesen más számozási tartomány, mint a cél saját belső lap-azonosítói; és maga a cellatartomány, egyszerű sor- és oszlopkoordináták, amelyek nem igényelnek fordítást, mert soha nem voltak munkafüzet-relatívak eleve. Rontsd el az első kettő bármelyikét, és az Excel még mindig megnyitja a fájlt, még mindig mutat egy képletet, és panasz nélkül kiértékeli a rossz külső cellák ellen. Egyfajta csomópont még ezt a fa-szintű újrakötést is legyőzi: egy hivatkozás egy elnevezett névre, egy index a saját munkafüzetének privát névtáblájába, pontosan úgy, ahogyan egy lapindex privát a saját EXTERNSHEET-jéhez, semmilyen egyenértékű fa-szintű javítás nem érhető el — abban a pillanatban, amikor az újrakötési bejárás névhivatkozással találkozik bárhol a fában, feladja a teljes képletet ahelyett hogy egy részlegesen helyeset írna ki. Még ha az újrakötés sikerül is, a célcella nem mutat frissen újraszámított számot; azt az értéket mutatja, amit a forráscella már tartott a másolás idejében, egy gyorsítótárazott helyen tartva, ugyanúgy, ahogyan maga az Excel is gyorsítótárazza bármely külső hivatkozás utolsó ismert értékét, amíg kifejezetten nem frissíted a linkeket, ami a helyes alapértelmezés, mivel az élő linken keresztüli, egy másik fájlba történő újraszámítás pontosan az a fajta művelet, amit egyszer, szándékosan akarsz kiváltani, nem minden megnyitáskor
Mibe kerül ez a tervezés
Az AddCopy dekompilálás-és-újrafordítás gépezete nem ingyenes, és a költséggel érdemes tervezni, mielőtt egy nagy konszolidációs feladatot szkriptelsz, nem utána. Egy lap másolása ugyanazon a munkafüzeten belül az olcsó utat veszi, a lefordított fa egyenes memóriabeli duplikálását, mert minden index benne már érvényes abban a munkafüzetben, ahol marad; egy munkafüzetek közötti másolás minden képletcellánál valódi elemzésért fizet ehelyett, dekompilálás szöveggé, majd annak a szövegnek az újrafordítása nulláról, és bár a különbség nem érdemes megmérni néhány tucat képlettel rendelkező lapon, egy forrás-munkafüzet több tízezer képletcellával, egy kötegelt feladatban tucatnyi közül egy lapként másolva, arra számíthat, hogy az újrafordítás dominálja a futásidőt, nem a körülötte lévő fájl I/O. A másolási sorrend egy második okból is számít a sebességen túl: egy képlet, amely egy olyan lapra hivatkozik, amelyet az AddCopy még nem ért el ebben a kötegben, elbukja az újrafordítását ugyanazon okból, mint egy olyan képlet, amely egy valóban nemlétező lapra hivatkozik, így egy feladat, amely a B lapot másolja a rá támaszkodó A lap képlete előtt, azt fogja látni, hogy az a képlet pontosan úgy degradálódik, ahogyan fent leírtuk, sztring-szöveggé vagy egy külső-link-visszaesésbe, amely egyenesen visszamutat arra a forrásfájlra, ahonnan éppen jött. És mivel egy konszolidációs köteg minden forrás-munkafüzetét általában függetlenül írják, érdemes kifejezetten tesztelni azt az egy hibamódot, amire egyetlen forrásfájl sem tudott volna figyelmeztetni téged — öt fióktelepi munkafüzet, amelyek mindegyike összesíti egy társfiók számait, valódi körkörös hivatkozássá kombinálódhat az összesítő munkafüzeten belül anélkül, hogy bármelyik egyedi forrásfájl valaha is tartalmazott volna egyet, egy ciklus, amely csak akkor létezik, amikor minden lap ugyanoda landolt, és az újraszámítás a kombinált halmazon fut
A munkafüzetek közötti munkalap-másolás az AddCopy szabványos viselkedéseként érkezik a Delphihez és C++Builderhez készült HotXLS Delphi Excel Komponensben; a termékoldal hordozza a teljes munkalap- és munkafüzet-API-referenciát, beleértve az itt leírt diagram-, formázottszöveg-, és külső-hivatkozás-viselkedést