ชื่อที่กำหนดและสูตรข้ามแผ่นงานใน Delphi ด้วย HotXLS

defined name คือป้ายชื่อที่ใช้แทนค่าคงที่ ช่วงเซลล์ หรือนิพจน์สูตร ถูกเก็บไว้ครั้งเดียวใน workbook แล้วอ้างอิงแบบสัญลักษณ์ได้ทุกที่ที่ต้องการ เขียน TaxRate ในสูตร แล้วเครื่องคำนวณจะ resolve มันไปยังสิ่งที่นิยามของชื่อนั้นเก็บไว้ ไม่ว่าจะเป็นค่า literal 0.08 หรือ range Data!$A$2:$D$100 reference ข้าม sheet เป็นแนวคิดที่ตั้งฉากกัน: Data!D2 เข้าถึงเซลล์บน sheet อื่นด้วยการระบุ address พร้อมชื่อ sheet เอาสองอย่างนี้มารวมกัน แล้ว sheet สรุปก็จะรวมยอดจาก sheet รายละเอียดผ่านชื่อที่ไม่เคยพูดถึง address ตรง ๆ เลยแม้แต่อันเดียว ซึ่งเป็นสิ่งที่คุณต้องการเป๊ะ ๆ ใน workbook ที่ generator ประกอบขึ้นแล้วนักบัญชีมาตรวจสอบภายหลัง

HotXLS ไลบรารี Delphi แบบ native ของ losLab สำหรับไฟล์ XLS และ XLSX เปิดตาราง name ของทั้งสองฟอร์แมตให้เข้าถึงได้ทั้งสร้าง ค้นหา และลบ พร้อมเครื่องคำนวณสูตรที่ resolve ชื่อและ reference ข้าม sheet ได้ในตัวมันเอง ทั้งสองฟอร์แมตมี class hierarchy แยกจากกัน และความต่างระหว่าง API ของชื่อทั้งสองแบบนี้คือส่วนที่ทำให้โค้ดที่ย้ายจากแบบหนึ่งไปอีกแบบหนึ่งสะดุด

ที่เก็บชื่อสองที่ที่ไม่ใช้ interface ร่วมกัน

ฝั่ง XLS, TXLSWorkbook.GetNames คืน collection IXLSNames ที่มี overload Add(Name, RefersTo, Visible) เขียนชื่อลงในตาราง name ของ BIFF รายการแต่ละอันคืนกลับมาเป็น object IXLSName ที่พก Name, RefersTo, RefersToRange ที่ resolve แล้ว และเมธอด Delete ฝั่ง XLSX, TXLSXWorkbook.DefinedNames คือ collection แบบ TXLSXDefinedNames ที่มี Add, FindByName และ DeleteByName

ธรรมเนียมการค้นหาแตกต่างกันในแบบที่จะโผล่ขึ้นมาระหว่างการย้ายโค้ด ไม่ใช่ตอน compile property Item เริ่มต้นของ collection ฝั่ง XLS รับ Variant ดังนั้นทั้ง Names[0] และ Names['TaxRate'] resolve กับมันได้ collection ฝั่ง XLSX ไม่มี property เริ่มต้นแบบนั้นเลย คุณต้องเรียก FindByName('TaxRate') ซึ่งคืนค่า nil เมื่อไม่มีชื่อนั้น โค้ดที่เขียนสำหรับส่วนหน้าหนึ่งจะ compile ผ่านกับอีกส่วนหน้าได้แค่โดยบังเอิญ และความล้มเหลวมักโผล่ขึ้นมาเป็น nil access ตอน runtime แทนที่จะเป็นเส้นหยักสีแดงใน IDE

Scope คือการตัดสินใจแรก ไม่ใช่ flag ที่เพิ่มทีหลัง

defined name เป็นได้ทั้ง workbook-scoped ที่มองเห็นได้จากสูตรบนทุก sheet หรือ sheet-scoped ที่มองเห็นได้แค่จากสูตรบน sheet เจ้าของมันเท่านั้น ใน API ของ XLSX ความต่างนี้คือพารามิเตอร์ทางเลือกตัวเดียว DefinedNames.Add(AName, AFormula) สร้างชื่อระดับ workbook ในขณะที่ Add(AName, AFormula, ASheetIndex) ผูกมันเข้ากับหนึ่ง sheet เมื่ออ่านกลับมา TXLSXDefinedName.SheetIndex คืน -1 สำหรับ scope ระดับ workbook และคืน index ของ sheet แบบเริ่มนับที่ 0 สำหรับกรณีอื่น

scope ทำหน้าที่เป็นนโยบายการชนกันของคุณไปในตัวด้วย และนั่นคือเหตุผลที่ควรตกลงมันก่อนที่คุณจะเขียนชื่อแรก Excel อนุญาตให้มี Total แบบ sheet-local บนทุก sheet บวกกับ Total ระดับ workbook ได้ และสูตรบน sheet หนึ่งจะ resolve ตัวที่ local ก่อนเสมอ workbook ที่สร้างขึ้นควรพึ่งพากลไกนี้อย่างตั้งใจ ข้อสมมติทางธุรกิจที่หลาย sheet ใช้ร่วมกัน เช่น อัตราภาษี อัตราแลกเปลี่ยน และรอบการรายงาน ควรอยู่ที่ scope ระดับ workbook ส่วน range ตัวช่วยที่มีแค่สูตรของ sheet เดียวอ้างอิงถึงจะปลอดภัยกว่าถ้าเป็น sheet-scoped ที่ซึ่งไม่มีอะไรมาบดบังมันได้ และมันก็บดบังอะไรไม่ได้เช่นกัน

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... เติมแถวรายละเอียดลงใน Data!A2:D100 ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // scope ระดับ workbook, ค่าคงที่
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // scope ระดับ workbook, range
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // ผูก scope ไว้กับ sheet index 1 เท่านั้น

    // สูตรของ XLSX ไม่ต้องมี '=' นำหน้า
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

defined name ไม่จำเป็นต้องชี้ไปที่ range เสมอไป TaxRate ข้างบนอ้างถึงค่าคงที่ดิบ ๆ 0.08 และนั่นคือวิธีที่สะอาดที่สุดในการเผยแพร่ข้อสมมติทางธุรกิจ มันปรากฏครั้งเดียวใน Name Manager ของ Excel ทุกสูตรอ้างอิงมันแบบสัญลักษณ์ และการเปลี่ยนอัตราในไตรมาสหน้าก็เป็นแค่การแก้ generator บรรทัดเดียว แทนที่จะต้องค้นหาข้ามสตริงสูตรที่ประกอบไว้สิบสี่ชุด

เครื่องหมายเท่ากับที่ควรอยู่แค่ด้านเดียว

ช่องทางป้อนสูตรคือจุดที่โค้ดที่ย้ายมาพังบ่อยที่สุด เพราะทั้งสองส่วนหน้าไม่เห็นตรงกันเรื่องเครื่องหมายเท่ากับ เซลล์ XLS รับสูตรผ่าน Value โดยมี = นำหน้า เซลล์ XLSX มี property Formula เฉพาะของมันเองที่รับนิพจน์ โดยไม่มี เครื่องหมายนำหน้า เขียน '=SUM(A1:A10)' ลงใน TXLSXCell.Formula แล้วเครื่องหมายเท่ากับจะกลายเป็นส่วนหนึ่งของข้อความนิพจน์ที่ถูกเก็บไว้แทนที่จะเป็นตัวบ่งชี้ และไฟล์จะไม่ทำงานแบบเดียวกับที่สตริงเดียวกันนั้นทำงานฝั่ง XLS

var
  Book: IXLSWorkbook;   // นับ reference ผ่าน interface: ห้าม Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // สมมติว่า sheet ชื่อ 'Data' มีแถวรายละเอียดอยู่แล้ว
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = ซ่อนจาก Name Manager

  // สูตรของ XLS ไปผ่าน Value โดยมี '=' นำหน้า
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

ตัวอย่างนั้นแสดงลูกเล่นเฉพาะของ XLS อีกสองอย่าง collection ของ sheet เริ่มนับที่ 1 ดังนั้น Sheets[1] คือ sheet แรก ต่างจาก Sheets[0] ของ XLSX ที่เริ่มนับที่ 0 และพารามิเตอร์ที่สามของ Add สร้างชื่อแบบ ซ่อน: มีอยู่ในไฟล์และสูตรใช้งานได้ แต่มองไม่เห็นใน Name Manager ของ Excel ชื่อที่ซ่อนอยู่คือพาหนะที่เหมาะสมสำหรับกลไกภายในของ generator ที่ผู้ใช้ปลายทางไม่ควรแก้หรือลบโดยบังเอิญ

reference ข้าม sheet และสิ่งที่เกิดขึ้นเมื่อแถวถูกย้าย

เครื่องคำนวณสูตรทั้งสองตัวรับ syntax มาตรฐานสำหรับข้าม sheet ชื่อ sheet ธรรมดาระบุตรง ๆ ได้เป็น Data!A1 ส่วนชื่อที่มีช่องว่างหรือเครื่องหมายวรรคตอนต้องใส่เครื่องหมายคำพูดเดี่ยวครอบ อย่าง 'Sheet With Space'!A1 ภายในข้อความ RefersTo ของชื่อ ให้ใช้ reference แบบ absolute อย่าง Data!$A$2:$D$100 เกือบทุกครั้ง reference แบบ relative ภายใน defined name จะ resolve สัมพัทธ์กับเซลล์ที่ใช้มัน ซึ่งเป็นฟีเจอร์ของ Excel ที่ตั้งใจออกแบบไว้ และเป็นแหล่งความสับสนที่เชื่อถือได้เมื่อมันทำงานโดยไม่ตั้งใจ

การแก้ไขเชิงโครงสร้างคือจุดที่การจัดบัญชีข้าม sheet แสดงคุณค่าของมัน และฝั่ง XLSX รักษาชื่อให้สอดคล้องกันตลอดกระบวนการนั้น InsertRows และ DeleteRows เลื่อน range ของ defined name ไปพร้อมกับเซลล์ การ merge hyperlink และจุดยึดของแผนภูมิ ดังนั้นชื่อที่ชี้ไปที่ Data!$A$2:$D$100 จะยังครอบคลุมบล็อกข้อมูลได้ต่อไปแม้ generator จะเปิดช่องว่างไว้ข้างบนมัน สูตรมาพร้อมข้อควรระวังที่มีเอกสารรองรับหนึ่งข้อ: การแทรกแถวปรับแค่ reference ที่เล็งไปที่ sheet ที่กำลังถูกแก้ไขเท่านั้น สูตรของ Summary ที่อ้างอิง Data!D2:D100 จะถูกเขียนใหม่เมื่อมีแถวถูกแทรกเข้าไปใน Data ซึ่งเป็นกรณีที่คุณต้องการโดยปกติ ตรวจสอบมันแทนที่จะสันนิษฐานเอาเอง เพราะเครื่องคำนวณจะบอกคุณได้ในราคาถูก:

// เครื่องคำนวณ resolve ชื่อและ reference ข้าม sheet ได้ในตัวมันเอง
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate evaluate นิพจน์ใด ๆ ก็ได้กับสถานะปัจจุบันของ workbook โดยไม่ save อะไรเลย ซึ่งทำให้มันเป็น assertion primitive ตามธรรมชาติสำหรับทดสอบ generator คำนวณ aggregate ที่คาดไว้จากข้อมูลต้นทางใน Pascal, evaluate สูตรของ workbook เอง แล้วเทียบทั้งสองค่า บทความเรื่องเครื่องคำนวณสูตร ครอบคลุมว่าเครื่องคำนวณ evaluate อะไร เมื่อไหร่ และวิธีขยายมันด้วยฟังก์ชันกำหนดเอง

ชื่อ _xlnm ที่ชั้น property เป็นเจ้าของ

เปิดตาราง name ของไฟล์ที่สร้างขึ้นด้วยตัวตรวจสอบระดับต่ำ แล้วคุณจะพบรายการที่คุณไม่เคยเขียนเอง: _xlnm.Print_Area, _xlnm.Print_Titles และญาติของมัน นี่คือวิธีที่ OOXML (ECMA-376 / ISO 29500) เก็บพื้นที่พิมพ์และแถวหัวเรื่องที่ทำซ้ำ โดยเก็บเป็น defined name ที่มี identifier สงวนไว้ HotXLS จัดการมันผ่าน property เฉพาะของ worksheet ดังนั้นการตั้งค่า PrintArea หรือ PrintTitleRows จะเขียนรายการ _xlnm.* ที่สอดคล้องกันให้คุณ

กับดักคือการเอื้อมมือเข้าไปแตะ namespace ที่สงวนไว้นั้นเอง เพิ่มรายการ _xlnm.Print_Area ผ่าน DefinedNames.Add ในขณะที่ก็ตั้งค่า property PrintArea ด้วย แล้ว workbook ก็จะพกนิยามที่ขัดแย้งกันสองชุดสำหรับชื่อสงวนชื่อเดียว ซึ่งเป็นสถานะที่ Excel แก้ปัญหาด้วยวิธีที่ไม่มีผลิตภัณฑ์ไหนควรพึ่งพา ปฏิบัติต่อทุก identifier ที่ขึ้นต้นด้วย _xlnm. เหมือนเป็นของชั้น property ในการตรวจสอบการตั้งค่าพิมพ์ ให้อ่าน property ไม่ใช่ตาราง name บทความเรื่องการป้องกันและการตั้งค่าหน้ากระดาษ ครอบคลุม property ของพื้นที่พิมพ์ในบริบทนั้น

ขอบเขตสองอย่างที่ควรรู้ก่อนตัดสินใจออกแบบ

defined name ไม่ได้ติดตามไปด้วยผ่าน bridge สะดวก ๆ จาก XLS ไปยัง XLSX SaveXLSWorkbookAsXLSX คัดลอกเนื้อหาเซลล์และ formatting พื้นฐาน แต่ตาราง name ไม่อยู่ในรายการคัดลอกที่มีเอกสารรองรับของมัน ดังนั้น workbook ที่พึ่งพาชื่อของมันจะสูญเสียมันไปตอนข้ามฟอร์แมต สร้างชื่อขึ้นใหม่ผ่าน DefinedNames.Add หลังการแปลง ขั้นตอนนั้นไม่ใช่งานจุกจิกอย่างที่ฟังดู เพราะมันให้โอกาสคุณปรับ scope ของชื่อให้เป็นมาตรฐานแทนที่จะติดตามสิ่งที่ไฟล์ XLS บังเอิญมีอยู่

ขอบเขตอีกอย่างคือการเบี่ยงเบนระหว่างสตริงสูตรกับชื่อ sheet Excel เขียน reference ของ sheet ใหม่ทั้งภายในสูตรและชื่อระหว่างการเปลี่ยนชื่อแบบ interactive ดังนั้นไฟล์ที่ผู้ใช้แก้ไขใน Excel จึงยังคงสอดคล้องกันได้เอง จุดเปราะบางอยู่ที่ฝั่ง generator: เมื่อโค้ด Pascal ประกอบสตริงสูตรจากค่า literal ของชื่อ sheet การเปลี่ยนชื่อ sheet ที่จุดหนึ่งแล้วลืมอีกจุดหนึ่งจะสร้าง reference ไปยัง sheet ที่ไม่มีอยู่แล้ว เก็บชื่อ sheet ไว้ใน Delphi constant ตัวเดียว แล้วป้อนมันให้ทั้ง Sheets.Add และการประกอบสูตรของคุณ แล้วทั้งสองจะไม่มีวันขัดแย้งกัน นี่คือสัญชาตญาณเดียวกับที่สนับสนุนให้ตั้งชื่อเซลล์ output ของรายงานแทนที่จะเขียน address แบบ hard-code: template ที่เซลล์ผลรวมถูก ตั้งชื่อ ไว้จะยังทำงานต่อได้แม้นักออกแบบจะแทรกสามแถวไว้ข้างบนมัน ในขณะที่ generator ที่เขียนลง B17 ตรง ๆ จะปล่อยตัวเลขของมันลงผิดที่อย่างเงียบ ๆ บทความเรื่องการสร้างรายงานจาก template ต่อยอดจากรูปแบบนั้นเป๊ะ ๆ

API ของ defined name ฉบับเต็มสำหรับทั้งสองฟอร์แมต พร้อมเอกสารอ้างอิงเครื่องคำนวณสูตร มาพร้อมกับ HotXLS Delphi Component