Metode AddCopy milik HotXLS menyalin sebuah worksheet dari satu workbook Excel ke workbook lain dengan men-dekompilasi setiap formula pada sheet itu menjadi teks gaya-A1 dan mengompilasi ulang teks itu di dalam workbook tujuan, alih-alih menyalin langsung pohon formula terkompilasi, karena referensi seri chart, indeks font rich text, dan penomoran external-link semuanya ditetapkan secara independen di dalam setiap file workbook
Kegagalannya muncul persis pada workbook yang Anda duga: sebuah job akhir-bulan yang menarik satu sheet dari setiap laporan kantor cabang dan menambahkannya ke sebuah file ringkasan. Buka hasilnya dan sebuah chart subtotal memplot angka cabang yang sama sekali berbeda, sebuah catatan yang dulunya tebal dan merah di sumber kembali menjadi teks hitam polos, dan sebuah formula yang dulunya menarik sebuah tarif pajak dari sebuah workbook lookup pendamping sekarang menampilkan sebuah angka beku yang tidak bisa dijelaskan siapa pun. Tidak ada yang memunculkan exception di sini — file itu terbuka, angkanya terlihat masuk akal, dan kerusakannya duduk di sana sampai seseorang memperhatikan sebuah chart dengan judul yang salah duduk di sebelahnya
Mengapa AddCopy tidak bisa sekadar menyalin pohon formula terkompilasi?
AddCopy tidak bisa memindahkan pohon formula terkompilasi tanpa berubah, karena sebuah formula BIFF terkompilasi bukan teks yang berdiri sendiri — ia adalah sebuah urutan token, dan beberapa token itu adalah integer kecil yang hanya terselesaikan dengan benar di dalam workbook yang menghasilkannya. Sebuah referensi 3D seperti Sheet2!A1:A10 tidak membawa nama literal Sheet2 begitu terkompilasi; ia membawa sebuah field yang disebut spesifikasi BIFF sebagai ixti (HotXLS mempertahankan nilai yang sama dalam pohon terkompilasinya sendiri di bawah nama field FExternID), sebuah indeks ke dalam tabel EXTERNSHEET privat workbook itu, dinomori sesuai bagaimana workbook tertentu itu kebetulan mendaftarkan sheet dan buku eksternalnya. Pindahkan token tanpa berubah ke dalam sebuah workbook yang tabel EXTERNSHEET-nya dibangun dalam urutan berbeda dan indeks 3 tidak lagi berarti Sheet2 — ia berarti sheet apa pun yang kebetulan menempati slot 3 di sana, dan Excel tidak punya cara menandai kesalahan itu, karena sejauh yang dipedulikan format file, formula itu sempurna well-formed. Inilah persis kegagalan yang ingin dihindari TXLSWorksheets.AddCopy: dipanggil dari koleksi sheet salah satu workbook sendiri dalam kode Delphi atau C++Builder, metode ini menyalin sebuah worksheet — nilai sel, format, formula, chart, komentar, gabungan, page setup, dan lainnya — dari sebuah workbook sumber yang mungkin atau mungkin bukan yang sedang Anda panggil, dan menambahkan hasilnya ke tujuan dengan nama yang Anda pilih atau sebuah salinan yang dibedakan dari aslinya
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;Perbaikannya: dekompilasi ke teks, kompilasi ulang di tujuan
HotXLS menyelesaikan masalah indexing dengan tidak pernah membiarkan pohon terkompilasi itu sendiri menyeberangi batas workbook. Untuk setiap sel formula pada sebuah copy lintas-workbook, AddCopy mendekompilasi formula sumber menjadi teks gaya-A1 yang sama seperti yang akan dilihat pengguna di formula bar Excel, lalu menyerahkan teks itu ke workbook tujuan, yang mem-parse-nya kembali menjadi sebuah pohon menggunakan tabelnya sendiri dari nol — sebuah referensi bersyarat-sheet seperti Data!D2:D100 hanyalah sebuah string pada titik itu, dan sebuah string berarti sama di workbook mana pun, sehingga jika tujuan sudah memiliki sebuah sheet bernama Data, referensi itu terselesaikan dengan benar tanpa terjemahan indeks sama sekali, karena tidak pernah ada indeks mentah yang perlu diterjemahkan. HotXLS hanya membayar untuk round trip ini ketika memang harus: menyalin sebuah sheet di dalam workbook yang sama mengambil jalur yang lebih murah di mana pohon terkompilasi sekadar diduplikasi di memori, karena setiap indeks di dalamnya sudah valid di tempatnya tinggal, dan jalan memutar teks itu hanya berjalan begitu AddCopy mendeteksi bahwa sumber dan tujuan benar-benar instance workbook yang berbeda. Layak dijelaskan secara presisi juga apa yang bukan penulisan-ulang ini. Ini tidak ada hubungannya dengan pergeseran baris dan kolom yang berjalan ketika Anda menyisipkan atau menghapus baris di dalam satu sheet, yang dibahas mendalam sebuah artikel pendamping — mesin itu menulis ulang teks A1 di tempat untuk melacak sel yang berpindah beberapa baris naik atau turun di dalam satu workbook, sementara yang ini berjalan ketika sebuah formula meninggalkan workbook yang mengompilasinya sepenuhnya, di mana baris yang berpindah bukan masalahnya dan penomoran privat-workbook-lah yang menjadi masalah
// 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);Bagaimana jika tujuan belum memiliki sheet itu, atau nama itu?
Kompilasi ulang AddCopy hanya berhasil ketika workbook tujuan sudah memiliki segala sesuatu yang dirujuk teks formula tersebut, dan dua celah yang muncul dalam praktik adalah sebuah sheet senama yang belum disalin dalam batch ini, dan sebuah defined name berlingkup-workbook yang belum pernah ada di tujuan sama sekali. HotXLS tidak memunculkan exception ketika kompilasi ulang gagal di tengah penyalinan sheet — assignment Value sel itu diam-diam menyimpan teks formula sebagai string polos sebagai gantinya, sebuah mode kegagalan yang disengaja dan bisa diperiksa alih-alih diam-diam, karena sebuah sel formula yang tak terduga menampilkan teks literal seperti =SUM(Q1!B2:B12) alih-alih sebuah angka terhitung adalah petunjuk bahwa ada sesuatu di hulu penyalinan yang tidak terselesaikan. Sebelum menyerah, AddCopy mencoba satu perbaikan: ia menelusuri pohon sintaks formula yang gagal itu, mengumpulkan setiap ID defined-name yang disentuh formula tersebut, dan untuk setiap nama berlingkup-workbook yang ada di sumber tetapi belum di tujuan, ia menyalin nama itu ke seberang dan mengompilasi ulang teks yang sama untuk kedua kalinya. Nama berlingkup-sheet berada di luar apa yang bisa diperbaiki perbaikan ini, karena sebuah nama yang hanya terlihat oleh formula pada satu sheet workbook sumber tidak memiliki slot setara untuk dimigrasikan, dan sebuah tujuan yang sudah memiliki sebuah nama dengan ejaan yang sama dibiarkan tidak tersentuh alih-alih ditimpa, dengan asumsi bahwa sebuah nama yang sengaja dibuat sebelumnya pemanggil adalah yang ingin mereka hormati. Di dalam satu workbook, lookup nama sebuah formula lintas-sheet menelusuri dari lingkup sheet naik ke lingkup workbook secara otomatis, yang merupakan mekanisme yang dibahas artikel defined name dan formula lintas-sheet milik HotXLS; menyeberangi sebuah batas workbook sesungguhnya sepenuhnya menghilangkan jaring pengaman itu, dan sebuah nama harus sengaja dibawa menyeberang atau formula yang bergantung padanya menurun menjadi teks
Referensi seri chart membutuhkan perbaikan yang sama, tetapi jalur kode berbeda
Sebuah seri chart HotXLS yang memplot sebuah rentang sel menabrak persis masalah penomoran yang sama seperti sebuah formula sel biasa, karena sebuah referensi rentang-data chart juga sebuah stream token formula terkompilasi — spesifikasi BIFF menyebut record yang membawanya BRAI ([MS-XLS] section 2.4.51) — tetapi AddCopy tidak bisa memperbaikinya dengan menggunakan kembali jalur pemuatan-chart normal, karena jalur itulah persis yang menciptakan bug tersebut. Ketika sebuah record chart di-parse dari disk dalam alur biasa membuka sebuah file, pohon formulanya dibangun dengan menerjemahkan byte mentah lewat kalkulator apa pun yang sedang mem-parse; masukkan byte BRAI mentah dari sebuah chart sumber lewat record loader biasa workbook tujuan sendiri sebagai gantinya, dan ixti yang tersemat dalam byte itu terselesaikan terhadap tabel EXTERNSHEET tujuan, sehingga seri itu diam-diam menunjuk ke sheet apa pun yang menempati slot itu di sana — kelas kesalahan yang sama seperti menyalin pohon terkompilasi sebuah sel tanpa berubah, hanya lebih sulit diperhatikan karena tidak ada yang membaca formula seri chart dengan cara mereka membaca formula sel. HotXLS menghindari jebakan itu dengan sebuah jalur clone khusus sebagai gantinya: TXLSCustomChart.AssignFrom menyalin byte header non-formula setiap record chart persis apa adanya, lalu membangun ulang rentang yang terpasang lewat primitif dekompilasi-dan-kompilasi-ulang yang sama yang digunakan untuk sel biasa, sehingga pohon barunya dibangun terhadap tabel EXTERNSHEET tujuan dari nol alih-alih ditafsirkan ulang terhadapnya setelah fakta
Masalah penomoran yang sama, satu indeks font pada satu waktu
Tidak setiap angka lokal-workbook di dalam sebuah chart atau sebuah sel rich-text adalah sebuah formula, dan sebuah indeks font adalah kelas masalah yang sama dalam bentuk mini. Run rich text, beserta dua tipe record chart lagi yang membawa sebuah caption atau font axis, menyimpan sebuah referensi font sebagai indeks integer mentah ke dalam tabel font workbook pemiliknya sendiri, dan indeks itu tidak berarti apa-apa di tabel workbook lain — ia bisa saja sama mudahnya menunjuk ke sebuah typeface, ukuran, atau warna yang sama sekali berbeda di sana. HotXLS menyelesaikan ini berdasarkan nilai alih-alih berdasarkan angka: ia mencari atribut font sesungguhnya pada indeks itu di tabel sumber, menemukan atau membuat sebuah entri yang cocok di tabel font tujuan, dan menulis ulang indeks tersimpan untuk menunjuk ke slot baru itu. Satu keunikan format membuat lookup itu sendiri merepotkan — indeks di-file melewati slot 4, sebuah celah penomoran yang didokumentasikan [MS-XLS] section 2.5.339, sehingga kode harus menggeser indeks turun satu sebelum membandingkan font dan naik satu lagi sebelum menulis hasilnya
// 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);Apa yang terjadi pada sebuah formula yang sudah menunjuk ke luar workbook?
Sebuah formula yang menjangkau ke sebuah workbook ketiga sebelum Anda pernah memanggil AddCopy adalah satu-satunya kasus yang tidak bisa dibawa round trip teks, karena dekompiler formula-ke-teks milik HotXLS sendiri sengaja tidak mensintesis teks tanda kurung [Book]Sheet! untuk sebuah referensi eksternal, dan compiler di ujung lainnya juga tidak menerima sintaks itu sebagai input — sehingga satu kasus ini berjalan lewat mekanisme kedua yang sama sekali tidak menyentuh teks. Ketika perbaikan migrasi-nama yang dijelaskan di atas masih meninggalkan sebuah sel sebagai string, dan workbook sumber memiliki sebuah nama file sungguhan, AddCopy beralih strategi: ia melakukan deep-copy pohon formula terkompilasi itu sendiri alih-alih teksnya, lalu menyerahkan salinan itu ke sebuah langkah rebinding khusus, RebindExternRefsInTree, yang menelusurinya node demi node. Untuk setiap referensi rentang yang ditemukannya, langkah itu menyelesaikan entri EXTERNSHEET sumber kembali menjadi sepasang nama sheet, dan mendaftarkan, atau menggunakan kembali, sebuah entri setara dalam tabel referensi-eksternal tujuan sendiri, membuat sebuah link workbook-eksternal yang benar-benar baru jika tujuan belum pernah merujuk file sumber itu sebelumnya
Di sinilah masalah penomoran lokal-workbook paling literal, karena sebuah token referensi eksternal membundel tiga koordinat terpisah ke dalam satu field dan setiap satunya privat bagi workbook yang menulisnya: workbook eksternal mana, sebuah slot dalam daftar buku eksternal tujuan sendiri yang ditetapkan dalam urutan apa pun yang kebetulan didaftarkan workbook itu; sheet mana di dalam daftar sheet workbook eksternal itu sendiri, disimpan sebagai indeks berbasis-1 yang berlingkup khusus buku eksternal, sebuah domain penomoran yang sama sekali berbeda dari ID sheet internal tujuan sendiri; dan rentang sel itu sendiri, koordinat baris dan kolom polos yang tidak membutuhkan terjemahan karena tidak pernah relatif-workbook sejak awal. Salahkan salah satu dari dua yang pertama dan Excel tetap membuka file itu, tetap menampilkan sebuah formula, dan mengevaluasinya terhadap sel eksternal yang salah tanpa keluhan. Satu jenis node mengalahkan bahkan rebind tingkat-pohon ini: sebuah referensi ke sebuah defined name, sebuah indeks ke dalam tabel nama privat workbook-nya sendiri persis seperti sebuah indeks sheet privat bagi EXTERNSHEET-nya sendiri, tanpa perbaikan tingkat-pohon setara yang tersedia — begitu penelusuran rebinding bertemu sebuah referensi nama di mana pun dalam pohon, ia meninggalkan seluruh formula alih-alih menulis yang setengah-benar. Bahkan ketika rebind memang berhasil, sel tujuan tidak menampilkan sebuah angka yang baru dihitung ulang; ia menampilkan nilai yang sudah dipegang sel sumber pada saat penyalinan, dipertahankan dalam sebuah slot ter-cache dengan cara yang sama seperti Excel sendiri meng-cache nilai terakhir-diketahui dari referensi eksternal apa pun sampai Anda secara eksplisit me-refresh link, yang merupakan default yang tepat, karena menghitung ulang lintas sebuah link hidup ke file lain persis merupakan jenis operasi yang ingin Anda picu sekali, secara sengaja, alih-alih pada setiap pembukaan
Apa biaya desain ini bagi Anda
Mesin dekompilasi-dan-kompilasi-ulang milik AddCopy tidak gratis, dan biayanya layak direncanakan sebelum Anda menulis script sebuah job konsolidasi besar, bukan sesudahnya. Menyalin sebuah sheet di dalam workbook yang sama mengambil jalur murah, sebuah duplikasi langsung di-memori dari pohon terkompilasi, karena setiap indeks di dalamnya sudah valid di workbook tempatnya tinggal; sebuah copy lintas-workbook membayar untuk sebuah parse sungguhan pada setiap sel formula sebagai gantinya, dekompilasi ke teks lalu kompilasi teks itu lagi dari nol, dan sementara perbedaannya tidak layak diukur pada sebuah sheet dengan beberapa lusin formula, sebuah workbook sumber dengan puluhan ribu sel formula, disalin sebagai satu sheet di antara lusinan dalam sebuah job batch, seharusnya mengharapkan kompilasi ulang mendominasi waktu jalan alih-alih I/O file di sekitarnya. Urutan penyalinan penting untuk alasan kedua di luar kecepatan: sebuah formula yang merujuk sebuah sheet yang belum dijangkau AddCopy dalam batch ini gagal kompilasi ulangnya untuk alasan yang sama seperti sebuah formula yang merujuk sebuah sheet yang benar-benar tidak ada, sehingga sebuah job yang menyalin sheet B sebelum sheet A yang formula-nya bergantung padanya akan melihat formula itu menurun persis seperti yang dijelaskan di atas, teks string atau sebuah fallback external-link yang menunjuk kembali persis ke file sumber tempatnya baru saja berasal. Dan karena setiap workbook sumber dalam sebuah batch konsolidasi biasanya ditulis secara independen, layak secara eksplisit menguji satu mode kegagalan yang tidak pernah bisa diperingatkan file sumber tunggal mana pun kepada Anda — lima workbook cabang yang masing-masing mentotal angka sebuah cabang sejawat bisa bergabung menjadi sebuah referensi sirkular sungguhan di dalam workbook ringkasan tanpa file sumber individual mana pun pernah mengandung satu, sebuah siklus yang hanya ada begitu setiap sheet mendarat di tempat yang sama dan rekalkulasi berjalan atas kumpulan gabungan itu
Penyalinan worksheet lintas-workbook disertakan sebagai perilaku standar AddCopy di dalam HotXLS Delphi Excel Component untuk Delphi dan C++Builder; halaman produk membawa referensi API worksheet dan workbook lengkap, termasuk perilaku chart, rich-text, dan referensi-eksternal yang dijelaskan di sini