הערכת עיצוב מותנה באקסל בדלפי עם HotXLS

HotXLS הוא רכיב גיליון אלקטרוני ילידי עבור דלפי ו-C++Builder, ומאז גרסה 2.209.0 הוא יכול לענות על השאלה שאקסל בדרך כלל שומרת לעצמה: עבור התא המדויק הזה, אילו כללי עיצוב מותנה מופעלים, ולאיזה מילוי, גופן, פס נתונים או אייקון הם נפתרים. זו התשובה שאתה צריך ברגע שהפלט שלך הוא דוח HTML, PDF, או רשת שאתה מצייר בעצמך

זו בעיה שונה מיצירת כללים. שתי הערות קודמות מכסות את צד היצירה: עיצוב מותנה וסגנונות טקסט עשיר עוסקת בצירוף כללים ופורמטים דיפרנציאליים לטווח, וחלוקת עיצובים מותנים מעוגנים עוסקת במה שקורה לטווח כלל כששורות ועמודות מוכנסות או נמחקות. שתיהן מבניות. זו עוסקת בסמנטיקה: בהינתן חוברת עבודה שכבר נושאת כללים, חשב את ההדגשה

למה פורמט הקובץ לא אומר לך אילו תאים נדלקים

התשובה הקצרה היא ש-ECMA-376 ו-ISO 29500-1 מגדירים אחסון, לא הערכה. אלמנט conditionalFormatting (§18.3.1.18) נושא sqref ורשימה של ילדי cfRule (§18.3.1.10), וכל כלל נושא type, operator אופציונלי, priority, דגל stopIfTrue, ילד formula אחד או שניים, ועבור המשפחות הוויזואליות סט של ספים cfvo. כל אחד מאלה מתאר בנאמנות מה המשתמש הגדיר, ואף אחד מהם אינו אלגוריתם. עבור חצי מסוגי הכלל הפער הזה לא משנה: cellIs עם operator="greaterThan" פירושו גדול מ-, ו-containsText פירושו שתת-המחרוזת נוכחת. הפער נפתח על המשפחות המצטברות. כלל top10 עם rank="10" ו-percent="1" על 27 תאים מספריים מאוכלסים מדגיש כמה תאים? שתיים נקודה שבע אינו מספר. עיגול, floor, או ceiling — התקן שותק, ובחירה שגויה פירושה שה-PDF שלך חולק על חוברת העבודה שהלקוח פתוחה לצידה

כללים לתא-בודד והיכן TCondFormatRule.Evaluate עוצר

HotXLS לקחה את החצי הזול קודם. TCondFormatRule.Evaluate ב-lxCondFormat.pas, שנוסף ב-2.199.0, עונה האם כלל אחד מופעל עבור תא אחד בלי לדעת כלום על שאר הטווח. היא מטפלת בשמונה אופרטורי ההשוואה של BIFF מאחורי cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), כללי expression חופשיים שמוערכים בתא כך שהפניות יחסיות מתבססות מחדש נכון, ארבעת התחזיות של הטקסט, ותחזיות ה-blanks וה-errors. הספים מגיעים מ-FFormula1 ו-FFormula2 שנפתרים דרך TXLSCalculator.GetRangeValue במיקום התא, וגבולות הפוכים מוחלפים ולא נדחים

var
  I: Integer;
  Rule: TCondFormatRule;
  Value: Variant;
begin
  Value := Sheet.Cells[Row, Col].Value;
  for I := 0 to CondFormat.RuleCount - 1 do
  begin
    Rule := CondFormat.Rule(I);
    // Single-cell verdict only. Aggregate and visual kinds answer False.
    if Rule.Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      ApplyHighlight(Row, Col, Rule.Style);
  end;
end;

החלק הכן בשיטה הזו הוא מה שהיא מסרבת לנחש. top10, aboveAverage, belowAverage, duplicateValues ו-uniqueValues מחזירים False, לא כי הם קשים אלא כי הם בלתי-ניתנים-להכרעה מתא בודד — כל אחד מהם צריך סטטיסטיקה על פני התחום כולו. ארבע המשפחות הוויזואליות, dataBar, colorScale2, colorScale3 ו-iconSet, מחזירות False מסיבה שונה: הן אף פעם לא מפיקות בוליאני כלל, הן מפיקות פאילוד רינדור, וסוג החזרה בוליאני הוא הצורה השגויה עבורן

כיצד מעריך ברמת-גיליון נמנע מלסרוק מחדש את הגיליון?

על ידי חישוב כל כמות משותפת פעם אחת, בבנייה, ולעולם לא שוב. TXLSXConditionalFormatEvaluator ב-lxHandleX.pas הוא תמונת מצב בלתי-משתנה לגיליון עבודה אחד, שנבנה דרך TXLSXWorksheet.CreateConditionalFormatEvaluator, וכל העיצוב שלו הוא הגנה כנגד המימוש הנאיבי שבו כל תא מצוייר מפעיל סריקת טווח-מלא

ארבעה דברים קורים בבנאי. כל sqref רב-אזורי מובחן מנותח בדיוק פעם אחת לתוך TXlsxCfRangeSnapshot, כך שעשרה כללים שחולקים טווח אחד חולקים ניתוח אחד ומעבר סטטיסטיקה אחד. המעבר הזה זורם ממוצע, סטיית אוכלוסייה, מינימום ומקסימום על פני התאים המאוכלסים בהילוך יחיד, ושומר מערך מספרי מסודר רק כשכלל Top/Bottom או אחוזון באמת צריך סטטיסטיקות סדר. מפתחות כפילות וייחודיות נבנים בטוחי-יוניקוד וממוינים באצווה פעם אחת במקום לכל חיפוש. אז ציר השורות נחתך לרצועות בכל גבול אזור, כך ש-EvaluateCell מחפש-בינארית רצועה ומבקר רק בכללים שהטווחים שלהם יכולים אפשרית להגיע לשורה ההיא

הרביעי הוא זה שהכי חשוב בקנה מידה. נוסחת כלל יחסית כמו =A1>AVERAGE($A$1:$A$100) אומרת משהו שונה בכל תא של התחום, והמימוש הברור מהדר עץ תחביר טרי לכל תא. TXlsxCfRulePlan מהדר אותה פעם אחת ומעריך מחדש את אותו העץ דרך היסטי קואורדינטות הפיכים, מה שמשמר את התנהגות העוגן של אקסל ללא הקצאת עץ-תחביר לכל תא. כללים אז מסודרים בשכבות לפי priority, והתאמה בכלל ש-StopIfTrue שלו מוגדר שוברת את הלולאה, בדיוק כפי שאקסל מקצר-מעגל

var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
begin
  Evaluator := Sheet.CreateConditionalFormatEvaluator;
  try
    if Evaluator.EvaluateCell(Row, Col, Res) then
    begin
      if Res.HasFillColor then
        Canvas.Brush.Color := TColor(Res.FillColor);
      if Res.HasIcon then
        // IconIndex is zero-based inside Res.IconSetType
        DrawIcon(Res.IconSetType, Res.IconIndex, Res.IconCount);
      if Res.HasDataBar then
        // DataBarAxis and DataBarEnd are normalised to 0..1
        DrawBar(Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
      if not Res.ShowCellValue then
        Exit;  // showValue="0" on the rule hides the number
    end;
  finally
    Evaluator.Free;
  end;
end;

איך אקסל בעצם מעגל כלל Top 10 percent?

הוא עושה floor, עם מינימום של אחד, והוא כולל תיקו בנקודת החיתוך. זה לא כתוב בשום מקום ב-ISO 29500-1 — זה נעץ על ידי בדיקת אקסל 16 עם חוברות עבודה שנבנו ביד וקריאה חזרה אילו תאים היישום הדגיש. HotXLS מיישמת בדיוק את זה: ספירת הדרגה היא Floor(Count * Min(Rank, 100) / 100), מועלית ל-1 כשהיא נוחתת באפס, נחתכת לספירה המאוכלסת, וערך החיתוך אז מושווה עם >= כך שכל תא שווה לגבול מודגש גם כשזה חורג מהספירה המבוקשת. עשרים ושבעה ערכים וכלל 10 אחוז מדגישים שני תאים, בתוספת כל תא נוסף בתיקו עם השני

כללי above-average הסתירו עמימות שנייה: aboveAverage עם stdDev="1" בוחר תאים סטיית תקן אחת מעל הממוצע, אבל סטיית מדגם ואוכלוסייה נבדלות בתיקון בסל וחלוקות באורח נראה-לעין בטווחים קטנים, וזה בדיוק המקום שבו עיצוב מותנה משמש. אקסל 16 משתמש בסטיית אוכלוסייה, ו-HotXLS מתאימה, כאשר דגל equalAverage הופך את ההשוואה הקפדנית כוללת רק כשאין רצועת סטייה בפעולה. כללי duplicate ו-unique הפעילו את זהות המפתח במקום זאת. אם תא אחד מחזיק את המספר 100 ותא אחר מחזיק את הטקסט "100", אקסל מתייחס אליהם כאל אותו מפתח כפילות, כך ש-HotXLS מנרמלת טקסט מספרי לתוך מרחב המפתח המספרי במקום להשוות מחרוזות גולמיות. תאים ריקים הם המקרה המנוגד: תא ריק אמיתי משתתף בספירת הטווח אבל לא מוצג בעצמו, כך שהתאים הריקים בעמודה לא כולם נדלקים ככפילויות אחד של השני

סקלות צבע וסטי אייקונים: אינטרפולציה וכללי גבול

המשפחות הוויזואליות נפתרות למספרים מוכני-רינדור ולא לבוליאנים, וההתנהגות בקצה שלהן נעצה באותה דרך. עבור סקלת צבע עם ספים מספריים מפורשים, HotXLS מהדקת את שבר המיקום למרווח הסגור אפס עד אחד, ואז מבצעת אינטרפולציה לכל ערוץ עם קטיעה במקום עיגול — ערך מתחת למינימום מקבל את צבע המינימום במקום צבע אקסטרפולטיבי, סקלה בת שלושה ספים בוחרת את הזוג שלה בהשוואה לסף האמצעי, וסקלה מנוונת ששני קצותיה נושאים אותו סף מתכווצת לצבע העליון במקום לחלק באפס. סטי אייקונים היו זקוקים לזהירות מהסוג ההפוך, כי כל cfvo אחרי הראשון נושא את קפדנות ההשוואה שלו עצמה: HotXLS קוראת את ThresholdEqualsInclude לכל סף ומחילה >= או > בהתאם, הולכת כלפי מעלה כך שהסף הגבוה ביותר שמסופק מנצח באינדקס האייקון. סט הפוך הופך את האינדקס הנפתר במקום את הספים, override לכל-אייקון יכול למשוך גליף ממשפחה אחרת, וכל סף לא-תקין מפסיק את הכלל במקום להפיק אייקון שגוי-אך-סביר-למראה

הזנת רשת, ייצוא HTML ו-PDF מתוצאה אחת

מכיוון ש-EvaluateCell מחזירה TXLSXCfCellResult שנפתר במלואו — צבע מילוי וגופן דיפרנציאלי עם גוון ערכת נושא כבר מוחל, בולד, נטוי, קו-תחתי, מזהה פורמט מספר, היקפי פס כיווניים חיוביים ושליליים, מיקום ציר, משפחת אייקון ואינדקס — כל צרכן קורא את אותה רשומה ואף אחד מהם לא צריך להבין פנימיות כללים. HotXLS משתמשת בנתיב אחד הזה עבור ייצוא HTML, ייצוא PDF והצופה האינטראקטיבי, וזו הדרך המעשית היחידה למנוע משלושה מרנדרים להיסחף. גרסה 2.210.0 חיברה אותו ל-TXLSWorkbookViewer, שממטמנת מעריך מוכן אחד לכל גיליון עבודה פעיל ומשתמשת בו שוב על פני גלילה, בחירה וציור-מחדש, משחררת אותו כשחוברת העבודה או גיליון העבודה משתנים — בנייה מחדש של תמונת המצב בכל Paint הייתה מבטלת את כל עיצוב הבנייה-בזמן-בניה. המטמון הזה הוא גם למה TXLSWorkbookViewer.RefreshConditionalFormats קיימת: תמונת המצב בלתי-משתנה, כך שאם אתה משנה את חוברת העבודה המחוברת במקום, הסטטיסטיקות המצטברות והספים הנפתרים מיושנים עד שתקרא לה

// Editing behind a live viewer: the cached snapshot must be invalidated.
Sheet := Viewer.XlsxWorkbook.Sheets[1];
Sheet.Cells[5, 2].Value := 4200;         // changes mean, min, max, ranking
Viewer.RefreshConditionalFormats;        // drop evaluator, repaint

מה המעריך לא יעשה בשבילך

שלושה גבולות ראויים לציון ברור. TCondFormatRule.Evaluate הקלאסי לתא-בודד ו-TXLSXConditionalFormatEvaluator ברמת-גיליון הם משטחים שונים עם יכולות שונות, ותא-הבודד מסרב בכוונה למשפחות המצטברות והוויזואליות במקום לקרב אותן — אם אתה זקוק ל-Top/Bottom או לסקלת צבע, בנה את המעריך. תקופות זמן יחסיות תלויות בשעון המכונה בזמן ההערכה, כך שכלל timePeriod מתרנדר אחרת ב-PDF שנוצר היום ואחד שנוצר בשבוע הבא, וזו התנהגות נכונה ועדיין קריאת תמיכה מחכה לקרות אם הארכיון שלך אמור להיות יציב-בייט. השלישי דקדוקי ולא טכני: דקדוק נוסחת העיצוב-המותנה אוסר הפניות טבלה מובנות, כך שכלל לא יכול לפנות לעמודת טבלה לפי שם כפי שנוסחת גיליון-עבודה יכולה, וזו מגבלה של הפורמט ולא של המימוש

אם אתה בונה פלט דוח, pipeline ייצוא, או רשת מותאמת אישית שצריכה להסכים עם אקסל תא-לתא, אותה תוצאה נפתרת מפעילה גם את רשת ה-VCL הגיליון-אלקטרוני המותאמת אישית המתוארת במקום אחר בבלוג הזה. תיעוד API מלא, מודל הכלל והורדות ניסיון עבור רכיב הגיליון האלקטרוני HotXLS Delphi זמינים בעמוד המוצר