יצירת דוחות Excel מבוססי תבנית ב-Delphi עם HotXLS

הדרך האמינה להפיק דוח Excel מעוצב מ-Delphi היא להתחיל מחוברת עבודה שמעצב כבר בנה. מישהו במחלקת הכספים מסדר את החשבונית ב-Excel: הלוגו, כותרות העמודות, הגבולות על רצועת הפירוט, שורת הסיכומים המודגשת, פורמטי המטבע. הקוד שלכם פותח את הקובץ, משבץ נתונים חיים אל התאים שהמעצב שמר עבורם, ושומר את התוצאה. המראה הוא שלהם; המספרים הם שלכם. HotXLS, ספרייה מקורית ל-Delphi ול-C++Builder שקוראת וכותבת חוברות עבודה מסוג XLS ו-XLSX מבלי להפעיל את Excel, מעניקה לכם את שלוש הפעולות שגישה זו דורשת: חיפוש תא לפי הטקסט שבו, העתקת טווח עם הסגנונות והנוסחאות שלו ללא פגיעה, והכנסת שורות כך שכל מה שמתחתן יורד יחד עם הנתונים

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

עגנו כל קואורדינטה אל אסימון מציין מיקום

התיקון הוא לגרום לתבנית לשאת את הקואורדינטות שלה בעצמה. המעצב כותב אסימונים כגון {{CUSTOMER}}, {{DATE}} ו-{{DETAIL_START}} אל התאים שעל המחולל לגעת בהם, והמחולל מחשב כל מיקום בזמן ריצה ממקום שבו הוא מוצא את האסימונים האלה. עריכות פריסה כבר אינן משנות, מפני שהאסימון נע יחד עם התא שבו הוא יושב. החצי השני של החוזה הוא כלל הכשל: אם אסימון נדרש חסר, העבודה נעצרת לפני שכל נתון לקוח מגיע אל הקובץ. תבנית שסטתה צריכה להפיק כרטיס עבודה כושל, לא מסמך מוגש

איתור האסימונים: FindText ו-ReplaceText

שתי משפחות המחלקות של HotXLS חושפות חיפוש ברמת גיליון העבודה. FindText מחזירה את השורה והעמודה של התא הראשון שהטקסט שלו תואם, עם העמסה שמוסיפה רגישות לאותיות רישיות. ReplaceText מחליפה כל מופע ומחזירה כמה שינתה. השתיים מכסות את שני סוגי האסימונים שנוטים להיות לכם. עוגן בודד כמו שם הלקוח אתם מאתרים פעם אחת וכותבים לידו; אסימון שאמור להופיע בדיוק פעם אחת, כמו תאריך הדוח, אתם מחליפים ובודקים את הספירה. בצד ה-XLSX, מילוי שמעגן את עצמו כך נראה כך:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, C: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('invoice-template.xlsx') <> 1 then
      raise Exception.Create('Cannot open invoice template');
    Sheet := Book.Sheets[0];               // TXLSXSheets.Items is 0-based

    if not Sheet.FindText('{{CUSTOMER}}', R, C) then
      raise Exception.Create('Template drift: {{CUSTOMER}} anchor missing');
    Sheet.Cells[R, C].Value := 'ACME Corp';

    if Sheet.ReplaceText('{{DATE}}',
         FormatDateTime('yyyy-mm-dd', Date)) = 0 then
      raise Exception.Create('Template drift: {{DATE}} token missing');
    // detail expansion and save follow below
  finally
    Book.Free;
  end;
end;

שני פרטים חשובים. ראשית, FindText ו-ReplaceText מתאימות את ערך הטקסט של תא; אסימון המוטמע בתוך מחרוזת נוסחה אינו נראה להן, ולכן אסימוני מציין מיקום שייכים לתאים פשוטים, לעולם לא בתוך נוסחאות. שנית, ספירת ההחלפות היא גלאי הסטייה שלכם. תבנית שאמורה להכיל בדיוק אסימון {{DATE}} אחד אך מדווחת על אפס החלפות נערכה, וזריקת חריגה ברגע ההוא היא בדיוק מה שהופך סטיית פריסה שקטה לכשל גלוי

שכפול שורת הפירוט בלי לאבד סגנונות או נוסחאות

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

const
  DetailRow = 10;            // the formatted sample row in the template
var
  I: Integer;
begin
  // Open space before the totals block first, so the SUM range
  // below the detail band stretches together with the data.
  if Length(Items) > 1 then
    Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);

  for I := 0 to High(Items) do
  begin
    if I > 0 then              // clone styles + formulas from the sample row
      Sheet.CopyRange(DetailRow, 1, DetailRow, 5, DetailRow + I, 1);
    Sheet.Cells[DetailRow + I, 1].Value := Items[I].Name;
    Sheet.Cells[DetailRow + I, 2].Value := Items[I].Qty;
    Sheet.Cells[DetailRow + I, 3].Value := Items[I].UnitPrice;
    Sheet.Cells[DetailRow + I, 4].Formula :=
      Format('B%d*C%d', [DetailRow + I, DetailRow + I]);  // no '=' prefix
  end;
end;

שימו לב בקפידה אל הקצאת הנוסחה. מאפיין Formula של XLSX מקבל את הביטוי ללא סימן שוויון מוביל, בעוד שמעטפת ה-XLS מצפה ל-'=B10*C10' המוקצה דרך Value. ערבוב שתי המוסכמות הוא טעות הפורט הנפוצה ביותר בין משפחות המחלקות, והיא נכשלת ללא תלונה: התא פשוט מחזיק מחרוזת מילולית ש-Excel מציג כטקסט. אם התבנית מקשטת את רצועת הפירוט עם שורות כותרת ממוזגות, זכרו שרק התא השמאלי-עליון של אזור ממוזג נושא ערך. כללי הפריסה במאמר הנלווה על תאים ממוזגים בתבניות דוח מונחות-פריסה מסבירים מדוע אזורי מיזוג שייכים מחוץ לרצועת הנתונים לחלוטין

מה InsertRows מזיזה, ומה היא משאירה מאחור

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

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

חשבו מחדש לפני המסירה, או דעו מדוע דילגתם על כך

HotXLS אינה מעריכה נוסחאות במהלך SaveAs. כאשר אדם פותח את הקובץ, Excel מחשב מחדש את הכול (מעטפת ה-XLS חושפת את CalculationMode ואת RecalcOnSave אם אתם צריכים לכוון זאת), ולכן דוח המיועד לתיבת דואר אנושית אינו צריך מכם דבר נוסף. התמונה משתנה ברגע שחוברת העבודה מזינה תוכנית אחרת. ייצוא CSV כותב נוסחאות כטקסט המילולי שלהן ולעולם אינו מחשב אותן, וכל מנתח במורד הזרם שסומך על ערכים שנשמרו במטמון יקרא מספרים מיושנים או תאים ריקים. עבור מסלולים אלה, חשבו בשרת באמצעות Calculate, שמעריך ביטוי שרירותי כנגד חוברת העבודה הטעונה ומחזיר את התוצאה:

var
  Total: Variant;
  LastDetail: Integer;
begin
  LastDetail := DetailRow + Length(Items) - 1;
  Total := Book.Calculate(Format('SUM(Invoice!D%d:D%d)',
    [DetailRow, LastDetail]));
  if (not VarIsNumeric(Total)) or
     (Abs(Total - ExpectedTotal) > 0.005) then
    raise Exception.Create('Invoice total does not match the order record');

  if Book.SaveAs('invoice-2026-0611.xlsx') <> 1 then
    raise Exception.Create('Save failed: check output path and permissions');
end;

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

שתי משפחות מחלקות, אלגוריתם אחד

אותו היגיון עובר בין הפורמטים, אך לא אותו קוד. TXLSWorkbook עבור .xls הישן מבוסס-ממשק וסופר-הפניות, עם אינדוקס גיליונות מבוסס-1, ולעולם אינכם משחררים אותו ביד. TXLSXWorkbook עבור .xlsx הוא אובייקט רגיל שעליכם לשחרר ב-try..finally, עם אינדוקס גיליונות מבוסס-0 ומוסכמת הנוסחה שהוצגה למעלה. FindText, ReplaceText, CopyRange ו-InsertRows כולן קיימות בשני הצדדים, ולכן צורת העגינה-שכפול-חישוב-מחדש עוברת בנקיות. העצה המעשית היא להתחייב לפורמט אחד לכל צינור, או להסתיר את שני מחזורי החיים של האובייקטים מאחורי מתאם דק משלכם במקום לפזר את ההבדל לאורך המחולל

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

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