เขียนไฟล์ XLSX ล้านแถวใน Delphi ด้วยหน่วยความจำที่คงที่

งานจัดทำรายงานอาจทำงานได้ดีมาตลอดทั้งปี มันสร้างเวิร์กบุ๊ก เติมข้อมูลที่ได้จากการสืบค้นลงในชีต และบันทึกมัน จากนั้นก็มีลูกค้าที่มีประวัติข้อมูลถึงห้าปีขอให้ส่งออกแบบเต็มรูปแบบ จำนวนแถวทะลุเกินหนึ่งล้าน และกระบวนการก็ตายลงด้วยข้อผิดพลาดหน่วยความจำไม่เพียงพอ (out-of-memory) ก่อนที่ไฟล์จะไปถึงดิสก์นานเลยทีเดียว ไม่ได้มีอะไรผิดปกติกับโค้ด มันแค่เก็บเวิร์กบุ๊กทั้งหมดไว้ใน RAM เพื่อที่จะสามารถซีเรียลไลซ์มันได้ในตอนท้าย และหน่วยความจำที่มันต้องการก็เติบโตไปพร้อมๆ กับจำนวนแถวที่มันถูกขอให้เขียน

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

ทำไมเส้นทางการบันทึกปกติจึงเติบโตตามข้อมูล

เส้นทาง TXLSXWorkbook แบบปกติจะสร้างโมเดลออบเจ็กต์เต็มรูปแบบก่อน ทุกเซลล์พร้อมกับค่า ประเภท และการอ้างอิงสไตล์ จะทำงานเป็นออบเจ็กต์ในหน่วยความจำจนกว่าคุณจะเรียกการบันทึก ซึ่งเมื่อถึงจุดนั้น ต้นไม้ทั้งหมดก็จะถูกซีเรียลไลซ์ลงในแพ็กเกจ โมเดลนั้นเป็นโมเดลที่ถูกต้องเมื่อคุณต้องการอ่านชีต แก้ไขมัน คำนวณใหม่ และเขียนมันกลับไป เนื่องจากความสามารถในการเข้าถึงเซลล์แบบสุ่มคือสิ่งที่การแก้ไขต้องการพอดี แต่มันคือสิ่งผิดเมื่อคุณกำลังเทแถวไปในทิศทางเดียวและไม่เคยมองกลับมา เพราะคุณต้องจ่ายทรัพยากรเพื่อเก็บทุกแถวไว้ในหน่วยความจำโดยไม่ได้ประโยชน์อะไรกลับมา ออบเจ็กต์หนึ่งล้านแถวก็คือออบเจ็กต์หนึ่งล้านแถว ไม่ว่าคุณจะกลับมาดูมันอีกครั้งหรือไม่ก็ตาม

เครื่องเขียนแบบสตรีมมิ่งจะลบโครงสร้างต้นไม้ออก ทันทีที่เซลล์ถูกเขียน เซลล์นั้นจะกลายเป็นไบต์ในส่วนประกอบเวิร์กชีต และไบต์เหล่านั้นจะถูกส่งต่อไปยังเอาต์พุต zip สตรีมของเวิร์กชีตคือบัฟเฟอร์เดียวที่มีการขยายตัว และมันขยายตัวในฝั่งเอาต์พุต ไม่ใช่ในฐานะออบเจ็กต์ Delphi ที่ทำงานอยู่ในฮีป (heap) สิ่งที่ยังคงอยู่ในหน่วยความจำมีเพียงบัญชีที่ต้องจดไว้จำนวนตายตัว ได้แก่ ชื่อชีต แฟล็กบางตัว หมายเลขแถวปัจจุบัน และตัวนับเซลล์ ชุดนั้นจะไม่เปลี่ยนแปลงระหว่างแถวที่หนึ่งกับแถวที่สิบล้าน

ตารางสตริงที่ใช้ร่วมกันคือกับดัก และสตริงแบบอินไลน์คือทางออก

เครื่องเขียน XLSX แบบสตรีมมิ่งส่วนใหญ่ทำงานได้ดีจนกระทั่งมันเจอข้อความ โดยปกติแล้ว รูปแบบ OOXML จะจัดเก็บสตริงในตารางสตริงที่ใช้ร่วมกัน (shared-string table): แต่ละสตริงที่แตกต่างกันจะถูกเขียนไว้ครั้งเดียวในส่วนที่แยกกัน และทุกเซลล์ที่มีสตริงนั้นจะนำดัชนีชี้ไปยังตารางติดตัวไปแทนข้อความ มันเป็นการเพิ่มประสิทธิภาพพื้นที่ที่ดีสำหรับไฟล์ที่เต็มไปด้วยป้ายกำกับที่ซ้ำๆ กัน และมันก็เป็นค่าเริ่มต้นที่เส้นทางการบันทึกมาตรฐานใช้ ปัญหาสำหรับเครื่องเขียนแบบสตรีมมิ่งนั้นหนักหน่วง ในการลบรายการที่ซ้ำกัน ตารางจะต้องถูกเก็บไว้ในหน่วยความจำตลอดทั้งงาน เนื่องจากแถวใดๆ ที่ยังมาไม่ถึงอาจมีสตริงซ้ำกับแถวที่เขียนไปแล้วก็ได้ และต้องมีแค่แผนที่ที่สมบูรณ์ในหน่วยความจำของสตริงที่เคยเห็นแล้วเท่านั้น จึงจะสามารถกำหนดดัชนีที่ถูกต้องได้ ดังนั้น โครงสร้างเดียวที่เครื่องเขียนแบบสตรีมมิ่งไม่สามารถสตรีมได้ ก็คือโครงสร้างที่ควรจะทำให้ไฟล์มีขนาดเล็กนั่นเอง ข้อมูลที่เน้นข้อความหนักๆ จะเอาชนะจุดประสงค์การสตรีมที่คุณต้องการตั้งแต่แรก

เครื่องเขียนโดยตรงหลีกเลี่ยงตารางได้อย่างสิ้นเชิง สตริงต่างๆ จะถูกเขียนแบบอินไลน์ (inline) ในฐานะเซลล์ t="inlineStr" ซึ่งข้อความจะอยู่ตรงๆ ภายในเซลล์พร้อมด้วยอิลิเมนต์ <is><t> ไม่มีตารางให้สะสมและไม่มีแผนที่ของสตริงที่เห็นแล้วให้ถือครอง คอลัมน์ข้อความจึงไม่เปลืองหน่วยความจำไปกว่าคอลัมน์ตัวเลข การแลกเปลี่ยนนี้ชัดเจนและคุ้มค่าที่จะระบุอย่างตรงไปตรงมา สตริงแบบอินไลน์จะทำซ้ำข้อความเดียวกันทุกที่ที่มันเกิดขึ้น ดังนั้นไฟล์ที่มีป้ายกำกับที่เหมือนกันหลายอันจึงมีขนาดบนดิสก์ใหญ่กว่าไฟล์ที่มีสตริงที่ใช้ร่วมกันแบบเทียบเท่ากัน คุณเสียขนาดไฟล์เพื่อซื้อหน่วยความจำที่คงที่ สำหรับการส่งออกแบบรอบเดียว นี่คือการแลกเปลี่ยนที่ถูกต้อง และการบีบอัด zip จะดูดซับความซ้ำซ้อนส่วนใหญ่ไว้ระหว่างทางออกอยู่แล้ว

ตารางสไตล์จะมาถึงในตอนท้าย พร้อมรูปแบบวันที่หนึ่งรูปแบบ

สไตล์ก็นำเสนอความตึงเครียดเช่นเดียวกับสตริง เวิร์กบุ๊กอ้างอิงการจัดรูปแบบผ่านส่วนประกอบสไตล์ และเครื่องเขียนแบบสตรีมมิ่งไม่สามารถเก็บจานสไตล์ที่กำลังเติบโตให้สอดคล้องกับเซลล์ที่มันได้ส่งออกไปแล้วได้ เครื่องเขียนโดยตรงจะตอบโจทย์นี้โดยการเก็บตารางสไตล์ให้มีขนาดเล็กและคงที่ และปล่อยมันออกไปเมื่อปิดไฟล์แทนที่จะทำตั้งแต่แรก รูปแบบเซลล์ค่าเริ่มต้นหนึ่งรูปแบบจะครอบคลุมเซลล์ทั่วไป รูปแบบตัวเลขของวันที่หนึ่งรูปแบบจะครอบคลุมวันที่ โดยลงทะเบียนด้วยรหัสรูปแบบ yyyy-mm-dd ที่ตำแหน่งที่ทราบในรายการรูปแบบเซลล์

รูปแบบวันที่นั้นคือเหตุผลว่าทำไม WriteDateTime ถึงมีอยู่เป็นการเรียกของมันเอง Excel ไม่มีชนิดข้อมูลวันที่ดั้งเดิม วันที่ก็คือตัวเลขที่สวมใส่รูปแบบวันที่ WriteDateTime จะเขียนค่าเป็นตัวเลขอนุกรม (serial number) ล้วนๆ และแท็กเซลล์ด้วยรูปแบบวันที่เดียวนี้ ดังนั้นสเปรดชีตจึงจะแสดงมันเป็นวันที่แทนที่จะเป็นจำนวนเต็มห้าหลัก อนุกรมที่มันเขียนนั้นมีความสำคัญสำหรับการรับและส่งกลับ (round-tripping) มันจัดเก็บค่า TDateTime ภายใต้ระบบวันที่ปี 1900 โดยตรง ซึ่งเป็นข้อกำหนดเดียวกับที่เส้นทางการบันทึก TXLSXWorkbook ปกติใช้ เนื่องจากทั้งสองเส้นทางเห็นด้วยกับตัวเลขอนุกรม ไฟล์ที่เครื่องเขียนแบบสตรีมมิ่งผลิตขึ้นจึงสามารถอ่านกลับผ่านเครื่องอ่าน HotXLS และเปิดใน Excel ด้วยวันที่ที่ตรงกับที่คุณตั้งใจไว้ โดยไม่ต้องเจอเซอร์ไพรส์ความผิดพลาดแบบคลาดเคลื่อนไปหนึ่งวัน (off-by-one) หรือยุคสมัย (epoch) ระหว่างเครื่องเขียนกับเครื่องอ่าน

ลำดับเป็นสิ่งจำเป็น เนื่องจากไบต์เหล่านั้นได้จากไปแล้ว

การสตรีมซื้อโปรไฟล์หน่วยความจำด้วยกฎข้อหนึ่งที่คุณต้องเคารพ เอาต์พุตจะถูกส่งออกไปเรื่อยๆ และไม่สามารถกลับไปเยี่ยมชมได้อีก ดังนั้นทุกอย่างจึงต้องเขียนตามลำดับที่ปรากฏในไฟล์ ภายในแถวหนึ่ง เซลล์จะไปตามลำดับคอลัมน์ที่เพิ่มขึ้น ภายในชีตหนึ่ง แถวจะไปตามลำดับที่เพิ่มขึ้น ไม่มีบัฟเฟอร์ที่ให้เครื่องเขียนจัดเรียงเซลล์ของคุณหลังจากข้อเท็จจริงผ่านไปแล้ว เนื่องจากแถวที่คุณเพิ่งปิดไปเมื่อสักครู่ได้กลายเป็นไบต์ใน zip สตรีมและไม่สามารถเข้าถึงได้อีกแล้ว ส่งคอลัมน์ที่ 5 แล้วตามด้วยคอลัมน์ที่ 2 ในแถวเดียวกันให้กับมัน แล้วเอาต์พุตก็จะผิดรูป เนื่องจากเครื่องเขียนเพียงแค่ส่งออกสิ่งที่คุณมอบให้ในลำดับที่คุณมอบให้มัน

API ของแถวมีความสะดวกสบายเล็กๆ สำหรับกรณีทั่วไป AddRow ใช้อินเด็กซ์แถวแบบเริ่มที่ 1 (1-based) แต่การส่งผ่าน 0 หมายถึงการใช้แถวถัดไปต่อจากแถวก่อนหน้า ดังนั้นการเติมแบบต่อเนื่องจึงไม่ต้องติดตามและส่งผ่านตัวนับที่เพิ่มขึ้น AddRow แต่ละครั้งจะปิดแถวก่อนหน้า และ AddSheet แต่ละครั้งจะปิดชีตก่อนหน้า ดังนั้นคุณจึงไม่เคยยุติแถวหรือชีตอย่างชัดแจ้ง คุณเริ่มส่วนถัดไปและเครื่องเขียนจะสรุปโครงสร้างที่เปิดอยู่ให้คุณเอง

การทำ Escape จะได้รับการจัดการในจุดที่ข้อความเข้าสู่ XML

ข้อความใดๆ ที่คุณเขียนจะกลายเป็นส่วนหนึ่งของเอกสาร XML ดังนั้นจึงต้องมี escape สำหรับเอนทิตี XML ที่กำหนดไว้ล่วงหน้าห้าตัว มิฉะนั้นแพ็กเกจจะไม่ถูกต้องในทันทีที่ค่าประกอบด้วยเครื่องหมายแอมเพอร์แซนด์หรือวงเล็บมุม เครื่องเขียนจะทำ escape &, <, >, " และ ' ให้คุณทั้งในข้อความสตริงแบบอินไลน์และข้อความสูตร ซึ่งเป็นสองจุดที่ตัวอักษรที่ผู้โทรจัดหาให้จะตกไปอยู่ในมาร์กอัป (markup) คุณส่ง WideString แบบดิบๆ แล้วเครื่องเขียนจะทำให้มันปลอดภัย ชื่อผลิตภัณฑ์อย่าง Smith & Co <Ltd> หรือสูตรอ้างอิงชื่อชีตที่มีเครื่องหมายคำพูด จะออกมาเป็น XML ที่ก่อตัวอย่างดีโดยไม่ต้องมีขั้นตอน escape ใดๆ ในฝั่งของคุณ

วงจรชีวิตและเหตุผลที่ Destroy ยังคงปิดการทำงาน

การสิ้นสุดแพ็กเกจคือสิ่งที่เขียนส่วนประกอบเวิร์กบุ๊ก ส่วนประกอบสไตล์ ส่วนประกอบ content-types และความสัมพันธ์ (relationship) และสุดท้ายคือไดเร็กทอรีกลางของ zip งานนั้นจะเกิดขึ้นใน Close แพ็กเกจที่ไม่เคยถูกปิดก็คือไฟล์ zip ที่ไม่สมบูรณ์ซึ่งจะไม่มีโปรแกรมสเปรดชีตใดเปิดได้ ดังนั้นการปิดไฟล์จึงไม่ใช่แค่การล้างข้อมูลทางเลือก แต่มันคือขั้นตอนที่ทำให้ไฟล์นั้นถูกต้อง เพื่อป้องกันการลืม Close ในเส้นทางที่มีข้อผิดพลาด Destroy จะทำหน้าที่ปิดไฟล์อย่างเต็มความสามารถหากแพ็กเกจยังเปิดอยู่ ดังนั้นการปล่อยเครื่องเขียนเป็นอิสระจะไม่ทำให้รั่วไหลของออบเจ็กต์ zip พื้นฐาน แม้ว่าข้อยกเว้นจะข้ามการเรียกอย่างชัดแจ้งไปก็ตาม รูปแบบที่เชื่อถือได้ยังคงเป็นแบบ Delphi ธรรมดา: เขียนภายใน try, เรียก Close และปล่อยเป็นอิสระใน finally

การสตรีมชีตขนาดใหญ่ตั้งแต่ต้นจนจบ

รูปร่างของงานคือเริ่มต้น เพิ่มชีต เทข้อมูลแถว และปิด ตัวอย่างด้านล่างเขียนแถวส่วนหัวแล้วตามด้วยชุดของแถวข้อมูลตามชนิดที่พิมพ์ไว้ ผสมผสานสตริง ตัวเลข สูตรที่ไม่มีผลลัพธ์แคช และวันที่ หน่วยความจำที่ใช้สำหรับสิบแถวกับสิบล้านแถวนั้นเท่ากัน เพราะทุกเซลล์จะออกไปยังสตรีม zip ทันทีที่มันถูกเขียน

uses
  lxDirectWrite;

procedure StreamReport(const Path: string; RowCount: Integer);
var
  W: TXLSDirectWriter;
  I: Integer;
begin
  W := TXLSDirectWriter.Create;
  try
    W.BeginFile(Path);
    W.AddSheet('Sales');

    // Header row, written in ascending column order
    W.AddRow(1);
    W.WriteString(1, 'Item');
    W.WriteString(2, 'Qty');
    W.WriteString(3, 'Price');
    W.WriteString(4, 'Total');
    W.WriteString(5, 'Date');

    // Data rows; pass 0 to AddRow to take the next row automatically
    for I := 1 to RowCount do
    begin
      W.AddRow(0);
      W.WriteString(1, 'Item ' + IntToStr(I));
      W.WriteNumber(2, I);
      W.WriteNumber(3, 1.5 + (I mod 10));
      W.WriteFormula(4, Format('B%d*C%d', [I + 1, I + 1]));
      W.WriteDateTime(5, EncodeDate(2026, 1, 1) + I);
    end;

    W.Close;                       // finalises the package
  finally
    W.Free;
  end;
end;

ชีตที่สองเป็นเพียงการใช้ AddSheet อีกครั้งก่อนที่คุณจะดำเนินการต่อ และเครื่องเขียนจะปิดชีตแรกในขณะที่มันเปิดชีตที่สอง แฟล็กบูลีนจะใช้ WriteBoolean ซึ่งจะเขียนเซลล์บูลีนแบบตามชนิด (typed boolean cell) แทนที่จะเป็นข้อความ "True" หากคุณต้องการยืนยันว่าไฟล์นั้นแข็งแรงและการรับและส่งกลับ (round-trips) ได้ พร็อพเพอร์ตี้ CellCount จะรายงานว่ามีการเขียนเซลล์ไปกี่เซลล์ และการอ่านผลลัพธ์กลับด้วยเครื่องอ่านแบบสตรีมมิ่งควรรายงานผลรวมที่ตรงกัน

  // A second sheet of typed flags after the data sheet above
  W.AddSheet('Flags');
  W.AddRow(1);
  W.WriteString(1, 'Name');
  W.WriteString(2, 'Active');
  W.AddRow(0);
  W.WriteString(1, 'alpha');
  W.WriteBoolean(2, True);

  WriteLn(Format('wrote %d cells', [W.CellCount]));

การเขียนลงในสตรีมแทนไฟล์คือโค้ดเดียวกันที่มี BeginStream เข้ามาแทนที่ BeginFile ซึ่งทำให้เซิร์ฟเวอร์ส่งเวิร์กบุ๊กไปยังผลตอบรับ HTTP (HTTP response) หรือสตรีมหน่วยความจำโดยไม่ต้องมีไฟล์ชั่วคราวบนดิสก์ เครื่องเขียนไม่ได้เป็นเจ้าของสตรีมที่คุณส่งผ่านมาให้ ดังนั้นคุณจึงรักษาการควบคุมอายุการใช้งานของมันเอาไว้ได้

เมื่องานคือจุดปลายทางของเซิร์ฟเวอร์ (server endpoint) ที่สร้างเวิร์กบุ๊กตามความต้องการ รูปแบบใน การเขียนแบบสตรีมมิ่งสำหรับเซิร์ฟเวอร์และงานแบทช์ จะแสดงวิธีเชื่อมสิ่งนี้เข้ากับตัวจัดการคำขอ (request handler) และการส่งออกตามกำหนดเวลา เมื่อคำถามคือค่าใช้จ่ายในภาพรวมที่กว้างขึ้นของเวิร์กบุ๊กขนาดใหญ่มาก ทั้งการอ่านและการเขียน ประสิทธิภาพของเวิร์กบุ๊กขนาดใหญ่ใน Delphi จะครอบคลุมถึงว่าเวลาและหน่วยความจำหมดไปกับสิ่งใดจริงๆ เครื่องเขียนโดยตรงแบบสตรีมมิ่งนี้มีจัดส่งให้โดยเป็นส่วนหนึ่งของ HotXLS Component สำหรับ Delphi และ C++Builder ควบคู่ไปกับ API แบบเต็มรูปแบบเพื่อการอ่าน แก้ไข และการบันทึกที่ครอบคลุมในส่วนอื่นๆ ของบล็อกนี้