שמות מוגדרים ונוסחאות בין-גיליונות ב-Delphi (HotXLS)

שם מוגדר הוא תווית שעומדת במקום קבוע, טווח תאים, או ביטוי נוסחה, המאוחסן פעם אחת בחוברת העבודה ומופנה אליו סמלית בכל מקום שנחוץ. כתוב TaxRate בנוסחה והמנוע פותר אותו לכל מה שהגדרת השם מחזיקה, בין אם זה הליטרל 0.08 או הטווח Data!$A$2:$D$100. הפניה בין-גיליונות היא הרעיון האורתוגונלי: Data!D2 מגיע לתא בגיליון אחר על ידי הענקת כימות שם-גיליון לכתובת. שלב את שניהם וגיליון סיכום יכול לסכם גיליון פירוט דרך שם שלעולם לא מזכיר כתובת ליטרלית, שזה בדיוק מה שרוצים בחוברת עבודה שמחולל מרכיב ורואה חשבונב קודם מבקר מאוחר יותר

HotXLS, ספריית Delphi הטבעית של losLab לקובצי XLS ו-XLSX, חושפת את טבלת השמות של שני הפורמטים עם גישת יצירה, חיפוש ומחיקה, בתוספת מנוע נוסחאות שפותר שמות והפניות בין-גיליונות בתהליך. שני הפורמטים שומרים על היררכיות מחלקה נפרדות, וההבדלים בין ממשקי השמות שלהם הם החלק שמכשיל קוד שמועתק מאחד לשני

שני מאגרי שמות שלא חולקים ממשק

בצד ה-XLS, TXLSWorkbook.GetNames מחזיר אוסף IXLSNames שעלייתו Add(Name, RefersTo, Visible) כותבת שם לתוך טבלת השמות BIFF. רשומות בודדות חוזרות כאובייקטי IXLSName הנושאים Name, RefersTo, RefersToRange פתור, ומתודת Delete. בצד ה-XLSX, TXLSXWorkbook.DefinedNames הוא אוסף TXLSXDefinedNames עם Add, FindByName, ו-DeleteByName

מוסכמות החיפוש מתבדלות באופן שעולה במהלך הסבה ולא בזמן קומפילציה. מאפיין Item ברירת-המחדל של אוסף ה-XLS מקבל Variant, כך שגם Names[0] וגם Names['TaxRate'] פותרים כנגדו. לאוסף ה-XLSX אין מאפיין ברירת מחדל כזה; אתה קורא ל-FindByName('TaxRate'), שמחזיר nil כאשר השם נעדר. קוד שנכתב ל-facade אחד מקומפל כנגד השני רק במקרה, והכשל נוטה להופיע כגישת nil בזמן ריצה ולא כקו-אדום-מתפתל ב-IDE

היקף הוא ההחלטה הראשונה, לא דגל שמוסיפים מאוחר יותר

שם מוגדר הוא או בהיקף חוברת-עבודה, גלוי לנוסחאות בכל גיליון, או בהיקף גיליון, גלוי רק לנוסחאות בגיליון שבבעלותו. ב-API ה-XLSX ההבחנה היא פרמטר אופציונלי יחיד. DefinedNames.Add(AName, AFormula) יוצר שם ברמת חוברת-עבודה, בעוד Add(AName, AFormula, ASheetIndex) קושר אותו לגיליון אחד. בקריאה חוזרת, TXLSXDefinedName.SheetIndex מחזיר -1 להיקף חוברת-עבודה ואינדקס גיליון 0-בסיסי אחרת

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

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... מילוי Data!A2:D100 בשורות פירוט ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // היקף חוברת-עבודה, קבוע
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // היקף חוברת-עבודה, טווח
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // בהיקף גיליון-אינדקס 1 בלבד

    // נוסחאות XLSX לא נושאות '=' מוביל
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

לשם מוגדר אין חובה להצביע על טווח. TaxRate לעיל מתייחס לקבוע החשוף 0.08, וזו הדרך הנקייה ביותר לפרסם הנחה עסקית. הוא מופיע פעם אחת ב-Name Manager של Excel, כל נוסחה מפנה אליו סמלית, ושינוי השיעור של הרבעון הבא הוא עריכה של שורה אחת למחולל במקום חיפוש בארבע-עשרה מחרוזות נוסחה מורכבות

סימן השווה ששייך רק לצד אחד

ערוץ הזנת הנוסחה הוא המקום שבו קוד מוסב נשבר לרוב, כי שני ה-facade-ים לא מסכימים על סימן השווה. תאי XLS מקבלים נוסחאות דרך Value עם = מוביל. לתאי XLSX יש מאפיין Formula ייעודי שלוקח את הביטוי בלי הקידומת. כתוב '=SUM(A1:A10)' לתוך TXLSXCell.Formula וסימן השווה הופך לחלק מטקסט הביטוי המאוחסן במקום סמן, והקובץ לא יתנהג כפי שאותה מחרוזת התנהגה בצד ה-XLS

var
  Book: IXLSWorkbook;   // נספר-הפניות כממשק: אין לקרוא ל-Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // בהנחה שגיליון בשם 'Data' כבר מחזיק את שורות הפירוט
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = מוסתר מ-Name Manager

  // נוסחאות XLS עוברות דרך Value, עם קידומת '='
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

הקטע הזה מציג שתי גרסאות נוספות בצד ה-XLS. אוסף הגיליונות הוא 1-בסיסי, כך ש-Sheets[1] הוא הגיליון הראשון, בניגוד ל-Sheets[0] ה-0-בסיסי של XLSX. והפרמטר השלישי של Add יוצר שם נסתר: נוכח בקובץ ושמיש על ידי נוסחאות, אך בלתי-נראה ב-Name Manager של Excel. שמות נסתרים הם הכלי הנכון לעבודת-פנים של המחולל שמשתמשי-קצה לעולם לא צריכים לערוך או למחוק בטעות

הפניות בין-גיליונות, ומה קורה כששורות זזות

שני מנועי הנוסחאות מקבלים את תחביר ההפניה הבין-גיליונות הסטנדרטי. שמות גיליון פשוטים מכמתים ישירות כ-Data!A1; שם עם רווחים או פיסוק זקוק למירכאות בודדות, כמו ב-'Sheet With Space'!A1. בתוך טקסט RefersTo של שם, הושט יד אל הפניות אבסולוטיות כגון Data!$A$2:$D$100 כמעט כל פעם. הפניה יחסית בתוך שם מוגדר נפתרת יחסית לתא שמשתמש בה, שזו תכונה מכוונת של Excel ומקור אמין לבלבול כשהיא יורה בטעות

עריכות מבניות הן המקום שבו הנהלת חשבונות בין-גיליונות משתלמת, וצד ה-XLSX שומר על שמות עקביים דרכן. InsertRows ו-DeleteRows מזיזים טווחי שם-מוגדר יחד עם תאים, מיזוגים, היפר-קישורים, ועוגני תרשים, כך ששם המצביע על Data!$A$2:$D$100 עדיין מכסה את בלוק הנתונים לאחר שהמחולל פותח רווח מעליו. לנוסחאות יש אזהרה מתועדת אחת: הכנסת שורות מתקנת רק את ההפניות שמטרגטות את הגיליון הנערך. נוסחת Summary המפנה ל-Data!D2:D100 נכתבת מחדש כאשר שורות נכנסות ל-Data, שזה המקרה שבדרך כלל רוצים. אמת זאת במקום להניח אותה, כי המנוע יגיד לך בזול:

// מנוע החישוב פותר שמות והפניות בין-גיליוניות בתוך-תהליך
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

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

השמות _xlnm שבבעלות שכבת המאפיינים

פתח את טבלת השמות של קובץ מחולל במפקח ברמה נמוכה ותמצא רשומות שמעולם לא כתבת: _xlnm.Print_Area, _xlnm.Print_Titles, וקרוביהן. אלה האופן שבו OOXML (ECMA-376 / ISO 29500) מאחסן אזורי הדפסה ושורות כותרת חוזרות, כשמות מוגדרים עם מזהים שמורים. HotXLS מנהל אותם דרך מאפייני גיליון ייעודיים, כך שהגדרת PrintArea או PrintTitleRows כותבת את הרשומת _xlnm.* המתאימה עבורך

המלכודת היא להגיע למרחב השמות השמור הזה ביד. הוסף רשומת _xlnm.Print_Area דרך DefinedNames.Add בזמן שאתה גם קובע את מאפיין PrintArea וחוברת העבודה נושאת שתי הגדרות סותרות לשם שמור אחד, מצב ש-Excel פותר בדרכים ששום מוצר לא צריך להסתמך עליהן. התייחס לכל מזהה שמתחיל ב-_xlnm. כשייך לשכבת המאפיינים. לבדיקת הגדרת ההדפסה, קרא את המאפיינים, לא את טבלת השמות. מאמר ההגנה והגדרת העמוד מכסה את מאפייני אזור-ההדפסה בהקשר

שני גבולות שכדאי להכיר לפני שמתחייבים לתכן

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

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

ממשק ה-API המלא של שמות מוגדרים לשני הפורמטים, יחד עם הפניית מנוע הנוסחאות, מופץ עם HotXLS Delphi Component