Tính hợp lệ schema pivot field XLSX trong Delphi với HotXLS

HotXLS ghi các định nghĩa pivot table XLSX mà các phần tử pivotField và cacheField validate đạt schema ECMA-376 Part 1 §18.10: thuộc tính axis dùng các token ST_Axis là axisRow, axisCol và axisPage, field vùng giá trị mang dataField="1", danh sách item chẳng bao giờ rỗng, và cache field lưu một numFmtId dạng số. Từ v2.384.33, reader còn tôn trọng các giá trị mặc định của schema mà nó từng đọc sai

Các bug đằng sau đợt dọn dẹp này chia sẻ một đặc điểm chẳng lấy gì làm vẻ vang: chẳng cái nào từng làm hỏng một test. HotXLS ghi một pivot, HotXLS đọc ngược lại, mọi field đáp đúng trục, và round-trip suite giữ màu xanh suốt mấy năm. Vấn đề là writer và reader đã lặng lẽ thống nhất với nhau một phương ngữ riêng. Một pivot dựng từ Delphi nhìn vào mắt component tạo ra nó thì ổn thỏa, trong khi một phép đối chiếu với CT_PivotField và CT_CacheField lôi ra các token enumeration không hợp lệ, một phần tử rỗng mà schema cấm, và các cờ mà Excel kỳ vọng nhưng chẳng bao giờ nhận được. Nếu bạn sinh pivot trên server rồi giao cho những người mở chúng trong Excel hay nhét chúng vào parser của riêng họ, thì hợp đồng duy nhất có giá trị là schema, chứ không phải thứ mà reader của bạn tình cờ bỏ qua

Vì sao round trip của HotXLS chẳng bao giờ bắt được các token axis sai?

Round trip của HotXLS chẳng bao giờ bắt được các token axis sai vì reader chấp nhận cả hai kiểu chính tả. XlsxPivotAxisAttr cũ emit axis="rowAxis", colAxis và pageAxis, đọc tự nhiên trong tiếng Anh nhưng không tồn tại trong schema; ST_Axis định nghĩa đúng bốn giá trị, axisRow, axisCol, axisPage và axisValues. Trong khi đó PivotAxisFromToken trong lxPivotXml.pas khớp cả token của schema lẫn token tự chế, nên mọi self-test đều pass. Writer giờ chỉ emit các token schema, còn reader vẫn chấp nhận các chính tả cũ để những file do các version HotXLS trước lưu vẫn nạp được với layout nguyên vẹn

<!-- trước v2.384.33: giá trị ST_Axis không hợp lệ, CT_Items rỗng -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- từ v2.384.33 -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>

CT_PivotField đòi hỏi điều gì mà writer cũ bỏ sót?

CT_PivotField đòi ba thứ mà BuildPivotTableXml cũ bỏ sót hay làm sai. Thứ nhất, một field được tổng hợp trong vùng giá trị phải tự nói điều đó trên định nghĩa của chính nó bằng dataField="1"; writer giờ đặt cờ đó trên mọi field được một entry trong DataFields tham chiếu, chứ không chỉ trong danh sách <dataFields>. Thứ hai, CT_Items cần ít nhất một item, nên một field không có item nào không còn nhận một <items count="0"> rỗng mà cả phần tử bị bỏ hẳn. Thứ ba, mỗi item giữ trạng thái của nó: h="1" cho item ẩn (TXLSPivotItem.IsHidden) và sd="0" cho chi tiết đã thu gọn (IsDetailHidden), cả hai mà writer cũ vứt bỏ ở mỗi lần save

Phần tinh tế là các item subtotal đuôi. Khi một field có item, Excel liệt kê thêm một item cho mỗi hàm subtotal sau các data item, với kiểu ST_ItemType: <item t="default"/> cho subtotal tự động, rồi sum, countA, avg, max, min, product, count, stdDev, stdDevP, var và varP cho các loại tường minh. HotXLS suy ra các entry đó từ TXLSPivotField.Subtotals lúc save và đếm chúng vào items count. Các field tạo bởi AddPivotTable khởi đầu với tập Subtotals rỗng, thứ ghi defaultSubtotal="0" và không có item đuôi nào, nên hãy yêu cầu subtotal tường minh khi báo cáo cần chúng. Chú ý cái bẫy đặt tên: xlpsCount ánh xạ thành countA (mọi entry) và xlpsCountNums ánh xạ thành count (chỉ số)

uses
  lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // đánh số từ 1, giống engine XLS
    Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 3, 6, 'RegionTotals');
    if Pivot = nil then
      raise Exception.Create('Bad source range or anchor');

    Region := Pivot.AddRowField('Region');    // nil nếu chẳng có field đó
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // đặt cờ Revenue dataField="1"

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

HotXLS giờ đọc item subtotal và các giá trị mặc định schema thế nào?

Reader của HotXLS giờ bỏ qua mọi item mà thuộc tính t xuất hiện và không phải data, vì các entry subtotal, grand-total và blank không mang chỉ mục cache nào. Trước v2.384.34, các entry đó được nạp như item thường với CacheItemIndex bằng -1, nên một pivot do Excel tạo quay về kèm các thành phần ma trỏ về hư không, và bất kỳ code nào đi qua Items đều phải tự tay lọc chúng. Vì writer dựng lại các entry đuôi từ Subtotals, việc của reader là dịch chúng ngược thành tập đó, chứ không phải giữ chúng như dữ liệu

Bản sửa reader thứ hai liên quan đến các thuộc tính vắng mặt. Trong schema, defaultSubtotal trên CT_PivotField và containsString trên CT_SharedItems đều mặc định là true, và Excel bỏ qua chúng khi giữ đúng giá trị mặc định đó. HotXLS từng đọc một thuộc tính vắng mặt thành false, nghĩa là mọi pivot do Excel lưu lặng lẽ mất subtotal mặc định khi nạp, và một cache field văn bản thuần bị phân loại thành mixed thay vì string. Đây là tấm gương phản chiếu của bug axis: một writer luôn viết ra mọi thuộc tính chẳng bao giờ đi qua đường mặc định, nên chỉ có file từ producer khác mới phơi ra nó

Vì sao numFmtId="General" không hợp lệ trên cache field?

Giá trị numFmtId="General" không hợp lệ vì ST_NumFmtId là một số nguyên không dấu, chứ không phải một tên định dạng. Cache writer cũ hard-code chuỗi đó trên mọi cacheField, mượn cái tên người dùng thấy trong hộp thoại Format Cells. HotXLS giờ ghi NumberFormat của cache field dưới dạng số, là 0 (định dạng General dựng sẵn) trừ khi có thứ khác đặt nó. Một parser nghiêm ngặt lấy kiểu thuộc tính từ schema sẽ từ chối thẳng giá trị cũ, và đó chính là lớp lỗi biến thành hộp thoại sửa chữa; bài về các luật OPC và markup đằng sau prompt sửa chữa của Excel trình bày cách những hộp thoại đó được kích hoạt

Vì sao pivot table đặt tại dòng 65535 trở xuống bị cắt cụt?

Các pivot table XLSX đặt tại dòng 65536 trở xuống bị cắt cụt vì pivot model dùng chung lưu FirstRow, LastRow, FirstHeaderRow, FirstDataRow và các cặp cột tương ứng dưới dạng Word, và code dịch dòng chặn chúng bằng Min(.., High(Word)). Đó là tàn dư của record BIFF8 SxView, nơi 16 bit là đủ, nhưng một sheet XLSX chạy tới 1.048.576 dòng. Từ v2.384.37, các thuộc tính đó trên TXLSPivotTable là Integer, các phép chặn bị dỡ bỏ, và chỉ writer BIFF8 thu hẹp giá trị. TXLSXWorksheet.AddPivotTable và AddPivotTableCopy giờ trả nil cho một anchor ngoài 1..1048576 nhân 1..16384, hay cho một bản copy có phạm vi sẽ tràn khỏi lưới

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Dòng 70001 từng wrap vào miền 16-bit; giờ nó sống sót qua save và load
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // anchor ngoài sheet hay vùng nguồn không phân giải được
  Pivot.AddRowField('Region');
  Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Book.SaveAs('late.xlsx');

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

Classic engine XLS nhận bản sửa tương ứng trong v2.384.38. Model của nó từng lưu các giá trị SxView và DConRef thô tính từ 0 và cho các anchor AddPivotTable đi thẳng qua, trong khi tài liệu, các demo và engine XLSX đều dùng ô tính từ 1 kiểu Cells[Row, Col]. Cả hai engine giờ giữ vị trí tính từ 1 trong model, reader BIFF8 cộng 1 và writer trừ 1 tại biên record, nên code từng anchor ở (0, 0) phải chuyển sang (1, 1), vì classic AddPivotTable giờ trả nil cho một anchor ngoài 1..65536 nhân 1..256; lời gọi mới ghi ra cùng byte như lời gọi cũ. Bố cục record bản thân nó không đổi và được mô tả trong các record SX của BIFF8 đằng sau pivot table .xls cổ điển

Validate với schema, đừng validate với reader của chính bạn

Bài học khái quát được ra ngoài pivot: một reader khoan dung che giấu các vi phạm của writer, nên một round trip qua chính code của bạn chứng minh tính nhất quán, không phải tính đúng đắn. Mọi bug ở đây sống sót vì phía khoan dung và phía lỗi sống chung trong cùng một thư viện. Các phép kiểm thực sự bắt được lớp khuyết tật này là validate schema các phần được sinh ra, các file do Excel tạo được bơm qua reader của bạn với các thuộc tính bị bỏ qua ở giá trị mặc định, và các fixture ghim token chính xác thay vì kết quả đã parse. Các pivot bạn dựng qua API, bao gồm calculated field, calculated item và bố cục percent-of-total trình bày trong dựng và refresh pivot table XLSX với calculated fields, nhận XML đã sửa mà chẳng cần đổi code, còn pivot nạp từ file Excel tiếp tục replay các phần gốc của nó cho tới khi bạn sửa chúng

Tất cả các bản sửa này đều có trong HotXLS Delphi spreadsheet component hiện tại, thứ đọc và ghi XLS, XLSX và pivot table từ Delphi và C++Builder mà không cần Excel hay COM automation trên máy