להפסיק שמירות XLS שמחשבות נוסחאות בשקט בדלפי

‏HotXLS, ספריית הגיליון הילידית ל-Delphi ו-C++Builder, שומרת חוברת .xls קלאסית בפורמט BIFF8 בגישת cache first: TXLSWorksheet.WriteFormula מבקשת מ-TXLSWorkbook.TryGetCachedFormulaValue את הערך ש-Excel אחסנה לצד כל נוסחה, ורק כשאין מטמון כזה או שהוא בוטל היא קוראת למעריך. חוברת שפתחתם ולא נגעתם בה נשמרת עם אותם מספרים, ותוצאות טריות דורשות קריאה מפורשת אחת ל-Recalculate במקום להיות תופעת לוואי נסתרת של SaveAs

הבאג שאילץ את החוזה הזה לצוף היה קטן עד כדי מבוכה. קובץ קורפוס בשם nested-subtotals.xls מחזיק סכום כולל ב-R2C4 שערכו השמור הוא 37. פתחו אותו עם HotXLS, בקשו TryGetCachedFormulaValue לתא, קבלו 37. שמרו בלי לשנות אף תא, פתחו את העותק שנשמר, שאלו את אותה שאלה, קבלו 67. שום דבר ב-API לא התבקש לחשב משהו, ובכל זאת מספר בקובץ זז בדיוק ב-30 — ו-30 הוא במקרה הסכום של שני סכומי הביניים של הקבוצות, 10 ו-20, שיושבים בתוך הטווח שהסכום הכולל מכסה

למה שמירה של קובץ XLS משנה ערך של נוסחה?

שני פגמים בלתי תלויים היו חייבים להתיישר כדי ש-37 יהפוך ל-67, ותיקון של אחד מהם בלבד היה מסתיר את השני. הראשון היה מבני: הכותב הקלאסי חישב מחדש כל נוסחה בכל שמירה. השני היה בדיקת טיפוס שלעולם לא יכלה להיות אמיתית לנוסחה שנטענה מהדיסק, מה שגרם למעריך לספור תאי SUBTOTAL מקוננים פעמיים. קובץ הקורפוס היה פשוט הקלט הראשון שבו חישוב מחדש בזמן שמירה הניב תשובה שונה מ-Excel ומישהו השווה בין השתיים. הפגם המבני קל לניסוח: לפני v2.382.3, TXLSWorksheet.WriteFormula ואחיה לנוסחאות משותפות WriteFormulaWithTExp השיגו את שדה FormulaValue בן שמונת הבתים של כל רשומת Formula על ידי קריאה ל-TXLSWorkbook.GetFormulaValue, שהוא המעריך. המטמון ש-ParseFormula פענחה בקפידה מקובץ המקור בזמן הטעינה מעולם לא נבדק ביציאה. למעשה, כל שמירה הייתה חישוב מחדש מלא עם עקיפת ה-API של חישוב מחדש ברמת החוברת, כך ששום דבר שיכולתם להגדיר בחוברת לא היה עוצר אותה. כל מקום שבו המעריך של HotXLS לא הסכים עם Excel, בין אם פונקציה שלא נתמכת לגיטימית ובין אם באג פשוט, הפך לשינוי נתונים שקט בשמירה

הפגם השני ישב ב-callback של סכומי הביניים המקוננים שהמעריך משתמש בו. Excel מגדיר כל צורה של SUBTOTAL כמתעלמת מתאים שהנוסחה של עצמם היא SUBTOTAL אחר, כך שהמחשבון ב-lxCalc.pas מזריע את FIgnoreSubtotalCells בזמן האגרגציה ושואל את החוברת, דרך TXLSWorkbook.GetClassicIsSubtotalCell, אם כל תא בטווח הוא כזה. ה-callback הזה הביא את טקסט הנוסחה כ-Variant ובדק אותו עם VarType(f) = varOleStr. הטקסט חוזר מ-GetUnCompiledFormula כ-String של דלפי, ו-String שמושם ל-Variant הוא varUString, אף פעם לא varOleStr. הפרדיקט היה שקרי לכל תא בכל קובץ שנטען, סכומי הביניים של הקבוצות נגלגלו לסכום הכולל פעם שנייה, ובשמירה שחישבה מחדש הכול, 10 + 20 + 7 הפך ל-67

// HotXLS 2.381 ומטה: Variant של נוסחה שנבנה מ-String
// הוא varUString, כך שההשוואה הזו אף פעם לא הצליחה
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr מקבל varString, varOleStr ו-varUString,
// ו-AGGREGATE מוחרג מסכומי ביניים עוטפים כמו ש-Excel עושה
if VarIsStr(f) then
  Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
    SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
    SameText(Copy(f, 1, 10), 'AGGREGATE(') or
    SameText(Copy(f, 1, 11), '=AGGREGATE(');

גרסה 2.382.0 הביאה את תיקון ה-VarIsStr, ובדרך לימדה את ה-callback שגם תאי AGGREGATE מוחרגים מסכומי ביניים עוטפים. זה לבדו גרם לקביעת הקורפוס לעבור, כי ה-37 שחושב מחדש התאים כעת ל-37 שנטען. זה לא עשה את הספרייה ישרה: השמירה עדיין חישבה מחדש, והבדיקה הייתה ירוקה רק כי המעריך במקרה הסכים עם Excel בקובץ המסוים הזה. הכללים לאילו תאים SUBTOTAL ו-AGGREGATE מדלגים, כולל שורות מוסתרות, מכוסים במאמר על שורות מוסתרות ב-SUBTOTAL וב-AGGREGATE; מה שחשוב כאן הוא שאף מעריך לא אמור לקבל זכות הצבעה על קובץ שלא ביקשתם ממנו לחשב

מה Excel מבטיח לגבי ערכים שמורים בשמירה?

‏Excel מתייחס לשמירה כתמונת מצב, לא כאירוע חישוב. הערך שנכתב לשדה FormulaValue של רשומת Formula ([MS-XLS] §2.4.127, פריסה ב-§2.5.133) הוא מה שהתא מציג כרגע, שבמצב חישוב ידני עשוי להיות מיושן בשנים, ו-Excel עדיין כותב אותו בנאמנות. חישוב מחדש הוא פעולה נפרדת עם טריגר משל עצמה. HotXLS נוהגת כעת באותו כלל לשמירות קלאסיות: WriteFormula ו-WriteFormulaWithTExp קוראות תחילה ל-TryGetCachedFormulaValue, לוקחות את CacheInfo.Value כשהמצב הוא xlfcsLoaded או xlfcsCalculated, ונופלות ל-GetFormulaValue רק עבור xlfcsMissing ו-xlfcsInvalidated. החצי של צד הקריאה בחוזה הזה, כולל מה כל מצב אומר ולמה מטמון של ריק או False עדיין נחשב לערך, מתואר בקריאת ערכי נוסחה שמורים ב-Excel מדלפי בלי חישוב מחדש

מסלול הנפילה לאחור נשמר בכוונה, לא הוסר. נוסחה שהקציתם בסשן הזה דרך Cells[Row, Col].Formula מגיעה בלי מטמון, ונוסחה שהחלפתם בתא שנטען מסומנת כ-xlfcsInvalidated על ידי _SetCompiledFormula; שתיהן מוערכות בזמן השמירה בדיוק כמו קודם, כך שחוברת שנוצרה עדיין נפתחת ב-Excel עם מספרים. כשאפילו המעריך לא מצליח להפיק ערך, הכותב פולט מטען אפס ומדליק את fAlwaysCalc (ביט 0 ב-grbit של §2.4.127) כדי ש-Excel יחשב את התא מחדש בפתיחה במקום לבטוח במחזיק המקום

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // גיליון, שורה ועמודה באינדוקס 1: R2C4 בגיליון הראשון
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // שום מעריך לא מעורב בתאים שמורים במטמון
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 עבור nested-subtotals.xls
    // שמירה שהייתה מחשבת מחדש הייתה כותבת כאן 67
  finally
    Book.Free;
  end;
end;

איפה שורש של נוסחה משותפת ב-BIFF שומר את הערך השמור שלו?

ברשומת ה-Formula של עצמו, כמו כל תא נוסחה אחר, וזה בדיוק מה שהפך את תא השורש של קבוצה משותפת למקום היחיד שבו שמירה בגישת cache first עדיין איבדה משהו. נוסחה משותפת ב-BIFF8 נשמרת כרשומת ShrFmla ([MS-XLS] §2.4.260) שבאה אחרי רשומת ה-Formula של התא השמאלי-עליון, וכל תא חבר, כולל השורש, נושא rgce שמורכב מאסימון PtgExp בודד (§2.5.198): הבית הראשון של הביטוי שנפרס הוא $01, ואחריו השורה והעמודה של תא השורש. תאי העוקבים הם עצמאיים — HotXLS קוראת את ה-FormulaValue של כל אחד מהם ופותרת את הביטוי על ידי חיפוש הנוסחה המקומפלת של השורש. תא השורש שונה, כי כשהביטוי של רשומת ה-Formula שלו נפרס הוא עדיין לא קיים; הוא מגיע רשומה אחת אחר כך

בפער של הרשומה האחת הזו נעלם המטמון. TXLSReader.ParseFormula מפענחת את הערך השמור, ועל PtgExp שהקואורדינטות שלו שוות לאלה של התא עצמו היא זוכרת את התא ב-FSharedFormulaRow וב-FSharedFormulaCol ומפרסמת את המטמון לתא. כשמגיעה רשומת ShrFmla ($04BC), ParseSharedFormula מקמפלת את הביטוי ומתקינה אותו עם _SetCompiledFormula, ו-_SetCompiledFormula עושה מה שהיא חייבת לעשות לכל שינוי נוסחה: היא מנקה את FCachedFormulaValue ומאפסת את המצב ל-xlfcsMissing. ה-37 שנטען של השורש נזרק לכן לפני שמישהו הספיק לקרוא אותו, TryGetCachedFormulaValue דיווחה על השורש כלא שמור במטמון, והכותב בגישת cache first נפל בצייתנות חזרה למעריך בדיוק בשביל התא שכולם הסתכלו עליו. רשומת Array (§2.4.4) חולקת אותו סדר והיה בה אותו חור

התיקון ב-v2.382.3 מוסיף שדה שלישי, FSharedFormulaCachedValue, לצד קואורדינטות השורש הממתינות. ParseFormula מאחסנת בו את המטמון שפוענח כשהיא מזהה שורש, וגם ParseSharedFormula וגם ParseArrayFormula משמיעות אותו מחדש דרך _SetCellCachedFormulaValue מיד אחרי התקנת הביטוי המקומפל, ואז מאפסות את המאחסן ל-Unassigned. הווריאנט מסוג String של המטמון לא מושפע מכל זה כי המטען שלו מגיע ברשומת String נפרדת ומנותב לפי קואורדינטות תא, לא לפי סדר רשומות. אם אתם עובדים עם הצד של OOXML באותו רעיון, המאמר על הרחבת si של נוסחה משותפת ב-XLSX מסביר למה לפורמט החבילה אין בעיית סדר מקבילה אבל יש לו מלכודות הרחבה משל עצמו

למה העוקבים של נוסחה משותפת צריכים הזזה יחסית?

כי הביטוי שנשמר ב-ShrFmla נכתב יחסית לתא השורש, ועוקב שמשתמש בו מילולית מעריך את ההפניות של השורש במקום את שלו. הקורא הישן התקין Value.GetCopy() על כל עוקב, העתקה עמוקה בלי תזוזה, כך שקבוצה ששורשה ב-B1 עם =A1*3 נתנה לכל עוקב גם =A1*3. שמירה בגישת cache first דווקא הסתירה את זה בקבצים שנטענו, כי לעוקבים היה FormulaValue משלהם והם מעולם לא נזקקו לביטוי כדי להישמר נכון; זה צף ברגע שמשהו חישב מחדש. הקורא מתקין כעת TXLSCompiledFormula.GetCopy(row - srow, col - scol), שהולך על עץ התחביר ומזיז כל הפניה יחסית לפי המרחק של העוקב מהשורש, כך שהעוקב ב-B2 מחזיק =A2*3 אמיתי

בדיקת הרגרסיה שמקבעת את שתי ההתנהגויות ראויה לקריאה כי היא מסרבת לתת למקריות לעבור. היא בונה חוברת עם =A1*3 ו-=A2*3 מעל הקלטים 2 ו-4, ואז מזריקה את המטמונים השגויים במכוון 999 ו-888 דרך _SetCellCachedFormulaValue, פעם אחת עם UseSharedFormulas דלוק ופעם אחת כבוי. אחרי שמירה וטעינה מחדש, שני התאים חייבים עדיין לדווח 999 ו-888 — הוכחה שהשמירה לא נגעה לא במטמון השורש ולא במטמון העוקב. רק אחרי Recalculate מפורש הם חייבים להפוך ל-6 ול-12, הוכחה שהביטוי המוזז של העוקב נכון. בדיקה שהזריעה את הערכים האמיתיים הייתה עוברת גם תחת הכותב הישן, וזה כל העניין בהזרעת ערכים שגויים

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // לשנות קלט

    // מטמונים שנטענו של נוסחאות תלויות לא מבוטלים
    // בעריכת ערך מפורש, כך ש-SaveAs רגיל ישמור את המספרים הישנים.
    // בקשו חישוב מחדש כשאתם באמת רוצים תוצאות טריות:
    Book.Recalculate;

    if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
      Writeln('B1 now ', VarToStr(Info.Value),
        ', state ordinal ', Ord(Info.State));   // xlfcsCalculated
    Book.SaveAs('quarterly-model-updated.xls');
  finally
    Book.Free;
  end;
end;

מה החוזה של cache first לא עושה בשבילכם

שמירה בגישת cache first משמרת את מה שנטען; היא לא עוקבת אחרי האם מה שנטען עוד נכון. שינוי ערך מפורש שנוסחה תלויה בו מסמן את גרף התלויות כמלוכלך עבור המעריך, אבל הוא משאיר את המטמון xlfcsLoaded של התא התלוי במקומו, והכותב הקלאסי ישמח לכתוב את הערך המיושן הזה אלא אם תקראו ל-Recalculate או תקראו קודם ל-Value של התא, מה שמחשב אותו ומעביר את המצב ל-xlfcsCalculated. זו אותה עסקה ש-Excel עושה במצב חישוב ידני, והיא הנכונה לצינור שפותח קבצים של צד שלישי, עורך כמה תוויות ושומר — אבל היא אומרת שחוברת שעורכת קלטים חייבת לקחת בעלות מפורשת על שלב החישוב מחדש שלה. המדיניות RecalcBeforeSave של כותב ה-XLSX לא משתנה בעבודה הזו ויש לה מצב ידני משלה שמשמר מטמונים באותה רוח. שני גבולות קטנים יותר נובעים מכך: מסלול cache first עוזר רק לתאים שהמצב שלהם xlfcsLoaded או xlfcsCalculated; גנרטור שכותב נוסחאות ואף פעם לא מעריך אותן עדיין משלם הערכה אחת לכל תא בזמן השמירה, בדיוק כמו קודם. ותיקון סכומי הביניים המקוננים מתקן אילו תאים המעריך מדלג, לא כל פונקציה שהמעריך מממש — קובץ שהנוסחאות שלו HotXLS לא יכולה לחשב בזהות ל-Excel בטוח כעת להלוך ושוב בלי שינוי, אבל Recalculate מכוון על אותו קובץ עדיין יפיק את התשובה של הספרייה ולא של Excel, וכדאי להשוות בין השתיים לפני שסומכים על שמירה שחושבה מחדש

שמירות קלאסיות בגישת cache first, מטמוני השורש המשוחזרים של נוסחאות משותפות ומערכים, ההזזה של ההפניות היחסיות לעוקבים משותפים, וכללי הקינון המתוקנים של SUBTOTAL ו-AGGREGATE כולם מגיעים ברכיב הגיליון HotXLS ל-Delphi הרגיל ל-Delphi ו-C++Builder, בלי תלות ב-Excel או בכל שרת אוטומציה של OLE; עמוד המוצר נושא את תיעוד ה-API המלא לנקודות הכניסה של החוברת, קורא המטמון והחישוב מחדש שהיו בשימוש כאן