Metoda AddCopy u HotXLS-u kopira radni list iz jedne Excelove radne knjige u drugu tako da svaku formulu na tom listu rastavi u tekst u stilu A1 i ponovno sastavi unutar odredišne radne knjige, umjesto da izravno kopira kompilirano stablo formule, jer se reference nizova grafikona, indeksi fontova obogaćenog teksta i numeriranje vanjskih veza u svakoj datoteci radne knjige dodjeljuju neovisno
Kvar se pojavljuje upravo u radnoj knjizi koju biste očekivali: posao na kraju mjeseca uzima po jedan list iz izvještaja svake podružnice i dodaje ga u sažetu datoteku. Kada otvorite rezultat, grafikon međuzbroja prikazuje brojke potpuno druge podružnice, bilješka koja je u izvoru bila podebljana i crvena ponovno je običan crni tekst, a formula koja je nekoć dohvaćala poreznu stopu iz pomoćne radne knjige sada prikazuje zamrznuti broj koji nitko ne može objasniti. Ovdje se ne javlja nikakva iznimka — datoteka se otvara, brojke izgledaju uvjerljivo, a šteta ostaje sve dok netko ne primijeti grafikon s pogrešnim naslovom pokraj njega
Zašto AddCopy jednostavno ne može kopirati kompilirano stablo formule
AddCopy ne može nepromijenjeno premjestiti kompilirano stablo formule jer kompilirana BIFF formula nije samostojeći tekst — ona je niz tokena, a nekoliko tih tokena čine mali cijeli brojevi koji se ispravno razrješavaju samo unutar radne knjige koja ih je proizvela. 3D referenca poput Sheet2!A1:A10 nakon kompilacije ne nosi doslovni naziv Sheet2; nosi polje koje BIFF specifikacija naziva ixti, a HotXLS istu vrijednost u vlastitom kompiliranom stablu čuva pod nazivom polja FExternID, kao indeks u privatnu tablicu EXTERNSHEET te radne knjige, numeriran onako kako je ta konkretna radna knjiga registrirala svoje listove i vanjske knjige. Premjestite token bez promjene u radnu knjigu čija je tablica EXTERNSHEET izgrađena drugim redoslijedom i indeks 3 više ne znači Sheet2 — znači što god se nalazi u utoru 3 na odredišnom mjestu, a Excel ne može označiti pogrešku jer je, prema formatu datoteke, formula potpuno ispravna. Upravo taj kvar izbjegava TXLSWorksheets.AddCopy: pozvan iz zbirke listova bilo koje vlastite radne knjige u Delphi ili C++Builder kodu, kopira radni list — vrijednosti ćelija, formate, formule, grafikone, komentare, spajanja, postavke stranice i još mnogo toga — iz izvorne radne knjige koja može, ali i ne mora biti ona na kojoj ga pozivate te dodaje rezultat u odredište pod nazivom koji odaberete ili kao razriješenu kopiju izvornog 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;Rješenje: rastavljanje u tekst i ponovno sastavljanje u odredištu
HotXLS rješava problem indeksiranja tako da kompiliranom stablu nikada ne dopušta prelazak granice radne knjige. Za svaku formulsku ćeliju pri kopiranju između radnih knjiga AddCopy rastavlja izvornu formulu u isti tekst u stilu A1 koji bi korisnik vidio u Excelovoj traci formule, a zatim taj tekst predaje odredišnoj radnoj knjizi, koja ga iznova raščlanjuje u stablo koristeći vlastite tablice — referenca kvalificirana nazivom lista poput Data!D2:D100 u tom je trenutku samo niz znakova, a niz znakova znači isto u svakoj radnoj knjizi, pa se referenca ispravno razrješava ako odredište već ima list naziva Data, bez ikakvog prevođenja indeksa jer sirovi indeks nikada nije bio u prijenosu. HotXLS ovaj obilazak plaća samo kada mora: kopiranje lista unutar iste radne knjige ide jeftinijim putem, u kojem se kompilirano stablo jednostavno duplicira u memoriji jer je svaki indeks unutar njega već valjan na mjestu na kojem ostaje, a obilazak kroz tekst pokreće se tek kada AddCopy utvrdi da su izvor i odredište doista različite instance radnih knjiga. Važno je precizno reći i što ova prerada nije. Ona nema veze s pomicanjem redaka i stupaca koje se provodi pri umetanju ili brisanju redaka unutar jednog lista, što detaljno obrađuje prateći članak — taj mehanizam prepisuje A1 tekst na mjestu kako bi pratio ćelije pomaknute nekoliko redaka gore ili dolje unutar jedne radne knjige, dok se ovaj mehanizam pokreće kada formula napusti radnu knjigu koja ju je kompilirala, gdje problem nije pomak redaka nego privatno numeriranje radne knjige
// 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);Što ako odredište još nema taj list ili taj naziv
Ponovna kompilacija koju provodi AddCopy uspijeva samo kada odredišna radna knjiga već ima sve na što se tekst formule poziva, a u praksi se pojavljuju dvije praznine: istoimeni list koji u ovoj skupnoj obradi još nije kopiran i ime definirano na razini radne knjige koje u odredištu nikada nije postojalo. HotXLS ne podiže iznimku kada ponovna kompilacija usred kopiranja lista ne uspije — dodjela Value ćelije tiho sprema tekst formule kao običan niz znakova, što je namjerno i lako provjerljivo ponašanje kvara, a ne tihi kvar, jer je formulna ćelija koja neočekivano prikazuje doslovni tekst poput =SUM(Q1!B2:B12) umjesto izračunatog broja znak da se nešto uzvodno u kopiranju nije razriješilo. Prije odustajanja AddCopy pokušava jedan popravak: prolazi sintaksnim stablom neuspjele formule, prikuplja svaki ID definiranog imena koji formula dodiruje te za svako ime na razini radne knjige koje postoji u izvoru, ali još ne u odredištu, kopira ime i drugi put ponovno kompilira isti tekst. Imena ograničena na list izvan su dosega ovog popravka jer ime vidljivo samo formulama na jednom listu izvorne radne knjige nema odgovarajući utor koji bi se mogao prenijeti, a odredište koje već posjeduje ime s istim pisanjem ostavlja se nepromijenjenim uz pretpostavku da je ime koje je pozivatelj namjerno unaprijed stvorio ono koje želi zadržati. Unutar jedne radne knjige pretraga naziva u formuli koja prelazi između listova automatski ide od opsega lista prema opsegu radne knjige, što obrađuje članak HotXLS-a o definiranim imenima i formulama između listova; prelazak stvarne granice radne knjige potpuno uklanja tu sigurnosnu mrežu, pa ime treba namjerno prenijeti ili će formula koja o njemu ovisi propasti u tekst
Reference nizova grafikona trebaju isti popravak, ali drugim putem koda
Niz grafikona u HotXLS-u koji prikazuje raspon ćelija nailazi na potpuno isti problem numeriranja kao obična formula ćelije jer je referenca raspona podataka grafikona također kompilirani tok tokena formule — BIFF specifikacija zapis koji ga nosi naziva BRAI ([MS-XLS] odjeljak 2.4.51) — ali AddCopy ga ne može popraviti ponovnim korištenjem uobičajenog puta učitavanja grafikona jer upravo taj put stvara kvar. Kada se zapis grafikona raščlani s diska pri uobičajenom otvaranju datoteke, stablo formule gradi se prevođenjem sirovih bajtova kroz instancu kalkulatora koja tada provodi raščlambu; ako sirove BRAI bajtove izvornog grafikona umjesto toga provedete kroz uobičajeni učitavač zapisa odredišne radne knjige, ixti ugrađen u te bajtove razriješit će se prema odredišnoj tablici EXTERNSHEET, pa će niz tiho pokazivati na list koji se ondje nalazi u tom utoru — ista klasa pogreške kao pri nepromijenjenom kopiranju kompiliranog stabla ćelije, samo ju je teže primijetiti jer nitko ne čita formule nizova grafikona kao formule ćelija. HotXLS izbjegava zamku namjenskim putem kloniranja: TXLSCustomChart.AssignFrom kopira vlastite bajtove zaglavlja svakog zapisa grafikona koji nisu formule bez izmjene, a zatim ponovno gradi priloženi raspon istim primitivom rastavljanja i ponovne kompilacije koji se koristi za obične ćelije, pa se novo stablo iz početka gradi prema odredišnoj tablici EXTERNSHEET, umjesto da se naknadno ponovno tumači prema njoj
Isti problem numeriranja, jedan indeks fonta odjednom
Nije svaki broj vezan uz radnu knjigu unutar grafikona ili ćelije s obogaćenim tekstom formula, a indeks fonta isti je problem u malom. Rasponi obogaćenog teksta, zajedno s još dvije vrste zapisa grafikona koji nose font naslova ili osi, spremaju referencu na font kao sirovi cijeli broj u tablicu fontova vlastite radne knjige, a taj indeks u tablici druge radne knjige ne znači ništa — jednako lako može pokazivati na potpuno drugačiji tip slova, veličinu ili boju. HotXLS to razrješava prema vrijednosti, a ne prema broju: u izvornoj tablici potraži stvarne atribute fonta na tom indeksu, pronađe ili izradi odgovarajući unos u odredišnoj tablici fontova i prepiše spremljeni indeks tako da pokazuje na novi utor. Jedna posebnost formata datoteke otežava samo traženje — indeks u datoteci preskače utor 4, što dokumentira [MS-XLS] odjeljak 2.5.339, pa kod mora pomaknuti indeks za jedan prema dolje prije usporedbe fontova i za jedan prema gore prije zapisivanja 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);Što se događa s formulom koja već pokazuje izvan radne knjige
Formula koja prije poziva AddCopy dohvaća podatke iz treće radne knjige jedini je slučaj koji tekstualni obilazak ne može prenijeti jer HotXLS-ov vlastiti dekompilator formule namjerno ne stvara tekst sa zagradama [Book]Sheet! za vanjsku referencu, a kompilator na drugoj strani tu sintaksu također ne prihvaća kao ulaz — zato se ovaj slučaj obrađuje drugim mehanizmom koji uopće ne dodiruje tekst. Kada opisani popravak migracije imena i dalje ostavi ćeliju kao niz znakova, a izvorna radna knjiga ima stvarni naziv datoteke, AddCopy mijenja strategiju: dubinski kopira samo kompilirano stablo formule, a zatim kopiju predaje namjenskom prolazu za ponovno vezivanje, RebindExternRefsInTree, koji ga obilazi čvor po čvor. Za svaku pronađenu referencu raspona taj prolaz izvornu stavku EXTERNSHEET razrješava natrag u par naziva listova te registrira ili ponovno koristi odgovarajući unos u vlastitim tablicama vanjskih referenci odredišta, stvarajući novu vezu s vanjskom radnom knjigom ako odredište prije toga nikada nije upućivalo na tu izvornu datoteku
Ovdje je problem numeriranja vezanog uz radnu knjigu najdoslovniji jer token vanjske reference spaja tri odvojene koordinate u jedno polje, a svaka je privatna radnoj knjizi koja ju je zapisala: koja vanjska radna knjiga, odnosno utor u vlastitom popisu vanjskih knjiga odredišta dodijeljen redoslijedom kojim ih je ta radna knjiga registrirala; koji list unutar popisa listova te vanjske radne knjige, spremljen kao indeks počevši od jedan koji je ograničen baš na tu vanjsku knjigu i pripada potpuno drugom području numeriranja od internih ID-ova listova odredišta; te sam raspon ćelija, obične koordinate redaka i stupaca koje ne treba prevoditi jer nikada nisu bile relativne prema radnoj knjizi. Ako pogriješite u bilo kojem od prva dva podatka, Excel će i dalje otvoriti datoteku, i dalje prikazati formulu i bez prigovora je izračunati prema pogrešnim vanjskim ćelijama. Jedna vrsta čvora nadjačava čak i ovo ponovno vezivanje na razini stabla: referenca na definirano ime, indeks u privatnu tablicu imena vlastite radne knjige potpuno na isti način na koji je indeks lista privatan vlastitoj tablici EXTERNSHEET, bez dostupnog popravka na razini stabla — čim obilazak ponovnog vezivanja bilo gdje u stablu naiđe na referencu imena, odustaje od cijele formule umjesto da zapiše djelomično ispravnu formulu. Čak i kada ponovno vezivanje uspije, odredišna ćelija ne prikazuje svježe ponovno izračunati broj; prikazuje vrijednost koju je izvorna ćelija imala u trenutku kopiranja, spremljenu u predmemoriranom utoru na isti način na koji Excel sprema posljednju poznatu vrijednost svake vanjske reference dok izričito ne osvježite veze, što je razumna zadana vrijednost jer je ponovno izračunavanje preko žive veze prema drugoj datoteci upravo ona vrsta radnje koju želite namjerno pokrenuti jednom, a ne pri svakom otvaranju
Koja je cijena ovog dizajna
Mehanizam rastavljanja i ponovne kompilacije koji koristi AddCopy nije besplatan, pa vrijedi planirati taj trošak prije skriptiranja velikog posla konsolidacije, a ne nakon njega. Kopiranje lista unutar iste radne knjige ide jeftinim putem, izravnim dupliciranjem kompiliranog stabla u memoriji jer je svaki indeks u radnoj knjizi u kojoj ostaje već valjan; kopiranje između radnih knjiga umjesto toga plaća stvarnu raščlambu svake formulne ćelije, rastavlja je u tekst i ponovno kompilira taj tekst od nule, a iako razliku ne vrijedi mjeriti na listu s nekoliko desetaka formula, izvorna radna knjiga s desecima tisuća formulskih ćelija, kopirana kao jedan list među desecima listova u skupnom poslu, može očekivati da će ponovna kompilacija dominirati vremenom izvođenja, a ne ulazno-izlazne operacije nad datotekama. Redoslijed kopiranja važan je i iz drugog razloga osim brzine: formula koja upućuje na list do kojeg AddCopy još nije stigao u ovoj obradi ne uspijeva pri ponovnoj kompilaciji iz istog razloga kao formula koja upućuje na stvarno nepostojeći list, pa će posao koji najprije kopira list B, a zatim list A čija formula ovisi o njemu, vidjeti kako se formula pretvara upravo u gore opisani oblik, tekstualni niz ili rezervni vanjski link koji ponovno pokazuje na izvornu datoteku iz koje je upravo došao. Budući da svaku izvornu radnu knjigu u poslu konsolidacije obično izrađuje neovisna ekipa, vrijedi izričito testirati način kvara na koji vas nijedna pojedinačna izvorna datoteka nije mogla upozoriti — pet radnih knjiga podružnica koje svaka zbraja brojke partnerske podružnice mogu se spojiti u stvarnu kružnu referencu unutar sažete radne knjige, iako nijedna pojedinačna izvorna datoteka nije sadržavala takvu referencu, jer ciklus postoji tek kada svi listovi završe na istom mjestu i pokrene se preračun cijelog skupa
Kopiranje radnih listova između radnih knjiga standardno je ponašanje metode AddCopy u HotXLS Delphi Excel komponenti za Delphi i C++Builder; stranica proizvoda sadrži potpuni API za radne listove i radne knjige, uključujući ponašanje grafikona, obogaćenog teksta i vanjskih referenci opisano ovdje