Создание и обновление сводных таблиц Excel в Delphi с помощью HotXLS

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

Типичный сценарий, в котором полезна эта возможность, — сервер создания отчетов. Вы формируете сотни книг за ночь, каждая из которых содержит сводную таблицу, обобщающую один счет. В следующем месяце данные меняются, и каждый файл должен отражать новые строки источника. Управление Excel из службы Windows нестабильно и не лицензируется для серверного использования, а ручное формирование XML сводной таблицы — бесконечный проект по изучению спецификации. HotXLS предлагает решение посередине: строго типизированную объектную модель поверх XML-структур сводной таблицы, так что тот же код на 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) — это иное: это новое значение, полученное из других столбцов (например, маржа на основе выручки и себестоимости), и оно передается как формула в поле кэша через 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, ссылающимися на имена полей и объектов так, как это делает сам 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"/>, чтобы сгруппированное по месяцам или по числовому шагу поле открывалось так же, как его сгруппировал бы сам Excel

Поля страниц (page fields) представляют собой выпадающие списки фильтров над сводной таблицей. AddPageField помещает поле на ось страниц, а PageItemIndex предварительно выбирает один объект кэша (по умолчанию xlPageItemAll со значением $7FFD, что означает (Все)). Чтобы разрешить выбор нескольких элементов одновременно, установите MultipleItemSelectionAllowed := True, что HotXLS запишет как <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 является вычислителем: при открытии файла он рассчитывает группы, вычисляет формулы, применяет правила отображения и агрегирует ячейки. Значения, которые видит пользователь, рассчитываются самим Excel на основе инструкций, записанных HotXLS, поэтому каждая формула и индекс должны быть строго верными при записи. Подробнее о подготовке данных — проверке ячеек и автоматических фильтрах — см. в статье о проверке данных, AutoFilter и структурированных таблицах

Две границы возможностей стоит упомянуть. Метод AddPivotTable анализирует прямоугольный диапазон A1 с дополнительным префиксом листа. Источники на основе именованных диапазонов и внешних книг не обрабатываются сборщиком, хотя кэши, прочитанные из созданного в Excel файла, сохраняют свои именованные диапазоны при перезаписи. Сводные таблицы, созданные в Excel и прочитанные HotXLS с диска, сохраняются без изменений, поэтому описанные методы применимы к создаваемым в коде сводным таблицам, сохраняя унаследованные таблицы нетронутыми

Модель сводных таблиц является частью компонента HotXLS для Delphi и C++Builder, который поддерживает чтение и запись как типизированных структур XLSX, так и записей BIFF8 из единой объектной модели