Metoda AddCopy u HotXLS-u kopira radni list iz jedne Excel radne sveske u drugu tako što svaku formulu na tom listu dekompajlira u tekst u stilu A1 i ponovo kompajlira tekst unutar odredišne radne sveske, umesto da direktno kopira kompajlirano stablo formule, jer se reference serija grafikona, indeksi fontova obogaćenog teksta i numerisanje spoljnih veza nezavisno dodeljuju unutar svake datoteke radne sveske
Problem se pojavljuje upravo u radnoj svesci u kojoj biste ga očekivali: posao na kraju meseca uzima po jedan list iz izveštaja svake poslovnice i dodaje ga u zbirnu datoteku. Kada otvorite rezultat, grafikon međuzbira prikazuje potpuno druge brojeve poslovnice, napomena koja je u izvoru bila podebljana i crvena ponovo je običan crni tekst, a formula koja je ranije preuzimala poresku stopu iz prateće radne sveske za pretragu sada prikazuje zamrznut broj koji niko ne ume da objasni. Ovde se ne javlja nikakav izuzetak — datoteka se otvara, brojevi deluju uverljivo, a problem ostaje dok neko ne primeti grafikon sa pogrešnim naslovom pored njega
Zašto AddCopy jednostavno ne može da kopira kompajlirano stablo formule
AddCopy ne može nepromenjeno da prenese kompajlirano stablo formule, jer kompajlirana BIFF formula nije samostalan tekst — ona je niz tokena, a nekoliko tih tokena su mali celi brojevi koji se ispravno razrešavaju samo unutar radne sveske koja ih je proizvela. 3D referenca kao što je Sheet2!A1:A10 nakon kompajliranja više ne sadrži doslovni naziv Sheet2; ona sadrži polje koje BIFF specifikacija naziva ixti (HotXLS istu vrednost u svom kompajliranom stablu čuva pod nazivom polja FExternID), odnosno indeks privatne tabele EXTERNSHEET te radne sveske, numerisane redosledom kojim je baš ta radna sveska registrovala svoje listove i spoljne knjige. Ako token nepromenjen prebacite u radnu svesku čija je tabela EXTERNSHEET napravljena drugim redosledom, indeks 3 više ne znači Sheet2 — on znači šta god se nalazi u slotu 3 tamo, a Excel nema način da označi grešku jer je sa stanovišta formata datoteke formula potpuno ispravna. Upravo takvu grešku sprečava TXLSWorksheets.AddCopy: pozvana iz kolekcije listova bilo koje radne sveske u Delphi ili C++Builder kodu, ona kopira radni list — vrednosti ćelija, formate, formule, grafikone, komentare, spajanja, podešavanje stranice i još mnogo toga — iz izvorne radne sveske, koja može ali ne mora biti ona nad kojom poziv izvršavate, i dodaje rezultat u odredište pod nazivom koji izaberete ili pod razjašnjenom kopijom originalnog naziva
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;Rešenje: dekompajliranje u tekst i ponovna kompilacija u odredištu
HotXLS rešava problem indeksiranja tako što nikada ne dozvoljava da samo kompajlirano stablo pređe granicu radne sveske. Za svaku ćeliju sa formulom pri kopiranju između radnih svezaka, AddCopy dekompajlira izvornu formulu u isti tekst u stilu A1 koji bi korisnik video u Excelovoj traci za formule, a zatim taj tekst prosleđuje odredišnoj radnoj svesci, koja ga od početka ponovo raščlanjuje u stablo koristeći sopstvene tabele — referenca sa kvalifikovanim listom kao što je Data!D2:D100 u tom trenutku je samo niska, a niska ima isto značenje u svakoj radnoj svesci, pa se referenca ispravno razrešava ako odredište već ima list pod nazivom Data, bez ikakvog prevođenja indeksa jer nijedan sirovi indeks nije bio u toku koji bi trebalo prevesti. HotXLS ovaj prolaz plaća samo kada mora: kopiranje lista unutar iste radne sveske koristi jeftiniji put, pri kojem se kompajlirano stablo jednostavno duplira u memoriji jer je svaki njegov indeks već važeći tamo gde ostaje, dok se obilazak preko teksta izvršava tek kada AddCopy utvrdi da su izvorna i odredišna radna sveska zaista različite instance. Važno je i precizno reći šta ova izmena nije. Ona nema veze sa pomeranjem redova i kolona koje se izvršava kada umetnete ili obrišete redove unutar jednog lista, a koje obrađuje prateći članak — taj mehanizam menja A1 tekst na mestu kako bi pratio ćelije pomerene nekoliko redova gore ili dole unutar jedne radne sveske, dok se ovaj mehanizam pokreće kada formula napusti radnu svesku koja ju je kompajlirala, gde pomereni redovi nisu problem, već privatno numerisanje radne sveske
// 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);Šta ako odredište još nema taj radni list ili to ime
Ponovna kompilacija koju obavlja AddCopy uspeva samo kada odredišna radna sveska već ima sve na šta tekst formule upućuje, a u praksi se pojavljuju dva nedostatka: list sa istim nazivom koji još nije kopiran u ovoj grupnoj operaciji i ime definisano na nivou radne sveske koje u odredištu nikada nije postojalo. HotXLS ne podiže izuzetak kada ponovna kompilacija delimično zakaže tokom kopiranja lista — dodela Value ćelije umesto toga nečujno čuva tekst formule kao običnu nisku, što je nameran i proverljiv način otkaza, jer ćelija formule koja iznenada prikazuje doslovni tekst kao =SUM(Q1!B2:B12) umesto izračunatog broja jasno pokazuje da nešto ranije u kopiranju nije razrešeno. Pre odustajanja, AddCopy pokušava jednu popravku: prolazi kroz sintaksno stablo neuspele formule, prikuplja svaki ID definisanog imena koji formula dodiruje i za svako ime na nivou radne sveske koje postoji u izvoru, ali još ne postoji u odredištu, kopira ime i drugi put ponovo kompajlira isti tekst. Imena vezana za list ostaju izvan opsega ove popravke, jer ime vidljivo samo formulama na jednom listu izvorne radne sveske nema odgovarajući slot koji bi se mogao preneti, a odredište koje već poseduje ime istog pravopisa ostavlja se neizmenjeno umesto da bude prepisano, uz pretpostavku da je ime koje je pozivalac namerno unapred napravio upravo ono koje treba poštovati. Unutar jedne radne sveske, traženje imena za formulu koja pokazuje na drugi list automatski ide od opsega lista ka opsegu radne sveske, što je mehanizam obrađen u HotXLS članku o definisanim imenima i formulama između listova; prelazak stvarne granice radne sveske potpuno uklanja tu zaštitu, pa se ime mora namerno preneti ili će formula koja od njega zavisi preći u tekst
Reference serija grafikona zahtevaju isto rešenje, ali drugačiji tok koda
Serija grafikona u HotXLS-u koja prikazuje opseg ćelija nailazi na potpuno isti problem numerisanja kao obična formula ćelije, jer je referenca opsega podataka grafikona takođe tok tokena kompajlirane formule — BIFF specifikacija zapis koji je nosi naziva BRAI ([MS-XLS] odeljak 2.4.51) — ali AddCopy ne može da ga popravi ponovnim korišćenjem uobičajenog puta za učitavanje grafikona, jer upravo taj put stvara grešku. Kada se zapis grafikona čita sa diska pri uobičajenom otvaranju datoteke, njegovo stablo formule gradi se prevođenjem sirovih bajtova kroz instancu kalkulatora koja trenutno obrađuje podatke; ako sirove BRAI bajtove izvornog grafikona umesto toga provučete kroz uobičajeni učitavač zapisa odredišne radne sveske, ixti ugrađen u te bajtove razrešava se prema tabeli EXTERNSHEET odredišta, pa serija nečujno pokazuje na list koji zauzima taj slot tamo — ista vrsta greške kao pri nepromenjenom kopiranju kompajliranog stabla ćelije, samo teža za uočavanje jer niko ne čita formule serija grafikona kao formule ćelija. HotXLS izbegava zamku namenskim putem kloniranja: TXLSCustomChart.AssignFrom kopira sopstvene bajtove zaglavlja svakog zapisa grafikona koji nisu formula doslovno, a zatim ponovo gradi pripojeni opseg pomoću iste primitive za dekompajliranje i ponovnu kompilaciju koja se koristi za obične ćelije, tako da se novo stablo od početka gradi prema tabeli EXTERNSHEET odredišta, umesto da se naknadno tumači prema njoj
Isti problem numerisanja, jedan po jedan indeks fonta
Nije svaki broj vezan za radnu svesku unutar grafikona ili ćelije sa obogaćenim tekstom formula, a indeks fonta je isti problem u malom. Rasponi obogaćenog teksta, zajedno sa još dva tipa zapisa grafikona koji nose font naslova ili ose, čuvaju referencu na font kao sirovi celobrojni indeks u sopstvenoj tabeli fontova radne sveske, a taj indeks nema značenje u tabeli druge radne sveske — tamo bi jednako lako mogao da pokazuje na potpuno drugačiji tip slova, veličinu ili boju. HotXLS ovo rešava prema vrednosti, a ne prema broju: traži stvarne atribute fonta na tom indeksu u izvornoj tabeli, pronalazi ili pravi odgovarajući unos u tabeli fontova odredišta i menja sačuvani indeks tako da pokazuje na novi slot. Jedna posebnost formata čini samo traženje nezgodnim — indeks u datoteci preskače slot 4, numeričku prazninu dokumentovanu u odeljku 2.5.339 specifikacije [MS-XLS], pa kod mora da smanji indeks za jedan pre poređenja fontova i da ga ponovo poveća za jedan pre upisivanja rezultata
// 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);Šta se dešava sa formulom koja već pokazuje izvan radne sveske
Formula koja pre poziva AddCopy pristupa trećoj radnoj svesci jeste jedini slučaj koji obilazak preko teksta ne može da prenese, jer HotXLS-ov dekompajler formula u tekstu namerno ne pravi oznaku u zagradama [Book]Sheet! za spoljnu referencu, a kompajler na drugoj strani takođe ne prihvata tu sintaksu kao ulaz — zato se u ovom slučaju koristi drugi mehanizam koji uopšte ne dodiruje tekst. Kada popravka prenosa imena opisana iznad i dalje ostavi ćeliju kao nisku, a izvorna radna sveska ima stvarno ime datoteke, AddCopy menja strategiju: duboko kopira samo kompajlirano stablo formule umesto njenog teksta, a zatim kopiju predaje namenskom prolazu ponovnog vezivanja, RebindExternRefsInTree, koji je obilazi čvor po čvor. Za svaku referencu opsega koju pronađe taj prolaz razrešava izvorni unos EXTERNSHEET nazad u par naziva listova i registruje ili ponovo koristi odgovarajući unos u sopstvenim tabelama spoljnih referenci odredišta, stvarajući novu vezu ka spoljnoj radnoj svesci ako odredište nikada ranije nije upućivalo na tu izvornu datoteku
Tu je problem lokalnog numerisanja radne sveske najdoslovniji, jer token spoljne reference spaja tri zasebne koordinate u jedno polje, a svaka od njih je privatna za radnu svesku koja ju je upisala: koja spoljna radna sveska, odnosno slot u sopstvenoj listi spoljnih knjiga odredišta dodeljen redosledom kojim je ta radna sveska registrovala knjige; koji list unutar sopstvene liste listova te spoljne radne sveske, sačuvan kao indeks počev od 1 koji važi baš za tu spoljnu knjigu, što je potpuno drugi domen numerisanja od internih ID-jeva listova odredišta; i sam opseg ćelija, obične koordinate reda i kolone koje ne zahtevaju prevođenje jer nikada nisu bile relativne prema radnoj svesci. Ako pogrešite u bilo kojoj od prve dve vrednosti, Excel i dalje otvara datoteku, i dalje prikazuje formulu i bez prigovora je izračunava nad pogrešnim spoljnim ćelijama. Jedna vrsta čvora nadjačava čak i ovo ponovno vezivanje na nivou stabla: referenca na definisano ime, indeks u privatnoj tabeli imena svoje radne sveske na isti način na koji je indeks lista privatan za sopstveni EXTERNSHEET, bez dostupne popravke na nivou stabla — čim prolaz ponovnog vezivanja naiđe na referencu imena bilo gde u stablu, odustaje od cele formule umesto da ispiše delimično ispravnu verziju. Čak i kada ponovno vezivanje uspe, ćelija odredišta ne prikazuje sveže izračunat broj; prikazuje vrednost koju je izvorna ćelija već imala u trenutku kopiranja, sačuvanu u keširanom slotu na isti način na koji Excel čuva poslednju poznatu vrednost svake spoljne reference dok izričito ne osvežite veze, što je ispravno podrazumevano ponašanje jer je ponovno izračunavanje preko aktivne veze sa drugom datotekom upravo operacija koju želite da pokrenete jednom, namerno, a ne pri svakom otvaranju
Koliko ovaj dizajn košta
Mehanizam dekompajliranja i ponovne kompilacije koji koristi AddCopy nije besplatan, a cenu vredi uračunati pre nego što napišete veliku grupnu konsolidaciju, a ne posle. Kopiranje lista unutar iste radne sveske koristi jeftin put, direktno umnožavanje kompajliranog stabla u memoriji, jer je svaki indeks u njemu već važeći u radnoj svesci u kojoj ostaje; kopiranje između radnih svezaka plaća stvarno raščlanjivanje za svaku ćeliju sa formulom, prvo dekompajliranje u tekst, a zatim ponovnu kompilaciju tog teksta od nule, i dok razliku nije vredno meriti na listu sa nekoliko desetina formula, izvorna radna sveska sa desetinama hiljada ćelija formula, kopirana kao jedan list među desetinama listova u grupnom poslu, treba da očekuje da će ponovna kompilacija dominirati vremenom izvršavanja, a ne ulazno-izlazne operacije nad datotekama. Redosled kopiranja važan je i iz drugog razloga osim brzine: formula koja upućuje na list do kojeg AddCopy još nije stigao u ovoj grupnoj operaciji ne uspeva pri ponovnoj kompilaciji iz istog razloga kao formula koja upućuje na zaista nepostojeći list, pa će posao koji kopira list B pre lista A o kojem njegova formula zavisi videti kako se ta formula pogoršava upravo kao što je gore opisano, prelazeći u tekstualnu vrednost ili rezervnu spoljnu vezu koja pokazuje pravo nazad na izvornu datoteku iz koje je upravo došla. Pošto je svaka izvorna radna sveska u grupnoj konsolidaciji obično nezavisno napravljena, vredi izričito testirati i jedini način otkaza na koji nijedna pojedinačna izvorna datoteka nije mogla da vas upozori — pet radnih svezaka poslovnica koje svaka sabira brojeve druge poslovnice mogu da se spoje u stvarnu kružnu referencu unutar zbirne radne sveske, iako nijedna pojedinačna izvorna datoteka sama nije sadržala takvu referencu, jer ciklus nastaje tek kada svi listovi dospeju na isto mesto i kada se izvrši ponovno izračunavanje nad kombinovanim skupom
Kopiranje radnih listova između radnih svezaka standardno je ponašanje metode AddCopy u HotXLS Delphi Excel Component za Delphi i C++Builder; stranica proizvoda sadrži potpunu referencu API-ja radne sveske i radnog lista, uključujući ponašanje grafikona, obogaćenog teksta i spoljnih referenci opisano ovde