موتور فرمول‌نویسی HotXLS و توابع سفارشی در Delphi

کتابخانه‌ای برای صفحه‌گسترده که فقط رشته‌های فرمول را ذخیره می‌کند، و کتابخانه‌ای با یک موتور فرمول واقعاً کارکننده، دو محصول متفاوت‌اند که تا لحظه‌ای که از یکی از آن‌ها یک عدد بخواهید، یکسان به‌نظر می‌رسند. بیشتر کدهای صفحه‌گسترده در Delphi هرگز متوجه این شکاف نمی‌شوند، چون اکسل آن را می‌پوشاند: SUM(B2:B501) را در یک سلول بنویسید، ذخیره کنید، و اکسل در همان لحظه که یک انسان فایل را باز می‌کند، مجموع را دوباره محاسبه می‌کند. حالا انسان را از این چرخه حذف کنید و همان ورک‌بوک را از میان یک pipeline سروری بگذرانید که مستقیماً به CSV خروجی می‌گیرد؛ در این‌جا تفاوت دیگر نظری نیست. فایل CSV به‌جای یک عدد، متن خام =SUM(B2:B501) را حمل می‌کند، چون در هیچ لحظه‌ای واقعاً کسی فرمول را ارزیابی نکرده است

این همان مرزی است که HotXLS در سمت درست آن قرار دارد. این کتابخانه با یک فرمول همان‌طور رفتار می‌کند که فرمت‌های فایل رفتار می‌کنند: به‌عنوان متن ذخیره‌شده به‌همراه یک نتیجهٔ کش‌شدهٔ اختیاری، بنابراین یک خروجی CSV خام دستور تهیه (recipe) را بازتولید می‌کند، نه خود غذا را. اما HotXLS همچنین یک موتور محاسبه دارد که می‌توانید مستقیماً آن را فراخوانی کنید، همان موتور در هر دو facade مربوط به XLS و XLSX، به‌علاوهٔ یک hook برای حل نام توابعی که موتور تا حالا نشنیده است. HotXLS یک کتابخانهٔ بومی Object Pascal است که XLS و XLSX را از Delphi و C++Builder بدون automation اکسل می‌خواند و می‌نویسد، و نیمهٔ محاسباتی آن همان چیزی است که فرمول‌های ذخیره‌شده را در صورت تقاضا دوباره به مقدار تبدیل می‌کند

فرمول‌ها ذخیره می‌شوند، نه این‌که فوراً ارزیابی شوند

نوشتن یک فرمول در یک سلول چیزی را محاسبه نمی‌کند. در زمان ذخیره، ورک‌بوک متن فرمول را ثبت می‌کند. در سمت XLS، پرچم‌هایی هم ثبت می‌شود که با RecalcOnSave کنترل می‌شوند؛ این مقدار پیش‌فرض True دارد و به اکسل می‌گوید هنگام باز شدن دوباره محاسبه کند. این مدل برای فایل‌هایی که مقصدشان اکسل است درست است و برای pipeline‌هایی که مستقیماً مقدار سلول‌ها را مصرف می‌کنند نادرست، چه این مصرف خروجی CSV باشد، چه خروجی HTML، چه کد خودتان که سلول‌ها را دوباره می‌خواند. برای این موارد، به‌صراحت با Calculate ارزیابی کنید. این متد در چهار نقطهٔ ورود وجود دارد: TXLSWorkbook، IXLSWorksheet، TXLSXWorkbook و TXLSXWorksheet همگی function Calculate(const Formula: WideString): Variant را در اختیار می‌گذارند

// درون‌فرآیندی ارزیابی کنید، سپس مقدار را ارسال کنید نه دستور‌العمل را
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // CSV اکنون آن عدد را حمل می‌کند

عبارتی که به Calculate داده می‌شود، متن معمولی فرمول اکسل است. ارجاعات بین‌شیتی، نام‌های تعریف‌شده و توابع تودرتو همگی در برابر ورک‌بوک فعلی درون حافظه حل می‌شوند، که این فراخوانی را فراتر از رفع خروجی‌های CSV هم مفید می‌کند. آن را به‌عنوان یک سازوکار assertion در نظر بگیرید. تولیدکننده‌ای که تازه پانصد ردیف جزئیات نوشته می‌تواند از ورک‌بوک بخواهد جمع کل خودش را بدهد و آن را با رقمی که مستقل در Pascal محاسبه کرده مقایسه کند، و این‌طور یک خطای off-by-one در بازه را پیش از این‌که ممیز مشتری آن را پیدا کند، شناسایی کند

این موضوع همچنین استراتژی درست آزمون را برای خروجی‌های پرفرمول مشخص می‌کند. اکسل همچنان پیاده‌سازی مرجع زبان فرمول است، پس برای آن دسته انگشت‌شمار فرمول که پیامد کسب‌وکاری دارند، یک فایل fixture تأییدشده نگه دارید که مقادیر مورد انتظار آن را خود اکسل تولید کرده است، و pipeline ساخت را طوری تنظیم کنید که فرمول‌های ورک‌بوک تولیدشده را با Calculate در برابر همان fixtureها ارزیابی کند. در این‌صورت تفاوت‌ها به‌صورت آزمون‌های ناموفق در Delphi ظاهر می‌شوند، نه به‌صورت مغایرت‌هایی که مشتری با مقایسهٔ دو گزارش کشف می‌کند

افزودن توابع کسب‌وکاری با OnUserFunction

وقتی موتور به نام تابعی برمی‌خورد که نمی‌شناسد، به‌جای شکست کامل، یک event صادر می‌کند. OnUserFunction را روی هرکدام از کلاس‌های workbook تنظیم کنید و می‌توانید خودتان فراخوانی را حل کنید:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args به‌صورت یک آرایه Variant می‌رسد
    Handled := True;
  end;
end;

// سیم‌کشی و استفاده
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

سه جزئیات ارزش توجه دارند. اول، Handled := True را فقط زمانی تنظیم کنید که واقعاً نام را شناخته‌اید. رها کردن آن روی False به موتور اجازه می‌دهد رفتار عادی خودش را برای تابع ناشناخته ادامه دهد، بنابراین یک handler می‌تواند به چند ورک‌بوک خدمت کند بدون این‌که ادعا کند همه‌چیزی که از آن عبور می‌کند مال خودش است. دوم، نام‌ها را با SameText بدون حساسیت به حروف بزرگ و کوچک مقایسه کنید، چون نویسندگان فرمول discount( و DISCOUNT( را به‌جای هم می‌نویسند. سوم، آرگومان‌ها از قبل ارزیابی‌شده می‌رسند: DISCOUNT(A1) مقدار A1 را به شما می‌دهد، نه ارجاع آن را، پس یک تابع نمی‌تواند بفهمد ورودی‌هایش از کجا آمده‌اند. همین نکتهٔ آخر زمینه‌ساز محدودیتی است که بخش بعدی به آن می‌پردازد

با بدنهٔ handler همان‌قدر با احتیاط رفتار کنید که با هر نقطهٔ ورودی خارجی. آرایهٔ Args بازتاب هر چیزی است که نویسندهٔ فرمول تایپ کرده، پس پیش از اندیس‌گذاری روی آن، تعداد و نوع آرگومان‌ها را بررسی کنید و از پیش تصمیم بگیرید که یک فراخوانی نامعتبر چه چیزی برمی‌گرداند: یک مقدار خطای Variant، یا یک exception. این انتخاب مهم است چون یک exception که درون handler پرتاب شود، از طریق همان فراخوانی Calculate که ارزیابی را آغاز کرده بیرون منتشر می‌شود. این رفتار در یک تولیدکنندهٔ کاملاً کنترل‌شده قابل‌قبول است، اما در سرویسی که ورک‌بوک‌های نوشته‌شده توسط کاربر را ارزیابی می‌کند بی‌ادبانه است، جایی که یک فرمول بد می‌تواند کل درخواست را از کار بیندازد. در آن حالت، درون handler خطا را بگیرید و یک مقدار نگهبان (sentinel) برگردانید که گردش‌کار اطراف بتواند آن را تشخیص دهد و ثبت کند

توابع موقعیت‌آگاه به نسخهٔ Ex نیاز دارند

برخی توابع واقعاً به این‌که کجا ارزیابی می‌شوند وابسته‌اند. نرخی که در هر شیت متفاوت است، یک جستجوی وابسته به ردیف، یک ضریب مخصوص هر منطقه که فقط روی شیت‌های منطقه‌ای اعمال می‌شود: هیچ‌کدام از این‌ها را نمی‌توان فقط با مقادیر آرگومان پاسخ داد. event ساده نمی‌تواند این را بیان کند، پس موتور OnUserFunctionEx را ارائه می‌دهد، دقیقاً مشابه قبلی به‌جز یک پارامتر اضافه:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // همان فرمول روی هر شیت منطقه‌ای نرخ متفاوتی می‌دهد
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext مقادیر SheetIndex، Row و Col سلول در حال ارزیابی را حمل می‌کند. اگر نتیجهٔ یک تابع حتی کمی به موقعیت آن وابسته باشد، از همان ابتدا event نوع Ex را سیم‌کشی کنید. افزودن دوبارهٔ context به یک handler که سی فرمول از قبل آن را فراخوانی می‌کنند، بسیار شلوغ‌تر از انتخاب امضای درست در روز اول است، و این دو event در بقیهٔ جهات آن‌قدر شبیه‌اند که دلیل کمی برای شروع با نسخهٔ محدودتر وجود دارد

توابع سفارشی به اکسل منتقل نمی‌شوند

یک تابع سفارشی کاملاً درون فرآیند خودتان زندگی می‌کند. نام DISCOUNT فقط تا زمانی معنا دارد که کد Delphi شما و handler آن در حال اجرا باشند. فایل ذخیره‌شده را در اکسل باز کنید، و DISCOUNT فقط یک نام ناشناخته است؛ سلول #NAME? نشان می‌دهد مگر این‌که یک تابع VBA یا افزونهٔ متناظر به‌طور تصادفی روی دستگاه کاربر موجود باشد. این همان واقعیت طراحی است که یک نمونهٔ نمایشی را از یک محصول قابل‌ارسال جدا می‌کند، و شما را وادار می‌کند انتخابی را عمداً انجام دهید، نه این‌که بعداً کشفش کنید

برای هر سلول تصمیم بگیرید کدام‌یک از این دو قرارداد را ارائه می‌دهید. سلول‌هایی که قرار است کاربر آن‌ها را در اکسل دوباره‌محاسبه‌شده ببیند، باید فقط از واژگان تابع‌های خود اکسل ساخته شوند و نه چیز دیگری. سلول‌هایی که منطقشان اختصاصی است باید درون‌فرآیندی با Calculate ارزیابی شده و به‌صورت مقدار خام ذخیره شوند، تا تابع سفارشی مانند یک قاعدهٔ محاسباتی داخلی رفتار کند، نه مانند محتوای فایل. حالت شکستی که به‌طور قابل‌اطمینان تیکت پشتیبانی تولید می‌کند، همان راه میانه است: ذخیرهٔ یک فرمول با تابع سفارشی و انتظار این‌که اکسل آن را اجرا کند

یک مزیت آرام در قرارداد فقط-مقدار وجود دارد: از مالکیت فکری محافظت می‌کند. یک قاعدهٔ قیمت‌گذاری که در فرآیند Delphi شما ارزیابی و به‌صورت یک عدد ارسال شده، نمی‌تواند از روی ورک‌بوک reverse-engineer شود آن‌طور که یک فرمول قابل‌مشاهده می‌شود، و کاربر هم نمی‌تواند آن را با ویرایش یک سلول واسط بشکند. تولیدکننده‌های فاکتور، صورت‌حساب کمیسیون و کارت‌های نرخ تقریباً همیشه در همین دسته قرار می‌گیرند. موردی که واقعاً به فرمول‌های زنده نیاز دارد، مدل تعاملی what-if است، جایی که انتظار می‌رود مشتری ورودی‌ها را تغییر دهد و ببیند جمع‌ها حرکت می‌کنند، و این‌ها باید از واژگان خود اکسل به‌علاوهٔ نام‌های تعریف‌شده ساخته شوند

حالت‌های محاسبه، تکرار (iteration) و R1C1: پیچ‌های تنظیم facade مربوط به XLS

facade مربوط به XLS تنظیمات محاسبهٔ سطح BIFF را که اکسل از فایل می‌خواند در اختیار می‌گذارد. CalculationMode مقادیر xlCalcManual، xlCalcAutomatic (پیش‌فرض) یا xlCalcAutomaticExceptTables را می‌پذیرد و مشخص می‌کند اکسل پس از باز شدن فایل چگونه رفتار کند. یک ورک‌بوک مدل با هزاران فرمول اغلب اگر در حالت دستی تحویل داده شود دوستانه‌تر است، تا گیرنده تصمیم بگیرد چه زمانی طوفان بازمحاسبه اتفاق بیفتد. EnableIteration (پیش‌فرض False)، به‌همراه MaxIterations (پیش‌فرض ۱۰۰) و MaxIterationChange (پیش‌فرض ۰٫۰۰۱)، امکان ارجاع‌های چرخشی عمدی از نوع همگرایی تکراری را باز می‌کند که در برخی مدل‌های مالی دیده می‌شود. ReferenceStyle بین نمایش A1 و R1C1 جابه‌جا می‌شود، و UseFullPrecision گزینهٔ «دقت همان‌طور که نمایش داده می‌شود» در اکسل را بازتاب می‌دهد

این property‌ها روی facade مربوط به XLS قرار دارند چون به رکوردهای BIFF نگاشت می‌شوند؛ هنگام تولید .xlsx، فرمول‌ها را طوری برنامه‌ریزی کنید که به تنظیمات تکراری وابسته نباشند، یا مقادیر همگراشده را در Delphi محاسبه کنید و نتیجه را بنویسید

فرمول‌های آرایه‌ای: نقطهٔ ورود عمومی XLSX است

فرمول‌های آرایه‌ای سبک قدیمی CSE از طریق TXLSXRange.SetArrayFormula ساخته می‌شوند:

// یک فرمول آرایه‌ای که A2:A4 را در بر می‌گیرد
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

متد معادل در سلسله‌مراتب کلاس XLS وجود دارد اما در یک بخش private قرار گرفته، پس هیچ راه پشتیبانی‌شده‌ای برای نوشتن فرمول‌های آرایه‌ای جدید در فایل‌های .xls وجود ندارد. فرمول‌های آرایه‌ای موجود در فایل‌های بازشده سالم round-trip می‌شوند؛ آنچه نمی‌توانید انجام دهید ساختن آن‌هاست. قانونی که از این نتیجه می‌شود به‌اندازهٔ کافی ساده است: وقتی معنای آرایه‌ای بخشی از نیازمندی است، .xlsx را هدف بگیرید. اگر یک خروجی قدیمی .xls واقعاً به رفتار آرایه‌ای نیاز دارد، مسیر عملی این است که نتیجهٔ آرایه را در Delphi محاسبه کنید و مقادیر تکی را در سلول‌ها بنویسید

دو مطلب مرتبط در همین سایت: نام‌های تعریف‌شده و فرمول‌های بین‌شیتی نحوهٔ حل نام توسط موتور را پوشش می‌دهد، و مقالهٔ خروجی CSV و TSV رفتار خروجی‌ای را که محاسبهٔ صریح را ضروری می‌کند شرح می‌دهد. مرجع کامل موتور، شامل مجموعهٔ توابع پشتیبانی‌شده، به‌همراه HotXLS Delphi Component ارائه می‌شود