Xây dựng bàn làm việc kiểm toán và chuyển đổi bảng tính trong Delphi bằng HotXLS

Một job chuẩn hóa hàng loạt bảng tính là ba vấn đề khoác chung một tấm áo. Bạn có một kho lưu trữ với định dạng lẫn lộn: .xls từ thời BIFF, .xlsx hiện đại, rải rác vài tệp .ods từ một thử nghiệm LibreOffice nào đó, và một số tệp chẳng ai mở được vì mật khẩu đã ra đi cùng một nhân viên cũ. Mục tiêu là chuyển đổi mọi thứ sang XLSX và CSV. Phiên bản của job đó mà hầu hết mọi người viết ra là một vòng lặp mở từng tệp rồi lưu dưới một phần mở rộng mới, và nó chạy tốt cho tới khi có ai đó hỏi tệp nào đã mất chart, tệp nào rơi mất macro, hay tệp nào chưa từng mở được. Vòng lặp đó không có câu trả lời, vì chuyển đổi đơn thuần không lưu lại ghi chép gì cả. Một workbench thì có: nó kiểm kê trước, chuyển đổi sau, rồi xác minh cuối cùng, và ba giai đoạn đó phải chia sẻ thông tin cho nhau thì mọi thứ mới đáng tin cậy

Lắp ráp workbench đó trong Delphi hay C++Builder nghĩa là nối bốn khả năng của HotXLS lại với nhau, không cái nào trong số đó cần cài Excel ở bất cứ đâu trong pipeline. Có hai engine thuần: một facade BIFF8 cho .xls và một facade OOXML cho .xlsx và .ods. Có các lệnh dò rẻ tiền đọc metadata mà không cần phân tích cả tệp. Có các bộ đếm audit theo từng sheet cho biết một workbook thực sự chứa những gì. Và có một ma trận chuyển đổi với một hồ sơ độ trung thực đã được ghi lại rõ ràng cho từng tuyến. Công việc nằm ở chỗ biết mỗi thứ đó có cạnh sắc ở đâu, vì cái nào cũng có, và những cạnh sắc đó chính xác là những thứ biến một lô chạy ban đêm sạch sẽ thành một sự cố sáng thứ Hai

Dò trước khi nạp: tên sheet và phát hiện mã hóa

Mở một workbook 200 MB rồi mới phát hiện nó đã mã hóa lãng phí vài phút cho mỗi tệp, và nhân lên trên cả một kho lưu trữ lớn thì lãng phí cả nhiều ngày. Cả hai facade đều phơi ra GetSheetNames, đọc metadata của sheet mà không nạp dữ liệu vào workbook. Cách triển khai BIFF chỉ quét các bản ghi BoundSheet ở đầu luồng; cách triển khai OOXML chỉ đọc workbook.xml bên trong zip. Đi kèm với nó, CanReadEncrypted phát hiện một container đã mã hóa mà không cố thử giải mã:

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;

Hai chi tiết vận hành khiến vòng lặp này rẻ. GetSheetNames không reset cũng không nạp dữ liệu vào thể hiện workbook, nên một đối tượng dò duy nhất có thể phân loại hàng nghìn tệp mà không cần tạo lại. Và phiên bản của cùng lệnh gọi đó ở facade XLS cũng hiểu được các package .xlsx, khiến nó trở thành một lượt dò đơn lẻ tiện lợi khi phần mở rộng tệp không thể tin cậy được, điều hiếm khi xảy ra trong một kho lưu trữ cũ đến vậy. Sàng lọc trước khi nạp đáng được bàn riêng; cơ chế của việc kiểm tra nhẹ nằm trong bài viết của chúng tôi về liệt kê sheet và kiểm tra workbook nhẹ nhàng

Đếm những gì một workbook thực sự chứa

Khi một tệp vượt qua sàng lọc, lượt audit sẽ quyết định tuyến chuyển đổi của nó. Facade XLSX phơi ra một bộ đếm cho mỗi họ tính năng ảnh hưởng tới quyết định độ trung thực: ô merge, chart, hình ảnh, conditional format, data validation, table, hyperlink, và comment, cộng thêm các cờ ở cấp workbook cho macro, bảo vệ, và định dạng nguồn. Tuyến chuyển đổi cho một tệp phụ thuộc gần như hoàn toàn vào việc cái nào trong số đó trả về khác 0

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;

Hãy đọc Cells.Count với một lưu ý trong đầu. Kho ô là dạng thưa, nên con số đó đếm các ô đã được khởi tạo, không phải diện tích hình chữ nhật của vùng đã dùng. Một sheet có một giá trị ở A1 và một giá trị khác ở ZZ9999 báo cáo hai ô, chứ không phải cả triệu ô nằm giữa chúng. Lượt quét tương đương ở phía BIFF dùng biên UsedRange cùng với ForEachCell, và nó mang theo lỗi lệch-một khiến gần như ai cũng vấp phải ngay lần đầu: UsedRange.FirstRow và các thuộc tính anh em của nó bắt đầu từ 0, trong khi Cells.Item[Row, Col] bắt đầu từ 1. Một lượt duyệt quên cộng thêm một vào mỗi biên sẽ audit nhầm hình chữ nhật và không bao giờ báo cho bạn biết

Hai đòn bẩy cắt giảm chi phí của một lượt chỉ-audit trên các tệp cũ lớn. Đặt _DisableGraphics thành true trước khi mở một .xls bỏ qua hoàn toàn việc phân tích lớp drawing OfficeArt, giúp tiết kiệm thời gian thực sự trên các workbook dày đặc shape. Tuy vậy, đây hoàn toàn là một tối ưu chỉ-đọc: lưu từ một thể hiện đã mở theo cách đó sẽ làm rơi mất các drawing mà nó chưa từng phân tích, nên cờ này chỉ nên nằm trên những đường không bao giờ ghi tệp trở lại. Khi lượt audit cần nội dung theo từng ô thay vì số đếm, callback ForEachCell duyệt trực tiếp các ô đã có dữ liệu và né được chi phí Variant theo từng lần truy cập mà các thuộc tính ô có chỉ mục phải trả trên mỗi lượt đọc, một chi phí cộng dồn rất nhanh trên hàng triệu ô

Chuẩn hóa các mã trả về không nhất quán ngay từ đầu

Các lệnh gọi I/O của HotXLS báo lỗi qua kết quả kiểu số nguyên thay vì exception, và các quy ước đó không thống nhất trên toàn bộ API. Hầu hết các lệnh open và save trả về 1 khi thành công và -1 khi thất bại. GetSheetNames trả về số sheet, hoặc -1 với danh sách bị xóa sạch. SaveAsHTML của XLSX lại phá vỡ khuôn mẫu đó một lần nữa, trả về 0 khi thành công, -1 khi chỉ mục sheet nằm ngoài phạm vi. Một workbench kiểm tra = 1 ở khắp mọi nơi sẽ âm thầm phân loại sai những lệnh gọi báo hiệu thành công theo cách khác, còn một workbench kiểm tra <> -1 sẽ nuốt chửng những lệnh thất bại với một mã khác

Quy tắc đứng vững được khi va chạm với toàn bộ API lại hẹp hơn vẻ ngoài của nó: coi <= 0 là thất bại cho các lệnh gọi trả về số đếm, kiểm tra giá trị thành công đã được ghi lại cho từng routine save mà bạn thực sự dùng, rồi đặt cả hai đằng sau một hàm kiểm tra kết quả nhỏ duy nhất để quy ước đó chỉ sống ở đúng một chỗ. Các pipeline batch thất bại thường xuyên hơn nhiều vì một đống mã trả về không được kiểm tra chất dần lên chậm rãi, chứ không phải vì một lỗi parser kỳ lạ nào, và cái giá của việc làm sai điều này là bốn mươi nghìn tệp sau đó, khi chẳng ai còn nhớ lần chuyển đổi nào thực sự đã thành công

Ma trận chuyển đổi và mỗi con đường làm mất dữ liệu ở đâu

Hai facade chia nhau công việc chuyển đổi. TXLSXWorkbook mở XLSX, ODS, và CSV, và lưu ra XLSX, ODS, CSV, HTML, RTF, và XLSX đã mã hóa AES. TXLSWorkbook mở và lưu BIFF, và xuất ra HTML, RTF, và CSV. Điều hữu ích là mỗi tuyến đi kèm một hồ sơ độ trung thực đã được ghi lại rõ ràng, chứ không phải một lời hứa mơ hồ về sự đúng đắn, nên bạn có thể quyết định trước tuyến nào an toàn cho tệp nào

Xuất CSV ghi ra UTF-8 kèm BOM, kết thúc dòng CRLF, và quy tắc trích dẫn theo RFC 4180. Điều nó không làm là tính giá trị công thức: một ô giữ =SUM(...) xuất ra dưới dạng văn bản công thức nguyên văn, nên một sheet toàn công thức biến thành một sheet toàn chuỗi trừ khi bạn tính giá trị trước. Xuất HTML tạo ra một bảng duy nhất, với colspan và rowspan thay thế cho ô merge và các style cơ bản được inline. Xuất RTF có một giới hạn khắt khe hơn: nó không thể trải một ô merge ngang qua nhiều cột, nên các ô nối tiếp của một vùng merge sẽ ra trống rỗng. Việc nhập ODS cố tình nhẹ, theo đúng tài liệu của chính thư viện. Giá trị vô hướng và kết quả công thức đã cache thì đi qua được; style, biểu thức công thức ODF sống, và drawing thì không. Điều đó quan trọng ngay khi kho lưu trữ chứa các tệp OpenDocument thực sự tuân theo OASIS ODF 1.3, nơi bất cứ điều gì gần với một chuyển đổi trung thực về mặt hình ảnh đều cần nhiều hơn những gì đường nhập này được xây để gánh, và lượt audit chính là thứ báo cho bạn biết những tệp đó tồn tại trước khi lô chuyển đổi âm thầm làm phẳng chúng

SaveXLSWorkbookAsXLSX là một cầu nối dữ liệu, không phải một cầu nối layout

Facade BIFF không thể ghi OOXML trực tiếp, nên việc băng qua từ .xls sang .xlsx phải chạy qua hàm SaveXLSWorkbookAsXLSX trong unit lxXlsxExport. Độ trung thực của cầu nối đó đáng được nói thẳng ra, vì cái tên gợi ý nhiều hơn những gì nó thực sự làm. Nó sao chép giá trị, công thức, định dạng số, màu nền, thuộc tính font lõi, chiều rộng cột, và các thiết lập view như gridline. Nó không sao chép viền, vùng merge, comment, chart, hay conditional format. Với việc chuẩn hóa ở mức dữ liệu, nơi các hệ thống phía sau sẽ phân tích kết quả và chẳng ai nhìn vào định dạng, thì như vậy là vừa đủ và không có gì bị mất mà ai đó thực sự cần. Với một báo cáo hội đồng quản trị đã định dạng dành cho con người đọc, thì như vậy là chưa đủ, và đây chính xác là chỗ mà các bộ đếm audit xứng đáng có chỗ đứng: một tệp mà audit đánh dấu là mang theo chart và conditional format nên được định tuyến vào một hàng đợi thủ công, chứ không phải qua một cầu nối sẽ làm rơi mất cả hai mà không hề báo trước

var
  Legacy: IXLSWorkbook;        // tham chiếu interface: không gọi 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 của sheet vào zip
      if Modern.Open(FileName) = 1 then
        Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
    finally
      Modern.Free;
    end;
  end;
end;

Vòng lặp ở trên cũng cho thấy đòn bẩy thông lượng ở phía OOXML. Đặt StreamingWrite thành true phát trực tiếp XML của worksheet vào package đầu ra thay vì dàn nó thành một chuỗi khổng lồ duy nhất trong bộ nhớ, đó là sự khác biệt giữa một lượt chạy thoải mái và một crash hết bộ nhớ khi tệp chạm tới hàng trăm nghìn dòng. Kích thước và hành vi bộ nhớ cho chế độ đó được bàn riêng trong bài viết của chúng tôi về streaming write cho các job batch phía server. Còn một thuộc tính nữa quan trọng với một lô muốn dùng hết mọi lõi CPU: không facade nào thread-safe cả, nhưng cũng không facade nào chia sẻ trạng thái toàn cục, nên mẫu hình được hỗ trợ cho chuyển đổi song song là một thể hiện workbook cho mỗi worker thread, không khóa lẫn nhau giữa chúng

Các tệp có mật khẩu, và cách xử lý chúng

Các tệp bị khóa trong kho lưu trữ chia tách gọn gàng theo định dạng, và cách chia đó quyết định chúng đi đâu. Mã hóa .xls kiểu cũ, dù là RC4, RC4 qua CryptoAPI, hay kiểu làm rối XOR cũ, đều đọc được: truyền mật khẩu vào Open và tệp chuyển đổi như bất kỳ tệp nào khác. Các package .xlsx đã mã hóa lại là một câu chuyện khác. HotXLS phát hiện chúng bằng CanReadEncrypted nhưng không thể giải mã chúng, nên nước đi trung thực duy nhất là định tuyến chúng vào một hàng đợi nơi một con người mở và lưu lại từng tệp trong Excel trước khi nó quay lại pipeline. Sự bất đối xứng đó đáng được thiết kế sẵn ngay từ đầu, vì các tệp XLSX đã mã hóa thường là những bản ghi mà ai đó thực sự quan tâm nhất

Khép vòng lặp bằng xác minh

Giai đoạn thứ ba là giai đoạn hay bị bỏ qua, và việc bỏ qua nó chính là thứ biến một lượt chuyển đổi hàng loạt thành một gánh nặng trách nhiệm. Không đường lưu nào trong HotXLS tính giá trị công thức. Excel tự tính lại khi mở một tệp, nên một lượt chuyển đổi XLSX-sang-XLSX vẫn đúng, nhưng một đích CSV nhận văn bản công thức y nguyên trừ khi pipeline chạy Calculate trên các ô trước rồi ghi kết quả trở lại. Biết trước điều đó chính là sự khác biệt giữa một CSV toàn số và một CSV toàn chuỗi =SUM(...) mà chẳng ai để ý cho tới khi một lượt import ở phía sau bị nghẹn vì chúng

Bản thân việc xác minh đủ rẻ để không có lý do gì bỏ qua nó. Mở lại từng tệp đã chuyển đổi bằng cùng thư viện đó, chạy lại các bộ đếm audit, rồi so sánh chúng với các con số trước-chuyển-đổi mà lượt kiểm kê đã ghi lại từ trước. Một số đếm sheet bị giảm, một số đếm chart về 0 trong khi nguồn có ba, một số đếm ô rơi tuột dốc: mỗi trường hợp đó là một mất mát âm thầm bị bắt được với cái giá của một lượt mở lại thứ hai. Kiểm tra bằng mắt một mẫu trong Excel hay LibreOffice thêm vào đó nữa, và sự kết hợp này bắt được đại đa số thiệt hại chuyển đổi trước khi nó được giao đi. Đây chính là toàn bộ lý do vì sao giai đoạn kiểm kê nuôi giai đoạn xác minh. Không có các con số trước, thì các con số sau chẳng chứng minh được gì cả

Một workbench đặt audit lên hàng đầu biến một lượt chuyển đổi hàng loạt đầy rủi ro thành một quy trình đo lường được, với một làn cách ly riêng cho những tệp không thể vượt qua sạch sẽ. Tất cả các lệnh gọi dò, đếm, và chuyển đổi được trình bày ở đây đều là một phần của HotXLS Delphi Component, chạy tất cả những thứ đó thuần trong tiến trình mà không cần tự động hóa Excel