การสร้างและรีเฟรช Pivot Table ของ Excel ใน Delphi ด้วย HotXLS

HotXLS สร้างและรีเฟรช Pivot Table แบบ XLSX ดั้งเดิมจาก Delphi และ C++Builder โดยไม่จำเป็นต้องติดตั้ง Excel และไม่ต้องใช้ COM automation บนเครื่อง คุณสามารถเรียกใช้ AddPivotTable พร้อมกำหนดช่วงแหล่งข้อมูล วางฟิลด์ลงในแกนแถว, แกนคอลัมน์, แกนหน้า และแกนข้อมูล รวมถึงสร้างรายการคำนวณหรือการแสดงค่าแบบเปอร์เซ็นต์ของยอดรวม และตัวส่วนประกอบจะเขียนข้อมูลส่วน pivotCacheDefinition และ pivotTableDefinition ที่ Excel จะเปิดเป็น Pivot ที่ใช้งานได้และสามารถรีเฟรชได้จริง

สถานการณ์ที่ทำให้โซลูชันนี้คุ้มค่าคือระบบเซิร์ฟเวอร์รายงาน คุณต้องสร้างเวิร์กบุ๊กหลายร้อยไฟล์ในแต่ละคืน โดยแต่ละไฟล์มี Pivot สรุปบัญชี และเมื่อตัวเลขเปลี่ยนแปลงในเดือนถัดไป ทุกไฟล์จะต้องสะท้อนถึงแถวแหล่งข้อมูลใหม่ การควบคุม Excel ผ่าน Windows service นั้นไม่เสถียรและไม่ได้รับอนุญาตให้ใช้บนเซิร์ฟเวอร์ ส่วนการเขียน XML ของ Pivot ด้วยตนเองก็เป็นงานค้นคว้าสเปกที่ไม่มีวันสิ้นสุด HotXLS จึงเข้ามาแก้ปัญหานี้ด้วยการนำเสนอโมเดลออบเจกต์ที่มีการกำหนดประเภทเหนือโครงสร้างส่วนประกอบของ Pivot แบบ OOXML ช่วยให้โค้ด Pascal ชุดเดียวกันที่ใช้ถมข้อมูลลงในเซลล์สามารถประกาศ Pivot และสร้างแคชใหม่ในโปรเซสเดียวกันได้

จะสร้าง Pivot Table ใน Delphi โดยไม่ใช้ Excel ได้อย่างไร?

คุณสามารถสร้างขึ้นมาได้ด้วยการเรียกใช้งานเพียงครั้งเดียวแล้วนำฟิลด์ไปวางบนแกนต่างๆ โดย AddPivotTable จะรับช่วงแหล่งข้อมูลในรูปแบบพิกัด A1, เซลล์ปลายทางที่เป็นจุดยึดตาราง และชื่อ เมธอดจะวิเคราะห์โครงสร้างช่วงข้อมูล สแกนแต่ละคอลัมน์เพื่อระบุประเภทข้อมูล สร้างแคชของ Pivot (หรือนำแคชที่ผูกกับช่วงข้อมูลเดียวกันมาใช้ใหม่) และส่งคืนออบเจกต์ TXLSPivotTable ที่ฟิลด์ทั้งหมดยังไม่ได้จัดวางบนแกน จากนั้นใช้วิธีอำนวยความสะดวกอย่าง AddRowField, AddColumnField, AddPageField และ AddDataFieldByName เพื่อจัดวางเค้าโครง และฟิลด์ข้อมูลแต่ละฟิลด์จะใช้หนึ่งใน 11 รูปแบบการจัดกลุ่มจาก enum TXLSPivotAggregation (ได้แก่ xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP)

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;

เหตุใดแคชและตารางจึงแยกเป็นสองส่วนประกอบ

Pivot Table ประกอบด้วยสองส่วนประกอบที่อ้างอิงถึงกันและกัน และการทำความเข้าใจความต่างนี้จะช่วยให้ส่วนที่เหลือเข้าใจได้ง่าย ส่วน pivotCacheDefinition คือส่วนบันทึกข้อมูลภาพถ่าย (snapshot): มี worksheetSource ที่ชี้ไปยังช่วงข้อมูลต้นทาง และมี cacheField หนึ่งตัวต่อคอลัมน์เพื่อจัดเก็บค่าที่ไม่ซ้ำกันของคอลัมน์นั้น (เรียกว่า sharedItems) พร้อมขอบเขตข้อมูลที่ดึงมา ส่วน pivotTableDefinition คือมุมมองการแสดงผล: ว่าฟิลด์แคชตัวใดวางอยู่บนแกนใด, ฟิลด์ข้อมูลและรูปแบบการจัดกลุ่มข้อมูล ตลอดจนการสลับเค้าโครง มุมมองจะผูกกับแคชด้วยแอตทริบิวต์ cacheId ผ่านความสัมพันธ์ pivotCaches ของเวิร์กบุ๊ก ตามที่กำหนดไว้ใน ECMA-376 ส่วนที่ 1 §18.10 และ [MS-XLSX]

การอ้างอิงทางอ้อมนี้ไม่ใช่ขั้นตอนที่ซ้ำซ้อน แต่มอบข้อดีสองประการ: ข้อแรก แคชเดียวสามารถสนับสนุนตารางข้อมูลได้หลายตาราง ดังนั้นการรีเฟรชแคชเพียงครั้งเดียวจะอัปเดตทุกมุมมองที่สร้างจากแคชนี้ ข้อสอง แคชจะจัดเก็บค่าของแต่ละคอลัมน์แบบลบข้อมูลซ้ำด้วยตารางดัชนีต่อระเบียนแทนที่จะเป็นตารางกริดดิบ ซึ่งเป็นสาเหตุที่การสร้าง Pivot หมายถึงการสแกนแหล่งข้อมูลไม่ใช่การคัดลอกเซลล์ HotXLS จำลองโครงสร้างนี้ในลักษณะเดียวกันสำหรับทั้งสองเอนจิน โค้ดด้านบนจึงเกือบจะเหมือนกับพาธแบบคลาสสิกที่ระบุไว้ใน the binary SX-record layout behind classic .xls pivots หากแหล่งข้อมูลของคุณอยู่ในชีตอื่นหรือคุณอ้างอิงผ่านชื่อ ช่วงนำหน้าและ defined names and cross-sheet references จะใช้กฎการเขียนพิกัด A1 ทั่วไป โดยอนุญาตให้ใส่เครื่องหมายอัญประกาศในชื่อชีตที่มีช่องว่างได้

รายการคำนวณไม่ใช่ฟิลด์คำนวณ

คำศัพท์ทั้งสามนี้ระบุถึงสิ่งที่แตกต่างกันสามประการ และการสับสนปนเปกันคือข้อผิดพลาดคลาสสิกของ Pivot รายการคำนวณ (calculated item) จะอยู่ในฟิลด์เดียวและรวมข้อมูลของฟิลด์นั้นๆ ตามชื่อ ตัวอย่างเช่น ภายในฟิลด์ Region คุณสามารถกำหนดแถวสังเคราะห์ CoreMarkets ที่มีค่าเท่ากับ North บวก South โดย HotXLS จะแสดงสิ่งนี้ผ่าน TXLSPivotField.AddCalculatedItem and เขียน <calculatedItem> ภายใต้ <calculatedItems> ของฟิลด์นั้น ส่วนฟิลด์คำนวณ (calculated field) นั้นต่างออกไป: มันคือค่าใหม่ที่คำนวณจากคอลัมน์อื่น เช่น Margin ที่คำนวณจาก Revenue และ Cost และทำงานเป็นสูตรบนฟิลด์แคชผ่าน TXLSPivotCacheField.Formula สำหรับสมาชิกคำนวณ (calculated member) ที่เพิ่มด้วย TXLSPivotTable.AddCalculatedMember คือสมาชิกที่กำหนดเองในระดับตารางที่ทำงานเป็นค่าวัด (ประเภทสมาชิก data) หรือสมาชิกมิติ (dimension member) ซึ่งมักใช้กับ Pivot รูปแบบ 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;

ข้อจำกัดที่พึงตระหนักร่วมกันสำหรับทั้งสามประเภทคือ HotXLS จะเขียนข้อความสูตรลงใน XML คำจำกัดความเท่านั้น ไม่ได้ทำการคำนวณผลลัพธ์ โปรแกรม Excel จะเป็นผู้ประมวลผลรายการ ฟิลด์ หรือสมาชิกคำนวณเมื่อเปิดไฟล์ ในลักษณะเดียวกับการคำนวณค่าผลรวมในตาราง เนื่องจาก HotXLS ทำหน้าที่เขียนชุดคำสั่ง ไม่ใช่การเขียนผลลัพธ์ ดังนั้นสูตรที่คุณระบุจะต้องถูกต้องตามหลักสูตร Pivot ในไวยากรณ์ของ Excel โดยอ้างอิงชื่อฟิลด์และรายการตามแบบที่ Excel คาดหวัง

จะแสดงค่าเป็นเปอร์เซ็นต์ของยอดรวมได้อย่างไร?

คุณสามารถตั้งค่าโหมดการแสดงผลค่าบนฟิลด์ข้อมูลได้โดยตรง แทนที่จะต้องเขียนโค้ดแปลงตัวเลขด้วยตัวเอง โดย TXLSPivotDataField.ShowDataAs จะรับค่า enum TXLSPivotShowDataAs ซึ่งสะท้อนค่าของ ST_ShowDataAs ใน OOXML: ได้แก่ xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal และ xlpsdaIndex เทคนิคที่ใช้บ่อยคือการวางคอลัมน์แหล่งข้อมูลเดียวกันบนแกนข้อมูลสองครั้ง ครั้งแรกเป็นผลรวมดิบ และครั้งที่สองเป็นสัดส่วนของยอดรวมหลัก เพื่อให้รายงานแสดงทั้งตัวเลขจริงและน้ำหนักเปอร์เซ็นต์

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;

การจัดกลุ่มวันที่และตัวเลข ตลอดจนตัวกรองฟิลด์หน้า

การจัดกลุ่มจะถูกกำหนดบนฟิลด์แคช ไม่ใช่ฟิลด์ Pivot เนื่องจากโครงสร้างนี้จะเป็นการเปลี่ยนวิธีแบ่งกลุ่มขอบเขตแหล่งข้อมูล กำหนด HasGroup := True บนฟิลด์แคชที่ส่งคืนจาก Cache.FindFieldByName จากนั้นเลือกค่าช่วงตัวเลข (GroupStartNum, GroupEndNum, GroupInterval) หรือเลือกแฟล็กโครงสร้างวันที่ (GroupByDate ร่วมกับ GroupMonths, GroupQuarters, GroupYears และช่วง GroupStartDate / GroupEndDate) HotXLS จะส่งออกองค์ประกอบ <fieldGroup><rangePr groupBy="months"/> ที่เกี่ยวข้องเพื่อให้ฟิลด์จัดกลุ่มตามเดือนหรือตามช่วงตัวเลขห่างทีละ 1000 แสดงผลตามที่ Excel จัดกลุ่มไว้

ฟิลด์หน้า (page fields) คือรายการตัวกรองแบบดึงลง (dropdown) ด้านบนของตาราง โดย AddPageField จะวางฟิลด์บนแกนหน้า และ PageItemIndex จะเลือกรายการแคชล่วงหน้า มีค่าเริ่มต้นเป็น xlPageItemAll ($7FFD หมายถึง (All)) หากต้องการให้ผู้อ่านเลือกได้หลายรายการพร้อมกัน ให้ตั้งค่า MultipleItemSelectionAllowed := True ซึ่ง HotXLS จะเขียนในรูปแบบ <pivotField multipleItemSelectionAllowed="1"/> สำหรับเกณฑ์การกรองที่ซับซ้อนกว่าการเลือกด้วยตนเอง ทุกฟิลด์จะมีคอลเลกชัน Filters ที่ครอบคลุมตระกูล ST_FilterType ของ OOXML — เช่น ตัวกรองจำนวน, เปอร์เซ็นต์, ผลรวม, คำอธิบายภาพ, ค่า และวันที่ โดยแต่ละรายการจะจับคู่ประเภทตัวกรองกับค่าเปรียบเทียบของมัน

จะรีเฟรชแคช Pivot เมื่อแหล่งข้อมูลเปลี่ยนแปลงได้อย่างไร?

เมธอด RefreshPivotCache บน TXLSXWorkbook จะทำการสแกนช่วงแหล่งข้อมูลใหม่และสร้างแคชใหม่ในพื้นที่เดิม ซึ่งเป็นสิ่งที่กระบวนการทำงานแบบชุด (batch pipeline) ต้องการหลังจากแก้ไขแถวข้อมูลหลัก ส่งค่าไอดีของแคชเข้าไป และเมธอดจะอ่านทุกเซลล์ในช่วงแหล่งข้อมูลใหม่ (แถวแรกเป็นส่วนหัว แถวที่สองขึ้นไปเป็นข้อมูล) สร้างขอบเขตรายการร่วมของแต่ละฟิลด์ใหม่โดยลบค่าซ้ำและคำนวณขอบเขตแต่ละประเภทใหม่ และเขียนข้อมูลดัชนีรายการของแต่ละระเบียนใหม่ ฟังก์ชันจะส่งคืนค่า 1 เมื่อสำเร็จ และส่งคืน -1 เมื่อไม่พบไอดีแคชหรือชีตแหล่งข้อมูลสูญหาย

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;

ขอบเขตการทำงานในจุดนี้เป็นสิ่งที่ควรชี้แจงอย่างชัดเจน ก่อนที่จะมีเมธอดนี้ HotXLS จะพึ่งพาแฟล็ก refreshOnLoad="1" และปล่อยให้หน้าที่การรีเฟรชเป็นของ Excel เมื่อมีการเปิดไฟล์ในครั้งถัดไป ซึ่งอาจจะใช้ได้เมื่อมีมนุษย์เป็นผู้เปิดดูไฟล์ แต่ไม่มีประโยชน์สำหรับกระบวนการทำงานแบบ headless ที่ต้องส่งต่อข้อมูลที่ถูกต้อง เมธอด RefreshPivotCache จะทำให้แคชที่บันทึกไว้เป็นปัจจุบันภายในโปรเซสการทำงาน และเนื่องจากตารางผูกกับแคชด้วยรหัสไอดี Pivot ทุกตัวที่สร้างจากแคชนั้นจะมองเห็นการอัปเดตนี้ด้วย สิ่งที่เมธอดนี้ไม่ได้ทำคือการจัดวางโครงร่างหรือสรุปยอดในตารางกริดที่มองเห็น — Excel จะยังคงเป็นผู้คำนวณสรุปผลตารางใหม่จากแคชที่รีเฟรชแล้วเมื่อมีการเปิดไฟล์ขึ้นมา

สิ่งที่ HotXLS เขียนและสิ่งที่ Excel ประมวลผล

เมื่อเข้าใจสัดส่วนการแบ่งงานนี้แล้ว คุณจะไม่พบกับเรื่องน่าประหลาดใจในการทำงาน HotXLS ทำหน้าที่เป็นผู้เขียนข้อกำหนด: โดยเป็นผู้สร้าง pivotCacheDefinition, ระเบียนแคช และ pivotTableDefinition ที่สมบูรณ์พร้อมด้วยแกนข้อมูล, รูปแบบการจัดกลุ่ม, รายการและสมาชิกคำนวณ, การแสดงผลค่า, การจัดกลุ่ม และตัวกรอง ส่วน Excel จะทำหน้าที่คำนวณ: เมื่อเปิดไฟล์ Excel จะสร้างกลุ่มจัดหมวดหมู่ ประมวลผลสูตรคำนวณ ใช้ตัวแปลงข้อมูล showDataAs และทำการจัดกลุ่มสรุปผลข้อมูล ค่าต่างๆ ที่ผู้ใช้เห็นจะเป็นของ Excel ซึ่งคำนวณจากชุดคำสั่งที่ HotXLS เขียนขึ้น นี่คือสาเหตุที่ทุกสูตรและดัชนีฐานข้อมูลจะต้องถูกต้องตั้งแต่ขั้นตอนที่เขียนข้อมูล ไม่ใช่การมาตรวจสอบในขั้นตอนเรนเดอร์

มีข้อจำกัดสองประการที่ควรทราบก่อนตัดสินใจออกแบบระบบโดยใช้ฟีเจอร์นี้ เมธอด AddPivotTable จะวิเคราะห์ช่วง A1 รูปสี่เหลี่ยมผืนผ้าโดยมีข้อมูลนำหน้าชื่อชีตเป็นตัวเลือกเพิ่มเติม ส่วนช่วงข้อมูลที่เป็นชื่อ (named-range) และแหล่งข้อมูลภายนอกจากเวิร์กบุ๊กอื่นจะอยู่นอกเหนือการประมวลผลของตัวสร้าง แต่แคชที่อ่านจากไฟล์ที่สร้างด้วย Excel จะสามารถรักษารูปแบบชื่อช่วงข้อมูลแหล่งต้นทางในการแปลงข้อมูลไปกลับได้ นอกจากนี้ Pivot ที่สร้างจาก Excel ที่ HotXLS อ่านเข้ามาจากดิสก์จะถูกบันทึกกลับไปแบบไบต์ต่อไบต์เมื่อบันทึกไฟล์ ทำให้การแก้ไขผ่านโค้ดที่อธิบายในที่นี้ใช้ได้กับ Pivot ที่คุณสร้างขึ้นใหม่ในโค้ดเท่านั้น ในขณะที่ Pivot เดิมในไฟล์จะไม่มีข้อมูลสูญหายเลย สำหรับข้อมูลอินพุตของรายงาน — เช่น เซลล์ที่มีการตรวจสอบข้อมูลและความถูกต้อง ตลอดจนตารางข้อมูลที่ผ่านตัวกรองที่ Pivot นำมาสรุปผล — สามารถดูรายละเอียดได้ในหัวข้อ data validation, AutoFilter, and structured tables

โมเดล Pivot ที่แสดงในที่นี้เป็นส่วนหนึ่งของมาตรฐาน HotXLS Delphi Excel Component สำหรับ Delphi และ C++Builder ซึ่งสามารถอ่านและเขียนทั้งส่วนของ Pivot แบบกำหนดประเภทข้อมูลของ XLSX และระเบียน BIFF8 แบบเก่าได้จากโมเดลออบเจกต์ชุดเดียวกัน