หยุดการ save XLS ที่คำนวณสูตรใหม่เงียบ ๆ ใน Delphi

HotXLS ซึ่งเป็นไลบรารี Excel แบบเนทีฟสำหรับ Delphi และ C++Builder save เวิร์กบุ๊ก BIFF8 .xls แบบคลาสสิกโดยใช้ cache ก่อน: TXLSWorksheet.WriteFormula ถาม TXLSWorkbook.TryGetCachedFormulaValue ขอค่าที่ Excel เก็บไว้ข้างสูตรแต่ละตัว และเรียก evaluator เฉพาะเมื่อ cache นั้นหายไปหรือถูกทำให้เป็นโมฆะ เวิร์กบุ๊กที่คุณเปิดแล้วไม่เคยแตะจะ save ตัวเลขเดิมกลับไป และผลลัพธ์ใหม่ต้องเรียก Recalculate อย่างชัดเจนหนึ่งครั้ง แทนที่จะเป็นผลข้างเคียงที่ซ่อนอยู่ของ SaveAs

bug ที่บังคับให้สัญญานี้ออกมาเปิดเผยนั้นเล็กจนน่าอาย ไฟล์ใน corpus ชื่อ nested-subtotals.xls มียอดรวมใหญ่ใน R2C4 ที่ค่าซึ่ง cache ไว้เป็น 37 เปิดมันด้วย HotXLS, ถาม TryGetCachedFormulaValue ของเซลล์นั้น, ได้ 37 save โดยไม่แก้สักเซลล์, เปิดสำเนาที่ save แล้ว, ถามคำถามเดิม, ได้ 67 ไม่มีอะไรใน API ถูกขอให้คำนวณอะไรเลย แต่ตัวเลขในไฟล์ขยับไปพอดี 30 — และ 30 ก็บังเอิญเป็นผลรวมของผลรวมย่อยสองกลุ่มคือ 10 กับ 20 ที่อยู่ภายในช่วงที่ยอดรวมใหญ่ครอบอยู่

ทำไมการ save ไฟล์ XLS ถึงเปลี่ยนค่าของสูตร

bug สองตัวที่แยกกันต้องเรียงแถวกันพอดีเพื่อให้ 37 กลายเป็น 67 และการแก้ตัวใดตัวหนึ่งเพียงตัวเดียวก็จะซ่อนอีกตัวไว้ ตัวแรกเป็นเชิงโครงสร้าง: writer แบบคลาสสิกคำนวณสูตรใหม่ทุกตัวในทุกครั้งที่ save ตัวที่สองเป็นการตรวจชนิดที่ไม่มีทางเป็นจริงสำหรับสูตรที่โหลดมาจากดิสก์ ซึ่งทำให้ evaluator นับเซลล์ SUBTOTAL ที่ซ้อนกันสองครั้ง ไฟล์ใน corpus เป็นแค่ input ตัวแรกที่การคำนวณใหม่ตอน save ให้คำตอบต่างจาก Excel แล้วมีคนเทียบสองอันนั้น ข้อบกพร่องเชิงโครงสร้างพูดง่าย ๆ ได้ว่า: ก่อน v2.382.3 TXLSWorksheet.WriteFormula กับพี่น้องฝั่ง shared formula อย่าง WriteFormulaWithTExp ได้ field FormulaValue ขนาดแปด byte ของทุก Formula record มาจากการเรียก TXLSWorkbook.GetFormulaValue ซึ่งก็คือ evaluator นั่นเอง cache ที่ ParseFormula อุตส่าห์ decode มาจากไฟล์ต้นทางตอนโหลดไม่เคยถูกปรึกษาตอนเขียนออกเลย ในทางปฏิบัติ การ save แต่ละครั้งคือการคำนวณใหม่เต็มรูปแบบที่ข้าม API recalculate ระดับเวิร์กบุ๊กไป อะไรก็ตามที่คุณตั้งบนเวิร์กบุ๊กก็หยุดมันไม่ได้ ทุกจุดที่ evaluator ของ HotXLS เห็นต่างจาก Excel ไม่ว่าจะเป็นฟังก์ชันที่ยังไม่รองรับอย่างชอบธรรมหรือ bug ล้วน ๆ กลายเป็นการเปลี่ยนข้อมูลเงียบ ๆ ตอน save

bug ตัวที่สองอยู่ใน callback ของ subtotal ที่ซ้อนกันซึ่ง evaluator ใช้ Excel นิยาม SUBTOTAL ทุกรูปแบบว่าต้องเพิกเฉยต่อเซลล์ที่สูตรของมันเองเป็น SUBTOTAL อีกตัว ตัวคำนวณใน lxCalc.pas จึงตั้ง FIgnoreSubtotalCells ระหว่างการรวมค่า แล้วถามเวิร์กบุ๊กผ่าน TXLSWorkbook.GetClassicIsSubtotalCell ว่าเซลล์แต่ละตัวในช่วงเป็นแบบนั้นไหม callback นั้นดึงข้อความสูตรมาเป็น Variant แล้วทดสอบด้วย VarType(f) = varOleStr ข้อความนั้นกลับมาจาก GetUnCompiledFormula เป็น String ของ Delphi และ String ที่ถูก assign ให้ Variant จะเป็น varUString ไม่มีทางเป็น varOleStr เงื่อนไขนั้นเป็นเท็จสำหรับทุกเซลล์ในทุกไฟล์ที่โหลดมา ผลรวมย่อยของกลุ่มจึงถูกทบเข้าไปในยอดรวมใหญ่อีกครั้ง และในการ save ที่คำนวณทุกอย่างใหม่ 10 + 20 + 7 ก็กลายเป็น 67

// HotXLS 2.381 และก่อนหน้า: Variant ของสูตรที่สร้างจาก String
// คือ varUString การเปรียบเทียบนี้จึงไม่เคยสำเร็จ
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr รับ varString, varOleStr และ varUString
// และ AGGREGATE ถูกกันออกจาก subtotal ที่ครอบมันอยู่เหมือนที่ Excel ทำ
if VarIsStr(f) then
  Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
    SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
    SameText(Copy(f, 1, 10), 'AGGREGATE(') or
    SameText(Copy(f, 1, 11), '=AGGREGATE(');

v2.382.0 ส่งการแก้ VarIsStr ออกไป และในขณะที่อยู่ในฟังก์ชันเดียวกันก็สอน callback ว่าเซลล์ AGGREGATE ก็ถูกกันออกจาก subtotal ที่ครอบมันอยู่ด้วย แค่นั้นก็ทำให้ assertion ใน corpus ผ่าน เพราะค่า 37 ที่คำนวณใหม่ตอนนี้ตรงกับ 37 ที่โหลดมา แต่มันไม่ได้ทำให้ไลบรารีซื่อสัตย์: การ save ยังคำนวณใหม่อยู่ และเทสต์ก็เขียวเพียงเพราะ evaluator บังเอิญเห็นตรงกับ Excel ในไฟล์นั้นเป็นพิเศษ กฎว่า SUBTOTAL กับ AGGREGATE ข้ามเซลล์ไหน รวมถึงแถวที่ซ่อนอยู่ มีอยู่ในบทความเรื่องแถวที่ซ่อนกับ SUBTOTAL และ AGGREGATE สิ่งที่สำคัญตรงนี้คือไม่มี evaluator ตัวไหนควรมีสิทธิ์ออกเสียงในไฟล์ที่คุณไม่ได้ขอให้มันคำนวณ

Excel รับประกันอะไรเรื่องค่าที่ cache ไว้ตอน save

Excel มองการ save เป็นการเก็บภาพ ณ ขณะหนึ่ง ไม่ใช่เหตุการณ์คำนวณ ค่าที่เขียนลง field FormulaValue ของ Formula record ([MS-XLS] §2.4.127, โครงสร้างอยู่ใน §2.5.133) คืออะไรก็ตามที่เซลล์แสดงอยู่ในตอนนั้น ซึ่งในโหมดคำนวณด้วยมืออาจเก่าเป็นปี และ Excel ก็ยังเขียนมันออกไปอย่างซื่อสัตย์ การคำนวณใหม่เป็นคนละ operation ที่มีตัวกระตุ้นของตัวเอง ตอนนี้ HotXLS ทำตามกฎเดียวกันสำหรับการ save แบบคลาสสิก: WriteFormula กับ WriteFormulaWithTExp เรียก TryGetCachedFormulaValue ก่อน ใช้ CacheInfo.Value เมื่อสถานะเป็น xlfcsLoaded หรือ xlfcsCalculated และตกไปที่ GetFormulaValue เฉพาะกรณี xlfcsMissing กับ xlfcsInvalidated ครึ่งฝั่งอ่านของสัญญานี้ รวมถึงความหมายของแต่ละสถานะและทำไม blank หรือ False ที่ cache ไว้ถึงยังนับเป็นค่า อธิบายไว้ในอ่านค่าสูตรที่ cache ไว้จาก Excel ใน Delphi โดยไม่ต้องคำนวณใหม่

เส้นทาง fallback ถูกเก็บไว้โดยตั้งใจ ไม่ได้ถอดออก สูตรที่คุณ assign ในเซสชันนี้ผ่าน Cells[Row, Col].Formula มาพร้อม cache ว่าง และสูตรที่คุณเขียนทับบนเซลล์ที่โหลดมามีสถานะเป็น xlfcsInvalidated จาก _SetCompiledFormula ทั้งคู่ถูกประเมินตอน save เหมือนเดิมทุกประการ เวิร์กบุ๊กที่สร้างขึ้นจึงยังเปิดใน Excel แล้วมีตัวเลขอยู่ เมื่อแม้แต่ evaluator ก็หาค่าไม่ได้ writer จะ emit payload เป็นศูนย์แล้วตั้ง fAlwaysCalc (grbit bit 0 ของ §2.4.127) เพื่อให้ Excel คำนวณเซลล์นั้นใหม่ตอนเปิด แทนที่จะเชื่อค่าตัวแทนนั้น

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // sheet, row และ column แบบเริ่มที่ 1: R2C4 บนชีตแรก
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // ไม่มี evaluator เกี่ยวข้องกับเซลล์ที่มี cache
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 สำหรับ nested-subtotals.xls
    // การ save ที่คำนวณใหม่จะเขียน 67 ลงตรงนี้
  finally
    Book.Free;
  end;
end;

เซลล์รากของ shared formula ใน BIFF เก็บค่าที่ cache ไว้ตรงไหน

ใน Formula record ของตัวเอง เหมือนเซลล์สูตรตัวอื่นทุกตัว และนั่นแหละคือสิ่งที่ทำให้เซลล์รากของกลุ่ม shared formula กลายเป็นจุดเดียวที่การ save แบบ cache-first ยังสูญเสียอยู่ shared formula ใน BIFF8 ถูกเก็บเป็น record ShrFmla ([MS-XLS] §2.4.260) ที่ตามหลัง Formula record ของเซลล์ซ้ายบน และเซลล์สมาชิกทุกตัวรวมทั้งรากก็พก rgce ที่ประกอบด้วย PtgExp token ตัวเดียว (§2.5.198): byte แรกของนิพจน์ที่ parse แล้วคือ $01 ตามด้วยแถวและคอลัมน์ของเซลล์ราก เซลล์ลูกนั้นพึ่งพาตัวเองได้ — HotXLS อ่าน FormulaValue ของแต่ละตัวแล้ว resolve นิพจน์ด้วยการไปค้นสูตรที่ compile แล้วของราก เซลล์รากต่างออกไป เพราะตอนที่ Formula record ของมันถูก parse นิพจน์ยังไม่มีอยู่ มันมาถึงช้ากว่าหนึ่ง record

ช่องว่างหนึ่ง record นั้นแหละที่ cache หายไป TXLSReader.ParseFormula decode ค่าที่ cache ไว้ และเมื่อเจอ PtgExp ที่พิกัดตรงกับของเซลล์เอง ก็จำเซลล์นั้นไว้ใน FSharedFormulaRow กับ FSharedFormulaCol แล้วประกาศ cache ให้เซลล์นั้น เมื่อ record ShrFmla ($04BC) มาถึง ParseSharedFormula จะ compile นิพจน์และติดตั้งมันด้วย _SetCompiledFormula และ _SetCompiledFormula ก็ทำสิ่งที่มันต้องทำสำหรับการเปลี่ยนสูตรใด ๆ: มันล้าง FCachedFormulaValue แล้วตั้งสถานะกลับเป็น xlfcsMissing ค่า 37 ที่โหลดมาของรากจึงถูกทิ้งไปก่อนที่ใครจะได้อ่านมัน TryGetCachedFormulaValue รายงานว่ารากไม่มี cache และ writer แบบ cache-first ก็ตกไปหา evaluator อย่างว่านอนสอนง่ายสำหรับเซลล์ตัวที่ทุกคนกำลังจ้องมองอยู่พอดี record Array (§2.4.4) มีลำดับแบบเดียวกันและมีรูแบบเดียวกัน

การแก้ใน v2.382.3 เพิ่ม field ตัวที่สามคือ FSharedFormulaCachedValue ไว้ข้างพิกัดรากที่ค้างอยู่ ParseFormula เก็บ cache ที่ decode ได้ไว้ที่นั่นเมื่อมันรู้ว่าเป็นราก และทั้ง ParseSharedFormula กับ ParseArrayFormula ก็ replay มันผ่าน _SetCellCachedFormulaValue ทันทีหลังจากติดตั้งนิพจน์ที่ compile แล้ว จากนั้นรีเซ็ตที่เก็บนั้นเป็น Unassigned cache ชนิด String ไม่ได้รับผลกระทบจากเรื่องทั้งหมดนี้เพราะ payload ของมันมาใน String record แยกต่างหาก และถูกส่งต่อด้วยพิกัดของเซลล์ ไม่ใช่ด้วยลำดับ record ถ้าคุณทำงานกับฝั่ง OOXML ของแนวคิดเดียวกัน บทความเรื่องการขยาย si ของ shared formula ใน XLSX อธิบายว่าทำไมฟอร์แมตแบบ package ถึงไม่มีปัญหาลำดับแบบเดียวกัน แต่มีกับดักการขยายในแบบของตัวเอง

ทำไมเซลล์ลูกของ shared formula ถึงต้องเลื่อนแบบ relative

เพราะนิพจน์ที่เก็บใน ShrFmla ถูกเขียนโดยอ้างอิงกับเซลล์ราก และเซลล์ลูกที่เอามันไปใช้แบบ verbatim ก็จะประเมินการอ้างอิงของรากแทนของตัวเอง reader ตัวเก่าติดตั้ง Value.GetCopy() ให้เซลล์ลูกแต่ละตัว ซึ่งเป็นสำเนาแบบ deep copy ที่ไม่มีการเลื่อนตำแหน่ง กลุ่มที่ตั้งรากที่ B1 ด้วย =A1*3 จึงให้ =A1*3 กับเซลล์ลูกทุกตัวด้วย การ save แบบ cache-first จริง ๆ แล้วกลบ bug นี้ไว้สำหรับไฟล์ที่โหลดมา เพราะเซลล์ลูกมี FormulaValue ของตัวเองและไม่ต้องใช้นิพจน์เพื่อ save ให้ถูกอยู่แล้ว มันโผล่ทันทีที่มีอะไรคำนวณใหม่ ตอนนี้ reader ติดตั้ง TXLSCompiledFormula.GetCopy(row - srow, col - scol) ซึ่งเดินผ่าน syntax tree แล้วเลื่อนการอ้างอิงแบบ relative ทุกตัวตามระยะห่างของเซลล์ลูกจากราก เซลล์ลูกที่ B2 จึงเป็นเจ้าของ =A2*3 ของจริง

เทสต์ regression ที่ตอกพฤติกรรมทั้งสองไว้คุ้มที่จะอ่าน เพราะมันไม่ยอมให้ความบังเอิญผ่านไปเฉย ๆ มันสร้างเวิร์กบุ๊กที่มี =A1*3 กับ =A2*3 บนอินพุต 2 กับ 4 จากนั้นฉีด cache ที่ผิดโดยตั้งใจคือ 999 กับ 888 ผ่าน _SetCellCachedFormulaValue ครั้งหนึ่งโดยเปิด UseSharedFormulas และอีกครั้งโดยปิด หลัง save แล้วโหลดใหม่ ทั้งสองเซลล์ต้องยังรายงาน 999 กับ 888 — เป็นหลักฐานว่าการ save ไม่ได้แตะทั้ง cache ของรากและของเซลล์ลูก ต้องรอหลัง Recalculate อย่างชัดเจนเท่านั้นที่มันจะกลายเป็น 6 กับ 12 — เป็นหลักฐานว่านิพจน์ที่เลื่อนแล้วของเซลล์ลูกถูกต้อง เทสต์ที่ใส่ค่าจริงลงไปก็จะผ่านใต้ writer ตัวเก่าด้วยเหมือนกัน นั่นแหละคือประเด็นทั้งหมดของการใส่ค่าผิดลงไป

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // เปลี่ยนอินพุตตัวหนึ่ง

    // cache ที่โหลดมาของสูตรที่พึ่งพากันไม่ถูกทำให้เป็นโมฆะโดย
    // การแก้ค่าคงที่ ดังนั้น SaveAs ธรรมดาจะเก็บตัวเลขเดิมไว้
    // จงขอการคำนวณใหม่เมื่อคุณต้องการผลลัพธ์สดใหม่จริง ๆ:
    Book.Recalculate;

    if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
      Writeln('B1 now ', VarToStr(Info.Value),
        ', state ordinal ', Ord(Info.State));   // xlfcsCalculated
    Book.SaveAs('quarterly-model-updated.xls');
  finally
    Book.Free;
  end;
end;

สัญญาแบบ cache-first ไม่ได้ทำให้คุณในเรื่องใด

การ save แบบ cache-first รักษาสิ่งที่โหลดมาไว้ แต่มันไม่ได้ติดตามว่าสิ่งที่โหลดมานั้นยังจริงอยู่ไหม การแก้ค่าคงที่ที่สูตรหนึ่งพึ่งพาอยู่ทำให้ dependency graph สกปรกในสายตา evaluator แต่มันปล่อยให้ cache xlfcsLoaded ของเซลล์ที่พึ่งพาอยู่คงเดิม และ writer แบบคลาสสิกก็จะเขียนค่าที่ค้างเก่านั้นออกไปอย่างสบายใจ เว้นแต่คุณจะเรียก Recalculate หรืออ่าน Value ของเซลล์นั้นก่อน ซึ่งจะคำนวณมันและเลื่อนสถานะไปเป็น xlfcsCalculated นี่คือการแลกแบบเดียวกับที่ Excel ทำในโหมดคำนวณด้วยมือ และมันเป็นการแลกที่ถูกต้องสำหรับ pipeline ที่เปิดไฟล์ของคนอื่น แก้ป้ายสักสองสามอันแล้ว save — แต่มันหมายความว่าเวิร์กบุ๊กที่แก้ค่าอินพุตต้องเป็นเจ้าของขั้นตอนคำนวณใหม่ของตัวเองอย่างชัดเจน นโยบาย RecalcBeforeSave ของ writer ฝั่ง XLSX ไม่เปลี่ยนไปเพราะงานนี้ และมีโหมด manual ของตัวเองที่รักษา cache ไว้ในเจตนารมณ์เดียวกัน ขอบเขตที่เล็กกว่าสองข้อตามมาจากเรื่องนี้: เส้นทาง cache-first ช่วยเฉพาะเซลล์ที่สถานะเป็น xlfcsLoaded หรือ xlfcsCalculated; ตัวสร้างไฟล์ที่เขียนสูตรแล้วไม่เคยประเมินมันก็ยังจ่ายการประเมินหนึ่งครั้งต่อเซลล์ตอน save เหมือนเดิมทุกประการ และการแก้ nested subtotal ก็แก้เรื่องว่า evaluator ข้ามเซลล์ไหน ไม่ได้แก้ทุกฟังก์ชันที่ evaluator implement — ไฟล์ที่สูตรของมัน HotXLS คำนวณไม่เหมือน Excel ตอนนี้ปลอดภัยที่จะ round-trip โดยไม่แตะ แต่ Recalculate ที่ตั้งใจเรียกบนไฟล์นั้นก็ยังให้คำตอบของไลบรารีไม่ใช่ของ Excel และคุณควรเทียบสองอันก่อนจะเชื่อการ save ที่คำนวณใหม่

การ save แบบคลาสสิกที่ใช้ cache ก่อน, cache ของรากสูตรแบบ shared และ array ที่กู้คืนมา, การเลื่อนการอ้างอิงแบบ relative ให้เซลล์ลูกของ shared formula และกฎการซ้อนของ SUBTOTAL กับ AGGREGATE ที่แก้แล้ว ล้วนอยู่ในHotXLS Delphi Spreadsheet Component มาตรฐานสำหรับ Delphi และ C++Builder โดยไม่พึ่งพา Excel หรือ OLE automation server ตัวใด หน้าผลิตภัณฑ์มีเอกสารอ้างอิง API ครบถ้วนสำหรับเวิร์กบุ๊ก, cache reader และจุดเข้า recalculate ที่ใช้ในบทความนี้