HotXLS، کامپوننت Excel در Delphi و C++Builder، یک قاعدهی قالببندی شرطی یا اعتبارسنجی داده را بهطور خودکار به دو یا چند شیء قاعدهی جداگانه تقسیم میکند هرگاه یک درج یا حذف ردیف یا ستون، بازهی پوششدادهشدهی قاعده را به تکههایی ببرد که به لنگرهای فرمول نسبی متفاوتی نیاز دارند، سپس به هر قاعدهی قالببندی شرطی یک شمارهی اولویت تازه و یکتا دوباره اختصاص میدهد. این رفتار در نسخهی 2.196 موتور XLSX عرضه شد و بهطور خودکار اجرا میشود، بدون هیچ تنظیمی برای انصراف. محرک آن محدود اما رایج است: یک قاعدهی cellIs یا expression که فرمولش یک سلول را نسبت به بازهی خودش میخواند، روی یک برگکاری که بعداً یک ردیف در جایی وسط همان بازهی دقیق درج یا حذف میشود
اغلب نوشتهها دربارهی خودکارسازی اکسل روی مسئلهی متن فرمول متوقف میشوند: شمارههای ردیف و ستون درون هر SUM() و هر VLOOKUP() را جابهجا کنید تا ارجاعها همچنان به سلولهای درست اشاره کنند. آن نیمه از داستان واقعی است، و در مقالهی همراه دربارهی چگونگی بازنویسی ارجاعهای فرمول توسط HotXLS هنگام جابهجایی ردیفها و ستونها پوشش داده شده، اما یک قالببندی شرطی یا یک قاعدهی اعتبارسنجی داده صرفاً یک فرمول نشسته درون یک سلول نیست. این یک فرمول را با یک بازه جفت میکند، sqref در اصطلاح ECMA-376، و این دو باید با هم حرکت کنند. وقتی یک ویرایش ساختاری آن بازه را به دو تکه میبرد که برای درستماندن به دو افست نسبی متفاوت نیاز دارند، نگهداشتن یک شیء قاعده با یک رشتهی فرمول دیگر گزینهای نیست، و وانمودکردن خلافش دقیقاً همان چیزی است که یک قاعدهی هایلایت را بیسروصدا وادار میکند ردیفهای اشتباه را مقایسه کند
چرا درج یک ردیف یک قاعدهی قالببندی شرطی را تقسیم میکند بهجای اینکه صرفاً آن را جابهجا کند؟
یک قالببندی شرطی یا قاعدهی اعتبارسنجی داده دقیقاً یک فرمول را برای کل بازهی خودش نگه میدارد، که نسبت به یک سلول لنگر تکی ارزیابی میشود، پس بهمحض اینکه یک ویرایش دو بخش از آن بازه را وادار کند به دو افست نسبی متفاوت نیاز داشته باشند، یک فرمول دیگر نمیتواند هر دو بخش را بهدرستی توصیف کند. ECMA-376 پوشش یک قاعده را بهعنوان ویژگی sqref روی عنصر conditionalFormatting یا dataValidation بیان میکند، و اکسل Formula1 و Formula2 را طوری ارزیابی میکند که انگار متن در سلول بالا-چپ آن sqref تایپ شده و در سرتاسر باقی آن پر شده، دقیقاً همانطور که یک فرمول نسبی معمولی در طول یک ستون پر میشود. یک هایلایت انحراف روی B2:B50 را تصور کنید که هر رقم واقعی بیش از بودجهاش را پرچم میزند، ساختهشده بهعنوان یک قاعدهی cellIs که Formula1اش متن لفظی C2 است، بهمعنای مقایسهی سلول B ردیف فعلی با سلول C همان ردیف
Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);
Sheet.InsertRows(25, 1); // one blank separator row, starting at old row 25آن یک ردیف جداکننده را در ردیف قدیمی ۲۵ درج کنید و ردیفهای بالای نقطهی درج جابهجا نمیشوند، پس سهم آنها از قاعده همچنان Formula1 را بهدرستی بهعنوان C2 میخواند. ردیفهایی که سابقاً ۲۵ تا ۵۰ بودند به ۲۶ تا ۵۱ میلغزند، و برای آنها C2 حالا کاملاً سلول اشتباهی است، چون ردیف ۲۶ باید در برابر C26 مقایسه شود، نه در برابر یک رقم بودجهی دو دوجین ردیف بالاتر
HotXLS چطور تصمیم میگیرد یک قاعده باید تقسیم شود
HotXLS فقط زمانی اشیاء قاعدهی اضافه میسازد که هندسه واقعاً آن را ایجاب کند: یک روال داخلی، XlsxBuildShiftedRuleParts، هر ناحیهی مجزا در sqref قاعده را میپیماید، حل میکند سلول لنگر آن ناحیه پیش از ویرایش چه بود و پس از آن چه میشود، و بررسی میکند آیا هر تکهی حاصل به همان تصحیح افست نسبی نیاز خواهد داشت. اگر همهی تکهها موافق باشند، یک قاعده باقی میماند، sqrefاش بهعنوان اجتماع تکههای جابهجاشده بازساخته میشود و فرمولش یکبار پایهگذاری مجدد میشود. یک تقسیم واقعی فقط زمانی رخ میدهد که تکهها ناموافق باشند، دقیقاً همان حالت B2:B50 بالا، جایی که بلوک بالایی لنگر اصلیاش را نگه میدارد و بلوک پایینی به یک لنگر جدید نیاز دارد
پایهگذاری مجدد فرمول یک تکه یک حرکت دومرحلهای است که ماشینآلاتی را که HotXLS از پیش برای گروههای فرمول مشترک OOXML حمل میکند دوباره استفاده میکند: ابتدا فرمول طوری ترجمه میشود انگار در ابتدا در سلول بالا-چپ خودِ آن تکه لنگر شده بود، با استفاده از همان محاسبات افست نسبی که یک فرمول مشترک را در سراسر بازهاش گسترش میدهد، سپس نتیجه از همان اسکنر جابهجایی ردیف-و-ستون که فرمولهای معمولی برگکاری را بازنویسی میکند عبور میکند. اینطور است که Formula1 در دو حرکت بهجای یک مورد خاص دستینوشته، از C2 به C26 میرود: C2 را ۲۳ ردیف به جلو ترجمه کنید تا C25 بهدست بیاید، انگار قاعده همیشه از آنجا شروع شده بود، سپس اجازه دهید جابهجایی معمولی در ردیف ۲۵ آن را به C26 بِراند. هر ویژگی دیگر، رنگ پرشدگی، توقف-اگر-درست، خودِ عملگر، بدون تغییر روی شیء قاعدهی جدید سوار میشود، پس هر دو نیمه همان رنگی را که همیشه رنگ میکردند به سلولها میرسانند
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)آیا نوارهای داده و مجموعههای آیکون هم مثل قواعد cellIs تقسیم میشوند؟
خیر: HotXLS فقط انواعی از قاعده را که درستیشان واقعاً به یک فرمول نسبی بهازای هر ناحیه بستگی دارد، مقایسههای cellIs و قواعد expression، بخشبندی میکند، و هر نوع دیگر قالببندی شرطی را بهعنوان یک شیء قاعدهی تکی باقی میگذارد که sqrefاش صرفاً برای پوشش تکههای جابهجاشده بهعنوان یک اجتماع چندناحیهای رشد میکند. داخلاً این شاخه یک بررسی سادهی Kind است، cf.Kind in [cfkCellIs, cfkExpression]، چیزی عجیبتر از این نیست. نوارهای داده، مقیاسهای دو و سهرنگی، مجموعههای آیکون، رتبهبندیهای بالا و پایین، و آشکارسازهای تکراری، خالی، و خطا یک payload حمل میکنند، یک رنگ نوار، مجموعهای از توقفهای مقیاس، یک خانوادهی آیکون، که کل بازهی پوششدادهشده را یکجا توصیف میکند نه یک مقایسهی نسبی بهازای هر سلول، پس تقسیم آنها به چند شیء قاعدهی اولویتدار هیچ درستیای نمیخرد و فقط قاعدههایی برای مدیریت میافزاید. وقتی یک ویرایش بازهشان را تقسیم میکند، HotXLS تکهها را در یک قاعده با sqref چندناحیهای دوباره ترکیب میکند و payload را بهعنوان یک واحد تکی دوباره لنگر میزند بهجای اینکه یک شیء قاعدهی جدید بهازای هر تکه شبیهسازی کند. این تمایز با طبقهبندی نوع-قاعده در مقالهی اصول قالببندی شرطی و متن غنی همراستا است: نوارهای داده، مقیاسهای رنگی، و مجموعههای آیکون از پیش با نادیدهگرفتن کامل ویژگی Style از قواعد cellIs جدا میایستند، و حالا معلوم میشود آنها به همان دلیل زیرین از دوبارهلنگرزنی بهازای هر ناحیه هم جدا میایستند
چرا اولویتهای قاعده پس از یک ویرایش ساختاری تغییر میکنند؟
اولویتها تغییر میکنند چون هر شبیهسازی دقیقاً همان مقدار اولویتی را که قاعدهای که از آن تقسیم شده داشت با خودش شروع میکند، و HotXLS بعداً یک پاس نرمالسازی اجرا میکند که تکراریهای حاصل را به یک ترتیب تمیز و بدونشکاف حل میکند بهجای اینکه دو قاعده را با رتبهی یکسان دستنخورده رها کند. یک روال داخلی دوم، XlsxNormalizeConditionalFormatPriorities، اولویت فعلی هر قالببندی شرطی را میگیرد، برای هر قاعدهای که هرگز یکی بهطور صریح روی آن تنظیم نشده به موقعیت آن قاعده در مجموعه فرومیگردد، کل فهرست را بهطور پایدار مرتب میکند پس تساویها ترتیب نسبی اصلیشان را نگه دارند، و نتیجهی مرتبشده را به یک دنبالهی متراکم ۱، ۲، ۳ بدون شکاف و بدون تکرار دوباره شمارهگذاری میکند. HotXLS یکبار پیش از شروع یک جابهجایی این را اجرا میکند، پس شبیهسازی از یک خطمبنای تمیز شروع میشود، و دوباره پس از هر تقسیم و هر قاعدهی خالیشدهای که حذف میشود، پس فایلی که ذخیره میشود هرگز دو ورودی قاعده ندارد که ادعای همان اولویت را داشته باشند. این وقتی اهمیت پیدا میکند که توصیهی مقالهی اصول قالببندی شرطی را دنبال کرده باشید که بین مقادیر اولویت شکاف بگذارید پس یک قاعدهی بعدی بتواند بدون دوبارهشمارهگذاری بقیه جا بگیرد: آن شکافها تا ویرایش ردیف یا ستون بعدی که آن برگکاری را لمس کند دوام میآورند، سپس فرومیریزند، چون نرمالسازی فقط یکتایی و ترتیب پایدار را تضمین میکند، نه اینکه طرح شمارهگذاری اصلی شما بدون تغییر برگردد
قواعد اعتبارسنجی داده هم تقسیم میشوند، بدون یک اولویت برای دوبارهشمارهگذاری
قواعد اعتبارسنجی داده از همان منطق بخشبندی بازهی cellIs و قالببندیهای شرطی expression عبور میکنند، و برخلاف قالببندی شرطی، هر نوع اعتبارسنجی یکنواخت از آن مسیر عبور میکند: HotXLS هیچ خانوادهی غیرفرمولی جداگانهای برای اعتبارسنجی داده ندارد، همانطور که نوارهای داده و مجموعههای آیکون برای قالببندی شرطی دارند، پس یک قاعدهی فهرست ساده یا عددکامل با همان روالی که یک فرمول سفارشی نسبی را مدیریت میکند بخشبندی میشود. آنچه فرق میکند اولویت است: ECMA-376 هیچ ویژگی priorityای اصلاً به عنصر dataValidation نمیدهد، پس هیچ گام دوبارهشمارهگذاریای برای اعتبارسنجیها مثل قالببندیهای شرطی وجود ندارد. یک اعتبارسنجی فرمول-سفارشی را تصور کنید که مبلغ واقعی هر ردیف را از عبورکردن از بودجهی خودش در ستون کناریاش نگه میدارد
Sheet.AddCustomValidation('D2:D400', 'D2<=C2');
Sheet.DeleteRows(150, 5); // remove five rows out of the validated range
// DataValidations now holds two rules instead of one:
// D2:D149 Formula1 = 'D2<=C2' (rows above the deletion)
// D150:D395 Formula1 = 'D150<=C150' (rows that shifted up)این به همان دلیلی اهمیت دارد که مقالهی اصول اعتبارسنجی داده در برابر پیوستکردن یک قاعده پیش از قطعیشدن شمار ردیف هشدار میدهد: یک اعتبارسنجی فقط سلولهای لفظیای را که به آن دادهاید پوشش میدهد، و یک ویرایش ساختاری بعدی میتواند دو یا چند قاعده را بهجا بگذارد که کاری را انجام میدهند که یکی سابقاً انجام میداد. از نظر عملکردی چیزی نمیشکند: هر سلول در بازهی اصلی همچنان توسط چیزی اعتبارسنجی میشود، اما کدی که فرض میکند یک ورودی DataValidations بهازای هر ستون وجود دارد، پس از اولین ویرایشی که آن را لمس کند، شروع به نمایهدهی اشتباه میکند. یک سقف سخت روی اینکه این تا کجا میتواند پیش برود وجود دارد: اگر تقسیمکردن یک برگکاری را از ۶۵٬۵۳۴ قاعدهی اعتبارسنجی داده فراتر ببرد، HotXLS بهجای نوشتن فایلی که اکسل بیسروصدا رد میکند، یک استثنا raise میکند، که همان امتناع کتابخانه از ساختن یک کاربرگ خراب است نه یک محدودیتی که استفادهی معمولی احتمالاً به آن برسد
پس از یک درج یا حذف انبوه چه چیزی را باید بررسی کرد
دو چیزی که ارزش بررسیکردن دارند پس از اینکه یک اسکریپت یک دسته از ویرایشهای ردیف یا ستون را روی یک برگهی پر از قالببندیهای شرطی و اعتبارسنجیها اجرا کند، شمار کل قاعده و ترتیب اولویت هستند، چون هر دو میتوانند به شکلهایی جابهجا شوند که در بازبینی کد بهراحتی از قلم میافتند و همان لحظهای که کسی Manage Rules را در اکسل باز کند آشکار میشوند. یک ویرایش بهندرت آسیب زیادی میزند: یک درج تکی در میانهی یک قاعدهی cellIs حداکثر دو شیء قاعده جایی که یکی بود تولید میکند. ریسک وقتی مرکب میشود که یک روال تولید گزارش، ردیفها را یکییکی درون یک حلقه روی برگهای که از پیش چند قاعدهی فرمول-لنگرشده حمل میکند درج میکند: هر پاس میتواند قواعدی را که یک پاس قبلی از پیش تقسیم کرده دوباره تقسیم کند، و پنج قاعدهی cellIs اصلی میتوانند به چند برابر آن تعداد قطعهی کمارزش که تکههای کوچکی از بازهی اصلی را پوشش میدهند ختم شوند. دستهبندی ویرایشهای ساختاری، درج کل بلوک جدید در یک فراخوانی بهجای یک ردیف در یک زمان، شمار قاعده را به تعداد لنگرهای واقعاً متمایز گره میزند نه به تعداد ویرایشهای انجامشده
بخشبندی قاعده و نرمالسازی اولویت بهعنوان رفتار استاندارد موتور XLSX در کامپوننت Excel از HotXLS برای Delphi برای Delphi و C++Builder عرضه میشود؛ صفحهی محصول مرجع کامل API ویرایش برگکاری را حمل میکند، از جمله متدهای قالببندی شرطی و اعتبارسنجی داده که در اینجا توصیف شد