نوشتن PivotTable‌های BIFF8 در دلفی: SXDB و SXLI

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

دلیل سخت بودن PivotTable این است که در واقع دو مصنوع به هم جوش خورده است. کش پیوت (pivot cache)، یک عکس فوری مستقل از داده‌های منبع با زیرجریان (substream) خاص خود وجود دارد، و نمای جدول، طرحی که می‌گوید کدام فیلدها در کدام محور قرار دارند. کش و نما از طریق نمایه به یکدیگر ارجاع می‌دهند. اگر یک نمایه را اشتباه وارد کنید، فایل با خطای تازه‌سازی (refresh error) یا یک شبکه بی‌صدا خالی باز می‌شود

کش پیوت یک زیرجریان مختص به خود است

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

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

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

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

بیت‌های یک رکورد در تمام فیلدها به هم متصل می‌شوند، سپس رکورد بعدی در یک مرز بایت تازه شروع می‌شود. رکوردها در یک راستای بایت هستند، نه بسته‌بندی شده بیتی پشت سر هم، که دسترسی تصادفی به جدول را با هزینه چند بیت پدینگ در هر ردیف قابل پیگیری می‌کند. بسته‌بندی درون یک بایت ابتدا از کم‌ارزش‌ترین بیت (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 است. هر رکورد در این فرمت، از جمله رکوردهای پیوت، باید بار خود را در حداکثر 8224 بایت جا دهد، و یک کش پیوت شلوغ با هزاران ردیف منبع مدت‌ها قبل از انتشار هر ردیف از این حد فراتر خواهد رفت. بنابراین جدول نمایه تقسیم می‌شود. HotXLS بدنه یک SXDBB منفرد را در 8220 بایت محدود می‌کند، که محدودیت 8224 رکورد منهای چهار بایت هدر رکورد برای نوع و طول است، آن را بر عرض بایت یک رکورد بسته‌بندی شده تقسیم می‌کند تا بفهمد چند ردیف کامل جا می‌شود، و سپس به همان اندازه رکوردهای ادامه یافته SXDBB منتشر می‌کند که تعداد ردیف می‌طلبد. هر ادامه به طور تمیز در یک مرز رکورد دوباره شروع می‌شود، بنابراین هیچ ردیفی هرگز در میان دو رکورد قطع نمی‌شود. خواننده‌ای که عرض بیت در هر رکورد را می‌داند، می‌تواند از میان هر SXDBB به ترتیب عبور کند، گویی یک آرایه بیتی پیوسته هستند

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

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

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

دو نویسنده در یکی: حباب‌های خام (raw blobs) و مدل نوع‌بندی شده

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

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

ساختن یک PivotTable در کد

تمام ماشین‌آلات بالا پشت یک تماس قرار دارد. 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های قالب‌بندی که در جای دیگری از این وبلاگ پوشش داده شده‌اند