HotXLS, רכיב ה-Excel של Delphi ו-C++Builder, מפצלת אוטומטית כלל עיצוב מותנה או אימות נתונים לשני אובייקטי כלל נפרדים או יותר בכל פעם שהוספה או מחיקה של שורה או עמודה חותכת את הטווח המכוסה של הכלל לחלקים שזקוקים לעוגני נוסחה יחסיים שונים, ואז מקצה מחדש לכל כלל עיצוב מותנה מספר עדיפות ייחודי חדש. ההתנהגות יצאה בגרסה 2.196 של מנוע ה-XLSX ורצה אוטומטית, ללא הגדרה לצאת ממנה. הטריגר צר אך נפוץ: כלל cellIs או expression שהנוסחה שלו קוראת תא יחסית לטווח שלו עצמו, שיושב על גיליון עבודה שמאוחר יותר מקבל שורה שמוכנסת או מוסרת אי-שם באמצע אותו טווח בדיוק
רוב הכתבות על אוטומציית Excel נעצרות בבעיית טקסט-הנוסחה: להזיז את מספרי השורה והעמודה בתוך כל SUM() וכל VLOOKUP() כך שההפניות עדיין מצביעות על התאים הנכונים. חצי הסיפור הזה אמיתי, ומתואר בהמאמר הנלווה על איך HotXLS כותבת מחדש הפניות נוסחה כאשר שורות ועמודות זזות, אבל כלל עיצוב מותנה או אימות נתונים אינו סתם נוסחה שיושבת בתא. הוא מצמיד נוסחה עם טווח, sqref במונחי ECMA-376, ושניהם חייבים לזוז יחד. כאשר עריכה מבנית פורסת את הטווח הזה לשני חלקים שהיו זקוקים לשני היסטים יחסיים שונים כדי להישאר נכונים, השארת אובייקט כלל אחד עם מחרוזת נוסחה אחת מפסיקה להיות אופציה, וההעמדת פנים שזה לא כך היא איך שכלל הדגשה מתחיל בשקט להשוות את השורות הלא-נכונות
למה הוספת שורה מפצלת כלל עיצוב מותנה במקום פשוט להזיז אותו?
כלל עיצוב מותנה או אימות נתונים שומר נוסחה אחת בדיוק לכל הטווח שלו, מוערכת יחסית לתא-עוגן יחיד, כך שברגע שעריכה כופה על שני חלקים של הטווח הזה לזקוק לשני היסטים יחסיים שונים, נוסחה אחת כבר לא יכולה לתאר את שני החלקים נכון. ECMA-376 מבטא את כיסוי הכלל כמאפיין sqref על אלמנט ה-conditionalFormatting או ה-dataValidation, ו-Excel מעריך את 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הכנס את שורת המפריד הבודדת ההיא בשורה הישנה 25 והשורות מעל נקודת ההכנסה לא זזות, כך שהחלק שלהן בכלל עדיין קורא את Formula1 כ-C2 נכון. השורות שהיו פעם 25 עד 50 גולשות מטה ל-26 עד 51, ועבורן C2 עכשיו התא הלא-נכון לגמרי, שכן שורה 26 צריכה להשוות מול C26, לא מול נתון תקציב עשרים וכמה שורות מעליה
איך HotXLS מחליטה אם כלל זקוק לפיצול
HotXLS יוצרת אובייקטי כלל נוספים רק כאשר הגיאומטריה באמת דורשת זאת: פונקציה פנימית, XlsxBuildShiftedRuleParts, עוברת על כל אזור נבדל ב-sqref של הכלל, מחשבת מה היה תא-העוגן של האזור הזה לפני העריכה ומה הוא הופך אחריה, ובודקת אם כל חלק שנוצר יזדקק לאותו תיקון היסט-יחסי. אם כל החלקים מסכימים, כלל אחד שורד, ה-sqref שלו נבנה מחדש כאיחוד החלקים שהוזזו והנוסחה שלו נגזרת מחדש (rebased) פעם אחת. פיצול אמיתי קורה רק כאשר החלקים לא מסכימים, בדיוק המקרה B2:B50 לעיל, שבו הבלוק העליון שומר על העוגן המקורי שלו והבלוק התחתון זקוק לחדש
גזירה-מחדש של נוסחת חלק היא מהלך דו-שלבי שעושה שימוש חוזר במנגנון ש-HotXLS כבר נושאת עבור קבוצות נוסחה משותפת של OOXML: קודם הנוסחה מתורגמת כאילו במקור עוגנה בתא השמאלי-עליון של אותו חלק עצמו, באמצעות אותו חשבון היסט-יחסי שמרחיב נוסחה משותפת על פני הטווח שלה, ואז התוצאה עוברת דרך אותו סורק היסט-שורה-ועמודה שכותב מחדש נוסחאות גיליון-עבודה רגילות. כך Formula1 עוברת מ-C2 ל-C26 בשני מהלכים במקום מקרה מיוחד כתוב-ביד אחד: תרגם את C2 קדימה ב-23 שורות כדי לקבל C25, כאילו הכלל תמיד התחיל שם, ואז תן להיסט הרגיל בשורה 25 לדחוף אותו הלאה ל-C26. כל מאפיין אחר, צבע מילוי, stop-if-true, האופרטור עצמו, נוסע יחד ללא שינוי לאובייקט הכלל החדש, כך ששני החצאים ממשיכים לצבוע תאים באותו צבע שתמיד צבעו
// 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 כיחידה בודדת במקום לשכפל אובייקט כלל חדש לכל חלק. ההבחנה מתיישרת עם טקסונומיית סוגי-הכלל במאמר יסודות העיצוב המותנה והטקסט העשיר: סרגלי-נתונים, סולמות-צבע, וסטי-אייקונים כבר עומדים בנפרד מכללי cellIs על ידי התעלמות מוחלטת ממאפיין ה-Style, ועכשיו מתברר שהם עומדים בנפרד מעיגון-מחדש לפי-אזור מאותה סיבה בסיסית
למה עדיפויות כלל משתנות אחרי עריכה מבנית?
עדיפויות משתנות משום שכל שכפול מתחיל בהחזקת בדיוק אותו ערך עדיפות שהכלל שהוא פוצל ממנו החזיק, ו-HotXLS מריצה מעבר נורמליזציה אחר כך שפותר את הכפילויות שנוצרו לסידור נקי, חסר-פערים, במקום להשאיר שני כללים קשורים באותו דירוג. פונקציה פנימית שנייה, XlsxNormalizeConditionalFormatPriorities, לוקחת את העדיפות הנוכחית של כל עיצוב מותנה, נופלת בחזרה למיקום הכלל הזה באוסף עבור כל כלל שאף פעם לא נקבע לו אחת במפורש, ממיינת את כל הרשימה בייצוב כך שתיקו שומרים על הסדר היחסי המקורי שלהם, וממספרת מחדש את התוצאה הממוינת לרצף צפוף 1, 2, 3 ללא פערים וללא חזרות. HotXLS מריצה אותה פעם אחת לפני שהיסט מתחיל, כך ששכפול מתחיל מבסיס נקי, ושוב אחרי כל פיצול וכל כלל שהתרוקן שמוסר, כך שהקובץ שנשמר לעולם לא מכיל שתי רשומות כלל שתובעות את אותה עדיפות. זה חשוב אם עקבת אחרי העצה במאמר יסודות העיצוב המותנה להשאיר פערים בין ערכי עדיפות כך שכלל מאוחר יותר יוכל להיכנס בלי למספר-מחדש את השאר: הפערים שורדים עד שעריכת שורה או עמודה הבאה נוגעת בגיליון העבודה הזה, ואז קורסים, משום שנורמליזציה מבטיחה רק ייחודיות וסדר יציב, לא שסכימת המספור המקורית שלך חוזרת ללא שינוי
כללי אימות נתונים גם מתפצלים, בלי עדיפות למספר מחדש
כללי אימות נתונים עוברים דרך אותה לוגיקת חלוקת-טווח כמו עיצובי cellIs ו-expression, ובניגוד לעיצוב מותנה, כל סוג אימות עובר בנתיב הזה באופן אחיד: ל-HotXLS אין משפחה לא-נוסחתית נפרדת עבור אימות נתונים באופן שסרגלי-נתונים וסטי-אייקונים הם עבור עיצוב מותנה, כך שכלל רשימה פשוטה או מספר-שלם מחולק על ידי אותה פונקציה בדיוק שמטפלת בנוסחה מותאמת-אישית יחסית. מה שנבדל הוא עדיפות: ECMA-376 לא נותן לאלמנט ה-dataValidation מאפיין priority בכלל, כך שאין שלב מספור-מחדש עבור אימותים כמו שיש עבור עיצובים מותנים. דמיין אימות נוסחה-מותאמת-אישית ששומר על כך שהסכום בפועל של כל שורה לא יחרוג מהתקציב שלה עצמה בעמודה שלידה
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 אחת לכל עמודה יתחיל לספור-אינדקס לא-נכון אחרי שהעריכה הראשונה נוגעת בו. יש תקרה קשיחה לכמה שזה יכול להתקדם: אם פיצול היה דוחף גיליון עבודה מעבר ל-65,534 כללי אימות-נתונים, HotXLS מעלה חריגה במקום לכתוב קובץ ש-Excel היה דוחה בשקט, וזו הספרייה מסרבת לייצר חוברת עבודה פגומה ולא מגבלה ששימוש רגיל צפוי להגיע אליה
מה לבדוק אחרי הכנסה או מחיקה המונית
שני הדברים ששווה לאמת אחרי שסקריפט מריץ אצווה של עריכות שורה או עמודה על גיליון מלא עיצובים מותנים ואימותים הם ספירת הכלל הכוללת וסדר העדיפות, שכן שניהם יכולים לסחוף בדרכים קלות-לפספוס בסקירת קוד וברורות ברגע שמישהו פותח Manage Rules ב-Excel. עריכה אחת לעיתים רחוקות עושה הרבה נזק: הכנסה בודדת באמצע כלל cellIs אחד מייצרת לכל היותר שני אובייקטי כלל במקום אחד. הסיכון מצטבר כאשר שגרת יצירת-דוח מכניסה שורות אחת בכל פעם בלולאה על גיליון שכבר נושא כמה כללים מעוגני-נוסחה: כל מעבר יכול לפצל מחדש כללים שמעבר קודם כבר פיצל, וחמישה כללי cellIs מקוריים יכולים להסתיים כמה פעמים יותר מזה כשברי ערך-נמוך שמכסים פיסות של הטווח המקורי. אצוות של עריכות מבניות, הכנסת כל הבלוק החדש בקריאה אחת במקום שורה אחת בכל פעם, שומרת את ספירת הכלל קשורה למספר העוגנים הבדלים באמת ולא למספר העריכות שבוצעו
חלוקת כלל ונורמליזציית עדיפות נשלחות כהתנהגות תקנית של מנוע ה-XLSX ברכיב ה-Excel Delphi של HotXLS עבור Delphi ו-C++Builder; דף המוצר נושא את מסמך ה-API המלא לעריכת גיליון עבודה, כולל פונקציות העיצוב המותנה ואימות הנתונים המתוארות כאן