نوشتن جداول محوری BIFF8 در Delphi: متدهای SXDB و SXLI

تقریباً هر بخشی از فرمت باینری قدیمی Excel یک رکورد واحد با یک نوع تمیز دو بایتی و طول دو بایتی است. یک سلول یک LABELSST یا یک NUMBER است. یک ناحیه ادغام‌شده یک MERGEDCELLS است. شما می‌توانید بیشتر یک کاربرگ را با پیمایش تک‌تک رکوردها در هر زمان و ارسال بر اساس کلمه نوع بخوانید. اما جداول محوری (PivotTables) این ریتم را می‌شکنند. یک جدول محوری واحد یک رکورد نیست، بلکه برنامه کوچکی است که از ده‌ها رکورد همکاری‌کننده در دو مکان مختلف در همان جریان سند مرکب OLE ساخته شده است، و روابط بین آن‌ها موقعیتی، بسته‌بندی‌شده با بیت و نابخشودنی است. این همان ساختاری است که اکثر خوانندگان BIFF8 یا کاملاً از آن چشم‌پوشی می‌کنند یا آن را به عنوان بایت‌های غیرشفاف حفظ می‌نمایند، زیرا نوشتن یکی از آن‌ها از ابتدا به معنای بازتولید هر ارجاع متقابلی است که خود Excel نگهداری می‌کند

دلیل سخت بودن یک جدول محوری این است که در واقع دو محصول به هم جوش‌خورده است. کش محوری (Pivot cache) وجود دارد که یک پیش‌نمایش خودکفا از داده‌های منبع با جریان فرعی خود است، و نمای جدول (Table view) وجود دارد که طرح‌بندی مشخص‌کننده قرارگیری فیلدها در هر محور است. کش و نما از طریق نمایه به یکدیگر ارجاع می‌دهند. اگر یک نمایه را اشتباه بگیرید، فایل با خطای به‌روزرسانی یا یک شبکه بی‌صدا خالی باز می‌شود

کش محوری خود یک جریان فرعی است

کش در جریان عمومی کتاب کار به عنوان یک جریان فرعی کامل BIFF قرار دارد که توسط یک رکورد BOF قاب‌بندی شده که نوع سند آن 0x0006 است (مقداری که یک کش محوری را علامت‌گذاری می‌کند، در مقابل 0x0005 برای کتاب کار یا 0x0010 برای یک کاربرگ) و توسط EOF مطابقت بسته می‌شود. در داخل آن قاب ساختار ثابت است. یک رکورد SXDB هدر کش است. این رکورد تعداد رکوردها، تعداد فیلدهای کش و شناسه جریانی را که نمای جدول برای اتصال خود به این کش نقل قول می‌کند، حمل می‌نماید. سپس هر ستون منبع یک رکورد تعریف فیلد SXFDB و به دنبال آن یک SXFDBType را که آن را طبقه‌بندی می‌کند، و سپس مقادیر منحصربه‌فردی را که آن ستون به خود اختصاص داده است، به صورت یک رکورد آیتم تایپ‌شده برای هر مقدار متمایز صادر می‌سازد

رکوردهای آیتم جایی هستند که کش ارزش خود را نشان می‌دهد. یک مقدار متنی تبدیل به یک SXSTRING، یک مقدار عددی تبدیل به SXNUM، یک مقدار منطقی تبدیل به SXBOOLEAN و خطای فرمول تبدیل به SXERR می‌شود. کش شبکه منبع را ذخیره نمی‌کند، بلکه مقادیر متمایز در هر فیلد به همراه یک جدول نمایه را ذخیره می‌سازد که نشان می‌دهد برای رکورد n، هر فیلد کدام آیتم متمایز را به خود گرفته است. به همین دلیل است که ساخت یک جدول محوری به صورت برنامه‌نویسی، موضوع کپی کردن سلول‌ها نیست. شما باید محدوده منبع را اسکن کنید، نوع هر فیلد را از روی مقادیری که نگه می‌دارد استنتاج کنید، آن‌ها را در یک لیست آیتم تایپ‌شده حذف تکرار نمایید و هر ردیف را به عنوان یک تاپل از نمایه‌های آیتم ثبت کنید. نرم‌افزار HotXLS دقیقاً این کار را انجام می‌دهد: یک ستون کاملاً عددی با آیتم‌های SXNUM صادر می‌شود، یک ستون متنی مخلوط تبدیل به آیتم‌های SXSTRING می‌شود و تاریخ‌ها به عنوان مقادیر سریال از طریق همان مسیر عددی منتقل می‌گردند

رکورد SXDBB و بسته‌بندی بیتی که آن را جالب می‌کند

جدول نمایه برای هر رکورد، تنها بخش فنی کنجکاوی‌برانگیز کل ساختار است و در رکورد SXDBB زندگی می‌کند. رمزگذاری ساده، نمایه آیتم هر فیلد را به عنوان یک کلمه ۱۶ بیتی ذخیره می‌کند. اما Excel این کار را انجام نمی‌دهد. بلکه نمایه هر فیلد را دقیقاً در تعداد بیت‌های مورد نیاز برای آدرس‌دهی آیتم‌های آن فیلد بسته‌بندی می‌کند و نه بیشتر. عرض آن برابر با ceil(log2(itemCount + 1)) بیت است. مقدار + 1 مهم است: مقدار اضافی یک نگهبان به معنای "خالی، بدون مقدار برای این فیلد در این رکورد" است، بنابراین فیلدی با سه آیتم متمایز نیاز به نمایش چهار حالت دارد و بنابراین دو بیت می‌گیرد، نه یک بیتی که سه آیتم به تنهایی نشان می‌دهند. فیلدی بدون آیتم اصلاً هیچ بیتی ایجاد نمی‌کند و در طول بسته‌بندی به طور کامل نادیده گرفته می‌شود

بیت‌های یک رکورد در تمام فیلدها به هم متصل می‌شوند، سپس رکورد بعدی در یک مرز بایت جدید شروع می‌شود. رکوردها تراز بایت دارند، نه اینکه بیت‌ها از ابتدا تا انتها بسته‌بندی شوند، که این کار دسترسی تصادفی به جدول را به قیمت چند بیت پدینگ در هر ردیف آسان می‌سازد. بسته‌بندی در داخل یک بایت، ابتدا با کم‌ارزش‌ترین بیت (least-significant-bit) انجام می‌شود. هنگامی که این دو قانون را پذیرفتید، رمزگذار یک پمپ بیتی ساده است و رمزگشا آینه آن است

// 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;

دلیل اینکه این جزئیات را نمی‌توان نادیده گرفت، سقف ۸۲۲۴ بایتی در یک رکورد واحد BIFF است. هر رکورد در این فرمت، از جمله رکوردهای محوری، باید داده‌های خود را حداکثر در ۸۲۲۴ بایت جا دهد، و یک کش محوری شلوغ با هزاران ردیف منبع، بسیار قبل از اینکه هر ردیفی را صادر کند، از آن فراتر خواهد رفت. بنابراین جدول نمایه تقسیم می‌شود. نرم‌افزار HotXLS بدنه یک SXDBB واحد را به ۸۲۲۰ بایت محدود می‌کند که همان محدودیت ۸۲۲۴ رکوردی منهای هدر رکورد چهار بایتی نوع و طول است، آن را بر عرض بایت یک رکورد بسته‌بندی‌شده تقسیم می‌کند تا بدنه بداند چه تعداد ردیف کامل جا می‌شود، و سپس به تعداد مورد نیاز رکوردهای ادامه SXDBB را صادر می‌نماید. هر ادامه به طور تمیز در یک مرز رکورد دوباره شروع می‌شود، بنابراین هیچ ردیفی هرگز بین دو رکورد قطع نمی‌گردد. خواننده‌ای که پهنای بیت هر رکورد را می‌داند، می‌تواند در تمام رکوردهای SXDBB به ترتیب قدم بگذارد گویی که آن‌ها یک آرایه بیتی پیوسته هستند

طرح‌بندی نما: SXLI برای بدنه، SXPI برای صفحه

با ساخته شدن کش، نمای جدول نیمه دوم کار است. هسته آن آیتم‌های خط محور است، یعنی ردیف‌های بدنه محوری که هر ترکیبی از مقادیر فیلد ردیف و فیلد ستون را که جدول رسم می‌کند شمارش می‌نمایند. این‌ها در رکوردهای SXLI حمل می‌شوند (نوع رکورد 0x00B5 که در بخش ۲.۴.۲۷۵ سند [MS-XLS] توصیف شده است). یک رکورد SXLI خطوط زیادی را نگه می‌دارد، دوباره تا زمانی که محدودیت ۸۲۲۴ بایتی رکورد جدیدی را تحمیل کند، و از یک ترفند فشرده‌سازی کوچک استفاده می‌نماید: هر خط فقط تفاوت خود را با خط بالای خود ذخیره می‌کند که به صورت شمارش پیشوند مشترک بیان می‌شود، بنابراین یک محور عمیقاً تو در تو، مقادیر فیلد بیرونی را در هر ردیف تکرار نمی‌کند. خط مجموع کل و اولین خط هر رکورد همیشه آن شمارش پیشوند را به صفر بازنشانی می‌کنند تا خواننده هرگز مجبور نباشد برای بازسازی یک خط به عقب و از روی مرز رکورد نگاه کند

محور صفحه، یعنی همان منوهای کشویی فیلتر که بالای یک جدول محوری قرار می‌گیرند، یک رکورد مجزاست. رکورد SXPI (نوع رکورد 0x00B6، بخش ۲.۴.۲۷۶ سند [MS-XLS]) یک ورودی ده بایتی به ازای هر فیلد صفحه حمل می‌کند: نمایه فیلد محوری isxvd، آیتم کش انتخاب‌شده iCache، کلمه موقعیت ipos، و شناسه شیء قدیمی objId. مقدار iCache موردی است که باید به آن دقت کرد. فیلد صفحه‌ای که مقدار "(All)" را نشان می‌دهد و چیزی را فیلتر نمی‌کند، به جای نمایه آیتم واقعی، نگهبان 0x7FFD را ذخیره می‌سازد. یک جدول محوری ساخته‌شده به صورت برنامه‌نویسی با تنظیم همه فیلدهای صفحه روی "(All)" باز می‌شود تا زمانی که فراخوان‌کننده یک آیتم را پیش‌انتخاب کند، در این مرحله نمایه کش آن آیتم جایگزین نگهبان شده و Excel با فیلتر از قبل اعمال‌شده باز می‌شود. در کنار این‌ها، رکوردهای پشتیبان قرار دارند که فیلدهای جداگانه و قالب‌بندی آن‌ها را توصیف می‌کنند: SXVD و SXVDEx برای تعاریف نمای فیلد، SXIVD برای لیست‌های نمایه فیلد که به هر محور نظم می‌دهند، و SXFormat برای قالب‌بندی اعداد، که هرکدام به همان کشی که خطوط بدنه به آن اشاره دارند، ارجاع می‌دهند

دو نویسنده در یک قالب: بلاک‌های خام و مدل تایپ‌شده

یک دلیل ساختاری وجود دارد که چرا HotXLS دو مسیر کاملاً جداگانه را برای نوشتن یک جدول محوری نگه می‌دارد، و این موضوع مستقیماً از نیاز به وفاداری به ساختار اصلی ناشی می‌شود. هنگامی که یک کتاب کار از روی دیسک خوانده می‌شود، رکوردهای محوری آن توسط Excel یا تولیدکننده دیگری نوشته شده‌اند و ممکن است از انواع رکورد، رفتارهای خاص مرتب‌سازی، یا رکوردهای توسعه استفاده کنند که هیچ نویسنده شخص ثالثی به طور کامل آن‌ها را مدل‌سازی نمی‌کند. تنها کار ایمن با آن بایت‌ها، بازگرداندن بدون تغییر آن‌هاست. سو یک جدول محوری که از یک فایل آمده با پرچم FromRawBlobs = True علامت‌گذاری می‌شود و در هنگام ذخیره، نویسنده بلاک‌های رکورد حفظ‌شده را به صورت کلمه به کلمه پخش می‌کند. هیچ چیز دوباره تولید نمی‌شود، هیچ چیز دوباره تفسیر نمی‌گردد، و فرآیند رفت و برگشت از طریق باز کردن و ذخیره کردن، از نظر بایت پایدار است

جدول محوری که برنامه ساخته است، مورد معکوس است. هیچ بایت اصلی برای حفظ کردن وجود ندارد، فقط مدل شیء تایپ‌شده وجود دارد: یک TXLSPivotCache با فیلدها و لیست‌های آیتم آن، و یک TXLSPivotTable با تخصیص‌های محور آن. آن جدول با پرچم FromRawBlobs = False علامت‌گذاری می‌شود و نویسنده آن را به روش سخت سریال‌سازی می‌کند، یک جریان فرعی کش تازه BOF = 0x0006 صادر می‌کند، جدول نمایه SXDBB را از روی نمایه‌های آیتمی که مدل تایپ‌شده دارد بسته‌بندی می‌کند، و رکوردهای SXLI و SXPI را بر اساس پیکربندی محور می‌چیند. این پرچم چیزی است که به هر دو نوع اجازه می‌دهد در یک کتاب کار همزیستی داشته باشند. بدون آن، یک نویسنده واحد باید یا وفاداری به جداول خوانده‌شده را کنار می‌گذاشت یا از تولید جداول جدید امتناع می‌کرد. هرگونه رکورد توسعه مخصوص تولیدکننده که یک جدول خوانده‌شده حمل می‌کرد، به عنوان رکوردهای تکمیلی نگهداری می‌شود که از طریق لیست SupplementalRecords جدول قابل دسترسی است، بنابراین جدولی که از طریق مدل تایپ‌شده بازرسی می‌شود، بخش‌هایی را که مدل توصیف نمی‌کند از دست نمی‌دهد

ساخت جدول محوری در کد

تمام ماشین‌آلات بالا در پشت یک فراخوانی قرار دارند. متد AddPivotTable محدوده منبع را در نماد A1، سلول مقصد که گوشه بالا سمت چپ جدول در آن مهار می‌شود، و یک نام را می‌گیرد. این متد محدوده را تجزیه می‌کند، آن را اسکن می‌کند تا انواع فیلد را استنتاج کند و کش را بسازد (در صورت اتصال جدول دیگری به همان محدوده، از کش موجود مجدداً استفاده می‌کند)، و یک TXLSPivotTable تایپ‌شده با یک فیلد به ازای هر ستون منبع برمی‌گرداند که هر فیلد در ابتدا خارج از محور است. سپس فیلدها را روی محورها قرار می‌دهید و یک تجمع (Aggregation) انتخاب می‌کنید. امضا دقیقاً به این شکل است، و کش، بسته‌بندی 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 است، نویسنده مسیر شروع از صفر را پیش می‌گیرد: یک کش خودکفا می‌سازد که به حضور محدوده منبع در زمان به‌روزرسانی بستگی ندارد، که این دقیقاً همان ویژگی است که می‌خواهید وقتی جدول محوری به گیرنده‌ای ارسال می‌شود که ممکن است داده‌های اصلی را جابه‌جا یا حذف کند

خواندن و تطبیق رکوردهای محوری و کش فایلی که خود تولید نکرده‌اید، از جمله مسیر حفظ بلاک خام، در راهنمای ممیزی کتاب کار و میز کار تبدیل پوشش داده شده است. هنگامی که محدوده منبع به ده‌ها هزار ردیف می‌رسد و جریان SXDBB رکوردهای ادامه زیادی را شامل می‌شود، تکنیک‌های موجود در یادداشت‌های عملکرد کتاب کار بزرگ مانع از این می‌شوند که ساخت کش بر زمان اجرای شما حاکم شود. هر دو با نویسنده محوری جفت می‌شوند که در کامپوننت صفحه گسترده HotXLS برای Delphi و C++Builder همراه با APIهای سلول، فرمول، نمودار و قالب‌بندی که در بخش‌های دیگر این وبلاگ پوشش داده شده‌اند، عرضه می‌شود