Pivottabel-API
HotXLS understøtter typet pivot-inspektion, oprettelse og materialisering af resultater for XLS- og XLSX-projektmapper. Relationsdrevet XLSX-indlæsning opløser vilkårlige pakkedele-navne og gendanner cache-poster, delte elementer, feltmetadata, filtre, gruppering, beregnede definitioner, formater og udvidelser i den offentlige model. Klassisk XLS-input bevarer sine oprindelige pivot-poster til troværdige save round-trips
Round-trip og typet model
Når en klassisk projektmappe allerede indeholder pivottabeller, bevarer readeren de rå BIFF-poster og eksponerer samtidig en typet model. Tabeller og caches indlæst fra en klassisk fil beholder FromRawBlobs = True, så save-output gentager de oprindelige bytes for at bevare trofastheden. XLSX-pivots indlæses og gemmes via deres eje pakkerelationer, mens programmatisk oprettede pivots bruger den typede writer
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;
Indgangspunkter for regneark og projektmappe
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 registrerer en talformatstreng og returnerer dens BIFF8-ifmt-indeks, hvilket er præcis hvad TXLSPivotDataField.NumberFormat og TXLSPivotCacheField.NumberFormat forventer; registreres den samme streng to gange, fås det samme indeks
Byg et drag-and-drop pivot-layout
TXLSPivotDesigner er en ikke-visuel layoutmodel, der kan drive VCL-, FMX-, web- eller kommandolinjebaserede feltopstillinger uden at koble pivotlogikken til ét bestemt UI-toolkit
Hvert felt oplyser sin zone — ledig, række, kolonne, filter eller værdi — samt sin deterministiske position; MoveField udfører et drop, SetAggregation ændrer et værdifelt, Validate rapporterer ufuldstændige layouts, og Apply validerer og materialiserer resultatet gennem den sædvanlige pivot-writer
Nye numeriske værdifelter og datoværdifelter får som standard xlpaSum, mens tekst-, booleske, fejl-, tomme og blandede felter får 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;
Opret en pivottabel
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;
Vis værdier som
Sæt ShowDataAs på hvert datafelt, før du kalder Make eller gemmer projektmappen. Difference, Percent og PercentDiff kræver et nulbaseret BaseField-indeks for pivotfeltet plus et BaseItem. RunTotal kræver kun BaseField. Række-, kolonne-, total- og indeks-tilstandene udleder deres nævnere af kildeposterne med den konfigurerede aggregering
En eksplicit BaseItem er et nulbaseret elementindeks inde i basis-pivotfeltet. Brug xlPivotBaseItemPrevious eller xlPivotBaseItemNext til sammenligninger med naboelementer. Manglende skæringspunkter forbliver tomme, mens en eksisterende nævner på nul giver #DIV/0!
DataField := Pivot.AddDataFieldByName('Revenue', xlpaSum);
DataField.ShowDataAs := xlpsdaPercentDiff;
DataField.BaseField := 0;
DataField.BaseItem := xlPivotBaseItemPrevious;
CellsWritten := Pivot.Make(ResultWriter.WriteCell);
Filtrering, gruppering og sortering
Make anvender skjulte elementer og sideselektioner, før domænet konstrueres, og evaluerer derefter caption-, dato-, antals-, procent-, sum- og værdifiltre. Numeriske intervaller, kalendergrupper og diskrete grupper bruger cache-feltets grupperingsmetadata før aggregering. SortType vælger stabil etiketrækkefølge, mens SortDataField vælger det nulbaserede værdifelt til stabil rækkefølge efter dataværdi
Beregnede udtryk
Beregnede cache-felter udføres én gang pr. kildepost, beregnede elementer udføres på tværs af de sideliggende elementaggregater, og beregnede medlemmer med MemberType = 'data' tilføjer virtuelle værdifelter efter kildeaggregeringen. Formelnavne skelner ikke mellem store og små bogstaver og kan skrives i klammer eller anførselstegn. Aritmetik, sammenligninger, procenter, parenteser og gængse numeriske og logiske funktioner understøttes, med gentagne dependency-passes til acykliske beregnede definitioner
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';
Deltotaler og stortotaler
Hvert række- eller kolonnefelt kan materialisere enhver konfigureret deltotalfunktion over eller under sine detaljeelementer via Subtotals og SubtotalTop. RowGrandTotals og ColumnGrandTotals styrer politikken for den gemte projektmappe, mens MaterializeGrandTotalRow og MaterializeGrandTotalColumn får resultat-callbacks til at levere eksplicit stortotal-output uden at genlæse kildeposterne
Udvidede "vis værdier som"-tilstande
ExtendedShowDataAs tilbyder procent af overordnet element, procent af overordnet række, procent af overordnet kolonne, procent af løbende total samt stigende eller faldende rang. Disse tilstande bruger XLSX-udvidelsesmodellen og emitterer ikke klassiske BIFF-dataværdier, der ikke understøttes
Inspicer en eksisterende pivot-projektmappe
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;