Създаване и обновяване на Excel Pivot таблици в Delphi с HotXLS

HotXLS създава и обновява нативни XLSX обобщени (Pivot) таблици от Delphi и C++Builder без необходимост от инсталиран Excel и без COM автоматизация на компютъра. Вие извиквате метода AddPivotTable с изходен диапазон от данни, поставяте полета по осите на редовете, колоните, страниците и данните, добавяте изчислени елементи или представяне в проценти от общото количество, и тогава компонентът генерира XML структурите pivotCacheDefinition и pivotTableDefinition, които Excel отваря като работеща и обновяема Pivot таблица

Сценарият, който прави това решение ценно, е сървър за генериране на отчети. Вие създавате стотици работни книги на вечер, всяка от които съдържа Pivot таблица, обобщаваща даден акаунт, а следващия месец числата се променят и всеки файл трябва да отрази новите изходни данни. Управлението на Excel от Windows услуга (service) е нестабилно и нелицензирано за сървърна употреба, а ръчното писане на XML структурата на Pivot таблиците е изключително сложен процес. HotXLS застава между тези два неуспешни подхода, предлагайки типизиран обектен модел над Pivot частите на OOXML, така че същият Pascal код, който попълва клетките, описва обобщената таблица и преизгражда нейния кеш в същия процес

Как се създава обобщена (Pivot) таблица в Delphi без Excel?

Вие създавате таблица с едно извикване и след това разполагате полетата по осите. Методът AddPivotTable приема изходния диапазон в нотация A1, целевата клетка за свързване на таблицата и нейното име; той анализира диапазона, сканира всяка колона за извеждане на типа данни, изгражда Pivot кеш (или преизползва вече свързан към същия диапазон кеш) и връща обект 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;

Защо кешът и таблицата са две отделни части

Pivot таблицата реално се състои от два елемента, които се отнасят един към друг, и разбирането на това разделение е от съществено значение. Структурата pivotCacheDefinition представлява моментна снимка на данните: worksheetSource сочещ към изходния диапазон и по едно поле cacheField за всяка колона, съдържащо нейните уникални стойности (нейните sharedItems) плюс изведените граници. Структурата pivotTableDefinition е изгледът (view): кое кеш поле се намира на съответната ос, полетата с данни и техните агрегации, както и опциите за оформление. Изгледът се свързва към кеша по идентификатор cacheId чрез връзката pivotCaches на работната книга, точно по начина, описан в ECMA-376 Part 1 §18.10 и [MS-XLSX]

Тази непряка връзка не е излишно усложнение, тя осигурява две неща. Един кеш може да поддържа няколко обобщени таблици, така че обновяването на кеша веднъж актуализира всеки изглед, базиран на него. Също така кешът съхранява стойностите на всяка колона след премахване на дубликатите с таблица с индекси, поради което изграждането на Pivot таблица изисква сканиране на източника, а не копиране на клетките. HotXLS моделира това по еднакъв начин за двете ядра, така че кодът по-горе е почти идентичен с класическия път, документиран в статията за структурата от двоични SX записи зад класическите .xls Pivot таблици. Ако вашите изходни данни са на друг работен лист или се достъпват чрез име, префиксът на диапазона и дефинираните имена и препратки между страници следват стандартните правила за A1, като се позволява използването на единични кавички за имена на страници с интервали

Изчислените елементи (Calculated Items) не са изчислени полета (Calculated Fields)

Тези термини назовават различни функционалности и объркването им е класическа грешка при работа с обобщени таблици. Изчисленият елемент (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 и какво изчислява Excel

Едно общо ограничение важи и за трите елемента. HotXLS записва текста на формулата в дефиниционния XML файл; той не го пресмята. Excel изчислява изчисления елемент, поле или член при отваряне на файла по същия начин, по който изчислява всички агрегации в таблицата. HotXLS записва инструкциите, а не готовите резултати, така че подадените от вас формули трябва да бъдат валидни Pivot формули в диалекта на Excel, рефериращи към имената на полетата и елементите по съответния начин

Как се показват стойности като процент от общото количество (Percent of Total)?

Вие указвате режима на показване на стойностите в полето с данни, вместо сами да преобразувате числата. Свойството 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"/> и подобно на изчислените формули, оставя пресмятането на Excel

Групиране на дати и числа и филтри за страници

Групирането се конфигурира в полето на кеша, а не в Pivot полето, тъй като то променя начина на структуриране на източника. Задайте HasGroup := True в полето на кеша, върнато от Cache.FindFieldByName, след което изберете числовите параметри (GroupStartNum, GroupEndNum, GroupInterval) или флаговете за дата (GroupByDate с GroupMonths, GroupQuarters, GroupYears и обхват GroupStartDate / GroupEndDate). HotXLS генерира съответния XML елемент <fieldGroup><rangePr groupBy="months"/>, така че поле, групирано по месеци или числов диапазон, се отваря точно по същия начин, по който Excel би го групирал

Полетата за страници са падащите менюта с филтри над таблицата. AddPageField поставя поле по оста на страниците, а PageItemIndex избира предварително единичен елемент от кеша (по подразбиране xlPageItemAll, съответстващ на $7FFD или (All)). За да позволите на потребителя да маркира няколко елемента наведнъж, настройте MultipleItemSelectionAllowed := True, което HotXLS записва като <pivotField multipleItemSelectionAllowed="1"/>. За критерии отвъд ръчния избор, всяко поле има колекция Filters, покриваща класовете филтри по OOXML ST_FilterType – филтри за брой, процент, сума, надпис, стойност и дата

Как се обновява Pivot кешът при промяна на източника?

Методът 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 разчиташе на флага refreshOnLoad="1" и оставяше цялото обновяване на Excel при следващото отваряне на файла, което е удачно, когато човек отваря файла, но е неизползваемо при фонови процеси, които трябва да върнат коректни данни. RefreshPivotCache актуализира записания кеш в самия процес и тъй като таблиците се свързват с кеш по неговия идентификатор, всяка Pivot таблица, черпеща данни от него, вижда промяната. Това, което методът не прави, е изграждането и агрегирането на самата видима таблица – Excel все още извършва финалното изчисление от обновения кеш при отваряне на файла

Какво записва HotXLS и какво изчислява Excel

Имайте предвид това разделение на работата и нищо няма да ви изненада. HotXLS дефинира структурата: той създава pivotCacheDefinition, записите на кеша и pivotTableDefinition, включително осите, агрегациите, изчислените елементи и членове, представянето на стойностите, групирането и филтрите. Excel е изчислителят: при отваряне той визуализира групите, пресмята формулите, прилага промените на showDataAs и агрегира стойностите в таблицата. Стойностите, които потребителят вижда, се изчисляват от Excel на базата на инструкциите, записани от HotXLS, поради което всяка формула и индекс трябва да бъдат напълно коректни в момента на запис

Две ограничения трябва да се знаят, преди да се работи с тази функция. AddPivotTable анализира правоъгълен диапазон A1 с незадължителен префикс на страницата; дефинираните имена на диапазони и външни работни книги не се поддържат от вградения конструктор, въпреки че кешът, прочетен от създаден в Excel файл, запазва източника си с име при двупосочно четене/запис. Създадените в Excel обобщени таблици, които HotXLS чете от диска, се презаписват байт по байт при записване на файла, така че описаните тук промени се прилагат коректно към Pivot таблици, които изграждате в кода, докато съществуващите таблици се запазват без загуби. За входната част на отчетите – валидираните клетки и филтрираните таблици, които Pivot таблицата обобщава – вижте статията за валидиране на данни, AutoFilter и структурирани таблици

Моделът на Pivot таблици, показан тук, е част от стандартния HotXLS Delphi Excel Component за Delphi и C++Builder, който чете и запива както типизираните Pivot части на XLSX, така и класическите BIFF8 записи чрез един и същ обектен модел