העתקה בין-חוברות-עבודה וקישור-מחדש נוסחאות ב-HotXLS ב-Delphi

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

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

למה AddCopy לא יכולה פשוט להעתיק את עץ הנוסחה המקומפל?

‏AddCopy לא יכולה להעביר את עץ הנוסחה המקומפל ללא שינוי, משום שנוסחת BIFF מקומפלת אינה טקסט עצמאי — היא רצף טוקנים, וכמה מהטוקנים האלה הם מספרים שלמים קטנים שנפתרים נכון רק בתוך חוברת העבודה שייצרה אותם. הפניה תלת-ממדית (3D) כמו Sheet2!A1:A10 לא נושאת את השם המילולי Sheet2 ברגע שהיא מקומפלת; היא נושאת שדה שמפרט ה-BIFF קורא לו ixti (HotXLS שומרת על אותו ערך בעץ המקומפל שלה עצמה תחת שם השדה FExternID), אינדקס לתוך טבלת ה-EXTERNSHEET הפרטית של חוברת העבודה הזו, ממוספר בכל סדר שחוברת העבודה הספציפית הזו במקרה רשמה את הגיליונות והספרים החיצוניים שלה. הזז את הטוקן ללא שינוי לתוך חוברת עבודה שטבלת ה-EXTERNSHEET שלה נבנתה בסדר שונה ואינדקס 3 כבר לא אומר Sheet2 — הוא אומר איזה גיליון שבמקרה תופס משבצת 3 שם, ול-Excel אין דרך לסמן את הטעות, משום שככל שפורמט הקובץ נוגע בדבר הנוסחה מעוצבת-היטב לחלוטין. זה בדיוק הכשל ש-TXLSWorksheets.AddCopy קיימת כדי למנוע: נקראת מאוסף הגיליונות של כל אחת מחוברות העבודה בקוד Delphi או C++Builder, היא מעתיקה גיליון עבודה — ערכי תא, פורמטים, נוסחאות, תרשימים, הערות, מיזוגים, הגדרת עמוד, ועוד — מחוברת עבודה מקור שאולי היא זו שאתה קורא לה ואולי לא, ומצרפת את התוצאה ליעד תחת שם שאתה בוחר או עותק מבודל של המקור

var
  Summary, Branch: IXLSWorkbook;   // interface-counted: do not Free
begin
  Summary := TXLSWorkbook.Create;
  Branch := TXLSWorkbook.Create;
  Branch.Open('branch-east.xls');

  // Appends a copy of Branch's first sheet onto Summary, renamed to
  // stay unique inside the destination workbook
  Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
  Summary.SaveAs('consolidated.xls');
end;

התיקון: דה-קומפילציה לטקסט, קומפילציה מחדש ביעד

HotXLS פותרת את בעיית האינדוקס על ידי כך שהיא אף פעם לא נותנת לעץ המקומפל עצמו לחצות את גבול חוברת העבודה. עבור כל תא-נוסחה בהעתקה בין-חוברות-עבודה, ‏AddCopy מבצעת דה-קומפילציה לנוסחת המקור לתוך אותו טקסט בסגנון A1 שמשתמש היה רואה בשורת הנוסחה של Excel, ואז מוסרת את הטקסט הזה לחוברת העבודה היעד, שמפענחת אותו בחזרה לעץ באמצעות הטבלאות שלה עצמה מאפס — הפניה מוסמכת-גיליון כמו Data!D2:D100 היא סתם מחרוזת באותה נקודה, ומחרוזת אומרת אותו דבר בכל חוברת עבודה, כך שאם ליעד כבר יש גיליון בשם Data ההפניה נפתרת נכון ללא תרגום אינדקס בכלל, משום שמעולם לא היה אינדקס גולמי בטיסה לתרגם. HotXLS משלמת עבור המסע הלוך-ושוב הזה רק כשהיא חייבת: העתקת גיליון בתוך אותה חוברת עבודה נוקטת נתיב זול יותר שבו העץ המקומפל פשוט משוכפל בזיכרון, שכן כל אינדקס בתוכו כבר תקף במקום שהוא נשאר בו, והמעקף-טקסט רץ רק ברגע ש-AddCopy מזהה שהמקור והיעד הם באמת מופעי חוברת-עבודה שונים. שווה להיות מדויק גם לגבי מה הכתיבה-מחדש הזו לא: אין לה שום קשר להיסט השורה והעמודה שרץ כאשר אתה מכניס או מוחק שורות בתוך גיליון בודד, שמאמר נלווה מכסה בפירוט — המנוע ההוא כותב מחדש טקסט A1 במקום כדי לעקוב אחר תאים שזזו כמה שורות מעלה או מטה בתוך חוברת עבודה אחת, בעוד זה רץ כאשר נוסחה עוזבת את חוברת העבודה שקימפלה אותה לגמרי, שם שורות שזזו אינן הבעיה ומספור פרטי-לחוברת-עבודה כן

// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);

מה קורה אם ליעד עדיין אין את הגיליון הזה, או את השם הזה?

הקומפילציה-מחדש של AddCopy מצליחה רק כאשר לחוברת העבודה היעד כבר יש כל מה שטקסט הנוסחה מפנה אליו, ושני הפערים שמופיעים בפועל הם גיליון בעל-אותו-שם שעדיין לא הועתק באצווה הזו, ושם מוגדר בהיקף-חוברת-עבודה שאף פעם לא היה קיים ביעד בכלל. HotXLS לא מעלה חריגה כשקומפילציה-מחדש נכשלת באמצע העתקת גיליון — הצבת ה-Value של התא בשקט שומרת את טקסט הנוסחה כמחרוזת פשוטה במקום, מצב-כשל מכוון, ניתן-לבדיקה ולא שקט, שכן תא נוסחה שבאופן בלתי-צפוי מציג טקסט מילולי כמו =SUM(Q1!B2:B12) במקום מספר מחושב הוא הרמז שמשהו במעלה-הזרם בהעתקה לא נפתר. לפני שהיא מוותרת, AddCopy מנסה תיקון אחד: היא עוברת על עץ התחביר של הנוסחה שנכשלה ואוספת כל מזהה שם-מוגדר שהנוסחה נוגעת בו, ועבור כל שם בהיקף-חוברת-עבודה שקיים במקור אך עדיין לא ביעד, היא מעתיקה את השם לצד השני ומקמפלת מחדש את אותו טקסט פעם שנייה. שמות בהיקף-גיליון יושבים מחוץ למה שהתיקון הזה יכול לתקן, שכן לשם שגלוי רק לנוסחאות על גיליון אחד של חוברת העבודה המקור אין משבצת מקבילה להגר אליה, ויעד שכבר מחזיק שם עם אותו איות נשאר ללא נגיעה במקום שנכתב-מעל, בהנחה ששם שהקוד הקורא במכוון יצר מראש הוא זה שהוא רוצה שיכובד. בתוך חוברת עבודה בודדת, חיפוש-שם של נוסחה חוצת-גיליון עובר מהיקף-גיליון למעלה להיקף-חוברת-עבודה אוטומטית, שזה המנגנון שמאמר השמות המוגדרים והנוסחאות חוצות-הגיליון של HotXLS מכסה; חציית גבול חוברת-עבודה בפועל מסירה את רשת הביטחון הזו לגמרי, ושם חייב להיות מובא במכוון לצד השני או שהנוסחה שתלויה בו מתדרדרת לטקסט

הפניות סדרת תרשים זקוקות לאותו תיקון, אבל נתיב קוד שונה

סדרת תרשים HotXLS שמשרטטת טווח תאים נתקלת בדיוק באותה בעיית מספור כמו נוסחת תא רגילה, משום שגם הפניית טווח-נתונים של תרשים היא זרם-טוקן נוסחה מקומפל — מפרט ה-BIFF קורא לרשומה שנושאת אותה BRAI ([MS-XLS] סעיף 2.4.51) — אבל AddCopy לא יכולה לתקן את זה על ידי שימוש חוזר בנתיב טעינת-התרשים הרגיל, משום שהנתיב הזה בדיוק מה שיוצר את הבאג. כאשר רשומת תרשים מפוענחת מהדיסק במהלך הרגיל של פתיחת קובץ, עץ הנוסחה שלה נבנה על ידי תרגום הבייטים הגולמיים דרך איזה מופע מחשבון שמבצע את הפענוח; הזן את בייטי ה-BRAI הגולמיים של תרשים המקור דרך טוען הרשומות הרגיל של חוברת העבודה היעד עצמה במקום זאת, וה-ixti המוטמע בבייטים האלה נפתר מול טבלת ה-EXTERNSHEET של היעד, כך שהסדרה בשקט מצביעה על איזה גיליון שתופס את המשבצת הזו שם — אותה מחלקת טעות כמו העתקת עץ מקומפל של תא ללא שינוי, פשוט קשה יותר לשים לב אליה משום שאף אחד לא קורא נוסחאות סדרת תרשים באופן שהוא קורא נוסחאות תא. HotXLS נמנעת מהמלכודת עם נתיב שכפול ייעודי במקום: TXLSCustomChart.AssignFrom מעתיקה את בייטי הכותרת שאינם-נוסחה של כל רשומת תרשים מילה-במילה, ואז בונה מחדש את הטווח המצורף דרך אותו פרימיטיב דה-קומפילציה-וקומפילציה-מחדש שמשמש עבור תאים רגילים, כך שהעץ החדש נבנה מול טבלת ה-EXTERNSHEET של היעד מאפס במקום להתפרש-מחדש מולה אחרי המעשה

אותה בעיית מספור, אינדקס גופן אחד בכל פעם

לא כל מספר מקומי-לחוברת-עבודה בתוך תרשים או תא טקסט-עשיר הוא נוסחה, ואינדקס גופן הוא אותה מחלקת בעיה במיניאטורה. ריצות טקסט-עשיר, לצד שני סוגי רשומת תרשים נוספים שנושאים גופן כותרת או ציר, שומרים הפניית גופן כאינדקס מספר-שלם גולמי לתוך טבלת הגופן של חוברת העבודה המחזיקה, והאינדקס הזה לא אומר כלום בטבלה של חוברת עבודה אחרת — הוא יכול באותה קלות להצביע על גופן, גודל, או צבע לגמרי שונה שם. HotXLS פותרת את זה לפי-ערך ולא לפי-מספר: היא מחפשת את תכונות הגופן בפועל באינדקס ההוא בטבלת המקור, מוצאת או יוצרת רשומה תואמת בטבלת הגופן של היעד, וכותבת מחדש את האינדקס השמור כך שיצביע על המשבצת החדשה ההיא. קוריוז פורמט אחד הופך את החיפוש עצמו מסובך — האינדקס הממוספר-בקובץ מדלג על משבצת 4, פער-מספור ש-[MS-XLS] סעיף 2.5.339 מתעד, כך שהקוד חייב להזיז את האינדקס למטה באחד לפני השוואת גופנים ובחזרה למעלה באחד לפני כתיבת התוצאה

// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
  Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
  Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
  Inc(Ifnt);

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

נוסחה שמגיעה לתוך חוברת עבודה שלישית עוד לפני שאתה אי-פעם קורא ל-AddCopy היא המקרה היחיד שמסע-הטקסט-הלוך-ושוב לא יכול לשאת, משום שה-דה-קומפיילר נוסחה-לטקסט של HotXLS עצמה במכוון לא מייצרת טקסט סוגריים [Book]Sheet! עבור הפניה חיצונית, והמקמפל בצד השני גם לא מקבל את התחביר הזה כקלט — כך שהמקרה הבודד הזה רץ דרך מנגנון שני שאף פעם לא נוגע בטקסט בכלל. כאשר תיקון הגירת-השם המתואר לעיל עדיין משאיר תא כמחרוזת, ולחוברת העבודה המקור יש שם קובץ אמיתי, ‏AddCopy מחליפה אסטרטגיה: היא מעתיקה-עמוק (deep-copy) את עץ הנוסחה המקומפל עצמו במקום את הטקסט שלו, ואז מוסרת את ההעתק למעבר קישור-מחדש ייעודי, ‏RebindExternRefsInTree, שעובר עליו node אחר node. עבור כל הפניית טווח שהיא מוצאת, המעבר ההוא פותר את רשומת ה-EXTERNSHEET של המקור בחזרה לזוג שמות גיליון, ורושם, או עושה שימוש חוזר, ברשומה מקבילה בטבלאות ההפניה-החיצונית של היעד עצמה, יוצרת קישור חוברת-עבודה-חיצונית חדש לגמרי אם ליעד אף פעם לא הייתה הפניה לקובץ המקור ההוא

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

מה העיצוב הזה עולה לך

המנגנון של דה-קומפילציה-וקומפילציה-מחדש של AddCopy אינו חינמי, והעלות שווה לתכנן סביבה לפני שאתה כותב סקריפט לעבודת איחוד גדולה ולא אחרי. העתקת גיליון בתוך אותה חוברת עבודה נוקטת בנתיב הזול, שכפול ישיר בזיכרון של העץ המקומפל, משום שכל אינדקס בתוכו כבר תקף בחוברת העבודה שהוא נשאר בה; העתקה בין-חוברות-עבודה משלמת עבור פענוח אמיתי על כל תא-נוסחה במקום, דה-קומפילציה לטקסט ואז קומפילציה של הטקסט ההוא שוב מכלום, ולמרות שההבדל לא שווה מדידה על גיליון עם כמה עשרות נוסחאות, חוברת עבודה מקור עם עשרות אלפי תאי נוסחה, שמועתקת כגיליון אחד מתוך תריסרים בעבודת אצווה, צריכה לצפות שהקומפילציה-מחדש תשלוט בזמן הריצה ולא ה-I/O של הקובץ סביבה. סדר ההעתקה חשוב מסיבה שנייה מעבר למהירות: נוסחה שמפנה לגיליון ש-AddCopy עדיין לא הגיעה אליו באצווה הזו נכשלת בקומפילציה-מחדש שלה מאותה סיבה שנוסחה שמפנה לגיליון שבאמת לא קיים נכשלת, כך שעבודה שמעתיקה גיליון B לפני נוסחת גיליון A שתלויה בו תראה את הנוסחה ההיא מתדרדרת בדיוק כמתואר לעיל, טקסט מחרוזת או חלופת-קישור-חיצוני שמצביעה ישר בחזרה לקובץ המקור שהיא הרגע הגיעה ממנו. ומשום שכל חוברת עבודה מקור באצוות איחוד בדרך כלל נכתבת באופן עצמאי, שווה לבדוק במפורש עבור מצב הכשל היחיד שאף קובץ מקור בודד לא היה יכול להזהיר אותך ממנו — חמש חוברות עבודה של סניפים שכל אחת מסכמת את המספרים של סניף עמית יכולות להתחבר להפניה מעגלית אמיתית בתוך חוברת עבודת הסיכום ללא שאף קובץ מקור בודד אי-פעם הכיל אחת, מחזור שקיים רק ברגע שכל גיליון נחת באותו מקום וחישוב-מחדש רץ על פני הקבוצה המשולבת

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