LБлог розробників losLab SoftwareТехнічні нотатки для розробників PDF, Delphi та програмного забезпечення для роботи з документами.

Технічна стаття

Створення та оновлення зведених таблиць Excel в Delphi за допомогою HotXLS

HotXLS Component

HotXLS створює та оновлює нативні зведені таблиці (pivot tables) XLSX безпосередньо з Delphi та C++Builder без необхідності встановлення Excel або використання COM-автоматизації. Ви викликаєте метод AddPivotTable з діапазоном джерела даних, переносите поля на осі рядків, стовпців, сторінок та даних, додаєте обчислювані елементи або відображення у відсотках від загальної суми, а компонент записує розділи pivotCacheDefinition та pivotTableDefinition, які Excel відкриває як інтерактивну зведену таблицю з можливістю оновлення.

Сценарій, який робить це корисним, — сервер генерації звітів. Ви створюєте сотні книг щоночі, кожна з яких містить зведену таблицю з підсумками по одному рахунку, а наступного місяця дані змінюються і кожен файл має відображати нові вихідні рядки. Керування Excel з Windows-служби є нестабільним рішенням і не ліцензується для серверного використання, а ручне написання XML зведеної таблиці — це нескінченний процес вивчення специфікацій. HotXLS знаходиться між цими двома крайнощами, пропонуючи типізовану об'єктну модель поверх частин зведеної таблиці OOXML. Таким чином, той самий код на Pascal, який заповнює комірки, також створює зведену таблицю та перебудовує її кеш в одному процесі.

Як створити зведену таблицю у Delphi без Excel?

Ви створюєте її за допомогою одного виклику, а потім розміщуєте поля на осях. Метод AddPivotTable приймає діапазон джерела даних у нотації A1, цільову комірку, де закріплюється таблиця, та її назву. Він розбирає діапазон, сканує кожен стовпець для визначення типу даних, будує кеш зведеної таблиці (або використовує існуючий кеш, прив'язаний до цього ж діапазону) і повертає об'єкт 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 — це представлення: яке поле кешу знаходиться на якій осі, які поля даних використовуються та їх агрегації, а також перемикачі макета. Представлення зв'язується з кешем через cacheId за допомогою зв'язку pivotCaches книги, відповідно до стандартів 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-визначення, але не обчислює її. Excel розраховує обчислювані елементи, поля або члени при відкритті файлу точно так само, як і будь-які інші агрегати в сітці. HotXLS записує інструкції, а не результати, тому формули мають відповідати правилам Excel, посилаючись на назви полів та елементів відповідно до його вимог.

Як відображати значення у відсотках від загальної суми?

Ви можете налаштувати режим відображення значень безпосередньо для поля даних, замість того щоб перераховувати числа вручну. Властивість TXLSPivotDataField.ShowDataAs приймає значення з переліку TXLSPivotShowDataAs, який відповідає атрибутам ST_ShowDataAs у форматі OOXML: 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.

Групування дат і чисел, а також фільтри полів сторінок

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

Поля сторінок (page fields) — це спадні списки фільтрів над таблицею. Метод AddPageField розміщує поле на осі сторінок, а властивість PageItemIndex дозволяє попередньо вибрати один елемент кешу (за замовчуванням xlPageItemAll ($7FFD, що означає (All))). Щоб дозволити вибір кількох елементів одночасно, встановіть MultipleItemSelectionAllowed := True, що записується як <pivotField multipleItemSelectionAllowed="1"/>. Для налаштування складніших умов кожне поле містить типізовану колекцію Filters, яка охоплює родини фільтрів ST_FilterType в OOXML (фільтри за кількістю, відсотками, сумою, заголовком, значенням та датою), де кожен запис пов'язує тип фільтра з порівнюваними значеннями.

Як оновити кеш зведеної таблиці при зміні джерела?

Метод 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 оновлює збережений кеш безпосередньо в процесі роботи програми, і оскільки таблиці зв'язані з кешем за ідентифікатором, кожна зведена таблиця на базі цього кешу побачить оновлення. Проте метод не будує та не обчислює видиму сітку зведеної таблиці — Excel все одно перераховує її при відкритті файлу на основі оновленого кешу.

Що записує HotXLS, а що обчислює Excel

Пам'ятайте про цей розподіл завдань, і процес роботи буде повністю зрозумілим. HotXLS виступає записувачем визначень: він генерує pivotCacheDefinition, записи кешу та pivotTableDefinition з осями, агрегаціями, обчислюваними елементами та членами, налаштуваннями відображення значень, групуванням та фільтрами. Excel виступає калькулятором: при відкритті файлу він формує групи, обчислює формули, застосовує перетворення showDataAs та агрегує дані. Значення, які бачить користувач, розраховуються самим Excel на основі інструкцій, написаних HotXLS, тому кожна формула та базовий індекс мають бути правильними на момент запису.

Перед проектуванням звітів варто врахувати два обмеження. Метод AddPivotTable розбирає прямокутний діапазон A1 з необов'язковим префіксом листа. Джерела на основі іменованих діапазонів або зовнішніх книг не підтримуються конструктором, хоча кеш, зчитаний із файлу Excel, зберігає свої вихідні іменовані діапазони при збереженні. Зведені таблиці, створені в Excel, які HotXLS зчитує з диска, зберігаються байт-в-байт без змін, тому описані тут методи редагування безпечно застосовувати до таблиць, створених у коді, тоді як існуючі таблиці залишаються неушкодженими. Інформацію про підготовку вхідних даних (перевірку комірок та автофільтрацію) див. у статті про валідацію даних, AutoFilter та структуровані таблиці.

Описана тут модель зведених таблиць є частиною компонента HotXLS Delphi Excel Component для Delphi та C++Builder, який підтримує читання та запис як структурованих частин зведених таблиць XLSX, так і класичних записів BIFF8 з єдиної об'єктної моделі.