API de tabla dinámica XLS clásica

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

Modelo tipado y de ida y vuelta

Cuando un libro de trabajo clásico ya contiene tablas dinámicas, el lector preserva los registros BIFF en bruto y además expone un modelo tipado. Las tablas y cachés cargadas desde un archivo clásico conservan FromRawBlobs = True, de modo que la salida al guardar reproduce los bytes originales por fidelidad. Las tablas dinámicas XLSX se cargan y guardan a través de las relaciones del paquete que las contiene, mientras que las tablas dinámicas creadas por código 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 de trabajo

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

DestRow y DestCol están basados en uno, al igual que Cells[Row, Col], así que AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') ancla la tabla dinámica en A3. Las posiciones sobre TXLSPivotTable (FirstRow .. FirstDataCol) y el rango de origen sobre 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

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

TXLSPivotDesigner es un modelo de diseño no visual que puede respaldar interfaces de listas de campos en VCL, FMX, web o línea de comandos, sin acoplar la lógica de la tabla dinámica a un solo toolkit de UI

Cada campo informa 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 valores, Validate informa los diseños incompletos, y Apply valida y materializa el resultado a través del escritor de tablas dinámicas normal

Los campos de valores numéricos y de fecha nuevos usan xlpaSum de forma predeterminada, mientras que los campos de texto, booleanos, de error, vacíos y mixtos usan 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;
      

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 de guardar el libro de trabajo. Difference, Percent y PercentDiff requieren un índice de campo dinámico BaseField de base 0 más un BaseItem. RunTotal requiere solo BaseField. Los modos de fila, columna, total e índice obtienen sus denominadores de los registros de origen con la agregación configurada

Un BaseItem explícito es un índice de elemento de base 0 dentro del campo dinámico base. Use xlPivotBaseItemPrevious o xlPivotBaseItemNext para comparaciones con elementos adyacentes. Las intersecciones de comparación faltantes 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 los dominios, y luego evalúa los filtros de título, fecha, conteo, porcentaje, suma y valor. Los rangos numéricos, los grupos de calendario y los grupos discretos usan los metadatos de agrupación de campos de caché antes de la agregación. SortType selecciona el orden estable de etiquetas, mientras que SortDataField selecciona el campo de valores de base 0 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 sobre los agregados de elementos hermanos, y los miembros calculados con MemberType = 'data' agregan campos de valores virtuales después de la agregación de origen. Los nombres de fórmulas no distinguen mayúsculas y pueden ir entre corchetes o comillas. Se admiten la aritmética, las comparaciones, los porcentajes, los paréntesis y las 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 encima o debajo de sus elementos de detalle mediante Subtotals y SubtotalTop. RowGrandTotals y ColumnGrandTotals controlan la política guardada del libro de trabajo, mientras que MaterializeGrandTotalRow y MaterializeGrandTotalColumn hacen que los callbacks de resultados incluyan la salida explícita de totales generales sin volver a escanear los registros de origen

Modos extendidos de mostrar valores

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

Inspeccionar un libro de trabajo dinámico 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
• Demo InteractiveFeatures
• Preservación de metadatos del libro de trabajo XLS Classic