ساخت و به‌روزرسانی جدول‌های محوری اکسل در دلفی با استفاده از HotXLS

ابزار HotXLS جدول‌های محوری (pivot tables) بومی XLSX را از طریق دلفی و سی‌پلاس‌پلاس‌بیلدر بدون نیاز به نصب اکسل و بدون استفاده از اتوماسیون COM روی سیستم می‌سازد و به‌روزرسانی می‌کند. شما متد AddPivotTable را با یک محدوده منبع فراخوانی می‌کنید، فیلدها را در محورهای ردیف، ستون، صفحه و داده قرار می‌دهید، آیتم‌های محاسباتی یا نمایش درصد از کل را اضافه می‌نمایید، و کامپوننت بخش‌های pivotCacheDefinition و pivotTableDefinition را می‌نویسد که اکسل آنها را به عنوان یک جدول محوری پویا و قابل به‌روزرسانی باز می‌کند

سناریویی که این کار را ارزشمند می‌کند، یک سرور گزارش‌دهی است. شما صدها کارپوشه را در یک شب تولید می‌کنید که هر کدام حاوی یک جدول محوری است که یک حساب را خلاصه می‌کند، و در ماه آینده اعداد تغییر می‌کنند و هر فایل باید ردیف‌های منبع جدید را منعکس کند. اجرای اکسل از طریق یک سرویس ویندوز شکننده است و برای استفاده سرور مجاز نیست، و نوشتن دستی XML جدول محوری پروژه‌ای بسیار طولانی است که هرگز به پایان نمی‌رسد. HotXLS بین این دو بن‌بست قرار می‌گیرد: یک مدل شیء تایپ‌شده روی بخش‌های جدول محوری OOXML، به طوری که همان کدهای پاسکال که سلول‌ها را پر می‌کنند، جدول محوری را نیز تعریف کرده و کش آن را در همان فرآیند بازسازی می‌نمایند

چگونه بدون اکسل یک جدول محوری در دلفی ایجاد کنیم؟

شما با یک فراخوانی واحد یکی ایجاد می‌کنید و سپس فیلدها را در محورها قرار می‌دهید. متد AddPivotTable محدوده منبع را با فرمت A1، سلول مقصد را که جدول به آن متصل می‌شود، و یک نام را دریافت می‌کند؛ محدوده را تجزیه می‌نماید، تک‌تک ستون‌ها را برای تشخیص نوع داده اسکن می‌کند، یک کش جدول محوری (pivot cache) می‌سازد (یا از کشی که از قبل به همان محدوده متصل است استفاده مجدد می‌کند)، و یک کلاس TXLSPivotTable را برمی‌گرداند که همه فیلدهای آن خارج از محور شروع می‌شوند. از آنجا، متدهای کمکی AddRowField، AddColumnField، AddPageField و AddDataFieldByName طرح‌بندی را تنظیم می‌کنند و هر فیلد داده یکی از یازده نوع تجمیع را از نوع شمارشی TXLSPivotAggregation دریافت می‌کند (شامل xlpaSum، xlpaCount، xlpaAverage، xlpaMax، xlpaMin، xlpaProduct، xlpaCountNums، xlpaStdDev، xlpaStdDevP، xlpaVar، xlpaVarP)

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // report sheet (1-based in the XLSX engine)

    // Source A1:E500 on the 'Data' sheet; 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.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

چرا کش و جدول دو بخش مجزا هستند

یک جدول محوری در واقع دو ساختار است که به یکدیگر ارجاع می‌دهند، و درک این تفکیک چیزی است که بقیه فرآیند را شفاف نگه می‌دارد. بخش pivotCacheDefinition پیش‌نمایش داده‌ها است: یک worksheetSource که به محدوده منبع اشاره می‌کند، و یک cacheField برای هر ستون که مقادیر متمایز آن ستون (یعنی sharedItems آن) به همراه محدوده‌های مشتق‌شده را نگه می‌دارد. بخش pivotTableDefinition نمای ظاهری است: اینکه کدام فیلد کش روی کدام محور قرار می‌گیرد، فیلدهای داده و تجمیع آنها، و تنظیمات طرح‌بندی. نما از طریق رابطه pivotCaches کارپوشه و با استفاده از شناسه cacheId به کش متصل می‌شود، دقیقاً همان‌گونه که در استانداردهای ECMA-376 Part 1 §18.10 و [MS-XLSX] تعریف شده است

این روش غیرمستقیم، دیوان‌سالاری نیست، بلکه دو مزیت دارد. یک کش می‌تواند از چندین جدول پشتیبانی کند، بنابراین به‌روزرسانی یک‌باره کش، هر نمایی را که از آن ترسیم شده است به‌روز می‌کند. و کش مقادیر هر ستون را به صورت حذف تکراری‌ها در یک جدول شاخص برای هر رکورد ذخیره می‌کند و نه در شبکه خام سلول‌ها، به همین دلیل ساخت یک جدول محوری به معنای اسکن کردن منبع است، نه کپی کردن سلول‌ها. HotXLS این مورد را برای هر دو موتور به یک شکل مدل‌سازی می‌کند، بنابراین کد بالا تقریباً مشابه مسیر کلاسیکی است که در کنار طرح‌بندی رکورد باینری SX در پس جدول‌های محوری کلاسیک .xls مستند شده است. اگر منبع شما در شیت دیگری قرار دارد یا آن را با یک نام آدرس‌دهی می‌کنید، پیشوند محدوده و نام‌های تعریف‌شده و ارجاعات بین‌شیتی از قوانین معمول A1 پیروی می‌کنند، و استفاده از علامت نقل قول برای نام شیت‌هایی که حاوی فاصله هستند مجاز است

آیتم‌های محاسباتی با فیلدهای محاسباتی متفاوت هستند

این سه اصطلاح نام سه چیز متفاوت هستند، و اشتباه گرفتن آنها با هم، اشتباه مرسوم در جدول‌های محوری است. یک آیتم محاسباتی (calculated item) در داخل یک فیلد قرار می‌گیرد و آیتم‌های خود آن فیلد را بر اساس نام ترکیب می‌کند، بنابراین در یک فیلد Region می‌توانید یک ردیف مجازی CoreMarkets برابر با North به علاوه South تعریف کنید؛ HotXLS آن را به عنوان TXLSPivotField.AddCalculatedItem ارائه می‌دهد و عبارت <calculatedItem> را در زیر بخش <calculatedItems> آن فیلد می‌نویسد. یک فیلد محاسباتی (calculated field) متفاوت است: این یک مقدار جدید است که از ستون‌های دیگر به دست می‌آید، مانند Margin از Revenue و Cost، و به عنوان فرمولی روی یک فیلد کش از طریق TXLSPivotCacheField.Formula منتقل می‌شود. یک عضو محاسباتی (calculated member) که با TXLSPivotTable.AddCalculatedMember اضافه می‌شود، یک عضو سفارشی در سطح جدول است که می‌تواند به عنوان یک شاخص (نوع عضو data) یا یک عضو بعد عمل کند، که عمدتاً برای جدول‌های محوری با ساختار OLAP کاربرد دارد

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // A calculated ITEM combines items of a single field by name.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // A calculated MEMBER is declared at the table level. Member type
  // 'data' marks it as a measure; an empty type is a dimension member.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

یک محدودیت واقعی برای هر سه مورد صدق می‌کند. HotXLS متن فرمول را در XML تعریف صادر می‌کند؛ اما آن را ارزیابی نمی‌نماید. اکسل آیتم محاسباتی، فیلد یا عضو را در زمان باز کردن فایل محاسبه می‌کند، دقیقاً به همان روشی که هر تجمیعی را در شبکه سلول‌ها محاسبه می‌نماید. HotXLS دستورالعمل‌ها را می‌نویسد، نه نتایج را، بنابراین فرمول‌هایی که ارائه می‌دهید باید فرمول‌های معتبر جدول محوری در گویش خود اکسل باشند و به فیلدها و نام‌های آیتم‌ها همان‌گونه ارجاع دهند که اکسل ارجاع می‌دهد

چگونه مقادیر را به صورت درصد از کل نشان دهیم؟

شما به جای اینکه خودتان اعداد را تغییر دهید، حالت نمایش مقدار را روی فیلد داده تنظیم می‌کنید. ویژگی TXLSPivotDataField.ShowDataAs نوع شمارشی TXLSPivotShowDataAs را دریافت می‌کند که مقادیر OOXML ST_ShowDataAs را شبیه‌سازی می‌کند: شامل xlpsdaNormal، xlpsdaDifference، xlpsdaPercent، xlpsdaPercentDiff، xlpsdaRunTotal، xlpsdaPercentOfRow، xlpsdaPercentOfCol، xlpsdaPercentOfTotal و xlpsdaIndex. یک ترفند معمول این است که یک ستون منبع را دو بار روی محور داده قرار دهید، یک بار به عنوان مجموع خام و یک بار به عنوان سهمی از کل کل، به طوری که گزارش هم عدد و هم وزن آن را نشان دهد

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // Item-relative modes need a base to compare against. A running total
  // down 'Quarter' (cache-field index 3) would read:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // cache-field index the transform runs over
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

حالت xlpsdaPercentOfTotal نیازی به پایه ندارد زیرا نسبت به کل کل سنجیده می‌شود، اما حالت‌های نسبی به آیتم به پایه نیاز دارند. حالت‌های xlpsdaDifference، xlpsdaPercentDiff و xlpsdaRunTotal به BaseField (ایندکس فیلد کش که مقایسه روی آن انجام می‌شود) و برای حالت‌های متصل به آیتم، به یک ایندکس BaseItem نیاز دارند که در آن $7FFD نشان‌دهنده گزینه (All) است. HotXLS این موارد را به عنوان ویژگی‌های <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> می‌نویسد و همانند فرمول‌های محاسباتی، محاسبات ریاضی را به اکسل واگذار می‌کند

گروه‌بندی تاریخ‌ها و اعداد، و فیلترهای فیلد صفحه

گروه‌بندی روی فیلد کش پیکربندی می‌شود نه فیلد جدول محوری، زیرا نحوه تقسیم‌بندی محدوده منبع را تغییر می‌دهد. مقدار HasGroup := True را روی فیلد کش بازگردانده شده توسط Cache.FindFieldByName تنظیم کنید، سپس تنظیمات محدوده عددی (GroupStartNum، GroupEndNum، GroupInterval) یا فلگ‌های ساختار درختی تاریخ (GroupByDate به همراه GroupMonths، GroupQuarters، GroupYears و بازه GroupStartDate / GroupEndDate) را انتخاب نمایید. HotXLS عنصر منطبق <fieldGroup><rangePr groupBy="months"/> را صادر می‌کند تا فیلدی که بر اساس ماه یا بر اساس یک محدوده عددی با عرض ۱۰۰۰ گروه‌بندی شده است، همان‌گونه باز شود که اکسل آن را گروه‌بندی می‌کرد

فیلدهای صفحه همان لیست‌های کشویی فیلتر در بالای جدول هستند. متد AddPageField یک فیلد را در محور صفحه قرار می‌دهد و PageItemIndex یک آیتم کش تک را پیش‌انتخاب می‌کند که مقدار پیش‌فرض آن xlPageItemAll (معادل $7FFD یا همان (All)) است. برای اینکه به یک خواننده اجازه دهید چندین آیتم را به طور همزمان انتخاب کند، ویژگی MultipleItemSelectionAllowed := True را تنظیم نمایید که HotXLS آن را به صورت <pivotField multipleItemSelectionAllowed="1"/> می‌نویسد. برای معیارهایی فراتر از انتخاب دستی، هر فیلد دارای یک مجموعه Filters تایپ‌شده شامل خانواده‌های OOXML ST_FilterType است — فیلترهای تعداد، درصد، مجموع، عنوان، مقدار و تاریخ — که هر ورودی یک نوع فیلتر را با مقادیر مقایسه‌ای خود جفت می‌کند

چگونه کش جدول محوری را در زمان تغییر منبع به‌روزرسانی کنیم؟

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

var
  Rc: Integer;
begin
  // ...the source data has changed since the pivot was built...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // a corrected Revenue figure

  // Rebuild the cache's shared items and record indices from the source.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = refreshed, -1 = cache/source missing
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

آنچه HotXLS می‌نویسد و آنچه اکسل محاسبه می‌کند

تقسیم کار را مد نظر داشته باشید تا هیچ چیز شما را شگفت‌زده نکند. HotXLS یک نویسنده تعریف است: بخش‌های pivotCacheDefinition، رکوردهای کش و pivotTableDefinition را به همراه محورها، تجمیع‌ها، آیتم‌ها و اعضای محاسباتی، نمایش مقادیر، گروه‌بندی و فیلترها تولید می‌کند. اکسل محاسبه‌گر است: در زمان باز شدن، بخش‌های گروه‌بندی را تشکیل می‌دهد، فرمول‌های محاسباتی را ارزیابی می‌کند، تبدیل‌های showDataAs را اعمال می‌نماید و بدنه را تجمیع می‌کند. مقادیری که کاربر مشاهده می‌کند متعلق به اکسل است که از روی دستورالعمل‌های نوشته‌شده توسط HotXLS تولید شده‌اند، به همین دلیل است که هر فرمول و شاخص پایه باید در زمان نوشتن صحیح باشد به جای اینکه در زمان رندر بررسی شود

دو محدودیت وجود دارد که دانستن آنها قبل از طراحی بر اساس این ویژگی مفید است. متد AddPivotTable یک محدوده مستطیلی A1 را با پیشوند اختیاری شیت تجزیه می‌کند؛ منابع محدوده نام‌گذاری‌شده و کارپوشه خارجی خارج از محدوده چیزی است که سازنده حل می‌کند، هرچند کش‌های خوانده شده از یک فایل ساخته‌شده در اکسل، منبع محدوده نام‌گذاری‌شده خود را در طول رفت و برگشت حفظ می‌کنند. و جدول‌های محوری ایجادشده توسط اکسل که HotXLS آنها را از دیسک می‌خواند در زمان ذخیره بایت‌به‌بایت بازنویسی می‌شوند، بنابراین ویرایش‌های تایپ‌شده توصیف‌شده در اینجا به خوبی روی جدول‌های محوری که در کد می‌سازید اعمال می‌شوند در حالی که جدول‌های محوری موجود بدون تغییر می‌مانند. برای بخش ورودی یک گزارش — سلول‌های اعتبارسنجی شده و جدول‌های فیلترشده که جدول محوری آنها را خلاصه می‌کند — بخش اعتبارسنجی داده‌ها، فیلتر خودکار و جدول‌های ساختاریافته را ببینید

مدل جدول محوری نشان داده شده در اینجا بخشی از HotXLS Delphi Excel Component استاندارد برای دلفی و سی‌پلاس‌پلاس‌بیلدر است که هر دو بخش جدول محوری تایپ‌شده XLSX و رکوردهای کلاسیک BIFF8 را از روی همان مدل شیء می‌خواند و می‌نویسد