ประสิทธิภาพของเวิร์กบุ๊ก Excel ขนาดใหญ่ใน Delphi ด้วย HotXLS

เมื่อการ export 300,000 แถวทะลุงบประมาณหน่วยความจำ จำนวนแถวมักถูกโทษก่อนเสมอ แต่จำนวนแถวมักไม่ใช่ตัวการจริง ๆ ส่วนที่แพงของ workbook ขนาดใหญ่มักเป็นสิ่งที่ถูกสร้างขึ้นเป็นผลข้างเคียง: style pool ที่โตขึ้นทีละหนึ่งรายการต่อหนึ่งเซลล์เพราะ formatting ถูกเพิ่มเข้าไปภายใน loop, XML ของ worksheet ที่ถูกประกอบเป็นสตริงก้อนยักษ์ก้อนเดียวตอน save, เนื้อหาสูตรที่เหมือนกันเป๊ะนับล้านชุดที่ถูกเก็บทีละตัว HotXLS ไลบรารี Delphi แบบ native ของ losLab สำหรับไฟล์ XLS และ XLSX ให้ปุ่มปรับเฉพาะสำหรับแต่ละต้นทุนเหล่านี้ ไม่มีปุ่มไหนเปิดใช้งานเป็นค่าเริ่มต้นเลย เพราะแต่ละปุ่มเปลี่ยน trade-off ดังนั้นการรู้ว่าปุ่มปรับตัวไหนตรงกับอาการแบบไหนคือทักษะด้านประสิทธิภาพที่แท้จริง

workbook ขนาดใหญ่ใช้หน่วยความจำที่ไหนบ้าง

มีระบอบหน่วยความจำสองแบบที่แยกจากกันซึ่งควรพิจารณา ระหว่าง การสร้าง โมเดลเซลล์ในหน่วยความจำจะโตขึ้นทุกครั้งที่คุณแตะเซลล์: ค่า รูปแบบ และสูตรทั้งหมดกลายเป็น object หรือรายการใน pool ระหว่าง การ save เส้นทาง XLSX แบบเริ่มต้นยัง render XML ของแต่ละ worksheet ให้เป็นสตริงกว้างก่อนที่จะบีบอัดมันเข้าไปใน zip container เพิ่มเติมด้วย ดังนั้นการใช้งานสูงสุดจึงเป็นโมเดล บวกกับ รูปแบบที่ serialize แล้วของ sheet ที่ใหญ่ที่สุด งานที่รอดจาก build loop แล้วมาตายภายใน SaveAs กำลังชนกับระบอบที่สอง ไม่ใช่ระบอบแรก และวิธีแก้สำหรับอันหนึ่งก็ไม่ช่วยอะไรกับอีกอันเลย

สองระบอบหน่วยความจำในงานเวิร์กบุ๊กใหญ่ของ HotXLS บน Delphi: โมเดลเซลล์ในหน่วยความจำที่ลูปสร้างข้อมูลสร้างขึ้น บวกสตริง XML ที่ serialize แล้วของชีตที่ใหญ่ที่สุดระหว่างการบันทึกแบบเริ่มต้น ซึ่ง StreamingWrite ขจัดทิ้ง — ลูปสร้างและการเรียกบันทึกล้มเหลวในสองระบอบหน่วยความจำที่ต่างกัน StreamingWrite จึงลดเฉพาะหัวคลื่นตอนบันทึก ส่วนหน่วยความจำฝั่งการสร้างต้องใช้เครื่องมือ style-pool และ callback

ขนาดไฟล์เดินตามกฎที่เกี่ยวข้องกัน: เซลล์เป็นแค่ปัจจัยหนึ่ง ควบคู่ไปกับสไตล์ shared string สูตร รูปภาพ และ comment การตรวจสอบด้วย ForEachCell และการนับจำนวนใน collection แต่ละ sheet บอกคุณได้ว่าทรัพยากรตัวไหนครองพื้นที่ในไฟล์ที่มีปัญหาจริง ๆ ก่อนที่คุณจะไปปรับแต่งผิดจุด มีข้อควรระวังในการวัดอย่างหนึ่ง: Sheet.Cells.Count ฝั่ง XLSX รายงานจำนวนเซลล์ที่ ถูกสร้างขึ้นจริง ใน sparse store ไม่ใช่พื้นที่ของ used range sheet ที่ข้อมูลครอบครองสี่เหลี่ยมขนาด 1000 คูณ 50 โดยครึ่งหนึ่งของเซลล์ว่างเปล่า จะนับได้ประมาณ 25,000 ไม่ใช่ 50,000 ความต่างนี้สำคัญเมื่อคุณเทียบไฟล์ "ใหญ่มาก" ของลูกค้ากับ fixture ของคุณเอง เพราะพื้นที่ของ used range กับจำนวนเซลล์ที่มีข้อมูลจริงต่างกันได้เป็นสิบเท่าใน layout การเงินแบบ sparse

StreamingWrite แก้เส้นทางการ save ไม่ใช่เส้นทางการ build

การตั้งค่า TXLSXWorkbook.StreamingWrite := True สลับ SaveAs ไปใช้ตัว serializer แบบ streaming ที่เขียน XML ของ worksheet ตรงเข้าไปใน zip stream เลย ตัดสตริงตัวกลางต่อ sheet ทิ้งไป มันมีค่าเริ่มต้นเป็น False เพื่อความเข้ากันได้เชิงพฤติกรรม และการเปิดมันก็เป็นแค่การเปลี่ยนโค้ดบรรทัดเดียว:

Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Bulk');
  for R := 1 to 100000 do
  begin
    Sheet.Cells[R, 1].Value := R;
    Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
    Sheet.Cells[R, 3].Value := R * 1.5;
  end;
  Book.StreamingWrite := True;   // XML ของ sheet stream เข้าไปใน zip container
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

ควรเข้าใจให้ชัดว่ามันซื้ออะไรให้คุณ: โมเดลเซลล์ที่ loop สร้างขึ้นยังคงใช้หน่วยความจำเท่าเดิมทุกประการ StreamingWrite ทำให้ยอดพุ่งตอน save แบนราบลง ซึ่งเป็นความต่างระหว่างงาน batch ที่รันจบกับงานที่ล้มเหลวตรงเครื่องหมาย 95% ถ้า build loop เองกินหน่วยความจำจนหมด ปุ่มปรับที่คุณต้องใช้คือสองอันถัดไป

Style pool: เพิ่มครั้งเดียว นำ index กลับมาใช้ซ้ำ

formatting ของ XLSX ใน HotXLS ใช้ pool เป็นฐาน: Book.Fonts.Add(...), Fills.AddSolid(...) และ Borders.Add(...) คืนค่า index ของ pool แบบเริ่มนับที่ 0 ที่เซลล์อ้างอิงถึง การเรียก Fonts.Add ด้วยพารามิเตอร์เดียวกันภายใน loop จะถูก deduplicate ดังนั้นมันเปลืองเวลา ไม่ใช่พื้นที่ Alignments.Add ทำงานต่างออกไป: มันคืน object ใหม่ทุกครั้งที่เรียก ดังนั้นการสร้าง alignment ต่อเซลล์จะทำให้ pool โตขึ้นเป็นเส้นตรงตามจำนวนแถว มีนิสัยหนึ่งที่ครอบคลุมทั้งสองกรณี resolve index ของ pool ทุกตัวครั้งเดียวนอก loop แล้วกำหนด index ภายใน loop

เปรียบเทียบการใช้ style pool ของ HotXLS บน Delphi: ออบเจ็กต์ Alignments.Add ใหม่ที่สร้างครั้งละหนึ่งต่อแถวทำให้ pool โตเป็นเชิงเส้น ขณะที่ดัชนี Fonts.Add ที่ยกขึ้นไป resolve ครั้งเดียวเหนือลูป ถูกทุกเซลล์ใช้ซ้ำโดยดัชนีเริ่มศูนย์ถูกเลื่อนขึ้นหนึ่ง — อ้างอิงดัชนีฟอนต์ สีเติม เส้นขอบ และการจัดวางทุกตัวให้เสร็จหนึ่งครั้งนอกลูป แล้วจึงกำหนดดัชนี pool แบบ 0-based ที่บวกหนึ่งแล้วภายในลูป

// ยก pool lookup ออกมานอก loop ที่ร้อนแรง
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // index ของ pool เริ่มนับที่ 0
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // เซลล์เก็บแบบเริ่มนับที่ 1; 0 = default

+ 1 ไม่ใช่การพิมพ์ผิด และการลืมมันคือ bug ต้นเหตุคลาสสิกของอาการนี้: pool แจก index แบบเริ่มนับที่ 0 ในขณะที่ property ฝั่งเซลล์ถือว่า 0 คือ "default" ดังนั้น index ของ pool ทุกตัวต้องถูกเลื่อนบวกหนึ่งตอนกำหนดค่า ทำผิดด้วยการละเลยแล้ว header ของคุณจะ render ด้วยฟอนต์ default ของ workbook อย่างเงียบ ๆ ข้อบกพร่องที่ไม่มีใครสังเกตเห็นจนกว่าจะถึงการตรวจสอบแบรนด์

แทน Variant traffic ต่อเซลล์ด้วย row callback

Sheet.Cells[R, C].Value := X ทุกครั้งเกี่ยวข้องกับการค้นหา-หรือ-สร้างเซลล์บวกกับการกำหนดค่า Variant ที่หลายแสนเซลล์ ค่าใช้จ่ายต่อการเข้าถึงนั้นจะวัดได้ชัดใน profile HotXLS มี bulk callback API ให้ใช้บนทั้งสองส่วนหน้า (ForEachCell และ ForEachRow สำหรับการอ่าน, WriteCells และ WriteRows สำหรับการเขียน) ที่ย้ายการวนซ้ำเข้าไปในเครื่องยนต์ และส่งทั้งแถวมาให้โค้ดของคุณทีเดียว:

procedure TLedgerExport.FillRow(Sender: TObject;
  SheetIndex, Row, FirstCol, LastCol: Integer;
  var Values: Variant; var Skip: Boolean; var Cancel: Boolean);
begin
  if Row > FCount then
  begin
    Cancel := True;     // หยุดการเขียนทั้งหมด
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// call เดียวกับเครื่องยนต์ แทนที่การเข้าถึง property หลายแสนครั้ง
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

flag Skip ของ callback ปล่อยแถวหนึ่งไว้โดยไม่แตะต้องโดยไม่ยกเลิกทั้งหมด และ Cancel จบการทำงานก่อนเวลา ซึ่งมีประโยชน์เมื่อแหล่งข้อมูลคือ reader ที่คุณค้นพบความยาวของมันไปเรื่อย ๆ ระหว่างทาง จับคู่ WriteRows สำหรับการ build กับ StreamingWrite สำหรับการ save แล้วเส้นทางการสร้างก็จะไม่เหลือจุดร้อนต่อเซลล์อีกเลย

ปุ่มปรับฝั่งอ่านบนส่วนหน้า XLS

ไฟล์ .xls รุ่นเก่าขนาดใหญ่มีชุดเครื่องมือของตัวเอง _DisableGraphics := True ก่อน Open จะข้าม parse ชั้น drawing ไปเลยทั้งหมด ซึ่งเร่งความเร็วการโหลด workbook ที่พก shape และรูปภาพฝังตัวสะสมมาหลายปี ข้อจำกัดนี้เข้มงวด: ชั้น drawing จะหายไปจากโมเดลเลย ดังนั้นการ save workbook แบบนั้นจะเขียนไฟล์ที่ไม่มี drawing ของมัน สงวน flag นี้ไว้สำหรับงานวิเคราะห์แบบอ่านอย่างเดียว SetTempDir เปลี่ยนทิศทางไฟล์ชั่วคราวของตัวเขียน BIFF ซึ่งสำคัญบนเซิร์ฟเวอร์ที่ตำแหน่ง temp เริ่มต้นมี quota หรืออยู่บน storage ที่ช้า UseSharedFormulas จัดกลุ่มเนื้อหาสูตรที่ซ้ำกันให้เป็น record แบบ shared-formula ทำให้ไฟล์เล็กลงในกรณีที่คอลัมน์สูตรหนึ่งซ้ำกันลงไปหกหมื่นแถว

loop การอ่านบนข้อมูล XLS มีกับดักเรื่อง index ที่ควรชี้ให้เห็น เพราะมันทำให้งานเพิ่มเป็นสองเท่าเมื่อจัดการอย่างระมัดระวัง และทำให้ผลลัพธ์เสียหายเมื่อพลาดไป: UsedRange รายงานขอบเขต FirstRow, LastRow, FirstCol และ LastCol แบบเริ่มนับที่ 0 ในขณะที่ Cells.Item[Row, Col] เริ่มนับที่ 1 การสแกนที่เดินตาม used range ต้องบวกหนึ่งเข้ากับแต่ละพิกัดตอนเข้าถึงเซลล์ อย่างเช่น Cells.Item[Row + 1, Col + 1] ไม่งั้นมันจะอ่าน grid ที่เลื่อนไปทางเฉียงหนึ่งเซลล์ ทำให้แถวและคอลัมน์สุดท้ายหายไปอย่างเงียบ ๆ พร้อมรวมแถวแรกที่เป็นภาพลวงตาเข้ามาด้วย callback ForEachCell เลี่ยงความคลาดเคลื่อนนี้ได้ทั้งหมด ซึ่งเป็นอีกเหตุผลหนึ่งที่ควรเลือกใช้มันสำหรับการสแกนทั้ง sheet

ตรวจสอบไฟล์ก่อนที่จะโหลดมัน

ปฏิบัติการที่ถูกที่สุดสำหรับ workbook ขนาดใหญ่คือปฏิบัติการที่คุณหลีกเลี่ยงได้ GetSheetNames บนทั้งสองส่วนหน้าแสดงรายชื่อ worksheet ของไฟล์โดยไม่โหลดข้อมูลเซลล์ implementation ของ XLSX อ่านแค่ manifest ของ workbook ภายใน zip เท่านั้น และตั้งใจปล่อยให้ instance ของ workbook ไม่มีข้อมูลเติมไว้ ส่วนหน้าของ XLS หยุดสแกนที่ขอบเขต substream แรก นั่นทำให้มันเป็นการตรวจสอบก่อนบินที่ถูกต้องสำหรับคำถาม "งาน import นี้ควรเล็งไปที่ sheet ไหน" และ CanReadEncrypted ตอบคำถาม "container นี้ถูกเข้ารหัสหรือไม่" ก่อนความพยายาม Open ที่จะล้มเหลวตั้งแต่ต้น

ขั้นตอน pre-flight สำหรับไฟล์ Excel ที่ไม่รู้จักใน Delphi ด้วย HotXLS: GetSheetNames ระบุรายชื่อเวิร์กชีตโดยไม่โหลดข้อมูลเซลล์, รหัสคืนที่ศูนย์หรือต่ำกว่าทำให้ลิสต์ว่างและส่งสัญญาณล้มเหลว, CanReadEncrypted ติดธงคอนเทนเนอร์เข้ารหัสก่อน Open ที่ต้องล่มแน่ และการโหลดเต็มจึงจะรัน — GetSheetNames และ CanReadEncrypted ตอบว่าจะกำหนดเป้าชีตใด และคอนเทนเนอร์อ่านได้หรือไม่ ก่อนข้อมูลเซลล์ใด ๆ จะถูกแยกวิเคราะห์

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // ความล้มเหลวจะเคลียร์รายการทิ้ง
  // เลือก sheet เป้าหมาย แล้วค่อยตัดสินใจว่าการ Open เต็มรูปแบบคุ้มค่าหรือไม่
finally
  Book.Free;
  Names.Free;
end;

สังเกตธรรมเนียมของ return code: ฟังก์ชันตรวจสอบเหล่านี้ส่งสัญญาณความล้มเหลวด้วยค่าที่เท่ากับหรือต่ำกว่าศูนย์ และเคลียร์รายการ output ทิ้ง ดังนั้นให้ทดสอบด้วย <= 0 แทนที่จะเทียบกับค่าความสำเร็จค่าใดค่าหนึ่งโดยเฉพาะ

ปรับขนาดวิธีการให้เข้ากับงาน

สำหรับ pipeline แบบไม่มีคนดูแลที่สร้างไฟล์ขนาดใหญ่หลายไฟล์ต่อเนื่องกัน มีอีกสองนิสัยที่เติมภาพให้ครบ object ของ workbook ไม่ปลอดภัยต่อการใช้ร่วมกันข้าม thread แต่ก็ไม่มีอะไรห้าม workbook อิสระหนึ่งตัวต่อหนึ่ง worker thread ซึ่งทำให้การแปลงแบบ batch ขนานกันได้อย่างสะอาด และเมื่อ output ไปที่ HTTP แทนที่จะเป็นดิสก์ overload การ save แบบ TStream ผสานกับ StreamingWrite ได้ ดังนั้น response ขนาดใหญ่จะไม่มีวันกลายเป็นไฟล์ชั่วคราวเลย มีข้อควรระวังด้านปฏิบัติการหนึ่งข้อที่ใช้ได้: การ save ผ่าน stream เขียนจากตำแหน่งปัจจุบันโดยไม่ rewind ดังนั้นตั้งค่า Position := 0 ก่อนที่จะส่ง stream ให้ framework ของ response บทความเรื่อง streaming write และงาน batch job พัฒนารูปแบบฝั่งเซิร์ฟเวอร์นั้นต่อ และ บทความเรื่องการ export ฐานข้อมูล แสดงว่าปุ่มปรับเหล่านี้เข้าไปอยู่ตรงไหนในรายงานที่ขับเคลื่อนด้วย dataset

สุดท้าย เก็บ fixture ที่แย่ที่สุดไว้หนึ่งชุดต่อหนึ่งตระกูลรายงาน แล้วจับเวลามันใน CI การถดถอยด้านประสิทธิภาพในการสร้างเอกสารแทบไม่เคยประกาศตัวเองออกมา สไตล์ที่ถูกเพิ่มเข้าไปภายใน loop หรือการตรวจสอบที่ถูกแทนที่ด้วย Open เต็มรูปแบบไม่เปลี่ยนอะไรในเชิงฟังก์ชันเลย และงาน batch ที่รันทุกคืนก็แค่ใช้เวลานานขึ้นสี่สิบนาที test ที่จับเวลาบน fixture ตัวแทนขนาดห้าแสนเซลล์เปลี่ยนความคลาดเคลื่อนนั้นให้กลายเป็น build ที่แดง แทนที่จะเป็นเหตุการณ์ด้านปฏิบัติการ

Build ประเมินผล demo project พร้อมตัวอย่างการสร้างจำนวนมาก และเอกสารอ้างอิง API ฉบับเต็ม มีให้ใช้งานที่หน้า HotXLS Delphi Component