اعتبار اسکیمای فیلد pivot در XLSX با HotXLS دلفی

HotXLS تعاریف جدول‌های pivot در XLSX را طوری می‌نویسد که عناصر pivotField و cacheFieldشان از اسکیمای ECMA-376 بخش 1 §18.10 پاس شود: ویژگی‌های axis از توکن‌های ST_Axis یعنی axisRow، axisCol و axisPage استفاده می‌کنند، فیلدهای ناحیهٔ مقدار dataField="1" حمل می‌کنند، فهرست‌های آیتم هرگز خالی نمی‌مانند و فیلدهای cache یک numFmtId عددی ذخیره می‌کنند. از v2.384.33 به بعد reader هم پیش‌فرض‌های اسکیما را که قبلاً اشتباه می‌گرفت محترم می‌شمارد

باگ‌های پشت این پاک‌سازی یک ویژگی مشترک غیرقابل دفاع دارند: هیچ‌کدام هرگز آزمونی را رد نکردند. HotXLS یک pivot می‌نوشت، HotXLS همان را برمی‌گرداند خواند، هر فیلد روی axis درست می‌نشست و مجموعهٔ آزمون round-trip سال‌ها سبز بود. مشکل این بود که writer و reader بی‌سروصدا روی یک لهجهٔ خصوصی توافق کرده بودند. پیکانی که از دلفی ساخته می‌شد برای همان کامپوننتی که ساخته بودش خوب به نظر می‌رسید، در حالی که یک بررسی در برابر CT_PivotField و CT_CacheField توکن‌های شمارشی نامعتبر، یک عنصر خالی که اسکیما منعش می‌کند و پرچم‌هایی که اکسل انتظار دارد و هرگز نمی‌گرفت را بیرون می‌کشید. اگر روی سرور pivot تولید می‌کنید و به کسانی می‌دهید که در اکسل بازش می‌کنند یا به پارسرهای خودشان می‌دهند، تنها قراردادی که اهمیت دارد اسکیما است، نه هر چیزی که reader خودتان اتفاقی ببخشد

چرا round tripهای HotXLS هرگز توکن‌های axis غلط را نگرفتند؟

round tripهای HotXLS هرگز توکن‌های axis غلط را نگرفتند چون reader هر دو املا را قبول می‌کرد. XlsxPivotAxisAttr قدیمی axis="rowAxis"، colAxis و pageAxis صادر می‌کرد، که در انگلیسی طبیعی خوانده می‌شوند اما در اسکیما وجود ندارند؛ ST_Axis دقیقاً چهار مقدار تعریف می‌کند: axisRow، axisCol، axisPage و axisValues. در همین حین PivotAxisFromToken در lxPivotXml.pas هم توکن اسکیما و هم توکن ابداعی را match می‌کرد، پس هر خودآزمونی قبول می‌شد. writer حالا فقط توکن‌های اسکیما را صادر می‌کند و reader املاهای قدیمی را همچنان می‌پذیرد تا فایل‌های ذخیره‌شده توسط نسخه‌های قبلی 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 چه چیزی می‌خواهد که writer قدیمی جا می‌انداخت؟

CT_PivotField سه چیز می‌خواهد که BuildPivotTableXml قدیمی جا می‌انداخت یا غلط می‌گرفت. اول، فیلدی که در ناحیهٔ مقدار تجمیع می‌شود باید روی تعریف خودش با dataField="1" این را بگوید؛ writer حالا این پرچم را روی هر فیلدی که یک ردیف در DataFields به آن ارجاع می‌دهد می‌گذارد، نه فقط در فهرست <dataFields>. دوم، CT_Items دست‌کم یک item می‌خواهد، پس فیلد بی‌آیتم دیگر <items count="0"> خالی نمی‌گیرد و کل عنصر به‌سادگی حذف می‌شود. سوم، هر آیتم وضعیتش را نگه می‌دارد: h="1" برای آیتم مخفی (TXLSPivotItem.IsHidden) و sd="0" برای جزئیات جمع‌شده (IsDetailHidden)، که writer قدیمی هر دو را در هر ذخیره دور می‌ریخت

بخش ظریف آیتم‌های subtotal انتهایی است. وقتی فیلد آیتم دارد، اکسل بعد از آیتم‌های داده به‌ازای هر تابع subtotal یک item اضافه فهرست می‌کند، با تایپ از ST_ItemType: <item t="default"/> برای subtotal خودکار، بعد sum، countA، avg، max، min، product، count، stdDev، stdDevP، var و varP برای موارد صریح. HotXLS این ردیف‌ها را هنگام ذخیره از TXLSPivotField.Subtotals استخراج و در items count می‌شمارد. فیلدهایی که AddPivotTable می‌سازد با مجموعهٔ Subtotals خالی شروع می‌کنند، که defaultSubtotal="0" و بدون آیتم انتهایی می‌نویسد، پس اگر گزارش به subtotal نیاز دارد صریحاً درخواستش بدهید. به دام نام‌گذاری دقت کنید: 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];                  // یک‌مبنا، مثل موتور 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 حالا آیتم‌های subtotal و پیش‌فرض‌های اسکیما را چطور می‌خواند؟

reader در HotXLS حالا هر itemای که ویژگی t دارد و مقدارش data نیست را رد می‌کند، چون ردیف‌های subtotal، grand-total و خالی هیچ شاخص cache حمل نمی‌کنند. پیش از v2.384.34 این ردیف‌ها به‌عنوان آیتم‌های عادی با CacheItemIndex برابر -1 بار می‌شدند، پس پیکانِ ساختهٔ اکسل با اعضای شبح‌مانندی برمی‌گشت که به هیچ‌جا اشاره نمی‌کردند و هر کدی که Items را می‌گشت باید دستی فیلترشان می‌کرد. چون writer ردیف‌های انتهایی را از Subtotals بازمی‌سازد، کار reader ترجمهٔ آن‌ها به همان مجموعه است، نه نگه داشتن‌شان به‌عنوان داده

fix دوم reader دربارهٔ ویژگی‌های غایب است. در اسکیما، defaultSubtotal روی CT_PivotField و containsString روی CT_SharedItems هر دو پیش‌فرضشان true است و اکسل وقتی همان مقدار پیش‌فرض را دارند آن‌ها را نمی‌نویسد. HotXLS ویژگی غایب را false می‌خواند، که یعنی هر پیکان ذخیره‌شدهٔ اکسل هنگام load بی‌سروصدا subtotal پیش‌فرضش را گم می‌کرد و یک فیلد cache متنی به‌جای string، مخلوط طبقه‌بندی می‌شد. این تصویر آینه‌ایِ باگ axis است: writerی که همیشه همهٔ ویژگی‌ها را می‌نویسد هرگز مسیر پیش‌فرض را ورزش نمی‌دهد، پس فقط فایل‌های تولیدکنندهٔ دیگر آن را لو می‌دهند

چرا numFmtId="General" روی فیلدهای cache نامعتبر بود؟

مقدار numFmtId="General" نامعتبر بود چون ST_NumFmtId یک عدد صحیح بدون علامت است، نه نام یک قالب. writer قدیمی cache آن رشته را روی هر cacheField هاردکد می‌کرد، به عاریه گرفتن نامی که کاربران در پنجرهٔ Format Cells می‌بینند. HotXLS حالا NumberFormat فیلد cache را به‌صورت عدد می‌نویسد، که مگر چیزی آن را ست کرده باشد 0 است (قالب General داخلی). یک پارسر سخت‌گیرانه که ویژگی‌ها را از روی اسکیما تایپ می‌کند مقدار قدیمی را کلاً رد می‌کند، و دقیقاً همین دسته از خرابی است که به پنجرهٔ تعمیر تبدیل می‌شود؛ مقالهٔ قواعد OPC و markup پشت پیام تعمیر اکسل پوشش می‌دهد این پنجره‌ها چطور تحریک می‌شوند

چرا جدول‌های pivot زیر سطر 65535 بریده می‌شدند؟

جدول‌های pivot در XLSX که در سطر 65536 یا پایین‌تر قرار می‌گرفتند بریده می‌شدند چون مدل مشترک pivot مقادیر FirstRow، LastRow، FirstHeaderRow، FirstDataRow و معادل‌های ستونی‌شان را در Word نگه می‌داشت و کد جابه‌جایی سطرها آن‌ها را با Min(.., High(Word)) میخکوب می‌کرد. این میراث رکورد SxView مربوط به BIFF8 است که 16 بیت برایش کافی است، اما شیت XLSX تا 1,048,576 سطر می‌رود. از v2.384.37 به بعد این پراپرتی‌ها روی TXLSPivotTable از نوع Integer هستند، محدودها رفته‌اند و فقط writer مربوط به BIFF8 مقادیر را باریک می‌کند. TXLSXWorksheet.AddPivotTable و AddPivotTableCopy حالا برای لنگری بیرون از 1..1048576 در 1..16384، یا کپی‌ای که دامنه‌اش از شبکه بیرون می‌زند nil برمی‌گردانند

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 fix متناظر را گرفت. مدلش مقادیر خام صفرمبنای SxView و DConRef را نگه می‌داشت و لنگرهای AddPivotTable را مستقیم عبور می‌داد، در حالی که مستندات، دموها و موتور XLSX همگی از سلول‌های یک‌مبنا مثل Cells[Row, Col] استفاده می‌کردند. هر دو موتور حالا موقعیت‌ها را یک‌مبنا در مدل نگه می‌دارند، reader مربوط به BIFF8 یکی اضافه می‌کند و writer در مرز رکورد یکی کم می‌کند، پس کدی که در (0, 0) لنگر می‌انداخت باید به (1, 1) برود، چون AddPivotTable کلاسیک حالا برای لنگری بیرون از 1..65536 در 1..256 مقدار nil برمی‌گرداند؛ فراخوانی جدید همان بایت‌های فراخوانی قدیمی را می‌نویسد. چیدمان رکورد خودش تغییری نکرده و در رکوردهای SX مربوط به BIFF8 پشت جدول‌های pivot در .xls کلاسیک توضیح داده شده

در برابر اسکیما اعتبارسنجی کنید، نه reader خودتان

درس ماجرا از pivot هم فراتر می‌رود: یک reader آسان‌گیر تخلفات writer را پنهان می‌کند، پس یک round trip از میان کد خودتان سازگاری را اثبات می‌کند، نه درستی. هر باگ اینجا زنده مانده چون سمت آسان‌گیر و سمت خراب در یک کتابخانه زندگی می‌کردند. بررسی‌هایی که واقعاً این دسته از عیب را می‌گیرند عبارت‌اند از: اعتبارسنجی اسکیمای بخش‌های تولیدشده، فایل‌های ساختهٔ اکسل که با ویژگی‌های حذف‌شده در پیش‌فرض‌ها از reader شما عبور کنند، و fixtureهایی که توکن دقیق را میخکوب می‌کنند نه نتیجهٔ parse‌شده را. پیکان‌هایی که از طریق API می‌سازید، از جمله فیلدهای محاسباتی، آیتم‌های محاسباتی و چیدمان‌های درصد از کل که در ساخت و تازه‌سازی جدول‌های pivot در XLSX با فیلدهای محاسباتی نشان داده شده، XML اصلاح‌شده را بدون هیچ تغییر در کد می‌گیرند، در حالی که پیکان‌های لودشده از فایل‌های اکسل تا وقتی دستکاری‌شان نکنید بخش‌های اصلی‌شان را بازپخش می‌کنند

همهٔ این fixها در کامپوننت صفحه‌گستردهٔ دلفی HotXLS فعلی عرضه می‌شوند، که از دلفی و C++Builder بدون اکسل و بدون COM automation روی ماشین، XLS، XLSX و جدول‌های pivot را می‌خواند و می‌نویسد