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

یک نام تعریف‌شده (defined name) برچسبی است که جای یک ثابت، یک بازهٔ سلولی یا یک عبارت فرمول را می‌گیرد؛ این نام یک‌بار در ورک‌بوک ذخیره می‌شود و هرجا لازم باشد به‌صورت نمادین به آن ارجاع داده می‌شود. اگر TaxRate را در یک فرمول بنویسید، موتور محاسبه آن را به هر چیزی که تعریف نام نگه می‌دارد تبدیل می‌کند، خواه مقدار ثابت 0.08 باشد یا بازهٔ Data!$A$2:$D$100. ارجاع بین‌شیتی (cross-sheet) ایدهٔ متفاوتی است: Data!D2 با افزودن نام شیت به آدرس، به سلولی در شیت دیگر دسترسی پیدا می‌کند. با ترکیب این دو، یک شیت خلاصه می‌تواند از طریق نامی که هرگز به آدرس ثابتی اشاره نمی‌کند، مجموع یک شیت جزئیات را محاسبه کند؛ دقیقاً همان چیزی که در ورک‌بوکی که یک تولیدکننده (generator) می‌سازد و بعداً یک حسابدار بازبینی می‌کند، لازم دارید

HotXLS، کتابخانهٔ بومی Delphi از losLab برای فایل‌های XLS و XLSX، جدول نام‌ها را در هر دو فرمت با دسترسی برای ایجاد، جستجو و حذف در اختیار می‌گذارد، به‌همراه یک موتور فرمول که نام‌ها و ارجاعات بین‌شیتی را درون-فرآیندی (in process) حل می‌کند. این دو فرمت سلسله‌مراتب کلاس جداگانه‌ای دارند و تفاوت میان APIهای نام‌گذاری آن‌ها همان بخشی است که کد منتقل‌شده از یکی به دیگری را دچار مشکل می‌کند

دو مخزن نام که رابط مشترکی ندارند

در سمت XLS، TXLSWorkbook.GetNames یک مجموعهٔ IXLSNames برمی‌گرداند که overload آن یعنی Add(Name, RefersTo, Visible) یک نام را در جدول نام BIFF می‌نویسد. ورودی‌های تکی به‌صورت اشیاء IXLSName بازمی‌گردند که Name، RefersTo، یک RefersToRange حل‌شده و متد Delete را در خود دارند. در سمت XLSX، TXLSXWorkbook.DefinedNames یک مجموعهٔ TXLSXDefinedNames است که Add، FindByName و DeleteByName را دارد

قراردادهای جستجو به شکلی متفاوت‌اند که بیشتر هنگام انتقال کد (porting) خودش را نشان می‌دهد تا در زمان کامپایل. property پیش‌فرض Item در مجموعهٔ XLS یک Variant می‌پذیرد، بنابراین هم Names[0] و هم Names['TaxRate'] روی آن قابل حل‌اند. مجموعهٔ XLSX چنین property پیش‌فرضی ندارد؛ باید FindByName('TaxRate') را فراخوانی کنید که در صورت نبودن نام، nil برمی‌گرداند. کدی که برای یک facade نوشته شده فقط به‌طور تصادفی روی دیگری کامپایل می‌شود و معمولاً خطا به‌صورت یک دسترسی nil در زمان اجرا ظاهر می‌شود، نه یک خط قرمز در IDE

دامنه اولین تصمیم است، نه پرچمی که بعداً اضافه می‌کنید

یک نام تعریف‌شده یا در دامنهٔ ورک‌بوک (workbook-scoped) است و برای فرمول‌های همهٔ شیت‌ها قابل مشاهده است، یا در دامنهٔ شیت (sheet-scoped) است و فقط برای فرمول‌های شیت مالک آن قابل مشاهده. در API مربوط به XLSX این تمایز فقط یک پارامتر اختیاری است. DefinedNames.Add(AName, AFormula) یک نام در سطح ورک‌بوک می‌سازد، در حالی که Add(AName, AFormula, ASheetIndex) آن را به یک شیت مشخص متصل می‌کند. هنگام خواندن دوباره، TXLSXDefinedName.SheetIndex برای دامنهٔ ورک‌بوک مقدار -1 و در غیر این‌صورت اندیس شیت (بر پایهٔ صفر) را برمی‌گرداند

دامنه در عین حال نقش سیاست برخورد نام‌ها (collision policy) را نیز بازی می‌کند و به همین دلیل باید پیش از نوشتن اولین نام مشخص شود. اکسل اجازه می‌دهد یک Total محلی در هر شیت، به‌علاوهٔ یک Total در سطح ورک‌بوک وجود داشته باشد، و فرمول یک شیت مشخص ابتدا نسخهٔ محلی را حل می‌کند. ورک‌بوک‌های تولیدشده باید عمداً روی همین رفتار تکیه کنند. مفروضات کسب‌وکاری که چند شیت از آن‌ها استفاده می‌کنند، مانند نرخ مالیات، نرخ ارز و دورهٔ گزارش‌دهی، باید در دامنهٔ ورک‌بوک قرار گیرند. بازه‌های کمکی که فقط فرمول‌های یک شیت به آن‌ها ارجاع می‌دهند، در دامنهٔ شیت امن‌ترند، جایی که چیزی نمی‌تواند آن‌ها را سایه بیندازد و آن‌ها هم نمی‌توانند چیزی را سایه بیندازند

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... Data!A2:D100 را با ردیف‌های جزئیات پر کنید ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // دامنه کارپوشه، یک ثابت
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // دامنه کارپوشه، یک بازه
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // فقط محدود به شیت با اندیس 1

    // فرمول‌های XLSX علامت '=' پیشرو ندارند
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

یک نام تعریف‌شده لازم نیست به یک بازه اشاره کند. TaxRate در مثال بالا به مقدار ثابت خام 0.08 ارجاع می‌دهد، و این تمیزترین راه برای انتشار یک مفروضهٔ کسب‌وکاری است. این نام یک‌بار در Name Manager اکسل ظاهر می‌شود، هر فرمولی به‌صورت نمادین به آن ارجاع می‌دهد، و تغییر نرخ در فصل بعد فقط یک ویرایش تک‌خطی در تولیدکننده است، نه جستجو در میان چهارده رشتهٔ فرمول ساخته‌شده

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

کانال ورود فرمول جایی است که کد منتقل‌شده بیشترین خرابی را در آن نشان می‌دهد، چون این دو facade در مورد علامت مساوی توافق ندارند. سلول‌های XLS فرمول‌ها را از طریق Value با یک = پیشرو دریافت می‌کنند. سلول‌های XLSX یک property اختصاصی به نام Formula دارند که عبارت را بدون پیشوند می‌گیرد. اگر '=SUM(A1:A10)' را در TXLSXCell.Formula بنویسید، علامت مساوی به‌جای یک نشانگر، بخشی از متن عبارت ذخیره‌شده می‌شود و فایل دیگر همان رفتاری را که همین رشته در سمت XLS داشت، نشان نمی‌دهد

var
  Book: IXLSWorkbook;   // interface-counted: Free نکنید
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // فرض کنید شیتی به نام 'Data' از قبل ردیف‌های جزئیات را نگه می‌دارد
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = پنهان از Name Manager

  // فرمول‌های XLS از طریق Value، با پیشوند '=' می‌روند
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

این قطعه‌کد دو ویژگی خاص دیگر سمت XLS را هم نشان می‌دهد. مجموعهٔ شیت‌ها بر پایهٔ ۱ شمارش می‌شود، پس Sheets[1] اولین شیت است، در برابر Sheets[0] بر پایهٔ صفر در XLSX. و پارامتر سوم Add یک نام پنهان می‌سازد: در فایل حضور دارد و برای فرمول‌ها قابل استفاده است، اما در Name Manager اکسل نامرئی است. نام‌های پنهان ابزار مناسبی برای سازوکار داخلی تولیدکننده هستند که کاربر نهایی هرگز نباید به‌طور تصادفی آن‌ها را ویرایش یا حذف کند

ارجاعات بین‌شیتی، و اتفاقی که هنگام جابه‌جایی ردیف‌ها می‌افتد

هر دو موتور فرمول از نحو استاندارد بین‌شیتی پشتیبانی می‌کنند. نام‌های ساده شیت مستقیماً به‌صورت Data!A1 به‌کار می‌روند؛ نامی که فاصله یا نشانه‌گذاری دارد به علامت تک‌نقل‌قول نیاز دارد، مانند 'Sheet With Space'!A1. در متن RefersTo یک نام، تقریباً همیشه باید سراغ ارجاعات مطلق مانند Data!$A$2:$D$100 بروید. یک ارجاع نسبی درون یک نام تعریف‌شده نسبت به سلولی که آن را استفاده می‌کند حل می‌شود، که این یک ویژگی عمدی در اکسل است و وقتی به‌طور تصادفی فعال شود، منبع مطمئنی برای سردرگمی است

ویرایش‌های ساختاری همان جایی است که حسابداری بین‌شیتی ارزش خودش را نشان می‌دهد، و سمت XLSX نام‌ها را در طول این ویرایش‌ها سازگار نگه می‌دارد. InsertRows و DeleteRows بازه‌های نام‌های تعریف‌شده را همراه با سلول‌ها، merge‌ها، هایپرلینک‌ها و لنگرهای نمودار جابه‌جا می‌کنند، بنابراین نامی که به Data!$A$2:$D$100 اشاره دارد، حتی پس از اینکه تولیدکننده شکافی بالای آن باز می‌کند، همچنان بلوک داده را پوشش می‌دهد. فرمول‌ها یک نکتهٔ مستندشده دارند: درج ردیف فقط ارجاعاتی را تنظیم می‌کند که به شیت در حال ویرایش اشاره دارند. یک فرمول در Summary که به Data!D2:D100 ارجاع می‌دهد، وقتی ردیف‌هایی به Data اضافه می‌شود، بازنویسی می‌شود، که معمولاً همان چیزی است که می‌خواهید. به‌جای فرض‌کردن آن را بررسی کنید، چون موتور محاسبه این کار را کم‌هزینه در اختیارتان می‌گذارد:

// موتور محاسبه، نام‌ها و ارجاعات بین‌شیتی را درون-فرآیندی حل می‌کند
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

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

نام‌های _xlnm که متعلق به لایهٔ property هستند

اگر جدول نام یک فایل تولیدشده را در یک ابزار بازرسی سطح‌پایین باز کنید، ورودی‌هایی می‌بینید که هرگز خودتان ننوشته‌اید: _xlnm.Print_Area، _xlnm.Print_Titles و موارد مشابه آن‌ها. این‌ها روشی است که OOXML (ECMA-376 / ISO 29500) ناحیه‌های چاپ و ردیف‌های عنوان تکرارشونده را به‌صورت نام‌های تعریف‌شده با شناسه‌های رزرو‌شده ذخیره می‌کند. HotXLS آن‌ها را از طریق property‌های اختصاصی worksheet مدیریت می‌کند، پس تنظیم PrintArea یا PrintTitleRows ورودی _xlnm.* متناظر را برایتان می‌نویسد

دام اصلی این است که دستی به آن فضای‌نام رزروشده دست بزنید. اگر یک ورودی _xlnm.Print_Area را از طریق DefinedNames.Add اضافه کنید و همزمان property مربوط به PrintArea را هم تنظیم کنید، ورک‌بوک دو تعریف متعارض برای یک نام رزروشده حمل می‌کند، وضعیتی که اکسل آن را به شیوه‌هایی حل می‌کند که هیچ محصولی نباید به آن‌ها متکی باشد. هر شناسه‌ای که با _xlnm. شروع می‌شود را متعلق به لایهٔ property در نظر بگیرید. برای بررسی تنظیمات چاپ، property‌ها را بخوانید، نه جدول نام را. مقالهٔ محافظت و تنظیمات صفحه این property‌های ناحیهٔ چاپ را در بافت (context) خودشان توضیح می‌دهد

دو مرز که ارزش دانستن پیش از قطعی‌کردن طراحی را دارند

نام‌های تعریف‌شده از پل تبدیل راحت XLS-to-XLSX همراه نمی‌آیند. SaveXLSWorkbookAsXLSX محتوای سلول و قالب‌بندی پایه را کپی می‌کند، و جدول نام در فهرست مستندشدهٔ کپی آن نیست، بنابراین ورک‌بوکی که به نام‌های خود وابسته بود، آن‌ها را در این گذر از دست می‌دهد. پس از تبدیل، نام‌ها را دوباره از طریق DefinedNames.Add بسازید. این مرحله کمتر از آنچه به‌نظر می‌رسد دردسر دارد، چون این فرصت را به شما می‌دهد که دامنهٔ آن‌ها را یکدست کنید، به‌جای اینکه هرچه فایل XLS تصادفاً داشته باشد را همان‌طور منتقل کنید

مرز دیگر، انحراف (drift) میان رشته‌های فرمول و نام شیت‌هاست. اکسل هنگام تغییرنام تعاملی، ارجاعات شیت را درون فرمول‌ها و نام‌ها بازنویسی می‌کند، پس فایل‌هایی که کاربر در اکسل ویرایش می‌کند خودشان سازگار می‌مانند. نقطهٔ آسیب‌پذیر در سمت تولیدکننده است: وقتی کد Pascal رشته‌های فرمول را از یک literal نام شیت می‌سازد، تغییرنام شیت در یک جا و فراموش‌کردن جای دیگر، ارجاعی به شیتی می‌سازد که دیگر وجود ندارد. نام شیت را در یک ثابت (constant) واحد در Delphi نگه دارید و همان را هم به Sheets.Add و هم به مونتاژ فرمول خود بدهید، تا این دو هرگز با هم اختلاف پیدا نکنند. این همان غریزه‌ای است که نام‌گذاری سلول‌های خروجی یک گزارش را به‌جای hard-code کردن آدرس‌ها توصیه می‌کند: قالبی که سلول جمع کل آن نام‌گذاری‌شده است، حتی پس از اینکه طراح سه ردیف بالای آن درج می‌کند همچنان کار می‌کند، در حالی که تولیدکننده‌ای که در آدرس ثابت B17 می‌نویسد، بی‌سروصدا عدد خود را در جای اشتباه قرار می‌دهد. مقالهٔ تولید گزارش قالبی دقیقاً بر همین الگو بنا شده است

API کامل نام‌های تعریف‌شده برای هر دو فرمت، به‌همراه مرجع موتور فرمول، به‌همراه HotXLS Delphi Component ارائه می‌شود