عملکرد ورک‌بوک‌های بزرگ اکسل در Delphi با HotXLS

وقتی یک خروجی 300,000 ردیفی از بودجهٔ حافظه فراتر می‌رود، معمولاً تعداد ردیف‌ها متهم می‌شود. تعداد ردیف‌ها معمولاً بی‌گناه است. بخش‌های پرهزینهٔ یک workbook بزرگ همان‌هایی هستند که به‌عنوان اثر جانبی ساخته می‌شوند: استخر سبک‌ها که به ازای هر سلول یک ورودی رشد می‌کند چون قالب‌بندی داخل حلقه اضافه شده است، XML کاربرگ که هنگام ذخیره به‌صورت یک رشتهٔ غول‌پیکر یکجا ساخته می‌شود، و یک میلیون بدنهٔ فرمول یکسان که تک‌تک ذخیره می‌شوند. HotXLS، کتابخانهٔ بومی Delphi از losLab برای فایل‌های XLS و XLSX، برای هر یک از این هزینه‌ها اهرم مشخصی در اختیار شما می‌گذارد. هیچ‌کدام به‌صورت پیش‌فرض فعال نیست، چون هر کدام یک بده‌بستان را تغییر می‌دهد؛ پس دانستن اینکه کدام اهرم به کدام نشانه می‌خورد، همان مهارت واقعی در کار با کارایی است

حافظهٔ یک workbook بزرگ کجا مصرف می‌شود

دو رژیم حافظه‌ای متمایز وجود دارد که باید دربارهٔ آنها جداگانه فکر کرد. در حین تولید، مدل سلولی در حافظه با هر سلولی که لمس می‌کنید رشد می‌کند: مقدارها، قالب‌ها و فرمول‌ها همگی به شیء یا ورودی استخر تبدیل می‌شوند. در حین ذخیره، مسیر پیش‌فرض XLSX علاوه بر این، XML هر کاربرگ را پیش از فشرده‌سازی در ظرف zip به یک wide string تبدیل می‌کند، بنابراین اوج مصرف برابر است با مدل به‌علاوهٔ شکل سریال‌شدهٔ بزرگ‌ترین برگه. کاری که از حلقهٔ ساخت جان سالم به در می‌برد و بعد داخل 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(...) یک اندیس استخر مبنا-صفر برمی‌گردانند که سلول‌ها به آن ارجاع می‌دهند. فراخوانی Fonts.Add با پارامترهای یکسان داخل یک حلقه حذف تکرار می‌شود، پس به‌جای فضا، زمان هدر می‌دهد. Alignments.Add رفتار دیگری دارد: به ازای هر فراخوانی یک شیء تازه برمی‌گرداند، بنابراین ساختن ترازبندی برای تک‌تک سلول‌ها استخر را خطی با تعداد ردیف‌ها بزرگ می‌کند. یک عادت هر دو حالت را پوشش می‌دهد. هر اندیس استخر را یک‌بار و بیرون از حلقه به دست بیاورید و اندیس‌ها را داخل حلقه نسبت دهید

// جست‌وجوهای استخر را از حلقهٔ داغ بیرون بکشید
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // اندیس استخر مبنا-صفر
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // سلول‌ها مبنا-یک ذخیره می‌کنند؛ 0 = پیش‌فرض

آن + 1 اشتباه تایپی نیست و فراموش‌کردنش همان باگ کلاسیکِ نشانه‌ساز در این بخش است: استخرها اندیس‌های مبنا-صفر تحویل می‌دهند، در حالی که ویژگی‌های سمت سلول مقدار 0 را «پیش‌فرض» می‌گیرند، پس هر اندیس استخر هنگام انتساب باید یک واحد جابه‌جا شود. اگر از قلم بیفتد، سرصفحه‌های شما بی‌سروصدا با فونت پیش‌فرض workbook رندر می‌شوند؛ نقصی که تا بازبینی برندینگ کسی متوجهش نمی‌شود

ترافیک Variant به ازای هر سلول را با callback ردیفی جایگزین کنید

هر Sheet.Cells[R, C].Value := X شامل یک جست‌وجو-یا-ساخت سلول به‌علاوهٔ یک انتساب Variant است. در چند صد هزار سلول، این سربار دسترسی در پروفایل‌ها قابل اندازه‌گیری می‌شود. HotXLS روی هر دو نما APIهای دسته‌ای مبتنی بر callback فراهم می‌کند (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 در callback یک ردیف را بدون لغو کل کار دست‌نخورده رها می‌کند و Cancel عملیات را زودتر تمام می‌کند، که وقتی منبع یک reader است و طولش را در حین کار کشف می‌کنید مفید است. WriteRows را برای ساخت با StreamingWrite برای ذخیره جفت کنید تا مسیر تولید هیچ نقطهٔ داغ باقی‌مانده‌ای در سطح سلول نداشته باشد

اهرم‌های سمت خواندن روی نمای XLS

فایل‌های بزرگ و قدیمی .xls جعبه‌ابزار خودشان را دارند. _DisableGraphics := True پیش از Open تجزیهٔ لایهٔ ترسیم را کاملاً رد می‌کند و بارگذاری workbookهایی را که سال‌ها شکل و تصویر جاسازی‌شده انباشته‌اند سریع‌تر می‌کند. محدودیتش سخت‌گیرانه است: لایهٔ ترسیم آن‌گاه در مدل غایب است، پس ذخیرهٔ چنین workbookای فایلی بدون ترسیم‌هایش می‌نویسد. این پرچم را برای کارهای تحلیلی فقط‌خواندنی نگه دارید. SetTempDir فایل‌های موقت نویسندهٔ BIFF را تغییر مسیر می‌دهد، که روی سرورهایی مهم می‌شود که محل موقت پیش‌فرضشان سهمیه دارد یا روی ذخیره‌سازی کند نشسته است. UseSharedFormulas بدنه‌های فرمول تکراری را در رکوردهای فرمول اشتراکی گروه می‌کند و فایل‌هایی را کوچک می‌کند که یک ستون فرمول در آنها تا شصت هزار ردیف تکرار می‌شود

حلقه‌های خواندن روی دادهٔ XLS یک تلهٔ اندیس‌گذاری دارند که ارزش هشدار دادن دارد، چون در برخورد محتاطانه کار را دو برابر می‌کند و در صورت غفلت نتیجه‌ها را خراب می‌کند: UsedRange مرزهای FirstRow، LastRow، FirstCol و LastCol خود را مبنا-صفر گزارش می‌کند، در حالی که Cells.Item[Row, Col] مبنا-یک است. پویشی که محدودهٔ استفاده‌شده را می‌پیماید باید هنگام دسترسی به سلول به هر مختصات یک واحد اضافه کند، مانند Cells.Item[Row + 1, Col + 1]، وگرنه شبکه‌ای را می‌خواند که یک سلول به‌صورت قطری جابه‌جا شده است، بی‌سروصدا آخرین ردیف و ستون را می‌اندازد و یک ردیف و ستون اولِ خیالی را وارد می‌کند. callback به نام ForEachCell این ناهماهنگی را کاملاً دور می‌زند، که دلیلی دیگر برای ترجیح آن در پویش کل برگه است

پیش از بارگذاری، فایل‌ها را وارسی کنید

ارزان‌ترین عملیات روی یک workbook بزرگ، همان عملیاتی است که از آن پرهیز می‌کنید. GetSheetNames روی هر دو نما کاربرگ‌های یک فایل را بدون بارگذاری دادهٔ سلولی فهرست می‌کند. پیاده‌سازی XLSX فقط مانیفست workbook درون zip را می‌خواند و صراحتاً نمونهٔ workbook را بدون داده رها می‌کند، و نمای 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 را آزمون کنید

اندازه‌کردن رویکرد به‌قدر کار

برای خط‌های لولهٔ بدون سرپرست که پشت سر هم فایل‌های بزرگ زیادی تولید می‌کنند، دو عادت دیگر تصویر را کامل می‌کند. اشیای workbook برای اشتراک‌گذاری thread-safe نیستند، اما هیچ چیز مانع یک workbook مستقل به ازای هر thread کارگر نمی‌شود، که تبدیل دسته‌ای را تمیز موازی می‌کند. و وقتی خروجی به‌جای دیسک به HTTP می‌رود، overloadهای ذخیره روی TStream با StreamingWrite ترکیب می‌شوند تا یک پاسخ بزرگ هرگز به‌شکل فایل موقت مادی نشود. یک پانویس عملیاتی هم هست: ذخیره روی جریان از موقعیت جاری و بدون بازگرداندن به ابتدا می‌نویسد، پس پیش از تحویل جریان به چارچوب پاسخ، Position := 0 را تنظیم کنید. مقالهٔ نوشتن جریانی و کارهای دسته‌ای آن الگوی سمت سرور را بسط می‌دهد، و مقالهٔ خروجی‌گرفتن از پایگاه داده نشان می‌دهد این اهرم‌ها کجای یک گزارش داده‌محور جا می‌گیرند

در پایان، برای هر خانوادهٔ گزارش یک نمونهٔ آزمونِ بدترین‌حالت نگه دارید و زمانش را در CI بگیرید. پس‌رفت‌های کارایی در تولید سند به‌ندرت خودشان را اعلام می‌کنند. سبکی که داخل یک حلقه اضافه شده یا وارسی‌ای که با Open کامل جایگزین شده از نظر عملکردی چیزی را تغییر نمی‌دهد، و کار دستهٔ شبانه فقط چهل دقیقه بیشتر طول می‌کشد. یک آزمون زمان‌دار روی نمونه‌ای نماینده با نیم‌میلیون سلول، آن رانش را به‌جای یک حادثهٔ عملیاتی به یک build قرمز تبدیل می‌کند

نسخه‌های ارزیابی، پروژه‌های نمونه همراه با مثال تولید انبوه، و مرجع کامل API در صفحهٔ HotXLS Delphi Component در دسترس است