Spreadsheet dengan sejuta baris dan selusin kolom adalah hasil ekspor yang sangat biasa dari tugas pelaporan database. Jika Anda membukanya dengan cara biasa, yaitu memuat seluruh workbook ke dalam TXLSWorkbook, proses tersebut harus mewujudkan setiap dari dua belas juta sel itu sebagai objek aktif sebelum baris pertama logika bisnis Anda berjalan. File di disk mungkin berukuran enam puluh megabyte XML terkompresi. Pohon objek yang dihasilkannya bisa beberapa kali lipat ukurannya, dan semuanya harus berada di memori secara bersamaan karena desain modelnya adalah akses acak (random-access). Untuk laporan yang hanya ingin Anda baca dari atas ke bawah lalu dibuang, itu adalah jumlah memori yang sangat besar yang dihabiskan untuk struktur yang tidak pernah Anda butuhkan
Ada jalur kedua untuk melalui file yang sama. Alih-alih membangun sebuah model, Anda memindai XML worksheet secara maju saja (forward only), satu sel setiap kalinya, dan membiarkan setiap sel berlalu setelah Anda melihatnya. Tidak ada yang menumpuk. Memori tetap nyaris konstan baik sheet tersebut memiliki seribu baris maupun sepuluh juta baris, karena pembaca tidak pernah menahan lebih dari bagian yang sedang diurainya ditambah beberapa tabel pencarian kecil. Inilah yang dilakukan oleh pembaca langsung HotXLS, dan sisa artikel ini membahas mengapa ini tetap berukuran kecil dan apa yang diberikannya kepada Anda sebagai gantinya
Mengapa model in-memory tidak menskala
File XLSX adalah paket ZIP berisi bagian-bagian XML yang dijelaskan oleh ECMA-376. Setiap worksheet adalah bagiannya sendiri, xl/worksheets/sheetN.xml, dan di dalamnya setiap baris adalah elemen <row> yang menampung elemen sel <c>. Jalur pemuatan reguler membaca bagian itu dan mengonstruksi objek yang dapat dialamatkan untuk setiap sel sehingga Anda nantinya dapat meminta Cells[12345, 7] dan mendapatkan jawaban dalam waktu konstan. Akses acak adalah inti utama dari model workbook, dan itulah yang membuat pengeditan, evaluasi formula, dan penataan gaya menjadi nyaman
Harganya adalah akses acak menuntut semuanya ada secara bersamaan. Anda tidak dapat melakukan indeks ke dalam struktur yang baru Anda bangun sebagian. Jadi memori puncak dari pemuatan penuh merupakan fungsi dari jumlah sel, dan pada sheet dengan jutaan sel yang terisi, fungsi tersebut akan mendarat di suatu tempat yang tidak diinginkan oleh layanan Anda, terutama jika beberapa tugas tersebut berjalan sekaligus di mesin bersama. Ketika pola akses yang sebenarnya Anda butuhkan bersifat sekuensial, membayar untuk akses acak berarti membayar untuk kemampuan yang tidak akan Anda gunakan
Pemindaian SAX maju-saja yang tidak membangun pohon
Pembaca langsung membuka paket ZIP dan menyusuri setiap bagian worksheet dengan pull parser bergaya SAX. SAX di sini berarti parser melaporkan kejadian urai (parse events) saat menemukannya, elemen awal, rentetan teks, elemen akhir, lalu beralih. Parser ini tidak menyisakan pohon node di belakangnya. Pembaca melacak baris dan kolom saat ini dari atribut r, mengumpulkan tipe sel, indeks gaya, nilai, dan teks formula saat kejadian tiba, dan ketika tag penutup </c> terlihat, pembaca memancarkan (emits) satu sel lalu melupakannya. Sel berikutnya menggunakan ulang segelintir variabel lokal yang sama
Karena tidak ada yang ditahan di antara sel, jejak memori tidak membengkak seiring bertambahnya jumlah sel. Itu adalah properti yang patut dipertahankan. Sheet dua ratus baris dan sheet dua puluh juta baris membutuhkan memori residen yang sama bagi pembaca, dan perbedaan di antara keduanya hanyalah seberapa lama pemindaian berjalan. Anda menyerahkan akses acak, fitur unggulan model tersebut, dan sebagai imbalannya Anda mendapatkan batas atas memori yang tidak dapat ditembus oleh jumlah sel
Apa yang tetap berada di memori, dan mengapa kedua bagian tersebut
Pemindaian tidak sepenuhnya tanpa-keadaan (stateless), dan pengecualiannya cukup instruktif. Dua tabel kecil harus ditahan di memori selama proses berlangsung, karena sel itu sendiri tidak membawa cukup informasi untuk ditafsirkan tanpanya
Yang pertama adalah tabel string bersama (shared string table). Dalam SpreadsheetML, sel teks tidak menyimpan teksnya sendiri. Sel itu membawa t="s" dan payload numerik yang merupakan indeks ke dalam xl/sharedStrings.xml, sebuah daftar tunggal yang dideduplikasi dari setiap string yang berbeda di dalam workbook. Ini adalah pertukaran ruang yang bagus untuk file di mana label yang sama berulang di ribuan baris, tetapi ini berarti pembaca harus memuat tabel string tersebut di awal dan mempertahankannya di memori, karena sel mana pun di mana pun di sheet mana pun dapat mereferensikan entri mana pun di dalamnya. Tabel tersebut diukur dari jumlah string yang berbeda, bukan oleh jumlah sel, sehingga ukurannya tetap wajar bahkan pada sheet yang sangat besar sekalipun
Yang kedua adalah pemetaan format angka dari bagian gaya. Sel numerik dan sel tanggal berukuran sama persis secara byte di jalur transmisi: keduanya berupa angka polos, karena tanggal dalam SpreadsheetML hanyalah hitungan hari serial. Satu-satunya hal yang membedakannya adalah gaya sel tersebut, yang menunjuk melalui cellXfs di xl/styles.xml ke sebuah id format angka. Untuk melaporkan tanggal sebagai tanggal ketimbang sebagai nomor serial mentah, pembaca memuat tabel gaya-ke-format itu dan menahannya di memori. Segala hal lainnya di dalam file, data sel aktual yang menyusun sebagian besar byte, mengalir lewat tanpa disimpan
Setiap sel melaporkan jenis dan nilainya
Setiap sel yang dipancarkan tiba sebagai record TXLSDirectCell. Record ini membawa indeks dan nama sheet, baris dan kolom berbasis 1, Kind (jenis) semantik, Value sebagai Variant, teks Formula tanpa tanda sama dengan di depannya, dan StyleIndex mentah. Jenisnya adalah salah satu dari xdkNumber, xdkString, xdkBoolean, xdkDate, atau xdkError, sehingga Anda dapat membuat percabangan berdasarkan arti sel ketimbang menurunkannya ulang dari atribut. Sel formula melaporkan jenis hasil cache-nya, bersamaan dengan teks formula di sebelahnya, sehingga total perhitungan akan masuk sebagai angka yang juga memberi tahu Anda bagaimana total itu dihasilkan
type
TReportScan = class
procedure OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
end;
procedure TReportScan.OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
begin
case Cell.Kind of
xdkString: AccumulateLabel(Cell.Row, Cell.Col, VarToStr(Cell.Value));
xdkNumber: AddToTotals(Cell.Col, Double(Cell.Value));
xdkDate: NoteWhen(Cell.Row, VarToDateTime(Cell.Value));
xdkBoolean: FlagRow(Cell.Row, Boolean(Cell.Value));
xdkError: LogBadCell(Cell.Row, Cell.Col, VarToStr(Cell.Value));
end;
end;Membedakan tanggal dari angka
Masalah tanggal patut ditelaah lebih saksama karena di sinilah sebagian besar pemindai naif melakukan kesalahan. Tidak ada tipe tanggal pada sel numerik. Sel yang menampung nilai serial 46000 bisa saja merupakan kuantitas, harga, atau 17 Februari 2025, dan file itu memberi tahu Anda tipe yang mana hanya melalui id format angka yang dicapai melalui gaya sel. ECMA-376 mencadangkan satu blok id format bawaan yang maknanya tetap di semua pembuat yang mematuhinya, dan id bermuatan tanggal berada di dua rentang: 14 hingga 22 untuk format standar tanggal dan waktu, serta 45 hingga 47 untuk format waktu berlalu (elapsed-time) seperti [h]:mm:ss. Saat DetectDates diaktifkan, seperti standarnya, pembaca meresolusi gaya setiap sel numerik ke id formatnya, dan sel yang id-nya masuk dalam rentang yang dicadangkan tersebut dilaporkan sebagai xdkDate dengan Value-nya sudah dikonversi ke sebuah TDateTime Delphi. Format kustom juga diperiksa, dengan memeriksa kode format untuk mencari token tanggal dan waktu, tetapi rentang yang dicadangkan adalah tulang punggung yang dapat diandalkan. Nonaktifkan DetectDates dan tabel gaya bahkan tidak dimuat sama sekali, setiap sel numerik masuk sebagai xdkNumber, dan pemindaian tersebut secara fraksional menjadi lebih ramping
Melewati sheet dan membatalkan lebih awal
Pemindaian sekuensial memiliki keunggulan tak kasat mata yang tidak bisa ditandingi oleh akses acak: Anda dapat berhenti. Event OnSheet dipicu sebelum setiap worksheet dibuka, dan memberi Anda dua sakelar. Atur SkipSheet dan seluruh bagian itu tidak akan pernah diurai, yang mana merupakan cara Anda untuk memindai hanya sheet yang Anda pedulikan di workbook multi-sheet tanpa harus membayar pemrosesan untuk membaca sisanya. Atur Abort dan seluruh pemindaian segera berakhir. Event OnCell membawa Abort-nya sendiri, sehingga Anda dapat berhenti saat Anda telah menemukan apa yang Anda cari, sebuah baris tertentu, nilai sentinel, akhir dari blok header, tanpa membaca sisa jutaan sel lainnya. Pada pemindaian maju-saja, pembatalan benar-benar gratis, karena pekerjaan yang Anda lewati adalah pekerjaan yang belum terjadi
procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
// Scan only the "Data" sheet; leave the rest unread
SkipSheet := SheetName <> 'Data';
end;Menghitung sel tanpa penangan
Satu penyempurnaan baru-baru ini patut disebutkan karena ini mengubah pertanyaan umum menjadi satu panggilan yang murah. Pembaca menghitung setiap sel terisi yang dilewatinya, dan pembaca melakukan hal ini terlepas dari apakah penangan OnCell dipasang atau tidak. Sebelumnya, dengan penangan yang tidak diatur, jumlah sel yang terisi akan kembali sebagai nol, karena penghitungan adalah efek samping dari pemancaran (emitting). Kini, penghitungan tidak bergantung pada pancaran. Itu berarti Anda dapat menanyakan satu pertanyaan, yaitu berapa banyak sel terisi yang sebenarnya dikandung oleh workbook ini, dan mendapatkan jawabannya dengan biaya pemindaian tanpa panggilan balik (callbacks) sama sekali. ReadFile dan ReadStream keduanya mengembalikan total tersebut sebagai tipe Int64, dan angka yang sama kemudian tersedia sebagai properti CellCount. Nilai kembalian -1 menandakan bahwa file tidak dapat dibuka atau bukan paket OOXML
var
Reader: TXLSDirectReader;
Populated: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// No OnCell handler: a pure populated-cell census, still near-constant memory
Populated := Reader.ReadFile('quarterly_export.xlsx');
if Populated < 0 then
raise Exception.Create('Not a readable XLSX package')
else
Writeln(Format('%d populated cells (CellCount = %d)',
[Populated, Reader.CellCount]));
finally
Reader.Free;
end;
end;Untuk pemindaian penuh, Anda memasang penangan dan memanggil ReadFile dengan cara yang sama persis. Kontrasnya dengan muatan penuh (full load) adalah poin utamanya: di mana memuat quarterly_export.xlsx ke dalam workbook akan membentangkan setiap sel menjadi objek residen dan menahan semuanya, pembaca langsung hanya menyimpan string bersama dan tabel gaya sementara dua belas juta sel mengalir melalui OnCell Anda satu per satu. Aritmetika yang berjalan per sel tidak menyisakan apa pun, sehingga memori puncak ditentukan oleh jumlah string berbeda milik workbook, bukan oleh jumlah barisnya
Pembaca langsung adalah alat yang tepat ketika tugasnya adalah membaca workbook besar satu kali untuk mengekstrak atau merangkumnya. Bila sebaliknya Anda membutuhkan akses acak dari model penuh namun ingin agar ini berjalan baik pada file-file besar, penyetelan di catatan kami tentang performa workbook besar di Delphi mencakup jalur tersebut. Dan ketika arahnya dibalik, memproduksi output besar ketimbang mengonsumsinya, panduan tulis streaming untuk pekerjaan batch server menerapkan disiplin memori konstan yang sama pada penulisan. Ketiganya dikirimkan sebagai bagian dari Komponen HotXLS untuk Delphi dan C++Builder, berdampingan dengan API pembacaan, penulisan, formula, dan format yang dibahas di bagian lain dari blog ini