ביצועי חוברות עבודה גדולות ב-Excel ב-Delphi עם HotXLS

כשייצוא של 300,000 שורות חורג מתקציב הזיכרון שלו, האשמה מוטלת בדרך כלל על מספר השורות. מספר השורות הוא בדרך כלל חף מפשע. החלקים היקרים בחוברת עבודה גדולה הם אלה שנוצרים כתופעת לוואי: מאגר סגנונות שגדל ברשומה אחת לכל תא משום שהעיצוב נוסף בתוך הלולאה, XML של גיליון העבודה שנבנה כמחרוזת ענקית אחת בזמן השמירה, מיליון גופי נוסחה זהים שנשמרים אחד-אחד. HotXLS, ספריית Delphi הילידית של losLab לקובצי XLS ו-XLSX, מספקת לכם ידית ספציפית לכל אחת מהעלויות הללו. אף אחת מהן אינה מופעלת כברירת מחדל, משום שכל אחת משנה איזון מסוים, ולכן הכישרון הביצועי האמיתי הוא לדעת איזו ידית מתאימה לאיזה סימפטום

היכן חוברת עבודה גדולה מבזבזת זיכרון

קיימים שני משטרי זיכרון נפרדים שיש להבין. בזמן הייצור, מודל התאים שבזיכרון גדל עם כל תא שאתם נוגעים בו: ערכים, עיצובים ונוסחאות כולם הופכים לאובייקטים או לרשומות מאגר. בזמן השמירה, מסלול ה-XLSX שבברירת מחדל בנוסף מעבד את ה-XML של כל גיליון עבודה למחרוזת רחבה לפני דחיסתו אל מכל ה-zip, כך שצריכת השיא היא המודל בתוספת הצורה המסודרת של הגיליון הגדול ביותר. עבודה ששורדת את לולאת הבנייה ואז מתה בתוך SaveAs נתקלת במשטר השני, לא בראשון, והתיקון לאחד אינו עושה דבר עבור האחר

גודל הקובץ נשמע מכלל קשור: תאים הם רק תורם אחד, לצד סגנונות, מחרוזות משותפות, נוסחאות, תמונות והערות. מעבר ביקורת עם ForEachCell וספירות האוספים לכל גיליון מגלה לכם איזה משאב באמת שולט בקובץ בעייתי לפני שתבצעו אופטימיזציה למשאב הלא נכון. דקות מדידה אחת: Sheet.Cells.Count בצד ה-XLSX מדווח על מספר התאים המאותחלים במאגר הדליל, ולא על שטח הטווח בשימוש. גיליון שבו הנתונים תופסים מלבן של 1000 על 50 עם מחצית מהתאים ריקים נספר כ-25,000 בקירוב, ולא 50,000. ההבחנה הזו חשובה כשאתם משווים קובץ "ענק" של לקוח אל הקבצים הקבועים שלכם, משום ששטח הטווח בשימוש ואוכלוסיית התאים בפועל יכולים להיבדל בסדר גודל בפריסות פיננסיות דלילות

StreamingWrite מתקן את מסלול השמירה, לא את מסלול הבנייה

הגדרת TXLSXWorkbook.StreamingWrite := True מחליפה את SaveAs למסרן זרימה שכותב את ה-XML של גיליון העבודה ישירות אל זרם ה-zip, ומבטל את מחרוזת הביניים לכל גיליון. ברירת המחדל היא 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 של הגיליון זורם אל מכל ה-zip
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

היו מדויקים לגבי מה זה קונה: מודל התאים שנבנה על ידי הלולאה תופס בדיוק אותו זיכרון כמו קודם. StreamingWrite משטח את קפיצת הזיכרון בזמן השמירה, וזה ההבדל בין עבודת אצווה שמסתיימת לבין כזו שנכשלת בסימן ה-95%. אם לולאת הבנייה עצמה ממצה את הזיכרון, הידיות שאתם זקוקים להן הן שתי הבאות

מאגרי סגנונות: הוסיפו פעם אחת, השתמשו שוב באינדקס

עיצוב XLSX ב-HotXLS מבוסס מאגר: Book.Fonts.Add(...), Fills.AddSolid(...) ו-Borders.Add(...) מחזירים אינדקס מאגר מבוסס-0 שהתאים מפנים אליו. קריאה ל-Fonts.Add עם פרמטרים זהים בתוך לולאה עוברת מניעת כפילויות, כך שהיא מבזבזת זמן ולא מקום. Alignments.Add מתנהג אחרת: הוא מחזיר אובייקט חדש בכל קריאה, כך שיצירת יישור לכל תא מגדילה את המאגר ליניארית עם מספר השורות. הרגל אחד מכסה את שני המקרים. פתרו כל אינדקס מאגר פעם אחת, מחוץ ללולאה, והקצו אינדקסים בתוכה

// הוציאו את חיפושי המאגר אל מחוץ ללולאה החמה
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // אינדקס מאגר מבוסס-0
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // התאים שומרים מבוסס-1; 0 = ברירת מחדל

ה-+ 1 אינו טעות הקלדה, ושכחתו היא הבאג הקלאסי מחולל-הסימפטומים כאן: המאגרים מחלקים אינדקסים מבוססי-0, בעוד המאפיינים בצד התא מתייחסים ל-0 כ"ברירת מחדל", ולכן כל אינדקס מאגר חייב להיות מוסט באחד בהקצאה. טעו בכך בהשמטה וכותרותיכם ירונדרו בשקט בגופן ברירת המחדל של חוברת העבודה, פגם שאיש לא מבחין בו עד לבדיקת המיתוג

החליפו תעבורת Variant לכל תא בהתקשרויות חזרה ברמת שורה

כל Sheet.Cells[R, C].Value := X כרוך בחיפוש-או-יצירה של תא בתוספת הקצאת Variant. במאות אלפי תאים, התקורה הזו לכל גישה הופכת ניתנת למדידה בפרופילים. HotXLS מספקת ממשקי התקשרות-חזרה גורפים בשתי החזיתות (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;

// קריאת מנוע אחת במקום מאות אלפי פגיעות במאפיינים
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

דגל ה-Skip של ההתקשרות-חזרה משאיר שורה ללא נגיעה מבלי לבטל, ו-Cancel מסיים את הפעולה מוקדם, מה שמועיל כשהמקור הוא קורא שאת אורכו אתם מגלים תוך כדי תנועה. שלבו WriteRows לבנייה עם StreamingWrite לשמירה, ובמסלול הייצור לא נותרת נקודה חמה כלשהי לכל תא

ידיות בצד הקריאה בחזית ה-XLS

לקובצי .xls מורשתיים גדולים יש ערכת כלים משלהם. _DisableGraphics := True לפני Open מדלג כליל על ניתוח שכבת הציור, מה שמאיץ את טעינת חוברות העבודה שנושאות שנים של צורות מצטברות ותמונות מוטמעות. ההגבלה נוקשה: שכבת הציור נעדרת אז מהמודל, ולכן שמירת חוברת עבודה כזו כותבת קובץ ללא ציוריו. שמרו דגל זה לעבודות ניתוח קריאה-בלבד. SetTempDir מפנה מחדש את קובצי הזמני של כותב ה-BIFF, מה שחשוב בשרתים שבהם מיקום הזמני שבברירת מחדל מוגבל במכסה או יושב על אחסון איטי. UseSharedFormulas מקבץ גופי נוסחה חוזרים אל רשומות נוסחה-משותפת, ומכווץ קבצים שבהם עמודת נוסחה חוזרת לאורך שישים אלף שורות

ללולאות קריאה על פני נתוני XLS יש מלכודת אינדוקס ששווה לסמן משום שהיא מכפילה עבודה כשמטופלת באופן הגנתי ומשחיתה תוצאות כשמוחמצת: UsedRange מדווח על גבולותיו FirstRow, LastRow, FirstCol ו-LastCol כמבוססי-0, בעוד Cells.Item[Row, Col] הוא מבוסס-1. סריקה שמהלכת על פני הטווח בשימוש חייבת להוסיף אחד לכל קואורדינטה בגישה לתא, כמו ב-Cells.Item[Row + 1, Col + 1], אחרת היא קוראת רשת מוסטת אלכסונית בתא אחד, מורידה בשקט את השורה והעמודה האחרונות ומכלילה ראשונה רפאית. ההתקשרות-חזרה ForEachCell עוקפת את חוסר ההתאמה לחלוטין, וזו סיבה נוספת להעדיף אותה לסריקות גיליון שלם

בדקו קבצים לפני טעינתם

הפעולה הזולה ביותר על חוברת עבודה גדולה היא זו שאתם נמנעים ממנה. GetSheetNames בשתי החזיתות מציג את גיליונות העבודה של קובץ ללא טעינת נתוני תאים. מימוש ה-XLSX קורא רק את מניפסט חוברת העבודה בתוך ה-zip ומשאיר במפורש את מופע חוברת העבודה לא-מאוכלס, וחזית ה-XLS מפסיקה לסרוק בגבול תת-הזרם הראשון. זה הופך אותה לבדיקה המקדימה הנכונה ל"איזה גיליון עבודת ייבוא זו צריכה לכוון אליו", ו-CanReadEncrypted משיב על "האם זהו מכל מוצפן" לפני ניסיון Open נדון לכישלון

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // כישלון מנקה את הרשימה
  // בחרו את גיליון היעד, ואז החליטו אם Open מלא שווה את זה
finally
  Book.Free;
  Names.Free;
end;

שימו לב למוסכמת קוד-ההחזרה: פונקציות בדיקה אלו מאותתות כישלון בערכים השווים לאפס או נמוכים ממנו ומרוקנות את רשימת הפלט, ולכן בדקו <= 0 במקום להשוות מול ערך הצלחה ספציפי אחד

התאמת הגישה לעבודה

עבור צינורות בלתי-מאוישים שמייצרים קבצים גדולים רבים ברצף, שני הרגלים נוספים משלימים את התמונה. אובייקטי חוברת עבודה אינם בטוחים לשיתוף בין תהליכונים, אך דבר אינו מונע חוברת עבודה עצמאית אחת לכל תהליכון עובד, מה שמקבל את המרת האצווה במקביל בנקיון. וכשהפלט יוצא ל-HTTP ולא לדיסק, העמסות השמירה של TStream משתלבות עם StreamingWrite כך שתגובה גדולה לעולם אינה מתממשת כקובץ זמני. הערת שוליים תפעולית אחת חלה: שמירת הזרם כותבת מהמיקום הנוכחי מבלי לגלגל לאחור, ולכן הגדירו Position := 0 לפני מסירת הזרם למסגרת התגובה. מאמר הכתיבה הזורמת ועבודות האצווה מפתח את הדפוס הזה בצד השרת, ומאמר ייצוא מסד הנתונים מראה היכן ידיות אלו משתלבות בדוח מונחה-מערך-נתונים

לבסוף, שמרו קובץ קבוע אחד למקרה הגרוע ביותר לכל משפחת דוחות ותזמנו אותו ב-CI. רגרסיות ביצועים בייצור מסמכים נדירות מכריזות על עצמן. סגנון שנוסף בתוך לולאה או בדיקה שהוחלפה ב-Open מלא אינם משנים דבר פונקציונלית, ועבודת האצווה הלילית פשוט אורכת ארבעים דקות יותר. בדיקה מתוזמנת על קובץ קבוע מייצג של חצי מיליון תאים הופכת את הסחף הזה לבנייה אדומה במקום לתקרית תפעולית

בנייות הערכה, פרויקטי הדגמה עם דוגמת ייצור-גורף, ועיון ה-API המלא זמינים בעמוד HotXLS Component