การส่งออกผลลัพธ์ฐานข้อมูล Delphi ไปยังรายงาน Excel ด้วย HotXLS

การเปลี่ยนผลลัพธ์ query ให้เป็นรายงาน Excel คือสามปัญหาที่สวมเสื้อคลุมตัวเดียวกัน ชนิดฟิลด์แต่ละแบบของ Delphi ต้องลงเซลล์เป็นชนิด Excel ที่ถูกต้อง แถวหัวตารางต้องอ่านเหมือนรายงาน ไม่ใช่การทิ้ง schema ออกมาดิบ ๆ และตัวเลข วันที่ และเงินต้องพกรูปแบบที่รอดจากการเดินทาง ข้ามข้อใดข้อหนึ่งไป ไฟล์ก็ยังเปิดได้ ยังดูน่าเชื่อถือ และยังคงล้มเหลวทันทีที่ผู้ใช้ฝ่ายการเงินเลือกคอลัมน์แล้วรอผลรวมที่ไม่มีวันปรากฏขึ้น ค่าเหล่านั้นถูกเขียนเป็นข้อความ Excel จึงมองมันเป็นแค่ label และไม่มี exception ไหนถูกเรียกขึ้นมาเตือนคุณเลย

HotXLS เป็นไลบรารี spreadsheet แบบ Object Pascal ที่เขียนไฟล์ XLS และ XLSX ตรงจาก Delphi และ C++Builder ได้เลย โดยไม่ต้องพึ่ง Excel automation มันเสนอสองเส้นทางจาก TDataset ไปสู่ workbook: component สำเร็จรูป TDataToXLS และ loop ที่เขียนมือกับ workbook API ทั้งสองไม่สามารถใช้แทนกันได้ component เป็นพลเมือง VCL เต็มตัวที่สร้างบนส่วนหน้า XLS ดังนั้นทางเลือกที่ถูกต้องขึ้นอยู่กับว่าโค้ดรันที่ไหนและผู้บริโภคต้องการ file format แบบไหน เนื้อหาต่อจากนี้คือทั้งสองเส้นทาง จุดที่ component หยุดเป็นเครื่องมือที่ถูกต้อง และวิธีรักษาชนิดฟิลด์ให้ครบไม่ว่าคุณจะเลือกแบบไหน

ชนิดฟิลด์คือ contract ที่แท้จริงของการ export

ก่อนเรียก API ใด ๆ ให้ตัดสินใจก่อนว่าชนิดฟิลด์แต่ละแบบของ Delphi จะลงเซลล์อย่างไร เซลล์ที่รับสตริงของ Delphi จะยังเป็นสตริงต่อไป HotXLS ไม่เดาว่า '1,234.50' ตั้งใจจะเป็นตัวเลข และมันไม่ควรเดาด้วย เพราะการ reparse ตาม locale คือวิธีที่แน่นอนที่สุดที่ทำให้ comma ทศนิยมแบบเยอรมันกลายเป็นตัวคั่นหลักพันบนเซิร์ฟเวอร์ภาษาอังกฤษ รูปแบบที่เชื่อถือได้คือกำหนดค่าผ่าน typed accessor: AsFloat หรือ AsCurrency สำหรับฟิลด์ตัวเลข AsDateTime สำหรับวันที่ เพื่อให้เซลล์เก็บ Excel date serial ของจริง ไม่ใช่สตริงที่ format ไว้แล้ว และ AsString เฉพาะกับฟิลด์ที่เป็นข้อความจริง ๆ เท่านั้น

การจัดการค่า null สมควรได้รับการตัดสินใจอย่างชัดเจน ไม่ใช่ปล่อยตามค่าเริ่มต้น การแปลงค่าฟิลด์ด้วย VarToStr จะเปลี่ยน SQL NULL ให้กลายเป็นสตริงว่างเปล่า ซึ่งคือเซลล์ข้อความ ในขณะที่การข้ามการกำหนดค่าไปเลยจะปล่อยให้เซลล์ว่างเปล่าจริง ๆ ซึ่งเป็นสิ่งที่ AVERAGE, COUNT และผู้บริโภคแบบ pivot table คาดหวัง สำหรับคอลัมน์เงิน ตัดสินใจก่อนที่จะเขียน loop ว่า NULL หมายถึงศูนย์หรือไม่ทราบค่า ทั้งสองแบบแสดงผลเหมือนกันทันทีที่ใครสัก format คอลัมน์นั้น และความต่างนี้เปลี่ยนทุก aggregate ที่คำนวณต่อไปในปลายทาง

เส้นทาง component: TDataToXLS ในแอปพลิเคชัน VCL

สำหรับแอปพลิเคชัน VCL แบบคลาสสิกที่มี query ต่อเข้ากับ data module อยู่แล้ว TDataToXLS คือเส้นทางแบบเรียกครั้งเดียวจบ มันเดิน TDataset descendant ตัวไหนก็ได้ ไม่ว่าจะเป็น FireDAC, ADO, IBX หรืออะไรก็ตามที่ implement abstract dataset interface และผลิต worksheet ที่มีสไตล์ พร้อม caption หัวตาราง ฟอนต์ เส้นขอบ ผลรวมย่อยตามกลุ่มแบบเลือกได้ และการแบ่ง sheet อัตโนมัติสำหรับผลลัพธ์ชุดใหญ่

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // TDataset descendant ตัวไหนก็ได้
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // caption ไม่ใช่ชื่อคอลัมน์ดิบ
    Exporter.GroupFields.Add('CustomerID');   // บล็อกผลรวมย่อยต่อลูกค้า
    Exporter.RowsPerSheet := 50000;           // ให้ต่ำกว่าเพดานแถวของ BIFF8
    Exporter.VisibleFieldsOnly := True;             // เคารพ Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

สอง property รับน้ำหนักงานจริงส่วนใหญ่ตรงนี้ HeaderSource := hsDisplayLabel เขียน DisplayLabel ของแต่ละฟิลด์แทนที่จะเป็นชื่อคอลัมน์ SQL ดิบ ๆ ดังนั้น workbook จึงบอกว่า "Customer Name" แทนที่จะเป็น CUST_NM RowsPerSheet มีอยู่เพราะ component เขียน BIFF8 ซึ่ง grid ของมันหยุดที่ 65,536 แถวคูณ 256 คอลัมน์ การตั้งค่ามันไว้ที่ 50,000 จะแบ่งผลลัพธ์ชุดใหญ่ข้าม sheet ก่อนที่เพดาน format จะตัดมันทิ้ง รูปลักษณ์ถูกจัดการด้วย property HeaderFont, DetailFont, GroupColor และสไตล์เส้นขอบ ส่วนชุด DisableFormat ปิดหมวดหมู่การจัด format ทั้งหมวดเมื่อผู้บริโภคต้องการเซลล์เปล่า ๆ สำหรับอะไรที่ต้องปรับแต่งเฉพาะ event AfterCell และ AfterRow จะส่ง range ที่เพิ่งเขียนเสร็จให้คุณเพื่อประมวลผลต่อ

จุดที่ component หยุดอยู่แค่นั้น

มีข้อจำกัดสามอย่างที่ถูกออกแบบไว้ใน TDataToXLS ตั้งแต่ต้น และการรู้มันไว้ก่อนช่วยหลีกเลี่ยงการออกแบบใหม่แบบเก้ ๆ กัง ๆ ในอีกสอง sprint ข้างหน้า

• มันคือ VCL component แบบเต็มตัว unit ของมันดึง Forms, Controls และ Dialogs เข้ามา ดังนั้นการ link มันเข้ากับงาน console หรือ Windows service จะลาก VCL เข้าไปในไฟล์ binary ด้วย ในขณะที่ unit หลักของ workbook ไม่มี dependency แบบนั้นเลย มันต้องการแค่ Windows, Classes, SysUtils และ Variants ซึ่งเป็นเหตุผลว่าทำไมโค้ดฝั่งเซิร์ฟเวอร์ควรใช้ loop ที่แสดงด้านล่างแทน
• มันสร้างขึ้นบนส่วนหน้า XLS component เติมข้อมูลลงใน IXLSWorkbook และเขียน .xls (BIFF8) ไม่มี property ไหนที่สลับให้มันเขียน output แบบ OOXML ได้
• event ของมันพูดภาษาถิ่นของ XLS พารามิเตอร์ Cell: IXLSRange ใน AfterCell เป็นของ object model ของ XLS ดังนั้นการปรับแต่งรายเซลล์ที่เขียนตรงนั้นก็คือโค้ดสไตล์ XLS แม้ว่าไฟล์จะถูกแปลงเป็น .xlsx ในภายหลังก็ตาม

ผลิต .xlsx จาก output ของ component

เมื่อผู้บริโภคยืนยันต้องการ .xlsx แต่ตรรกะการ export อาศัยอยู่ใน TDataToXLS อยู่แล้ว ฟังก์ชัน bridge ใน unit lxXlsxExport จะแปลง workbook ที่เติมข้อมูลแล้วด้วยการเรียกครั้งเดียว:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// component เปิด IXLSWorkbook ที่มันเติมข้อมูลไว้ให้เข้าถึงได้
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

ปฏิบัติต่อ bridge นี้เหมือนเป็นตัวขนส่งข้อมูลแบบตาราง ไม่ใช่ตัวแปลงที่รักษาความเที่ยงตรงเต็มรูปแบบ มันคัดลอกค่า สูตร รูปแบบตัวเลข สี fill attribute ฟอนต์ ความกว้างคอลัมน์ และการตั้งค่ามุมมอง มันตั้งใจไม่คัดลอกเส้นขอบ range ที่ merge ไว้ comment แผนภูมิ หรือ conditional format สำหรับ grid แบนราบที่มีแค่หัวตารางบวกแถวข้อมูล นั่นเพียงพอพอดี แต่สำหรับรายงานที่มีสไตล์แล้วไม่พอ และวิธีแก้ที่ตรงไปตรงมาคือสร้าง XLSX ตรง ๆ แทนที่จะแพตช์ไฟล์ที่แปลงมาแล้ว

loop ที่เขียนมือสำหรับ service และงาน batch

โค้ดฝั่งเซิร์ฟเวอร์ควรเล็งไปที่ TXLSXWorkbook ตรง ๆ สังเกตความต่างเรื่อง lifetime ระหว่างสองส่วนหน้าก่อนจะคัดลอกตัวอย่างใดก็ตาม TXLSWorkbook ฝั่ง XLS ถูกถือไว้ผ่าน interface แบบ reference-counted และห้าม free มันเองด้วยมือ ในขณะที่ TXLSXWorkbook เป็น class ธรรมดาที่ต้องใช้ try..finally Free การปนกันระหว่างสองธรรมเนียมนี้คือวิธีที่แน่นอนที่จะสร้าง leak หรือ double-free ขึ้นมา

procedure ExportOrders(Q: TDataSet; const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 'Order No';
    Sheet.Cells[1, 2].Value := 'Customer';
    Sheet.Cells[1, 3].Value := 'Ordered';
    Sheet.Cells[1, 4].Value := 'Amount';

    Row := 2;
    Q.First;
    while not Q.Eof do
    begin
      Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
      Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
      if not Q.FieldByName('Ordered').IsNull then
        Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
      Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
      Inc(Row);
      Q.Next;
    end;

    Book.StreamingWrite := True;  // stream XML ของ sheet ตรงเข้าไปใน zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

บรรทัดที่สำคัญคือการกำหนดค่าแบบมีชนิดข้อมูลและการ์ด IsNull วันที่มาถึงในรูป date serial จำนวนเงินมาถึงในรูป double และวันสั่งซื้อที่เป็น NULL ยังคงว่างเปล่าจริง ๆ แทนที่จะกลายเป็นสตริงว่าง StreamingWrite := True เปลี่ยนแค่เส้นทางการ save เท่านั้น: XML ของ worksheet stream ตรงเข้าไปใน zip container แทนที่จะถูกประกอบเป็นสตริงก้อนใหญ่ก้อนเดียวก่อน ซึ่งทำให้ยอดพุ่งของหน่วยความจำตอน SaveAs แบนราบลงสำหรับจำนวนแถวหลักแสน เมธอด save ทุกตัวยังมี overload แบบ TStream ด้วย ดังนั้น workbook จึงส่งตรงเข้า HTTP response ได้เลยโดยไม่ต้องแตะดิสก์ บทความเรื่อง streaming write และงาน batch job เดินผ่านรูปแบบการ deploy นั้น และ บทความเรื่องประสิทธิภาพของ workbook ขนาดใหญ่ ครอบคลุมสิ่งที่ควรทำเมื่อจำนวนแถวไต่สูงขึ้นไปอีก

loop นี้ยังเป็นเส้นทางที่ scale ข้าม thread ได้ด้วย engine ทั้งสองตัวเป็นตัวเขียน Object Pascal แบบ native, BIFF8 record stream ในด้านหนึ่ง และ OOXML zip บวก XML ในอีกด้านหนึ่ง ดังนั้นไม่มีส่วนไหนของการ export แตะ COM automation หรือต้องใช้ license Excel บนเซิร์ฟเวอร์เลย สิ่งที่คุณได้จากนั้นคือความขนานกันโดยไม่มี bottleneck แบบ instance เดียว ตราบใดที่แต่ละ thread สร้าง workbook ของตัวเอง object ของ workbook ไม่ปลอดภัยต่อการใช้ร่วมกันข้าม thread ดังนั้นกฎคือหนึ่ง instance ต่อหนึ่งการ export เสมอ ห้ามใช้ตัวเดียวร่วมกันโดยมี lock คุมอยู่

มีข้อจำกัดหนึ่งที่ควรรู้ไว้ก่อนออกแบบรอบมัน grid ของ XLSX หยุดที่ 1,048,576 แถวคูณ 16,384 คอลัมน์ ดังนั้นการแบ่ง sheet ที่ RowsPerSheet จัดการฝั่ง XLS แทบไม่จำเป็นเลยตรงนี้ workbook ล้านแถวก็ไม่ค่อยเป็นสิ่งที่ผู้บริโภคที่เป็นมนุษย์ต้องการเช่นกัน เมื่อผลลัพธ์ชุดใหญ่ขนาดนั้นจริง ๆ ไฟล์แบบมีตัวคั่นมักเป็น contract ที่ดีกว่า และ บทความเรื่องการ export CSV และ TSV ครอบคลุมตัวคั่น พฤติกรรม BOM และข้อควรระวังเรื่องการ evaluate สูตรที่ใช้ได้ที่นั่น

เลือกจุดเริ่มต้น

ถ้าการ export อาศัยอยู่ในเครื่องมือ desktop แบบ VCL และ output .xls รับได้ ให้เริ่มจาก TDataToXLS และการรองรับ grouping ของมัน มันเป็นโค้ดที่น้อยที่สุด และ bridge ผ่าน SaveXLSWorkbookAsXLSX ก็พร้อมอยู่ตรงนั้นเมื่อมีใครสักคนขอ .xlsx ในภายหลัง ตราบใดที่คุณยอมรับข้อจำกัดด้านความเที่ยงตรงที่อธิบายไว้แล้ว ถ้าโค้ดรันแบบไม่มีคนดูแล หรือผู้บริโภคต้องการ .xlsx ตั้งแต่ต้น ให้เขียน loop เอง ทั้งสองเส้นทางมาพร้อม demo project ที่ใช้งานได้จริง และเป็นส่วนหนึ่งของแพ็กเกจ HotXLS Delphi Component