Sebuah follower shared formula dalam XLSX tidak membawa teks formula apa pun. Elemen <f t="shared" si="N"/>-nya menunjuk ke sebuah sel master di tempat lain dalam sheet, dan reader harus membangun ulang teksnya dengan menggeser formula master sebesar selisih baris dan kolomnya. HotXLS Component untuk Delphi dan C++Builder melakukan ekspansi itu pada saat open, sehingga setiap follower melaporkan sebuah formula yang lengkap
Bila Anda pernah memuat sebuah XLSX dunia nyata di sebuah pustaka pihak ketiga dan menemukan bahwa sebuah kolom berisi seribu formula punya teks di persis satu sel dan string kosong di 999 sel lainnya, Anda telah bertemu fitur ini dari sisi yang salah. Tidak ada yang korup. Berkasnya melakukan apa yang diizinkan ECMA-376, dan reader-nya sekadar berhenti pada titik tempat XML-nya berhenti
Kenapa sel shared formula kosong?
Karena format ini sengaja menyimpan formulanya hanya sekali. Dalam ECMA-376 Part 1 dan ISO/IEC 29500-1, elemen <f> (§18.3.1.40) membawa sebuah atribut t bertipe ST_CellFormulaType, dan nilai shared berarti sel ini berpartisipasi dalam sebuah grup yang diidentifikasi oleh atribut si. Persis satu sel dalam grup itu, sang master, juga membawa sebuah atribut ref yang memberikan rentang tempat grup itu berlaku, dan hanya sel itu yang membawa teks formula sebagai konten elemen. Setiap sel lain dalam grup adalah follower. Ia mengulangi t="shared" dan si yang sama, dan konten elemennya kosong. Excel menulis grup semacam ini secara agresif, karena sebuah fill-down atas sebuah kolom 200.000-baris runtuh dari 200.000 string formula menjadi satu string plus 199.999 elemen placeholder mungil. Penghematannya nyata dan biayanya jatuh sepenuhnya pada reader: tanpa ekspansi, follower tidak punya makna apa pun dengan sendirinya
Pergeseran ini adalah sebuah translasi, bukan salinan teks
HotXLS meresolusi sebuah follower dengan menemukan master yang terdaftar di bawah si yang sama, menghitung delta baris dan kolom dari anchor master ke sel saat ini, dan mentranslasikan setiap reference dalam formula master sebesar delta itu. Dimensi relatif bergerak, dimensi absolut tidak, dan mixed reference hanya menggerakkan paruh non-absolutnya. Literal string sama sekali dilewati, sehingga sebuah formula yang kebetulan mengandung teks "A1" mempertahankan teks itu tidak berubah di setiap follower
const
// xl/worksheets/sheet1.xml, trimmed to the interesting cells
SheetXml: WideString=
'<row r="1"><c r="A1"><v>1</v></c>'+
'<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
'A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)</f><v>7</v></c></row>'+
'<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
'<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';
var
Wb: TXLSXWorkbook;
Sh: TXLSXWorksheet;
begin
Wb:= TXLSXWorkbook.Create;
try
Wb.Open(FileName);
Sh:= Wb.Sheets[1];
// Master, verbatim
// B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
// Follower one row down: relative row moves, absolute row frozen,
// the mixed A$1 keeps its row, and the literal stays a literal
// B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
ShowMessage(Sh.Cells[2, 2].Formula);
finally
Wb.Free;
end;
end;Atribut ref adalah sebuah gerbang, bukan hiasan. Sebuah follower yang koordinatnya jatuh di luar rentang berlaku master tidak diekspansi, karena berkasnya kalau begitu sedang membuat sebuah klaim yang tidak didukung grupnya. Demikian pula, ketika sebuah pergeseran akan mendorong sebuah reference melampaui baris satu atau kiri dari kolom A, HotXLS menerbitkan #REF! untuk token itu, alih-alih diam-diam meng-clamp-nya, yang persis apa yang akan dihasilkan Excel sendiri untuk edit yang sama. Translasi ini adalah kerabat dekat, tapi bukan hal yang sama, dengan penulisan ulang reference yang terjadi ketika Anda menyisipkan atau menghapus baris. Jalur itu punya aturannya sendiri soal apa yang dilakukan sebuah range ketika sebuah edit memotong melaluinya, dan itu dijelaskan terpisah di artikel tentang penyesuaian reference formula saat insert dan delete. Ekspansi shared lebih sederhana: itu adalah sebuah offset murni dari sebuah anchor yang diketahui, diterapkan sekali, pada saat parse
Bentuk reference apa saja yang harus dicakup shifter?
Semuanya, kalau tidak ekspansinya adalah sebuah bug data-loss yang menyamar. Sebuah shifter naif yang hanya memahami A1 dan A1:B2 akan merusak atau membuang bentuk yang lebih eksotis, dan workbook nyata penuh dengan itu. Translator shared-formula HotXLS mengenali seluruh keluarga A1 sebelum memutuskan apa yang harus digerakkan. Reference workbook eksternal seperti [Book.xlsx]Sheet1!A1 dan reference 3D seperti Sheet1:Sheet3!A1 mempertahankan prefix-nya utuh sementara cell reference di ekornya bergeser. Nama sheet yang dikutip tetap bertahan, termasuk kasus yang menjengkelkan tempat sheet-nya secara literal bernama A1, sehingga 'A1'!A1 hanya menggeser bagian setelah tanda seru. Whole-column A:A menggerakkan dimensi kolomnya dan tidak ada yang lain; whole-row 1:1 menggerakkan dimensi barisnya dan tidak ada yang lain; $A:$A sama sekali tidak bergerak. Structured table reference seperti Table[A1] dibiarkan tidak tersentuh, karena bagian dalam kurung siku itu adalah sebuah nama kolom, bukan sebuah koordinat
// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1 : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3 : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]Nama fungsi adalah jebakan diam-diam di sini. Sebuah token scanner yang menangkap huruf diikuti angka akan dengan senang hati menulis ulang LOG10 menjadi LOG11 satu baris ke bawah. HotXLS mensyaratkan sebuah batas reference sebelum sebuah token kandidat dan sesudahnya, sehingga sebuah identifier yang berlanjut ke sebuah huruf, angka, underscore, titik, atau tanda kurung buka bukanlah sebuah cell reference. Bila Anda bekerja dalam keluarga notasi yang lain, masalah batas yang sama muncul secara berbeda, dan artikel notasi R1C1 membahas di mana kedua model itu berbeda
Kenapa elemen f self-closing menelan value berikutnya?
Karena sebuah elemen self-closing tidak menghasilkan event end-element apa pun. Ini adalah bug paling mahal tunggal di seluruh fitur ini, dan tidak spesifik pada satu XML parser mana pun. Dalam TXMLReader, <f t="shared" si="4"/> memunculkan persis satu event Element dengan IsEmptyElement diset True, dan tidak pernah memunculkan EndElement pasangannya. Sebuah parser yang menutup state penangkap-formulanya hanya pada EndElement karena itu tetap berada di dalam formulanya, dan teks berikutnya yang dilihatnya, yaitu hasil cache di dalam <v>, ditambahkan ke buffer formula. Lebih buruk lagi, state itu bertahan melewati batas sel, sehingga sel berikutnya yang memiliki sebuah <f> sungguhan justru teks formulanya diserap oleh sel sebelumnya. Perbaikannya adalah mengakhiri state formula pada event Element itu sendiri setiap kali IsEmptyElement bernilai True, dan menjalankan seluruh resolusi follower di situ alih-alih menunggu. Itu berarti membaca t, si, ref, aca, dan ca dari atributnya, menerapkan ekspansi shared, menulis atribut recalculation ke sel, dan membersihkan state shared, semuanya di dalam cabang yang menangani elemen kosong. Perhatikan bahwa formatnya mengizinkan kedua ejaan, <f t="shared" si="4"/> dan <f t="shared" si="4"></f>, dan yang kedua memang memunculkan sebuah EndElement. Sebuah reader yang benar harus menangani keduanya secara identik, dan itulah sebabnya HotXLS mencakup kedua ejaan dalam berkas regression yang sama
Nilai si yang sparse, tidak berurutan, dan pending queue
Atribut si adalah sebuah unsigned integer yang disediakan berkas, bukan sebuah posisi array yang Anda kendalikan. Tidak ada apa pun dalam skema yang mensyaratkan indeks shared bersifat padat, dimulai dari nol, atau muncul dalam urutan menaik, dan tidak ada apa pun yang mencegah sebuah berkas jahat atau sekadar aneh memakai si="4294967290" pada sel pertama. Mengukur sebuah lookup array dari si terbesar yang teramati karena itu adalah sebuah primitif memory-exhaustion, bukan sebuah optimisasi. HotXLS sebagai gantinya mempertahankan jalur workbook-open pada sebuah tabel sparse yang terurut: grup shared didaftarkan di bawah integer key-nya dalam sebuah TStringList yang terurut, yang membuat lookup menjadi sebuah binary search atas berapa pun jumlah grup yang sesungguhnya ada, tanpa hubungan dengan ukuran numerik dari indeksnya. Urutan adalah separuh kedua dari masalah ini. Sebuah master biasanya mendahului follower-nya dalam urutan dokumen, tetapi itu adalah sebuah konvensi, bukan sebuah aturan, sehingga follower mana pun yang tidak bisa meresolusi si-nya pada saat ia diurai masuk ke sebuah pending queue. Ketika sheet-nya selesai, queue itu diputar ulang terhadap tabel yang kini sudah lengkap, dan master yang datang belakangan meresolusi anak yatimnya. Sel yang tidak pernah menemukan sebuah master mempertahankan formula yang kosong, yang merupakan hasil jujur untuk sebuah berkas yang mereferensikan sebuah grup yang tidak pernah didefinisikannya
Mengekspansi shared formula tanpa memuat workbook
Streaming reader menghadapi syarat yang sama di bawah anggaran memori yang jauh lebih ketat, dan mereka menyelesaikannya dengan sebuah tabel worksheet-local. TXLSDirectReader dan TXLSRowCursor keduanya mengekspansi follower menjadi formula per-sel yang lengkap sambil mempertahankan perilaku bounded-memory dan proyeksinya, sehingga sebuah pass forward-only atas sebuah sheet 300 MB tetap memberi Anda teks formula yang sesungguhnya
var
Reader: TXLSDirectReader;
Cursor: TXLSRowCursor;
begin
// Projection: only rows 2..3, only column A. The master lives in row 1,
// outside the projection, and is still parsed so the followers resolve
Reader:= TXLSDirectReader.Create;
try
Reader.FirstRow:= 2;
Reader.LastRow:= 3;
Reader.IncludeColumn(1);
Reader.OnCell:= HandleCell; // Cell.Formula is fully expanded here
Reader.ReadFile(FileName);
finally
Reader.Free;
end;
// Forward-only row traversal, same expansion
Cursor:= TXLSRowCursor.Create;
try
Cursor.Open(FileName);
if Cursor.FindFirst then
repeat
if Cursor.CellCount > 0 then
WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
until not Cursor.FindNext;
finally
Cursor.Free;
end;
end;Ada dua batasan yang muncul dari desain itu. Pertama, proyeksi tidak pernah bisa melewatkan master. Sebuah filter baris yang diset dengan FirstRow dan LastRow, atau sebuah filter kolom yang dibangun dengan IncludeColumn, boleh saja melewatkan penerbitan sel master ke callback Anda, tapi parser-nya tetap harus mencatat si-nya, koordinat anchor-nya, rentang berlakunya, dan teks formulanya, kalau tidak setiap follower di dalam proyeksi itu meresolusi ke ketiadaan. Hanya pekerjaan sisi-follower, pergeseran dan decode nilai, yang aman untuk dilewatkan. Kedua, tabelnya per worksheet dan masa hidupnya harus dikelola secara eksplisit: TXLSRowCursor memegang satu instance sepanjang durasi sebuah sheet pass dan membersihkannya saat restart, ganti sheet, akhir berkas, exception, dan close, sehingga sebuah grup yang didefinisikan di sheet satu tidak pernah bisa bocor ke sheet dua. Karena jalur streaming ini adalah sebuah hot loop, ia memakai sebuah open-addressing integer hash alih-alih sorted string table, yang menghindari sebuah konversi integer-ke-string per sel
Apa yang terjadi saat save, dan di mana batasannya
Begitu sebuah follower sudah diekspansi, ia menjadi sebuah formula biasa, dan HotXLS menulisnya kembali sebagai sebuah elemen <f> independen tanpa t="shared" dan tanpa si. Round trip-nya stabil dan hasil cache <v> tetap bertahan, tapi outputnya lebih besar daripada inputnya untuk sebuah sheet yang sangat banyak berbagi, dan pengelompokan yang dibuat Excel tidak direkonstruksi saat save. Bila kesetiaan level-byte dari grup shared lebih penting bagi Anda daripada memiliki teks formula sungguhan di setiap sel, inilah trade-off yang Anda terima. Sisi XLS-nya kebetulan berbeda: record BIFF8 SHRFMLA punya encoding-nya sendiri dan writer-nya sendiri, dengan sebuah toggle shared-group pada workbook-nya
Ada dua hal terkait yang secara eksplisit bukan shared formula meski mereka berbagi elemen <f>. Legacy CSE array formula memakai t="array" dengan sebuah ref yang mencakup rentang yang di-anchor, dan dynamic array memakai ejaan t="array" yang sama tapi diidentifikasi lewat sebuah atribut cm yang berantai lewat cellMetadata ke sebuah record XLDAPR. Memperlakukan sebuah sel spill dynamic-array sebagai follower shared atau CSE adalah sebuah bug kebenaran yang sungguhan, dan pemisahannya dibahas di artikel tentang dynamic array dan spill formula. Baca ketiga kasus itu sebagai tiga parser yang kebetulan berbagi sebuah nama tag, dan kode-nya tetap jujur
Ekspansi shared-formula, streaming reader, dan reference translator yang dijelaskan di sini hadir sebagai bagian dari HotXLS Excel component untuk Delphi dan C++Builder; halaman produk memuat referensi API formula dan direct-read lengkap, termasuk property proyeksi yang dipakai di atas