การ Copy ข้าม Workbook และการ Rebind สูตรของ HotXLS ใน Delphi

method AddCopy ของ HotXLS copy เวิร์กชีตจาก workbook Excel หนึ่งไปยังอีกอันหนึ่ง ด้วยการ decompile ทุกสูตรบนชีตนั้นให้เป็นข้อความสไตล์ A1 แล้ว recompile ข้อความนั้นภายใน workbook ปลายทาง แทนที่จะ copy formula tree ที่ compile แล้วโดยตรง เพราะการอ้างอิง chart series, ดัชนีฟอนต์ของ rich text และการกำหนดหมายเลข external-link ล้วนถูกกำหนดอย่างอิสระภายในไฟล์ workbook แต่ละไฟล์

ความล้มเหลวปรากฏขึ้นตรงใน workbook ที่คุณคาดไว้พอดี งาน month-end ที่ดึงหนึ่งชีตออกจากรายงานของทุกสาขาแล้วต่อท้ายเข้าไฟล์สรุป เปิดผลลัพธ์และกราฟ subtotal พล็อตตัวเลขของสาขาที่ต่างไปโดยสิ้นเชิง บันทึกที่เคยตัวหนาและสีแดงในต้นฉบับกลับเป็นข้อความสีดำธรรมดา และสูตรที่เคยดึงอัตราภาษีจาก workbook ค้นหาคู่หูตอนนี้แสดงตัวเลขแช่แข็งที่ไม่มีใครอธิบายได้ ไม่มีอะไร throw exception ตรงนี้เลย ไฟล์เปิดได้ ตัวเลขดูสมเหตุสมผล และความเสียหายก็อยู่ตรงนั้นจนกว่าจะมีใครสังเกตเห็นกราฟที่มีชื่อผิดอยู่ข้างๆ มัน

ทำไม AddCopy ถึง copy compiled formula tree ตรงๆ ไม่ได้

AddCopy ไม่สามารถย้าย compiled formula tree โดยไม่เปลี่ยนแปลงได้ เพราะสูตร BIFF ที่ compile แล้วไม่ใช่ข้อความอิสระ มันเป็นลำดับของ token และ token หลายตัวเป็นจำนวนเต็มเล็กๆ ที่ resolve ถูกต้องก็ต่อเมื่ออยู่ภายใน workbook ที่สร้างมันขึ้นมาเท่านั้น การอ้างอิงแบบ 3D เช่น Sheet2!A1:A10 ไม่ได้พกชื่อ Sheet2 ตรงตัวเมื่อมันถูก compile แล้ว มันพก field ที่สเปค BIFF เรียกว่า ixti (HotXLS เก็บค่าเดียวกันไว้ใน compiled tree ของตัวเองภายใต้ชื่อ field FExternID) เป็นดัชนีเข้าไปใน table EXTERNSHEET ส่วนตัวของ workbook นั้น กำหนดหมายเลขตามลำดับที่ workbook นั้นบังเอิญลงทะเบียนชีตและ external book ของมัน ย้าย token โดยไม่เปลี่ยนแปลงเข้าไปใน workbook ที่ table EXTERNSHEET ของมันสร้างขึ้นในลำดับที่ต่างออกไป แล้วดัชนี 3 ก็ไม่ได้หมายถึง Sheet2 อีกต่อไป มันหมายถึงชีตใดก็ตามที่บังเอิญครองช่อง 3 อยู่ที่นั่น และ Excel ไม่มีทางแจ้งความผิดพลาดได้เลย เพราะเท่าที่รูปแบบไฟล์เข้าใจ สูตรก็มีรูปแบบที่ถูกต้องสมบูรณ์แบบ นี่คือความล้มเหลวที่ TXLSWorksheets.AddCopy มีอยู่เพื่อหลีกเลี่ยงพอดี เรียกจาก collection ชีตของ workbook ใดตัวหนึ่งในโค้ด Delphi หรือ C++Builder มันจะ copy เวิร์กชีต ค่าเซลล์, format, สูตร, กราฟ, ความคิดเห็น, การรวม, การตั้งค่าหน้า และอื่นๆ จาก workbook ต้นทางที่อาจเป็นหรือไม่เป็นตัวที่คุณกำลังเรียกมันอยู่ก็ได้ และต่อท้ายผลลัพธ์เข้าไปยังปลายทางภายใต้ชื่อที่คุณเลือกหรือสำเนาที่แก้ความกำกวมของต้นฉบับ

var
  Summary, Branch: IXLSWorkbook;   // interface-counted: do not Free
begin
  Summary := TXLSWorkbook.Create;
  Branch := TXLSWorkbook.Create;
  Branch.Open('branch-east.xls');

  // Appends a copy of Branch's first sheet onto Summary, renamed to
  // stay unique inside the destination workbook
  Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
  Summary.SaveAs('consolidated.xls');
end;

ทางแก้: decompile เป็นข้อความ compile ใหม่ที่ปลายทาง

HotXLS แก้ปัญหาการทำดัชนีด้วยการไม่ปล่อยให้ compiled tree เองข้ามขอบเขต workbook เลย สำหรับทุกเซลล์สูตรในการ copy ข้าม workbook AddCopy decompile สูตรต้นทางเป็นข้อความสไตล์ A1 เดียวกับที่ผู้ใช้จะเห็นใน formula bar ของ Excel แล้วส่งข้อความนั้นให้ workbook ปลายทาง ซึ่ง parse มันกลับเป็น tree โดยใช้ table ของตัวเองตั้งแต่ต้น การอ้างอิงที่ระบุชีตอย่าง Data!D2:D100 ก็แค่ string ณ จุดนั้น และ string มีความหมายเดียวกันในทุก workbook ดังนั้นถ้าปลายทางมีชีตชื่อ Data อยู่แล้ว การอ้างอิงจะ resolve ถูกต้องโดยไม่ต้องแปลงดัชนีเลย เพราะไม่เคยมีดัชนีดิบลอยอยู่ให้แปลงตั้งแต่แรก HotXLS จ่ายค่าใช้จ่ายสำหรับการไปกลับนี้ก็ต่อเมื่อมันต้องทำเท่านั้น การ copy ชีตภายใน workbook เดียวกันใช้เส้นทางที่ถูกกว่า ที่ compiled tree แค่ถูก duplicate ในหน่วยความจำ เพราะทุกดัชนีข้างในมันถูกต้องอยู่แล้วในที่ที่มันจะอยู่ต่อ และการวนผ่านข้อความจะรันก็ต่อเมื่อ AddCopy ตรวจพบว่าต้นทางกับปลายทางเป็น workbook instance ที่ต่างกันจริงๆ เท่านั้น คุ้มค่าที่จะพูดให้ชัดเจนด้วยว่าการเขียนใหม่นี้ไม่ใช่อะไร มันไม่เกี่ยวอะไรเลยกับการเลื่อนแถวและคอลัมน์ที่รันเมื่อคุณแทรกหรือลบแถวภายในชีตเดียว ซึ่งบทความคู่กันครอบคลุมไว้อย่างละเอียด เอนจิ้นนั้นเขียนข้อความ A1 ใหม่ตรงที่เพื่อติดตามเซลล์ที่ขยับขึ้นหรือลงไม่กี่แถวภายใน workbook เดียว ในขณะที่ตัวนี้รันเมื่อสูตรออกจาก workbook ที่ compile มันทั้งหมด ซึ่งแถวที่ขยับไม่ใช่ปัญหา แต่การกำหนดหมายเลขที่เป็นส่วนตัวของ workbook คือปัญหา

// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);

ถ้าปลายทางยังไม่มีชีตนั้น หรือชื่อนั้น จะเกิดอะไรขึ้น

การ recompile ของ AddCopy จะสำเร็จก็ต่อเมื่อ workbook ปลายทางมีทุกอย่างที่ข้อความสูตรอ้างอิงถึงอยู่แล้วเท่านั้น และช่องว่างสองอย่างที่ปรากฏในทางปฏิบัติคือ ชีตชื่อเดียวกันที่ยังไม่ถูก copy มาใน batch นี้ และชื่อที่กำหนด (defined name) ระดับ workbook ที่ไม่เคยมีอยู่ในปลายทางเลย HotXLS ไม่ยก exception เมื่อการ recompile ล้มเหลวกลางการ copy ชีต การกำหนดค่า Value ของเซลล์จะเก็บข้อความสูตรเป็น string ธรรมดาแทนอย่างเงียบๆ เป็นโหมดความล้มเหลวที่จงใจและตรวจสอบได้ ไม่ใช่แบบเงียบสนิท เพราะเซลล์สูตรที่แสดงข้อความตรงตัวอย่าง =SUM(Q1!B2:B12) แทนที่จะเป็นตัวเลขที่คำนวณแล้วอย่างไม่คาดคิด คือสัญญาณว่ามีบางอย่างต้นทางในการ copy ที่ resolve ไม่สำเร็จ ก่อนจะยอมแพ้ AddCopy ลองซ่อมหนึ่งครั้ง มันเดินผ่าน syntax tree ของสูตรที่ล้มเหลว รวบรวมทุก ID ของชื่อที่กำหนดที่สูตรแตะ และสำหรับแต่ละชื่อระดับ workbook ที่มีอยู่ในต้นทางแต่ยังไม่มีในปลายทาง มันจะ copy ชื่อนั้นข้ามไปและ recompile ข้อความเดียวกันเป็นครั้งที่สอง ชื่อระดับชีตอยู่นอกเหนือสิ่งที่การซ่อมนี้จะแก้ได้ เพราะชื่อที่มองเห็นได้แค่จากสูตรบนชีตเดียวของต้นทางไม่มีช่องที่เทียบเท่าให้ย้ายไป และปลายทางที่มีชื่อสะกดเหมือนกันอยู่แล้วจะถูกปล่อยไว้ไม่แตะต้อง ไม่ใช่เขียนทับ โดยสมมติว่าชื่อที่ผู้เรียกจงใจสร้างไว้ล่วงหน้าคือชื่อที่พวกเขาต้องการให้เคารพ ภายใน workbook เดียว การค้นหาชื่อของสูตรข้ามชีตจะเดินจาก scope ระดับชีตขึ้นไปยัง scope ระดับ workbook โดยอัตโนมัติ ซึ่งเป็นกลไกที่บทความเรื่องชื่อที่กำหนดและสูตรข้ามชีตของ HotXLSครอบคลุมไว้ การข้ามขอบเขต workbook จริงๆ ลบตาข่ายนิรภัยนั้นออกไปทั้งหมด และชื่อต้องถูกนำข้ามไปอย่างจงใจ ไม่เช่นนั้นสูตรที่พึ่งพามันจะลดระดับลงเหลือแค่ข้อความ

การอ้างอิง chart series ต้องการทางแก้เดียวกัน แต่ code path ต่างกัน

chart series ของ HotXLS ที่พล็อตช่วงเซลล์เจอปัญหาการกำหนดหมายเลขแบบเดียวกันเป๊ะกับสูตรเซลล์ธรรมดา เพราะการอ้างอิงช่วงข้อมูลของกราฟก็เป็น compiled formula token stream เช่นกัน สเปค BIFF เรียก record ที่พกมันว่า BRAI ([MS-XLS] section 2.4.51) แต่ AddCopy แก้มันไม่ได้ด้วยการใช้ chart-loading path ปกติซ้ำ เพราะ path นั้นเองคือสิ่งที่สร้างบั๊กขึ้นมาพอดี เมื่อ record ของกราฟถูก parse จากดิสก์ในขั้นตอนปกติของการเปิดไฟล์ formula tree ของมันถูกสร้างขึ้นด้วยการแปลไบต์ดิบผ่าน calculator instance ใดก็ตามที่กำลัง parse อยู่ ป้อนไบต์ BRAI ดิบของกราฟต้นทางผ่าน record loader ปกติของ workbook ปลายทางแทน แล้ว ixti ที่ฝังอยู่ในไบต์เหล่านั้นจะถูก resolve เทียบกับ table EXTERNSHEET ของปลายทาง ดังนั้น series จะชี้ไปยังชีตใดก็ตามที่ครองช่องนั้นอยู่ที่นั่นอย่างเงียบๆ เป็นความผิดพลาดประเภทเดียวกับการ copy compiled tree ของเซลล์โดยไม่เปลี่ยนแปลง เพียงแต่สังเกตยากกว่าเพราะไม่มีใครอ่านสูตร chart series แบบที่อ่านสูตรเซลล์ HotXLS หลีกเลี่ยงกับดักนี้ด้วยเส้นทาง clone เฉพาะแทน TXLSCustomChart.AssignFrom copy ไบต์ header ที่ไม่ใช่สูตรของแต่ละ record กราฟตรงตัว แล้วสร้างช่วงที่แนบไว้ใหม่ผ่าน primitive decompile-แล้ว-recompile เดียวกับที่ใช้กับเซลล์ธรรมดา ดังนั้น tree ใหม่จึงถูกสร้างขึ้นเทียบกับ table EXTERNSHEET ของปลายทางตั้งแต่ต้น แทนที่จะถูกตีความใหม่เทียบกับมันภายหลัง

ปัญหาการกำหนดหมายเลขเดียวกัน ทีละหนึ่งดัชนีฟอนต์

ไม่ใช่ทุกตัวเลขที่เป็นส่วนตัวของ workbook ภายในกราฟหรือเซลล์ rich-text จะเป็นสูตร และดัชนีฟอนต์ก็เป็นปัญหาประเภทเดียวกันในขนาดย่อ rich text run พร้อมกับ chart record อีกสองประเภทที่พกฟอนต์ของคำบรรยายหรือแกน เก็บการอ้างอิงฟอนต์เป็นจำนวนเต็มดิบเข้าไปใน table ฟอนต์ของ workbook เจ้าของเอง และดัชนีนั้นไม่มีความหมายอะไรใน table ของ workbook อื่นเลย มันอาจชี้ไปยัง typeface, ขนาด หรือสีที่ต่างไปโดยสิ้นเชิงที่นั่นก็ได้ง่ายๆ HotXLS แก้ปัญหานี้ด้วยค่าแทนที่จะเป็นตัวเลข มันค้นหา attribute ฟอนต์จริงที่ดัชนีนั้นใน table ต้นทาง หา หรือสร้าง entry ที่ตรงกันใน table ฟอนต์ของปลายทาง แล้วเขียนดัชนีที่เก็บไว้ใหม่ให้ชี้ไปยังช่องใหม่นั้น ความประหลาดของฟอร์แมตอย่างหนึ่งทำให้การค้นหาเองยุ่งยาก ดัชนีที่กำหนดหมายเลขในไฟล์ข้ามช่อง 4 ซึ่งเป็นช่องว่างการกำหนดหมายเลขที่ [MS-XLS] section 2.5.339 บันทึกไว้ ดังนั้นโค้ดจึงต้องเลื่อนดัชนีลงหนึ่งก่อนเปรียบเทียบฟอนต์และเลื่อนขึ้นหนึ่งก่อนเขียนผลลัพธ์

// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
  Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
  Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
  Inc(Ifnt);

เกิดอะไรขึ้นกับสูตรที่ชี้ออกนอก workbook ไปแล้ว

สูตรที่เข้าถึง workbook ที่สามก่อนที่คุณจะเรียก AddCopy เลย เป็นกรณีเดียวที่การไปกลับผ่านข้อความพาไปไม่ได้ เพราะตัว decompiler สูตรเป็นข้อความของ HotXLS เองจงใจไม่สร้างข้อความวงเล็บ [Book]Sheet! สำหรับการอ้างอิงภายนอก และตัว compiler ที่อีกฝั่งก็ไม่รับ syntax นั้นเป็นอินพุตเช่นกัน ดังนั้นกรณีนี้จึงรันผ่านกลไกที่สอง ที่ไม่แตะข้อความเลย เมื่อการซ่อมด้วยการย้ายชื่อที่อธิบายไว้ข้างต้นยังทิ้งเซลล์ไว้เป็น string และ workbook ต้นทางมีชื่อไฟล์จริง AddCopy เปลี่ยนกลยุทธ์ มัน deep-copy compiled formula tree เอง แทนที่จะเป็นข้อความของมัน แล้วส่งสำเนานั้นให้รอบการ rebind เฉพาะ คือ RebindExternRefsInTree ซึ่งเดินผ่านมันทีละ node สำหรับการอ้างอิงช่วงทุกตัวที่มันพบ รอบนั้นจะ resolve entry EXTERNSHEET ของต้นทางกลับเป็นคู่ชื่อชีต และลงทะเบียนหรือใช้ entry ที่เทียบเท่าใน table การอ้างอิงภายนอกของปลายทางเองซ้ำ สร้างลิงก์ external-workbook ใหม่เอี่ยมถ้าปลายทางไม่เคยอ้างอิงไฟล์ต้นทางนั้นมาก่อน

นี่คือจุดที่ปัญหาการกำหนดหมายเลขที่เป็นส่วนตัวของ workbook ชัดเจนที่สุด เพราะ token การอ้างอิงภายนอกมัดพิกัดสามอย่างที่แยกกันไว้เข้าเป็นฟิลด์เดียว และแต่ละอย่างเป็นส่วนตัวของ workbook ที่เขียนมันขึ้นมา คือ external workbook ตัวไหน เป็นช่องในรายการ external book ของปลายทางเอง กำหนดในลำดับใดก็ตามที่ workbook นั้นบังเอิญลงทะเบียนไว้ ชีตตัวไหนภายในรายการชีตของ external workbook นั้นเอง เก็บเป็นดัชนีเริ่มที่ 1 ที่จำกัดขอบเขตอยู่แค่ external book นั้นโดยเฉพาะ เป็นโดเมนการกำหนดหมายเลขที่ต่างไปโดยสิ้นเชิงจาก sheet ID ภายในของปลายทางเอง และช่วงเซลล์เอง ซึ่งเป็นพิกัดแถวและคอลัมน์ธรรมดาที่ไม่ต้องแปลเลย เพราะไม่เคยสัมพัทธ์กับ workbook ตั้งแต่แรก ผิดสองอย่างแรกอย่างใดอย่างหนึ่งแล้ว Excel ก็ยังเปิดไฟล์ได้ ยังแสดงสูตร และประเมินมันเทียบกับเซลล์ภายนอกที่ผิดโดยไม่บ่นอะไรเลย node ประเภทหนึ่งเอาชนะการ rebind ระดับ tree นี้ได้ด้วยซ้ำ คือการอ้างอิงถึงชื่อที่กำหนด เป็นดัชนีเข้าไปใน table ชื่อส่วนตัวของ workbook ของตัวเอง เหมือนกับที่ดัชนีชีตเป็นส่วนตัวของ EXTERNSHEET ของตัวเอง โดยไม่มีการซ่อมระดับ tree ที่เทียบเท่ากันให้ใช้เลย ทันทีที่การเดิน rebind พบการอ้างอิงชื่อที่ไหนก็ตามใน tree มันจะละทิ้งสูตรทั้งหมด แทนที่จะเขียนสูตรที่ถูกต้องแค่บางส่วนออกมา แม้เมื่อการ rebind สำเร็จ เซลล์ปลายทางก็ไม่แสดงตัวเลขที่คำนวณใหม่สดๆ มันแสดงค่าที่เซลล์ต้นทางถืออยู่แล้ว ณ เวลา copy เก็บไว้ในช่อง cache แบบเดียวกับที่ Excel เองแคชค่าล่าสุดที่รู้ของการอ้างอิงภายนอกใดๆ ไว้จนกว่าคุณจะรีเฟรชลิงก์อย่างชัดเจน ซึ่งเป็นค่าเริ่มต้นที่ถูกต้อง เพราะการคำนวณใหม่ข้ามลิงก์ที่ยังเชื่อมกับไฟล์อื่นอยู่เป็นการดำเนินการประเภทที่คุณต้องการกระตุ้นครั้งเดียวอย่างจงใจ ไม่ใช่ทุกครั้งที่เปิด

การออกแบบนี้มีต้นทุนอะไรบ้าง

กลไก decompile-แล้ว-recompile ของ AddCopy ไม่ได้ฟรี และต้นทุนนั้นควรค่าแก่การวางแผนไว้ก่อนที่คุณจะเขียนสคริปต์งานรวมข้อมูลขนาดใหญ่ ไม่ใช่หลังจากนั้น การ copy ชีตภายใน workbook เดียวกันใช้เส้นทางที่ถูก คือการ duplicate compiled tree ในหน่วยความจำตรงๆ เพราะทุกดัชนีข้างในมันถูกต้องอยู่แล้วใน workbook ที่มันจะอยู่ต่อ การ copy ข้าม workbook จ่ายค่า parse จริงสำหรับทุกเซลล์สูตรแทน decompile เป็นข้อความแล้ว compile ข้อความนั้นใหม่จากศูนย์ และแม้ความแตกต่างจะไม่คุ้มค่าที่จะวัดบนชีตที่มีสูตรแค่ไม่กี่สิบตัว workbook ต้นทางที่มีเซลล์สูตรนับหมื่นตัว copy เป็นหนึ่งชีตในหลายสิบชีตของงาน batch ควรคาดหวังว่าการ recompile จะครองเวลารันมากกว่า I/O ของไฟล์รอบๆ มัน ลำดับการ copy สำคัญด้วยเหตุผลที่สองนอกเหนือจากความเร็ว สูตรที่อ้างอิงชีตที่ AddCopy ยังไปไม่ถึงใน batch นี้ จะ recompile ล้มเหลวด้วยเหตุผลเดียวกับที่สูตรที่อ้างอิงชีตที่ไม่มีอยู่จริงล้มเหลว ดังนั้นงานที่ copy ชีต B ก่อนสูตรของชีต A ที่พึ่งพามัน จะเห็นสูตรนั้นเสื่อมลงตามที่อธิบายไว้ข้างต้นเป๊ะ เป็นข้อความ string หรือ fallback แบบ external-link ที่ชี้กลับไปยังไฟล์ต้นทางที่มันเพิ่งมาจากพอดี และเพราะ workbook ต้นทางแต่ละไฟล์ในงานรวมข้อมูลมักถูกเขียนขึ้นอย่างอิสระ จึงคุ้มค่าที่จะทดสอบโหมดความล้มเหลวหนึ่งอย่างที่ไม่มีไฟล์ต้นทางไฟล์เดียวจะเตือนคุณได้เลยอย่างชัดเจน workbook สาขาห้าไฟล์ที่แต่ละไฟล์รวมตัวเลขของสาขาคู่กัน สามารถรวมกันเป็นการอ้างอิงแบบวนรอบ (circular reference) จริงภายใน workbook สรุป โดยไม่มีไฟล์ต้นทางไฟล์ไหนเลยที่เคยมีมันอยู่เอง เป็นวงจรที่มีอยู่ได้ก็ต่อเมื่อทุกชีตลงเอยอยู่ที่เดียวกันและการคำนวณใหม่รันข้ามชุดที่รวมกันแล้วเท่านั้น

การ copy เวิร์กชีตข้าม workbook มาเป็นพฤติกรรมมาตรฐานของ AddCopy ในHotXLS Delphi Excel Componentสำหรับ Delphi และ C++Builder หน้าผลิตภัณฑ์มีเอกสารอ้างอิง API เวิร์กชีตและ workbook แบบเต็ม รวมถึงพฤติกรรมด้านกราฟ, rich-text และการอ้างอิงภายนอกที่อธิบายไว้ในบทความนี้