Performa Workbook Excel Besar di Delphi dengan HotXLS

Ketika sebuah ekspor 300.000 baris menembus anggaran memorinya, jumlah barisnya biasanya yang disalahkan. Jumlah barisnya biasanya tidak bersalah. Bagian-bagian mahal dari sebuah workbook besar justru yang tercipta sebagai efek samping: sebuah style pool yang bertumbuh satu entri per cell karena formatting ditambahkan di dalam loop, XML worksheet yang dirakit sebagai satu string raksasa tunggal saat penyimpanan, sejuta badan formula identik yang disimpan satu per satu. HotXLS, library Delphi native milik losLab untuk file XLS dan XLSX, memberi Anda sebuah tuas spesifik untuk setiap biaya ini. Tidak satu pun dari tuas itu diaktifkan secara default, karena masing-masing mengubah sebuah trade-off, sehingga mengetahui tuas mana yang cocok dengan gejala mana adalah keterampilan performa yang sesungguhnya

Ke mana sebuah workbook besar menghabiskan memori

Ada dua rezim memori yang berbeda untuk dipikirkan. Selama generasi, cell model in-memory bertumbuh dengan setiap cell yang Anda sentuh: nilai, format, dan formula semuanya menjadi objek atau entri pool. Selama penyimpanan, jalur XLSX default juga merender XML setiap worksheet menjadi sebuah wide string sebelum mengompresnya ke dalam kontainer zip, sehingga pemakaian puncaknya adalah model itu ditambah bentuk terserialisasi dari sheet terbesar. Sebuah job yang selamat melewati loop pembangunan lalu mati di dalam SaveAs sedang menabrak rezim kedua, bukan yang pertama, dan perbaikan untuk yang satu tidak berbuat apa-apa untuk yang lain

Ukuran file mengikuti sebuah aturan yang terkait: cell hanyalah salah satu kontributor, di samping style, shared string, formula, gambar, dan comment. Sebuah lintasan audit dengan ForEachCell dan jumlah koleksi per-sheet memberi tahu Anda resource mana yang sebenarnya mendominasi sebuah file bermasalah sebelum Anda mengoptimalkan yang salah. Satu kehalusan pengukuran: Sheet.Cells.Count di sisi XLSX melaporkan jumlah cell yang diinstansiasi dalam penyimpanan sparse-nya, bukan luas used range. Sebuah sheet yang datanya menempati sebuah persegi panjang 1000-kali-50 dengan setengah cell-nya kosong akan terhitung kira-kira 25.000, bukan 50.000. Perbedaan itu penting ketika Anda membandingkan sebuah file "besar" milik pelanggan dengan fixture Anda sendiri, karena luas used-range dan populasi cell sesungguhnya bisa berbeda satu orde besaran dalam layout finansial yang sparse

StreamingWrite memperbaiki jalur penyimpanan, bukan jalur pembangunan

Menyetel TXLSXWorkbook.StreamingWrite := True mengalihkan SaveAs ke sebuah serializer streaming yang menulis XML worksheet langsung ke dalam stream zip, menghilangkan perantara string per-sheet. Ia default ke False demi kompatibilitas perilaku, dan mengaktifkannya adalah sebuah perubahan satu baris:

Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Bulk');
  for R := 1 to 100000 do
  begin
    Sheet.Cells[R, 1].Value := R;
    Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
    Sheet.Cells[R, 3].Value := R * 1.5;
  end;
  Book.StreamingWrite := True;   // XML sheet mengalir ke dalam kontainer zip
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Bersikaplah presisi soal apa yang didapatkan dari ini: cell model yang dibangun oleh loop tersebut tetap menempati memori dalam jumlah yang persis sama seperti sebelumnya. StreamingWrite meratakan lonjakan saat-penyimpanan, yang menjadi perbedaan antara sebuah batch job yang selesai dan yang gagal pada tanda 95%. Jika loop pembangunannya sendiri yang menghabiskan memori, tuas yang Anda butuhkan adalah dua yang berikutnya

Style pool: tambahkan sekali, gunakan kembali indeksnya

Formatting XLSX dalam HotXLS berbasis pool: Book.Fonts.Add(...), Fills.AddSolid(...), dan Borders.Add(...) mengembalikan sebuah indeks pool berbasis 0 yang dirujuk cell. Memanggil Fonts.Add dengan parameter identik di dalam sebuah loop akan dideduplikasi, sehingga hanya membuang waktu, bukan ruang. Alignments.Add berperilaku berbeda: ia mengembalikan sebuah objek baru pada setiap pemanggilan, sehingga pembuatan alignment per-cell menumbuhkan pool secara linear dengan jumlah baris. Satu kebiasaan mencakup kedua kasus ini. Selesaikan setiap indeks pool sekali saja, di luar loop, dan tetapkan indeksnya di dalamnya

// angkat lookup pool keluar dari loop yang sibuk
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // indeks pool berbasis 0
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // cell menyimpan berbasis 1; 0 = default

+ 1 itu bukan sebuah typo, dan melupakannya adalah bug klasik penghasil gejala di sini: pool-pool itu mengeluarkan indeks berbasis 0, sementara properti di sisi cell memperlakukan 0 sebagai "default", sehingga setiap indeks pool harus digeser satu saat penetapan. Salah karena kelalaian ini, dan header Anda diam-diam dirender dalam font default milik workbook, sebuah cacat yang tidak disadari siapa pun sampai review branding

Ganti lalu lintas Variant per-cell dengan callback baris

Setiap Sheet.Cells[R, C].Value := X melibatkan sebuah lookup-atau-create cell plus sebuah penetapan Variant. Pada beberapa ratus ribu cell, overhead per-akses itu menjadi terukur dalam profil. HotXLS menyediakan API callback massal pada kedua facade (ForEachCell dan ForEachRow untuk membaca, WriteCells dan WriteRows untuk menulis) yang memindahkan iterasi ke dalam engine dan menyerahkan kepada kode Anda seluruh baris sekaligus:

procedure TLedgerExport.FillRow(Sender: TObject;
  SheetIndex, Row, FirstCol, LastCol: Integer;
  var Values: Variant; var Skip: Boolean; var Cancel: Boolean);
begin
  if Row > FCount then
  begin
    Cancel := True;     // hentikan seluruh penulisan
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// satu pemanggilan engine, bukan ratusan ribu akses properti
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

Flag Skip milik callback membiarkan sebuah baris tidak tersentuh tanpa membatalkan operasinya, dan Cancel mengakhiri operasi lebih awal, yang berguna ketika sumbernya adalah sebuah reader yang panjangnya baru Anda ketahui sambil berjalan. Pasangkan WriteRows untuk pembangunan dengan StreamingWrite untuk penyimpanan, dan jalur generasi tidak lagi punya titik panas per-cell yang tersisa

Tuas sisi-baca pada facade XLS

File .xls lawas berukuran besar punya toolkit-nya sendiri. _DisableGraphics := True sebelum Open melewati parsing drawing layer sepenuhnya, yang mempercepat pemuatan workbook yang membawa bertahun-tahun akumulasi shape dan gambar tertanam. Batasannya tegas: drawing layer kemudian tidak ada dalam model, sehingga menyimpan workbook semacam itu menulis sebuah file tanpa drawing-nya. Cadangkan flag ini untuk job analisis khusus-baca. SetTempDir mengalihkan file sementara milik BIFF writer, yang penting pada server tempat lokasi temp default punya kuota atau berada di storage yang lambat. UseSharedFormulas mengelompokkan badan formula yang berulang ke dalam rekaman shared-formula, mengecilkan file tempat sebuah kolom formula berulang sampai enam puluh ribu baris

Loop pembacaan atas data XLS punya sebuah jebakan indexing yang layak ditandai karena ia menggandakan kerja ketika ditangani secara defensif dan merusak hasil ketika terlewat: UsedRange melaporkan batas FirstRow, LastRow, FirstCol, dan LastCol-nya berbasis 0, sementara Cells.Item[Row, Col] berbasis 1. Sebuah scan yang menelusuri used range harus menambahkan satu ke setiap koordinat saat mengakses cell, seperti pada Cells.Item[Row + 1, Col + 1], atau ia membaca sebuah grid yang bergeser secara diagonal satu cell, diam-diam menjatuhkan baris dan kolom terakhir serta menyertakan sebuah baris pertama hantu. Callback ForEachCell sepenuhnya menghindari ketidakcocokan ini, yang menjadi satu alasan lagi untuk lebih memilihnya pada scan seluruh sheet

Periksa file sebelum memuatnya

Operasi workbook-besar yang paling murah adalah yang Anda hindari. GetSheetNames pada kedua facade mendaftar worksheet sebuah file tanpa memuat data cell-nya. Implementasi XLSX-nya hanya membaca manifest workbook di dalam zip dan dengan sengaja membiarkan instance workbook itu tidak terisi, dan facade XLS berhenti memindai pada batas substream pertama. Itu menjadikannya pemeriksaan pra-terbang yang tepat untuk "sheet mana yang harus disasar job import ini", dan CanReadEncrypted menjawab "apakah ini sebuah kontainer terenkripsi" sebelum sebuah upaya Open yang sudah pasti gagal

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // kegagalan mengosongkan daftarnya
  // pilih sheet targetnya, lalu tentukan apakah sebuah Open penuh sepadan dilakukan
finally
  Book.Free;
  Names.Free;
end;

Perhatikan konvensi kode-kembalinya: fungsi pemeriksa ini menandakan kegagalan dengan nilai pada atau di bawah nol dan mengosongkan daftar output-nya, sehingga uji dengan <= 0, bukan membandingkan terhadap satu nilai sukses tertentu

Menyesuaikan pendekatan dengan skala pekerjaan

Untuk pipeline tanpa pengawasan yang menghasilkan banyak file besar secara berurutan, dua kebiasaan lagi melengkapi gambarannya. Objek workbook tidak thread-safe untuk pemakaian bersama, tetapi tidak ada yang menghalangi satu workbook independen per worker thread, yang memparalelkan konversi batch dengan bersih. Dan ketika output menuju HTTP alih-alih disk, overload penyimpanan TStream berpadu dengan StreamingWrite sehingga sebuah response besar tidak pernah terwujud sebagai sebuah file temp. Satu catatan operasional berlaku: penyimpanan stream menulis dari posisi saat ini tanpa mundur ke awal, sehingga setel Position := 0 sebelum menyerahkan stream itu ke framework response. Artikel tentang streaming write dan batch job mengembangkan pola sisi-server itu, dan artikel tentang ekspor database menunjukkan di mana tuas-tuas ini masuk ke dalam sebuah laporan yang digerakkan dataset

Terakhir, simpan satu fixture kasus-terburuk per keluarga laporan dan ukur waktunya di CI. Regresi performa dalam pembuatan dokumen jarang mengumumkan dirinya sendiri. Sebuah style yang ditambahkan di dalam sebuah loop atau sebuah probe yang diganti dengan sebuah Open penuh tidak mengubah apa pun secara fungsional, dan batch malam hari itu hanya menjadi empat puluh menit lebih lama. Sebuah test berwaktu pada sebuah fixture setengah-juta-cell yang representatif mengubah pergeseran itu menjadi sebuah build merah, bukan sebuah insiden operasional

Build evaluasi, proyek demo dengan sebuah contoh generasi massal, dan referensi API lengkap tersedia pada halaman HotXLS Delphi Component