Metoda AddCopy a HotXLS copiază o foaie de calcul dintr-un registru de lucru Excel în altul decompilând fiecare formulă de pe acea foaie în text stil A1 și recompilând textul în interiorul registrului de lucru destinație, în loc să copieze direct arborele de formulă compilat, pentru că referințele de serie de grafic, indexurile de font pentru text îmbogățit și numerotarea legăturilor externe sunt toate atribuite independent în interiorul fiecărui fișier de registru de lucru
Eșecul apare exact în registrul de lucru la care v-ați aștepta: un job de sfârșit de lună care extrage o foaie din raportul fiecărei sucursale și o adaugă la un fișier rezumat. Deschideți rezultatul și un grafic de subtotal reprezintă cifrele unei sucursale complet diferite, o notă care era îngroșată și roșie în sursă a revenit la text simplu negru, iar o formulă care odată prelua o cotă de impozit dintr-un registru de lucru de căutare însoțitor acum arată un număr înghețat pe care nimeni nu îl poate explica. Nimic nu ridică o excepție aici — fișierul se deschide, numerele arată plauzibil, iar prejudiciul stă acolo până când cineva observă un grafic cu titlul greșit alături
De ce nu poate AddCopy pur și simplu să copieze arborele de formulă compilat?
AddCopy nu poate muta arborele de formulă compilat neschimbat, pentru că o formulă BIFF compilată nu este text de sine stătător — este o secvență de token-uri, iar mai multe din acele token-uri sunt numere întregi mici care se rezolvă corect doar în interiorul registrului de lucru care le-a produs. O referință 3D precum Sheet2!A1:A10 nu poartă numele literal Sheet2 odată compilată; poartă un câmp pe care specificația BIFF îl numește ixti (HotXLS păstrează aceeași valoare în propriul arbore compilat sub numele de câmp FExternID), un index în tabelul privat EXTERNSHEET al acelui registru de lucru, numerotat oricum s-a întâmplat ca acel registru de lucru particular să își înregistreze foile și cărțile externe. Mutați token-ul neschimbat într-un registru de lucru al cărui tabel EXTERNSHEET a fost construit într-o ordine diferită, iar indexul 3 nu mai înseamnă Sheet2 — înseamnă orice foaie se întâmplă să ocupe slotul 3 acolo, iar Excel nu are nicio modalitate de a semnala greșeala, pentru că din punctul de vedere al formatului de fișier formula este perfect bine formată. Aceasta este exact greșeala pe care TXLSWorksheets.AddCopy există pentru a o evita: apelat din propria colecție de foi a oricărui registru de lucru în cod Delphi sau C++Builder, copiază o foaie de calcul — valori de celule, formate, formule, grafice, comentarii, îmbinări, configurare de pagină, și mai multe — dintr-un registru de lucru sursă care poate fi sau nu cel pe care îl apelați, și adaugă rezultatul la destinație sub un nume ales de dvs. sau o copie dezambiguizată a originalului
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;Soluția: decompilare la text, recompilare în destinație
HotXLS rezolvă problema de indexare nelăsând niciodată arborele compilat însuși să treacă granița registrului de lucru. Pentru fiecare celulă cu formulă la o copiere între registre de lucru, AddCopy decompilează formula sursă în același text stil A1 pe care un utilizator l-ar vedea în bara de formule a Excel, apoi predă acel text registrului de lucru destinație, care îl analizează înapoi într-un arbore folosind propriile sale tabele de la zero — o referință calificată cu foaie precum Data!D2:D100 este doar un șir în acel moment, iar un șir înseamnă același lucru în orice registru de lucru, așa că dacă destinația are deja o foaie numită Data, referința se rezolvă corect fără nicio traducere de index deloc, pentru că nu a existat niciodată un index brut în zbor de tradus. HotXLS plătește pentru acest drum dus-întors doar când trebuie: copierea unei foi în interiorul aceluiași registru de lucru ia o cale mai ieftină unde arborele compilat este pur și simplu duplicat în memorie, întrucât fiecare index din interiorul lui este deja valid unde rămâne, iar ocolul prin text rulează doar odată ce AddCopy detectează că sursa și destinația sunt cu adevărat instanțe de registru de lucru diferite. Merită să fiți precis despre ce nu este această rescriere, de asemenea. Nu are nimic de-a face cu deplasarea de rânduri și coloane care rulează atunci când inserați sau ștergeți rânduri în interiorul unei singure foi, ceea ce un articol complementar acoperă în detaliu — acel motor rescrie textul A1 pe loc pentru a urmări celulele care s-au mutat câteva rânduri în sus sau jos în interiorul unui singur registru de lucru, în timp ce acesta rulează atunci când o formulă părăsește complet registrul de lucru care a compilat-o, unde rândurile mutate nu sunt problema, iar numerotarea privată a registrului de lucru este
// 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);Ce se întâmplă dacă destinația nu are încă acea foaie, sau acel nume?
Recompilarea lui AddCopy reușește doar atunci când registrul de lucru destinație are deja tot ce referă textul formulei, iar cele două goluri care apar în practică sunt o foaie cu același nume care nu a fost încă copiată în acest lot, și un nume definit cu domeniu de registru de lucru care nu a existat niciodată în destinație deloc. HotXLS nu ridică o excepție atunci când recompilarea eșuează la mijlocul unei copieri de foi — atribuirea Value a celulei stochează silențios textul formulei ca un simplu șir în schimb, un mod de eșec deliberat, inspectabil, în loc de unul silențios, întrucât o celulă cu formulă care arată neașteptat text literal precum =SUM(Q1!B2:B12) în loc de un număr calculat este semnul că ceva în amonte în copiere nu s-a rezolvat. Înainte de a renunța, AddCopy încearcă o reparație: parcurge arborele de sintaxă al formulei eșuate colectând fiecare ID de nume definit pe care formula îl atinge, iar pentru fiecare nume cu domeniu de registru de lucru care există în sursă dar nu încă în destinație, copiază numele peste și recompilează același text a doua oară. Numele cu domeniu de foaie stau în afara a ce poate repara această reparație, întrucât un nume vizibil doar formulelor de pe o foaie a registrului de lucru sursă nu are niciun slot echivalent în care să migreze, iar o destinație care deja deține un nume cu aceeași ortografie este lăsată neatinsă, în loc de suprascrisă, pe presupunerea că un nume pe care apelantul l-a pre-creat deliberat este cel pe care dorește să fie respectat. În interiorul unui singur registru de lucru, căutarea de nume a unei formule cross-sheet parcurge de la domeniul de foaie la domeniul de registru de lucru automat, ceea ce este mecanismul pe care articolul HotXLS despre nume definite și formule cross-sheet îl acoperă; trecerea unei granițe reale de registru de lucru elimină complet acea plasă de siguranță, iar un nume trebuie deliberat purtat peste, altfel formula care depinde de el se degradează la text
Referințele de serie de grafic au nevoie de aceeași soluție, dar o cale de cod diferită
O serie de grafic HotXLS care reprezintă un interval de celule întâlnește exact aceeași problemă de numerotare ca o formulă obișnuită de celulă, pentru că o referință de interval de date a unui grafic este de asemenea un flux de token-uri de formulă compilat — specificația BIFF numește înregistrarea care o poartă BRAI ([MS-XLS] secțiunea 2.4.51) — dar AddCopy nu o poate repara refolosind calea normală de încărcare a graficului, pentru că acea cale este exact ce creează bug-ul. Când o înregistrare de grafic este analizată de pe disc în cursul obișnuit al deschiderii unui fișier, arborele său de formulă este construit prin traducerea octeților bruți prin orice instanță de calculator face analiza; alimentați octeții BRAI bruți ai unui grafic sursă prin propriul încărcător obișnuit de înregistrări al registrului de lucru destinație în schimb, iar ixti-ul încorporat în acei octeți este rezolvat față de tabelul EXTERNSHEET al destinației, așa că seria indică silențios spre orice ocupă acel slot acolo — aceeași clasă de greșeală ca a copia arborele compilat al unei celule neschimbat, doar mai greu de observat pentru că nimeni nu citește formulele seriei de grafic așa cum citesc formulele de celule. HotXLS evită capcana cu o cale de clonare dedicată în schimb: TXLSCustomChart.AssignFrom copiază propriii octeți de antet non-formulă ai fiecărei înregistrări de grafic textual, apoi reconstruiește intervalul atașat prin același primitiv de decompilare-și-recompilare folosit pentru celulele obișnuite, astfel încât noul arbore este construit față de tabelul EXTERNSHEET al destinației de la zero, în loc de reinterpretat față de el ulterior
Aceeași problemă de numerotare, un index de font odată
Nu fiecare număr local al registrului de lucru din interiorul unui grafic sau unei celule cu text îmbogățit este o formulă, iar un index de font este aceeași clasă de problemă în miniatură. Secvențele de text îmbogățit, împreună cu încă două tipuri de înregistrări de grafic care poartă un font de titlu sau axă, stochează o referință de font ca un index întreg brut în propriul tabel de fonturi al registrului de lucru proprietar, iar acel index nu înseamnă nimic în tabelul unui alt registru de lucru — ar putea la fel de ușor să indice spre un tip de literă, dimensiune, sau culoare complet diferite acolo. HotXLS rezolvă asta prin valoare, nu prin număr: caută atributele reale de font la acel index în tabelul sursă, găsește sau creează o intrare care se potrivește în tabelul de fonturi al destinației, și rescrie indexul stocat pentru a indica spre acel nou slot. O particularitate de format face căutarea însăși dificilă — indexul din fișier sare peste slotul 4, un gol de numerotare pe care [MS-XLS] secțiunea 2.5.339 îl documentează, așa că codul trebuie să deplaseze indexul cu unu în jos înainte de a compara fonturile și înapoi cu unu în sus înainte de a scrie rezultatul
// 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);Ce se întâmplă cu o formulă care deja indică în afara registrului de lucru?
O formulă care ajunge într-un al treilea registru de lucru înainte să apelați vreodată AddCopy este singurul caz pe care drumul dus-întors prin text nu îl poate transporta, pentru că propriul decompilator formulă-la-text al HotXLS deliberat nu sintetizează text de paranteze [Book]Sheet! pentru o referință externă, iar compilatorul de la celălalt capăt nici nu acceptă acea sintaxă ca intrare — așa că acest singur caz rulează printr-un al doilea mecanism care nu atinge niciodată textul deloc. Când reparația de migrare de nume descrisă mai sus tot lasă o celulă ca șir, iar registrul de lucru sursă are un nume de fișier real, AddCopy schimbă strategia: copiază profund arborele de formulă compilat însuși, nu textul său, apoi predă copia unei treceri dedicate de rebindare, RebindExternRefsInTree, care îl parcurge nod cu nod. Pentru fiecare referință de interval pe care o găsește, acea trecere rezolvă intrarea EXTERNSHEET a sursei înapoi într-o pereche de nume de foi, și înregistrează, sau refolosește, o intrare echivalentă în propriile tabele de referință externă ale destinației, creând o legătură de registru de lucru extern complet nouă dacă destinația nu a referit niciodată acel fișier sursă înainte
Aici problema de numerotare locală a registrului de lucru este la cea mai literală, pentru că un token de referință externă înmănunchează trei coordonate separate într-un singur câmp, și fiecare dintre ele este privată registrului de lucru care le-a scris: care registru de lucru extern, un slot în propria listă a destinației de cărți externe atribuit oricum s-a întâmplat ca acel registru de lucru să le înregistreze; care foaie în interiorul propriei liste de foi a acelui registru de lucru extern, stocată ca un index bazat pe 1 cu domeniu specific cărții externe, un domeniu de numerotare complet diferit de propriile ID-uri interne de foi ale destinației; și intervalul de celule însuși, simple coordonate de rând și coloană care nu au nevoie de nicio traducere pentru că nu au fost niciodată relative la registrul de lucru în primul rând. Greșiți oricare din primele două, iar Excel tot deschide fișierul, tot arată o formulă, și o evaluează față de celulele externe greșite fără plângere. Un tip de nod învinge chiar și această rebindare la nivel de arbore: o referință la un nume definit, un index în propriul tabel privat de nume al registrului de lucru exact cum un index de foaie este privat propriului EXTERNSHEET, fără nicio reparație echivalentă la nivel de arbore disponibilă — în momentul în care parcurgerea de rebindare întâlnește o referință de nume oriunde în arbore, abandonează întreaga formulă, în loc să scrie una parțial corectă. Chiar și atunci când rebindarea reușește, celula destinație nu arată un număr proaspăt recalculat; arată valoarea pe care celula sursă o avea deja la momentul copierii, păstrată într-un slot cu cache la fel cum Excel însuși pune în cache ultima valoare cunoscută a oricărei referințe externe până când reîmprospătați explicit legăturile, ceea ce este implicitul corect, întrucât recalcularea printr-o legătură vie într-un alt fișier este exact genul de operație pe care doriți să o declanșați o dată, deliberat, nu la fiecare deschidere
Ce vă costă acest design
Mecanismul de decompilare-și-recompilare al AddCopy nu este gratuit, iar costul merită planificat înainte, nu după, ce scriptați un job mare de consolidare. Copierea unei foi în interiorul aceluiași registru de lucru ia calea ieftină, o dublare directă în memorie a arborelui compilat, pentru că fiecare index din interiorul lui este deja valid în registrul de lucru unde rămâne; o copiere între registre de lucru plătește pentru o analiză reală pe fiecare celulă cu formulă în schimb, decompilare la text și apoi compilarea acelui text din nou de la zero, iar în timp ce diferența nu merită măsurată pe o foaie cu câteva zeci de formule, un registru de lucru sursă cu zeci de mii de celule cu formulă, copiat ca o foaie printre zeci într-un job de lot, ar trebui să se aștepte ca recompilarea să domine timpul de execuție, nu I/O-ul de fișier din jurul lui. Ordinea de copiere contează dintr-un al doilea motiv dincolo de viteză: o formulă care referă o foaie pe care AddCopy nu a atins-o încă în acest lot își eșuează recompilarea din același motiv pentru care o formulă care referă o foaie cu adevărat inexistentă o face, așa că un job care copiază foaia B înaintea formulei foii A care depinde de ea va vedea acea formulă degradându-se exact cum s-a descris mai sus, text șir sau o rezervă de legătură externă care indică direct înapoi spre fișierul sursă din care tocmai a venit. Și pentru că fiecare registru de lucru sursă dintr-un lot de consolidare este de obicei scris independent, merită testat explicit singurul mod de eșec pe care niciun fișier sursă unic nu v-ar fi putut avertiza vreodată — cinci registre de lucru de sucursală care fiecare totalizează cifrele unei sucursale pereche se pot combina într-o referință circulară reală în interiorul registrului de lucru rezumat fără ca niciun fișier sursă individual să conțină vreodată una, un ciclu care există doar odată ce fiecare foaie a ajuns în același loc, iar recalcularea rulează peste setul combinat
Copierea de foi de calcul între registre de lucru este livrată ca un comportament standard al AddCopy în componenta Excel HotXLS pentru Delphi pentru Delphi și C++Builder; pagina de produs conține referința completă a API-ului de foi de calcul și registru de lucru, inclusiv comportamentul de grafic, text îmbogățit și referință externă descris aici