อ่านค่าแคชของสูตร Excel ใน Delphi โดยไม่คำนวณใหม่

HotXLS ไลบรารี Excel แบบ native สำหรับ Delphi และ C++Builder อ่านค่าที่ Excel เก็บเอาไว้ข้างสูตรแล้ว ผ่าน TryGetCachedFormulaValue กับ IXLSFormulaCacheReader จุดเข้าทั้งสองไม่เรียกเครื่องคำนวณ ไม่ถอด token ของสูตร ไม่อัปเดตสถานะ dirty และไม่เขียนอะไรกลับลงโมเดล เวิร์กบุ๊กที่คุณแค่อ่านจึงคงอยู่ตรงตามที่คุณเปิดมัน

สถานการณ์ที่ผลักเรื่องนี้มาจนถึงจุดนี้จืดชืดและพบมากเหลือเกิน งานประจำกลางคืนเปิดเวิร์กบุ๊กที่คนอื่นสร้างไว้สองสามร้อยไฟล์ ดึงคอลัมน์ยอดรวมออกจากแต่ละไฟล์หนึ่งคอลัมน์ แล้วส่งตัวเลขเข้าคลังข้อมูล ยอดรวมเหล่านั้นนั่งรออยู่ในไฟล์แล้ว — Excel คำนวณและบันทึกมันไว้แล้ว ทว่าพองานถามเซลล์สูตรหาค่า ไลบรารีที่มีคำตอบเดียวสำหรับคำถามนี้จะสร้างกราฟการพึ่งพาแล้วคำนวณทั้งชีต งานที่ควรติดขัดที่ I/O จึงกลายเป็นเวทีแข่งความเร็วการคำนวณ

ทำไมการอ่านเซลล์สูตรหนึ่งเซลล์จึงต้องแลกด้วยการคำนวณใหม่ทั้งชุด?

เพราะ getter ค่าบนเซลล์สูตรคือคำขอให้ผลิตค่า และวิธีที่ถูกต้องสากลเพียงหนึ่งเดียวในการผลิตค่าคือการประเมินสูตร นั่นเป็นค่าปริยายที่ถูกสำหรับแอปพลิเคชันที่แก้ไขเวิร์กบุ๊ก แต่เป็นค่าปริยายที่ผิดสำหรับไปป์ไลน์ที่ดึงข้อมูลออกมา ยิ่งไปกว่านั้น การประเมินไม่เคยปลอดผลข้างเคียง: มันเขียนผลลัพธ์กลับลงเซลล์ มันพลิก flag dirty และมันอาจ resolve ออกมาต่างจากแอปที่ผลิตไฟล์เมื่อฟังก์ชันไม่รองรับหรือการอ้างอิงภายนอกพัง งานที่คุณบอกทีมปฏิบัติการว่าอ่านอย่างเดียว กำลังผลิตเวิร์กบุ๊กที่ไม่ตรงกับไฟล์บนดิสก์อีกต่อไป และถ้ามีอะไรบันทึกมันทีหลัง ไฟล์บนดิสก์ก็เปลี่ยนด้วย

การอ่านค่าแคชคือครึ่งอีกฝั่งของสัญญา มันตอบคำถามที่แคบกว่า — แอปพลิเคชันที่ผลิตไฟล์เก็บอะไรไว้ตรงนี้? — และปฏิเสธที่จะตอบอย่างอื่น เมื่อคุณอยากได้ตัวเลขสดจริง ๆ HotXLS ก็ยังมี การคำนวณใหม่แบบเพิ่มขึ้นที่ขับเคลื่อนด้วยกราฟการพึ่งพาให้ใช้ ประเด็นคือการดึงข้อมูลกับการประเมินควรเป็นสองคำเรียกที่แยกกัน ไม่ใช่คำเรียกเดียวที่มีสองอารมณ์

สามข้อเท็จจริงอิสระต่อกันของเซลล์เดียว

สรุปก่อนเลย: ค่าแคชของสูตรแบกข้อเท็จจริงอิสระสามข้อ และการยุบมันลงเป็น Variant เดียวทำให้ข้อมูลที่คุณต้องใช้หายไป TXLSFormulaCacheInfo แยกมันไว้เป็น State, Kind และ Value TXLSFormulaCacheState บันทึกแหล่งกำเนิดครบห้ากรณี — xlfcsNotFormula, xlfcsMissing, xlfcsLoaded, xlfcsCalculated และ xlfcsInvalidated — ส่วน TXLSFormulaCacheValueKind จัดชนิด payload เป็น xlfcvBlank, xlfcvNumber, xlfcvDateTime, xlfcvString, xlfcvBoolean หรือ xlfcvError การแยกแบบนี้เองที่ทำให้ "มีอยู่" ถูกรายงานอย่างตรงไปตรงมา แคชว่าง แคชสตริงว่าง แคช False แคชศูนย์ และแคช error ล้วนเป็นค่าจริง จึงห้ามอนุมานการมีอยู่จาก VarIsEmpty หรือ VarIsNull TryGetCachedFormulaValue คืน True เฉพาะ xlfcsLoaded กับ xlfcsCalculated และยังกรอกสถานะที่วินิจฉัยได้เสมอแม้คืน False

เรกคอร์ด TXLSFormulaCacheInfo ของ HotXLS แยกข้อเท็จจริงอิสระสามข้อของเซลล์สูตรหนึ่งเซลล์: แหล่งกำเนิด State ครบห้ากรณี ชนิด payload ครบหก และ Variant Value จึงไม่มีแคชว่างหรือ False ถูกเข้าใจผิดเป็นแคชที่ขาดหาย — แหล่งกำเนิด ชนิด payload และค่า payload แยกกันอยู่ นั่นคือทางเดียวที่แคชว่าง ศูนย์ สตริงว่าง หรือ error จะถูกรายงานได้ตามค่าจริงของมัน

var
  Book: TXLSXWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-model.xlsx');
    // SheetIndex, Row และ Col เป็น one-based ทั้งหมดในนี้
    if Book.TryGetCachedFormulaValue(1, 12, 5, Info) then
      Writeln('cached value: ', VarToStr(Info.Value))
    else
      Writeln('no usable cache, state ordinal ', Ord(Info.State));
  finally
    Book.Free;
  end;
end;

ทำไมค่าแคชจึงหายไป?

มีเหตุผลเป๊ะสี่ข้อที่ TryGetCachedFormulaValue ส่ง False กลับมา และสถานะบอกคุณได้ว่าข้อไหนเป็นต้นเรื่อง xlfcsNotFormula หมายถึงเซลล์เก็บค่าลิเทอรัลหรือไม่มีอะไรเลย พิกัดที่หลุดช่วงก็ยุบเข้าคำตอบเดียวกัน xlfcsMissing หมายถึงเซลล์เป็นสูตรจริง แต่ผู้ผลิตไม่ได้เก็บ payload ค่าไว้ให้ — ผลลัพธ์ธรรมดาของตัวสร้างไฟล์ที่เขียนแต่สูตรแล้วปล่อยให้ Excel กรอกผลตอนเปิดครั้งแรก xlfcsInvalidated หมายถึงข้อความสูตรถูกแทนที่หลังโหลด ค่าที่เคยอยู่ตรงนั้นจึงบรรยายนิพจน์ที่ไม่มีอยู่แล้ว ส่วน xlfcsCalculated เป็นกรณีสำเร็จ: มันติดค่าที่โค้ดของคุณเองหรือตัวประเมินของ HotXLS ผลิตในเซสชันนี้ ต่างจาก xlfcsLoaded ที่มาจากไฟล์

ความตรงไปตรงมาเรื่องแคชที่หายสำคัญกว่าการเอาสีทาทับมัน HotXLS ปฏิเสธที่จะแต่งค่าขึ้นมา และตอนบันทึกก็เข้มงวดเท่ากัน — เฉพาะ xlfcsLoaded กับ xlfcsCalculated เท่านั้นที่ปล่อยค่าแคชออกไป ขณะที่ xlfcsMissing กับ xlfcsInvalidated เขียนสูตรเปล่า ๆ แทนที่จะตรึงเลขเก่าไว้ในไฟล์ เหลือทางตอบสนองที่สุขภาพดีสามทางในไปป์ไลน์: ข้ามแถวนั้นแล้วจดช่องว่างไว้ คำนวณเวิร์กบุ๊กนั้นตั้งใจและยอมรับต้นทุน หรือประเมินแล้วกระทบยอด ถ้าตัวเลขที่ประเมินได้ขัดกับสิ่งที่แอปผลิตไฟล์จะเขียนไว้ เครื่องมือติดตามการประเมินสูตรคืออุปกรณ์สำหรับไล่ว่าสองการคำนวณแยกจากกันตรงไหน ไม่ใช่การเดาจากผลลัพธ์

ตัวอ่านเดียวคร่อมเอนจินคลาสสิก, OOXML และ ODF

ไปป์ไลน์ไม่ควรต้องสนใจว่าไฟล์ที่เพิ่งเปิดเป็น BIFF, OOXML หรือ ODF IXLSFormulaCacheReader คือจุดเข้าแบบอ่านอย่างเดียวจุดเดียวสำหรับทั้งสาม: ทั้ง TXLSWorkbook.CreateFormulaCacheReader และ TXLSXWorkbook.CreateFormulaCacheReader คืน adapter เบา ๆ ที่ครอบการค้นเซลล์แบบ sparse ที่แต่ละเอนจินใช้อยู่แล้ว พร้อมพิกัดชีต แถว คอลัมน์แบบ one-based เหมือนกันเป๊ะ คลาสเวิร์กบุ๊กตั้งใจไม่ implement interface นี้เอง — การอ้างอิง interface ชี้ไปที่เวิร์กบุ๊กจะเปลี่ยนความหมายการเป็นเจ้าของและเปิดช่องให้ผู้เรียกลอดผ่านการเช่าอายุการใช้งานได้ แทนที่แบบนั้น การทำลายเวิร์กบุ๊กจะล้างพอยน์เตอร์ดิบข้างในการเช่านั้น และตัวอ่านที่โค้ดคุณยังถืออยู่จะ raise EXLSFormulaCacheReaderInvalidated ในคิวรีถัดไป แทนที่จะเข้าถึงหน่วยความจำที่ถูกปล่อยไปแล้ว นี่คือการตรวจอายุการใช้งานแบบ fail-fast ไม่ใช่การรับประกันความพร้อมกัน

var
  Reader: IXLSFormulaCacheReader;
  Info: TXLSFormulaCacheInfo;
  Row, Missing, Errors: Integer;
  Total: Double;
begin
  Reader := Book.CreateFormulaCacheReader;
  Total := 0;
  Missing := 0;
  Errors := 0;
  for Row := 2 to LastRow do
    if Reader.TryGetCachedFormulaValue(1, Row, 7, Info) then
    begin
      case Info.Kind of
        xlfcvNumber: Total := Total + Double(Info.Value);
        xlfcvError:  Inc(Errors);
      end;
    end
    else if Info.State = xlfcsMissing then
      Inc(Missing);
  // ไม่มีเครื่องคำนวณถูกเรียก ไม่มี flag dirty ขยับ Book ไม่เปลี่ยน
end;

ไบต์แคชอาศัยอยู่ที่ไหนจริง ๆ

สำหรับไฟล์ .xls แบบคลาสสิก แคชคือฟิลด์ FormulaValue ของเรกคอร์ด Formula แปดไบต์ซึ่ง [MS-XLS] §2.5.133 บรรยายไว้ เมื่อเวิร์ดสูงเท่ากับ $FFFF payload ไม่ใช่ IEEE 754 double แต่เป็น variant ที่ติดแท็ก และเลย์เอาต์พลาดง่ายแบบละเอียด: ชนิด variant นั่งอยู่ใน val[0] payload boolean หรือ BErr นั่งอยู่ใน val[2] โดย val[1] ไม่นิยาม HotXLS เคยอ่าน payload จาก val[1] ซึ่งเป็น off-by-one ชนิดที่โผล่เฉพาะไฟล์ที่แคช boolean หรือ error แทนที่จะเป็นตัวเลข ตอนนี้ตัวอ่านกับตัวเขียนสูตรแบบ shared เห็นพ้องกันที่ออฟเซ็ตเดียวกัน แคช TRUE จึงรอดพ้นจากวงจรโหลดและบันทึกได้ครบถ้วน แทนที่จะเน่าเป็นสัญญาณรบกวน

ฟิลด์ FormulaValue แปดไบต์ของเรกคอร์ด Formula ในไฟล์ XLS คลาสสิกตามที่ HotXLS อ่าน: เป็น IEEE 754 double เว้นแต่เวิร์ดสูงเท่ากับ FFFF ซึ่งชนิด variant จะนั่งใน val ศูนย์ และ payload boolean หรือ error นั่งใน val สอง — เมื่อเวิร์ดสูงเป็น FFFF ฟิลด์จะเป็น variant ที่ติดแท็ก และ payload นั่งอยู่ใน val[2] โดย val[1] ไม่นิยาม ซึ่งเป๊ะกับไบต์ที่ตัวอ่านเคยอ่านผิด

ความซื่อตรงต่อชนิดข้อมูลในฟอร์แมตแพ็กเกจเป็นอีกปัญหาหนึ่งที่มีกับดักของตัวเอง ใน OOXML ค่าแคชห้อยอยู่กับอิลิเมนต์ c ผ่าน <v> โดยแอตทริบิวต์ t ระบุชนิดตาม ECMA-376 Part 1 §18.3.1.4 HotXLS อ่าน t="e" เข้า varError Variant ตรง ๆ แล้วแมปกลับเป็นข้อความ error มาตรฐานตอนบันทึก error จึงไม่มีวันปลอมตัวเป็นจำนวนเต็มธรรมดา — แต่ RTL ของ Delphi จะไม่ช่วยคุณตรงนี้ เพราะ VarAsType(Integer, varError) ปล่อย exception การแปลงชนิดออกมา วิธีสร้างที่ใช้งานได้คือกำหนด TVarData.VType กับ TVarData.VError ตรง ๆ วันที่วิ่งเป็นวินัยเดียวกันในทิศตรงข้าม: t="d" กับชนิดค่าวันที่ของ ODF เป็นการประกาศชนิดอย่างชัดเจนและกลายเป็น varDate ขณะที่แคชตัวเลขของ BIFF ไม่มี flag วันที่เลย จึงคงเป็น Double HotXLS ไม่เดาวันที่จากรูปแบบตัวเลขของเซลล์ เพราะรูปแบบตัวเลขคือส่วนนำเสนอ ส่วนแคชคือข้อมูล ODF เติมกรณีหนึ่งที่ควรรู้ — office:value-type="void" แสดงแคชที่มีอยู่แต่ไม่แบกค่าใด และเนื่องจาก ODF ไม่มีชนิดค่า error ข้อความที่ดูเหมือน error จะถูกเก็บเป็นข้อความไว้ ไม่ถูกยกระดับเป็น error

function DescribeCache(const Info: TXLSFormulaCacheInfo): string;
begin
  case Info.State of
    xlfcsNotFormula:  Result := 'not a formula cell';
    xlfcsMissing:     Result := 'formula stored with no cached value';
    xlfcsInvalidated: Result := 'formula replaced since load';
  else
    case Info.Kind of
      xlfcvError:    Result := 'error code ' + IntToStr(TVarData(Info.Value).VError);
      xlfcvDateTime: Result := DateTimeToStr(VarToDateTime(Info.Value));
      xlfcvBoolean:  Result := BoolToStr(Info.Value, True);
      xlfcvNumber:   Result := FloatToStr(Double(Info.Value));
      xlfcvString:   Result := VarToStr(Info.Value);
    else
      Result := 'present but blank';
    end;
  end;
end;

สูตรแบบ shared แชร์ค่าแคชร่วมกันหรือไม่?

ไม่ และการสมมติอย่างอื่นคือวิธีที่การกวาดทั้งชุดรายงานตัวเลขเดียวกันให้ทั้งคอลัมน์ สูตรแบบ shared ของ OOXML แชร์เฉพาะนิพจน์สูตรกับการประหยัดพื้นที่จัดเก็บเท่านั้น เซลล์สมาชิกทุกเซลล์ยังเป็นเจ้าของ <v> ของตัวเอง HotXLS จึงไม่มีวันกระจายแคชของเซลล์รากไปให้สมาชิกที่มาโดยไม่มีค่า และสมาชิกที่โหลดมาแบบ xlfcsMissing ก็ยังรายงาน xlfcsMissing ต่อไปหลังบันทึกและเปิดใหม่ ถ้าคุณกำลังไล่ว่ากลุ่มนี้ถูกเก็บและขยายอย่างไรแต่แรก กลไกของ แอตทริบิวต์ si ของสูตรแบบ shared กับการขยายมันถูกเล่าแยกไว้แล้ว สำหรับการอ่านแคช กฎยุบเหลือบรรทัดเดียว — ถามทุกเซลล์ ไม่เชื่อสิ่งใดที่คุณไม่ได้ถาม

มุมมองของ HotXLS ต่อกลุ่มสูตรแบบ shared ของ OOXML ซึ่งแอตทริบิวต์ si แชร์เฉพาะนิพจน์กับเลย์เอาต์การจัดเก็บ ขณะที่เซลล์สมาชิกทุกเซลล์เป็นเจ้าของค่าแคชของตัวเอง สมาชิกที่มาโดยไม่มีค่าจึงยังรายงาน xlfcsMissing — กลุ่มแชร์นิพจน์ ไม่ใช่ตัวเลข แคชของเซลล์รากจึงไม่มีวันถูกกระจาย และสมาชิกที่มาโดยไม่มีค่ายังรายงานช่องว่างนั้นต่อไป

การอ่านค่าแคช ตัวอ่านแบบรวมคร่อมเอนจิน และเครื่องคำนวณใหม่ที่คุณเลือกไม่เรียกใช้ก็ได้ มาพร้อมกันใน HotXLS Delphi Spreadsheet Component รุ่นมาตรฐานสำหรับ Delphi และ C++Builder โดยไม่พึ่ง Excel หรือเซิร์ฟเวอร์ OLE automation ใด หน้าผลิตภัณฑ์เก็บเอกสาร API ครบถ้วนของจุดเข้าเวิร์กบุ๊กกับตัวอ่านที่โชว์ไว้ในบทความนี้