בנייה ורענון של טבלאות ציר (Pivot Tables) של Excel בדלפי עם HotXLS

HotXLS בונה ומרעננת טבלאות ציר (pivot tables) מקוריות של XLSX מדלפי ומ-C++Builder ללא התקנת Excel וללא אוטומציית COM במכונה. אתה קורא ל-AddPivotTable עם טווח מקור, מציב שדות על צירי השורות, העמודות, העמודים והנתונים, מוסיף פריטים מחושבים או תצוגה של אחוז מהסך הכל, והרכיב כותב את חלקי ה-pivotCacheDefinition וה-pivotTableDefinition ש-Excel פותחת כטבלת ציר חיה וניתנת לרענון

התרחיש שהופך את זה לכדאי הוא שרת דוחות. אתה מפיק מאות חוברות עבודה בלילה, כל אחת נושאת טבלת ציר המסכמת חשבון אחד, ובחודש הבא המספרים משתנים וכל קובץ חייב לשקף את שורות המקור החדשות. הרצת Excel מתוך שירות (service) של Windows היא שבירה ואינה מורשית לשימוש בשרתים, וכתיבה ידנית של ה-XML של טבלת הציר היא פרויקט ארכיאולוגיה של מפרטים שלעולם אינו נגמר. HotXLS יושבת בין שני המבויים הסתומים הללו: מודל אובייקטים מוקלד מעל חלקי טבלת הציר של OOXML, כך שאותו קוד Pascal שממלא את התאים גם מצהיר על טבלת הציר ובונה מחדש את המטמון שלה באותו תהליך

כיצד יוצרים טבלת ציר בדלפי ללא Excel?

אתה יוצר אחת בקריאה בודדת ואז מציב שדות על הצירים. AddPivotTable מקבלת את טווח המקור ברישום A1, את תא היעד שבו הטבלה מעוגנת, ושם; היא מנתחת את הטווח, סורקת כל עמודה כדי להסיק את סוג הנתונים שלה, בונה מטמון ציר (או עושה שימוש חוזר בכזה שכבר קשור לאותו טווח), ומחזירה TXLSPivotTable ששדותיו מתחילים כולם מחוץ לציר. משם, שיטות הנוחות AddRowField,‏ AddColumnField,‏ AddPageField ו-AddDataFieldByName מחברות את הפריסה, וכל שדה נתונים מקבל אחד מאחד עשר חישובים מתוך ה-enum ‏TXLSPivotAggregation (כגון xlpaSum,‏ xlpaCount,‏ xlpaAverage,‏ xlpaMax,‏ xlpaMin,‏ xlpaProduct,‏ xlpaCountNums,‏ xlpaStdDev,‏ xlpaStdDevP,‏ xlpaVar,‏ xlpaVarP)

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // report sheet (1-based in the XLSX engine)

    // Source A1:E500 on the 'Data' sheet; anchor the pivot at row 3, col 1.
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

מדוע המטמון (Cache) והטבלה הם שני חלקים נפרדים

טבלת ציר מורכבת למעשה משני רכיבים המפנים זה לזה, והבנת הפיצול היא מה ששומר על הסדר. ה-pivotCacheDefinition הוא תצלום מצב (snapshot) של הנתונים: worksheetSource המצביע על טווח המקור, ו-cacheField אחד לכל עמודה המחזיק את הערכים הייחודיים של אותה עמודה (ה-sharedItems שלה) בתוספת גבולות נגזרים. ה-pivotTableDefinition הוא התצוגה: איזה שדה מטמון יושב על איזה ציר, שדות הנתונים והקיבוצים שלהם, ומתגי הפריסה. התצוגה נקשרת למטמון לפי cacheId דרך קשר ה-pivotCaches של חוברת העבודה, בדיוק כפי שמתארים ECMA-376 Part 1 §18.10 ו-[MS-XLSX]

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

פריטים מחושבים אינם שדות מחושבים

שלושת המונחים הללו מכנים שלושה דברים שונים, ובלבול ביניהם הוא טעות טבלת הציר הקלאסית. פריט מחושב חי בתוך שדה בודד ומשלב את הפריטים של אותו שדה לפי שמם, כך שבתוך שדה Region תוכל להגדיר שורה סינתטית CoreMarkets השווה ל-North פלוס South;‏ HotXLS חושפת זאת כ-TXLSPivotField.AddCalculatedItem וכותבת <calculatedItem> תחת ה-<calculatedItems> של אותו שדה. שדה מחושב הוא שונה: הוא ערך חדש הנגזר מעמודות אחרות, כמו Margin מ-Revenue ו-Cost, והוא נישא כנוסחה בשדה מטמון דרך TXLSPivotCacheField.Formula. איבר מחושב (calculated member), הנוסף באמצעות TXLSPivotTable.AddCalculatedMember, הוא איבר מותאם אישית ברמת הטבלה שיכול לשמש כמדד (סוג איבר data) או כאיבר מימד, בעיקר לטבלאות ציר בעלות מבנה OLAP

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // A calculated ITEM combines items of a single field by name.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // A calculated MEMBER is declared at the table level. Member type
  // 'data' marks it as a measure; an empty type is a dimension member.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

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

כיצד מציגים ערכים כאחוז מהסך הכל?

אתה מגדיר את מצב תצוגת הערך בשדה הנתונים במקום להמיר את המספרים בעצמך. TXLSPivotDataField.ShowDataAs מקבל את ה-enum ‏TXLSPivotShowDataAs, המשקף את ערכי ה-OOXML ‏ST_ShowDataAs: ‏xlpsdaNormal,‏ xlpsdaDifference,‏ xlpsdaPercent,‏ xlpsdaPercentDiff,‏ xlpsdaRunTotal,‏ xlpsdaPercentOfRow,‏ xlpsdaPercentOfCol,‏ xlpsdaPercentOfTotal ו-xlpsdaIndex. טריק נפוץ הוא להציב את אותה עמודת מקור על ציר הנתונים פעמיים, פעם אחת כסכום גולמי ופעם אחת כחלק מהסך הכל, כך שהדוח מציג הן את המספר והן את משקלו

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // Item-relative modes need a base to compare against. A running total
  // down 'Quarter' (cache-field index 3) would read:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // cache-field index the transform runs over
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

xlpsdaPercentOfTotal אינו זקוק לבסיס מכיוון שהוא יחסי לסך הכל הכללי, אך המצבים היחסיים לפריטים זקוקים לו. xlpsdaDifference,‏ xlpsdaPercentDiff ו-xlpsdaRunTotal דורשים BaseField (אינדקס שדה המטמון שעליו רצה ההשוואה) ועבור הצורות המעוגנות בפריטים, אינדקס BaseItem, שבו $7FFD מייצג את הזקיף (All). HotXLS כותבת אותם כמאפייני <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> וכמו בנוסחאות מחושבות, משאירה את האריתמטיקה ל-Excel

קיבוץ תאריכים ומספרים, ומסנני שדות עמודים

קיבוץ מוגדר בשדה המטמון, לא בשדה ציר, מכיוון שהוא משנה את האופן שבו תחום המקור מחולק לדליים (buckets). הגדר HasGroup := True בשדה המטמון המוחזר על ידי Cache.FindFieldByName, ואז בחר בלחצני הטווח המספרי (GroupStartNum,‏ GroupEndNum,‏ GroupInterval) או בדгלי היררכיית התאריכים (GroupByDate עם GroupMonths,‏ GroupQuarters,‏ GroupYears וטווח GroupStartDate / GroupEndDate). HotXLS פולטת את אלמנט ה-<fieldGroup><rangePr groupBy="months"/> המתאים כך ששדה המקובץ לפי חודש או לפי פס מספרי ברוחב 1000 נפתח כפי ש-Excel הייתה מקבצת אותו

שדות עמוד הם תפריטי הסינון הנפתחים מעל הטבלה. AddPageField מציבה שדה על ציר העמודים, ו-PageItemIndex בוחרת מראש פריט מטמון בודד, ברירת מחדל xlPageItemAll ($7FFD, שמשמעותו (All)). כדי לאפשר לקורא לסמן מספר פריטים בו-זמנית, הגדר MultipleItemSelectionAllowed := True, ש-HotXLS כותבת כ-<pivotField multipleItemSelectionAllowed="1"/>. עבור קריטריונים מעבר לבחירה ידנית, כל שדה נושא אוסף Filters מוקלד המקיף את משפחות ה-OOXML ‏ST_FilterType — מסנני ספירה, אחוז, סכום, כותרת, ערך ותאריך — כל רשומה מצמדת סוג מסנן עם ערכי ההשוואה שלו

כיצד מרעננים את מטמון טבלת הציר כאשר המקור משתנה?

RefreshPivotCache ב-TXLSXWorkbook סורקת מחדש את טווח המקור ובונה מחדש את המטמון במקומו, וזה מה שצינור עיבוד אצווה צריך לאחר שהוא עורך את השורות הבסיסיות. העבר את מזהה המטמון (cache id), והשיטה תקרא מחדש כל תא בטווח המקור (כותרת בשורה הראשונה, נתונים מהשנייה), תבנה מחדש את תחום הפריטים המשותפים של כל שדה על ידי הסרת כפילויות מחדש וגזירה מחדש של הגבולות לכל סוג, ותכתוב מחדש את אינדקסי הפריטים לכל רשומה. היא מחזירה 1 במקרה של הצלחה ו--1 כאשר מזהה המטמון אינו ידוע או שגיליון המקור חסר

var
  Rc: Integer;
begin
  // ...the source data has changed since the pivot was built...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // a corrected Revenue figure

  // Rebuild the cache's shared items and record indices from the source.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = refreshed, -1 = cache/source missing
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

הגבול הסמנטי כאן ראוי לציון ברור. לפני ששיטה זו הייתה קיימת, HotXLS הסתמכה על דגל refreshOnLoad="1" והשאירה את כל עבודת הרענון ל-Excel בפתיחה הבאה, וזה בסדר כאשר אדם יפתח את הקובץ אך חסר תועלת בצינור עיבוד ללא ממשק משתמש (headless) שצריך למסור נתונים נכונים. RefreshPivotCache הופכת את המטמון השמור לעדכני בתוך התהליך, ומכיוון שטבלאות נקשרות למטמון לפי מזהה, כל טבלת ציר הנגזרת מאותו מטמון רואה את העדכון. מה שהיא אינה עושה זה לפרוס או לקבץ את הגריד הגלוי — Excel עדיין מחשבת מחדש את הטבלה המרונדרת מהמטמון המרוענן כאשר הקובץ נפתח

מה ש-HotXLS כותבת ומה ש-Excel מחשבת

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

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

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