Validasi Data, AutoFilter, dan Tabel Worksheet di Delphi dengan HotXLS

Tiga fitur dalam HotXLS berbagi satu worksheet tetapi bekerja pada objek yang sama sekali berbeda, dan masalahnya dimulai ketika Anda mengira ketiganya melakukan hal yang serupa. Data validation melekatkan sebuah rule pada sebuah range yang membatasi apa yang boleh diketik seorang pengguna ke dalamnya. Sebuah AutoFilter melekatkan sebuah definisi kriteria tersimpan pada sebuah region dan mengubah baris mana yang ditampilkan seorang penampil. Sebuah table membungkus sebuah range dalam sebuah struktur bernama dan bertipe dengan styling berpita. Yang satu membatasi input, yang satu mencatat sebuah tampilan, yang satu lagi memaksakan sebuah skema. Tidak satu pun dari ketiganya memindahkan satu pun nilai cell dengan sendirinya, dan AutoFilter khususnya sering mengecoh orang, karena namanya menyiratkan sebuah aksi padahal ia hanya menyimpan sebuah definisi. Mengetahui objek mana yang disentuh setiap pemanggilan, dan kapan efeknya benar-benar terwujud, adalah yang membedakan sebuah workbook yang berperilaku sama di Excel seperti saat diuji dari yang diam-diam menyimpang

AutoFilter menyimpan sebuah definisi, bukan memangkas baris

Sebuah AutoFilter dalam file yang disimpan adalah sebuah rekaman kriteria. Penyembunyian baris terjadi belakangan, ketika Excel membuka workbook dan mengevaluasi kriteria tersebut terhadap data. HotXLS menulis rekaman itu dengan setia dan tidak memangkas apa pun: setiap baris yang Anda filter tetap ada secara fisik dalam file. Sebuah pipeline yang menerapkan sebuah filter untuk membuang order yang ditolak lalu membaca kembali workbook itu akan melihat semuanya, termasuk yang ditolak, dan kodenya benar menurut API sekalipun salah menurut model mental penulisnya. Pada worksheet XLSX, SetAutoFilter mendeklarasikan region yang difilter dan AddAutoFilterColumn melekatkan kriteria pada satu kolom di dalamnya. Ketika kode di sisi server membutuhkan hasil sesungguhnya, untuk sebuah jumlah baris dalam sebuah ringkasan atau untuk meneruskan hanya baris yang cocok, library ini mengevaluasi kriteria itu untuk Anda alih-alih berpura-pura file itu sudah berubah:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, Visible: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    // Column id 3 = kolom keempat DI DALAM filter range (offset berbasis 0)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible sekarang cocok dengan apa yang akan ditampilkan Excel setelah file dibuka

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

AutoFilterRowVisible menjawab per baris, dan PreviewAutoFilterRows menelusuri seluruh region lewat sebuah callback ketika Anda butuh kumpulan yang cocok dalam satu lintasan. Ada satu kasus di mana keduanya bukan jawaban yang tepat: jika persyaratannya adalah baris yang dikecualikan sama sekali tidak boleh ada dalam file, sebuah pemotongan privasi, bukan sekadar sebuah tampilan, hapus saja baris-baris itu langsung. Sebuah filter adalah alat yang salah di sana, karena penerima mana pun bisa menghapusnya dengan satu klik dan data yang ingin Anda sembunyikan kembali tampil di layar

Column id adalah sebuah offset, bukan nomor kolom

Komentar dalam cuplikan di atas menandai jebakan yang paling banyak menghabiskan waktu debugging pada API ini. AddAutoFilterColumn mengidentifikasi targetnya berdasarkan posisi berbasis 0 di dalam filter range, bukan berdasarkan kolom worksheet. Untuk sebuah filter pada A1:E500, kedua sistem penomoran itu kebetulan hanya berbeda satu angka, yang justru merupakan jenis kesalahan hampir-lolos yang bertahan lewat pengujian cepat dan pecah begitu seorang kolega memfilter kolom yang berbeda. Untuk sebuah filter yang dimulai pada kolom C, id 0 berarti kolom C, dan ketidakcocokannya menjadi jelas dengan cepat. Ketika filter range dihitung saat runtime, turunkan column id dari variabel yang sama yang membangun string range tersebut, jangan pernah dari sebuah konstanta kolom worksheet. Setiap kolom menerima sebuah kondisi kedua lewat overload yang menerima dua operator, dua kriteria, dan sebuah penghubung and/or, yang mencerminkan dialog custom filter milik Excel. Facade XLS mencakup wilayah yang sama dengan SetAutoFilter ditambah ApplyAutoFilter, yang parameter kriteria dan operatornya mengikuti konvensi gaya COM yang lebih tua dan menomori field mulai dari 1. Berpindah facade berarti berpindah basis indeks, sehingga lokasi pemanggilannya layak diberi komentar yang menyebutkan mana yang sedang dipakai

Rule validation adalah kontrak yang menjadi acuan edit pengguna Anda

Dari ketiga fitur, validation adalah satu-satunya yang secara aktif membatasi input di masa depan, dan ia paling layak mendapat perhatian desain dalam workbook yang dikirim keluar untuk diisi lalu kembali untuk diproses. Varian list menanggung sebagian besar dari pekerjaan itu:

var
  Idx: Integer;
begin
  Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
  Sheet.DataValidations[Idx].SetPrompt('Status',
    'Pick one of the listed states');
  Sheet.DataValidations[Idx].SetError('Invalid status',
    'Type or paste only listed values', xlsxDvErrStop);
  Sheet.DataValidations[Idx].AllowBlank := False;

  // Kuantitas: bilangan bulat, nol atau lebih
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Di luar list dan bilangan bulat, keluarga yang sama mencakup desimal, tanggal, waktu, panjang teks, dan formula bebas-bentuk lewat AddCustomValidation, dan AddDataValidation yang generik mengekspos seluruh matriks tipe-dan-operator untuk rule builder yang digerakkan oleh konfigurasi. Gaya error-nya lebih penting daripada yang tersirat dari namanya. xlsxDvErrStop menolak input yang salah secara langsung; gaya warning dan information membiarkan nilainya lolos setelah satu klik. Pilih per kolom berdasarkan apakah kode yang membaca kembali workbook itu bisa mentoleransi sebuah nilai di luar rule. Dua batasan layak masuk ke dalam teks prompt atau README yang Anda kirim bersama file itu. Validation di Excel menjaga proses mengetik, tetapi menempelkan sebuah blok di atas range yang divalidasi lolos begitu saja dari rule, sehingga kode apa pun yang membaca kembali data itu harus memvalidasi ulang alih-alih memercayai cell-nya begitu saja. Dan sebuah rule mencakup range harfiah yang Anda berikan kepadanya, yang berarti melekatkan validation sebelum Anda tahu jumlah baris akhir meninggalkan ekor yang ditambahkan tanpa penjagaan. Tulis datanya terlebih dahulu, baru sesuaikan ukuran rule dengan jangkauan sesungguhnya

Facade lawas menawarkan keluarga rule yang sama dengan satu perbedaan ergonomis. Creator di sisi XLS, yaitu AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation, dan AddCustomValidation, mengembalikan objek TDataValidation secara langsung alih-alih sebuah indeks, sehingga konfigurasi prompt dan error dirangkai dari referensi yang dikembalikan itu, bukan dari sebuah lookup. Enumerasi operatornya (xlsDvBetween, xlsDvGreaterThan, dan sisanya) mencerminkan kumpulan XLSX, sehingga kode pembangun rule bisa dipindahkan antar facade, kecuali untuk perbedaan gaya nilai balik itu. Teks prompt itu sendiri layak dipikirkan sama seriusnya dengan rule-nya. Sebuah dropdown yang menolak input dengan kotak error kosong mengajarkan pengguna untuk mengirim email ke tim IT; yang menyebutkan status yang sah mengajarkan mereka untuk memperbaiki cell-nya dan melanjutkan

Satu pembalikan polaritas yang diserap library ini untuk Anda

Siapa pun yang pernah membaca langsung XML validation OOXML pernah bertemu dengan atribut showDropDown yang terbalik: dalam ISO/IEC 29500 sebuah nilai true berarti "sembunyikan panah dropdown," kebalikan dari yang tersirat dari namanya. HotXLS membalikkan ini secara internal, sehingga properti ShowDropDown pada sebuah rule validation berarti persis seperti yang tertulis, dengan true menampilkan dropdown-nya. Satu-satunya cara untuk terjebak adalah mencampur dua level kebenaran, menyetel properti itu dari kode sementara seorang kolega mengaudit XML yang tersimpan dan "membetulkan" atribut yang menurutnya tampak terbalik. Tentukan apakah properti atau XML mentahnya yang otoritatif untuk tooling review, dan tuliskan pembalikan ini di tempat keputusan itu berada

Table memberi sebuah range sebuah skema dan sebuah nama

Sebuah table worksheet, ListObject dalam istilah Excel, membungkus sebuah range dengan sebuah nama, kolom bertipe, styling berpita, dan dukungan structured-reference. Ini adalah fitur yang membuat sebuah workbook yang dihasilkan terasa rampung begitu pengguna mulai mengurutkan dan memperluasnya. Pembuatannya simetris di kedua facade, dengan AddTable menerima sebuah nama, sebuah range, dan sebuah daftar kolom:

var
  Cols: TStringList;
begin
  Cols := TStringList.Create;
  try
    Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
    Sheet.AddTable('Orders', 'A1:E500', Cols);
  finally
    Cols.Free;
  end;
end;

Pada sisi XLSX, objek table yang dihasilkan mengekspos StyleName (keluarga bawaan TableStyleMedium2 dan saudara-saudaranya), toggle garis-belang, dan sebuah flag totals-row, sehingga menerapkan styling standar perusahaan cukup dengan sebuah penetapan properti, bukan sebuah lintasan formatting manual. Dalam file .xls lawas, pemanggilan yang sama menulis rekaman table BIFF8, dan facade ini juga menawarkan AddPivotTable untuk tampilan ringkasan yang dibangun dari field row, column, dan data, sebuah pengingat bahwa "table" dalam format yang lebih tua ini menjangkau lebih jauh daripada ListObject OOXML. Beri nama table seperti Anda menamai view database. Kode downstream yang membaca Orders[Amount] lewat structured reference tetap bertahan melewati pengurutan ulang kolom yang justru merusak kode berbasis posisi

Dua konvensi menghemat pembersihan belakangan. Excel mewajibkan nama table unik di seluruh workbook, sehingga sebuah generator yang menghasilkan satu sheet per region membutuhkan sebuah skema seperti Orders_EMEA alih-alih memakai ulang Orders. Sebuah duplikat tidak gagal saat penulisan; ia baru muncul sebagai sebuah dialog repair ketika pengguna membuka file, yang merupakan tempat paling buruk untuk menemukannya. Konvensi lainnya menyangkut totals row: ketika diaktifkan, ia berada tepat di bawah data range, sehingga kode apa pun yang belakangan menambahkan baris lewat "baris terakhir yang terpakai plus satu" justru menulis ke dalam pita totals, bukan setelahnya. Lacak jangkauan data secara terpisah dari jangkauan table, dan penambahan baris akan mendarat tepat di tempat yang Anda harapkan

Ketiga fitur ini berpadu secara alami dalam deliverable data-entry. Sebuah table mendefinisikan region yang bisa diedit, validation membatasi kolom-kolom yang diketik pengguna, dan sebuah filter yang sudah diset lebih dulu menghemat beberapa klik pertama bagi penerima. Ada argumen yang cukup masuk akal untuk mengirimkan sebuah filter yang sudah diterapkan sehingga workbook terbuka terfokus pada baris-baris yang penting, selama Anda ingat bahwa baris yang dikecualikan tetap ada dalam file dan seorang penerima yang penasaran bisa menampilkannya kembali. Memasukkan hasil query ke dalam sheet secara efisien, separuh hulu dari pipeline ini, dibahas dalam mengekspor hasil database ke Excel dari Delphi, dan workbook di mana formula meringkas data yang tervalidasi diuntungkan oleh defined names untuk referensi lintas-sheet yang stabil

Validation, filter, dan table adalah perbedaan antara mengirimkan sekadar grid berisi nilai dan mengirimkan sebuah aplikasi kecil. Referensi lengkap untuk rule, filter, dan table tersedia pada halaman produk HotXLS Delphi Component