מנוע נוסחאות HotXLS ופונקציות מותאמות ב-Delphi

ספריית גיליונות אלקטרוניים שרק מאחסנת מחרוזות נוסחה וספרייה עם מנוע נוסחאות עובד הן שני מוצרים שונים שנראים זהים עד הרגע שאתה מבקש מאחד מהם מספר. רוב קוד הגיליונות האלקטרוניים ב-Delphi לעולם לא מבחין בפער, כי Excel מצפה אותו: כתוב SUM(B2:B501) לתא, שמור, ו-Excel מחשב מחדש את הסך ברגע שאדם פותח את הקובץ. הוצא את האדם מהלולאה, הרץ את אותה חוברת עבודה דרך pipeline שרת שמייצא ישירות ל-CSV, וההבדל מפסיק להיות אקדמי. ה-CSV נושא את הטקסט הליטרלי =SUM(B2:B501) היכן שמספר היה צריך להיות, כי בשום שלב שום דבר לא העריך באמת את הנוסחה

זה הקו ש-HotXLS יושב בצד הנכון שלו. הוא מתייחס לנוסחה כפי שפורמטי הקובץ עושים, כטקסט מאוחסן בתוספת תוצאה מטמון אופציונלית, כך שייצוא CSV חשוף משחזר את המתכון במקום את המנה. אך הוא גם נושא מנוע חישוב שאפשר לקרוא לו ישירות, אותו מנוע הן ב-facade ה-XLS והן ב-XLSX, בתוספת hook לפתרון שמות פונקציות שהמנוע מעולם לא שמע עליהם. HotXLS היא ספריית Object Pascal טבעית שקוראת וכותבת XLS ו-XLSX מ-Delphi ו-C++Builder ללא אוטומציה של Excel, והחצי החישובי שלה הוא מה שהופך נוסחאות מאוחסנות בחזרה לערכים לפי דרישה

נוסחאות מאוחסנות, לא מוערכות בלהיטות

כתיבת נוסחה לתא לא מחשבת דבר. בזמן השמירה חוברת העבודה רושמת את טקסט הנוסחה. בצד ה-XLS היא גם רושמת דגלים הנשלטים על ידי RecalcOnSave, שברירת המחדל שלו True ואומר ל-Excel לחשב מחדש בפתיחה. המודל הזה נכון לקבצים המיועדים ל-Excel ושגוי ל-pipelines שצורכים ערכי תאים ישירות, בין אם זה ייצוא CSV, ייצוא HTML, או הקוד שלך שקורא תאים בחזרה. עבור אלה, הערך במפורש עם Calculate. הוא קיים בארבע נקודות כניסה: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook ו-TXLSXWorksheet כולם חושפים function Calculate(const Formula: WideString): Variant

// הערכה בתוך-תהליך, ואז שילוח הערך במקום המתכון
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // ה-CSV נושא כעת את המספר

הביטוי המוסר ל-Calculate הוא טקסט נוסחת Excel רגיל. הפניות בין-גיליונות, שמות מוגדרים ופונקציות מקוננות כולם פותרים כנגד חוברת העבודה הנוכחית בזיכרון, מה שהופך את הקריאה לשימושית הרבה מעבר לתיקון ייצוא CSV. התייחס אליה כמנגנון אסרטציה. מחולל שרק כתב חמש מאות שורות פירוט יכול לבקש מחוברת העבודה את הסך-הכל של עצמה ולהשוות אותו כנגד הסכום שחישב באופן עצמאי ב-Pascal, תופס שגיאת טווח off-by-one לפני שהמבקר של הלקוח עושה זאת

זה גם ממסגר את אסטרטגיית הבדיקה הנכונה לפלט עתיר-נוסחאות. Excel נשאר מימוש הייחוס של שפת הנוסחאות, כך שעבור החופן של נוסחאות שנושאות השלכות עסקיות, שמור קובץ fixture מאושר שערכיו הצפויים הופקו על ידי Excel עצמו, וגרום ל-pipeline הבנייה להעריך את הנוסחאות של חוברת העבודה המחוללת עם Calculate כנגד אותם fixtures. הבדלים אז עולים כבדיקות נכשלות ב-Delphi במקום כפערים שהתגלו על ידי לקוח שמשווה שני דוחות

הוספת פונקציות עסקיות עם OnUserFunction

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

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args מגיע כמערך Variant
    Handled := True;
  end;
end;

// חיווט ושימוש
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

שלושה פרטים ראויים לתשומת לב. ראשית, קבע Handled := True רק כאשר הכרת באמת בשם. השארתו False מאפשרת למנוע להמשיך את טיפול הפונקציה-הבלתי-מוכרת הרגיל שלו, כך ש-handler יחיד יכול לשרת מספר חוברות עבודה בלי לתבוע כל מה שעובר. שנית, השווה שמות ללא רגישות-לרישיות עם SameText, שכן מחברי נוסחאות מקלידים discount( ו-DISCOUNT( לסירוגין. שלישית, ארגומנטים מגיעים מוערכים-מראש: DISCOUNT(A1) מוסר לך את הערך של A1, לא את ההפניה, כך שפונקציה לא יכולה להגיד מאין הגיעו קלטיה. הנקודה האחרונה מכינה את המגבלה שהסעיף הבא עוסק בה

התייחס לגוף ה-handler באותה הגנתיות כמו כל נקודת כניסה חיצונית. מערך Args משקף את מה שמחבר הנוסחה הקליד, כך שאמת את ספירת הארגומנטים והסוגים לפני אינדקס לתוכו, והחלט מראש מה קריאה לא-תקינה מחזירה: ערך שגיאת Variant, או חריג שהועלה. הבחירה חשובה כי חריג שנזרק בתוך ה-handler מתפשט החוצה דרך קריאת Calculate שהפעילה את ההערכה. זה מקובל במחולל מבוקר-היטב וגס בשירות המעריך חוברות עבודה שנכתבו-על-ידי-משתמש, שבו נוסחה רעה אחת תפיל את הבקשה. בהגדרה זו, תפוס בתוך ה-handler והחזר sentinel שזרימת-העבודה המקיפה יכולה לזהות וללוג

פונקציות מבוססות-מיקום זקוקות ל-variant ה-Ex

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

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // אותה נוסחה מניבה שיעור שונה על כל גיליון אזורי
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext נושא SheetIndex, Row ו-Col של התא המעריך. אם התוצאה של פונקציה תלויה במיקום שלה אפילו מעט, חווט את אירוע ה-Ex מההתחלה. התקנת context לתוך handler ששלושים נוסחאות כבר קוראות לו מבולגנת הרבה יותר מבחירת החתימה הנכונה ביום הראשון, ושני האירועים דומים כל כך שיש מעט סיבה להתחיל עם הצר יותר

פונקציות מותאמות לא נוסעות ל-Excel

פונקציה מותאמת חיה לחלוטין בתוך התהליך שלך. השם DISCOUNT משמעותי רק בזמן שקוד ה-Delphi שלך ו-handler האירוע שלו רצים. פתח את הקובץ השמור ב-Excel ו-DISCOUNT הוא רק שם לא-מוכר; התא מראה #NAME? אלא אם פונקציית VBA תואמת או add-in קיימים במקרה על מכונת המשתמש. זו עובדת התכן שמפרידה demo ממוצר הניתן-למשלוח, והיא כופה בחירה שעליך לעשות בכוונה ולא לגלות מאוחר יותר

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

יש יתרון שקט לחוזה ערכים-בלבד: הוא מגן על קניין רוחני. כלל תמחור שהוערך בתהליך ה-Delphi שלך ונשלח כמספר לא יכול להיות מהונדס-לאחור מחוברת העבודה כפי שנוסחה גלויה יכולה, ומשתמש לא יכול לשבור אותו על ידי עריכת תא ביניים. מחוללי חשבוניות, דפי עמלה וכרטיסי שיעור כמעט תמיד שייכים למחנה זה. המקרה שזקוק באמת לנוסחאות חיות הוא מודל what-if אינטראקטיבי, שבו הלקוח צפוי לשנות קלטים ולצפות בסכומים זזים, ואלה חייבים להיבנות מאוצר המילים של Excel בתוספת שמות מוגדרים

מצבי חישוב, איטרציה ו-R1C1: חוגות facade ה-XLS

facade ה-XLS חושף את הגדרות החישוב ברמת BIFF ש-Excel קורא מהקובץ. CalculationMode מקבל xlCalcManual, xlCalcAutomatic (ברירת המחדל) או xlCalcAutomaticExceptTables, והוא קובע כיצד Excel מתנהג ברגע שהקובץ פתוח. חוברת עבודה של מודל עם אלפי נוסחאות לעיתים קרובות ידידותית יותר כשהיא נמסרת במצב ידני, כך שהנמען מחליט מתי סופת החישוב-מחדש קורת. EnableIteration (ברירת מחדל False), יחד עם MaxIterations (ברירת מחדל 100) ו-MaxIterationChange (ברירת מחדל 0.001), פותח את ההפניות המעגליות המכוונות מסוג התכנסות-איטרטיבית שעולות בכמה מודלים פיננסיים. ReferenceStyle מחליף בין תצוגת A1 ו-R1C1, ו-UseFullPrecision משקף את האפשרות precision-as-displayed של Excel

מאפיינים אלה חיים על facade ה-XLS כי הם ממפים לרשומות BIFF; בעת יצירת ‎.xlsx‎, תכנן נוסחאות כך שלא יהיו תלויות בהגדרות איטרטיביות, או חשב את הערכים המתכנסים ב-Delphi וכתוב תוצאות

נוסחאות מערך: נקודת הכניסה הציבורית היא XLSX

נוסחאות מערך מסוג CSE מורשת נוצרות דרך TXLSXRange.SetArrayFormula:

// נוסחת מערך אחת המשתרעת על פני A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

המתודה המקבילה קיימת בהיררכיית המחלקות XLS אך יושבת בסעיף פרטי, כך שאין דרך נתמכת לחבר נוסחאות מערך חדשות לתוך קובצי ‎.xls‎. נוסחאות קיימות בקבצים פתוחים עוברות round-trip שלם; מה שאינך יכול לעשות הוא ליצור אותן. הכלל שנובע פשוט מספיק: כאשר סמנטיקת מערך היא חלק מהדרישה, מתג אל ‎.xlsx‎. אם תוצר ‎.xls‎ מורשת אכן זקוק להתנהגות מערך, הנתיב הפרגמטי הוא לחשב את תוצאת המערך ב-Delphi ולכתוב את הערכים הבודדים לתאים

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