API de tablas dinámicas

HotXLS admite la inspección, la creación y la materialización de resultados de tablas dinámicas tipadas para libros de trabajo XLS y XLSX. La carga de XLSX basada en relaciones resuelve nombres de partes del paquete arbitrarios y restaura en el modelo público los registros de caché, los elementos compartidos, los metadatos de campos, los filtros, las agrupaciones, las definiciones calculadas, los formatos y las extensiones. La entrada XLS clásica mantiene sus registros de tabla dinámica originales disponibles para round-trips de guardado fieles

Modelo con tipo y de ida y vuelta

Cuando un libro de trabajo clásico ya contiene tablas dinámicas, el lector conserva los registros BIFF sin procesar y también expone un modelo tipado. Las tablas y cachés cargadas desde un archivo clásico mantienen FromRawBlobs = True, de modo que la salida al guardar reproduce los bytes originales para conservar la fidelidad. Las tablas dinámicas XLSX se cargan y guardan a través de las relaciones de su paquete propietario, mientras que las tablas dinámicas creadas por programación usan el escritor 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;
      

Puntos de entrada de hoja de cálculo y libro

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 una cadena de formato de número y devuelve su índice ifmt de BIFF8, que es lo que esperan TXLSPivotDataField.NumberFormat y TXLSPivotCacheField.NumberFormat; registrar la misma cadena dos veces devuelve el mismo índice

DestRow y DestCol están basados en uno, igual que Cells[Row, Col], de modo que AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') ancla la tabla dinámica en A3. Las posiciones de TXLSPivotTable (FirstRow .. FirstDataCol) y el rango de origen de TXLSPivotCache también están basados en uno, igual que en el motor XLSX; un ancla fuera de la hoja de 65,536 x 256 devuelve nil

Construir un diseño de tabla dinámica de arrastrar y soltar

TXLSPivotDesigner es un modelo de diseño no visual que puede dar soporte a interfaces de listas de campos en VCL, FMX, web o línea de comandos sin acoplar la lógica de tablas dinámicas a un único toolkit de UI

Cada campo informa de una zona disponible, de fila, de columna, de filtro o de valor, más su posición determinista; MoveField aplica una colocación, SetAggregation cambia un campo de valor, Validate informa de diseños incompletos y Apply valida y materializa el resultado a través del escritor de tablas dinámicas normal

Los nuevos campos de valor numéricos y de fecha usan xlpaSum por defecto, mientras que los campos de texto, booleanos, de error, vacíos y mixtos usan xlpaCount por defecto

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;
      

Crear una tabla 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;
      

Mostrar valores como

Establezca ShowDataAs en cada campo de datos antes de llamar a Make o guardar el libro de trabajo. Difference, Percent y PercentDiff requieren un índice de campo de tabla dinámica BaseField basado en cero, más un BaseItem. RunTotal requiere solo BaseField. Los modos de fila, columna, total e índice derivan sus denominadores de los registros de origen con la agregación configurada

Un BaseItem explícito es un índice de elemento basado en cero dentro del campo de tabla dinámica base. Use xlPivotBaseItemPrevious o xlPivotBaseItemNext para comparaciones de elementos adyacentes. Las intersecciones de comparación ausentes quedan en blanco, mientras que un denominador cero presente produce #DIV/0!

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

Filtrado, agrupación y ordenación

Make aplica los elementos ocultos y las selecciones de página antes de construir el dominio y luego evalúa los filtros de título, fecha, recuento, porcentaje, suma y valor. Los rangos numéricos, los grupos de calendario y los grupos discretos usan metadatos de agrupación de campos de caché antes de la agregación. SortType selecciona un orden estable de etiquetas, mientras que SortDataField selecciona el campo de valor basado en cero para un orden estable por valores de datos

Expresiones calculadas

Los campos de caché calculados se ejecutan una vez por registro de origen, los elementos calculados se ejecutan a través de los agregados de elementos hermanos y los miembros calculados con MemberType = 'data' añaden campos de valor virtuales después de la agregación de origen. Los nombres de fórmulas no distinguen mayúsculas de minúsculas y pueden ir entre corchetes o comillas. Se admiten aritmética, comparaciones, porcentajes, paréntesis y funciones numéricas y lógicas comunes, con pasadas repetidas de dependencias para definiciones 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';
      

Subtotales y totales generales

Cada campo de fila o columna puede materializar cualquier función de subtotal configurada por encima o por debajo de sus elementos de detalle mediante Subtotals y SubtotalTop. RowGrandTotals y ColumnGrandTotals controlan la política de guardado del libro de trabajo, mientras que MaterializeGrandTotalRow y MaterializeGrandTotalColumn hacen que las devoluciones de llamada de resultados incluyan la salida explícita de totales generales sin volver a examinar los registros de origen

Modos extendidos de mostrar valores

ExtendedShowDataAs proporciona porcentaje del elemento primario, porcentaje de la fila primaria, porcentaje de la columna primaria, porcentaje del total acumulado y rango ascendente o descendente. Estos modos usan el modelo de extensión XLSX y no emiten valores de campo de datos BIFF clásicos no soportados

Inspeccionar un libro de tabla 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;
      

Temas relacionados

• Interfaz IXLSWorkbook
• Clase TXLSWorksheet
• InteractiveFeatures demo
• Conservación de metadatos del libro XLS clásico