קריאת ערכי נוסחה שמורים של Excel ב־Delphi בלי חישוב מחדש

HotXLS, ספריית ה־Excel המקורית ל־Delphi ו־C++Builder, קוראת את הערך ש־Excel כבר אחסן לצד נוסחה דרך TryGetCachedFormulaValue ו־IXLSFormulaCacheReader. אף נקודת כניסה לא קוראת למחשבון, לא מפרקת אסימוני נוסחה, לא מעדכנת מצב מלוכלך ולא כותבת דבר חזרה למודל, כך שחוברת שאתם רק קוראים נשארת בדיוק כפי שפתחתם אותה

התרחיש שמניע את זה משעמם ונפוץ להחריד. עבודה לילית פותחת כמה מאות חוברות שמישהו אחר יצר, מוציאה עמודת סך הכול אחת מכל אחת ודוחפת את המספרים למחסן נתונים. הסך הכול כבר יושב בקבצים — Excel חישב אותם ושמר אותם. ואף על פי כן, ברגע שהעבודה שואלת תא נוסחה מה הערך שלו, ספרייה שיש לה רק תשובה אחת לשאלה הזאת בונה גרף תלויות ומעריכה את כל הגיליון, ועבודה שאמורה להיות חסומת קלט/פלט הופכת למיקרו־בנצ'מרק של חישוב

למה קריאת תא נוסחה עולה בחישוב מחדש מלא?

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

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

שלוש עובדות אורתוגונליות על תא אחד

המסקנה קודם: ערך נוסחה שמור נושא שלוש עובדות בלתי תלויות, וצמצום שלהן ל־Variant אחד מאבד מידע שאתם צריכים. TXLSFormulaCacheInfo מפריד ביניהן כ־State, Kind ו־Value. TXLSFormulaCacheState מתעד את המקור על פני חמישה מקרים — xlfcsNotFormula, xlfcsMissing, xlfcsLoaded, xlfcsCalculated ו־xlfcsInvalidated — בעוד TXLSFormulaCacheValueKind מסווג את המטען כ־xlfcvBlank, xlfcvNumber, xlfcvDateTime, xlfcvString, xlfcvBoolean או xlfcvError. ההפרדה הזאת היא מה שמאפשר לדווח על נוכחות ביושר: רווח שמור, מחרוזת ריקה שמורה, False שמור, אפס שמור ושגיאה שמורה כולם ערכים אמיתיים, ולכן נוכחות לעולם אינה נגזרת מ־VarIsEmpty או VarIsNull. TryGetCachedFormulaValue מחזיר True רק עבור xlfcsLoaded ו־xlfcsCalculated, ועדיין ממלא מצב שניתן לאבחון כשהוא מחזיר False

הרשומה TXLSFormulaCacheInfo של HotXLS מפרידה שלוש עובדות אורתוגונליות על תא נוסחה אחד: ה־State של המקור על פני חמישה מקרים, ה־Kind של המטען על פני שישה, וה־Value שהוא Variant, כך שרווח שמור או False שמור אינם נחשבים בטעות למטמון נעדר — המקור, סוג המטען וערך המטען נשארים מופרדים — זאת הדרך היחידה שבה רווח שמור, אפס, מחרוזת ריקה או שגיאה יכולים להדווח כערך האמיתי שהם

var
  Book: TXLSXWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-model.xlsx');
    // SheetIndex, Row ו־Col הם כולם בבסיס 1 כאן
    if Book.TryGetCachedFormulaValue(1, 12, 5, Info) then
      Writeln('cached value: ', VarToStr(Info.Value))
    else
      Writeln('no usable cache, state ordinal ', Ord(Info.State));
  finally
    Book.Free;
  end;
end;

למה הערך השמור חסר?

יש בדיוק ארבע סיבות שבגינן TryGetCachedFormulaValue מחזיר False, והמצב אומר לכם איזו מהן חלה. xlfcsNotFormula אומר שהתא מכיל ליטרלי או כלום בכלל, וגם קואורדינטות שמחוץ לטווח מתקפלות לאותה תשובה. xlfcsMissing אומר שהתא אכן נוסחה אבל המייצר לא אחסן עבורה מטען ערך — תוצאה נפוצה כשמחולל כותב נוסחאות ומשאיר ל־Excel למלא תוצאות בפתיחה הראשונה. xlfcsInvalidated אומר שטקסט הנוסחה הוחלף אחרי הטעינה, כך שהערך שהיה שם מתאר ביטוי שכבר אינו קיים. xlfcsCalculated, לעומת זאת, הוא מקרה הצלחה: הוא מסמן ערך שהקוד שלכם או מעריך HotXLS הפיקו במהלך ההפעלה הזאת, בניגוד ל־xlfcsLoaded, שהגיע מהקובץ

יושרה לגבי מטמון חסר חשובה יותר מטיוח שלו. HotXLS מסרב להמציא ערך, ובשמירה הוא קפדני לא פחות — רק xlfcsLoaded ו־xlfcsCalculated פולטים ערך שמור, בעוד xlfcsMissing ו־xlfcsInvalidated כותבים את הנוסחה לבדה במקום להקפיא מספר מיושן בתוך הקובץ. נותרות לכם שלוש תגובות שפויות בצינור: לדלג על השורה ולתעד את הפער, לחשב מחדש בכוונה תחילה את אותה חוברת אחת ולקבל את העלות, או להעריך וליישב. אם המספר שהוערך חולק על מה שהאפליקציה המייצרת הייתה כותבת, עוקב הערכת הנוסחאות הוא הכלי לגלות היכן שני החישובים מתפצלים, במקום לנחש מהתוצאה

קורא אחד על פני המנוע הקלאסי, OOXML ו־ODF

צינור לא אמור להתעניין אם הקובץ שפתח זה עתה הוא BIFF, OOXML או ODF. IXLSFormulaCacheReader היא נקודת הכניסה היחידה לקריאה בלבד עבור שלושתם: גם TXLSWorkbook.CreateFormulaCacheReader וגם TXLSXWorkbook.CreateFormulaCacheReader מחזירים מתאם קל משקל מעל חיפוש התאים הדליל שכל מנוע כבר משתמש בו, עם קואורדינטות גיליון, שורה ועמודה זהות בבסיס 1. מחלקות החוברת במכוון אינן מממשות את הממשק בעצמן — הפניית ממשק לחוברת הייתה משנה את סמנטיקת הבעלות שלה ומאפשרת למתקשרים לחמוק על פני חוזה משך החיים. במקום זאת, הריסת החוברת מנקה את המצביע הגולמי בתוך אותו חוזה, וכל קורא שהקוד שלכם עדיין מחזיק זורק EXLSFormulaCacheReaderInvalidated בשאילתה הבאה שלו במקום לפנות לזיכרון משוחרר. זו בדיקת חיים שנכשלת מהר, לא ערובה למקביליות

var
  Reader: IXLSFormulaCacheReader;
  Info: TXLSFormulaCacheInfo;
  Row, Missing, Errors: Integer;
  Total: Double;
begin
  Reader := Book.CreateFormulaCacheReader;
  Total := 0;
  Missing := 0;
  Errors := 0;
  for Row := 2 to LastRow do
    if Reader.TryGetCachedFormulaValue(1, Row, 7, Info) then
    begin
      case Info.Kind of
        xlfcvNumber: Total := Total + Double(Info.Value);
        xlfcvError:  Inc(Errors);
      end;
    end
    else if Info.State = xlfcsMissing then
      Inc(Missing);
  // לא רץ מחשבון, לא זז דגל מלוכלך, Book נשאר ללא שינוי
end;

היכן הבתים השמורים נמצאים בפועל

בקובצי .xls קלאסיים המטמון הוא השדה FormulaValue של רשומת Formula, שמונה בתים המתוארים ב־[MS-XLS] §2.5.133. כשהמילה הגבוהה שווה ל־$FFFF המטען אינו double של IEEE 754 אלא variant מתויג, ואת הפריסה קל לשבור בצורה עדינה: סוג ה־variant יושב ב־val[0] והמטען הבוליאני או ה־BErr יושב ב־val[2], ואילו val[1] אינו מוגדר. HotXLS קרא בעבר את המטען מ־val[1] — סוג הסטייה־באחד שמתגלה רק בקובצים הספציפיים ששומרים במטמון בוליאני או שגיאה במקום מספר. הקורא וכותב הנוסחאות המשותפות מסכימים כעת על אותם היסטים, כך ש־TRUE שמור שורד טעינה ושמירה בשלמותו במקום להתדרדר לרעש

שדה ה־FormulaValue בן שמונת הבתים של רשומת Formula ב־XLS קלאסי כפי ש־HotXLS קורא אותו: double של IEEE 754 אלא אם המילה הגבוהה שווה FFFF, ואז סוג ה־variant יושב ב־val אפס והמטען הבוליאני או השגיאה ב־val שתיים — כשהמילה הגבוהה היא FFFF השדה הוא variant מתויג, והמטען יושב ב־val[2] כש־val[1] אינו מוגדר — בדיוק הבית שהקורא היה נוטל בעבר

נאמנות הטיפוסים בפורמטי החבילה היא בעיה נפרדת עם מלכודת משלה. ב־OOXML הערך השמור תלוי על אלמנט ה־c כ־<v>, כשהתכונה t קובעת את הטיפוס לפי ECMA-376 חלק 1 §18.3.1.4. HotXLS קורא t="e" ישירות לתוך Variant מסוג varError וממפה אותו חזרה לטקסט השגיאה התקני בשמירה, כך ששגיאות לעולם אינן מתחזות למספרים שלמים רגילים — אבל ה־RTL של Delphi לא יסייע לכם כאן, כי VarAsType(Integer, varError) זורק חריגת המרה. הבנייה שעובדת קובעת את TVarData.VType ואת TVarData.VError ישירות. תאריכים מקפידים על אותה משמעת בכיוון ההפוך: t="d" וטיפוס ערך התאריך של ODF הן הצהרות טיפוס מפורשות והופכות ל־varDate, בעוד שמטמון מספרי של BIFF אינו נושא דגל תאריך כלל ולכן נשאר Double. HotXLS לעולם אינו מנחש תאריך מפורמט המספר של התא, כי פורמט המספר הוא תצוגה והמטמון הוא נתונים. ODF מוסיף מקרה נוסף שכדאי להכיר — office:value-type="void" מבטא מטמון שנוכח אך אינו נושא ערך, ומכיוון של־ODF אין טיפוס ערך של שגיאה, טקסט שנראה כשגיאה נשמר כטקסט במקום להתקדם לשגיאה

function DescribeCache(const Info: TXLSFormulaCacheInfo): string;
begin
  case Info.State of
    xlfcsNotFormula:  Result := 'not a formula cell';
    xlfcsMissing:     Result := 'formula stored with no cached value';
    xlfcsInvalidated: Result := 'formula replaced since load';
  else
    case Info.Kind of
      xlfcvError:    Result := 'error code ' + IntToStr(TVarData(Info.Value).VError);
      xlfcvDateTime: Result := DateTimeToStr(VarToDateTime(Info.Value));
      xlfcvBoolean:  Result := BoolToStr(Info.Value, True);
      xlfcvNumber:   Result := FloatToStr(Double(Info.Value));
      xlfcvString:   Result := VarToStr(Info.Value);
    else
      Result := 'present but blank';
    end;
  end;
end;

האם נוסחאות משותפות משתפות את הערכים השמורים שלהן?

לא, וההנחה שהתשובה אחרת היא הדרך שבה סריקה מסיימת בדיווח על אותו מספר לעמודה שלמה. נוסחה משותפת ב־OOXML משתפת רק את ביטוי הנוסחה ואת אופטימיזציית האחסון; כל תא חבר עדיין רוכש משלו <v>. לכן HotXLS לעולם אינו מפיץ את מטמון תא השורש לתא עוקב שהגיע בלי ערך, ותא עוקב שנטען כ־xlfcsMissing עדיין מדווח xlfcsMissing אחרי שמירה ופתיחה מחדש. אם אתם חוקרים כיצד הקבוצה נאחסנת ומורחבת מלכתחילה, המכניקה של התכונה si של הנוסחה המשותפת והרחבתה מכוסה בנפרד; לקריאת מטמון, הכלל מצטמצם לשורה אחת — שאלו כל תא, אל תסמכו על מה שלא שאלתם

תצוגת HotXLS של קבוצת נוסחה משותפת ב־OOXML שבה התכונה si משתפת רק את הביטוי ואת פריסת האחסון, בעוד כל תא חבר רוכש משלו ערך שמור, כך שתא עוקב שנטען בלי אחד כזה ממשיך לדווח xlfcsMissing — הקבוצה משתפת את הביטוי, לא את המספרים, ולכן המטמון של השורש לעולם אינו מופץ וחבר שהגיע בלי ערך ממשיך לדווח על הפער הזה

קריאת ערכים שמורים, הקורא המאוחד חוצה המנועים ומנוע החישוב מחדש שאתם יכולים לבחור לא להפעיל — כולם נכללים ב־HotXLS Delphi Spreadsheet Component התקני עבור Delphi ו־C++Builder, ללא תלות ב־Excel או בכל שרת אוטומציה של OLE; דף המוצר נושא את ההפניה המלאה ל־API עבור נקודות הכניסה של החוברת והקורא המוצגות כאן