API сводных таблиц классического XLS

HotXLS поддерживает два классических сценария сводных таблиц XLS. Книги, открытые из Excel, сохраняют исходное представление сводной таблицы и записи кэша сводной таблицы для обратного сохранения без потерь. Код также может просматривать типизированную модель сводной таблицы и создавать новые классические сводные таблицы XLS через

Полный цикл и типизированная модель

Когда книга уже содержит сводные таблицы, читатель сохраняет необработанные записи BIFF и также предоставляет типизированную модель по возможности. Таблицы и кэши, загруженные из файла, сохраняют FromRawBlobs = True, поэтому при сохранении оригинальные байты воспроизводятся ради точности. Сводные таблицы, созданные программно, используют типизированный писатель

const
  xlPivotBaseItemPrevious = $7FFB;
  xlPivotBaseItemNext = $7FFC;

type
  TXLSPivotFieldAxis = (xlpfaNone, xlpfaRowField, xlpfaColumnField, xlpfaPageField, xlpfaDataField);
  TXLSPivotAggregation = (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP);
  TXLSPivotShowDataAs = (xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal, xlpsdaIndex);
  TXLSPivotExtendedShowDataAs = (xlpesdaNone, xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending, xlpesdaRankDescending);
  TXLSPivotSubtotal = (xlpsDefault, xlpsSum, xlpsCount, xlpsAverage, xlpsMax, xlpsMin, xlpsProduct, xlpsCountNums, xlpsStdDev, xlpsStdDevP, xlpsVar, xlpsVarP);
  TXLSPivotSubtotals = set of TXLSPivotSubtotal;
  TXLSPivotDesignerZone = (xlpdzAvailable, xlpdzRows, xlpdzColumns, xlpdzFilters, xlpdzValues);

  TXLSPivotCacheValue = record
    ValueType: TXLSPivotCacheFieldType;
  end;

  TXLSPivotCacheField = class
    property Name: WideString;
    property FieldType: TXLSPivotCacheFieldType;
    property ItemCount: Integer;
    property Items[Index: Integer]: TXLSPivotCacheItem;
  end;

  TXLSPivotCache = class
    property CacheId: Integer;
    property SourceRangeSheet: WideString;
    property SourceFirstRow: Integer;
    property SourceFirstCol: Integer;
    property SourceLastRow: Integer;
    property SourceLastCol: Integer;
    property RecordCount: Integer;
    property FieldCount: Integer;
    property Fields[Index: Integer]: TXLSPivotCacheField;
    property RecordIndices[RecordIdx, FieldIdx: Integer]: Integer;
    property FromRawBlobs: Boolean;
  end;

  TXLSPivotCaches = class
    function Add: TXLSPivotCache;
    function FindByCacheId(ACacheId: Integer): TXLSPivotCache;
    property Count: Integer;
    property Items[Index: Integer]: TXLSPivotCache;
  end;

  TXLSPivotField = class
    function AddItem: TXLSPivotItem;
    function AddCalculatedItem(const AName, AFormula: WideString): TXLSPivotCalculatedItem;
    property Name: WideString;
    property Axis: TXLSPivotFieldAxis;
    property CacheFieldIndex: Integer;
    property ItemCount: Integer;
    property Items[Index: Integer]: TXLSPivotItem;
    property SubtotalMask: Word;
    property Subtotals: TXLSPivotSubtotals;
    property PageItemIndex: Integer;
    property SortType: TXLSPivotFieldSortType;
    property SortDataField: Integer;
    property Filters: TXLSPivotFilters;
  end;

  TXLSPivotDataField = class
    property Field: TXLSPivotField;
    property Aggregation: TXLSPivotAggregation;
    property NumberFormat: Word;
    property DisplayName: WideString;
    property ShowDataAs: TXLSPivotShowDataAs;
    property ExtendedShowDataAs: TXLSPivotExtendedShowDataAs;
    property BaseField: Integer;
    property BaseItem: Integer;
  end;

  TXLSPivotSupplementalRecord = class
    property RecID: Word;
    property BodyLength: Integer;
    property Body[Index: Integer]: Byte;
  end;

  TXLSPivotTable = class
    function AddField(const AName: WideString; ACacheFieldIndex: Integer): TXLSPivotField;
    function FindFieldByName(const AName: WideString): TXLSPivotField;
    function SetFieldAxis(const AName: WideString; AAxis: TXLSPivotFieldAxis): TXLSPivotField;
    function AddRowField(const AName: WideString): TXLSPivotField;
    function AddColumnField(const AName: WideString): TXLSPivotField;
    function AddPageField(const AName: WideString): TXLSPivotField;
    function AddDataField(AField: TXLSPivotField; AAggregation: TXLSPivotAggregation): TXLSPivotDataField;
    function AddDataFieldByName(const AName: WideString; AAggregation: TXLSPivotAggregation = xlpaSum): TXLSPivotDataField;
    function AddCalculatedMember(const AName, AFormula: WideString): TXLSPivotCalculatedMember;
    function AddSupplementalRecord(ARecID: Word; const ABody: TBytes): TXLSPivotSupplementalRecord;
    function Make(AWriter: TlxPivotResultWriter): Integer;
    property Name: WideString;
    property DataCaption: WideString;
    property CacheId: Integer;
    property Cache: TXLSPivotCache;
    property FieldCount: Integer;
    property Fields[Index: Integer]: TXLSPivotField;
    property DataFieldCount: Integer;
    property DataFields[Index: Integer]: TXLSPivotDataField;
    property RowGrandTotals: Boolean;
    property ColumnGrandTotals: Boolean;
    property MaterializeGrandTotalRow: Boolean;
    property MaterializeGrandTotalColumn: Boolean;
    property SupplementalRecordCount: Integer;
    property SupplementalRecords[Index: Integer]: TXLSPivotSupplementalRecord;
    property FromRawBlobs: Boolean;
  end;

  TXLSPivotDesigner = class
    constructor Create(APivotTable: TXLSPivotTable = nil);
    procedure ClearLayout;
    function MoveField(const AName: WideString; AZone: TXLSPivotDesignerZone; APosition: Integer = -1): Boolean;
    function SetAggregation(const AName: WideString; AAggregation: TXLSPivotAggregation): Boolean;
    function Validate(out ErrorText: WideString): Boolean;
    function Apply(AWriter: TlxPivotResultWriter): Integer;
    property PivotTable: TXLSPivotTable;
    property FieldCount: Integer;
    property Fields[Index: Integer]: TXLSPivotDesignerField;
  end;

  TXLSPivotTables = class
    function Add: TXLSPivotTable;
    function FindByName(const AName: WideString): TXLSPivotTable;
    property Count: Integer;
    property Items[Index: Integer]: TXLSPivotTable;
  end;
      

Точки входа листа и рабочей книги

type
  TXLSWorksheet = class
    function AddPivotTable(const SourceRangeA1: WideString; DestRow, DestCol: Integer; const Name: WideString): TXLSPivotTable;
    function PivotSetDataFieldFormat(DataField: TXLSPivotDataField; const FormatStr: WideString): Word;
    property PivotTables: TXLSPivotTables;
  end;

  TXLSWorkbook = class
    property PivotCaches: TXLSPivotCaches;
    function RegisterNumFormat(const FormatStr: WideString): Word;
end;
      

RegisterNumFormat регистрирует строку числового формата и возвращает её BIFF8-индекс ifmt — именно его ожидают TXLSPivotDataField.NumberFormat и TXLSPivotCacheField.NumberFormat; повторная регистрация той же строки возвращает тот же индекс

DestRow и DestCol отсчитываются с единицы, как Cells[Row, Col], поэтому AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') привязывает сводную таблицу к ячейке A3. Позиции в TXLSPivotTable (FirstRow .. FirstDataCol) и исходный диапазон в TXLSPivotCache тоже отсчитываются с единицы — точно так же, как в движке XLSX; якорь за пределами листа 65,536 x 256 возвращает nil

Построение drag-and-drop компоновки сводной таблицы

TXLSPivotDesigner — невизуальная модель компоновки, которая может стоять за интерфейсами списков полей VCL, FMX, веб или командной строки, не привязывая логику сводных таблиц к одному UI-тулкиту

Каждое поле сообщает зону (доступная, строка, столбец, фильтр или значение) и свою детерминированную позицию; MoveField выполняет перетаскивание, SetAggregation меняет поле значений, Validate сообщает о неполных компоновках, а Apply валидирует и материализует результат через обычный писатель сводных таблиц

Новые числовые поля значений и поля дат по умолчанию используют xlpaSum, тогда как текстовые, логические, ошибочные, пустые и смешанные поля по умолчанию используют xlpaCount

var
  Designer: TXLSPivotDesigner;
  ErrorText: WideString;
begin
  Designer := TXLSPivotDesigner.Create(Pivot);
  try
    Designer.MoveField('Region', xlpdzRows);
    Designer.MoveField('Quarter', xlpdzColumns);
    Designer.MoveField('Closed', xlpdzFilters);
    Designer.MoveField('Revenue', xlpdzValues);
    Designer.SetAggregation('Revenue', xlpaAverage);
    if Designer.Validate(ErrorText) then
      Designer.Apply(ResultWriter.WriteCell);
  finally
    Designer.Free;
  end;
end;
      

Создание сводной таблицы

uses lxHandle;

var
  Workbook: TXLSWorkbook;
  SourceSheet, PivotSheet: TXLSWorksheet;
  Pivot: TXLSPivotTable;
  RowField: TXLSPivotField;
  DataField: TXLSPivotDataField;
begin
  Workbook := TXLSWorkbook.Create;
  try
    SourceSheet := Workbook.Sheets[1];
    SourceSheet.Name := 'SalesData';
    SourceSheet.Cells[1, 1].Value := 'Region';
    SourceSheet.Cells[1, 2].Value := 'Product';
    SourceSheet.Cells[1, 3].Value := 'Revenue';

    PivotSheet := Workbook.Sheets.Add;
    PivotSheet.Name := 'PivotReport';
    Pivot := PivotSheet.AddPivotTable('SalesData!A1:C20', 4, 1, 'SalesPivot');
    RowField := Pivot.AddRowField('Region');
    Pivot.AddColumnField('Product');
    DataField := Pivot.AddDataFieldByName('Revenue', xlpaSum);
    RowField.Subtotals := [xlpsSum];
    PivotSheet.PivotSetDataFieldFormat(DataField, '$#,##0.00');

    Workbook.SaveAs('pivot-report.xls');
  finally
    Workbook.Free;
  end;
end;
      

Отображение значений как

Установите ShowDataAs у каждого поля данных перед вызовом Make или сохранением книги. Для Difference, Percent и PercentDiff нужен индекс BaseField поля сводной таблицы (с нулевой базой) плюс BaseItem. Для RunTotal достаточно одного BaseField. Режимы «строка», «столбец», «итог» и «индекс» берут знаменатели из исходных записей с настроенной агрегацией

Явный BaseItem — это индекс элемента с нулевой базой внутри базового поля сводной таблицы. Используйте xlPivotBaseItemPrevious или xlPivotBaseItemNext для сравнения соседних элементов. Отсутствующие пересечения для сравнения остаются пустыми, а присутствующий нулевой знаменатель даёт #DIV/0!

DataField := Pivot.AddDataFieldByName('Revenue', xlpaSum);
DataField.ShowDataAs := xlpsdaPercentDiff;
DataField.BaseField := 0;
DataField.BaseItem := xlPivotBaseItemPrevious;
CellsWritten := Pivot.Make(ResultWriter.WriteCell);
      

Фильтрация, группировка и сортировка

Make применяет скрытые элементы и выборы страничных полей перед построением домена, затем оценивает фильтры по подписи, дате, количеству, процентам, сумме и значению. Числовые диапазоны, календарные группы и дискретные группы используют метаданные группировки полей кэша до агрегации. SortType выбирает стабильное упорядочивание по подписям, а SortDataField задаёт поле значений с нулевой базой для стабильного упорядочивания значений данных

Вычисляемые выражения

Вычисляемые поля кэша выполняются один раз на исходную запись, вычисляемые элементы — по агрегатам соседних элементов, а вычисляемые члены с MemberType = 'data' добавляют виртуальные поля значений после агрегации источника. Имена формул нечувствительны к регистру и могут быть в квадратных скобках или кавычках. Поддерживаются арифметика, сравнения, проценты, скобки и распространённые числовые и логические функции, с повторными проходами зависимостей для ациклических вычисляемых определений

ProfitField := Pivot.Cache.AddField('Profit', xlpcftNumber);
ProfitField.Formula := '=Revenue-Cost';
ProfitField.DatabaseField := False;
Pivot.AddDataField(Pivot.AddField('Profit', 4), xlpaSum);

RegionField.AddCalculatedItem('Combined', '=North+South');
Pivot.AddCalculatedMember('Margin', '=Profit/Revenue').MemberType := 'data';
      

Промежуточные и общие итоги

Каждое поле строк или столбцов может материализовать любую настроенную функцию промежуточных итогов выше или ниже своих детальных элементов через Subtotals и SubtotalTop. RowGrandTotals и ColumnGrandTotals управляют политикой, сохраняемой в книге, а MaterializeGrandTotalRow и MaterializeGrandTotalColumn позволяют result-callback'у включать явный вывод общих итогов без повторного прохода по исходным записям

Расширенные режимы отображения значений

ExtendedShowDataAs предоставляет «процент от родителя», «процент от строки родителя», «процент от столбца родителя», «процент от нарастающего итога» и ранг по возрастанию или убыванию. Эти режимы используют модель расширений XLSX и не выдают неподдерживаемые значения полей данных классического BIFF

Просмотр существующей рабочей книги со сводными таблицами

Workbook.Open('excel-authored-pivot.xls');
for SheetIndex := 1 to Workbook.Sheets.Count do
begin
  Sheet := Workbook.Sheets[SheetIndex];
  for PivotIndex := 0 to Sheet.PivotTables.Count - 1 do
  begin
    Pivot := Sheet.PivotTables[PivotIndex];
    Writeln(Pivot.Name);
    Writeln(Pivot.FieldCount);
    if Assigned(Pivot.Cache) then
      Writeln(Pivot.Cache.SourceRangeSheet);
  end;
end;
      

Связанные разделы

• IXLSWorkbook интерфейс
• TXLSWorksheet класс
• InteractiveFeatures пример
• классический XLS метаданные книги сохранение