API de tabela dinâmica XLS clássico

O HotXLS oferece dois fluxos de trabalho clássicos de tabela dinâmica em XLS. As pastas de trabalho abertas no Excel mantêm a exibição dinâmica original e os registros do cache dinâmico para ciclos de ida e volta no salvamento. O código também pode inspecionar o modelo tipado de pivot e criar novas tabelas dinâmicas clássicas em XLS por meio de

Round-trip e modelo tipado

Quando uma pasta de trabalho já contém tabelas dinâmicas, o leitor preserva os registros BIFF brutos e também expõe um modelo tipado aproximado. As tabelas e os caches carregados de um arquivo mantêm FromRawBlobs = True, então a saída de salvamento reproduz os bytes originais para preservar a fidelidade. Os pivôs criados programaticamente usam o gravador tipado

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;
      

Pontos de entrada de planilha e pasta de trabalho

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 registra uma string de formato numérico e retorna seu índice ifmt BIFF8, que é o que TXLSPivotDataField.NumberFormat e TXLSPivotCacheField.NumberFormat esperam; registrar a mesma string duas vezes retorna o mesmo índice

DestRow e DestCol são baseadas em 1, como Cells[Row, Col], então AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') ancora a tabela dinâmica em A3. As posições em TXLSPivotTable (FirstRow .. FirstDataCol) e o intervalo de origem em TXLSPivotCache também são baseadas em 1, igual ao mecanismo XLSX; uma âncora fora da planilha de 65,536 x 256 retorna nil

Criar um layout de tabela dinâmica com arrastar e soltar

TXLSPivotDesigner é um modelo de layout não visual que pode dar suporte a interfaces de lista de campos em VCL, FMX, web ou linha de comando sem acoplar a lógica da tabela dinâmica a um único toolkit de UI

Cada campo informa uma zona disponível, de linha, de coluna, de filtro ou de valor, além de sua posição determinística; MoveField aplica um drop, SetAggregation altera um campo de valor, Validate informa layouts incompletos, e Apply valida e materializa o resultado por meio do escritor de tabela dinâmica normal

Novos campos de valor numéricos e de data têm padrão xlpaSum, enquanto campos de texto, Boolean, erro, vazio e mistos têm padrão 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;
      

Criar uma tabela dinâmica

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;
      

Exibir valores como

Defina ShowDataAs em cada campo de dados antes de chamar Make ou salvar a pasta de trabalho. Difference, Percente PercentDiff exigem um índice de campo dinâmico BaseField com base 0 mais um BaseItem. RunTotal exige apenas BaseField. Os modos de linha, coluna, total e índice derivam seus denominadores dos registros de origem com a agregação configurada

Um BaseItem explícito é um índice de item com base 0 dentro do campo dinâmico base. Use xlPivotBaseItemPrevious ou xlPivotBaseItemNext para comparações com itens adjacentes. Interseções de comparação ausentes permanecem em branco, enquanto um denominador zero presente produz #DIV/0!

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

Filtragem, agrupamento e classificação

Make aplica itens ocultos e seleções de página antes da construção do domínio, depois avalia filtros de legenda, data, contagem, porcentagem, soma e valor. Intervalos numéricos, grupos de calendário e grupos discretos usam metadados de agrupamento de cache-field antes da agregação. SortType seleciona a ordenação estável de rótulos, enquanto SortDataField seleciona o campo de valor (base 0) para a ordenação estável por valores de dados

Expressões calculadas

Campos de cache calculados executam uma vez por registro de origem, itens calculados executam entre agregados de itens irmãos, e membros calculados com MemberType = 'data' adicionam campos de valor virtuais depois da agregação de origem. Nomes de fórmula não diferenciam maiúsculas e podem vir entre colchetes ou aspas. Há suporte para aritmética, comparações, porcentagens, parênteses e funções numéricas e lógicas comuns, com passadas repetidas de dependências para definições calculadas acíclicas

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';
      

Subtotais e totais gerais

Cada campo de linha ou coluna pode materializar qualquer função de subtotal configurada acima ou abaixo dos seus itens de detalhe por meio de Subtotals e SubtotalTop. RowGrandTotals e ColumnGrandTotals controlam a política salva na pasta de trabalho, enquanto MaterializeGrandTotalRow e MaterializeGrandTotalColumn fazem os callbacks de resultado incluírem a saída explícita de total geral sem revarrer os registros de origem

Modos estendidos de exibição de valores

ExtendedShowDataAs oferece porcentagem do pai, porcentagem da linha pai, porcentagem da coluna pai, porcentagem do total acumulado e classificação crescente ou decrescente. Esses modos usam o modelo de extensão XLSX e não emitem valores clássicos de campo de dados BIFF não suportados

Inspecionar uma pasta de trabalho dinâmica existente

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;
      

Tópicos relacionados

• interface IXLSWorkbook
• classe TXLSWorksheet
• InteractiveFeatures demonstração
• clássico XLS metadados da pasta de trabalho preservação