การสร้างไฟล์ Excel ใน Delphi โดยไม่ต้องใช้ Office Automation

ถ้างานเดียวของเซิร์ฟเวอร์คือการสร้างไฟล์ Excel เซิร์ฟเวอร์นั้นก็ไม่มีเหตุผลใดต้องรัน Excel เลย การติดตั้ง Office บน build agent หรือบริการสร้างรายงานเพื่อควบคุมมันผ่าน COM automation คือการออกแบบที่ผิดพลาด และมันเป็นการออกแบบที่ผิดพลาดมาตั้งแต่แนวปฏิบัตินี้เริ่มมีมา Microsoft เองก็ระบุไว้เช่นนั้น ในคำแนะนำที่ไม่เคยผ่อนปรนมาตลอดยี่สิบปี ว่า Office ไม่ได้ถูกสร้างขึ้นหรือได้รับอนุญาตให้ทำงานอัตโนมัติจากโพรเซสฝั่งเซิร์ฟเวอร์ที่ไม่มีคนเฝ้า คำตอบที่ถูกต้องคือการเขียนไบต์ของ BIFF และ OOXML โดยตรง โดยไม่มี Excel เข้ามาเกี่ยวข้องเลย นั่นคือหลักการทั้งหมดของ HotXLS ไลบรารี Object Pascal แบบ native ที่อ่านและเขียนฟอร์แมตสเปรดชีตด้วยตัวเอง จึงไม่มีแอปพลิเคชันเดสก์ท็อปให้ค้าง รั่วหน่วยความจำ หรือต้องจ่ายค่าไลเซนส์ต่อที่นั่ง

เหตุใดการสั่งงาน EXCEL.EXE จากบริการจึงล้มเหลว

COM automation ควบคุมโปรแกรมเดสก์ท็อปจากระยะไกล และโปรแกรมเดสก์ท็อปก็ตั้งสมมติฐานเงียบ ๆ ไว้สามอย่างที่ Windows service ไม่มีทางส่งมอบให้ได้ นั่นคือ user profile ที่โหลดไว้แล้ว interactive window station และมนุษย์ที่กำลังเฝ้าหน้าจออยู่ เมื่อตัดสามอย่างนี้ออกไป ความล้มเหลวก็จะปรากฏในรูปแบบที่เครื่องของนักพัฒนาไม่มีทางจำลองซ้ำได้เลย กล่องข้อความกู้คืนไฟล์ ข้อผิดพลาดของ add-in หรือกล่องโต้ตอบเปิดใช้งานไลเซนส์จะเปิดขึ้นบนเดสก์ท็อปที่ไม่มีใครมองเห็น และการเรียก automation ที่ทำให้มันเปิดขึ้นก็จะไม่มีวันคืนค่ากลับมา ฝั่งที่เรียกใช้ในที่สุดก็จะ time out แล้วตาย ส่วนอินสแตนซ์ของ Excel นั้นบ่อยครั้งไม่ตายตาม กลับอยู่รอดในสภาพกำพร้าที่ยังคง lock ไฟล์ไว้และทำให้การรันครั้งถัดไปเสียหายไปด้วย ใครก็ตามที่เคยเห็นโพรเซส EXCEL.EXE จรจัดสิบเอ็ดตัวกองพะเนินอยู่ใต้ service account ย่อมรู้ตอนที่เหลือของเรื่องนี้ดี

เรื่องของการ scale ก็ไม่ได้ดีไปกว่ากันแม้ในตอนที่ไม่มีอะไรพัง อินสแตนซ์ของ Excel คือ pipeline ที่ประมวลผลได้ทีละ workbook เดียว การเข้าถึง property ทุกครั้งต้องแบกต้นทุนของ COM marshaling ข้ามโพรเซส และเครื่องที่รันโค้ดอยู่ก็ถือไลเซนส์ Office ที่ข้อกำหนดไม่อนุญาตให้ใช้งานลักษณะนี้โดยตรง ทีมส่วนใหญ่เจอขีดจำกัดเหล่านี้ทีละครั้งจาก outage แต่ละครั้ง ซึ่งนั่นก็ประมาณว่าทำไม "เลิกใช้ COM layer" ถึงกลายมาเป็นรายการในโรดแมป

ก่อนเริ่มเขียนใหม่ ต้องตกลงเรื่องขอบเขตงานให้ชัดเจนก่อนหนึ่งข้อ เพราะมันจะตัดสินว่างานจริง ๆ มีมากแค่ไหน โค้ด COM แทบไม่เคยแค่ตั้งค่า cell values เฉย ๆ มันมักเรียก Workbook.SaveAs พร้อม format constants บังคับให้คำนวณใหม่ (recalculation) กำหนดค่า print setup และบางครั้งก็ไปยุ่งกับ clipboard ด้วย ให้ไล่ดูโค้ดเก่าแล้วจดไว้ว่าพฤติกรรมไหนที่จริง ๆ แล้วส่งผลถึงผลลัพธ์สุดท้าย เพราะแต่ละอย่างจะไปตกอยู่คนละมุมของไลบรารี native และบางอย่าง (ที่ชัดที่สุดคือ clipboard interop) ก็ไม่มีความหมายใด ๆ ในฝั่งเซิร์ฟเวอร์เลย ควรตัดทิ้งไปมากกว่าจะย้ายมาด้วย

เอนจิ้น native สองตัว สองรูปแบบการจัดการความเป็นเจ้าของ

HotXLS แทนที่โพรเซส Excel ด้วยการ implement รูปแบบไฟล์โดยตรงสองแบบ เอนจิ้น BIFF8 record-stream (TXLSWorkbook ในยูนิต lxHandle) จัดการไฟล์ .xls ส่วนตัวเขียนแพ็กเกจ OOXML (TXLSXWorkbook ในยูนิต lxHandleX) สร้างไฟล์ .xlsx ที่สอดคล้องกับมาตรฐาน ECMA-376 / ISO/IEC 29500 ไม่มีอะไรต้อง register และไม่มีอะไรต้องติดตั้งบนเซิร์ฟเวอร์ และคุณสามารถเปิด workbook ค้างไว้พร้อมกันได้มากเท่าที่หน่วยความจำจะรองรับ

สิ่งที่ทำให้คนสะดุดตั้งแต่แรกคือ facade ทั้งสองแบบจัดการความเป็นเจ้าของหน่วยความจำต่างกัน และความต่างนี้จะเงียบสนิทจนกว่าโปรแกรมจะพัง:

var
  Book: IXLSWorkbook;          // interface reference: จะถูกปล่อยคืนอัตโนมัติ
  Sheet: IXLSWorksheet;
  BookX: TXLSXWorkbook;        // plain object: คุณต้อง free เอง
  SheetX: TXLSXWorksheet;
begin
  // เอาต์พุต BIFF8 .xls - ไม่ต้อง Free; refcount ของ interface เป็นเจ้าของแทน
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.Name := 'Report';
  Sheet.Cells.Item[1, 1].Value := 'Generated without Excel';
  Book.SaveAs('report.xls');

  // เอาต์พุต OOXML .xlsx - lifetime ต้องจัดการเอง
  BookX := TXLSXWorkbook.Create;
  try
    SheetX := BookX.Sheets.Add('Report');
    SheetX.Cells[1, 1].Value := 'Generated without Excel';
    BookX.SaveAs('report.xlsx');
  finally
    BookX.Free;
  end;
end;

facade ของ XLS นับ reference ผ่าน interface IXLSWorkbook ให้ประกาศตัวแปรเป็นชนิด interface แล้วอย่าเรียก Free กับมันเด็ดขาด ถ้าคุณเก็บอ็อบเจ็กต์เดียวกันไว้ในตัวแปรชนิด plain object แล้วไปเรียก free เองอีกที refcount ก็จะไป free มันซ้ำเป็นครั้งที่สอง ส่วน facade ของ XLSX เป็นอ็อบเจ็กต์ธรรมดาที่ต้องการ try..finally แบบปกติ การอ้างอิงเซลล์เริ่มที่ 1 (1-based) เหมือนกันทั้งสองฝั่ง ซึ่งเป็นจุดเดียวที่ทั้งคู่ตรงกัน แต่ collection ของ sheet ไม่ตรงกัน: ฝั่ง XLS นั้น Entries เริ่มที่ 1 ส่วน indexer Items ของ XLSX เริ่มที่ 0 และความคลาดเคลื่อนหนึ่งค่านี้ (off-by-one) จะคอมไพล์ผ่านฉลุยไม่ว่าคุณจะพลาดไปทางไหน แล้วจะโผล่ให้เห็นตอนรันไทม์เท่านั้น

การเขียน workbook ลงตรงใน HTTP response

การ export ฝั่งเซิร์ฟเวอร์โดยทั่วไปไม่มีเหตุผลต้องแตะดิสก์เลย ไฟล์ temp ต้องมีนโยบายเก็บกวาด อาจชนกันเมื่อมี concurrent request และปล่อยให้ข้อมูลลูกค้าตกค้างอยู่บน volume ที่ไม่มีใครคิดจะตรวจสอบ facade ทั้งสองแบบรับ TStream ผ่าน overload ของ SaveAs ได้ ทำให้ workbook ไหลตรงเข้าสู่ response ได้เลย:

Mem := TMemoryStream.Create;
Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Data');
  Sheet.Cells[1, 1].Value := 'Generated ' + DateTimeToStr(Now);
  Book.SaveAs(Mem);          // เขียนจากตำแหน่งปัจจุบันของ stream
  Mem.Position := 0;         // rewind ก่อนส่งมอบ stream ออกไป
  Response.ContentType :=
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
  Response.ContentStream := Mem;   // ตอนนี้ framework เป็นเจ้าของ Mem แล้ว
finally
  Book.Free;
end;

บรรทัด rewind นี่แหละคือบรรทัดที่สมควรมีคอมเมนต์กำกับ SaveAs(Stream) เขียนจากตำแหน่งปัจจุบันของ stream และไม่เคย seek กลับไปที่ศูนย์ให้เองทีหลัง ถ้าลืม Mem.Position := 0 ลูกค้าก็จะได้ไฟล์ดาวน์โหลดขนาดศูนย์ไบต์ หรือไม่ Excel ก็จะบอกว่าไฟล์เสีย นี่คือบั๊กที่พบบ่อยที่สุดในโค้ด workbook ฝั่งเว็บ และเป็นบั๊กที่โหดร้ายที่สุดด้วย เพราะมันแล่นผ่าน unit test ใด ๆ ที่แค่ตรวจว่า stream มีความยาวไม่เป็นศูนย์ไปได้อย่างสบาย

รูทีนสร้าง workbook ตัวเดียวสามารถไปถึงฟอร์แมตการส่งมอบอื่น ๆ ได้ทั้งหมดโดยไม่ต้องปรับโครงสร้างใหม่ SaveAsCSV ตอบโจทย์คำขอแบบ "ขอแค่ raw data" SaveAsHTML จัดการกรณี "เอาไปแปะในหน้าพอร์ทัล" SaveAsRTF ป้อนเข้า pipeline ของเอกสาร และ SaveAsODS ครอบคลุมข้อกำหนดของ OpenDocument ทั้งหมดนี้มี overload ทั้งแบบไฟล์และแบบ stream รูทีน export ตัวเดียวบวกกับพารามิเตอร์ฟอร์แมตมาแทนที่สิ่งที่เคยเป็น COM macro แยกกันสี่ตัว TXLSXHtmlExportOptions ของตัว export HTML มี title, CSS class และสวิตช์เลือกระหว่าง fragment หรือเอกสารเต็ม ซึ่งทำให้กรณีพอร์ทัลไม่ต้องไปยุ่งกับการแก้ markup ที่ export ออกมาด้วย regex

ค่าจากสูตรโดยไม่ต้องมีโพรเซส Excel มาคำนวณให้

ภายใต้ COM automation นั้น Excel คำนวณทุกอย่างให้ฟรี ๆ และการเลิกใช้ COM ก็เพิกถอนสิทธิ์นั้นไปอย่างเงียบ ๆ SaveAs เก็บสูตรไว้เป็นข้อความโดยไม่ประเมินค่า ตัวเลขจะปรากฏก็ต่อเมื่อ Excel เปิดไฟล์แล้วคำนวณใหม่เท่านั้น ซึ่งพฤติกรรมนี้ facade ของ XLS ให้คุณปรับแต่งได้ผ่าน RecalcOnSave และ CalculationMode สำหรับไฟล์ที่จะส่งให้คนอ่าน พฤติกรรมนี้ถูกต้องพอดี แต่มันผิดสำหรับบริการที่ต้องยืนยันยอดรวมก่อนส่งออก และผิดสำหรับการ export เป็น CSV ซึ่งจะเขียนข้อความสูตรแทนที่จะเป็นผลลัพธ์ ทั้งสองกรณีต้องประเมินค่าบนเซิร์ฟเวอร์ด้วยเอนจิ้นในตัวเอง:

SheetX.Cells[1, 1].Value := 1200;
SheetX.Cells[2, 1].Value := 950;
SheetX.Cells[3, 1].Formula := 'SUM(A1:A2)';   // facade ของ XLSX: ไม่มีเครื่องหมาย '=' นำหน้า
Total := BookX.Calculate('SUM(A1:A2)');       // ประเมินค่าบนเซิร์ฟเวอร์ตอนนี้เลย
if Total <> 2150 then
  raise Exception.Create('reconciliation failed before delivery');

ธรรมเนียมของ facade มากัดอีกครั้งตรงนี้ ฝั่ง XLSX กำหนด expression ผ่าน Cell.Formula โดยไม่มีเครื่องหมายเท่ากับ ส่วนฝั่ง XLS เขียนผ่าน Cell.Value โดยมี '=' นำหน้า ถ้าย้ายโค้ดจากฝั่งหนึ่งไปอีกฝั่งโดยไม่แก้ไข ธรรมเนียมที่ผิดจะเก็บข้อความที่แค่ดูคล้ายสูตรเอาไว้ โดยไม่มี error ใด ๆ มาเตือน เมื่อสูตรใน workbook ต้องเชื่อมเข้ากับ business logic ของคุณเอง callback OnUserFunction จะให้เอนจิ้นส่งชื่อฟังก์ชันที่มันไม่รู้จักไปให้โค้ด Delphi จัดการตอนประเมินค่า นั่นคือตัวแทนแบบ native ของ add-in ประเภท UDF ที่มักซ่อนตัวอยู่ในสเปรดชีตแบบเดียวกับที่ระบบ COM-automation เติบโตขึ้นมาด้วย

จุดขอบของการ deploy ที่โผล่มาให้เห็นเฉพาะบนเซิร์ฟเวอร์เท่านั้น

รายละเอียดไม่กี่อย่างเป็นตัวตัดสินว่าการ rollout จะราบรื่นหรือชวนงุนงง และอย่างแรกคือ unit graph ตัว export dataset แบบ drag-and-drop TDataToXLS ดึงเอา VCL Forms, Controls และ Dialogs เข้ามาด้วย ไม่มีปัญหาอะไรในเครื่องมือเดสก์ท็อป แต่ในบริการแบบ console มันจะลาก VCL ทั้งชุดตามเข้ามาด้วย ยูนิตหลักอย่าง lxHandle และ lxHandleX อ้างถึงแค่ Windows, Classes, SysUtils และ Variants เท่านั้น ดังนั้นบริการที่ต้องการความบริสุทธิ์ก็ควรเขียน dataset loop ของตัวเองด้วย core API มากกว่าจะ import คอมโพเนนต์เข้ามาเพื่อความสะดวก

จากนั้นก็เรื่อง threading อินสแตนซ์ของ workbook ไม่ thread-safe แต่ก็ไม่ได้แชร์ global state ใด ๆ เช่นกัน ดังนั้นแพทเทิร์นที่ scale ได้ก็คือแบบง่ายที่สุด นั่นคือ workbook object หนึ่งตัวต่อหนึ่งงาน หรือต่อหนึ่ง worker thread วิธีนี้ทำให้สร้างรายงานแบบขนานได้ ซึ่งอินสแตนซ์ Excel ที่แชร์กันตัวเดียวไม่มีวันทำได้ ตัวจัดการ request ที่สร้าง เติมข้อมูล บันทึก และ free workbook ของตัวเองไม่ต้องใช้ lock เลยแม้แต่ตัวเดียว และรัศมีความเสียหายเมื่อเกิดปัญหาก็ลดลงจาก "อินสแตนซ์ Excel ที่แชร์กันติดขัดสำหรับทุกคน" เหลือแค่ "request นี้ตัวเดียวที่โยน exception" ซึ่งเป็นสิ่งที่ error handling เดิมของคุณรู้อยู่แล้วว่าต้องจัดการอย่างไร

เรื่องสุดท้ายคือการเลือกฟอร์แมตเป้าหมาย TXLSWorkbook.SaveAs เขียนเป็น BIFF (xlExcel97) โดยค่าเริ่มต้น และการดันเนื้อหา XLS เข้าไปเป็น .xlsx จะวิ่งผ่านสะพาน SaveXLSWorkbookAsXLSX ซึ่งความแม่นยำลดลง ให้เลือก facade ตามฟอร์แมตที่ตั้งใจจะส่งมอบตั้งแต่ตอนออกแบบ ดีกว่าสร้างด้วยแบบหนึ่งแล้วค่อยแปลงตอนท้าย pipeline

สำหรับครึ่งหนึ่งของโปรเจ็กต์ทดแทนทั่วไปที่เป็นการโหลดข้อมูล รูปแบบการ export จากฐานข้อมูลไปเป็น workbook ครอบคลุมทั้งการใช้คอมโพเนนต์และการเขียน loop เอง และเมื่อจำนวนแถวไต่ถึงหลักแสน เทคนิคด้านประสิทธิภาพของ workbook ขนาดใหญ่ ก็จะกลายเป็นตัวตัดสินระหว่างหลักนาทีกับหลักวินาที ส่วนรายงานที่สร้างจาก layout ที่ดูแลผ่าน designer ครอบคลุมอยู่ใน คู่มือการสร้างรายงานด้วย template

HotXLS เผยแพร่เป็นซอร์สโค้ด Object Pascal สำหรับ Delphi และ C++Builder ส่วนรุ่น (edition) การออกไลเซนส์ และ API reference ฉบับเต็มอยู่ที่ หน้าผลิตภัณฑ์ HotXLS Delphi Component