خروجی گرفتن نتایج پایگاه داده Delphi به گزارش‌های اکسل با HotXLS

تبدیل یک نتیجهٔ کوئری به یک گزارش اکسل، سه مسئله است که یک کت پوشیده‌اند. هر نوع فیلد Delphi باید به‌عنوان نوع درست اکسل در یک سلول بنشیند، سطر سربرگ باید مثل یک گزارش خوانده شود نه یک دامپ اسکیما، و اعداد، تاریخ‌ها و پول باید فرمت‌هایی حمل کنند که این سفر را زنده بمانند. هرکدام از این‌ها را نادیده بگیرید، فایل همچنان باز می‌شود، همچنان محتمل به نظر می‌رسد، و همچنان همان لحظه‌ای که یک کاربر مالی یک ستون را انتخاب می‌کند و منتظر یک جمعِ هرگز-ظاهرنشونده می‌ماند شکست می‌خورد. مقدارها به‌صورت متن نوشته شده‌اند، Excel آن‌ها را به‌عنوان برچسب در نظر می‌گیرد، و هیچ exception‌ای هرگز برای هشدار دادن به شما بالا نیامده

HotXLS یک کتابخانهٔ صفحه‌گستردهٔ نیتیو Object Pascal است که فایل‌های XLS و XLSX را مستقیم از Delphi و C++Builder می‌نویسد، بدون هیچ اتوماسیون Excel‌ای درگیر. این کتابخانه دو مسیر از یک TDataset به یک کتابچهٔ کاری ارائه می‌دهد: کامپوننت آماده‌به‌کارِ TDataToXLS، و یک حلقهٔ دست‌نویس در برابر API کتابچهٔ کاری. این دو قابل‌تعویض نیستند. کامپوننت یک شهروند VCL است که روی نمای XLS ساخته شده، پس انتخاب درست بستگی دارد به این‌که کد کجا اجرا می‌شود و مصرف‌کننده کدام قالب فایل را انتظار دارد. آنچه در ادامه می‌آید هر دو مسیر است، خطی که کامپوننت دیگر ابزار درستی نیست، و این‌که چگونه، هرکدام را که انتخاب کنید، انواع فیلد را دست‌نخورده نگه دارید

انواع فیلد قرارداد واقعیِ خروجی‌گیری هستند

پیش از هر فراخوانی API، تصمیم بگیرید هر نوع فیلد Delphi چگونه در یک سلول می‌نشیند. سلولی که یک رشتهٔ Delphi را می‌گیرد، رشته می‌ماند. HotXLS حدس نمی‌زند که '1,234.50' قرار بوده یک عدد باشد، و نباید هم بزند، چون reparsing وابسته به locale دقیقاً همان چیزی است که یک ویرگول اعشاری آلمانی را روی یک سرور انگلیسی به جداکنندهٔ هزارگان تبدیل می‌کند. الگوی قابل‌اطمینان انتساب از طریق accessorهای تایپ‌شده است: AsFloat یا AsCurrency برای فیلدهای عددی، AsDateTime برای تاریخ‌ها تا سلول یک serial تاریخ واقعیِ اکسل را نگه دارد نه یک رشتهٔ فرمت‌شده، و AsString فقط برای فیلدهایی که واقعاً متن هستند

مدیریت NULL سزاوار یک تصمیم صریح است نه یک پیش‌فرض. تبدیل یک مقدار فیلد با VarToStr یک SQL NULL را به یک رشتهٔ خالی تبدیل می‌کند، که یک سلول متنی است، در حالی‌که رد کردن انتساب، سلول را واقعاً خالی می‌گذارد، که همان چیزی است که AVERAGE، COUNT و مصرف‌کنندگان pivot-table انتظار دارند. برای ستون‌های پولی، پیش از نوشتن حلقه تصمیم بگیرید NULL به معنای صفر است یا نامشخص. این دو، به‌محض این‌که کسی ستون را فرمت کند، یکسان رندر می‌شوند، و این تفاوت هر aggregate‌ای که پایین‌دستی محاسبه می‌شود را تغییر می‌دهد

مسیر کامپوننتی: TDataToXLS در اپلیکیشن‌های VCL

برای یک اپلیکیشن کلاسیک VCL با یک کوئری که از قبل به یک data module سیم‌کشی شده، TDataToXLS مسیر یک-فراخوانی است. این کامپوننت هر زیرکلاسی از TDataset را می‌گردد، چه FireDAC، چه ADO، چه IBX، یا هرچیز دیگری که رابط dataset انتزاعی را پیاده‌سازی می‌کند، و یک کاربرگ استایل‌دهی‌شده با caption‌های سربرگ، فونت‌ها، حاشیه‌ها، جمع‌فرعی‌های گروهیِ اختیاری، و تقسیم خودکار شیت برای نتایج بزرگ تولید می‌کند

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // هر زیرکلاسی از TDataset
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // caption ها، نه نام ستون‌های خام
    Exporter.GroupFields.Add('CustomerID');   // بلوک جمع‌فرعی به‌ازای هر مشتری
    Exporter.RowsPerSheet := 50000;           // زیر سقف ردیف BIFF8 بمانید
    Exporter.VisibleFieldsOnly := True;             // Field.Visible را رعایت کنید
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

دو ویژگی اینجا بیشترین وزن تولیدی را حمل می‌کنند. HeaderSource := hsDisplayLabel به‌جای نام ستون خام SQL، DisplayLabel هر فیلد را می‌نویسد، پس کتابچهٔ کاری «Customer Name» می‌گوید نه CUST_NM. RowsPerSheet وجود دارد چون کامپوننت BIFF8 می‌نویسد، که شبکه‌اش در 65,536 سطر در 256 ستون متوقف می‌شود؛ تنظیم آن روی 50,000 یک نتیجهٔ بزرگ را میان شیت‌ها تقسیم می‌کند پیش از این‌که سقف قالب آن را برش دهد. ظاهر توسط ویژگی‌های HeaderFont، DetailFont، GroupColor و سبک حاشیه مدیریت می‌شود، و مجموعهٔ DisableFormat کل دسته‌های فرمت‌دهی را وقتی مصرف‌کننده سلول‌های ساده می‌خواهد خاموش می‌کند. برای هرچیز سفارشی، رویدادهای AfterCell و AfterRow محدودهٔ تازه‌نوشته‌شده را برای پردازش پسین به شما می‌دهند

کامپوننت کجا متوقف می‌شود

سه محدودیت داخل TDataToXLS طراحی شده، و دانستن آن‌ها از قبل از یک بازطراحی ناجور دو اسپرینت بعد جلوگیری می‌کند

• این کامپوننت به معنای کامل کلمه یک کامپوننت VCL است. واحد آن Forms، Controls و Dialogs را می‌کشد، پس لینک کردن آن به یک کار کنسولی یا یک سرویس ویندوز، 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 از طریق یک اینترفیس reference-counted نگه‌داری می‌شود و نباید دستی آزاد شود، در حالی‌که TXLSXWorkbook یک کلاس ساده است که به try..finally Free نیاز دارد. مخلوط کردن این دو قرارداد یک راه قابل‌اطمینان برای تولید یک leak یا یک double-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 هستند. تاریخ‌ها به‌صورت date serial می‌رسند، مبلغ‌ها به‌صورت double می‌رسند، و تاریخ‌های سفارش NULL واقعاً خالی می‌مانند به‌جای این‌که به رشته‌های خالی تبدیل شوند. StreamingWrite := True فقط مسیر ذخیره را تغییر می‌دهد: XML کاربرگ مستقیم داخل کانتینر zip استریم می‌شود به‌جای این‌که ابتدا به‌عنوان یک رشتهٔ بزرگ سرهم شود، که جهش حافظه در زمان SaveAs را برای تعداد سطرهای شش‌رقمی مسطح می‌کند. هر متد ذخیره‌سازی هم یک اُورلود TStream دارد، پس کتابچهٔ کاری می‌تواند مستقیم بدون لمس دیسک داخل یک response HTTP برود. مقالهٔ نوشتن streaming و کارهای دسته‌ای آن الگوی استقرار را مرور می‌کند، و مقالهٔ کارایی کتابچه‌های کاری بزرگ پوشش می‌دهد که وقتی تعداد سطرها بیشتر بالا می‌رود چه باید کرد

این حلقه همچنین مسیری است که در سراسر ترد‌ها مقیاس می‌گیرد. هر دو موتور نویسندهٔ نیتیو Object Pascal هستند، جریان‌های رکورد BIFF8 در یک سمت و zip به‌علاوهٔ XML در OOXML در سمت دیگر، پس هیچ بخشی از یک خروجی‌گیری اتوماسیون COM را لمس نمی‌کند یا به یک مجوز Excel روی سرور نیاز ندارد. چیزی که این به شما می‌دهد موازی‌سازی بدون یک گلوگاه تک-نمونه‌ای است، به شرط این‌که هر ترد کتابچهٔ کاری خودش را بسازد. اشیای کتابچهٔ کاری برای استفادهٔ مشترک thread-safe نیستند، پس قاعده این است: یک نمونه به‌ازای هر خروجی‌گیری، هرگز یک نمونهٔ مشترک محافظت‌شده با یک قفل

یک محدودیت پیش از این‌که دورش طراحی کنید ارزش دانستن دارد. شبکهٔ XLSX در 1,048,576 سطر در 16,384 ستون متوقف می‌شود، پس تقسیم شیتی که RowsPerSheet در سمت XLS مدیریت می‌کند، اینجا به‌ندرت لازم است. یک کتابچهٔ کاریِ میلیون-سطری هم به‌ندرت چیزی است که یک مصرف‌کنندهٔ انسانی می‌خواهد. وقتی نتیجه واقعاً به آن بزرگی است، یک فایل delimited معمولاً قرارداد بهتری است، و مقالهٔ خروجی CSV و TSV جداکننده‌ها، رفتار BOM، و نکتهٔ ارزیابی فرمول که آنجا اعمال می‌شود را پوشش می‌دهد

انتخاب یک نقطهٔ شروع

اگر خروجی‌گیری داخل یک ابزار دسکتاپ VCL زندگی می‌کند و خروجی .xls قابل‌قبول است، با TDataToXLS و پشتیبانی گروه‌بندی‌اش شروع کنید. این کمترین کد است، و پل از طریق SaveXLSWorkbookAsXLSX وقتی بعداً کسی .xlsx بخواهد آنجاست، تا وقتی محدودیت‌های وفاداریِ از‌پیش‌توصیف‌شده را بپذیرید. اگر کد بدون نظارت اجرا می‌شود، یا مصرف‌کننده از ابتدا .xlsx می‌خواهد، حلقه را بنویسید. هر دو مسیر با پروژه‌های دموی کارآمد ارسال می‌شوند و بخشی از بستهٔ HotXLS Delphi Component هستند