תקינות סכמה של שדות Pivot ב-XLSX בדלפי עם HotXLS

‏HotXLS כותבת הגדרות של pivot table ב-XLSX שהאלמנטים pivotField ו-cacheField שלהן עוברים את הסכמה של ECMA-376 Part 1 §18.10: מאפייני axis משתמשים בטוקנים של ST_Axis — axisRow, axisCol ו-axisPage, שדות אזור הערכים נושאים dataField="1", רשימות פריטים לעולם לא ריקות, ושדות cache מאחסנים numFmtId מספרי. מאז v2.384.33 הקורא גם מכבד את ברירות המחדל של הסכמה שהוא נהג לפרש לא נכון

הבאגים שמאחורי הניקוי הזה חולקים תכונה לא מחמיאה: אף אחד מהם לא נכשל אף פעם בבדיקה. HotXLS כתבה pivot, HotXLS קראה אותו חזרה, כל שדה נחת על ה-axis הנכון, וסוויטת ה-round trip נשארה ירוקה במשך שנים. הבעיה הייתה שהכותב והקורא הסכימו בשקט על דיאלקט פרטי. pivot שנבנה מדלפי נראה תקין לרכיב שיצר אותו, בזמן שבדיקה מול CT_PivotField ו-CT_CacheField חשפה טוקני enumeration פסולים, אלמנט ריק שהסכמה אוסרת ודגלים ש-Excel מצפה להם ומעולם לא קיבל. אם אתה מייצר pivots על שרת ושולח אותם לאנשים שפותחים אותם ב-Excel או מזרימים אותם ל-parsers שלהם, החוזה היחיד שחשוב הוא הסכמה, לא מה שהקורא שלך במקרה מוחל עליו

למה round trips של HotXLS מעולם לא תפסו את טוקני ה-axis השגויים?

round trips של HotXLS מעולם לא תפסו את טוקני ה-axis השגויים כי הקורא קיבל את שתי האיותים. ה-XlsxPivotAxisAttr הישן פלט axis="rowAxis", colAxis ו-pageAxis, שנקראים טבעיים באנגלית אבל לא קיימים בסכמה; ST_Axis מגדיר בדיוק ארבעה ערכים, axisRow, axisCol, axisPage ו-axisValues. במקביל PivotAxisFromToken ב-lxPivotXml.pas זיהה גם את הטוקן של הסכמה וגם את המומצא, כך שכל בדיקה עצמית עברה. הכותב פולט עכשיו רק את טוקני הסכמה, והקורא ממשיך לקבל את האיותים הישנים כדי שקבצים שנשמרו על ידי גרסאות HotXLS קודמות עדיין ייטענו עם הפריסה שלהם שלמה

<!-- לפני v2.384.33: ערך ST_Axis פסול, CT_Items ריק -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- מאז v2.384.33 -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>

מה CT_PivotField דורש שהכותב הישן דילג עליו?

‏CT_PivotField דורש שלושה דברים שה-BuildPivotTableXml הישן השמיט או עשה לא נכון. ראשון, שדה שנצבר באזור הערכים חייב לומר זאת בהגדרה שלו עצמו עם dataField="1"; הכותב מציב עכשיו את הדגל הזה על כל שדה שמופנה על ידי רשומה ב-DataFields, ולא רק ברשימת ה-<dataFields>. שני, CT_Items זקוק ל-item אחד לפחות, כך ששדה חסר-פריטים כבר לא מקבל <items count="0"> ריק אלא האלמנט כולו פשוט מושמט. שלישי, כל item שומר על המצב שלו: h="1" עבור item מוסתר (TXLSPivotItem.IsHidden) ו-sd="0" עבור פרטים מקופלים (IsDetailHidden), שניהם אותם הכותב הישן זרק בכל שמירה

החלק העדין הוא פריטי המשנה הגולשים. כשלשדה יש items, Excel מונה item נוסף אחד לכל פונקציית משנה אחרי פריטי הנתונים, מוגדר עם ST_ItemType: <item t="default"/> עבור המשנה האוטומטי, ואז sum, countA, avg, max, min, product, count, stdDev, stdDevP, var ו-varP עבור המפורשים. HotXLS גוזרת את הרשומות האלה מ-TXLSPivotField.Subtotals בזמן השמירה וסופרת אותן אל items count. שדות שנוצרו על ידי AddPivotTable מתחילים עם קבוצת Subtotals ריקה, שכותבת defaultSubtotal="0" ובלי item גולש, ולכן בקשו משנים במפורש כשהדוח צריך אותם. שימו לב למלכודת השמות: xlpsCount ממופה ל-countA (כל הרשומות) ו-xlpsCountNums ממופה ל-count (מספרים בלבד)

uses
  lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // מבוסס-1, כמו מנוע ה-XLS
    Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 3, 6, 'RegionTotals');
    if Pivot = nil then
      raise Exception.Create('Bad source range or anchor');

    Region := Pivot.AddRowField('Region');    // nil אם אין שדה כזה
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // מציב dataField="1" על Revenue

    Book.SaveAs('orders-pivot.xlsx');
  finally
    Book.Free;
  end;
end;

איך HotXLS קורא עכשיו פריטי משנה וברירות מחדל של הסכמה?

הקורא של HotXLS מדלג עכשיו על כל item שלמאפיין ה-t שלו יש ערך והוא אינו data, כי רשומות של משנה, סה״כ כללי וריק לא נושאות אינדקס cache. לפני v2.384.34 הרשומות האלה נטענו כ-items רגילים עם CacheItemIndex שהוצב ל-1-, כך ש-pivot שנעשה ב-Excel חזר עם חברי רפאים שלא הצביעו לשום מקום, וכל קוד שעבר על Items היה צריך לסנן אותם ביד. מכיוון שהכותב בונה מחדש את הרשומות הגולשות מתוך Subtotals, תפקיד הקורא הוא לתרגם אותן אל הקבוצה הזאת, ולא לשמר אותן כנתונים

התיקון השני של הקורא עוסק במאפיינים שנעדרים. בסכמה, ל-defaultSubtotal על CT_PivotField ול-containsString על CT_SharedItems ברירת המחדל היא true, ו-Excel משמיט אותם כשהם מחזיקים את ברירת המחדל הזאת. HotXLS נהג לקרוא מאפיין חסר כ-false, מה שאומר שכל pivot שנשמר על ידי Excel איבד בשקט את המשנה של ברירת המחדל שלו בטעינה, ושדה cache של טקסט פשוט סווג כמעורב במקום מחרוזת. זו תמונת המראה של באג ה-axis: כותב שתמיד מאיית כל מאפיין לעולם לא מפעיל את נתיב ברירת המחדל, כך שרק קבצים ממפיק אחר חושפים אותו

למה numFmtId="General" היה פסול על שדות cache?

הערך numFmtId="General" היה פסול כי ST_NumFmtId הוא מספר שלם ללא סימן, לא שם פורמט. הכותב של ה-cache הישן קידד את המחרוזת הזאת באופן קבוע על כל cacheField, בהשאלה את השם שהמשתמשים רואים בדיאלוג Format Cells. HotXLS כותב עכשיו את ה-NumberFormat של שדה ה-cache כמספר, שהוא 0 (פורמט ה-General המובנה) אלא אם משהו קבע אחר. parser מחמיר שמטיפוס מאפיינים מתוך הסכמה דוחה את הערך הישן על הסף, וזו בדיוק משפחת הכשלים שהופכת לדיאלוג תיקון; המאמר על כללי ה-OPC וה-markup מאחורי הודעת התיקון של Excel מסביר איך הדיאלוגים האלה מופעלים

למה pivot tables מתחת לשורה 65535 נחתכו?

pivot tables ב-XLSX שהוצבו בשורה 65536 או מתחתיה נחתכו כי מודל ה-pivot המשותף אחסן את FirstRow, LastRow, FirstHeaderRow, FirstDataRow ומקבילות העמודות כ-Word, וקוד הזזת השורות צמצם אותם עם Min(.., High(Word)). זה שריד מרשומת ה-SxView של BIFF8, שבה 16 סיביות מספיקות, אבל גיליון XLSX מגיע ל-1,048,576 שורות. מאז v2.384.37 המאפיינים האלה על TXLSPivotTable הם Integer, הצמצומים הוסרו, ורק הכותב של BIFF8 מצמצם את הערכים. TXLSXWorksheet.AddPivotTable ו-AddPivotTableCopy מחזירים עכשיו nil עבור עוגן מחוץ ל-1..1048576 על 1..16384, או עבור עותק שטווחו ירוץ מעבר לסריג

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // שורה 70001 התגלגלה בעבר לטווח 16 הסיביות; עכשיו היא שורדת שמירה וטעינה
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // עוגן מחוץ לגיליון או טווח מקור שאי אפשר לפתור
  Pivot.AddRowField('Region');
  Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Book.SaveAs('late.xlsx');

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

מנוע ה-XLS הקלאסי קיבל את התיקון המתאים ב-v2.384.38. המודל שלו אחסן בעבר את הערכים הגולמיים מבוססי-האפס של SxView ו-DConRef והעביר עוגני AddPivotTable ישירות, בזמן שהתיעוד, הדמואים ומנוע ה-XLSX כולם השתמשו בתאים מבוססי-1 כמו Cells[Row, Col]. שני המנועים שומרים עכשיו על מיקומים מבוססי-1 במודל, הקורא של BIFF8 מוסיף 1 והכותב מחסיר 1 בגבול הרשומה, כך שקוד שעגן ב-(0, 0) חייב לעבור ל-(1, 1), כי ה-AddPivotTable הקלאסי מחזיר עכשיו nil עבור עוגן מחוץ ל-1..65536 על 1..256; הקריאה החדשה כותבת את אותם בייטים כמו הישנה. פריסת הרשומות עצמה לא השתנתה ומתוארת ברשומות ה-SX של BIFF8 מאחורי pivot tables של .xls קלאסי

בדקו מול הסכמה, לא מול הקורא שלכם

הלקח מתרחב מעבר ל-pivots: קורא סלחן מסתיר הפרות של הכותב, כך ש-round trip דרך הקוד שלך מוכיח עקביות, לא נכונות. כל באג כאן שרד כי הצד הסלחן והצד הפגום חיו באותה ספרייה. הבדיקות שבאמת תופסות את משפחת הפגמים הזאת הן אימות סכמה של החלקים המיוצרים, קבצים שנוצרו ב-Excel ומוזרמים דרך הקורא שלכם עם מאפיינים מושמטים בברירות המחדל, ו-fixtures שמעגנים את הטוקן המדויק ולא את התוצאה המפורסרת. pivots שאתם בונים דרך ה-API, כולל שדות מחושבים, items מחושבים ופריסות אחוז-מהסך שמוצגות בבנייה ורענון של pivot tables ב-XLSX עם שדות מחושבים, מקבלים את ה-XML המתוקן בלי שינוי קוד, בזמן ש-pivots שנטענו מקבצי Excel ממשיכים להשמיע את החלקים המקוריים שלהם עד שתשנו אותם

כל התיקונים האלה מגיעים ב-רכיב הגיליונות של HotXLS לדלפי הנוכחי, שקורא וכותב XLS, XLSX ו-pivot tables מתוך Delphi ו-C++Builder בלי Excel או COM automation על המכונה