Xác thực dữ liệu, AutoFilter và bảng tính trong Delphi bằng HotXLS

Ba tính năng trong HotXLS cùng chia sẻ một worksheet nhưng lại thao tác trên những đối tượng hoàn toàn khác nhau, và rắc rối bắt đầu khi bạn giả định chúng làm những việc giống nhau. Data validation gắn một quy tắc vào một range để ràng buộc những gì người dùng có thể gõ vào đó. Một AutoFilter gắn một định nghĩa tiêu chí đã lưu vào một vùng và thay đổi những hàng nào người xem nhìn thấy. Một table bọc một range trong một cấu trúc có tên, có kiểu, với style dải màu xen kẽ. Cái này ràng buộc đầu vào, cái kia ghi lại một view, cái còn lại áp đặt một schema. Không cái nào trong số chúng tự mình di chuyển dù chỉ một giá trị ô, và AutoFilter đặc biệt gây nhầm lẫn cho người dùng, bởi vì cái tên gợi ý một hành động trong khi nó chỉ lưu một định nghĩa. Biết đối tượng nào mỗi lệnh gọi chạm tới, và khi nào hiệu ứng thực sự hiện thực hóa, chính là điều phân biệt một workbook hành xử giống hệt trong Excel như nó đã hành xử trong các bài test của bạn, với một workbook âm thầm lệch hướng

AutoFilter lưu một định nghĩa, nó không cắt bỏ hàng

Một AutoFilter trong một file đã lưu là một bản ghi tiêu chí. Việc ẩn hàng xảy ra sau đó, khi Excel mở workbook và đánh giá tiêu chí đối chiếu với dữ liệu. HotXLS ghi bản ghi đó một cách trung thực và không cắt bỏ gì cả: mọi hàng bạn đã lọc vẫn hiện diện về mặt vật lý trong file. Một pipeline áp dụng một filter để loại bỏ các đơn hàng bị từ chối rồi sau đó đọc lại workbook sẽ thấy tất cả chúng, kể cả những đơn bị từ chối, và mã đó đúng theo API trong khi sai theo mô hình tư duy của người viết. Trên worksheet XLSX, SetAutoFilter khai báo vùng đã lọc và AddAutoFilterColumn gắn tiêu chí vào một cột của vùng đó. Khi mã phía server cần kết quả thực tế, chẳng hạn để đếm số hàng trong một bản tóm tắt hoặc để chuyển tiếp chỉ những hàng khớp, thư viện đánh giá tiêu chí giúp bạn thay vì giả vờ rằng file đã thay đổi:

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 = cột thứ tư BÊN TRONG filter range (offset đánh số từ 0)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible giờ khớp với những gì Excel sẽ hiển thị sau khi mở file

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

AutoFilterRowVisible trả lời theo từng hàng, còn PreviewAutoFilterRows duyệt qua toàn bộ vùng thông qua một callback khi bạn cần tập hợp các hàng khớp trong một lượt. Có một trường hợp mà không cái nào trong hai cái đó là câu trả lời đúng: nếu yêu cầu là các hàng bị loại phải hoàn toàn không tồn tại trong file, một phép cắt vì quyền riêng tư chứ không phải một view, hãy xóa hẳn các hàng đó. Một filter là công cụ sai ở đây, bởi vì bất kỳ người nhận nào cũng có thể xóa nó chỉ bằng một cú nhấp chuột và dữ liệu bạn định giữ kín sẽ trở lại trên màn hình

Column id là một offset, không phải một số cột

Comment trong đoạn mã ở trên đánh dấu cái bẫy tốn nhiều thời gian debug nhất trong API này. AddAutoFilterColumn nhận diện đích của nó bằng vị trí đánh số từ 0 bên trong filter range, chứ không phải bằng cột worksheet. Với một filter trên A1:E500, hai hệ đánh số này tình cờ lệch nhau đúng một, đó chính xác là kiểu suýt-trúng sống sót qua một bài test nhanh rồi hỏng ngay khi một đồng nghiệp lọc một cột khác. Với một filter bắt đầu từ cột C, id 0 nghĩa là cột C, và sự lệch pha trở nên rõ ràng nhanh chóng. Khi filter range được tính lúc runtime, hãy suy ra column id từ đúng biến đã dựng nên chuỗi range đó, không bao giờ từ một hằng số cột worksheet. Mỗi cột chấp nhận một điều kiện thứ hai thông qua overload nhận hai operator, hai tiêu chí, và một liên từ and/or, phản chiếu hộp thoại custom filter của Excel. Lớp giao diện XLS bao phủ cùng phạm vi đó với SetAutoFilter cộng ApplyAutoFilter, mà tham số tiêu chí và operator của nó theo các quy ước kiểu COM cũ hơn và đánh số trường bắt đầu từ 1. Chuyển lớp giao diện đồng nghĩa với chuyển cơ số đánh chỉ số, nên nơi gọi hàm xứng đáng có một comment nói rõ đang dùng cái nào

Quy tắc validation là hợp đồng mà người dùng của bạn chỉnh sửa theo

Trong ba tính năng, validation là tính năng duy nhất chủ động ràng buộc đầu vào trong tương lai, và nó xứng đáng nhận nhiều sự chú ý thiết kế nhất trong những workbook được gửi đi để hoàn thiện rồi quay về để xử lý. Biến thể list gánh phần lớn công việc đó:

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;

  // Số lượng: số nguyên, từ 0 trở lên
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Ngoài list và số nguyên, cùng họ hàng đó còn bao phủ số thập phân, ngày tháng, thời gian, độ dài văn bản, và công thức tự do thông qua AddCustomValidation, còn AddDataValidation tổng quát lộ ra toàn bộ ma trận kiểu-và-operator cho những bộ dựng quy tắc điều khiển bởi cấu hình. Kiểu lỗi quan trọng hơn cái tên của nó gợi ý. xlsxDvErrStop từ chối thẳng đầu vào sai; các kiểu warning và information cho giá trị đi qua sau một cú nhấp duy nhất. Hãy chọn theo từng cột dựa trên việc mã đọc lại workbook có thể chịu được một giá trị nằm ngoài quy tắc hay không. Hai ranh giới đáng được đưa vào văn bản prompt hoặc README bạn kèm theo file. Validation trong Excel bảo vệ việc gõ phím, nhưng dán một khối đè lên một range đã validate sẽ lách qua quy tắc, nên bất kỳ mã nào đọc lại dữ liệu đều phải validate lại thay vì tin tưởng các ô. Và một quy tắc bao phủ đúng range chữ bạn đưa cho nó, nghĩa là gắn validation trước khi bạn biết số hàng cuối cùng sẽ để lại phần đuôi được thêm vào sau không được bảo vệ. Hãy ghi dữ liệu trước, rồi mới định cỡ các quy tắc theo phạm vi thực tế

Lớp giao diện kế thừa cũ cung cấp cùng những họ quy tắc đó với một khác biệt về sự tiện dụng. Các hàm tạo ở phía XLS, cụ thể là AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation, và AddCustomValidation, trả về trực tiếp đối tượng TDataValidation thay vì một chỉ số, nên việc cấu hình prompt và error nối chuỗi ngay trên tham chiếu trả về thay vì phải tra cứu. Enum operator (xlsDvBetween, xlsDvGreaterThan, và phần còn lại) phản chiếu tập của XLSX, nên mã dựng quy tắc có thể chuyển đổi giữa hai lớp giao diện ngoại trừ khác biệt về kiểu giá trị trả về đó. Bản thân văn bản prompt xứng đáng được suy nghĩ kỹ lưỡng không kém gì quy tắc. Một dropdown từ chối đầu vào bằng một hộp lỗi trống rỗng dạy người dùng gửi email cho IT; một dropdown nêu tên các trạng thái hợp lệ dạy họ sửa ô rồi tiếp tục

Một lần lật cực tính mà thư viện đã hấp thụ giúp bạn

Bất kỳ ai từng đọc tay XML validation của OOXML đều đã gặp thuộc tính showDropDown bị đảo ngược: trong ISO/IEC 29500, một giá trị true nghĩa là "ẩn mũi tên dropdown", ngược hẳn với những gì cái tên gợi ý. HotXLS đảo ngược điều này ở bên trong, nên thuộc tính ShowDropDown trên một quy tắc validation có nghĩa đúng như tên gọi của nó, với true nghĩa là hiển thị dropdown. Cách duy nhất để bị vạ lây là trộn lẫn các tầng sự thật, đặt thuộc tính từ mã trong khi một đồng nghiệp kiểm tra XML đã lưu và "sửa lại" thuộc tính trông có vẻ ngược đối với họ. Hãy quyết định xem thuộc tính hay XML thô mới là nguồn có thẩm quyền cho công cụ review, và ghi lại sự đảo ngược đó ở nơi quyết định đó được lưu giữ

Table cho một range một schema và một cái tên

Một table trên worksheet, tức ListObject theo thuật ngữ Excel, bọc một range trong một cái tên, các cột có kiểu, style dải màu xen kẽ, và hỗ trợ structured-reference. Đây là tính năng khiến một workbook được tạo ra cảm giác hoàn thiện ngay khi người dùng bắt đầu sắp xếp và mở rộng nó. Việc tạo đối xứng nhau giữa hai lớp giao diện, với AddTable nhận một tên, một range, và một danh sách cột:

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;

Ở phía XLSX, đối tượng table kết quả lộ ra StyleName (họ style có sẵn TableStyleMedium2 và các anh em của nó), các nút bật/tắt sọc màu, và một cờ hàng tổng, nên việc áp dụng style theo chuẩn công ty chỉ là một phép gán thuộc tính thay vì một lượt định dạng thủ công. Trong các file .xls kiểu cũ, cùng lệnh gọi đó ghi ra các bản ghi table BIFF8, và lớp giao diện này còn cung cấp AddPivotTable cho các view tóm tắt được dựng từ các trường hàng, cột, và dữ liệu, một lời nhắc rằng "table" trong định dạng cũ hơn vươn xa hơn cả ListObject của OOXML. Hãy đặt tên table theo cách bạn đặt tên cho các view cơ sở dữ liệu. Mã downstream đọc Orders[Amount] bằng structured reference sẽ sống sót qua việc sắp xếp lại cột, điều làm hỏng mã dựa trên vị trí

Hai quy ước sau đây tiết kiệm công dọn dẹp về sau. Excel yêu cầu tên table phải duy nhất trên toàn bộ workbook, nên một trình tạo phát ra một sheet cho mỗi vùng cần một lược đồ đặt tên như Orders_EMEA thay vì tái sử dụng Orders. Một tên trùng lặp không thất bại ngay lúc ghi; nó chỉ nổi lên thành một hộp thoại sửa chữa khi người dùng mở file, đó là nơi tồi tệ nhất để phát hiện ra nó. Quy ước còn lại liên quan tới hàng tổng: khi được bật, nó nằm ngay bên dưới data range, nên bất kỳ mã nào sau này thêm hàng theo kiểu "hàng-đã-dùng-cuối-cùng-cộng-một" sẽ ghi vào dải hàng tổng đó thay vì ghi sau nó. Hãy theo dõi phạm vi dữ liệu tách biệt khỏi phạm vi table và các lượt thêm hàng sẽ đáp đúng nơi bạn mong đợi

Ba tính năng này kết hợp tự nhiên trong các sản phẩm giao nộp dạng nhập liệu. Một table định nghĩa vùng có thể chỉnh sửa, validation ràng buộc các cột người dùng gõ vào, và một filter đã đặt sẵn tiết kiệm cho người nhận vài cú nhấp chuột đầu tiên. Có một lập luận hợp lý cho việc giao một workbook với filter đã áp dụng sẵn để nó mở lên tập trung vào những hàng quan trọng, miễn là bạn nhớ rằng các hàng bị loại vẫn còn trong file và một người nhận tò mò có thể mở lộ chúng ra. Việc đưa kết quả truy vấn vào sheet một cách hiệu quả, nửa đầu của pipeline này, được nói tới trong xuất kết quả cơ sở dữ liệu ra Excel từ Delphi, và những workbook có công thức tóm tắt dữ liệu đã validate hưởng lợi từ defined names cho tham chiếu liên trang tính ổn định

Validation, filter, và table chính là sự khác biệt giữa việc giao một lưới giá trị và giao một ứng dụng nhỏ. Tài liệu tham khảo đầy đủ về rule, filter, và table nằm trên trang sản phẩm HotXLS Delphi Component