Jika SUBTOTAL(109, ...) dan SUBTOTAL(9, ...) mengembalikan angka yang sama pada sebuah workbook yang mengandung baris tersembunyi, salah satu dari keduanya pasti salah. HotXLS, komponen spreadsheet Excel native untuk Delphi dan C++Builder, berperilaku persis seperti itu hingga versi 2.197.0, karena calculation engine-nya tidak punya cara untuk bertanya kepada sebuah worksheet apakah suatu baris tertentu tersembunyi
Gejalanya jarang datang sebagai laporan bug soal kode formula. Ia datang sebagai ketidakcocokan: sebuah batch job di server menghitung sebuah total, seorang pengguna membuka berkas yang sama di Excel dengan sebuah filter diterapkan, dan kedua angka itu berbeda sebesar apa pun jumlah baris yang terfilter keluar. Tidak ada yang mencurigai fungsi agregasinya, karena string formula pada sel-nya identik di kedua tempat. Perbedaannya sepenuhnya ada pada apa yang diizinkan dilihat oleh evaluator
Kenapa SUBTOTAL 109 menyertakan baris tersembunyi?
Karena dalam kebanyakan desain engine, lapisan yang mengevaluasi sebuah formula tidak pernah mengetahui soal visibilitas baris. HotXLS adalah kasus buku teks: calculation engine di lxCalc.pas menjangkau nilai sel lewat satu callback TXLSGetValue tunggal yang menjawab dengan sebuah nilai untuk triple (sheet, row, column) dan tidak ada yang lain. Visibilitas adalah sebuah atribut presentasi yang disimpan pada row record, dan tidak ada bagian dari record itu yang ikut berjalan turun sepanjang call chain. Engine karena itu hanya punya satu jalur agregasi, dan kedua paruh dari tabel function-number SUBTOTAL sama-sama meresolusi ke situ. Itu bukan defek sekelas rounding-error: itu adalah keseluruhan alasan paruh kedua tabel itu ada. ECMA-376 Part 1, diterbitkan sebagai ISO/IEC 29500-1, mendefinisikan SUBTOTAL dalam definisi fungsi formulanya (§18.17.7) dengan sebuah argumen pertama yang memilih baik agregasi internal maupun kebijakan hidden-row. Kode 1 hingga 11 dipetakan ke AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, dan VARP sambil menyertakan nilai pada baris yang disembunyikan secara manual. Kode 101 hingga 111 memilih sebelas agregasi yang sama dan mengecualikan nilai tersebut. Seorang pengguna yang mengetik 109 alih-alih 9 sedang membuat sebuah pernyataan yang disengaja soal data tersembunyi, dan sebuah engine yang meleburkan perbedaan itu diam-diam membatalkan pernyataan tersebut
Ke mana nomor fungsi ini dipetakan di dalam engine
HotXLS meresolusi argumen pertama SUBTOTAL di CalcSubtotalFunc, yang menormalisasi kode 101 hingga 111 turun ke identifier fungsi internal yang sama dengan kode 1 hingga 11 lalu men-dispatch berdasarkan agregasinya sendiri. Sebagian besar keluarga ini mengalir lewat accumulator incremental ExcelSum, yang menangani SUM, COUNT, COUNTA, MIN, MAX, dan AVERAGE. Lima di antaranya tidak bisa: STDEV, VAR, STDEVP, VARP, dan PRODUCT membutuhkan sebuah pass closed-form atas datanya, sehingga CalcSubtotalFunc merutekan kode internal 12, 46, 193, 194, dan 183 ke sebuah reducer terpisah, SubtotalReduceVariance. Pemisahan itu adalah hal pertama yang layak dipetakan sebelum menyentuh apa pun, karena dua jalur agregasi independen berarti dua loop cell-walk independen, dan sebuah perbaikan yang diterapkan hanya pada salah satunya menghasilkan hasil terburuk yang mungkin: SUBTOTAL(109, ...) menghormati filternya sementara SUBTOTAL(107, ...) pada range yang sama tidak. Menghitung loop-loop dalam HotXLS menemukan enam buah begitu AGGREGATE disertakan, tersebar di seluruh evaluasi range, pengumpulan range biasa, dan tiga reducer terpisah
Kenapa sebuah scratch field, bukan enam signature baru?
Karena menjalankan sebuah parameter baru melintasi enam fungsi cell-walk, ditambah semua yang memanggilnya, adalah perubahan luas pada sebuah hot code path hanya demi satu boolean. HotXLS sudah punya preseden untuk alternatifnya: sebuah field transient pada calculator, dalam semangat yang sama dengan scratch field yang dipakai GetRangeInfo untuk mencatat ketika sebuah 3D reference meresolusi ke sebuah workbook eksternal. Versi 2.197.0 menambahkan yang kedua. Engine ini mendapat sebuah tipe callback, TXLSIsRowHidden, dideklarasikan sebagai sebuah fungsi dari (SheetIndex, row) yang mengembalikan Boolean, disimpan di FIsRowHidden, ditambah sebuah flag transient FIgnoreHiddenRows. Flag ini dipersenjatai pada entry CalcSubtotalFunc ketika kode fungsinya jatuh di 101 hingga 111, dan pada entry CalcAggregateFunc untuk kode opsi AGGREGATE yang memilih pengecualian hidden-row. Setiap loop cell-walk kemudian memeriksanya dan melewati satu baris ketika sedang aktif, masing-masing menambahkan satu baris kode saja
// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
for rr := r1 to r2 do
begin
if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
Continue;
for cc := c1 to c2 do
begin
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;Ada dua detail dalam kode pemasangannya yang membawa kebenaran seluruh skema ini. Flag-nya disimpan dan dikembalikan, bukan sekadar diset dan dibersihkan, karena sebuah argumen SUBTOTAL bisa mengandung sebuah ekspresi yang menjalankan evaluasinya sendiri sementara agregasi luarnya masih berada di stack, dan pekerjaan bersarang itu tidak boleh mewarisi atau merusak gerbang luarnya. Dan pengembaliannya berada di dalam sebuah blok finally, karena CalcSubtotalFunc memiliki beberapa exit dini untuk kode error; sebuah flag yang tertinggal aktif setelah sebuah return error akan diam-diam merusak formula tak terkait berikutnya dalam urutan recalculation
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Pemeriksaan Assigned itulah yang menjaga perubahan ini tetap kompatibel. HotXLS memperluas constructor calculator dengan sebuah parameter ketiga yang defaultnya nil, sehingga kode apa pun yang membangun sebuah TXLSCalculator dengan pemanggilan dua-argumen yang lama tetap terkompilasi dan tetap mendapat perilaku legacy include-hidden. Tidak ada apa pun dari API yang sudah ada yang berubah bentuk
Dari mana sebenarnya bit hidden-row ini berasal?
Dari worksheet, lewat dua sumber berbeda, karena HotXLS membawa dua workbook engine. Sisi BIFF lawas menjawab dari TXLSRowInfoList.GetHidden, dijangkau lewat TXLSWorkbook.GetRowHidden. Sisi OOXML menjawab dari TXLSXWorksheet.GetRowHidden, dijangkau lewat TXLSXWorkbook.GetCalcRowHidden. Keduanya dihubungkan ke calculator pada saat konstruksi, berdampingan dengan callback nilai-sel yang mereka cerminkan. Konvensi baris adalah tempat jenis jembatan seperti ini biasanya salah, sehingga layak dinyatakan secara eksplisit. Calculator memberikan ke callback sebuah baris berbasis-0, cocok dengan koordinat yang sudah dipakai TXLSGetValue. Worksheet XLSX mengunci row-hidden map-nya berdasarkan nomor baris berbasis-1, persis seperti Excel menomori baris, yang juga yang diekspos oleh property publik RowHidden[ARow]. Jembatan XLSX karena itu menambahkan satu sebelum lookup-nya, dan jembatan BIFF tidak, karena TXLSRowInfoList sudah berbasis-0. Kedua jembatan memperlakukan sebuah sheet index atau row di luar rentang yang valid sebagai visible, sehingga sebuah query out-of-bounds mundur ke jawaban lama include-hidden alih-alih membuang data
Apa yang berubah untuk workbook yang difilter
Inilah kasus yang menghasilkan tiket dukungan. Menerapkan sebuah AutoFilter di HotXLS lewat ApplyAutoFilter mengevaluasi kriteria kolom dan menyembunyikan setiap baris data yang tidak cocok, yang persis sama dengan yang dilakukan Excel ketika pengguna mengklik sebuah dropdown filter. Sebelum v2.197.0, baris tersembunyi itu tidak terlihat oleh pengguna namun sepenuhnya terlihat oleh calculation engine, sehingga sebuah SUBTOTAL(109, ...) di sisi server melaporkan total yang tidak terfilter. Kini pemanggilan yang sama melaporkan yang sudah terfilter
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
VisibleRows: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
VisibleRows := Sheet.ApplyAutoFilter; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;Penyembunyian manual bekerja dengan cara yang sama, karena RowHidden[ARow] := True adalah state yang sama yang ditulis filter. Kesetaraan itu disengaja di Excel dan kini berlaku juga di HotXLS. Satu konsekuensi layak mendapat catatan di dokumentasi apa pun yang menyertai workbook hasil generate Anda: sebuah total yang dihitung dengan kode 109 adalah angka yang bergantung pada tampilan, sehingga seorang penerima yang menghapus filternya akan mengubah angka itu. Ketika sebuah laporan harus menyatakan sebuah angka tetap terlepas dari apa yang dilakukan pembaca terhadap tampilannya, kode 9 adalah pilihan yang benar dan selalu begitu. Filter, validasi, dan tabel dibahas bersama di artikel tentang data validation, AutoFilter, dan tabel. Karena menyembunyikan baris tidak menyentuh formula apa pun, itu juga tidak mengotori dependency graph dengan sendirinya, yang layak diketahui bila Anda mengandalkan incremental recalculation atas dirty subgraph untuk menjaga workbook besar tetap responsif
Kode opsi AGGREGATE dan satu batasan yang masih terbuka
AGGREGATE adalah SUBTOTAL dengan sebuah argumen kebijakan kedua, dan HotXLS menanganinya di CalcAggregateFunc. Argumen opsi ini mengkodekan sakelar-sakelar independen: apakah pemanggilan SUBTOTAL dan AGGREGATE bersarang di dalam range dilewati, apakah nilai pada baris tersembunyi dilewati, dan apakah nilai error disupresi alih-alih dipropagasi. HotXLS mempersenjatai gerbang hidden-row bersama untuk kode opsi 2, 3, 6, dan 7, dan mensupresi nilai error untuk kode opsi 4 hingga 7. Argumen function-number kemudian memilih agregasinya persis seperti yang dilakukan SUBTOTAL, termasuk perutean variance, standard deviation, dan product lewat reducer mereka masing-masing. Satu celah yang terdokumentasi masih tersisa, dan lebih baik dinyatakan di sini daripada ditemukan di produksi: semantik ignore-nested-SUBTOTAL yang terkait dengan kode opsi rendah tidak diimplementasikan di HotXLS. Mendeteksi sebuah SUBTOTAL bersarang di dalam sebuah range yang direferensikan membutuhkan penandaan recursion state evaluator sehingga sebuah agregasi internal bisa mengumumkan dirinya ke agregasi luarnya, yang merupakan perubahan yang lebih besar daripada gerbang hidden-row. Dalam praktiknya, eksposurnya kecil, karena workbook nyata hampir selalu menempatkan formula SUBTOTAL di luar range yang diagregasi formula SUBTOTAL lainnya. Jika generator Anda memang membangun range agregasi yang tumpang tindih, jangan mengandalkan kode opsi rendah untuk melakukan dedup terhadapnya
Arity guard yang dirilis bersamaan dengannya
Versi 2.197.0 juga menutup sebuah celah validasi di dispatcher yang sama, dan alasan desainnya sama dengan yang memotivasi scratch field: letakkan pemeriksaan itu di tempat yang bisa ditulis sekali saja. Kira-kira 280 tubuh fungsi built-in masing-masing memverifikasi jumlah argumennya sendiri terhadap Item.ChildCount, yang tidak menyisakan batas konsisten untuk kasus argumen terlalu banyak. Sebuah pemanggilan seperti =SIN(1,2) mencapai sebuah tubuh fungsi yang memeriksa argumen pertamanya, mengabaikan kelebihannya, dan mengembalikan sebuah angka yang tampak masuk akal padahal Excel mengembalikan #VALUE!. HotXLS sudah menyimpan arity yang dideklarasikan dari setiap built-in dalam function registry-nya, diekspos sebagai THashFunc.ArgsCnt dengan -1 menandai sebuah fungsi variadik seperti SUM, IF, atau CONCAT. Versi 2.197.0 meneruskan itu lewat sebuah property baru TXLSFormula.FuncArgsCntByPtg dan menambahkan satu gerbang di puncak GetValueItemFunc, dispatcher utamanya
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;Guard ini menolak argumen yang terlalu banyak dan sengaja tidak mengatakan apa pun soal yang terlalu sedikit. Menghilangkan sebuah argumen opsional trailing sah dilakukan di Excel untuk VLOOKUP, SUBSTITUTE, dan daftar panjang fungsi lainnya, sehingga sebuah pemeriksaan yang simetris justru akan merusak formula yang benar demi menangkap yang salah. Identifier yang tidak dikenal dilaporkan sebagai variadik dan sepenuhnya melewati gerbang ini, yang menjaga fungsi buatan pengguna tidak terganggu; bila Anda mendaftarkan fungsi Anda sendiri, perilaku yang dijelaskan di panduan formula engine dan fungsi kustom tidak terpengaruh. Mensentralisasi kasus terlalu-sedikit adalah pekerjaan terpisah, karena masing-masing dari 280 tubuh fungsi itu punya semantik kode error-nya sendiri dan harus ditinjau satu per satu, bukan diasumsikan
Calculation engine yang dijelaskan di sini, kedua facade workbook-nya, dan API AutoFilter serta row-visibility yang memberinya masukan adalah bagian dari HotXLS Delphi spreadsheet component, yang dirilis dengan source lengkap untuk Delphi dan C++Builder serta tidak membutuhkan instalasi Excel di mesin yang menjalankannya