API XLS classique des tableaux croisés dynamiques

HotXLS prend en charge l'inspection, la construction et la matérialisation de résultats des tableaux croisés dynamiques typés pour les classeurs XLS et XLSX. Le chargement XLSX piloté par les relations résout des noms de parties de package arbitraires et restaure dans le modèle public les enregistrements de cache, les éléments partagés, les métadonnées de champs, les filtres, les groupements, les définitions calculées, les formats et les extensions. L'entrée XLS classique conserve ses enregistrements pivot d'origine pour des allers-retours d'enregistrement fidèles

Modèle typé et allers-retours

Lorsqu'un classeur classique contient déjà des tableaux croisés dynamiques, le lecteur préserve les enregistrements BIFF bruts et expose également un modèle typé. Les tables et les caches chargés depuis un fichier classique conservent FromRawBlobs = True, de sorte que la sortie d'enregistrement rejoue les octets d'origine pour la fidélité. Les pivots XLSX sont chargés et enregistrés via les relations de package qui les portent, tandis que les pivots créés par programme utilisent l'écrivain typé

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;
      

Points d'entrée de feuille de calcul et de classeur

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 enregistre une chaîne de format numérique et renvoie son index BIFF8 ifmt, celui qu'attendent TXLSPivotDataField.NumberFormat et TXLSPivotCacheField.NumberFormat ; enregistrer deux fois la même chaîne renvoie le même index

DestRow et DestCol sont en base 1, comme Cells[Row, Col] : AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') ancre donc le tableau croisé dynamique en A3. Les positions de TXLSPivotTable (FirstRow .. FirstDataCol) comme la plage source de TXLSPivotCache sont en base 1 elles aussi, comme dans le moteur XLSX ; une ancre hors de la grille 65,536 x 256 de la feuille renvoie nil

Construire une disposition pivot en glisser-déposer

TXLSPivotDesigner est un modèle de disposition non visuel qui peut servir de socle à des interfaces de liste de champs VCL, FMX, web ou en ligne de commande, sans coupler la logique pivot à une bibliothèque d'interface particulière

Chaque champ indique sa zone (disponible, ligne, colonne, filtre ou valeur) ainsi que sa position déterministe ; MoveField applique un dépôt, SetAggregation modifie un champ de valeurs, Validate signale les dispositions incomplètes, et Apply valide et matérialise le résultat via l'écrivain pivot habituel

Les nouveaux champs de valeurs numériques et de date prennent xlpaSum par défaut, tandis que les champs texte, booléens, d'erreur, vides et mixtes prennent 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;
      

Créer un tableau croisé dynamique

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;
      

Afficher les valeurs en tant que

Définissez ShowDataAs sur chaque champ de données avant d'appeler Make ou d'enregistrer le classeur. Difference, Percent et PercentDiff exigent un index BaseField de champ pivot en base zéro plus un BaseItem. RunTotal n'exige que BaseField. Les modes ligne, colonne, total et index calculent leurs dénominateurs à partir des enregistrements sources avec l'agrégation configurée

Un BaseItem explicite est un index d'élément en base zéro dans le champ pivot de base. Utilisez xlPivotBaseItemPrevious ou xlPivotBaseItemNext pour comparer des éléments adjacents. Les intersections de comparaison manquantes restent vides, tandis qu'un dénominateur nul présent produit #DIV/0!

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

Filtrage, groupement et tri

Make applique les éléments masqués et les sélections de page avant la construction des domaines, puis évalue les filtres de légende, de date, de nombre, de pourcentage, de somme et de valeur. Les plages numériques, les groupes calendaires et les groupes discrets utilisent les métadonnées de groupement des champs de cache avant l'agrégation. SortType choisit un ordre stable des étiquettes, tandis que SortDataField sélectionne le champ de valeurs en base zéro pour un ordre stable des valeurs de données

Expressions calculées

Les champs de cache calculés s'exécutent une fois par enregistrement source, les éléments calculés s'exécutent sur les agrégats des éléments voisins, et les membres calculés avec MemberType = 'data' ajoutent des champs de valeurs virtuels après l'agrégation source. Les noms de formules sont insensibles à la casse et peuvent être entre crochets ou entre guillemets. L'arithmétique, les comparaisons, les pourcentages, les parenthèses et les fonctions numériques et logiques courantes sont pris en charge, avec des passes de dépendances répétées pour les définitions calculées acycliques

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

Sous-totaux et totaux généraux

Chaque champ de ligne ou de colonne peut matérialiser n'importe quelle fonction de sous-total configurée au-dessus ou en dessous de ses éléments de détail via Subtotals et SubtotalTop. RowGrandTotals et ColumnGrandTotals pilotent la politique d'enregistrement du classeur, tandis que MaterializeGrandTotalRow et MaterializeGrandTotalColumn activent la sortie explicite des totaux généraux dans les callbacks de résultat sans rescanner les enregistrements sources

Modes d'affichage des valeurs étendus

ExtendedShowDataAs fournit pourcentage du parent, pourcentage de la ligne parente, pourcentage de la colonne parente, pourcentage du cumul et rang croissant ou décroissant. Ces modes utilisent le modèle d'extension XLSX et n'émettent pas de valeurs de champ de données BIFF classiques non prises en charge

Inspecter un classeur pivot existant

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;
      

Voir aussi

• Interface IXLSWorkbook
• Classe TXLSWorksheet
• Démo InteractiveFeatures
• Préservation des métadonnées de classeur XLS classique