הרחבת si בנוסחאות משותפות XLSX ב-Delphi: מלכודות

עוקב נוסחה משותפת ב-XLSX לא נושא טקסט נוסחה. אלמנט ה-<f t="shared" si="N"/> שלו מצביע על תא master במקום אחר בגיליון, והקורא חייב לבנות מחדש את הטקסט על ידי הזזת נוסחת ה-master בהפרש השורה והעמודה. HotXLS Component עבור Delphi ו-C++Builder מבצע את ההרחבה הזו בזמן הפתיחה, כך שכל עוקב מדווח נוסחה מלאה

אם אי פעם טענתם XLSX מהעולם האמיתי בספרייה חיצונית ומצאתם שלעמודה של אלף נוסחאות יש טקסט בדיוק בתא אחד ומחרוזות ריקות ב-999 האחרים, פגשתם את התכונה הזו מהצד הלא נכון. שום דבר לא מושחת. הקובץ עושה מה ש-ECMA-376 מאפשר לו, והקורא פשוט עצר בנקודה שבה ה-XML נעצר

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

כי הפורמט במכוון מאחסן את הנוסחה פעם אחת. ב-ECMA-376 חלק 1 וב-ISO/IEC 29500-1, האלמנט <f> (§18.3.1.40) נושא מאפיין t מסוג ST_CellFormulaType, והערך shared אומר שהתא הזה משתתף בקבוצה המזוהה על ידי המאפיין si. תא אחד בדיוק בקבוצה, ה-master, גם נושא מאפיין ref שנותן את הטווח שהקבוצה חלה עליו, ורק אותו תא נושא את טקסט הנוסחה כתוכן אלמנט. כל תא אחר בקבוצה הוא עוקב. הוא חוזר על t="shared" ואותו si, ותוכן האלמנט שלו ריק. Excel כותב את הקבוצות הללו באגרסיביות, כי מילוי-למטה על פני עמודה בת 200,000 שורות קורס מ-200,000 מחרוזות נוסחה למחרוזת אחת בתוספת 199,999 אלמנטי placeholder זעירים. החיסכון אמיתי והעלות נוחתת לגמרי על הקורא: בלי הרחבה, לעוקב אין משמעות בפני עצמו

ההזזה היא תרגום, לא העתקת טקסט

HotXLS פותר עוקב על ידי איתור ה-master הרשום תחת אותו si, חישוב דלתת השורה והעמודה מעוגן ה-master לתא הנוכחי, ותרגום כל הפניה בנוסחת ה-master לפי אותה דלתה. ממדים יחסיים זזים, ממדים מוחלטים לא, והפניות מעורבות זזות רק במחצית הלא-מוחלטת שלהן. literals של מחרוזת מדולגים לגמרי, כך שנוסחה שקורה להכיל את הטקסט "A1" שומרת על הטקסט הזה ללא שינוי בכל עוקב

const
  // xl/worksheets/sheet1.xml, trimmed to the interesting cells
  SheetXml: WideString=
    '<row r="1"><c r="A1"><v>1</v></c>'+
    '<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
    'A1+$A$1+A$1+$A1+&quot;A1&quot;+SUM(A1:A2)</f><v>7</v></c></row>'+
    '<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
    '<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
begin
  Wb:= TXLSXWorkbook.Create;
  try
    Wb.Open(FileName);
    Sh:= Wb.Sheets[1];
    // Master, verbatim
    // B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
    // Follower one row down: relative row moves, absolute row frozen,
    // the mixed A$1 keeps its row, and the literal stays a literal
    // B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
    ShowMessage(Sh.Cells[2, 2].Formula);
  finally
    Wb.Free;
  end;
end;

מאפיין ה-ref הוא שער, לא קישוט. עוקב שהקואורדינטות שלו נופלות מחוץ לטווח ההחלה של ה-master לא מורחב, כי הקובץ אז עושה טענה שהקבוצה לא תומכת בה. באופן דומה, כשהזזה הייתה דוחפת הפניה מעל שורה אחת או משמאל לעמודה A, HotXLS פולט #REF! עבור אותו טוקן במקום לצמצם אותו בשקט, שזה מה ש-Excel עצמו היה מפיק עבור אותה עריכה. התרגום הזה קרוב-משפחה, אך לא אותו דבר, לשכתוב ההפניות שקורה כשמוסיפים או מוחקים שורות. לנתיב ההוא יש כללים משלו לגבי מה טווח עושה כשעריכה חותכת דרכו, והוא מתואר בנפרד בהמאמר על התאמת הפניית נוסחה במהלך הוספה ומחיקה. הרחבה משותפת פשוטה יותר: זו offset טהורה מעוגן ידוע, מוחלת פעם אחת, בזמן הניתוח

אילו צורות הפניה השיפט חייב לכסות?

כולן, אחרת ההרחבה היא באג אובדן-נתונים במסווה. שיפט נאיבי שמבין רק A1 ו-A1:B2 ישחית או יפיל את הצורות האקזוטיות יותר, וחוברות עבודה אמיתיות מלאות בהן. מתרגם הנוסחה-המשותפת של HotXLS מזהה את כל משפחת A1 לפני שהוא מחליט מה להזיז. הפניות חוברת עבודה חיצוניות כמו [Book.xlsx]Sheet1!A1 והפניות תלת-ממדיות כמו Sheet1:Sheet3!A1 שומרות על הקידומת שלהן שלמה בעוד הפניית התא הנגררת זזה. שמות גיליון במרכאות שורדים, כולל המקרה המעצבן שבו הגיליון נקרא ממש A1, כך ש-'A1'!A1 מזיז רק את החלק אחרי סימן הקריאה. A:A עמודה-שלמה מזיז את מימד העמודה שלו ולא כלום אחר; 1:1 שורה-שלמה מזיז את מימד השורה שלו ולא כלום אחר; $A:$A לא זז בכלל. הפניות טבלה מובנות כמו Table[A1] נשארות ללא נגיעה, כי החלק בסוגריים מרובעים הוא שם עמודה, לא קואורדינטה

// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1       : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3       : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]

שמות פונקציה הם המלכודת השקטה כאן. סורק טוקנים שתופס אותיות ואחריהן ספרות ישכתב בשמחה LOG10 ל-LOG11 שורה אחת למטה. HotXLS דורש גבול הפניה לפני טוקן מועמד ואחריו, כך שמזהה שממשיך לתוך אות, ספרה, קו תחתון, נקודה, או סוגריים פתוחים אינו הפניית תא. אם אתם עובדים במשפחת הסימון האחרת, אותה בעיית גבול מופיעה אחרת, ומאמר סימון R1C1 מכסה היכן שני המודלים נבדלים

מדוע אלמנט f סוגר-עצמו בולע את הערך הבא?

כי אלמנט סוגר-עצמו לא מפיק אירוע סוף-אלמנט. זה הבאג היקר ביותר בכל התכונה כולה, והוא לא ספציפי לאף מנתח XML אחד. ב-TXMLReader, <f t="shared" si="4"/> מעלה בדיוק אירוע Element אחד עם IsEmptyElement מוגדר ל-True, ולעולם לא מעלה את ה-EndElement המתאים. מנתח שסוגר את מצב לכידת-הנוסחה שלו רק ב-EndElement לכן נשאר בתוך הנוסחה, והטקסט הבא שהוא רואה, שהוא התוצאה השמורה במטמון בתוך <v>, מצורף לבאפר הנוסחה. גרוע מזה, המצב שורד את גבול התא, כך שהתא הבא שמחזיק <f> אמיתי סופג את טקסט הנוסחה שלו על ידי התא הקודם. התיקון הוא לסיים את מצב הנוסחה באירוע ה-Element עצמו בכל פעם ש-IsEmptyElement הוא True, ולהריץ את כל פתרון העוקב שם במקום לחכות. זה אומר קריאת t, si, ref, aca, ו-ca מהמאפיינים, החלת ההרחבה המשותפת, כתיבת מאפייני החישוב-מחדש על התא, וניקוי מצב המשותף, הכל בתוך הענף שמטפל באלמנט הריק. שימו לב שהפורמט מתיר את שני האיותים, <f t="shared" si="4"/> ו-<f t="shared" si="4"></f>, והשני אכן מעלה EndElement. קורא נכון חייב לטפל בזוג באופן זהה, וזו הסיבה ש-HotXLS מכסה את שני האיותים באותו קובץ רגרסיה

ערכי si דלילים ולא-מסודרים ותור הממתינים

מאפיין ה-si הוא מספר שלם לא-חתום שמסופק על ידי הקובץ, לא מיקום מערך שאתם שולטים בו. שום דבר בסכימה לא דורש שאינדקסים משותפים יהיו צפופים, יתחילו באפס, או יופיעו בסדר עולה, ושום דבר לא עוצר קובץ עוין או פשוט מוזר מלהשתמש ב-si="4294967290" על התא הראשון. הקצאת גודל מערך חיפוש מהערך הגדול ביותר שנצפה של si היא לכן פרימיטיב מיצוי-זיכרון, לא אופטימיזציה. HotXLS שומר את נתיב פתיחת חוברת העבודה על טבלה דלילה ממוינת במקום: קבוצות משותפות רשומות תחת מפתח המספר השלם שלהן ב-TStringList ממוין, מה שהופך חיפוש לחיפוש בינארי על פני כמה קבוצות שבאמת קיימות, ללא קשר לגודל המספרי של האינדקסים. סדר הוא המחצית השנייה של הבעיה. master בדרך כלל קודם לעוקביו בסדר המסמך, אך זו מוסכמה ולא כלל, כך שכל עוקב שלא יכול לפתור את ה-si שלו ברגע שהוא מנותח נכנס לתור ממתינים. כשהגיליון מסתיים, התור מופעל מחדש מול הטבלה שעכשיו שלמה, וה-masters המאוחרים פותרים את יתומיהם. תאים שלעולם לא מוצאים master שומרים על נוסחה ריקה, שזו התוצאה הכנה עבור קובץ שמפנה לקבוצה שהוא מעולם לא הגדיר

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

הקוראים בזרימה עומדים בפני אותה דרישה תחת תקציב זיכרון הרבה יותר צר, והם פותרים אותה עם טבלה מקומית-לגיליון-עבודה. גם TXLSDirectReader וגם TXLSRowCursor מרחיבים עוקבים לתוך נוסחאות לכל-תא מלאות תוך שמירה על התנהגות הזיכרון-המוגבל וההיטל שלהם, כך שמעבר קדימה-בלבד על גיליון בן 300 MB עדיין מוסר לכם טקסט נוסחה אמיתי

var
  Reader: TXLSDirectReader;
  Cursor: TXLSRowCursor;
begin
  // Projection: only rows 2..3, only column A. The master lives in row 1,
  // outside the projection, and is still parsed so the followers resolve
  Reader:= TXLSDirectReader.Create;
  try
    Reader.FirstRow:= 2;
    Reader.LastRow:= 3;
    Reader.IncludeColumn(1);
    Reader.OnCell:= HandleCell;   // Cell.Formula is fully expanded here
    Reader.ReadFile(FileName);
  finally
    Reader.Free;
  end;

  // Forward-only row traversal, same expansion
  Cursor:= TXLSRowCursor.Create;
  try
    Cursor.Open(FileName);
    if Cursor.FindFirst then
      repeat
        if Cursor.CellCount > 0 then
          WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
      until not Cursor.FindNext;
  finally
    Cursor.Free;
  end;
end;

שני אילוצים נובעים מהעיצוב הזה. ראשית, היטל לעולם לא יכול לדלג על ה-master. מסנן שורה שהוגדר עם FirstRow ו-LastRow, או מסנן עמודה שנבנה עם IncludeColumn, עשוי לדלג על פליטת תא ה-master ל-callback שלכם, אך המנתח עדיין חייב לרשום את ה-si שלו, קואורדינטות עוגן, טווח החלה, וטקסט נוסחה, אחרת כל עוקב בתוך ההיטל נפתר ללא כלום. רק העבודה בצד-העוקב, ההזזה ופענוח הערך, בטוחה לדלג עליה. שנית, הטבלה היא לכל-גיליון-עבודה וחיי המחזור שלה חייבים להיות מנוהלים במפורש: TXLSRowCursor מחזיק מופע אחד למשך מעבר גיליון ומנקה אותו בהפעלה מחדש, מעבר גיליון, סוף קובץ, חריגה, וסגירה, כך שקבוצה שהוגדרה בגיליון אחד לעולם לא יכולה לדלוף לגיליון שתיים. מכיוון שנתיב הזרימה הוא לולאה חמה, הוא משתמש ב-hash מספר שלם מבוסס open-addressing במקום בטבלת המחרוזות הממוינת, מה שנמנע מהמרת מספר-שלם-למחרוזת לכל תא

מה קורה בשמירה, ואיפה הגבולות

ברגע שעוקב הורחב הוא נוסחה רגילה, ו-HotXLS כותב אותו בחזרה כאלמנט <f> עצמאי ללא t="shared" וללא si. סבב ההלוך-ושוב יציב והתוצאות השמורות ב-<v> שורדות, אך הפלט גדול יותר מהקלט עבור גיליון עם הרבה שיתוף, וקיבוץ ה-Excel שיצר לא נבנה מחדש בשמירה. אם נאמנות ברמת-בייט של הקבוצות המשותפות חשובה לכם יותר מנוסחת טקסט אמיתית בכל תא, זו העסקה שאתם מקבלים. צד ה-XLS שונה, אגב: לרשומת BIFF8 SHRFMLA יש קידוד משלה וכותב משלה, עם מתג קבוצה-משותפת על חוברת העבודה

שני דברים קשורים במפורש אינם נוסחאות משותפות למרות שהם חולקים את האלמנט <f>. נוסחאות מערך CSE ישנות משתמשות ב-t="array" עם ref שמכסה את הטווח המעוגן, ומערכים דינמיים משתמשים באותו איות t="array" אך מזוהים על ידי מאפיין cm שמשורשר דרך cellMetadata לרשומת XLDAPR. התייחסות לתא spill של מערך דינמי כעוקב משותף או CSE היא באג נכונות אמיתי, וההפרדה מכוסה בהמאמר על נוסחאות מערך דינמי ו-spill. קוראים את שלושת המקרים כשלושה מנתחים שקורה שהם חולקים שם תג, והקוד נשאר כן

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