اعتبارسنجی داده‌ها، فیلتر خودکار و جدول‌های اکسل در Delphi با HotXLS

سه ویژگی در HotXLS یک کاربرگ را به اشتراک می‌گذارند اما روی اشیای کاملاً متفاوتی عمل می‌کنند، و دردسر از جایی شروع می‌شود که فرض کنید کارهای مشابهی انجام می‌دهند. اعتبارسنجی داده یک قاعده را به یک محدوده متصل می‌کند که آنچه کاربر می‌تواند در آن تایپ کند را محدود می‌کند. یک AutoFilter یک تعریف معیار ذخیره‌شده را به یک ناحیه متصل می‌کند و این‌که کدام سطرها را یک بیننده نشان می‌دهد را تغییر می‌دهد. یک جدول یک محدوده را در یک ساختار نام‌دار و تایپ‌شده با استایل‌دهی نواری می‌پیچد. یکی ورودی را محدود می‌کند، یکی یک نما را ثبت می‌کند، یکی یک اسکیما تحمیل می‌کند. هیچ‌کدام به‌تنهایی حتی یک مقدار سلول را جابه‌جا نمی‌کند، و AutoFilter به‌طور خاص افراد را فریب می‌دهد، چون کلمه یک عمل را القا می‌کند در حالی‌که فقط یک تعریف را ذخیره می‌کند. دانستن این‌که هر فراخوانی کدام شیء را لمس می‌کند، و این‌که اثر واقعاً کِی متحقق می‌شود، همان چیزی است که یک کتابچهٔ کاری که در Excel همان‌طور رفتار می‌کند که در تست‌های شما رفتار کرد را از کتابچه‌ای که بی‌سروصدا واگرا می‌شود جدا می‌کند

AutoFilter یک تعریف را ذخیره می‌کند، سطرها را برش نمی‌دهد

یک AutoFilter در یک فایل ذخیره‌شده یک رکورد معیار است. مخفی‌شدن سطر بعداً اتفاق می‌افتد، وقتی Excel کتابچهٔ کاری را باز می‌کند و معیار را در برابر داده ارزیابی می‌کند. HotXLS آن رکورد را وفادارانه می‌نویسد و هیچ‌چیز را برش نمی‌دهد: هر سطری که فیلتر کرده‌اید همچنان فیزیکی در فایل حضور دارد. یک pipeline که یک فیلتر را برای انداختن سفارش‌های رد‌شده اعمال می‌کند و سپس کتابچهٔ کاری را دوباره می‌خواند، همهٔ آن‌ها را می‌بیند، رد‌شده‌ها هم شامل، و کد از نظر API درست است در حالی‌که از نظر مدل ذهنی نویسنده غلط است. روی کاربرگ XLSX، SetAutoFilter ناحیهٔ فیلترشده را اعلام می‌کند و AddAutoFilterColumn معیار را به یک ستون از آن متصل می‌کند. وقتی کد سمت سرور به نتیجهٔ واقعی نیاز دارد، برای تعداد سطر در یک خلاصه یا برای ارسال فقط سطرهای منطبق، کتابخانه معیار را برای شما ارزیابی می‌کند به‌جای این‌که وانمود کند فایل تغییر کرده:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, Visible: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    // شناسه ستون 3 = چهارمین ستون درون بازه فیلتر (افست مبتنی بر صفر)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible اکنون با آنچه اکسل پس از بازکردن فایل نشان می‌دهد مطابقت دارد

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

AutoFilterRowVisible به هر سطر جداگانه پاسخ می‌دهد، و PreviewAutoFilterRows کل ناحیه را از طریق یک callback می‌گردد وقتی به مجموعهٔ منطبق در یک گذر نیاز دارید. یک موردی هست که هیچ‌کدام پاسخ درستی نیست: اگر نیازمندی این است که سطرهای مستثنی‌شده اصلاً نباید در فایل وجود داشته باشند، یک برش حریم خصوصی به‌جای یک نما، سطرها را کاملاً حذف کنید. یک فیلتر آنجا ابزار غلطی است، چون هر گیرنده‌ای می‌تواند با یک کلیک آن را پاک کند و داده‌ای که قصد داشتید مخفی کنید دوباره روی صفحه برمی‌گردد

شناسهٔ ستون یک افست است، نه یک شمارهٔ ستون

کامنت در قطعه‌کد بالا همان تله‌ای را نشانه‌گیری می‌کند که بیشترین زمان دیباگ را در این API می‌گیرد. AddAutoFilterColumn هدفش را با موقعیت 0-پایه درون محدودهٔ فیلتر شناسایی می‌کند، نه با ستون کاربرگ. برای فیلتری روی A1:E500 این دو سیستم شماره‌گذاری اتفاقاً یک واحد اختلاف دارند، که دقیقاً همان نوع نزدیک‌به‌خطایی است که از یک تست سریع جان سالم به‌در می‌برد و درست همان لحظه‌ای که یک همکار ستون دیگری را فیلتر می‌کند خراب می‌شود. برای فیلتری که از ستون C شروع می‌شود، id برابر 0 یعنی ستون C، و ناهماهنگی سریع آشکار می‌شود. وقتی محدودهٔ فیلتر در زمان اجرا محاسبه می‌شود، شناسهٔ ستون را از همان متغیری استخراج کنید که رشتهٔ محدوده را ساخته، هرگز از یک ثابت ستون کاربرگ. هر ستون یک شرط دوم را از طریق اُورلودی می‌پذیرد که دو عملگر، دو معیار و یک اتصال‌دهندهٔ and/or می‌گیرد، که دیالوگ فیلتر سفارشی Excel را بازتاب می‌دهد. نمای XLS همین زمینه را با SetAutoFilter به‌همراه ApplyAutoFilter پوشش می‌دهد، که پارامترهای معیار و عملگرش قراردادهای قدیمی‌تر سبک COM را دنبال می‌کنند و فیلد را از 1 شماره‌گذاری می‌کنند. تعویض نما یعنی تعویض مبنای ایندکس، پس محل فراخوانی سزاوار یک کامنت است که بگوید کدام‌یک در کار است

قواعد اعتبارسنجی همان قراردادی هستند که کاربران شما زیر آن ویرایش می‌کنند

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

var
  Idx: Integer;
begin
  Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
  Sheet.DataValidations[Idx].SetPrompt('Status',
    'Pick one of the listed states');
  Sheet.DataValidations[Idx].SetError('Invalid status',
    'Type or paste only listed values', xlsxDvErrStop);
  Sheet.DataValidations[Idx].AllowBlank := False;

  // مقادیر: اعداد صحیح، صفر یا بیشتر
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

فراتر از لیست‌ها و اعداد صحیح، همان خانواده اعشاری‌ها، تاریخ‌ها، زمان‌ها، طول متن و فرمول‌های آزاد را از طریق AddCustomValidation پوشش می‌دهد، و AddDataValidation عمومی کل ماتریس نوع-و-عملگر را برای سازندگان قاعده‌ای که با پیکربندی هدایت می‌شوند در معرض قرار می‌دهد. سبک خطا بیشتر از آنچه نامش القا می‌کند اهمیت دارد. xlsxDvErrStop ورودی بد را کاملاً رد می‌کند؛ سبک‌های warning و information اجازه می‌دهند مقدار بعد از یک کلیک تنها عبور کند. بر اساس این‌که کدی که کتابچهٔ کاری را دوباره می‌خواند می‌تواند یک مقدار خارج از قاعده را تحمل کند یا نه، برای هر ستون انتخاب کنید. دو مرز باید در متن prompt یا README‌ای که با فایل ارسال می‌کنید ذکر شوند. اعتبارسنجی در Excel از تایپ محافظت می‌کند، اما چسباندن یک بلوک روی یک محدودهٔ اعتبارسنجی‌شده از قاعده جا خالی می‌دهد، پس هر کدی که داده را دوباره می‌خواند باید دوباره اعتبارسنجی کند به‌جای این‌که به سلول‌ها اعتماد کند. و یک قاعده محدودهٔ لفظی‌ای را که به آن داده‌اید پوشش می‌دهد، که یعنی متصل کردن اعتبارسنجی پیش از دانستن تعداد نهایی سطر، دنبالهٔ اضافه‌شده را بدون محافظ رها می‌کند. ابتدا داده را بنویسید، سپس اندازهٔ قواعد را با گسترهٔ واقعی تطبیق دهید

نمای قدیمی همان خانواده‌های قاعده را با یک تفاوت ارگونومیک ارائه می‌دهد. سازنده‌های سمت XLS، یعنی AddWholeNumberValidation، AddDecimalValidation، AddDateValidation، AddTimeValidation، AddTextLengthValidation و AddCustomValidation، شیء TDataValidation را مستقیماً برمی‌گردانند نه یک ایندکس، پس پیکربندی prompt و error از روی ارجاع برگشتی زنجیر می‌شود نه یک lookup. شمارش عملگر (xlsDvBetween، xlsDvGreaterThan و بقیه) مجموعهٔ XLSX را بازتاب می‌دهد، پس کد ساخت‌قاعده به‌جز آن تفاوت سبک بازگشتی، میان نماها قابل‌انتقال است. متن prompt خودش به همان اندازهٔ قاعده فکر می‌خواهد. یک منوی کشویی که ورودی را با یک جعبهٔ خطای خالی رد می‌کند به کاربران یاد می‌دهد به IT ایمیل بزنند؛ یکی که حالت‌های مجاز را نام می‌برد به آن‌ها یاد می‌دهد سلول را درست کنند و ادامه دهند

یک وارونگی قطبیت که کتابخانه برای شما جذب می‌کند

هرکسی که XML اعتبارسنجی OOXML را دستی خوانده با ویژگی وارونهٔ showDropDown روبه‌رو شده: در ISO/IEC 29500 یک مقدار true یعنی «فلش کشویی را سرکوب کن»، برعکس چیزی که نامش نشان می‌دهد. HotXLS این را داخلی وارونه می‌کند، پس ویژگی ShowDropDown روی یک قاعدهٔ اعتبارسنجی همان معنایی را می‌دهد که می‌گوید، با true که کشویی را نشان می‌دهد. تنها راهی که ممکن است دچار مشکل شوید مخلوط کردن سطوح حقیقت است، تنظیم ویژگی از کد در حالی‌که یک همکار XML ذخیره‌شده را ممیزی می‌کند و ویژگی‌ای را که برایش برعکس به نظر می‌رسد «تصحیح» می‌کند. تصمیم بگیرید که برای ابزار بازبینی، ویژگی معتبر است یا XML خام، و این وارونگی را همان‌جایی بنویسید که آن تصمیم زندگی می‌کند

جدول‌ها به یک محدوده یک اسکیما و یک نام می‌دهند

یک جدول کاربرگ، که در اصطلاح Excel ListObject نامیده می‌شود، یک محدوده را در یک نام، ستون‌های تایپ‌شده، استایل‌دهی نواری و پشتیبانی از ارجاع ساختاریافته می‌پیچد. این همان ویژگی‌ای است که یک کتابچهٔ کاریِ تولیدشده را به‌محض این‌که کاربران شروع به مرتب‌سازی و گسترش آن می‌کنند، تمام‌شده حس می‌کند. ساخت آن در سراسر نماها متقارن است، با AddTable که یک نام، یک محدوده و یک فهرست ستون می‌گیرد:

var
  Cols: TStringList;
begin
  Cols := TStringList.Create;
  try
    Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
    Sheet.AddTable('Orders', 'A1:E500', Cols);
  finally
    Cols.Free;
  end;
end;

در سمت XLSX، شیء جدول برگشتی StyleName (خانوادهٔ درون‌ساختِ TableStyleMedium2 و خواهر و برادرهایش)، سوییچ‌های نواری، و یک فلگ سطر جمع را در معرض قرار می‌دهد، پس اعمال استایل‌دهی سازمانی یک انتساب ویژگی است نه یک گذر فرمت‌دهی دستی. در فایل‌های قدیمی .xls همان فراخوانی رکوردهای جدول BIFF8 را می‌نویسد، و نما همچنین AddPivotTable را برای نماهای خلاصه‌ساخته‌شده از فیلدهای سطر، ستون و داده ارائه می‌دهد، یادآوری این‌که «جدول‌ها» در قالب قدیمی‌تر فراتر از ListObject در OOXML می‌روند. جدول‌ها را همان‌طور نام‌گذاری کنید که view‌های پایگاه‌داده را نام‌گذاری می‌کنید. کد پایین‌دستی‌ای که Orders[Amount] را با ارجاع ساختاریافته می‌خواند، از بازچینی ستونی که کد موقعیتی را می‌شکند جان سالم به‌در می‌برد

دو قرارداد بعداً پاک‌سازی را ذخیره می‌کنند. Excel لازم دارد نام جدول‌ها در کل کتابچهٔ کاری یکتا باشند، پس یک تولیدکننده که یک شیت در هر ناحیه صادر می‌کند به یک طرح مثل Orders_EMEA نیاز دارد به‌جای استفادهٔ مجدد از Orders. یک تکراری در زمان نوشتن شکست نمی‌خورد؛ به‌صورت یک دیالوگ تعمیر وقتی کاربر فایل را باز می‌کند سر بر می‌آورد، که بدترین جا برای کشف آن است. قرارداد دیگر به سطر جمع مربوط می‌شود: وقتی فعال باشد، درست زیر محدودهٔ داده می‌نشیند، پس هر کدی که بعداً با «آخرین سطر استفاده‌شده به‌علاوهٔ یک» اضافه می‌کند، داخل نوار جمع‌ها می‌نویسد به‌جای بعد از آن. گسترهٔ داده را جدا از گسترهٔ جدول ردیابی کنید تا افزوده‌ها همان‌جایی بنشینند که انتظار دارید

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

اعتبارسنجی، فیلترها و جدول‌ها همان تفاوت میان ارسال یک شبکه از مقدارها و ارسال یک اپلیکیشن کوچک هستند. مرجع کامل قاعده، فیلتر و جدول در صفحهٔ محصول HotXLS Delphi Component موجود است