การขยาย Shared Formula si ของ XLSX ใน Delphi: กับดักต่าง ๆ

Shared formula follower ใน XLSX ไม่บรรทุกข้อความฟอร์มูล่าไว้เลย element <f t="shared" si="N"/> ของมันชี้ไปยัง master cell ที่อื่นในเวิร์กชีต และตัวอ่านต้องสร้างข้อความขึ้นใหม่ด้วยการเลื่อน master formula ตามผลต่างของแถวและคอลัมน์ HotXLS Component สำหรับ Delphi และ C++Builder ทำการขยายนี้ตอนเปิดไฟล์ ดังนั้น follower ทุกตัวจึงรายงานฟอร์มูล่าที่สมบูรณ์

ถ้าคุณเคยโหลด XLSX จริง ๆ ในไลบรารีของบุคคลที่สามแล้วพบว่าคอลัมน์ที่มีฟอร์มูล่านับพันตัวมีข้อความอยู่แค่ cell เดียวและเป็นสตริงว่างเปล่าในอีก 999 cell คุณก็เคยเจอฟีเจอร์นี้จากฝั่งที่ผิดมาแล้ว ไม่มีอะไรเสียหายเลย ไฟล์กำลังทำในสิ่งที่ ECMA-376 อนุญาตให้มันทำ และตัวอ่านก็แค่หยุดอยู่ที่จุดที่ XML หยุด

ทำไม cell ของ shared formula ถึงว่างเปล่า

เพราะรูปแบบไฟล์จงใจเก็บฟอร์มูล่าไว้แค่ครั้งเดียว ใน ECMA-376 Part 1 และ ISO/IEC 29500-1 element <f> (§18.3.1.40) บรรทุก attribute t ชนิด ST_CellFormulaType และค่า shared หมายถึง cell นี้เป็นสมาชิกของกลุ่มที่ระบุด้วย attribute si มีพอดีหนึ่ง cell ในกลุ่มนั้น คือ master ที่บรรทุก attribute ref ด้วย ซึ่งให้ range ที่กลุ่มนี้ใช้ และมีแค่ cell นั้นเท่านั้นที่บรรทุกข้อความฟอร์มูล่าเป็นเนื้อหาของ element cell อื่นทุกตัวในกลุ่มเป็น follower มันซ้ำ t="shared" และ si เดียวกัน และเนื้อหา element ของมันว่างเปล่า Excel เขียนกลุ่มเหล่านี้อย่างจริงจัง เพราะการ fill-down ผ่านคอลัมน์ 200,000 แถวยุบจาก 200,000 สตริงฟอร์มูล่าเหลือหนึ่งสตริงบวก element ตัวยึดขนาดจิ๋วอีก 199,999 ตัว การประหยัดนั้นเกิดขึ้นจริง และต้นทุนตกอยู่ที่ตัวอ่านทั้งหมด: หากไม่มีการขยาย follower ก็ไม่มีความหมายอะไรเลยด้วยตัวมันเอง

การเลื่อนคือการแปล ไม่ใช่การคัดลอกข้อความ

HotXLS resolve follower ด้วยการหา master ที่ลงทะเบียนไว้ภายใต้ si เดียวกัน คำนวณผลต่างของแถวและคอลัมน์จากจุดยึดของ master ไปยัง cell ปัจจุบัน แล้วแปล reference ทุกตัวใน master formula ด้วยผลต่างนั้น มิติแบบ relative ขยับ มิติแบบ absolute ไม่ขยับ และ reference แบบผสมขยับแค่ครึ่งที่ไม่ absolute ของมัน string literal ถูกข้ามไปทั้งหมด ดังนั้นฟอร์มูล่าที่บังเอิญมีข้อความ "A1" จะคงข้อความนั้นไว้ไม่เปลี่ยนแปลงในทุก follower

const
  // xl/worksheets/sheet1.xml, trimmed to the interesting cells
  SheetXml: WideString=
    '<row r="1"><c r="A1"><v>1</v></c>'+
    '<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
    'A1+$A$1+A$1+$A1+&quot;A1&quot;+SUM(A1:A2)</f><v>7</v></c></row>'+
    '<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
    '<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
begin
  Wb:= TXLSXWorkbook.Create;
  try
    Wb.Open(FileName);
    Sh:= Wb.Sheets[1];
    // Master, verbatim
    // B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
    // Follower one row down: relative row moves, absolute row frozen,
    // the mixed A$1 keeps its row, and the literal stays a literal
    // B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
    ShowMessage(Sh.Cells[2, 2].Formula);
  finally
    Wb.Free;
  end;
end;

Attribute ref เป็นประตูตรวจสอบ ไม่ใช่ของตกแต่ง follower ที่พิกัดของมันตกอยู่นอก range ที่ master ใช้งานได้จะไม่ถูกขยาย เพราะไฟล์กำลังอ้างสิทธิ์บางอย่างที่กลุ่มนั้นไม่รองรับ ในทำนองเดียวกัน เมื่อการเลื่อนจะดัน reference ให้พ้นแถวหนึ่งขึ้นไปหรือพ้นคอลัมน์ A ไปทางซ้าย HotXLS จะปล่อย #REF! สำหรับ token นั้นแทนที่จะ clamp มันอย่างเงียบ ๆ ซึ่งเป็นสิ่งที่ Excel เองจะผลิตออกมาสำหรับการแก้ไขแบบเดียวกัน การแปลนี้เป็นญาติใกล้ชิดกับ แต่ไม่ใช่สิ่งเดียวกันกับ การเขียน reference ใหม่ที่เกิดขึ้นเมื่อคุณแทรกหรือลบแถว path นั้นมีกฎของตัวเองว่า range ทำอะไรเมื่อการแก้ไขตัดผ่านมัน และอธิบายแยกไว้ใน บทความเรื่องการปรับ formula reference ระหว่างแทรกและลบ การขยาย shared นั้นง่ายกว่า: มันเป็นแค่ offset บริสุทธิ์จากจุดยึดที่รู้จัก ถูกใช้ครั้งเดียวตอน parse

รูปทรง reference แบบไหนบ้างที่ตัวเลื่อนต้องครอบคลุม

ทั้งหมด ไม่อย่างนั้นการขยายก็เป็นบั๊กสูญเสียข้อมูลที่แฝงตัวมา ตัวเลื่อนที่ไร้เดียงสาที่เข้าใจแค่ A1 กับ A1:B2 จะทำให้รูปแบบที่แปลกกว่านั้นเสียหายหรือหายไป และ workbook จริงก็เต็มไปด้วยรูปแบบเหล่านั้น ตัวแปล shared-formula ของ HotXLS จำตระกูล A1 ทั้งหมดได้ก่อนที่มันจะตัดสินใจว่าจะย้ายอะไร reference ของ workbook ภายนอกอย่าง [Book.xlsx]Sheet1!A1 และ reference แบบ 3D อย่าง Sheet1:Sheet3!A1 คง prefix ของมันไว้ครบถ้วนในขณะที่ cell reference ที่ตามหลังเลื่อน ชื่อ sheet ที่อยู่ใน quote รอดด้วย รวมถึงกรณีที่ยุ่งยากที่ sheet ถูกตั้งชื่อว่า A1 ตามตัวอักษร ดังนั้น 'A1'!A1 จึงเลื่อนแค่ส่วนหลังเครื่องหมายอัศเจรีย์เท่านั้น คอลัมน์ทั้งหมด A:A ย้ายมิติคอลัมน์ของมันอย่างเดียว แถวทั้งหมด 1:1 ย้ายมิติแถวของมันอย่างเดียว $A:$A ไม่ย้ายเลย reference ของ table แบบมีโครงสร้างอย่าง Table[A1] ถูกปล่อยไว้ไม่แตะต้อง เพราะส่วนในวงเล็บเหลี่ยมเป็นชื่อคอลัมน์ ไม่ใช่พิกัด

// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1       : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3       : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]

ชื่อฟังก์ชันคือกับดักเงียบตรงนี้ token scanner ที่คว้าตัวอักษรตามด้วยตัวเลขจะเขียน LOG10 ใหม่เป็น LOG11 อย่างสบายใจเมื่อเลื่อนลงหนึ่งแถว HotXLS ต้องการขอบเขต reference ก่อนและหลัง token ที่เป็นตัวเลือก ดังนั้น identifier ที่ต่อเนื่องไปเป็นตัวอักษร ตัวเลข underscore จุด หรือวงเล็บเปิด จึงไม่ใช่ cell reference ถ้าคุณทำงานใน notation อีกตระกูลหนึ่ง ปัญหาขอบเขตแบบเดียวกันนี้ก็ปรากฏต่างออกไป และ บทความเรื่อง R1C1 notation ครอบคลุมจุดที่โมเดลทั้งสองแตกต่างกัน

ทำไม element f แบบ self-closing ถึงกลืนค่าถัดไป

เพราะ element แบบ self-closing ไม่สร้าง event end-element เลย นี่คือบั๊กที่มีต้นทุนแพงที่สุดตัวเดียวในฟีเจอร์ทั้งหมดนี้ และมันไม่ได้เจาะจงกับ XML parser ตัวใดตัวหนึ่ง ใน TXMLReader, <f t="shared" si="4"/> สร้าง event Element พอดีหนึ่งครั้งโดยตั้ง IsEmptyElement เป็น True และไม่เคยสร้าง EndElement ที่จับคู่กันเลย parser ที่ปิด state การจับฟอร์มูล่าของมันแค่ตอน EndElement เท่านั้นจึงยังคงอยู่ภายในฟอร์มูล่าต่อไป และข้อความถัดไปที่มันเห็น ซึ่งคือผลลัพธ์ที่แคชไว้ภายใน <v> ก็ถูกต่อเข้าไปใน buffer ของฟอร์มูล่า ที่แย่กว่านั้น state นี้รอดข้ามขอบเขตของ cell ไปด้วย ดังนั้น cell ถัดไปที่มี <f> จริงจึงมีข้อความฟอร์มูล่าของมันถูกดูดซับไปโดย cell ก่อนหน้า วิธีแก้คือให้จบ state ของฟอร์มูล่าที่ event Element เองเมื่อใดก็ตามที่ IsEmptyElement เป็น True และรัน resolve follower ทั้งหมดตรงนั้นแทนที่จะรอ นั่นหมายถึงการอ่าน t, si, ref, aca และ ca จาก attribute ใช้การขยาย shared เขียน attribute การคำนวณใหม่ลงบน cell และล้าง state ของ shared ทั้งหมดภายใน branch ที่จัดการ empty element สังเกตว่ารูปแบบไฟล์อนุญาตทั้งสองแบบสะกด คือ <f t="shared" si="4"/> และ <f t="shared" si="4"></f> และแบบที่สองนั้นสร้าง EndElement จริง ตัวอ่านที่ถูกต้องต้องจัดการทั้งคู่เหมือนกัน นี่คือเหตุผลที่ HotXLS ครอบคลุมทั้งสองแบบสะกดในไฟล์ regression เดียวกัน

ค่า si ที่กระจัดกระจายไม่เรียงลำดับ และคิวที่ค้างอยู่

Attribute si เป็นจำนวนเต็มไม่ติดลบที่ไฟล์กำหนดมาให้ ไม่ใช่ตำแหน่ง array ที่คุณควบคุมได้ ไม่มีอะไรใน schema ที่บังคับให้ shared index ต้องหนาแน่น เริ่มที่ศูนย์ หรือปรากฏตามลำดับจากน้อยไปมาก และไม่มีอะไรหยุดไฟล์ที่ประสงค์ร้ายหรือแค่แปลกไม่ให้ใช้ si="4294967290" บน cell แรก การกำหนดขนาด lookup array จากค่า si ที่สูงสุดที่พบจึงเป็นกลไกทำให้หน่วยความจำหมด ไม่ใช่การเพิ่มประสิทธิภาพ HotXLS คง path การเปิด workbook ไว้บนตารางแบบ sparse ที่เรียงลำดับแทน: กลุ่ม shared ลงทะเบียนภายใต้คีย์จำนวนเต็มของมันใน TStringList ที่เรียงลำดับไว้ ซึ่งทำให้การค้นหาเป็น binary search ผ่านจำนวนกลุ่มที่มีอยู่จริงเท่านั้น โดยไม่มีความเกี่ยวข้องกับขนาดตัวเลขของ index เลย ลำดับคือครึ่งที่สองของปัญหา master มักจะอยู่ก่อน follower ของมันตามลำดับเอกสาร แต่นั่นเป็นธรรมเนียมมากกว่ากฎ ดังนั้น follower ใดก็ตามที่ resolve si ของมันไม่ได้ในขณะที่ถูก parse จะเข้าไปในคิวที่ค้างอยู่ เมื่อ sheet จบลง คิวจะถูกเล่นซ้ำเทียบกับตารางที่สมบูรณ์แล้วในตอนนั้น และ master ที่มาช้าก็ resolve ลูกกำพร้าของมันได้ Cell ที่ไม่เคยพบ master เลยจะคงฟอร์มูล่าว่างเปล่าไว้ ซึ่งเป็นผลลัพธ์ที่ซื่อตรงสำหรับไฟล์ที่อ้างถึงกลุ่มที่มันไม่เคยนิยามไว้เลย

การขยาย shared formula โดยไม่โหลด workbook ทั้งเล่ม

ตัวอ่านแบบ streaming เผชิญข้อกำหนดเดียวกันนี้ภายใต้งบหน่วยความจำที่จำกัดกว่ามาก และมันแก้ปัญหาด้วยตารางที่จำกัดขอบเขตแค่ระดับเวิร์กชีต TXLSDirectReader และ TXLSRowCursor ทั้งคู่ขยาย follower เป็นฟอร์มูล่าเต็มต่อ cell ในขณะที่ยังคงพฤติกรรมหน่วยความจำจำกัดและการฉายภาพของมันไว้ ดังนั้นการผ่านแบบเดินหน้าอย่างเดียวบนชีตขนาด 300 MB ก็ยังส่งข้อความฟอร์มูล่าจริงให้คุณได้

var
  Reader: TXLSDirectReader;
  Cursor: TXLSRowCursor;
begin
  // Projection: only rows 2..3, only column A. The master lives in row 1,
  // outside the projection, and is still parsed so the followers resolve
  Reader:= TXLSDirectReader.Create;
  try
    Reader.FirstRow:= 2;
    Reader.LastRow:= 3;
    Reader.IncludeColumn(1);
    Reader.OnCell:= HandleCell;   // Cell.Formula is fully expanded here
    Reader.ReadFile(FileName);
  finally
    Reader.Free;
  end;

  // Forward-only row traversal, same expansion
  Cursor:= TXLSRowCursor.Create;
  try
    Cursor.Open(FileName);
    if Cursor.FindFirst then
      repeat
        if Cursor.CellCount > 0 then
          WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
      until not Cursor.FindNext;
  finally
    Cursor.Free;
  end;
end;

มีข้อจำกัดสองข้อที่ตกออกมาจากการออกแบบนั้น ข้อแรก การฉายภาพไม่มีวันข้าม master ได้เลย ตัวกรองแถวที่ตั้งด้วย FirstRow กับ LastRow หรือตัวกรองคอลัมน์ที่สร้างด้วย IncludeColumn อาจข้ามการส่ง master cell ไปยัง callback ของคุณ แต่ parser ยังต้องบันทึก si, พิกัดจุดยึด, range ที่ใช้งานได้ และข้อความฟอร์มูล่าของมันไว้อยู่ดี มิฉะนั้น follower ทุกตัวภายในการฉายภาพนั้นจะ resolve ไม่ได้เลย มีแค่งานฝั่ง follower เท่านั้น คือการเลื่อนกับการ decode ค่า ที่ข้ามได้อย่างปลอดภัย ข้อสอง ตารางนี้อยู่ในระดับเวิร์กชีตและ lifetime ของมันต้องถูกจัดการอย่างชัดเจน: TXLSRowCursor เก็บ instance หนึ่งไว้ตลอดการผ่าน sheet หนึ่งครั้งและล้างมันเมื่อเริ่มใหม่ เปลี่ยน sheet จบไฟล์ เกิด exception และปิด ดังนั้นกลุ่มที่นิยามไว้บน sheet หนึ่งจะไม่มีวันรั่วไหลเข้าไปใน sheet สองได้เลย เพราะ path แบบ streaming เป็น hot loop มันใช้ open-addressing integer hash แทนตาราง string ที่เรียงลำดับไว้ ซึ่งหลีกเลี่ยงการแปลง integer เป็น string ต่อ cell

เกิดอะไรขึ้นตอนเซฟ และขอบเขตอยู่ตรงไหน

เมื่อ follower ถูกขยายแล้ว มันก็เป็นฟอร์มูล่าธรรมดา และ HotXLS เขียนมันกลับเป็น element <f> อิสระโดยไม่มี t="shared" และไม่มี si การ round trip มีเสถียรภาพและผลลัพธ์ <v> ที่แคชไว้รอดอยู่ แต่ output ใหญ่กว่า input สำหรับ sheet ที่ใช้ shared อย่างหนัก และการจัดกลุ่มที่ Excel สร้างไว้ไม่ถูกสร้างใหม่ตอนเซฟ ถ้าความเที่ยงตรงระดับ byte ของกลุ่ม shared สำคัญกับคุณมากกว่าการมีข้อความฟอร์มูล่าจริงในทุก cell นี่คือการแลกที่คุณกำลังยอมรับ ฝั่ง XLS ต่างออกไปเล็กน้อย โดยบังเอิญ record SHRFMLA ของ BIFF8 มี encoding ของตัวเองและตัวเขียนของตัวเอง พร้อมสวิตช์เปิดปิดกลุ่ม shared บน workbook

มีสองสิ่งที่เกี่ยวข้องกันซึ่งไม่ใช่ shared formula อย่างชัดเจนแม้จะใช้ element <f> ร่วมกันก็ตาม CSE array formula แบบเก่าใช้ t="array" ที่มี ref ครอบคลุม range ที่ยึดไว้ และ dynamic array ใช้การสะกด t="array" เดียวกันแต่ระบุด้วย attribute cm ที่เชื่อมผ่าน cellMetadata ไปยัง record XLDAPR การปฏิบัติต่อ dynamic-array spill cell เหมือนเป็น follower ของ shared หรือ CSE เป็นบั๊กด้านความถูกต้องอย่างแท้จริง และการแยกแยะครอบคลุมไว้ใน บทความเรื่อง dynamic array และ spill formula อ่านสามกรณีนี้เป็นสาม parser ที่บังเอิญใช้ชื่อ tag ร่วมกัน แล้วโค้ดก็จะยังคงซื่อตรง

การขยาย shared-formula, ตัวอ่านแบบ streaming และตัวแปล reference ที่อธิบายไว้ที่นี่มาพร้อมกับ HotXLS Excel component สำหรับ Delphi และ C++Builder หน้าผลิตภัณฑ์มีเอกสารอ้างอิง API ฟอร์มูล่าและการอ่านโดยตรงแบบเต็ม รวมถึง property การฉายภาพที่ใช้ข้างต้น