Membangun Workbench Audit dan Konversi Workbook di Delphi dengan HotXLS

Sebuah job normalisasi spreadsheet massal adalah tiga masalah yang mengenakan satu mantel. Anda punya sebuah arsip berformat campuran: .xls era BIFF, .xlsx modern, sebaran .ods dari eksperimen LibreOffice tertentu, dan segelintir file yang tidak bisa dibuka siapa pun karena passwordnya ikut pergi bersama mantan karyawan. Tujuannya adalah mengonversi semuanya menjadi XLSX dan CSV. Versi job itu yang ditulis kebanyakan orang adalah sebuah loop yang membuka tiap file lalu menyimpannya dengan ekstensi baru, dan itu berjalan baik-baik saja sampai seseorang bertanya file mana yang kehilangan chart-nya, kehilangan makronya, atau bahkan tidak pernah berhasil terbuka sama sekali. Loop itu tidak punya jawaban, karena konversi saja tidak menyimpan catatan apa pun. Sebuah workbench punya jawabannya: ia melakukan inventarisasi lebih dulu, mengonversi kedua, dan memverifikasi ketiga, dan ketiga tahap itu harus saling berbagi informasi agar semuanya bisa dipercaya

Merakit workbench itu di Delphi atau C++Builder berarti merangkai bersama empat kemampuan HotXLS, yang tidak satu pun dari keempatnya membutuhkan Excel terinstal di mana pun dalam pipeline. Ada dua mesin native, sebuah facade BIFF8 untuk .xls dan sebuah facade OOXML untuk .xlsx dan .ods. Ada panggilan probing yang murah, yang membaca metadata tanpa mem-parse seluruh file. Ada counter audit per-sheet yang memberitahu Anda apa yang sebenarnya dimiliki sebuah workbook. Dan ada sebuah matriks konversi dengan profil fidelitas terdokumentasi untuk setiap jalurnya. Pekerjaannya terletak pada mengetahui di mana masing-masing dari itu punya sisi tajam, karena semuanya memang punya, dan sisi-sisi tajam itulah yang mengubah sebuah batch semalam yang bersih menjadi insiden Senin pagi

Probe Sebelum Memuat: Nama Sheet dan Deteksi Enkripsi

Membuka sebuah workbook 200 MB hanya untuk menemukan bahwa itu terenkripsi membuang beberapa menit per file, dan jika dikalikan di seluruh arsip yang besar, itu membuang berhari-hari. Kedua facade sama-sama mengekspos GetSheetNames, yang membaca metadata sheet tanpa mengisi workbook-nya. Implementasi BIFF hanya memindai record BoundSheet di bagian depan stream; implementasi OOXML hanya membaca workbook.xml di dalam zip. Di sampingnya, CanReadEncrypted mendeteksi sebuah kontainer enkripsi tanpa mencoba dekripsi:

var
  Probe: TXLSXWorkbook;
  Names: TStringList;
begin
  Names := TStringList.Create;
  Probe := TXLSXWorkbook.Create;
  try
    if Probe.CanReadEncrypted(FileName) then
    begin
      Writeln(FileName + ': encrypted container - route to manual handling');
      Exit;
    end;
    if Probe.GetSheetNames(FileName, Names) <= 0 then
      Writeln(FileName + ': unreadable - quarantine')
    else
      Writeln(Format('%s: %d sheet(s), first "%s"',
        [FileName, Names.Count, Names[0]]));
  finally
    Probe.Free;
    Names.Free;
  end;
end;

Dua detail operasional membuat loop ini murah. GetSheetNames tidak mereset maupun mengisi instance workbook, sehingga satu objek probe saja bisa mengklasifikasikan ribuan file tanpa perlu dibuat ulang. Dan versi facade XLS dari panggilan yang sama juga memahami paket .xlsx, yang menjadikannya sebuah probe tunggal yang praktis ketika ekstensi file tidak bisa dipercaya, sebagaimana jarang bisa dipercaya di arsip setua itu. Triase sebelum pemuatan layak dibahas tersendiri; mekanismenya soal inspeksi ringan ada di artikel kami tentang listing sheet dan inspeksi workbook ringan

Menghitung Apa yang Sebenarnya Dimiliki Sebuah Workbook

Begitu sebuah file lolos triase, tahap audit menentukan jalur konversinya. Facade XLSX mengekspos sebuah counter untuk setiap keluarga fitur yang berpengaruh pada keputusan fidelitas: sel gabungan, chart, gambar, conditional format, validasi data, tabel, hyperlink, dan komentar, ditambah flag tingkat workbook untuk makro, proteksi, dan format sumber. Jalur konversi untuk sebuah file hampir sepenuhnya bergantung pada mana saja dari daftar ini yang kembali bukan nol

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then Exit;
    for I := 0 to Book.Sheets.Count - 1 do
    begin
      Sheet := Book.Sheets[I];
      Writeln(Format('%s: cells=%d merges=%d charts=%d cf=%d dv=%d protected=%s',
        [Sheet.Name, Sheet.Cells.Count, Sheet.MergedCells.Count,
         Sheet.Charts.Count, Sheet.ConditionalFormats.Count,
         Sheet.DataValidations.Count, BoolToStr(Sheet.IsProtected, True)]));
    end;
    if Book.HasVbaProject then
      Writeln('  contains VBA project - macro policy applies');
    if Book.ExternalLinks.Count > 0 then
      Writeln(Format('  %d external link(s)', [Book.ExternalLinks.Count]));
  finally
    Book.Free;
  end;
end;

Baca Cells.Count dengan satu catatan di kepala. Penyimpanan sel bersifat sparse, sehingga angka itu menghitung sel yang sudah diinstansiasi, bukan area persegi dari used range. Sebuah sheet dengan satu nilai di A1 dan satu lagi di ZZ9999 melaporkan dua sel, bukan sekitar sejuta yang berada di antara keduanya. Pemindaian yang setara di sisi BIFF menggunakan batas UsedRange bersama ForEachCell, dan itu membawa kesalahan selisih satu yang menjebak hampir semua orang pada kali pertama: UsedRange.FirstRow dan saudara-saudaranya berbasis 0, sementara Cells.Item[Row, Col] berbasis 1. Sebuah traversal yang lupa menambahkan satu ke setiap batas akan mengaudit persegi yang salah dan tidak pernah memberitahukannya

Dua tuas memangkas biaya sebuah pass audit-only di atas file legacy yang besar. Menyetel _DisableGraphics menjadi true sebelum membuka sebuah .xls sepenuhnya melewati parsing lapisan gambar OfficeArt, yang menghemat waktu nyata pada workbook yang padat dengan shape. Tapi ini murni optimisasi read-only: menyimpan dari sebuah instance yang dibuka dengan cara itu akan membuang gambar-gambar yang tidak pernah di-parse-nya, sehingga flag ini hanya cocok pada jalur yang tidak akan pernah menulis kembali file itu. Ketika audit membutuhkan konten per-sel, bukan hanya angka, callback ForEachCell menyusuri sel-sel yang terisi secara langsung dan menghindari overhead Variant per-akses yang dibayar properti sel berindeks pada setiap pembacaan, yang cepat menumpuk di seluruh jutaan sel

Normalkan Kode Kembalian yang Tidak Konsisten Sejak Awal

Panggilan I/O HotXLS melaporkan error melalui hasil integer, bukan exception, dan konvensinya tidak seragam di seluruh API. Kebanyakan panggilan open dan save mengembalikan 1 saat berhasil dan -1 saat gagal. GetSheetNames mengembalikan jumlah sheet, atau -1 dengan list yang dikosongkan. SaveAsHTML pada XLSX memecah pola itu lagi dan mengembalikan 0 untuk sukses, -1 untuk index sheet di luar jangkauan. Sebuah workbench yang menguji = 1 di mana-mana akan diam-diam salah mengklasifikasikan panggilan yang menandakan sukses dengan cara lain, dan yang menguji <> -1 akan menelan begitu saja yang gagal dengan kode berbeda

Aturan yang tetap bertahan setelah bersentuhan dengan seluruh API ternyata lebih sempit daripada kelihatannya: perlakukan <= 0 sebagai kegagalan untuk panggilan yang mengembalikan count, periksa nilai sukses yang terdokumentasi untuk setiap rutin save yang sungguh Anda pakai, dan taruh keduanya di balik satu fungsi pemeriksa hasil yang kecil sehingga konvensinya hidup persis di satu tempat saja. Pipeline batch jauh lebih sering gagal karena tumpukan kode kembalian yang tidak diperiksa secara perlahan daripada karena bug parser yang eksotis, dan biaya dari kesalahan ini baru terasa empat puluh ribu file kemudian, ketika tidak seorang pun ingat konversi mana yang sebenarnya berhasil

Matriks Konversi dan di Mana Setiap Jalur Kehilangan Data

Kedua facade membagi pekerjaan konversi di antara mereka. TXLSXWorkbook membuka XLSX, ODS, dan CSV, dan menyimpan XLSX, ODS, CSV, HTML, RTF, dan XLSX terenkripsi AES. TXLSWorkbook membuka dan menyimpan BIFF, serta mengekspor HTML, RTF, dan CSV. Hal yang berguna adalah setiap jalur datang dengan profil fidelitas yang terdokumentasi, bukan janji kebenaran yang samar-samar, sehingga Anda bisa memutuskan lebih dulu jalur mana yang aman untuk file mana

Ekspor CSV menulis UTF-8 dengan BOM, akhir baris CRLF, dan quoting RFC 4180. Yang tidak dilakukannya adalah mengevaluasi formula: sebuah sel yang memegang =SUM(...) diekspor sebagai teks formula literal, sehingga sebuah sheet berisi formula berubah menjadi sheet berisi string kecuali Anda menghitung nilainya lebih dulu. Ekspor HTML menghasilkan satu tabel tunggal, dengan colspan dan rowspan menggantikan sel gabungan dan style dasar yang di-inline. Ekspor RTF punya batasan yang lebih tajam: ia tidak bisa merentangkan sel gabungan lintas kolom, sehingga sel-sel kelanjutan dari sebuah gabungan keluar kosong. Import ODS sengaja dibuat ringan, menurut dokumentasi pustaka itu sendiri. Nilai skalar dan hasil formula yang di-cache ikut terbawa; style, ekspresi formula ODF yang hidup, dan gambar tidak. Itu menjadi masalah begitu arsipnya berisi file OpenDocument sungguhan yang diatur oleh OASIS ODF 1.3, di mana apa pun yang mendekati konversi yang setia secara visual membutuhkan lebih dari yang dibangun untuk dibawa oleh jalur import ini, dan tahap audit itulah yang memberitahu Anda file-file itu ada sebelum batch tersebut diam-diam meratakannya

SaveXLSWorkbookAsXLSX Adalah Jembatan Data, Bukan Jembatan Layout

Facade BIFF tidak bisa menulis OOXML secara langsung, sehingga penyeberangan dari .xls ke .xlsx berjalan melalui fungsi SaveXLSWorkbookAsXLSX di unit lxXlsxExport. Fidelitas jembatan itu layak dinyatakan dengan jelas, karena namanya menyiratkan lebih dari yang sebenarnya dilakukannya. Ia menyalin nilai, formula, format angka, warna fill, atribut font inti, lebar kolom, dan pengaturan tampilan seperti gridline. Ia tidak menyalin border, rentang gabungan, komentar, chart, atau conditional format. Untuk normalisasi tingkat data, di mana sistem hilir akan mem-parse hasilnya dan tidak ada yang memperhatikan pemformatannya, itu sudah cukup persis dan tidak ada yang hilang yang dibutuhkan siapa pun. Untuk sebuah laporan dewan direksi yang diformat dan ditujukan untuk dibaca manusia, itu tidak cukup, dan di sinilah tepatnya counter audit menemukan tempatnya: sebuah file yang ditandai audit sebagai membawa chart dan conditional format seharusnya diarahkan ke antrean manual, bukan melalui jembatan yang akan membuang keduanya tanpa sepatah kata pun

var
  Legacy: IXLSWorkbook;        // referensi interface: jangan Free
  Modern: TXLSXWorkbook;
begin
  if SameText(ExtractFileExt(FileName), '.xls') then
  begin
    Legacy := TXLSWorkbook.Create;
    if Legacy.Open(FileName) <= 0 then Exit;
    if SaveXLSWorkbookAsXLSX(Legacy,
         ChangeFileExt(FileName, '.xlsx')) <= 0 then
      Writeln('bridge failed: ' + FileName);
  end
  else
  begin
    Modern := TXLSXWorkbook.Create;
    try
      Modern.StreamingWrite := True;     // stream XML sheet ke dalam zip
      if Modern.Open(FileName) = 1 then
        Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
    finally
      Modern.Free;
    end;
  end;
end;

Loop di atas juga menunjukkan tuas throughput di sisi OOXML. Menyetel StreamingWrite menjadi true men-stream XML worksheet langsung ke dalam paket output, alih-alih menampungnya sebagai satu string raksasa di memori, dan itulah perbedaan antara sebuah run yang nyaman dan crash out-of-memory begitu file mencapai ratusan ribu baris. Perilaku ukuran dan memori untuk mode itu dibahas tersendiri di artikel kami tentang streaming write untuk job batch server. Satu properti lagi penting untuk sebuah batch yang ingin memanfaatkan setiap core: tidak ada satu pun dari kedua facade yang thread-safe, tetapi keduanya juga tidak berbagi state global, sehingga pola yang didukung untuk konversi paralel adalah satu instance workbook per worker thread, tanpa locking di antara mereka

File Berpassword, dan Apa yang Harus Dilakukan dengannya

File terkunci dalam arsip terbagi dengan bersih berdasarkan format, dan pembagian itulah yang menentukan ke mana mereka pergi. Enkripsi .xls lawas, baik RC4, RC4 lewat CryptoAPI, maupun obfuskasi XOR lama, bisa dibaca: berikan password ke Open dan file itu terkonversi seperti file lainnya. Paket .xlsx terenkripsi adalah cerita yang berbeda. HotXLS mendeteksinya dengan CanReadEncrypted tetapi tidak bisa mendekripsinya, sehingga satu-satunya langkah yang jujur adalah mengarahkannya ke sebuah antrean di mana seorang manusia membuka dan menyimpan ulang masing-masing di Excel sebelum ia bergabung kembali dengan pipeline. Asimetri itu layak dirancang sejak awal, karena file XLSX terenkripsi itulah yang paling mungkin menjadi rekaman yang sungguh-sungguh dipedulikan seseorang

Menutup Loop dengan Verifikasi

Tahap ketiga adalah yang paling sering dilewatkan, dan melewatkannya itulah yang mengubah sebuah konversi massal menjadi liabilitas. Tidak ada satu pun jalur penyimpanan di HotXLS yang mengevaluasi formula. Excel merekalkulasi ketika membuka sebuah file, sehingga konversi XLSX-ke-XLSX tetap benar, tetapi target CSV menerima teks formula apa adanya kecuali pipeline lebih dulu menjalankan Calculate pada sel-selnya dan menuliskan hasilnya kembali. Mengetahui hal itu lebih dulu adalah perbedaan antara sebuah CSV penuh angka dan sebuah CSV penuh string =SUM(...) yang tidak disadari siapa pun sampai sebuah import di hilir tersedak olehnya

Verifikasi itu sendiri cukup murah sehingga tidak ada alasan untuk melewatkannya. Buka kembali setiap file yang sudah dikonversi dengan pustaka yang sama, jalankan ulang counter auditnya, dan bandingkan dengan angka pra-konversi yang sudah dicatat oleh tahap inventarisasi. Jumlah sheet yang berkurang, jumlah chart yang jatuh ke nol padahal sumbernya punya tiga, jumlah sel yang anjlok drastis: masing-masing adalah kehilangan senyap yang tertangkap dengan biaya satu kali open tambahan. Periksa spot-check sebuah sampel secara visual di Excel atau LibreOffice di atas itu semua, dan kombinasi ini menangkap sebagian besar kerusakan konversi sebelum dikirim. Inilah keseluruhan alasan mengapa tahap inventarisasi memberi makan tahap verifikasi. Tanpa angka sebelum, angka sesudah tidak membuktikan apa-apa

Sebuah workbench audit-first mengubah konversi massal yang berisiko menjadi proses yang terukur dengan jalur karantina untuk file yang tidak bisa lolos dengan bersih. Semua panggilan probing, counting, dan konversi yang ditunjukkan di sini adalah bagian dari HotXLS Delphi Component, yang menjalankannya secara native in-process tanpa otomatisasi Excel