pivot field XLSX ที่ถูกต้องตาม schema ใน Delphi ด้วย HotXLS

HotXLS เขียนนิยาม pivot table ของ XLSX ที่ element pivotField กับ cacheField validate ผ่าน schema ECMA-376 Part 1 §18.10: attribute แกนใช้ token ST_Axis คือ axisRow, axisCol และ axisPage ฟิลด์ในพื้นที่ค่าพก dataField="1" รายการ item ไม่ว่างเปล่าเด็ดขาด และ cache field เก็บ numFmtId แบบตัวเลข ตั้งแต่ v2.384.33 ตัวอ่านยังเคารพค่า default ของ schema ที่เคยอ่านผิดด้วย

bug ที่อยู่เบื้องหลังการเก็บกวาดรอบนี้แบกลักษณะที่ไม่น่าชมแบบเดียวกัน: ไม่มีตัวไหนเคยทำ test พังเลยสักครั้ง HotXLS เขียน pivot, HotXLS อ่านกลับ ทุกฟิลด์ลงถูกแกน และ suite แบบ round trip ก็เขียวมาหลายปี ปัญหาคือ writer กับ reader ตกลงกันเองเงียบ ๆ บนสำเนียงส่วนตัว pivot ที่สร้างจาก Delphi มองดีงามในสายตา component ที่สร้างมัน แต่พอเช็กกับ CT_PivotField กับ CT_CacheField ก็พบ token ของ enumeration ที่ไม่ถูกต้อง, element ว่าง ๆ ที่ schema ห้าม และธงที่ Excel คาดหวังแต่ไม่เคยได้รับ ถ้าคุณ generate pivot บนเซิร์ฟเวอร์แล้วส่งไปให้คนที่เปิดใน Excel หรือเอาไปป้อน parser ของเขาเอง สัญญาเดียวที่มีความหมายคือ schema ไม่ใช่สิ่งที่ reader ของคุณบังเอิญใจดีปล่อยผ่าน

ทำไม round trip ของ HotXLS ถึงไม่เคยจับ token แกนที่ผิดได้

round trip ของ HotXLS ไม่เคยจับ token แกนที่ผิดได้เพราะตัวอ่านยอมรับทั้งสองสำนวน XlsxPivotAxisAttr ตัวเก่า emit axis="rowAxis", colAxis และ pageAxis ซึ่งอ่านลื่นในภาษาอังกฤษ แต่ไม่มีอยู่ใน schema ST_Axis นิยามค่าแค่สี่ค่าเป๊ะ ๆ คือ axisRow, axisCol, axisPage และ axisValues ระหว่างนั้น PivotAxisFromToken ใน lxPivotXml.pas match ทั้ง token ตาม schema และตัวที่แต่งขึ้นเอง self-test ทุกตัวจึงผ่าน ตอนนี้ตัวเขียน emit แต่ token ของ schema เท่านั้น ส่วนตัวอ่านยังรับสำนวนเก่าต่อไป เพื่อให้ไฟล์ที่บันทึกโดย HotXLS รุ่นก่อนหน้ายังโหลดได้โดยผัง layout ครบถ้วน

<!-- ก่อน v2.384.33: ค่า ST_Axis ไม่ถูกต้อง, CT_Items ว่างเปล่า -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- ตั้งแต่ 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 ต้องการอะไรที่ตัวเขียนเก่าละเลย

CT_PivotField ต้องการสามอย่างที่ BuildPivotTableXml ตัวเก่าละไว้หรือทำผิด หนึ่ง ฟิลด์ที่ถูกรวมค่าในพื้นที่ค่าต้องประกาศในนิยามของตัวเองด้วย dataField="1" ตัวเขียนตอนนี้ตั้งธงนี้ให้ทุกฟิลด์ที่ถูกอ้างอิงโดย entry ใน DataFields ไม่ใช่แค่ในรายการ <dataFields> สอง CT_Items ต้องมี item อย่างน้อยหนึ่งตัว ฟิลด์ไร้ item จึงไม่ได้รับ <items count="0"> ว่าง ๆ อีกต่อไป แต่ element ทั้งก้อนถูกละไปเฉย ๆ สาม item แต่ละตัวเก็บสถานะของตัวเอง: h="1" สำหรับ item ที่ถูกซ่อน (TXLSPivotItem.IsHidden) และ sd="0" สำหรับรายละเอียดที่ยุบไว้ (IsDetailHidden) ซึ่งตัวเขียนเก่าทิ้งไปทุกครั้งที่บันทึก

ส่วนที่ละเอียดอ่อนคือ item subtotal ท้าย ๆ เมื่อฟิลด์มี item Excel จะเรียง item เพิ่มหนึ่งตัวต่อฟังก์ชัน subtotal หลัง item ของข้อมูล กำหนด type ด้วย ST_ItemType: <item t="default"/> สำหรับ subtotal อัตโนมัติ แล้วตามด้วย sum, countA, avg, max, min, product, count, stdDev, stdDevP, var และ varP สำหรับแบบระบุชัด HotXLS สร้าง entry พวกนี้จาก TXLSPivotField.Subtotals ตอนบันทึก แล้วนับรวมเข้า items count ฟิลด์ที่สร้างโดย AddPivotTable เริ่มต้นด้วยชุด Subtotals ว่าง ซึ่งเขียน defaultSubtotal="0" และไม่มี item ท้าย ๆ จึงต้องระบุ subtotal ชัด ๆ เมื่อรายงานต้องการ อย่าลืมกับดักการตั้งชื่อ: xlpsCount แมปไป countA (ทุก entry) และ xlpsCountNums แมปไป count (เฉพาะตัวเลข)

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];                  // เป็น 1-based เหมือน 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 ถ้าไม่มีฟิลด์นี้
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // ตั้งธง dataField="1" ให้ Revenue

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

HotXLS อ่าน item subtotal กับค่า default ของ schema อย่างไรตอนนี้

ตัวอ่านของ HotXLS ตอนนี้ข้าม item ทุกตัวที่มี attribute t อยู่และไม่ใช่ data เพราะ entry แบบ subtotal, grand total และค่าว่างไม่พก cache index มาด้วย ก่อน v2.384.34 entry พวกนี้ถูกโหลดเป็น item ธรรมดาที่ CacheItemIndex ตั้งเป็น -1 pivot ที่ Excel สร้างจึงกลับมาพร้อมสมาชิกผีที่ไม่ชี้ไปไหน และโค้ดที่เดินไล่ Items ต้องกรองมันทิ้งเองด้วยมือ เนื่องจากตัวเขียนสร้าง entry ท้าย ๆ ขึ้นใหม่จาก Subtotals หน้าที่ของตัวอ่านคือแปลงมันกลับเข้าชุดนั้น ไม่ใช่เก็บไว้เป็นข้อมูล

การแก้ตัวอ่านจุดที่สองเกี่ยวกับ attribute ที่ไม่อยู่ ใน schema defaultSubtotal บน CT_PivotField กับ containsString บน CT_SharedItems default เป็น true ทั้งคู่ และ Excel ละมันทิ้งเมื่อค่าเป็น default นั้น HotXLS เคยอ่าน attribute ที่หายไปเป็น false ทุก pivot ที่ Excel บันทึกจึงเสีย default subtotal ไปเงียบ ๆ ตอนโหลด และ cache field ข้อความธรรมดาถูกจัดประเภทเป็น mixed แทนที่จะเป็น string นี่คือภาพสะท้อนของ bug เรื่องแกน: writer ที่สะกด attribute ทุกตัวออกมาเสมอ ไม่เคยได้ใช้เส้นทาง default เลย มีแต่ไฟล์จากผู้ผลิตรายอื่นเท่านั้นที่เปิดเผยมัน

ทำไม numFmtId="General" ถึงไม่ถูกต้องบน cache field

ค่า numFmtId="General" ไม่ถูกต้องเพราะ ST_NumFmtId เป็นจำนวนเต็มไม่มีเครื่องหมาย ไม่ใช่ชื่อรูปแบบ ตัวเขียน cache ตัวเก่า hardcode string นี้ลงทุก cacheField ยืมชื่อที่ผู้ใช้เห็นใน dialog Format Cells มาใช้ HotXLS ตอนนี้เขียน NumberFormat ของ cache field เป็นตัวเลข ซึ่งเป็น 0 (รูปแบบ General ในตัว) เว้นแต่มีใครตั้งไว้ parser ที่เข้มงวดซึ่งกำหนด type ของ attribute ตาม schema จะปฏิเสธค่าเก่าทันที และนั่นคือคลาสของความล้มเหลวที่กลายเป็น dialog ซ่อมไฟล์พอดี บทความเรื่องกฎ OPC กับ markup ที่อยู่หลัง prompt ซ่อมไฟล์ของ Excel เล่าว่า dialog พวกนี้ถูกกระตุ้นขึ้นได้อย่างไร

ทำไม pivot table ที่อยู่แถว 65535 ลงไปถึงถูกตัดขาด

pivot table ของ XLSX ที่วางแถว 65536 ลงไปถูกตัดขาด เพราะโมเดล pivot ที่ใช้ร่วมกันเก็บ FirstRow, LastRow, FirstHeaderRow, FirstDataRow และคู่ฝั่งคอลัมน์เป็น Word และโค้ดเลื่อนแถวหนีบค่าด้วย Min(.., High(Word)) นั่นคือของเหลือจาก record SxView ของ BIFF8 ซึ่ง 16 บิตพอ แต่ชีต XLSX ยาวถึง 1,048,576 แถว ตั้งแต่ v2.384.37 property พวกนี้บน TXLSPivotTable เป็น Integer การหนีบหายไป และมีแค่ตัวเขียน BIFF8 เท่านั้นที่ยังบีบค่า TXLSXWorksheet.AddPivotTable กับ AddPivotTableCopy ตอนนี้คืน nil สำหรับ anchor ที่อยู่นอก 1..1048576 คูณ 1..16384 หรือสำหรับสำเนาที่ขอบเขตจะล้นตาราง

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // แถว 70001 เคยวนกลับเข้าช่วง 16 บิต ตอนนี้รอดทั้งการบันทึกและโหลด
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // anchor อยู่นอกชีต หรือช่วงต้นทาง resolve ไม่ได้
  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;

engine XLS คลาสสิกได้รับการแก้คู่กันใน v2.384.38 โมเดลของมันเคยเก็บค่า SxView กับ DConRef ดิบ ๆ แบบ 0-based และส่ง anchor ของ AddPivotTable ผ่านตรง ๆ ขณะที่เอกสาร ตัวอย่าง และ engine XLSX ใช้ cell แบบ 1-based อย่าง Cells[Row, Col] ทั้งหมด ทั้งสอง engine ตอนนี้เก็บตำแหน่งแบบ 1-based ในโมเดล ตัวอ่าน BIFF8 บวก 1 และตัวเขียนลบ 1 ที่ขอบเขตของ record โค้ดที่ anchor ที่ (0, 0) จึงต้องย้ายไป (1, 1) เพราะ AddPivotTable คลาสสิกตอนนี้คืน nil สำหรับ anchor นอก 1..65536 คูณ 1..256 การเรียกแบบใหม่เขียนไบต์เดียวกับแบบเก่า ผังของ record เองไม่เปลี่ยน และถูกอธิบายไว้ในเรื่อง record SX ของ BIFF8 ที่อยู่หลัง pivot table ของ .xls คลาสสิก

Validate กับ schema ไม่ใช่กับ reader ของตัวเอง

บทเรียนนี้ขยายไปไกลกว่า pivot: ตัวอ่านที่ใจกว้างซ่อนการละเมิดของตัวเขียน round trip ผ่านโค้ดของตัวเองจึงพิสูจน์ได้แค่ความสม่ำเสมอ ไม่ใช่ความถูกต้อง ทุก bug ที่นี่อยู่รอดเพราะฝ่ายใจดีกับฝ่ายมีตำหนิอาศัยอยู่ใน library เดียวกัน การเช็กที่จับข้อบกพร่องคลาสนี้ได้จริงคือ schema validation กับ part ที่ generate ออกมา, ไฟล์ที่ Excel ผลิตถูกป้อนผ่าน reader ของคุณโดย attribute ถูกละไว้ที่ค่า default, และ fixture ที่ตรึง token เป๊ะ ๆ แทนที่จะดูแค่ผล parse pivot ที่คุณสร้างผ่าน API รวมถึง calculated field, calculated item และผัง percent-of-total ที่แสดงไว้ในการสร้างและรีเฟรช pivot table XLSX ด้วย calculated field จะได้ XML ที่แก้แล้วโดยไม่ต้องแตะโค้ด ส่วน pivot ที่โหลดจากไฟล์ Excel ยังเล่นซ้ำ part เดิมของมันต่อไป จนกว่าคุณจะแก้ไขมัน

การแก้ทั้งหมดนี้ ship มากับHotXLS Delphi spreadsheet component รุ่นปัจจุบัน ซึ่งอ่านเขียน XLS, XLSX และ pivot table จาก Delphi กับ C++Builder ได้โดยไม่ต้องมี Excel หรือ COM automation บนเครื่อง