ייצוא נתוני מסד נתונים לדוחות Excel מ-Delphi עם HotXLS

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

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

טיפוסי השדות הם חוזה הייצוא האמיתי

לפני כל קריאת API, קבע כיצד כל טיפוס שדה Delphi נוחת בתא. תא המקבל מחרוזת Delphi נשאר מחרוזת. HotXLS אינו מנחש ש-'1,234.50' נועד להיות מספר, ואסור לו לנחש זאת, מפני שפענוח מחדש תלוי אזוריות הוא בדיוק הדרך שבה פסיק עשרוני גרמני הופך למפריד אלפים בשרת אנגלי. הדפוס האמין הוא הקצאה באמצעות הגישות המטופסות: AsFloat או AsCurrency לשדות מספריים, AsDateTime לתאריכים כדי שהתא יחזיק מספר סידורי אמיתי של Excel במקום מחרוזת מעוצבת, ו-AsString רק לשדות שהם באמת טקסט

טיפול ב-null ראוי להחלטה מפורשת ולא לברירת מחדל. המרת ערך שדה עם VarToStr הופכת SQL NULL למחרוזת ריקה, שהיא תא טקסט, ואילו דילוג על ההקצאה משאיר את התא ריק באמת, וזה מה שצרכני AVERAGE, COUNT וטבלאות ציר מצפים לו. עבור עמודות כסף, החלט לפני כתיבת הלולאה אם NULL פירושו אפס או לא ידוע. השניים מוצגים זהה ברגע שמישהו מעצב את העמודה, וההבדל משנה כל צבירה המחושבת בהמשך

נתיב הרכיב: TDataToXLS ביישומי VCL

ביישום VCL קלאסי עם שאילתה שכבר מחוברת למודול נתונים, TDataToXLS הוא הנתיב של קריאה אחת. הוא עובר על כל צאצא TDataset, בין אם FireDAC, ADO, IBX או כל דבר אחר המממש את ממשק מערך הנתונים המופשט, ומייצר גיליון מעוצב עם כותרות שדות, גופנים, גבולות, סכומי ביניים קבוצתיים לפי בחירה ופיצול גיליונות אוטומטי עבור קבוצות תוצאות גדולות

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // כל צאצא של TDataset
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // כותרות, לא שמות עמודה גולמיים
    Exporter.GroupFields.Add('CustomerID');   // בלוק סיכום-ביניים לכל לקוח
    Exporter.RowsPerSheet := 50000;           // להישאר מתחת לתקרת השורות של BIFF8
    Exporter.VisibleFieldsOnly := True;             // כיבוד Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

שני מאפיינים נושאים כאן את רוב המשקל הייצורי. HeaderSource := hsDisplayLabel כותב את DisplayLabel של כל שדה במקום שם עמודת ה-SQL הגולמי, כך שהחוברת אומרת "Customer Name" במקום CUST_NM. RowsPerSheet קיים מפני שהרכיב כותב BIFF8, שרשתו נעצרת ב-65,536 שורות וב-256 עמודות; הגדרתו ל-50,000 מפצלת ערכת תוצאות גדולה בין גיליונות לפני שתקרת הפורמט קוטעת אותה. המראה מטופל באמצעות מאפייני HeaderFont, DetailFont, GroupColor וסגנון הגבול, וקבוצת DisableFormat מכבה קטגוריות עיצוב שלמות כאשר הצרכן מעוניין בתאים פשוטים. עבור כל דבר מותאם במיוחד, אירועי AfterCell ו-AfterRow מוסרים לך את הטווח שזה עתה נכתב לעיבוד לאחר מכן

היכן הרכיב נעצר

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

• זהו רכיב VCL במלוא המובן. היחידה שלו מושכת את Forms, Controls ו-Dialogs, לכן קישורו למשימת קונסולה או לשירות Windows גורר את VCL אל הבינארי. ליחידות הליבה של החוברת אין תלות כזאת. הן זקוקות רק ל-Windows, Classes, SysUtils ו-Variants, ולכן קוד בצד השרת צריך להשתמש בלולאה המוצגת למטה במקום זאת
• הוא בנוי על חזית XLS. הרכיב מאכלס IXLSWorkbook וכותב .xls ‏(BIFF8). אין מאפיין המעביר אותו לפלט OOXML
• האירועים שלו מדברים בניב XLS. הפרמטר Cell: IXLSRange ב-AfterCell שייך למודל האובייקטים של XLS, לכן התאמה אישית לכל תא הנכתבת שם היא קוד בסגנון XLS גם אם הקובץ מומר ל-.xlsx לאחר מכן

הפקת .xlsx מפלט הרכיב

כאשר הצרכן מתעקש על .xlsx אך לוגיקת הייצוא כבר חיה ב-TDataToXLS, פונקציית הגישור ביחידה lxXlsxExport ממירה את החוברת המאוכלסת בקריאה אחת:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// הרכיב חושף את ה-IXLSWorkbook שהוא אכלס
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

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

הלולאה הנכתבת ידנית לשירותים ולעבודות אצווה

קוד בצד השרת צריך לפנות ישירות אל TXLSXWorkbook. שים לב להבדל במשך החיים בין שתי החזיתות לפני העתקת כל דוגמה. TXLSWorkbook בצד XLS מוחזק דרך ממשק עם ספירת הפניות ואסור לשחררו ידנית, ואילו TXLSXWorkbook היא מחלקה רגילה הדורשת try..finally Free. ערבוב שתי המוסכמות הוא דרך אמינה לייצר דליפה או שחרור כפול

procedure ExportOrders(Q: TDataSet; const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 'Order No';
    Sheet.Cells[1, 2].Value := 'Customer';
    Sheet.Cells[1, 3].Value := 'Ordered';
    Sheet.Cells[1, 4].Value := 'Amount';

    Row := 2;
    Q.First;
    while not Q.Eof do
    begin
      Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
      Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
      if not Q.FieldByName('Ordered').IsNull then
        Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
      Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
      Inc(Row);
      Q.Next;
    end;

    Book.StreamingWrite := True;  // הזרמת ה-XML של הגיליון ישירות לתוך ה-zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

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

לולאה זו היא גם הנתיב המתרחב בין תהליכונים. שני המנועים הם כותבי Object Pascal מקוריים, זרמי רשומות BIFF8 מצד אחד ו-OOXML zip בתוספת XML מצד האחר, לכן שום חלק מהייצוא אינו נוגע באוטומציית COM או דורש רישיון Excel בשרת. מה שמתקבל הוא מקביליות ללא צוואר בקבוק של מופע יחיד, בתנאי שכל תהליכון בונה חוברת משלו. אובייקטי החוברת אינם בטוחים לשימוש משותף בין תהליכונים, לכן הכלל הוא מופע אחד לכל ייצוא, לעולם לא מופע משותף המוגן במנעול

כדאי להכיר מגבלה אחת לפני שמתכננים סביבה. רשת XLSX נעצרת ב-1,048,576 שורות וב-16,384 עמודות, לכן פיצול הגיליונות שבו מטפל RowsPerSheet בצד XLS נדרש כאן לעיתים נדירות. גם חוברת עם מיליון שורות היא לעיתים נדירות מה שצרכן אנושי רוצה. כאשר ערכת התוצאות באמת כה גדולה, קובץ מופרד הוא בדרך כלל החוזה הטוב יותר, ו-המאמר על ייצוא CSV ו-TSV מכסה מפרידים, התנהגות BOM והסתייגות הערכת הנוסחאות החלה שם

בחירת נקודת התחלה

אם הייצוא חי בכלי שולחני של VCL ופלט .xls מתקבל, התחל ב-TDataToXLS ובתמיכת הקיבוץ שלו. זהו המינימום של קוד, והגשר דרך SaveXLSWorkbookAsXLSX זמין כאשר מישהו מבקש בהמשך .xlsx, כל עוד מקבלים את מגבלות הנאמנות שכבר תוארו. אם הקוד רץ ללא השגחה, או שהצרכן דורש .xlsx מלכתחילה, כתוב את הלולאה. שני הנתיבים נשלחים עם פרויקטי הדגמה עובדים והם חלק מחבילת HotXLS Delphi Component