30319021002

Committed 28 Jul 2026 01:00AM UTC coverage: 97.119% (-0.05%) from 97.169%

Build # 30319021002

Build Type

Pull #4942

github

Committed by

web-flow

Commit Message

Merge 4894ecd2b into 540dfb463

Pull Request Pull Request #4942: Feature/pivot table read

Coverage Stats

787 of 836 new or added lines in 12 files covered. (94.14%)

49176 of 50635 relevant lines covered (97.12%)

382.11 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

98.06

/src/PhpSpreadsheet/Worksheet/PivotTable/PivotTableBuilder.php

<?php

namespace PhpOffice\PhpSpreadsheet\Worksheet\PivotTable;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

/**
 * 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:
 *
 * ```php
 * $builder = new PivotTableBuilder($dataSheet, 'A1:C100');
 * $builder->addRowField('Region')
 *     ->addColumnField('Product')
 *     ->addDataField('Amount', PivotField::SUBTOTAL_SUM);
 * $pivotTable = $builder->build($pivotSheet, 'A3');
 * ```
 */
class PivotTableBuilder
{
    private Worksheet $sourceWorksheet;

    private string $sourceRange;

    /**
     * Field names in source-column order, taken from the header row.
     *
     * @var string[]
     */
    private array $fieldNames;

    /**
     * Requested placements, keyed by field name.
     *
     * @var array<string, array{axis: string, subtotal: ?string, caption: ?string}>
     */
    private array $placements = [];

    /**
     * Requested field groupings, keyed by field name.
     *
     * @var array<string, PivotFieldGroup>
     */
    private array $groups = [];

    /**
     * @param string $sourceRange the source data range including the header row, e.g. "A1:C100"
     */
    public function __construct(Worksheet $sourceWorksheet, string $sourceRange)
    {
        $this->sourceWorksheet = $sourceWorksheet;
        $this->sourceRange = str_replace('$', '', $sourceRange);
        $this->fieldNames = $this->readHeaderNames();
    }

    /**
     * Place a field on the row axis.
     */
    public function addRowField(string $fieldName): self
    {
        return $this->place($fieldName, PivotField::AXIS_ROW, null, null);
    }

    /**
     * Place a field on the column axis.
     */
    public function addColumnField(string $fieldName): self
    {
        return $this->place($fieldName, PivotField::AXIS_COLUMN, null, null);
    }

    /**
     * Place a field on the page (report filter) axis.
     */
    public function addPageField(string $fieldName): self
    {
        return $this->place($fieldName, PivotField::AXIS_PAGE, null, null);
    }

    /**
     * Group a numeric field into fixed-width buckets (e.g. 0-100, 100-200).
     *
     * @param float $interval bucket width
     * @param ?float $startNum lower bound of the first bucket (auto when null)
     * @param ?float $endNum upper bound of the last bucket (auto when null)
     */
    public function groupFieldByNumericRange(string $fieldName, float $interval, ?float $startNum = null, ?float $endNum = null): self
    {
        $this->assertFieldExists($fieldName);
        $this->groups[$fieldName] = PivotFieldGroup::numeric($interval, $startNum, $endNum);

        return $this;
    }

    /**
     * Group a date/time field by one or more calendar units.
     *
     * @param string|string[] $groupBy one or more PivotFieldGroup::GROUP_BY_* constants
     * @param ?string $startDate ISO-8601 start (auto when null)
     * @param ?string $endDate ISO-8601 end (auto when null)
     */
    public function groupFieldByDate(string $fieldName, array|string $groupBy, ?string $startDate = null, ?string $endDate = null): self
    {
        $this->assertFieldExists($fieldName);
        $this->groups[$fieldName] = PivotFieldGroup::date($groupBy, $startDate, $endDate);

        return $this;
    }

    /**
     * Add a value (data) field with the given aggregation function.
     *
     * @param string $subtotal one of the PivotField::SUBTOTAL_* constants
     * @param ?string $caption optional display caption (defaults to e.g. "Sum of Amount")
     */
    public function addDataField(string $fieldName, string $subtotal = PivotField::SUBTOTAL_SUM, ?string $caption = null): self
    {
        return $this->place($fieldName, PivotField::AXIS_VALUES, $subtotal, $caption);
    }

    /**
     * Build the PivotTable model and register it on the target worksheet.
     *
     * @param string $targetCell top-left cell of the pivot table, e.g. "A3"
     */
    public function build(Worksheet $targetWorksheet, string $targetCell, string $name = 'PivotTable1'): PivotTable
    {
        $this->assertHasDataField();

        $cacheDefinition = new PivotCacheDefinition(1);
        $cacheDefinition->setSourceWorksheet($this->sourceWorksheet->getTitle());
        $cacheDefinition->setSourceRange($this->sourceRange);
        $cacheDefinition->setCacheFields($this->fieldNames);
        foreach ($this->fieldNames as $fieldName) {
            $cacheDefinition->setSharedItems($fieldName, $this->distinctValues($fieldName));
            if (isset($this->groups[$fieldName])) {
                $cacheDefinition->setFieldGroup($fieldName, $this->groups[$fieldName]);
            }
        }

        $pivotTable = new PivotTable($name);
        $pivotTable->setGenerated(true);
        $pivotTable->setCacheDefinition($cacheDefinition);
        $pivotTable->setLocation($this->targetLocation($targetCell));

        foreach ($this->fieldNames as $index => $fieldName) {
            $field = new PivotField($index, $fieldName);
            if (isset($this->placements[$fieldName])) {
                $placement = $this->placements[$fieldName];
                if ($placement['axis'] === PivotField::AXIS_VALUES) {
                    $field->setDataField(true);
                    $field->setSubtotal($placement['subtotal']);
                    $field->setDataFieldCaption($placement['caption'] ?? $this->defaultCaption($placement['subtotal'], $fieldName));
                } else {
                    $field->setAxis($placement['axis']);
                }
            }
            $pivotTable->addField($field);
        }

        $targetWorksheet->addPivotTable($pivotTable);

        return $pivotTable;
    }

    private function place(string $fieldName, string $axis, ?string $subtotal, ?string $caption): self
    {
        $this->assertFieldExists($fieldName);
        $this->placements[$fieldName] = ['axis' => $axis, 'subtotal' => $subtotal, 'caption' => $caption];

        return $this;
    }

    private function assertFieldExists(string $fieldName): void
    {
        if (!in_array($fieldName, $this->fieldNames, true)) {
            throw new PhpSpreadsheetException("Pivot source field '{$fieldName}' does not exist in range {$this->sourceRange}");
        }
    }

    private function assertHasDataField(): void
    {
        foreach ($this->placements as $placement) {
            if ($placement['axis'] === PivotField::AXIS_VALUES) {
                return;
            }
        }

        throw new PhpSpreadsheetException('A pivot table requires at least one data (value) field');
    }

    /**
     * @return string[]
     */
    private function readHeaderNames(): array
    {
        [$start, $end] = Coordinate::rangeBoundaries($this->sourceRange);
        $startColumn = (int) $start[0];
        $endColumn = (int) $end[0];
        $headerRow = (int) $start[1];
        $names = [];
        for ($col = $startColumn; $col <= $endColumn; ++$col) {
            $cell = Coordinate::stringFromColumnIndex($col) . $headerRow;
            $names[] = $this->sourceWorksheet->getCell($cell)->getValueString();
        }

        return $names;
    }

    /**
     * Distinct string values in a field's data rows, in first-seen order.
     *
     * @return string[]
     */
    private function distinctValues(string $fieldName): array
    {
        $fieldIndex = array_search($fieldName, $this->fieldNames, true);
        if ($fieldIndex === false) {
            return [];
        }

        [$start, $end] = Coordinate::rangeBoundaries($this->sourceRange);
        $column = Coordinate::stringFromColumnIndex((int) $start[0] + (int) $fieldIndex);
        $firstDataRow = (int) $start[1] + 1;
        $lastRow = (int) $end[1];

        $values = [];
        for ($row = $firstDataRow; $row <= $lastRow; ++$row) {
            $value = $this->sourceWorksheet->getCell($column . $row)->getValueString();
            if ($value === '') {
                continue;
            }
            $values[$value] = true;
        }

        /** @var string[] $result */
        $result = array_keys($values);

        return $result;
    }

    /**
     * Resolve the pivot table's rendered range. The exact extent is recomputed
     * by the spreadsheet application on refresh; a single-cell ref is a valid
     * starting point that Excel expands.
     */
    private function targetLocation(string $targetCell): string
    {
        $targetCell = str_replace('$', '', $targetCell);

        return "$targetCell:$targetCell";
    }

    private function defaultCaption(?string $subtotal, string $fieldName): string
    {
        $labels = [
            PivotField::SUBTOTAL_SUM => 'Sum',
            PivotField::SUBTOTAL_COUNT => 'Count',
            PivotField::SUBTOTAL_AVERAGE => 'Average',
            PivotField::SUBTOTAL_MAX => 'Max',
            PivotField::SUBTOTAL_MIN => 'Min',
            PivotField::SUBTOTAL_PRODUCT => 'Product',
            PivotField::SUBTOTAL_COUNT_NUMS => 'Count',
            PivotField::SUBTOTAL_STD_DEV => 'StdDev',
            PivotField::SUBTOTAL_STD_DEV_P => 'StdDevp',
            PivotField::SUBTOTAL_VAR => 'Var',
            PivotField::SUBTOTAL_VAR_P => 'Varp',
        ];
        $label = $labels[$subtotal] ?? 'Sum';

        return "{$label} of {$fieldName}";
    }
}

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Worksheet\PivotTable;
4
5	use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
6	use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
7	use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
8
9	/**
10	* Fluent builder for creating a new pivot table from a range of source data.
11	*
12	* The builder reads the header row of the source range to discover field names,
13	* lets you place those fields on the row / column / value axes, and produces a
14	* PivotTable model. When the spreadsheet is saved as Xlsx, the pivot parts are
15	* generated with refreshOnLoad set, so the value cells are computed by the
16	* spreadsheet application when the file is opened.
17	*
18	* Example:
19	*
20	* ```php
21	* $builder = new PivotTableBuilder($dataSheet, 'A1:C100');
22	* $builder->addRowField('Region')
23	* ->addColumnField('Product')
24	* ->addDataField('Amount', PivotField::SUBTOTAL_SUM);
25	* $pivotTable = $builder->build($pivotSheet, 'A3');
26	* ```
27	*/
28	class PivotTableBuilder
29	{
30	private Worksheet $sourceWorksheet;
31
32	private string $sourceRange;
33
34	/**
35	* Field names in source-column order, taken from the header row.
36	*
37	* @var string[]
38	*/
39	private array $fieldNames;
40
41	/**
42	* Requested placements, keyed by field name.
43	*
44	* @var array<string, array{axis: string, subtotal: ?string, caption: ?string}>
45	*/
46	private array $placements = [];
47
48	/**
49	* Requested field groupings, keyed by field name.
50	*
51	* @var array<string, PivotFieldGroup>
52	*/
53	private array $groups = [];
54
55	/**
56	* @param string $sourceRange the source data range including the header row, e.g. "A1:C100"
57	*/
58	public function __construct(Worksheet $sourceWorksheet, string $sourceRange)	14✔
59	{
60	$this->sourceWorksheet = $sourceWorksheet;	14✔
61	$this->sourceRange = str_replace('$', '', $sourceRange);	14✔
62	$this->fieldNames = $this->readHeaderNames();	14✔
63	}
64
65	/**
66	* Place a field on the row axis.
67	*/
68	public function addRowField(string $fieldName): self	13✔
69	{
70	return $this->place($fieldName, PivotField::AXIS_ROW, null, null);	13✔
71	}
72
73	/**
74	* Place a field on the column axis.
75	*/
76	public function addColumnField(string $fieldName): self	5✔
77	{
78	return $this->place($fieldName, PivotField::AXIS_COLUMN, null, null);	5✔
79	}
80
81	/**
82	* Place a field on the page (report filter) axis.
83	*/
84	public function addPageField(string $fieldName): self	2✔
85	{
86	return $this->place($fieldName, PivotField::AXIS_PAGE, null, null);	2✔
87	}
88
89	/**
90	* Group a numeric field into fixed-width buckets (e.g. 0-100, 100-200).
91	*
92	* @param float $interval bucket width
93	* @param ?float $startNum lower bound of the first bucket (auto when null)
94	* @param ?float $endNum upper bound of the last bucket (auto when null)
95	*/
96	public function groupFieldByNumericRange(string $fieldName, float $interval, ?float $startNum = null, ?float $endNum = null): self	3✔
97	{
98	$this->assertFieldExists($fieldName);	3✔
99	$this->groups[$fieldName] = PivotFieldGroup::numeric($interval, $startNum, $endNum);	2✔
100
101	return $this;	2✔
102	}
103
104	/**
105	* Group a date/time field by one or more calendar units.
106	*
107	* @param string\|string[] $groupBy one or more PivotFieldGroup::GROUP_BY_* constants
108	* @param ?string $startDate ISO-8601 start (auto when null)
109	* @param ?string $endDate ISO-8601 end (auto when null)
110	*/
111	public function groupFieldByDate(string $fieldName, array\|string $groupBy, ?string $startDate = null, ?string $endDate = null): self	2✔
112	{
113	$this->assertFieldExists($fieldName);	2✔
114	$this->groups[$fieldName] = PivotFieldGroup::date($groupBy, $startDate, $endDate);	2✔
115
116	return $this;	2✔
117	}
118
119	/**
120	* Add a value (data) field with the given aggregation function.
121	*
122	* @param string $subtotal one of the PivotField::SUBTOTAL_* constants
123	* @param ?string $caption optional display caption (defaults to e.g. "Sum of Amount")
124	*/
125	public function addDataField(string $fieldName, string $subtotal = PivotField::SUBTOTAL_SUM, ?string $caption = null): self	11✔
126	{
127	return $this->place($fieldName, PivotField::AXIS_VALUES, $subtotal, $caption);	11✔
128	}
129
130	/**
131	* Build the PivotTable model and register it on the target worksheet.
132	*
133	* @param string $targetCell top-left cell of the pivot table, e.g. "A3"
134	*/
135	public function build(Worksheet $targetWorksheet, string $targetCell, string $name = 'PivotTable1'): PivotTable	12✔
136	{
137	$this->assertHasDataField();	12✔
138
139	$cacheDefinition = new PivotCacheDefinition(1);	11✔
140	$cacheDefinition->setSourceWorksheet($this->sourceWorksheet->getTitle());	11✔
141	$cacheDefinition->setSourceRange($this->sourceRange);	11✔
142	$cacheDefinition->setCacheFields($this->fieldNames);	11✔
143	foreach ($this->fieldNames as $fieldName) {	11✔
144	$cacheDefinition->setSharedItems($fieldName, $this->distinctValues($fieldName));	11✔
145	if (isset($this->groups[$fieldName])) {	11✔
146	$cacheDefinition->setFieldGroup($fieldName, $this->groups[$fieldName]);	3✔
147	}
148	}
149
150	$pivotTable = new PivotTable($name);	11✔
151	$pivotTable->setGenerated(true);	11✔
152	$pivotTable->setCacheDefinition($cacheDefinition);	11✔
153	$pivotTable->setLocation($this->targetLocation($targetCell));	11✔
154
155	foreach ($this->fieldNames as $index => $fieldName) {	11✔
156	$field = new PivotField($index, $fieldName);	11✔
157	if (isset($this->placements[$fieldName])) {	11✔
158	$placement = $this->placements[$fieldName];	11✔
159	if ($placement['axis'] === PivotField::AXIS_VALUES) {	11✔
160	$field->setDataField(true);	11✔
161	$field->setSubtotal($placement['subtotal']);	11✔
162	$field->setDataFieldCaption($placement['caption'] ?? $this->defaultCaption($placement['subtotal'], $fieldName));	11✔
163	} else {
164	$field->setAxis($placement['axis']);	11✔
165	}
166	}
167	$pivotTable->addField($field);	11✔
168	}
169
170	$targetWorksheet->addPivotTable($pivotTable);	11✔
171
172	return $pivotTable;	11✔
173	}
174
175	private function place(string $fieldName, string $axis, ?string $subtotal, ?string $caption): self	13✔
176	{
177	$this->assertFieldExists($fieldName);	13✔
178	$this->placements[$fieldName] = ['axis' => $axis, 'subtotal' => $subtotal, 'caption' => $caption];	12✔
179
180	return $this;	12✔
181	}
182
183	private function assertFieldExists(string $fieldName): void	14✔
184	{
185	if (!in_array($fieldName, $this->fieldNames, true)) {	14✔
186	throw new PhpSpreadsheetException("Pivot source field '{$fieldName}' does not exist in range {$this->sourceRange}");	2✔
187	}
188	}
189
190	private function assertHasDataField(): void	12✔
191	{
192	foreach ($this->placements as $placement) {	12✔
193	if ($placement['axis'] === PivotField::AXIS_VALUES) {	12✔
194	return;	11✔
195	}
196	}
197
198	throw new PhpSpreadsheetException('A pivot table requires at least one data (value) field');	1✔
199	}
200
201	/**
202	* @return string[]
203	*/
204	private function readHeaderNames(): array	14✔
205	{
206	[$start, $end] = Coordinate::rangeBoundaries($this->sourceRange);	14✔
207	$startColumn = (int) $start[0];	14✔
208	$endColumn = (int) $end[0];	14✔
209	$headerRow = (int) $start[1];	14✔
210	$names = [];	14✔
211	for ($col = $startColumn; $col <= $endColumn; ++$col) {	14✔
212	$cell = Coordinate::stringFromColumnIndex($col) . $headerRow;	14✔
213	$names[] = $this->sourceWorksheet->getCell($cell)->getValueString();	14✔
214	}
215
216	return $names;	14✔
217	}
218
219	/**
220	* Distinct string values in a field's data rows, in first-seen order.
221	*
222	* @return string[]
223	*/
224	private function distinctValues(string $fieldName): array	11✔
225	{
226	$fieldIndex = array_search($fieldName, $this->fieldNames, true);	11✔
227	if ($fieldIndex === false) {	11✔
NEW 228	return [];	×
229	}
230
231	[$start, $end] = Coordinate::rangeBoundaries($this->sourceRange);	11✔
232	$column = Coordinate::stringFromColumnIndex((int) $start[0] + (int) $fieldIndex);	11✔
233	$firstDataRow = (int) $start[1] + 1;	11✔
234	$lastRow = (int) $end[1];	11✔
235
236	$values = [];	11✔
237	for ($row = $firstDataRow; $row <= $lastRow; ++$row) {	11✔
238	$value = $this->sourceWorksheet->getCell($column . $row)->getValueString();	11✔
239	if ($value === '') {	11✔
NEW 240	continue;	×
241	}
242	$values[$value] = true;	11✔
243	}
244
245	/** @var string[] $result */
246	$result = array_keys($values);	11✔
247
248	return $result;	11✔
249	}
250
251	/**
252	* Resolve the pivot table's rendered range. The exact extent is recomputed
253	* by the spreadsheet application on refresh; a single-cell ref is a valid
254	* starting point that Excel expands.
255	*/
256	private function targetLocation(string $targetCell): string	11✔
257	{
258	$targetCell = str_replace('$', '', $targetCell);	11✔
259
260	return "$targetCell:$targetCell";	11✔
261	}
262
263	private function defaultCaption(?string $subtotal, string $fieldName): string	11✔
264	{
265	$labels = [	11✔
266	PivotField::SUBTOTAL_SUM => 'Sum',	11✔
267	PivotField::SUBTOTAL_COUNT => 'Count',	11✔
268	PivotField::SUBTOTAL_AVERAGE => 'Average',	11✔
269	PivotField::SUBTOTAL_MAX => 'Max',	11✔
270	PivotField::SUBTOTAL_MIN => 'Min',	11✔
271	PivotField::SUBTOTAL_PRODUCT => 'Product',	11✔
272	PivotField::SUBTOTAL_COUNT_NUMS => 'Count',	11✔
273	PivotField::SUBTOTAL_STD_DEV => 'StdDev',	11✔
274	PivotField::SUBTOTAL_STD_DEV_P => 'StdDevp',	11✔
275	PivotField::SUBTOTAL_VAR => 'Var',	11✔
276	PivotField::SUBTOTAL_VAR_P => 'Varp',	11✔
277	];	11✔
278	$label = $labels[$subtotal] ?? 'Sum';	11✔
279
280	return "{$label} of {$fieldName}";	11✔
281	}
282	}

PHPOffice / PhpSpreadsheet / 30319021002

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous