כתיבת XLSX של מיליון שורות ב-Delphi בזיכרון קבוע

משימת דיווח רצה היטב במשך שנה. היא בונה חוברת עבודה, ממלאת גיליון בכל מה שהשאילתה מחזירה, ושומרת אותו. אז מגיע לקוח עם חמש שנות היסטוריה ומבקש ייצוא מלא, ספירת השורות חוצה את המיליון, והתהליך קורס עם שגיאת חוסר-זיכרון (out-of-memory) הרבה לפני שהקובץ מגיע לדיסק. שום דבר לא היה שגוי בקוד. הוא פשוט החזיק את חוברת העבודה כולה ב-RAM כדי שיוכל לבצע לה סריאליזציה בסוף, והזיכרון שנדרש לו גדל בצעד אחד עם מספר השורות שהתבקש לכתוב

התיקון אינו מכונה גדולה יותר. זהו מודל כתיבה שונה. הכותב הישיר הזורם (streaming direct writer) ב-HotXLS פולט את חבילת ה-OOXML באופן הדרגתי ככל שהשורות מגיעות, כך שהזיכרון שבו הוא משתמש אינו תלוי בכמה שורות אתה כותב. זהו המקביל בצד-הכתיבה לקורא הזורם: במקום שבו הקורא סורק גיליון ענק מבלי לבנות עץ תאים, הכותב מייצר אחד מבלי לבנות עץ תאים גם כן

מדוע נתיב השמירה הרגיל גדל יחד עם הנתונים

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

הכותב הזורם מסיר את העץ. ברגע שתא נכתב הוא הופך לבתים (bytes) בתוך חלק גיליון העבודה, ואותם בתים נמסרים לפלט ה-zip. זרם גיליון העבודה הוא החוצץ (buffer) היחיד שגדל, והוא גדל בצד הפלט, לא כאובייקטים חיים של Delphi בערימה (heap). מה שנשאר בזיכרון הוא כמות קבועה של ניהול חשבונות: שמות הגיליונות, מספר דגלים, מספר השורה הנוכחי, ומונה תאים. קבוצה זו אינה משתנה בין השורה הראשונה לשורה העשרה מיליון

טבלת המחרוזות המשותפות היא המלכודת, ומחרוזות מוטבעות (inline strings) הן דרך המוצא

רוב כותבי ה-XLSX הזורמים מתפקדים היטב עד שהם פוגשים טקסט. פורמט OOXML שומר בדרך כלל מחרוזות בטבלת מחרוזות משותפות (shared-string table): כל מחרוזת ייחודית נכתבת פעם אחת לחלק נפרד, וכל תא שמחזיק באותה מחרוזת נושא אינדקס אל תוך הטבלה במקום את הטקסט. זוהי אופטימיזציית חלל טובה עבור קבצים מלאים בתוויות חוזרות, וזוהי ברירת המחדל שנתיב השמירה הסטנדרטי משתמש בה. הבעיה עבור כותב זורם היא אכזרית. כדי להסיר כפילויות, הטבלה חייבת להישאר בזיכרון לאורך כל העבודה, משום שכל שורה שעדיין עתידה לבוא עשויה לחזור על מחרוזת משורה שכבר נכתבה, ורק מפת זיכרון שלמה של מחרוזות שכבר נראו יכולה להקצות את האינדקס הנכון. לכן המבנה האחד שכותב זורם אינו יכול להזרים הוא בדיוק המבנה שאמור לעשות את הקובץ קטן. נתונים עמוסי-טקסט מביסים את ההזרמה שלשמה באת

הכותב הישיר עוקף את הטבלה לחלוטין. מחרוזות נכתבות באופן מוטבע (inline), כתאי t="inlineStr" שהטקסט שלהם יושב ישירות בתוך התא עם רכיב <is><t>. אין טבלה לאגור ואין מפה של מחרוזות שנראו להחזיק, ולכן עמודות טקסט אינן עולות יותר זיכרון מעמודות מספריות. הפשרה היא מפורשת ושווה להצהיר עליה בבירור. מחרוזות מוטבעות חוזרות על אותו טקסט בכל מקום שהוא מופיע, כך שקובץ עם תוויות זהות רבות הוא גדול יותר על הדיסק מאשר המקבילה עם מחרוזות משותפות. אתה מוציא גודל קובץ כדי לקנות זיכרון קבוע. עבור ייצוא במעבר-אחד (one-pass) זהו הצד הנכון של הפשרה, ודחיסת ה-zip סופגת בכל מקרה הרבה מהחזרתיות בדרך החוצה

טבלת העיצובים מגיעה בסוף, עם פורמט תאריך אחד

עיצובים מציגים את אותו מתח כמו מחרוזות. חוברת עבודה מפנה לפורמטינג שלה דרך חלק עיצובים (styles part), וכותב זורם אינו יכול לשמור על לוח עיצובים גדל בהתאמה לתאים שהוא כבר שטף (flushed) החוצה. הכותב הישיר עונה על כך על ידי שמירת טבלת העיצובים קטנה וקבועה, ופליטתה בעת הסגירה ולא מראש. פורמט תא ברירת מחדל אחד מכסה תאים רגילים. פורמט תאריך מספרי אחד מכסה תאריכים, ונרשם עם קוד פורמט של yyyy-mm-dd במיקום ידוע ברשימת פורמטי התאים

פורמט התאריך ההוא הוא הסיבה לכך ש-WriteDateTime קיים כקריאה משל עצמו. ל-Excel אין סוג תאריך מובנה; תאריך הוא מספר שלובש פורמט תאריך. WriteDateTime כותב את הערך כמספר סדרתי פשוט ומתייג את התא עם עיצוב התאריך האחד, כך שהגיליון האלקטרוני מציג אותו כתאריך במקום כמספר שלם בן חמש ספרות. המספר הסדרתי שהוא כותב חשוב למעבר הלוך-ושוב (round-tripping). הוא שומר את הערך של TDateTime ישירות תחת מערכת התאריכים של 1900, שהיא אותה מוסכמה שבה משתמש נתיב השמירה הרגיל של TXLSXWorkbook. מכיוון ששני הנתיבים מסכימים על המספר הסדרתי, קובץ שהכותב הזורם מייצר נקרא בחזרה דרך הקורא של HotXLS ונפתח ב-Excel עם תאריכים התואמים למה שהתכוונת, ללא שגיאות של חוסר-באחד (off-by-one) או הפתעות עידן (epoch) בין הכותב לקורא

סדר הוא חובה, משום שהבתים כבר אינם שם

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

ל-API של השורות יש נוחות קטנה עבור המקרה הנפוץ. AddRow מקבל אינדקס שורה מבוסס-1, אך העברת 0 פירושה לקחת את השורה הבאה אחרי הקודמת, כך שמילוי רציף אינו חייב לעקוב ולהעביר מונה עולה. כל AddRow סוגר את השורה שלפניו, וכל AddSheet סוגר את הגיליון שלפניו, כך שמעולם אינך מסיים באופן מפורש שורה או גיליון. אתה מתחיל את הבא בתור והכותב משלים (finalises) את המבנה הפתוח עבורך

הברחת תווים (Escaping) מטופלת היכן שהטקסט נכנס ל-XML

כל טקסט שאתה כותב הופך לחלק ממסמך XML, כך שחמש ישויות ה-XML המוגדרות מראש חייבות לעבור הברחה (escape) אחרת החבילה לא חוקית ברגע שערך מכיל אמפרסנד (ampersand) או סוגריים משולשים. הכותב מבריח את &, <, >, ", ו-' עבורך הן על טקסט של מחרוזות מוטבעות והן על טקסט של נוסחאות, שני המקומות שבהם תווים המסופקים על ידי הקורא (caller) נוחתים בתוך תגיות (markup). אתה מעביר WideString גולמי והכותב הופך אותו לבטוח. שם מוצר כמו Smith & Co <Ltd> או נוסחה המפנה לשם גיליון מצוטט יוצאים כ-XML בנוי-היטב (well-formed) ללא כל צורך בהברחה מהצד שלך

מחזור חיים, ומדוע Destroy עדיין סוגר

סיום החבילה הוא מה שכותב את חלק חוברת העבודה, את חלק העיצובים, את חלקי סוגי-התוכן והקשרים, ולבסוף את ספריית ה-zip המרכזית. עבודה זו קורית ב-Close. חבילה שלעולם לא נסגרת היא zip לא שלם ששום תוכנת גיליון אלקטרוני לא תפתח, ולכן סגירה אינה ניקוי אופציונלי, זהו השלב שהופך את הקובץ לחוקי. כדי לשמור מפני Close שנשכח בנתיב שגיאה, Destroy מבצע סגירה כמיטב יכולתו (best-effort) אם החבילה עדיין פתוחה, כך ששחרור הכותב אינו דולף את אובייקט ה-zip הבסיסי גם כאשר חריגה דילגה על הקריאה המפורשת. התבנית האמינה נשארת התבנית הרגילה של Delphi: כתוב בתוך try, קרא ל-Close, ושחרר בתוך ה-finally

הזרמת גיליון גדול מקצה לקצה

צורת המשימה היא להתחיל, להוסיף גיליון, למזוג שורות, לסגור. הדוגמה להלן כותבת שורת כותרת ולאחר מכן ריצה ארוכה של שורות נתונים בעלות טיפוס (typed), המערבבת מחרוזות, מספרים, נוסחה ללא תוצאה שמורה, ותאריך. הזיכרון שבו היא משתמשת עבור עשר שורות ועבור עשרה מיליון שורות הוא זהה, משום שכל תא עוזב לזרם ה-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) במקום הטקסט "True". אם ברצונך לאשר שהקובץ תקין ועובר הלוך-ושוב כראוי, המאפיין 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]));

כתיבה לזרם (stream) במקום לקובץ היא אותו קוד עם BeginStream במקום BeginFile, מה שמאפשר לשרת לשלוח את חוברת העבודה לתגובת HTTP או לזרם זיכרון ללא קובץ זמני על הדיסק. הכותב אינו הבעלים של הזרם שאתה מעביר, כך שאתה שומר על השליטה בזמן החיים שלו

כאשר העבודה היא נקודת קצה בשרת (server endpoint) שבונה חוברות עבודה לפי דרישה, התבניות בכתיבה זורמת עבור עבודות שרת ואצווה מראות כיצד לחבר זאת למטפל בבקשות ולייצוא מתוזמן. כאשר השאלה היא העלות הרחבה יותר של חוברות עבודה גדולות מאוד, הן קריאה והן כתיבה, ביצועי חוברת עבודה גדולה ב-Delphi מכסים לאן בעצם הולכים הזמן והזיכרון. הכותב הישיר הזורם מסופק כחלק מ-HotXLS Component עבור Delphi ו-C++Builder, לצד ממשקי ה-API המלאים לקריאה, עריכה ושמירה הנסקרים במקומות אחרים בבלוג זה