כמעט כל חלק בפורמט הבינארי הישן של Excel הוא רשומה בודדת עם טיפוס נקי של שני בתים ואורך של שני בתים. תא הוא LABELSST או NUMBER. אזור ממוזג הוא MERGEDCELLS. אתה יכול לקרוא את רוב גיליון העבודה על ידי מעבר על הרשומות אחת בכל פעם וניתוב לפי מילת הטיפוס. טבלאות ציר (PivotTables) שוברות את המקצב הזה. טבלת ציר בודדת אינה רשומה, היא תוכנית קטנה המורכבת מעשרות רשומות המשתפות פעולה המפוזרות בשני מקומות שונים באותו זרם מסמכים מורכב של OLE, והקשרים ביניהן הם מיקומיים, דחוסים בסיביות (bit-packed) ובלתי סלחניים. זהו המבנה שרוב קוראי BIFF8 מדלגים עליו לחלוטין או משמרים כבתים אטומים, מכיוון שכתיבת אחד מאפס פירושה שחזור כל הפניה צולבת ש-Excel עצמו מתחזק.
הסיבה שטבלת ציר היא קשה היא שמדובר למעשה בשני תוצרים המולחמים יחד. ישנו מטמון הציר (pivot cache), שהוא צילום מצב עצמאי של נתוני המקור עם תת-זרם משלו, וישנה תצוגת הטבלה (table view), שהיא הפריסה שאומרת אילו שדות יושבים על איזה ציר. המטמון והתצוגה מתייחסים זה לזה לפי אינדקס. טעה באינדקס אחד והקובץ ייפתח לשגיאת רענון או לגריד ריק בשקט.
המשמעות של מטמון הציר כתת-זרם בפני עצמו
המטמון חי בזרם הגלובלי של חוברת העבודה (workbook globals stream) כתת-זרם BIFF מלא, המתוחם ברשומת BOF שסוג המסמך שלה הוא 0x0006 (הערך המסמן מטמון ציר, בניגוד ל-0x0005 עבור חוברת העבודה או 0x0010 עבור גיליון עבודה) ונסגר על ידי ה-EOF התואם. בתוך אותה מסגרת המבנה קבוע. רשומת SXDB היא כותרת המטמון. היא נושאת את ספירת הרשומות, מספר שדות המטמון ומזהה הזרם שתצוגת הטבלה תצטט כדי לקשור את עצמה למטמון זה. כל עמודת מקור תורמת אז רשומת הגדרת שדה SXFDB ולאחריה SXFDBType שמסווג אותה, ואז את הערכים הייחודיים שהעמודה קיבלה, הנפלטים כרשומת פריט מוגדרת-טיפוס אחת לכל ערך ייחודי.
רשומות הפריטים הן המקום שבו המטמון מוכיח את יעילותו. ערך טקסט הופך ל-SXSTRING, ערך מספרי ל-SXNUM, ערך לוגי ל-SXBOOLEAN, ושגיאת נוסחה ל-SXERR. המטמון אינו שומר את גריד המקור, הוא שומר את הערכים הייחודיים לכל שדה בתוספת טבלת אינדקסים שאומרת, עבור רשומה n, איזה פריט ייחודי כל שדה קיבל. לכן בניית טבלת ציר באופן תכנותי אינה עניין של העתקת תאים. עליך לסרוק את טווח המקור, להסיק את הטיפוס של כל שדה מהערכים שהוא מחזיק, לבצע להם דה-דופליקציה לרשימת פריטים מוגדרת-טיפוס, ולרשום כל שורה כרשומה (tuple) של אינדקסי פריטים. HotXLS עושה בדיוק את זה: עמודה מספרית לחלוטין נפלטת עם פריטי SXNUM, עמודת טקסט מעורבת הופכת לפריטי SXSTRING, ותאריכים מועברים כערכים סידוריים (serial values) דרך אותו נתיב מספרי.
SXDBB ודחיסת הסיביות שהופכת את זה למעניין
טבלת האינדקסים לכל רשומה היא החלק המסקרן ביותר מבחינה טכנית בכל המבנה, והיא חיה ברשומת SXDBB. הקידוד הפשוט היה שומר את אינדקס הפריט של כל שדה כמילה של 16 סיביות. Excel אינו עושה זאת. הוא דוחס את האינדקס של כל שדה בדיוק למספר הסיביות הנדרש כדי לפנות לפריטים של אותו שדה, ולא יותר. הרוחב הוא ceil(log2(itemCount + 1)) סיביות. ה-+ 1 משנה: הערך הנוסף הוא סימן שפירושו "ריק, אין ערך לשדה זה ברשומה זו", כך ששדה עם שלושה פריטים ייחודיים צריך לייצג ארבעה מצבים ולכן לוקח שתי סיביות, ולא הסיבית האחת ששלושה פריטים בלבד היו מציעים. שדה ללא פריטים כלל תורם אפס סיביות ונשמט לחלוטין במהלך הדחיסה.
הסיביות עבור רשומה אחת משורשרות על פני כל השדות, ואז הרשומה הבאה מתחילה בגבול בית חדש. רשומות מיושרות לפי בתים (byte-aligned), ולא דחוסות סיביות מקצה לקצה, מה שהופך גישה אקראית לתוך הטבלה לניתנת לניהול במחיר של כמה סיביות ריפוד (padding) לכל שורה. הדחיסה בתוך בית היא הסיבית הפחות משמעותית תחילה (least-significant-bit first). ברגע שאתה מקבל את שני הכללים הללו המקודד הוא משאבת סיביות פשוטה, והמפענח הוא המראה שלו.
// Width of one field's index in the SXDBB stream.
// citmTotal distinct items need ceil(log2(citmTotal + 1)) bits,
// the +1 reserving a "blank" sentinel value.
function BitsForFieldItems(itemCount: Integer): Integer;
var
capacity: Integer;
begin
Result := 0;
if itemCount <= 0 then
Exit; // empty field contributes zero bits
Result := 1;
capacity := 2;
while capacity < itemCount + 1 do
begin
Inc(Result);
capacity := capacity * 2;
end;
end;הסיבה שלא ניתן להתעלם מפרט זה היא תקרת ה-8224 בתים לרשומת BIFF בודדת. Every record in the format, pivot records included, must fit its payload in at most 8224 bytes, and a busy pivot cache with thousands of source rows will blow past that long before it has emitted every row. So the index table is split. HotXLS caps a single SXDBB body at 8220 bytes, which is the 8224 record limit minus the four-byte record header of type and length, divides that by the byte width of one packed record to learn how many whole rows fit, and then emits as many continued SXDBB records as the row count demands. Each continuation restarts cleanly on a record boundary, so no row is ever cut across two records. A reader that knows the per-record bit width can stride through every SXDBB in sequence as if they were one contiguous bit array.
פריסת התצוגה: SXLI עבור הגוף, SXPI עבור העמוד
שורות הגוף של טבלת הציר המפרטות כל שילוב של שורות ועמודים נישאות ברשומות SXLI (סוג רשומה 0x00B5, המתואר ב-[MS-XLS] §2.4.275). רשומת SXLI אחת מחזיקה שורות רבות, שוב עד שהמגבלה של 8224 בתים מאלצת רשומה חדשה, והיא משתמשת בטריק דחיסה קטן: כל שורה שומרת רק את ההפרש שלה מהשורה שמעליה, המבוטא כספירת קידומת משותפת, כך שציר מקונן עמוק אינו חוזר על ערכי השדות החיצוניים בכל שורה. שורת הסך הכל הכללי והשורה הראשונה של כל רשומה מאפסות תמיד את ספירת הקידומת לאפס כך שקורא לעולם אינו צריך להביט לאחור מעבר לגבול רשומה כדי לשחזר שורה.
ציר העמוד, תפריטי הסינון הנפתחים היושבים מעל טבלת ציר, הוא רשומה נפרדת. SXPI (סוג רשומה 0x00B6, [MS-XLS] §2.4.276) נושא רשומה אחת בת עשרה בתים לכל שדה עמוד: אינדקס שדה הציר isxvd, פריט המטמון שנבחר iCache, מילת מיקום ipos ומזהה אובייקט ישן objId. ערך ה-iCache הוא זה שיש לעקוב אחריו. שהד עמוד המציג "(All)", שאינו מסנן דבר, שומר את הסימן 0x7FFD במקום אינדקס פריט אמיתי. ציר שנבנה תכנותית נפתח כאשר כל שדה עמוד מוגדר כ-"(All)" עד שהקורא בוחר מראש פריט, ובשלב זה אינדקס המטמון של אותו פריט מחליף את הסימן ו-Excel נפתח כאשר המסנן כבר מוחל. לצד אלו יושבות הרשומות התומכות המתארות שדות בודדים ואת העיצוב שלהם, SXVD ו-SXVDEx להגדרות תצוגת שדה, SXIVD לרשימות אינדקס השדות המדרגות כל ציר, ו-SXFormat לעיצוב מספרים, שכל אחת מהן מאנדקסת בחזרה לאותו מטמון ששורות הגוף מתייחסות אליו.
שני כותבים באחד: raw blobs והמודל המוגדר-טיפוס
ישנה סיבה מבנית לכך ש-HotXLS מחזיק שני נתיבים נפרדים לחלוטין לכתיבת טבלת ציר, והיא מגיעה ישר מדרישות הדיוק. כאשר חוברת עבודה נקראת מהדיסק, רשומות הציר שלה נכתבו על ידי Excel או על ידי יצרן אחר כלשהו, והן עשויות להשתמש בגרסאות רשומות, מוזרויות סדר או רשומות הרחבה ששום כותב צד שלישי אינו ממדל במלואן. הדבר הבטוח היחיד לעשות עם בתים אלה הוא להחזיר אותם ללא שינוי. לכן טבלת ציר שהגיעה מקובץ מסומנת ב-FromRawBlobs = True, ובשמירה הכותב מציג שוב את רשומות ה-blob שנשמרו מילה במילה. שום דבר אינו מיוצר מחדש, שום דבר אינו מפורש מחדש, וסבב מלא דרך פתיחה ושמירה הוא יציב ברמת הבתים.
טבלת ציר שהתוכנית בנתה היא המקרה ההפוך. אין בתים מקוריים לשמר, רק את מודל האובייקטים המוגדר-טיפוס: TXLSPivotCache עם השדות ורשימות הפריטים שלו, ו-TXLSPivotTable עם שיוכי הצירים שלו. טבלה זו מסומנת כ-FromRawBlobs = False, והכותב מבצע לה סיראליזציה בדרך הקשה, תוך פליטת תת-זרם מטמון BOF = 0x0006 חדש, דחיסת טבלת האינדקסים SXDBB מאינדקסי הפריטים שמודל האובייקטים מחזיק, ופריסת רשומות ה-SXLI וה-SXPI מהגדרות הציר. הדגל הוא מה שמאפשר לשני הסוגים להתקיים יחד בחוברת עבודה אחת. בלעדיו כותב יחיד היה צריך או להשליך את הדיוק של טבלאות שנקראו או לסרב לייצר חדשות. כל רשומות הרחבה ספציפיות ליצרן שטבלה שנקראה נשאה נשמרות כרשומות משלימות, הניתנות לגישה דרך רשימת ה-SupplementalRecords של הטבלה, כך שטבלה שנבדקת דרך המודל המוגדר-טיפוס אינה מאבדת את החלקים שהמודל אינו מתאר.
בניית טבלת ציר בקוד
כל המנגנון שלעיל יושב מאחורי קריאה אחת. המתודה AddPivotTable מקבלת את טווח המקור בסימון A1, את תא היעד שבו פינתה השמאלית-עליונה של הטבלה מעוגנת, ושם. היא מנתחת את הטווח, סורקת אותו כדי להסיק את טיפוסי השדות ולבנות את המטמון (תוך שימוש חוזר במטמון קיים אם טבלה אחרת כבר נקשרת לאותו טווח), ומחזירה TXLSPivotTable מוגדר-טיפוס עם שדה אחד לכל עמודת מקור, כאשר כל שדה מחוץ לציר בהתחלה. אתה מציב אז שדות על הצירים ובוחר אגרגציה. החתימה היא בדיוק זו, והמטמון, דחיסת SXDBB ורשומות התצוגה מיוצרים כולם עבורך בזמן השמירה.
uses
lxHandle, lxPivot;
var
Book : TXLSWorkbook;
Sheet: IXLSWorkSheet;
Pivot: TXLSPivotTable;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('Sales.xls');
Sheet := Book.Sheets[1];
// Source A1:E500 on 'Data'; 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.AddDataFieldByName('Revenue', xlpaSum);
end;
Book.SaveAs('Sales-Pivot.xls');
finally
Book.Free;
end;
end;השורה הראשונה של טווח המקור נקראת ככותרת המציינת את שמות שדות המטמון, כך ש-AddRowField('Region') מתאים לעמודה לפי טקסט הכותרת שלה ולא לפי מיקום. מכיוון שהטבלה המוחזרת היא מודל מוגדר-טיפוס עם FromRawBlobs = False, הכותב לוקח את הנתיב מאפס: הוא בונה מטמון עצמאי שאינו תלוי בכך שטווח המקור עדיין יהיה קיים בזמן הרענון, שזה בדיוק המאפיין שאתה רוצר כאשר טבלת הציר תישלח לנמען שעשוי להעביר או למחוק את הנתונים הבסיסיים.
קריאה והתאמה של רשומות ציר ומטמון של קובץ שלא אתה הפקת, כולל נתיב שימור ה-raw-blob, מכוסות ב-מדריך ביקורת חוברת עבודה וסביבת עבודה להמרה. כאשר טווח המקור מגיע לעשרות אלפי שורות וזרם ה-SXDBB משתרע על פני רשומות המשכיות רבות, הטכניקות ב-הערות על ביצועי חוברת עבודה גדולה מונעות מבניית המטמון להשתלט על זמן הריצה שלך. שניהם משתלבים עם כותב הציר שנשלח בתוך HotXLS spreadsheet component עבור Delphi ו-C++Builder לצד ממשקי ה-API לתא, נוסחה, תרשים ועיצוב המכוסים במקומות אחרים בבלוג זה.