HotXLS darbalapių kopijavimas tarp darbaknygių ir formulių susiejimas „Delphi“ programoje

HotXLS AddCopy metodas kopijuoja darbalapį iš vienos „Excel“ darbaknygės į kitą, išskaidydamas kiekvieną to lapo formulę į A1 stiliaus tekstą ir iš naujo sukompiliuodamas tekstą paskirties darbaknygėje, užuot tiesiogiai kopijavęs sukompiliuotą formulių medį, nes diagramų sekų nuorodos, raiškiojo teksto šriftų indeksai ir išorinių nuorodų numeracija kiekviename darbaknygės faile priskiriami nepriklausomai

Gedimas pasireiškia būtent toje darbaknygėje, kurioje jo tikėtumėtės: mėnesio pabaigos užduotis paima po vieną lapą iš kiekvieno filialo ataskaitos ir prideda jį prie suvestinės failo. Atidarius rezultatą, tarpinės sumos diagrama vaizduoja visiškai kito filialo skaičius, šaltinyje paryškinta ir raudona pastaba vėl tampa paprastu juodu tekstu, o formulė, anksčiau ėmusi mokesčio tarifą iš papildomos paieškos darbaknygės, dabar rodo sustingusį skaičių, kurio niekas negali paaiškinti. Čia neįvyksta jokia išimtis — failas atsidaro, skaičiai atrodo tikėtini, o žala lieka nepastebėta, kol kas nors pamato diagramą su neteisingu pavadinimu šalia jos

Kodėl AddCopy negali tiesiog nukopijuoti sukompiliuoto formulių medžio?

AddCopy negali nepakeistą perkelti sukompiliuoto formulių medžio, nes sukompiliuota BIFF formulė nėra savarankiškas tekstas — tai žetonų seka, o keli iš tų žetonų yra maži sveikieji skaičiai, teisingai išsprendžiami tik juos sukūrusioje darbaknygėje. 3D nuoroda, pavyzdžiui, Sheet2!A1:A10, sukompiliuota nebesaugo pažodinio pavadinimo Sheet2; ji saugo lauką, kurį BIFF specifikacija vadina ixti („HotXLS“ išlaiko tą pačią reikšmę savo sukompiliuotame medyje lauko pavadinimu FExternID), tai yra indeksą į privačią tos darbaknygės EXTERNSHEET lentelę, sunumeruotą taip, kaip konkreti darbaknygė užregistravo savo lapus ir išorines knygas. Nepakeistą žetoną perkėlus į darbaknygę, kurios EXTERNSHEET lentelė sudaryta kita tvarka, indeksas 3 nebereiškia Sheet2 — jis reiškia lapą, esantį trečioje vietoje kitoje darbaknygėje, o „Excel“ neturi kaip pažymėti klaidos, nes failo formato požiūriu formulė yra visiškai taisyklinga. Būtent šios klaidos išvengti skirta TXLSWorksheets.AddCopy: iškviestas iš bet kurios darbaknygės kolekcijos „Delphi“ arba C++Builder kode jis kopijuoja darbalapį — langelių reikšmes, formatus, formules, diagramas, komentarus, sujungimus, puslapio sąranką ir dar daugiau — iš šaltinio darbaknygės, kuri gali būti arba nebūti ta, kurioje metodą iškviečiate, ir prideda rezultatą prie paskirties darbaknygės pasirinktu pavadinimu arba atskirtu originalo kopijos pavadinimu

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;

Pataisymas: išskaidyti į tekstą ir iš naujo sukompiliuoti paskirties darbaknygėje

HotXLS išsprendžia indeksavimo problemą neleisdamas pačiam sukompiliuotam medžiui kirsti darbaknygės ribos. Kiekvieno formulės langelio kopijavimo tarp skirtingų darbaknygių metu AddCopy išskaido šaltinio formulę į tą patį A1 stiliaus tekstą, kurį vartotojas matytų „Excel“ formulių juostoje, tada perduoda tą tekstą paskirties darbaknygei, kuri iš naujo išanalizuoja jį ir sukuria medį naudodama tik savo lenteles — lapo vardu apibrėžta nuoroda, tokia kaip Data!D2:D100, tuo metu yra tik eilutė, o eilutė reiškia tą patį bet kurioje darbaknygėje, todėl jei paskirties darbaknygėje jau yra lapas pavadinimu Data, nuoroda išsprendžiama teisingai be jokio indekso vertimo, nes nebuvo jokio neapdoroto indekso, kurį reikėtų versti. HotXLS už šį grįžtamąjį kelią moka tik tada, kai reikia: kopijuojant lapą toje pačioje darbaknygėje pasirenkamas pigesnis kelias, kuriuo sukompiliuotas medis tiesiog dubliuojamas atmintyje, nes visi jame esantys indeksai jau galioja ten, kur medis lieka, o tekstinis aplinkkelis vykdomas tik tada, kai AddCopy nustato, kad šaltinis ir paskirtis iš tikrųjų yra skirtingi darbaknygės egzemplioriai. Taip pat svarbu tiksliai pasakyti, kuo šis perrašymas nėra. Jis nesusijęs su eilučių ir stulpelių poslinkiu, kuris vykdomas įterpiant ar trinant eilutes viename lape, o apie tai išsamiai rašoma papildomame straipsnyje — tas variklis vietoje perrašo A1 tekstą, kad sektų langelius, pajudėjusius keliomis eilutėmis aukštyn ar žemyn vienoje darbaknygėje, o šis variklis veikia tada, kai formulė visiškai palieka ją sukompiliavusią darbaknygę, kur problema yra ne pajudėjusios eilutės, o darbaknygės privati numeracija

// 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);

Kas nutinka, jei paskirties darbaknygėje dar nėra to lapo arba pavadinimo?

AddCopy iš naujo sukompiliuoja tik tada, kai paskirties darbaknygė jau turi viską, į ką nurodo formulės tekstas, o praktikoje išryškėja dvi spragos: to paties pavadinimo lapas, kuris šioje kopijavimo grupėje dar nebuvo nukopijuotas, ir darbaknygės lygmens apibrėžtas vardas, kurio paskirtyje niekada nebuvo. „HotXLS“ nekelia išimties, kai per darbalapio kopijavimą perkompiliavimas nepavyksta viduryje — langelio Value priskyrimas tyliai išsaugo formulės tekstą kaip paprastą eilutę, tai sąmoningas ir patikrinamas gedimo režimas, o ne tylus gedimas, nes formulės langelis, netikėtai rodantis pažodinį tekstą, pavyzdžiui, =SUM(Q1!B2:B12), vietoje apskaičiuoto skaičiaus parodo, kad kažkas ankstesniame kopijavimo etape nebuvo išspręsta. Prieš pasiduodamas AddCopy bando vieną taisymą: jis pereina per nepavykusios formulės sintaksės medį, surenka kiekvieną formulės naudojamą apibrėžto vardo ID ir kiekvieną darbaknygės lygmens vardą, esantį šaltinyje, bet dar nesantį paskirtyje, nukopijuoja bei antrą kartą sukompiliuoja tą patį tekstą. Lapo lygmens vardai nepatenka į tai, ką šis taisymas gali išspręsti, nes vardas, matomas tik šaltinio vieno lapo formulėms, neturi lygiaverčio lizdo, į kurį būtų galima perkelti, o paskirtyje jau esantis tokios pačios rašybos vardas paliekamas nepakeistas, darant prielaidą, kad vardas, kurį iš anksto sąmoningai sukūrė iškvietėjas, yra tas, kurį jis nori išsaugoti. Vienoje darbaknygėje tarp lapų esančios formulės vardo paieška automatiškai kyla nuo lapo srities iki darbaknygės srities, o šį mechanizmą aptaria „HotXLS“ apibrėžtų vardų ir formulių tarp lapų straipsnis; tikros darbaknygės ribos kirtimas visiškai pašalina šį apsaugos tinklą, todėl vardą reikia sąmoningai perkelti, antraip nuo jo priklausanti formulė suprastės iki teksto

Diagramų sekų nuorodoms reikia to paties pataisymo, bet kito kodo kelio

„HotXLS“ diagramos sekai, vaizduojančiai langelių intervalą, kyla lygiai tokia pati numeravimo problema kaip ir įprastai langelio formulei, nes diagramos duomenų intervalo nuoroda taip pat yra sukompiliuotas formulės žetonų srautas — BIFF specifikacija įrašą, kuriame jis saugomas, vadina BRAI ([MS-XLS] 2.4.51 skyrius) — tačiau AddCopy negali jos pataisyti pakartotinai naudodamas įprastą diagramos įkėlimo kelią, nes būtent tas kelias ir sukuria klaidą. Kai įprastai atveriant failą diagramos įrašas išanalizuojamas iš disko, jo formulių medis sukuriamas verčiant neapdorotus baitus per tą skaičiuotuvo egzempliorių, kuris tuo metu analizuoja; jei šaltinio diagramos neapdoroti BRAI baitai būtų perduoti per paskirties darbaknygės įprastą įrašų įkėlimo programą, tuose baituose esantis ixti būtų išspręstas pagal paskirties EXTERNSHEET lentelę, todėl seka tyliai rodytų lapą, esantį toje vietoje, ir tai būtų tos pačios klasės klaida kaip nepakeisto langelio sukompiliuoto medžio kopijavimas, tik sunkiau pastebima, nes niekas neskaito diagramų sekų formulių taip, kaip langelių formulių. HotXLS spąstų išvengia naudodamas specialų klonavimo kelią: TXLSCustomChart.AssignFrom kiekvieno diagramos įrašo ne formulės antraštės baitus nukopijuoja pažodžiui, tada prijungtą intervalą iš naujo sukuria naudodamas tą pačią išskaidymo ir kompiliavimo pirmyn bei atgal primityvą kaip įprastiems langeliams, todėl naujas medis nuo pradžių sukuriamas pagal paskirties EXTERNSHEET lentelę, o ne po fakto iš naujo interpretuojamas pagal ją

Ta pati numeravimo problema, po vieną šrifto indeksą

Ne kiekvienas darbaknygės lokalus skaičius diagramoje ar raiškiojo teksto langelyje yra formulė, o šrifto indeksas yra ta pati problema mažesniu mastu. Raiškiojo teksto sekos ir dar du diagramų įrašų tipai, kuriuose saugomas antraštės arba ašies šriftas, laiko šrifto nuorodą kaip neapdorotą sveikąjį indeksą į savininkės darbaknygės šriftų lentelę, o tas indeksas nieko nereiškia kitos darbaknygės lentelėje — kitoje vietoje jis taip pat galėtų rodyti visiškai kitą šriftą, dydį ar spalvą. HotXLS tai išsprendžia pagal reikšmę, o ne pagal skaičių: jis pagal tą indeksą šaltinio lentelėje randa tikruosius šrifto atributus, paskirties šriftų lentelėje randa arba sukuria atitinkantį įrašą ir perrašo saugomą indeksą, kad jis rodytų į naują lizdą. Viena formato ypatybė apsunkina pačią paiešką — faile esantis indeksas praleidžia 4 lizdą, tai numeracijos spraga, aprašyta [MS-XLS] 2.5.339 skyriuje, todėl kodas prieš lygindamas šriftus turi indeksą sumažinti vienetu, o prieš įrašydamas rezultatą padidinti vienetu

// 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);

Kas nutinka formulei, kuri jau rodo už darbaknygės ribų?

Formulė, kuri dar prieš iškviečiant AddCopy pasiekia trečią darbaknygę, yra vienintelis atvejis, kurio tekstinis grįžtamasis kelias negali perkelti, nes paties HotXLS formulės į tekstą išskaidytojas sąmoningai nesukuria [Book]Sheet! skliausteliuose pateikiamo teksto išorinei nuorodai, o kitos pusės kompiliatorius taip pat nepriima šios sintaksės kaip įvesties — todėl šis atvejis pereina per antrą mechanizmą, kuris visiškai neliečia teksto. Kai pirmiau aprašytas vardų perkėlimo taisymas vis dar palieka langelį kaip eilutę, o šaltinio darbaknygė turi tikrą failo vardą, AddCopy pakeičia strategiją: jis giliai nukopijuoja patį sukompiliuotą formulių medį, o ne jo tekstą, tada perduoda kopiją specialiam susiejimo iš naujo etapui RebindExternRefsInTree, kuris pereina per medį po vieną mazgą. Kiekvienai rastai intervalo nuorodai šis etapas išsprendžia šaltinio EXTERNSHEET įrašą į lapų pavadinimų porą ir paskirties išorinių nuorodų lentelėse užregistruoja arba pakartotinai panaudoja lygiavertį įrašą, sukurdama visiškai naują išorinės darbaknygės saitą, jei paskirties darbaknygė dar niekada nebuvo nurodžiusi to šaltinio failo

Čia darbaknygės vietinės numeracijos problema tampa tiesioginė, nes išorinės nuorodos žetonas į vieną lauką sujungia tris atskiras koordinates ir kiekviena jų yra privati darbaknygę įrašančiai darbaknygei: kuri išorinė darbaknygė, tai yra lizdas paskirties darbaknygės pačios išorinių knygų sąraše, priskirtas tokia tvarka, kokia ta darbaknygė registravo knygas; kuris lapas toje išorinėje darbaknygėje, saugomas kaip nuo 1 pradedamas indeksas, priklausantis konkrečiai išorinei knygai ir esantis visiškai kitoje numeracijos srityje nei paskirties vidiniai lapų ID; ir pats langelių intervalas, paprastos eilučių bei stulpelių koordinatės, kurių versti nereikia, nes jos niekada nebuvo santykinės darbaknygės atžvilgiu. Suklydus dėl vieno iš pirmųjų dviejų, „Excel“ vis tiek atidaro failą, vis tiek rodo formulę ir be įspėjimo apskaičiuoja ją naudodama ne tuos išorinius langelius. Net šį medžio lygmens susiejimą nugali vienas mazgo tipas: nuoroda į apibrėžtą vardą, tai indeksas į privačią jo darbaknygės vardų lentelę lygiai taip, kaip lapo indeksas yra privatus jo EXTERNSHEET, ir medžio lygmens taisymo jam nėra — vos tik susiejimo iš naujo eiga bet kurioje medžio vietoje aptinka vardo nuorodą, ji atsisako visos formulės, užuot įrašiusi iš dalies teisingą formulę. Net kai susiejimas iš naujo pavyksta, paskirties langelis nerodo ką tik perskaičiuoto skaičiaus; jis rodo reikšmę, kurią šaltinio langelis turėjo kopijavimo metu, saugomą podėlio lizde taip, kaip pati „Excel“ podėlyje saugo paskutinę žinomą bet kurios išorinės nuorodos reikšmę, kol aiškiai atnaujinate saitus, ir tai yra tinkamas numatytasis elgesys, nes perskaičiavimas per aktyvų saitą į kitą failą yra būtent tokia operacija, kurią norite suaktyvinti vieną kartą sąmoningai, o ne kiekvieno atidarymo metu

Kiek jums kainuoja šis projektas

AddCopy išskaidymo ir pakartotinio kompiliavimo mechanizmas nėra nemokamas, todėl prieš rašant didelę konsolidavimo užduotį verta suplanuoti kainą, o ne apie ją galvoti vėliau. Kopijuojant lapą toje pačioje darbaknygėje pasirenkamas pigus kelias — tiesus atmintyje esančio sukompiliuoto medžio dubliavimas, nes kiekvienas jo indeksas jau galioja darbaknygėje, kurioje medis lieka; kopijuojant tarp darbaknygių už kiekvieną formulės langelį mokama tikro analizavimo kaina: tekstas išskaidomas, o tada vėl sukompiliuojamas iš naujo, ir nors skirtumo neverta matuoti lape su keliomis dešimtimis formulių, šaltinio darbaknygė su dešimtimis tūkstančių formulės langelių, kopijuojama kaip vienas iš daugelio lapų paketinėje užduotyje, turėtų tikėtis, kad pakartotinis kompiliavimas, o ne failo įvestis ir išvestis, dominuos vykdymo laike. Kopijavimo tvarka svarbi ir dėl kitos, ne vien spartos priežasties: formulės, nurodančios į lapą, kurio AddCopy šioje grupėje dar nepasiekė, pakartotinis kompiliavimas nepavyks dėl tos pačios priežasties kaip formulei, nurodančiai į iš tikrųjų neegzistuojantį lapą, todėl užduotis, pirmiau nukopijuojanti lapą B nei lapą A, nuo kurio priklauso jos formulė, pamatys, kaip formulė suprastėja tiksliai taip, kaip aprašyta anksčiau: į tekstinę eilutę arba išorinės nuorodos atsarginį variantą, nukreiptą tiesiai į ką tik naudotą šaltinio failą. Kadangi kiekviena šaltinio darbaknygė konsolidavimo grupėje paprastai kuriama nepriklausomai, verta aiškiai patikrinti tą gedimo režimą, apie kurį nė vienas šaltinio failas negalėtų įspėti: penkios filialų darbaknygės, kurių kiekviena susumuoja kito filialo skaičius, suvestinės darbaknygėje gali sudaryti tikrą ciklinę nuorodą, nors jokiame atskirame šaltinio faile jos nebuvo, nes ciklas atsiranda tik tada, kai visi lapai patenka į tą pačią vietą ir perskaičiavimas vyksta visam bendram rinkiniui

Darbalapių kopijavimas tarp darbaknygių yra standartinė AddCopy elgsena „HotXLS Delphi Excel Component“, skirto „Delphi“ ir C++Builder; produkto puslapyje pateikiama visa darbalapių ir darbaknygių API nuoroda, įskaitant čia aprašytą diagramų, raiškiojo teksto ir išorinių nuorodų elgseną