เอ็นจิ้นสูตร HotXLS และฟังก์ชันที่กำหนดเองใน Delphi

ไลบรารี spreadsheet ที่แค่เก็บสตริงสูตร กับไลบรารีที่มีเครื่องคำนวณสูตรที่ทำงานได้จริง คือสองผลิตภัณฑ์ที่แตกต่างกัน แต่ดูเหมือนกันจนกว่าจะถึงจุดที่คุณขอตัวเลขจากมันสักตัว โค้ด spreadsheet ของ Delphi ส่วนใหญ่ไม่เคยสังเกตเห็นช่องว่างนี้ เพราะ Excel กลบมันไว้ให้: เขียน SUM(B2:B501) ลงในเซลล์ save แล้ว Excel ก็คำนวณผลรวมใหม่ทันทีที่มนุษย์คนหนึ่งเปิดไฟล์ ตัดมนุษย์ออกจากวงจร แล้วรัน workbook เดียวกันนั้นผ่าน pipeline บนเซิร์ฟเวอร์ที่ export ตรงไปเป็น CSV ความต่างนั้นก็เลิกเป็นเรื่องวิชาการทันที ไฟล์ CSV จะพกข้อความดิบ =SUM(B2:B501) ในตำแหน่งที่ควรเป็นตัวเลข เพราะไม่มีจุดไหนเลยที่มีอะไรมา evaluate สูตรนั้นจริง ๆ

นั่นคือเส้นแบ่งที่ HotXLS อยู่ฝั่งที่ถูกต้อง มันปฏิบัติต่อสูตรแบบเดียวกับที่ file format ทำ คือเป็นข้อความที่เก็บไว้บวกกับผลลัพธ์ที่แคชไว้แบบเลือกได้ ดังนั้นการ export CSV เปล่า ๆ จะสร้างสูตรออกมาซ้ำ ไม่ใช่ผลลัพธ์ แต่มันก็พก calculation engine ที่คุณเรียกตรงได้ด้วย เครื่องคำนวณตัวเดียวกันในทั้งส่วนหน้า XLS และ XLSX บวกกับ hook สำหรับ resolve ชื่อฟังก์ชันที่เครื่องคำนวณไม่เคยรู้จักมาก่อน HotXLS คือไลบรารี Object Pascal แบบ native ที่อ่านและเขียน XLS และ XLSX จาก Delphi และ C++Builder ได้โดยไม่ต้องพึ่ง Excel automation และครึ่งหนึ่งของมันที่เป็นการคำนวณคือสิ่งที่เปลี่ยนสูตรที่เก็บไว้กลับเป็นค่าได้ตามต้องการ

สูตรถูกเก็บไว้ ไม่ได้ถูก evaluate ทันที

การเขียนสูตรลงในเซลล์ไม่ได้คำนวณอะไรเลย ตอน save workbook จะบันทึกแค่ข้อความสูตร ฝั่ง XLS มันยังบันทึก flag ที่ควบคุมโดย RecalcOnSave ซึ่งค่าเริ่มต้นคือ True และบอก Excel ให้คำนวณใหม่ตอนเปิด โมเดลนั้นถูกต้องสำหรับไฟล์ที่จะไปสู่ Excel แต่ผิดสำหรับ pipeline ที่บริโภคค่าเซลล์โดยตรง ไม่ว่าจะเป็นการ export CSV, HTML หรือโค้ดของคุณเองที่อ่านเซลล์กลับมา สำหรับกรณีเหล่านั้น ให้ evaluate อย่างชัดเจนด้วย Calculate มันมีอยู่ที่จุดเข้าถึงสี่จุด: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook และ TXLSXWorksheet ทั้งหมดเปิด function Calculate(const Formula: WideString): Variant

// evaluate ในโปรเซสเดียวกัน แล้วส่งค่าออกไป ไม่ใช่ส่งสูตร
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // ตอนนี้ CSV พกตัวเลขแล้ว

นิพจน์ที่ส่งให้ Calculate คือข้อความสูตร Excel ธรรมดา reference ข้าม sheet, defined name และฟังก์ชันที่ซ้อนกันทั้งหมด resolve กับสถานะ workbook ที่อยู่ในหน่วยความจำปัจจุบัน ซึ่งทำให้ call นี้มีประโยชน์เกินกว่าแค่แก้ปัญหาการ export CSV มากทีเดียว ปฏิบัติต่อมันเหมือนกลไก assertion generator ที่เพิ่งเขียนแถวรายละเอียดห้าร้อยแถวไปสามารถขอให้ workbook คำนวณยอดรวมทั้งหมดของมันเอง แล้วเทียบกับตัวเลขที่มันคำนวณแยกไว้ใน Pascal ได้ จับข้อผิดพลาดของ range ที่คลาดไปหนึ่งได้ก่อนที่ผู้ตรวจสอบบัญชีของลูกค้าจะเจอ

มันยังกำหนดกลยุทธ์การทดสอบที่ถูกต้องสำหรับ output ที่หนักไปทางสูตรด้วย Excel ยังคงเป็น reference implementation ของภาษาสูตร ดังนั้นสำหรับสูตรจำนวนหนึ่งที่มีผลทางธุรกิจ ให้เก็บไฟล์ fixture ที่ได้รับการอนุมัติแล้ว ซึ่งค่าที่คาดหวังไว้ถูกผลิตโดย Excel เอง แล้วให้ build pipeline evaluate สูตรของ workbook ที่สร้างขึ้นด้วย Calculate เทียบกับ fixture เหล่านั้น ความต่างจะโผล่ขึ้นมาเป็น test ที่ล้มเหลวใน Delphi แทนที่จะเป็นความคลาดเคลื่อนที่ลูกค้าค้นพบจากการเทียบรายงานสองฉบับ

เพิ่มฟังก์ชันทางธุรกิจด้วย OnUserFunction

เมื่อเครื่องคำนวณเจอชื่อฟังก์ชันที่มันไม่รู้จัก มันจะยิง event ขึ้นมาแทนที่จะ fail ตรง ๆ กำหนดค่า OnUserFunction บน workbook class ตัวไหนก็ได้ แล้วคุณก็ resolve call นั้นได้ด้วยตัวเอง:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args มาถึงในรูป Variant array
    Handled := True;
  end;
end;

// การผูกและการใช้งาน
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

มีรายละเอียดสามอย่างที่ควรใส่ใจ อย่างแรก ตั้ง Handled := True เฉพาะเมื่อคุณรู้จักชื่อนั้นจริง ๆ เท่านั้น ปล่อยมันไว้เป็น False จะให้เครื่องคำนวณดำเนินการจัดการฟังก์ชันที่ไม่รู้จักตามปกติต่อไป ดังนั้น handler ตัวเดียวจึงรองรับหลาย workbook ได้โดยไม่ต้องอ้างสิทธิ์ทุกอย่างที่ผ่านเข้ามา อย่างที่สอง เทียบชื่อแบบไม่สนตัวพิมพ์ใหญ่เล็กด้วย SameText เพราะผู้เขียนสูตรพิมพ์ discount( และ DISCOUNT( สลับกันได้ อย่างที่สาม argument มาถึงแบบ evaluate ไว้ล่วงหน้าแล้ว: DISCOUNT(A1) ส่งค่าของ A1 มาให้คุณ ไม่ใช่ reference ดังนั้นฟังก์ชันจึงบอกไม่ได้ว่า input ของมันมาจากไหน จุดสุดท้ายนี้แหละที่นำไปสู่ข้อจำกัดที่หัวข้อถัดไปจะพูดถึง

ปฏิบัติต่อเนื้อหาของ handler ด้วยความระมัดระวังแบบเดียวกับจุดเข้าถึงภายนอกอื่น ๆ array Args สะท้อนสิ่งที่ผู้เขียนสูตรพิมพ์เข้ามา ดังนั้นตรวจสอบจำนวนและชนิดของ argument ก่อนที่จะเข้าถึงมันด้วย index แล้วตัดสินใจล่วงหน้าว่า call ที่ไม่ถูกต้องจะคืนอะไร: ค่า error แบบ Variant หรือการ raise exception ทางเลือกนี้สำคัญ เพราะ exception ที่ถูก throw ภายใน handler จะแพร่กระจายออกไปผ่าน call Calculate ที่กระตุ้นการ evaluate นั้น ซึ่งยอมรับได้ใน generator ที่ควบคุมแน่นหนา แต่หยาบคายในบริการที่ evaluate workbook ที่ผู้ใช้เขียนขึ้นเอง ที่ซึ่งสูตรผิดตัวเดียวจะทำให้ทั้ง request ล้มไปด้วย ในสถานการณ์แบบนั้น ให้ดักภายใน handler แล้วคืนค่า sentinel ที่ workflow รอบนอกรู้จักและ log ได้

ฟังก์ชันที่รู้ตำแหน่งของตัวเองต้องใช้ตัวแปร Ex

ฟังก์ชันบางตัวมีเหตุผลที่ต้องขึ้นอยู่กับว่ามันถูก evaluate ที่ไหน อัตราที่ต่างกันไปตาม sheet, การค้นหาแบบสัมพัทธ์กับแถว, ตัวคูณเฉพาะภูมิภาคที่ใช้ได้แค่บน sheet ของภูมิภาคนั้น: ไม่มีอันไหนตอบได้ด้วยค่า argument เพียงอย่างเดียว event แบบธรรมดาแสดงสิ่งนั้นไม่ได้ ดังนั้นเครื่องคำนวณจึงมี OnUserFunctionEx ให้ ซึ่งเหมือนกันทุกอย่างยกเว้นพารามิเตอร์เพิ่มอีกหนึ่งตัว:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // สูตรเดียวกันให้อัตราต่างกันบนแต่ละ sheet ของภูมิภาค
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext พก SheetIndex, Row และ Col ของเซลล์ที่กำลังถูก evaluate ถ้าผลลัพธ์ของฟังก์ชันขึ้นอยู่กับตำแหน่งของมันแม้เพียงเล็กน้อย ให้ผูก event แบบ Ex ตั้งแต่ต้น การย้อนกลับไปเพิ่ม context ให้ handler ที่สามสิบสูตรเรียกใช้อยู่แล้วยุ่งยากกว่าการเลือก signature ที่ถูกต้องตั้งแต่วันแรกมาก และ event ทั้งสองก็คล้ายกันมากจนแทบไม่มีเหตุผลจะเริ่มด้วยตัวที่แคบกว่า

ฟังก์ชันกำหนดเองไม่เดินทางไปถึง Excel

ฟังก์ชันกำหนดเองอาศัยอยู่ในโปรเซสของคุณทั้งหมด ชื่อ DISCOUNT มีความหมายก็ต่อเมื่อโค้ด Delphi ของคุณและ event handler ของมันกำลังรันอยู่เท่านั้น เปิดไฟล์ที่ save แล้วใน Excel และ DISCOUNT ก็จะเป็นแค่ชื่อที่ไม่รู้จัก เซลล์จะแสดง #NAME? เว้นแต่จะมีฟังก์ชัน VBA หรือ add-in ที่ตรงกันอยู่บนเครื่องผู้ใช้บังเอิญพอดี นี่คือข้อเท็จจริงเชิงการออกแบบที่แยก demo ออกจากผลิตภัณฑ์ที่ส่งมอบได้จริง และมันบังคับให้คุณต้องตัดสินใจอย่างตั้งใจแทนที่จะมาค้นพบทีหลัง

ตัดสินใจทีละเซลล์ว่าคุณกำลังส่งมอบ contract แบบไหนในสองแบบนี้ เซลล์ที่ผู้ใช้ควรเห็นการคำนวณใหม่ภายใน Excel ต้องสร้างจากคลังคำศัพท์ฟังก์ชันของ Excel เองเท่านั้น ไม่มีอย่างอื่น เซลล์ที่ตรรกะเป็นความลับทางธุรกิจควร evaluate ในโปรเซสด้วย Calculate แล้วบันทึกไว้เป็นค่าธรรมดา เพื่อให้ฟังก์ชันกำหนดเองทำหน้าที่เป็นกฎการคำนวณภายในแทนที่จะเป็นเนื้อหาของไฟล์ รูปแบบความล้มเหลวที่สร้าง support ticket ได้อย่างเสมอต้นเสมอปลายคือทางสายกลาง: บันทึกสูตรของฟังก์ชันกำหนดเองไว้ แล้วคาดหวังให้ Excel ยอมรับมัน

มีข้อดีเงียบ ๆ อยู่ใน contract แบบเก็บเฉพาะค่า: มันปกป้องทรัพย์สินทางปัญญา กฎการตั้งราคาที่ evaluate ในโปรเซส Delphi ของคุณแล้วส่งออกมาเป็นตัวเลข ไม่สามารถ reverse-engineer จาก workbook แบบเดียวกับที่ทำได้กับสูตรที่มองเห็นได้ และผู้ใช้ก็ทำให้มันพังด้วยการแก้เซลล์ตัวกลางไม่ได้ ตัวสร้างใบแจ้งหนี้ ใบแจ้งค่าคอมมิชชัน และตารางอัตราแทบทุกครั้งควรอยู่ในกลุ่มนี้ กรณีที่ต้องการสูตรที่มีชีวิตจริง ๆ คือโมเดล what-if แบบ interactive ที่ลูกค้าคาดว่าจะเปลี่ยน input แล้วดูยอดรวมขยับ และกรณีเหล่านั้นต้องสร้างจากคลังคำศัพท์ของ Excel เองบวกกับ defined name

โหมดการคำนวณ, การวนซ้ำ และ R1C1: ปุ่มปรับของส่วนหน้า XLS

ส่วนหน้าของ XLS เปิดการตั้งค่าการคำนวณระดับ BIFF ที่ Excel อ่านจากไฟล์ให้ใช้ CalculationMode รับค่า xlCalcManual, xlCalcAutomatic (ค่าเริ่มต้น) หรือ xlCalcAutomaticExceptTables และมันกำหนดว่า Excel จะทำงานอย่างไรเมื่อไฟล์ถูกเปิด workbook แบบโมเดลที่มีสูตรหลายพันตัวมักเป็นมิตรกว่าถ้าส่งมอบในโหมด manual เพื่อให้ผู้รับเป็นคนตัดสินใจว่าพายุการคำนวณใหม่จะเกิดขึ้นเมื่อไหร่ EnableIteration (ค่าเริ่มต้น False) พร้อมกับ MaxIterations (ค่าเริ่มต้น 100) และ MaxIterationChange (ค่าเริ่มต้น 0.001) ปลดล็อก circular reference แบบตั้งใจชนิดที่ลู่เข้าแบบวนซ้ำ ซึ่งพบได้ในโมเดลการเงินบางแบบ ReferenceStyle สลับระหว่างการแสดงผลแบบ A1 กับ R1C1 และ UseFullPrecision สะท้อนตัวเลือก precision-as-displayed ของ Excel

property เหล่านี้อยู่บนส่วนหน้าของ XLS เพราะมัน map ไปยัง BIFF record เมื่อสร้าง .xlsx ให้วางแผนสูตรไม่ให้ขึ้นอยู่กับการตั้งค่าแบบวนซ้ำ หรือไม่ก็คำนวณค่าที่ลู่เข้าแล้วใน Delphi แล้วเขียนผลลัพธ์ลงไป

สูตร array: จุดเข้าถึงสาธารณะคือ XLSX

สูตร array แบบ CSE ดั้งเดิมถูกสร้างผ่าน TXLSXRange.SetArrayFormula:

// สูตร array เดียวที่ครอบคลุม A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

เมธอดที่เทียบเท่ากันมีอยู่ใน class hierarchy ของ XLS แต่อยู่ใน section แบบ private ดังนั้นจึงไม่มีวิธีที่รองรับอย่างเป็นทางการในการเขียนสูตร array ใหม่ลงไปในไฟล์ .xls สูตร array ที่มีอยู่แล้วในไฟล์ที่เปิดมาจะรอด round-trip ได้อย่างสมบูรณ์ สิ่งที่คุณทำไม่ได้คือสร้างมันขึ้นมาใหม่ กฎที่ตามมาก็ง่ายพอ: เมื่อความหมายแบบ array เป็นส่วนหนึ่งของความต้องการ ให้เล็งไปที่ .xlsx ถ้า deliverable แบบ .xls รุ่นเก่าต้องการพฤติกรรมแบบ array จริง ๆ วิธีที่ใช้ได้จริงคือคำนวณผลลัพธ์ของ array ใน Delphi แล้วเขียนค่าแต่ละตัวลงในเซลล์เอง

มีบทความที่เกี่ยวข้องอีกสองบทความในเว็บนี้: defined name และสูตรข้าม sheet ครอบคลุมการ resolve ชื่อที่เครื่องคำนวณทำ และ บทความเรื่องการ export CSV และ TSV อธิบายพฤติกรรม export ที่ทำให้การคำนวณอย่างชัดเจนจำเป็น เอกสารอ้างอิงเครื่องคำนวณฉบับเต็ม รวมถึงชุดฟังก์ชันที่รองรับ มาพร้อมกับ HotXLS Delphi Component