Xây dựng và Làm mới Bảng tổng hợp Pivot Table Excel trong Delphi với HotXLS

HotXLS xây dựng và làm mới các bảng tổng hợp Pivot Table XLSX gốc từ Delphi và C++Builder mà không cần cài đặt Excel cũng như không cần tự động hóa COM trên máy tính. Bạn gọi AddPivotTable với một phạm vi nguồn, kéo các trường vào các trục hàng, cột, trang và dữ liệu, phủ thêm các mục tính toán hoặc hiển thị tỷ lệ phần trăm trên tổng số, và thành phần này sẽ ghi lại các phần pivotCacheDefinition và pivotTableDefinition mà Excel sẽ mở ra dưới dạng một Pivot Table động có thể làm mới được

Kịch bản làm cho công nghệ này trở nên đáng giá là một máy chủ báo cáo. Bạn tạo ra hàng trăm sổ làm việc mỗi đêm, mỗi sổ chứa một Pivot Table tóm tắt một tài khoản, và tháng sau các con số thay đổi thì mỗi tệp đều phải phản ánh các hàng nguồn mới. Việc điều khiển Excel từ một dịch vụ Windows rất mong manh và không được cấp phép để sử dụng trên máy chủ, còn việc tự viết mã XML cho Pivot Table là một dự án khảo cổ đặc tả kỹ thuật không bao giờ kết thúc. HotXLS nằm giữa hai ngõ cụt đó: một mô hình đối tượng có kiểu (typed object model) trên các phần Pivot Table OOXML, nhờ đó chính mã Pascal điền các ô cũng khai báo Pivot Table và xây dựng lại bộ nhớ đệm của nó trong cùng một tiến trình

Làm thế nào để tạo một bảng tổng hợp Pivot Table trong Delphi mà không cần Excel?

Bạn tạo một bảng chỉ với một cuộc gọi duy nhất và sau đó đặt các trường trên các trục. AddPivotTable nhận phạm vi nguồn theo ký hiệu A1, ô đích nơi bảng neo giữ, và một tên gọi; nó phân tích cú pháp phạm vi, quét từng cột để suy luận kiểu dữ liệu của nó, xây dựng bộ nhớ đệm Pivot Cache (hoặc sử dụng lại bộ nhớ đệm đã liên kết với cùng phạm vi), và trả về một đối tượng TXLSPivotTable có các trường ban đầu nằm ngoài trục. Từ đó, các phương thức tiện ích AddRowField, AddColumnField, AddPageField, và AddDataFieldByName sẽ thiết lập bố cục, và mỗi trường dữ liệu sẽ nhận một trong mười một hàm tổng hợp từ kiểu liệt kê TXLSPivotAggregation

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // report sheet (1-based in the XLSX engine)

    // Source A1:E500 on the 'Data' sheet; anchor the pivot at row 3, col 1.
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

Tại sao bộ nhớ đệm và bảng lại là hai phần riêng biệt

Một bảng tổng hợp thực chất là hai thành phần tham chiếu lẫn nhau, và việc hiểu rõ sự chia tách này là thứ giúp làm sáng tỏ phần còn lại. pivotCacheDefinition là ảnh chụp nhanh dữ liệu (data snapshot): một worksheetSource trỏ tới phạm vi nguồn, và một cacheField cho mỗi cột giữ các giá trị riêng biệt của cột đó (các sharedItems của nó) cộng với các ranh giới dẫn xuất. pivotTableDefinition là dạng xem (view): trường bộ nhớ đệm nào nằm trên trục nào, các trường dữ liệu và các hàm tổng hợp của chúng, các tùy chọn bố cục. Dạng xem liên kết với bộ nhớ đệm bằng cacheId thông qua mối quan hệ pivotCaches của sổ làm việc, chính xác như ECMA-376 Phần 1 §18.10 và [MS-XLSX] trình bày

Sự gián tiếp đó không phải là thủ tục rườm rà, nó mang lại hai lợi ích. Một bộ nhớ đệm có thể hỗ trợ cho nhiều bảng tổng hợp, do đó việc làm mới bộ nhớ đệm một lần sẽ cập nhật mọi dạng xem được vẽ từ nó. Và bộ nhớ đệm lưu trữ các giá trị của mỗi cột đã được loại bỏ trùng lặp bằng một bảng chỉ mục trên mỗi bản ghi thay vì lưới thô, đó là lý do tại sao việc xây dựng một Pivot Table nghĩa là quét nguồn dữ liệu chứ không phải sao chép các ô. HotXLS mô hình hóa điều này theo cùng một cách cho cả hai công cụ, do đó mã nguồn ở trên gần như giống hệt với đường dẫn cổ điển được tài liệu hóa bên cạnh bố cục bản ghi SX nhị phân đằng sau các Pivot Table .xls cổ điển. Nếu nguồn dữ liệu của bạn nằm trên một trang tính khác hoặc bạn địa chỉ hóa nó thông qua một tên gọi, tiền tố phạm vi cùng các tên đã định nghĩa và tham chiếu chéo trang tính sẽ tuân theo các quy tắc A1 thông thường, cho phép đặt tên trang tính trong dấu nháy đối với các tên có chứa khoảng trắng

Các mục tính toán không phải là các trường tính toán

Ba thuật ngữ này gọi tên ba thứ khác nhau, và việc nhầm lẫn giữa chúng là lỗi Pivot Table kinh điển. Một mục tính toán (calculated item) nằm bên trong một trường và kết hợp các mục của chính trường đó bằng tên gọi, do đó trong một trường Region bạn có thể định nghĩa một hàng CoreMarkets tổng hợp bằng North cộng South; HotXLS cung cấp tính năng này dưới dạng TXLSPivotField.AddCalculatedItem và ghi một <calculatedItem> dưới mục <calculatedItems> của trường đó. Một trường tính toán (calculated field) thì khác biệt: nó là một giá trị mới được dẫn xuất từ các cột khác, như Margin tính từ Revenue và Cost, và nó được truyền tải dưới dạng một công thức trên một trường bộ nhớ đệm thông qua TXLSPivotCacheField.Formula. Một thành viên tính toán (calculated member), được thêm bằng TXLSPivotTable.AddCalculatedMember, là một thành viên tùy chỉnh cấp bảng có thể đóng vai trò như một độ đo (member type data) hoặc một thành viên chiều, chủ yếu được quan tâm đối với các Pivot Table dạng OLAP

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // A calculated ITEM combines items of a single field by name.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // A calculated MEMBER is declared at the table level. Member type
  // 'data' marks it as a measure; an empty type is a dimension member.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Có một ranh giới thực tế áp dụng cho cả ba. HotXLS phát ra văn bản công thức vào tệp XML định nghĩa; nó không tự đánh giá nó. Excel sẽ tính toán mục, trường hoặc thành viên tính toán khi nó mở tệp, theo cùng cách nó tính toán mọi giá trị tổng hợp trong lưới. HotXLS đang ghi các chỉ dẫn chứ không phải kết quả, do đó các công thức bạn cung cấp phải là các công thức Pivot Table hợp lệ theo cú pháp riêng của Excel, tham chiếu đến tên trường và tên mục như Excel thực hiện

Làm thế nào để hiển thị các giá trị dưới dạng tỷ lệ phần trăm trên tổng số?

Bạn thiết lập chế độ hiển thị giá trị trên trường dữ liệu thay vị tự mình chuyển đổi các con số. TXLSPivotDataField.ShowDataAs nhận kiểu liệt kê TXLSPivotShowDataAs, vốn phản chiếu các giá trị OOXML ST_ShowDataAs

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // Item-relative modes need a base to compare against. A running total
  // down 'Quarter' (cache-field index 3) would read:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // cache-field index the transform runs over
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

xlpsdaPercentOfTotal không cần cơ sở (base) vì nó tương đối so với tổng cộng, nhưng các chế độ tương đối theo mục thì có. xlpsdaDifference, xlpsdaPercentDiff, và xlpsdaRunTotal yêu cầu BaseField (chỉ mục trường bộ nhớ đệm mà phép so sánh chạy qua) và, đối với các dạng liên kết theo mục, một chỉ mục BaseItem, trong đó $7FFD đại diện cho phần tử canh gác (All). HotXLS ghi các thuộc tính này dưới dạng thuộc tính <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> và giống như với các công thức tính toán, nhường quyền tính toán cho Excel

Nhóm ngày tháng và số lượng, cùng các bộ lọc trường trang (page-field filter)

Việc gom nhóm được cấu hình trên trường bộ nhớ đệm (cache field) chứ không phải trường bảng tổng hợp, bởi vì nó thay đổi cách phân nhóm miền nguồn. Đặt HasGroup := True trên trường bộ nhớ đệm được trả về bởi Cache.FindFieldByName, sau đó chọn các núm phạm vi số (GroupStartNum, GroupEndNum, GroupInterval) hoặc các cờ phân cấp ngày tháng (GroupByDate với GroupMonths, GroupQuarters, GroupYears, và một khoảng GroupStartDate / GroupEndDate). HotXLS phát ra phần tử <fieldGroup><rangePr groupBy="months"/> phù hợp để một trường được nhóm theo tháng hoặc theo một dải số rộng 1000 mở ra theo cách mà Excel gom nhóm

Các trường trang (page field) là các trình đơn thả xuống bộ lọc phía trên bảng. AddPageField đặt một trường lên trục trang, và PageItemIndex chọn trước một mục bộ nhớ đệm duy nhất, mặc định là xlPageItemAll ($7FFD, nghĩa là (All)). Để cho phép người đọc tích chọn nhiều mục cùng một lúc, hãy đặt MultipleItemSelectionAllowed := True, điều mà HotXLS ghi dưới dạng <pivotField multipleItemSelectionAllowed="1"/>. Đối với các tiêu chí vượt ngoài việc chọn thủ công, mỗi trường mang một bộ sưu tập Filters có kiểu bao trùm các họ ST_FilterType OOXML — các bộ lọc số lượng, phần trăm, tổng số, chú thích, giá trị và ngày tháng, mỗi mục ghép đôi một kiểu bộ lọc với các giá trị so sánh của nó

Làm thế nào để làm mới bộ nhớ đệm Pivot Cache khi nguồn dữ liệu thay đổi?

RefreshPivotCache trên TXLSXWorkbook quét lại phạm vi nguồn và xây dựng lại bộ nhớ đệm tại chỗ, đây là những gì một đường ống chạy hàng loạt cần sau khi nó chỉnh sửa các hàng dữ liệu cơ sở. Bạn truyền id bộ nhớ đệm, và phương thức này sẽ đọc lại từng ô trong phạm vi nguồn (tiêu đề ở hàng đầu tiên, dữ liệu từ hàng thứ hai), xây dựng lại miền mục chia sẻ của từng trường bằng cách loại bỏ trùng lặp lại các giá trị và dẫn xuất lại các ranh giới theo kiểu, rồi ghi lại các chỉ mục mục cho mỗi bản ghi. Nó trả về 1 khi thành công và -1 khi id bộ nhớ đệm không xác định hoặc trang tính nguồn bị thiếu

var
  Rc: Integer;
begin
  // ...the source data has changed since the pivot was built...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // a corrected Revenue figure

  // Rebuild the cache's shared items and record indices from the source.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = refreshed, -1 = cache/source missing
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

Ranh giới ngữ nghĩa ở đây đáng được nêu rõ một cách thẳng thắn. Trước khi phương thức này tồn tại, HotXLS phụ thuộc vào cờ refreshOnLoad="1" và nhường toàn bộ công việc làm mới cho Excel trong lần mở tiếp theo, điều này tốt khi một con người mở tệp nhưng vô dụng trong một đường ống tự động (headless pipeline) vốn cần bàn giao dữ liệu chính xác. RefreshPivotCache làm cho bộ nhớ đệm đã lưu được cập nhật ngay trong tiến trình, và vì các bảng liên kết với bộ nhớ đệm bằng id, nên mọi Pivot Table được vẽ từ bộ nhớ đệm đó đều thấy bản cập nhật. Những gì nó không thực hiện là bố cục hoặc tổng hợp lưới hiển thị trực quan — Excel vẫn tính toán lại bảng kết xuất từ bộ nhớ đệm được làm mới khi mở tệp

Những gì HotXLS ghi và những gì Excel tính toán

Hãy luôn lưu tâm về sự phân công lao động và bạn sẽ không gặp bất ngờ nào. HotXLS là một trình ghi định nghĩa: nó tạo ra pivotCacheDefinition, các bản ghi bộ nhớ đệm, và pivotTableDefinition, hoàn chỉnh với các trục, các hàm tổng hợp, các mục và thành viên tính toán, các chế độ hiển thị giá trị, gom nhóm và các bộ lọc. Excel đóng vai trò là bộ tính toán: khi mở, nó hiện thực hóa các nhóm phân loại, đánh giá các công thức tính toán, áp dụng các phép biến đổi showDataAs, và tổng hợp phần thân bảng. Các giá trị người dùng nhìn thấy là của Excel, được tạo ra từ các chỉ dẫn mà HotXLS đã ghi, đó là lý do tại sao mọi công thức và chỉ mục cơ sở phải chính xác tại thời điểm ghi thay vì kiểm tra tại thời điểm kết xuất

Có hai giới hạn đáng lưu ý trước khi bạn thiết kế xung quanh tính năng này. AddPivotTable phân tích cú pháp một phạm vi A1 hình chữ nhật với một tiền tố trang tính tùy chọn; các nguồn phạm vi được đặt tên (named-range) và sổ làm việc bên ngoài nằm ngoài những gì trình xây dựng có thể giải quyết, mặc dù các bộ nhớ đệm được đọc từ một tệp do Excel soạn thảo vẫn bảo toàn nguồn phạm vi đặt tên của chúng qua quá trình truyền nhận hai chiều. Và các Pivot Table do Excel tạo mà HotXLS đọc từ đĩa sẽ được phát lại từng byte khi lưu, vì thế các chỉnh sửa có kiểu được mô tả ở đây áp dụng sạch sẽ cho các Pivot Table bạn xây dựng trong mã nguồn trong khi các Pivot Table hiện có vẫn không bị mất dữ liệu. Đối với phía đầu vào của một báo cáo — các ô được xác thực và các bảng được lọc mà Pivot Table tóm tắt — hãy xem xác thực dữ liệu, AutoFilter, và các bảng có cấu trúc

Bảng tổng hợp Pivot Table hiển thị ở đây là một phần của HotXLS Delphi Excel Component dành cho Delphi và C++Builder, một thư viện đọc và ghi cả các phần Pivot Table OOXML và các bản ghi BIFF8 cổ điển từ cùng một mô hình đối tượng