LlosLab Software Development BlogGhi chép kỹ thuật dành cho nhà phát triển PDF, Delphi và phần mềm tài liệu

Bài viết Kỹ thuật

Công thức Tràn Mảng Động trong Delphi với HotXLS

HotXLS Component

HotXLS đánh giá các công thức tràn mảng động Excel 365 một cách tự nhiên trong Delphi và C++Builder: bạn đưa cho nó một sổ làm việc (workbook) có các ô chứa XLOOKUP hoặc FILTER, và công cụ công thức của nó sẽ tính toán tập kết quả rồi tràn nó trên hình chữ nhật đầu ra được neo giữ, giống hệt các giá trị mà Excel sẽ tạo ra. Thành phần Excel Delphi của HotXLS mô hình hóa một công thức tràn dưới dạng một ô gốc sở hữu phép tính toán cộng với một khối các ô phụ thuộc đọc ngược lại từ nó, đây là toàn bộ bức tranh bạn cần có trước khi viết bất kỳ dòng mã nguồn nào

Mô hình đó rất quan trọng bởi vì tác vụ hàng ngày không hề mang tính trừu tượng. Khách hàng gửi một tệp .xlsx được xây dựng trong Excel 365, các trang tính (sheet) của nó chứa đầy =FILTER(...) và =XLOOKUP(...), và dịch vụ của bạn phải tái tạo chính xác các con số tương tự một cách không giao diện (headless), trên một máy không cài đặt Excel, sau đó đọc các giá trị đã tràn hoặc tự ghi một vùng tràn mới của riêng bạn. Các mảng động đã thay đổi hợp đồng tính toán từ "một công thức, một ô" thành "một công thức, một hình chữ nhật các ô", và công cụ HotXLS tuân theo hợp đồng đó thay vì giả mạo nó bằng một lưới được mở rộng sẵn

Làm thế nào để đánh giá XLOOKUP và FILTER trong Delphi?

HotXLS điều phối mọi hàm mảng động qua một điểm truy cập đánh giá duy nhất, CalcDynArrayFunc trong lxCalc.pas, do đó toàn bộ nhóm hàm này chia sẻ chung một đường dẫn mã tràn. Tập hợp các hàm được hỗ trợ là XLOOKUP và FILTER (hai hàm ban đầu), cùng với XMATCH, SORT, UNIQUE, và SEQUENCE. Mỗi hàm trả về một mảng biến thể 2 chiều thay vì một giá trị vô hướng (scalar): SEQUENCE(3;2;1;1) tạo ra một lưới 3x2, SORT sắp xếp lại các hàng theo bất kỳ cột khóa nào tăng dần hoặc giảm dần, UNIQUE thu gọn các hàng trùng lặp và giữ lại lần xuất hiện đầu tiên, và XMATCH báo cáo vị trí bắt đầu từ 1 của một giá trị dưới cùng các chế độ khớp chính xác, ký tự đại diện, nhỏ hơn tiếp theo và lớn hơn tiếp theo giống như XLOOKUP. Không giống như các hàm phân phối thống kê vô hướng vốn trả về một số duy nhất cho mỗi lần gọi, các hàm này đưa lại toàn bộ một hình dạng vùng

Để tính toán một sổ làm việc vốn đã mang các công thức này, hãy mở nó, gọi Recalculate một lần, và đọc các ô mà vùng tràn đã đáp xuống. TXLSXWorkbook.Recalculate duyệt qua biểu đồ phụ thuộc và đánh giá từng công thức bẩn theo thứ tự tô pô, do đó một gốc tràn chỉ được tính toán một lần duy nhất và các phần tử của nó được ghi trực tiếp vào các ô thành viên

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // evaluate every formula, spills included
    Sheet := Book.Sheets[1];          // Sheets is 1-based
    // Read back the block the XLOOKUP / FILTER spilled into, say D2:F9.
    for r := 2 to 9 do
      for c := 4 to 6 do
        Writeln(Sheet.Cells[r, c].Text);
  finally
    Book.Free;
  end;
end;

Nếu sổ làm việc cần một hàm mà công cụ không đi kèm sẵn, cùng một bộ đánh giá đó cho phép bạn tích hợp logic riêng của mình thông qua hook các hàm trang tính tùy chỉnh, và một hàm tùy chỉnh có thể tự do trả về một mảng biến thể để nó tràn chính xác như các hàm tích hợp sẵn

Neo giữ phạm vi tràn bằng SetArrayFormula

HotXLS không bao giờ tự đoán kích thước vùng tràn: bạn chỉ định hình chữ nhật đầu ra, và TXLSRange.SetArrayFormula neo giữ công thức vào vùng đó. Phương thức này biên dịch công thức một lần, lưu trữ cây cú pháp trên ô gốc phía trên bên trái cùng với phạm vi được neo giữ, và cung cấp cho mỗi ô khác trong hình chữ nhật một công thức gọn nhẹ tham chiếu yếu tới ô gốc. Khi tính toán lại, ô gốc được đánh giá một lần và các phần tử ma trận của nó được phát trực tiếp lên từng ô thành viên, đây cũng là cách một gốc tràn hiển thị dưới dạng một nút trong biểu đồ phụ thuộc tính toán lại tăng dần. Đây là con đường cần sử dụng khi công việc là ghi một vùng tràn trở lại tệp dữ liệu thay vì đọc một vùng tràn ra

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Size the rectangle to the result: a 3-row by 2-column grid.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // fill A1:B3 with 1..6 down the rows
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

Hậu quả của việc neo giữ rõ ràng là một ranh giới đáng được nêu thẳng thắn: HotXLS không tự động thay đổi kích thước của vùng tràn theo cách mà Excel tương tác thực hiện. Excel tự động mở rộng và thu hẹp vùng tràn khi các đầu vào thay đổi và nâng lỗi #SPILL! khi các ô mục tiêu bị chiếm dụng. Trong HotXLS, hình chữ nhật bạn truyền vào SetArrayFormula chính là hình chữ nhật bạn sẽ nhận được, vì thế bạn phải định kích thước của nó theo kết quả mong đợi, và nếu một hàm tạo ra nhiều hàng hơn số lượng bạn đã neo giữ, phần dư thừa sẽ không có chỗ để hiển thị

Truyền phát mảng theo từng phần tử qua các toán tử

HotXLS áp dụng các toán tử số học theo từng phần tử khi một trong hai vế của phép toán là một mảng. Hàm bổ trợ ApplyArrayBinaryOp bao trùm +, -, *, /, và ^ trên các mảng biến thể 1 chiều và 2 chiều: một toán hạng vô hướng được truyền phát tới mọi phần tử, hai mảng phải khớp nhau về hình dạng nếu không phép toán sẽ bị từ chối, và phép chia cho không sẽ lan truyền dưới dạng một lỗi thay vì gây sập ứng dụng. Đường dẫn số học vô hướng được giữ nguyên không chạm tới, do đó tính năng này chỉ kích hoạt khi có ít nhất một toán hạng thực sự là một mảng, chẳng hạn như một phạm vi tràn hoặc kết quả của một hàm ma trận. Điều đó có nghĩa là một công thức như =D2:D13*1.1 được neo trên một cột sẽ lần lượt nhân từng phần tử, và =D2:D13*E2:E13 sẽ nhân hai cột có cùng hình dạng theo từng vị trí tương ứng

// Scalar broadcast: every cell of the anchor gets D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Two arrays of identical shape multiply element by element.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

Toán tử @ làm gì so với một mảng CSE cũ?

HotXLS đọc một ký tự @ hiển thị dưới dạng toán tử giao nhau tham chiếu (reference-intersection), chứ không phải là phép giao nhau ngầm định chọn hàng của Excel thực tế. Trong công cụ, A1:A3 @ B1:B3 trả về ô duy nhất nơi hai phạm vi cắt nhau, và toán tử này liên kết chặt chẽ hơn ^ và lỏng lẻo hơn %. Có một lưu ý thực tế đi kèm: dạng giao nhau phân tách bằng dấu cách ngầm định vẫn chưa được phân tích từ vựng (tokenize), bởi vì bộ phân tích từ vựng vẫn bỏ qua khoảng trắng, vì vậy bạn chỉ có thể thực hiện giao nhau thông qua ký hiệu @ hiển thị

Sự khác biệt sâu sắc hơn là giữa công thức mảng CSE cũ và mảng động hiện đại, và trong HotXLS cả hai đều đi qua cùng một cơ chế neo giữ. Một công thức mảng cổ điển là khối {=...} được nhập bằng tổ hợp Ctrl+Shift+Enter trên một vùng chọn được định kích thước sẵn, mọi ô chia sẻ chung một công thức đã biên dịch. Một mảng động là một công thức duy nhất có kết quả tự quyết định hình dạng của chính nó. HotXLS thống nhất chúng dưới phương thức SetArrayFormula: bạn luôn luôn chỉ định hình chữ nhật, ô gốc sở hữu cây biên dịch, và các ô thành viên tham chiếu đến nó, cho dù công thức đó là một biểu thức ma trận kiểu cũ hay một hàm SORT mới. Những gì bạn không bao giờ có được là hiệu ứng gợn sóng tự động của Excel nơi vùng dữ liệu tự động mở rộng trở lại một cách âm thầm, và việc ghi nhớ ranh giới đó giúp giữ cho hai mô hình tư duy không bị nhầm lẫn

Nơi sự hỗ trợ mảng động dừng lại

Nắm rõ các giới hạn sẽ giúp tiết kiệm cả buổi chiều gỡ lỗi. SORT tuân thủ hướng trên mỗi khóa, mặc định là tăng dần và giảm dần khi đối số thứ tự là số âm; hướng so sánh là điểm đáng để thử nghiệm các cột khóa của riêng bạn so với Excel, vì một phép so sánh đảo ngược sẽ âm thầm trả về các hàng theo thứ tự ngược lại thay vì báo lỗi. UNIQUE giữ lại lần xuất hiện đầu tiên của mỗi hàng riêng biệt, điều mà công cụ xác nhận bằng cách quét các hàng phía trước thay vì tin tưởng vào số lượng trùng lặp thô. Hàm TABLE (bảng dữ liệu Phân tích What-If) được nhận diện để nó có thể truyền nhận hai chiều qua tệp dữ liệu, nhưng nó được đánh giá thành một trình giữ chỗ bởi vì các kết quả thay thế thực sự là các giá trị được lưu trong bộ nhớ đệm mà Excel đã lưu trữ sẵn, và HotXLS không chạy lại lưới what-if

LET là giới hạn rõ ràng nhất cần được nêu ra. HotXLS đăng ký LET và mang theo một đường dẫn đánh giá cho nó, nhưng trình phân tích cú pháp sẽ từ chối một cái tên trần không phải là tên đã được định nghĩa sẵn, do đó =LET(x;10;x) sẽ thất bại khi phân tích cú pháp trước khi bộ đánh giá được tiếp cận. Hãy xử lý LET dưới dạng chưa được hỗ trợ cho đến khi trình phân tích cú pháp có khả năng nhận biết phạm vi cho các tên được liên kết của nó. Một lưu ý nhỏ về tính di động: các chuỗi công thức trong các ví dụ này sử dụng dấu phân cách danh sách của công cụ là dấu chấm phẩy, vì vậy hãy khớp với bất kỳ dấu phân cách nào mà bản dựng của bạn mong đợi khi bạn lắp ráp văn bản công thức. Các hàm mảng động được đề cập ở đây, từ XLOOKUP và FILTER cho đến SORT, UNIQUE, SEQUENCE và XMATCH, đi kèm trong HotXLS Delphi Excel Component, tài liệu công thức của nó liệt kê danh mục hàm đầy đủ cùng các chế độ đối số được hỗ trợ của từng hàm