API delle tabelle pivot XLS classiche

HotXLS supporta due flussi di lavoro per le tabelle pivot XLS classiche. I workbook aperti da Excel mantengono i record originali della vista pivot e della cache pivot per i round-trip di salvataggio. Il codice può anche ispezionare il modello pivot tipizzato e creare nuove tabelle pivot XLS classiche tramite

Modello tipizzato e round-trip

Quando un workbook contiene già tabelle pivot, il lettore preserva i record BIFF grezzi e inoltre espone un modello tipizzato a migliore sforzo. Le tabelle e le cache caricate da un file mantengono FromRawBlobs = True, così l'output di salvataggio riproduce i byte originali per fedeltà. Le pivot create a livello di programmazione usano il writer tipizzato

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;
      

Punti di ingresso del foglio di lavoro e del workbook

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 stringa di formato numerico e restituisce il suo indice ifmt BIFF8, che è ciò che TXLSPivotDataField.NumberFormat e TXLSPivotCacheField.NumberFormat si aspettano; registrare due volte la stessa stringa restituisce lo stesso indice

DestRow e DestCol sono in base 1, come Cells[Row, Col]: quindi AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') ancora la tabella pivot su A3. Anche le posizioni su TXLSPivotTable (FirstRow .. FirstDataCol) e l'intervallo di origine su TXLSPivotCache sono in base 1, esattamente come nel motore XLSX; un'ancora fuori dal foglio 65,536 x 256 restituisce nil

Costruzione di un layout pivot drag-and-drop

TXLSPivotDesigner è un modello di layout non visuale che può stare alla base di interfacce con lista dei campi per VCL, FMX, web o riga di comando, senza accoppiare la logica pivot a un singolo toolkit UI

Ogni campo riporta una zona disponibile, riga, colonna, filtro o valore più la sua posizione deterministica; MoveField applica un drop, SetAggregation cambia un campo valore, Validate segnala i layout incompleti e Apply valida e materializza il risultato attraverso il normale writer pivot

I nuovi campi valore numerici e di data usano xlpaSumper default, mentre i campi testo, booleani, errore, vuoti e misti usano 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;
      

Creazione di una tabella pivot

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;
      

Mostra i valori come

Impostare ShowDataAs su ogni campo dati prima di chiamare Make o salvare la cartella di lavoro. Difference, Percente PercentDiff richiedono un indice BaseField del campo pivot a base zero più un BaseItem. RunTotal richiede solo BaseField. Le modalità riga, colonna, totale e indice derivano i propri denominatori dai record sorgente con l'aggregazione configurata

Un BaseItem esplicito è un indice di elemento a base zero dentro il campo pivot di base. Usare xlPivotBaseItemPrevious o xlPivotBaseItemNext per confronti con l'elemento adiacente. Le intersezioni di confronto mancanti restano vuote, mentre un denominatore zero presente produce #DIV/0!

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

Filtri, raggruppamenti e ordinamenti

Make applica gli elementi nascosti e le selezioni di pagina prima della costruzione del dominio, poi valuta i filtri su didascalia, data, conteggio, percentuale, somma e valore. Gli intervalli numerici, i gruppi di calendario e i gruppi discreti usano i metadati di raggruppamento dei campi cache prima dell'aggregazione. SortType seleziona l'ordinamento stabile per etichetta, mentre SortDataField seleziona il campo valore a base zero per l'ordinamento stabile sui valori

Espressioni calcolate

I campi cache calcolati vengono eseguiti una volta per record sorgente, gli elementi calcolati vengono eseguiti sugli aggregati degli elementi fratelli, e i membri calcolati con MemberType = 'data' aggiungono campi valore virtuali dopo l'aggregazione della sorgente. I nomi delle formule non distinguono maiuscole/minuscole e possono essere tra parentesi quadre o virgolette. Sono supportati aritmetica, confronti, percentuali, parentesi e le comuni funzioni numeriche e logiche, con passaggi ripetuti di dipendenze per le definizioni calcolate acicliche

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

Subtotali e totali generali

Ogni campo di riga o colonna può materializzare qualsiasi funzione di subtotale configurata sopra o sotto i propri elementi di dettaglio tramite Subtotals e SubtotalTop. RowGrandTotals e ColumnGrandTotals controllano la politica salvata nella cartella di lavoro, mentre MaterializeGrandTotalRow e MaterializeGrandTotalColumn fanno sì che le callback dei risultati includano l'output esplicito dei totali generali senza riscansionare i record sorgente

Modalità show-value estese

ExtendedShowDataAs fornisce percentuale del padre, percentuale della riga padre, percentuale della colonna padre, percentuale del totale progressivo e rango crescente o decrescente. Queste modalità usano il modello di estensione XLSX e non emettono valori di campo dati BIFF classici non supportati

Ispezione di un workbook pivot esistente

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;
      

Argomenti correlati

• Interfaccia IXLSWorkbook
• Classe TXLSWorksheet
• Demo InteractiveFeatures
• Preservazione dei metadati del workbook XLS classico