HotXLS sekarang mengevaluasi structured table reference, sehingga =SUM(Table1[Amount]) menghasilkan sebuah angka, bukannya dilewati begitu saja. Resolver-nya menangani Table[Column], Table[[Column]], column span seperti Table[[Q1]:[Q4]], dan item specifier [#Data], [#All], [#Headers], serta [#Totals], meresolve masing-masing terhadap table model workbook pada saat parsing sementara teks formula asli tetap round-trip secara verbatim
Satu bentuk sengaja tidak hadir, dan itulah yang paling dulu ditemui orang. Singkatan current-row [@Column] tidak didukung, karena alasan struktural yang layak dipahami daripada sekadar diakali secara membabi buta
Mengapa structured reference bukan sekadar range dengan nama yang bersahabat?
Karena sebuah defined name membekukan sebuah alamat sedangkan table reference tidak. Tulis DataBlock sebagai sebuah nama yang menunjuk ke Sheet1!$A$2:$D$100 dan ia akan tetap menjadi persegi panjang itu sampai sesuatu menulis ulangnya. Tulis Sales[Amount] dan artinya adalah "kolom Amount dari table Sales", apa pun extent table itu kebetulan berada saat formula dievaluasi. Tambahkan dua puluh baris ke table itu dan sum-nya akan mencakupnya; tidak ada referensi yang perlu disesuaikan karena memang sejak awal tidak pernah ada alamat di dalam formula itu
Sifat simbolik itulah yang persis menjadi alasan mengapa referensi ini tidak bisa diselesaikan lewat substitusi string. Resolver-nya harus mencari table itu berdasarkan nama di dalam workbook, mencari kolom berdasarkan teks header-nya, memutuskan baris mana yang dicakup oleh item specifier yang diminta, dan menghasilkan sebuah persegi panjang yang konkret. HotXLS melakukan ini selama kompilasi formula lewat table model, itulah sebabnya sebuah formula yang ditulis sebelum table-nya bertambah besar tetap dievaluasi terhadap extent table saat ini
Grammar yang diselesaikan HotXLS
Grammar spec yang didukung mencakup satu hasil persegi panjang tunggal dan layak dinyatakan secara presisi, karena dokumentasi Excel menyajikan permukaan yang jauh lebih luas daripada yang diimplementasikan sebagian besar engine. HotXLS menerima [Col] dan variannya yang berkurung [[Col]], item specifier telanjang [#Data], [#All], [#Headers], dan [#Totals], bentuk gabungan [[#Data],[Col]], sebuah span di dalam item specifier seperti [[#Data],[Col1]:[Col2]], dan span biasa [Col1]:[Col2]
Apa yang diberikan oleh kumpulan itu adalah setiap bentuk referensi yang menghasilkan satu blok yang berdampingan: sebuah kolom, sederetan kolom yang bersebelahan, sebuah irisan body-only atau header-inclusive dari salah satunya. Union yang tidak bersebelahan dan hasil multi-area berada di luar itu. Saat sebuah referensi tidak bisa diselesaikan, formula itu tetap memakai perilaku skip-without-value sebelumnya alih-alih mengganti dengan sebuah tebakan, sehingga referensi yang tidak bisa diselesaikan tidak pernah berubah menjadi angka salah yang tampak masuk akal
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cols: TStringList;
begin
Book := TXLSXWorkbook.Create;
Cols := TStringList.Create;
try
Sheet := Book.Sheets.Add('Sales');
Cols.Add('Region');
Cols.Add('Q1');
Cols.Add('Q2');
Cols.Add('Amount');
Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
// ... tulis baris header dan 24 baris data ...
Sheet.Cells[27, 4].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[28, 4].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[29, 4].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[30, 4].Formula := 'ROWS(SalesTable[#All])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;Mengapa bentuk current-row dikecualikan dengan sengaja?
[@Column] dan [#This Row] berarti "cell dari kolom itu pada baris tempat formula ini berada". Nilainya karena itu bergantung pada posisi cell yang mengevaluasi, bukan hanya pada table-nya. Itu adalah jenis referensi yang berbeda: bukan sebuah persegi panjang yang bisa diselesaikan compiler sekali saja, melainkan sebuah resolusi per-cell yang harus diulang untuk setiap baris yang ditempati formula itu
HotXLS mengembalikan False dari table-range resolver untuk bentuk-bentuk itu, yang mengarahkannya ke jalur skip-without-value. Teks formula dipertahankan dan ditulis kembali tanpa perubahan, sehingga sebuah workbook yang memakai [@Amount] tetap terbuka dengan benar di Excel setelah round-trip melalui aplikasi Anda; yang hilang hanyalah nilai yang dihitung HotXLS. Diberi pilihan antara sebuah nilai yang hilang dan sebuah nilai yang dihitung terhadap baris yang salah, ketiadaan adalah yang bisa Anda deteksi
Cara mengakalinya secara praktis bersifat mekanis: pada workbook yang Anda hasilkan, tulis referensi relatif gaya A1 yang setara, yang memang disimpan Excel secara internal untuk sebagian besar logika ber-scope table. Pada workbook yang hanya Anda proses, biarkan formula itu apa adanya dan baca nilai cached yang sudah disimpan Excel, yang biasanya memang diinginkan sebuah pipeline load-and-report
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Table: TXLSXTable;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('sales.xlsx') <> 1 then Exit;
Sheet := Book.Sheets[1];
Table := Sheet.Tables.FindByName('SalesTable');
if Table <> nil then
begin
// Lookup gaya recordset atas body table, hasil baris 1-based
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value));
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
end;
finally
Book.Free;
end;
end;Apa yang terjadi saat table berubah bentuk
Structured reference diinvalidasi, bukannya diam-diam diarahkan ulang, saat hal yang mereka namai lenyap. Hapus sebuah kolom dan formula yang merujuk ke kolom itu diinvalidasi dengan cara yang sama seperti Excel menginvalidasinya; hapus atau ganti nama table-nya dan referensi ke sana ditangani dengan cara yang sama. Ini adalah perilaku yang benar dan mencerminkan penyesuaian referensi biasa, dijelaskan di penyesuaian formula reference pada insert dan delete, di mana tugas engine adalah menjaga formula tetap jujur, bukan sekadar terlihat valid
Pertumbuhan baris adalah kasus sebaliknya dan sama sekali tidak membutuhkan penyesuaian. Karena referensi itu menamai table, bukan sebuah persegi panjang, menambahkan baris di dalam range table itu memperluas apa yang dicakup [#Data] tanpa menyentuh satu pun formula. Itulah properti yang membuat table layak dipakai di dalam sebuah report template: baris total tetap menjumlahkan semua yang dihasilkan import, berapa pun jumlah baris yang ternyata dihasilkan
Disiplin round-trip
HotXLS mempertahankan teks formula aslinya. Sebuah workbook yang dimuat dengan SUM(SalesTable[Amount]) disimpan dengan SUM(SalesTable[Amount]), bukan dengan hasil resolusi SUM(D2:D25). Ini lebih penting daripada yang mungkin terlihat: pengguna yang membuka output Anda di Excel mengharapkan melihat formula yang mereka tulis, dan sebuah alamat hasil resolusi akan diam-diam mengubah model yang bisa memelihara dirinya sendiri menjadi model yang rapuh yang berhenti mencakup baris baru
Dua kemampuan terkait melengkapi gambarannya. Definisi table itu sendiri, termasuk table tanpa header dan komentar per-table, round-trip lewat table model yang dijelaskan di data validation, AutoFilter, dan table Excel. Dan saat banyak cell berbagi satu pola, XLSX menyimpannya sekali sebagai sebuah shared formula, yang diekspansi dan diterbitkan kembali seperti dibahas di ekspansi si shared formula. Structured reference di dalam shared formula melewati kedua jalur itu, sehingga keduanya harus berperilaku benar, dan memang begitu
HotXLS membaca dan menulis XLS, XLSX, dan ODS dari Delphi dan C++Builder tanpa instalasi Excel dan tanpa Office automation, mengevaluasi formula di dalam engine-nya sendiri. Table model, formula engine, dan API recalculation didokumentasikan di halaman komponen spreadsheet Delphi HotXLS