Documentation

PivotTableBuilder
in package

Fluent builder for creating a new pivot table from a range of source data.

The builder reads the header row of the source range to discover field names, lets you place those fields on the row / column / value axes, and produces a PivotTable model. When the spreadsheet is saved as Xlsx, the pivot parts are generated with refreshOnLoad set, so the value cells are computed by the spreadsheet application when the file is opened.

Example:

$builder = new PivotTableBuilder($dataSheet, 'A1:C100');
$builder->addRowField('Region')
    ->addColumnField('Product')
    ->addDataField('Amount', PivotField::SUBTOTAL_SUM);
$pivotTable = $builder->build($pivotSheet, 'A3');

Table of Contents

Methods

__construct()  : mixed
addColumnField()  : self
Place a field on the column axis.
addDataField()  : self
Add a value (data) field with the given aggregation function.
addPageField()  : self
Place a field on the page (report filter) axis.
addRowField()  : self
Place a field on the row axis.
build()  : PivotTable
Build the PivotTable model and register it on the target worksheet.
groupFieldByDate()  : self
Group a date/time field by one or more calendar units.
groupFieldByNumericRange()  : self
Group a numeric field into fixed-width buckets (e.g. 0-100, 100-200).

Methods

__construct()

public __construct(Worksheet $sourceWorksheet, string $sourceRange) : mixed
Parameters
$sourceWorksheet : Worksheet
$sourceRange : string

the source data range including the header row, e.g. "A1:C100"

addColumnField()

Place a field on the column axis.

public addColumnField(string $fieldName) : self
Parameters
$fieldName : string
Return values
self

addDataField()

Add a value (data) field with the given aggregation function.

public addDataField(string $fieldName[, string $subtotal = PivotField::SUBTOTAL_SUM ][, string|null $caption = null ]) : self
Parameters
$fieldName : string
$subtotal : string = PivotField::SUBTOTAL_SUM

one of the PivotField::SUBTOTAL_* constants

$caption : string|null = null

optional display caption (defaults to e.g. "Sum of Amount")

Return values
self

addPageField()

Place a field on the page (report filter) axis.

public addPageField(string $fieldName) : self
Parameters
$fieldName : string
Return values
self

addRowField()

Place a field on the row axis.

public addRowField(string $fieldName) : self
Parameters
$fieldName : string
Return values
self

build()

Build the PivotTable model and register it on the target worksheet.

public build(Worksheet $targetWorksheet, string $targetCell[, string $name = 'PivotTable1' ]) : PivotTable
Parameters
$targetWorksheet : Worksheet
$targetCell : string

top-left cell of the pivot table, e.g. "A3"

$name : string = 'PivotTable1'
Return values
PivotTable

groupFieldByDate()

Group a date/time field by one or more calendar units.

public groupFieldByDate(string $fieldName, string|array<string|int, string> $groupBy[, string|null $startDate = null ][, string|null $endDate = null ]) : self
Parameters
$fieldName : string
$groupBy : string|array<string|int, string>

one or more PivotFieldGroup::GROUP_BY_* constants

$startDate : string|null = null

ISO-8601 start (auto when null)

$endDate : string|null = null

ISO-8601 end (auto when null)

Return values
self

groupFieldByNumericRange()

Group a numeric field into fixed-width buckets (e.g. 0-100, 100-200).

public groupFieldByNumericRange(string $fieldName, float $interval[, float|null $startNum = null ][, float|null $endNum = null ]) : self
Parameters
$fieldName : string
$interval : float

bucket width

$startNum : float|null = null

lower bound of the first bucket (auto when null)

$endNum : float|null = null

upper bound of the last bucket (auto when null)

Return values
self 

        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results