tablepress / libraries / vendor / PhpSpreadsheet / Worksheet / PivotTable / PivotTableBuilder.php

PivotTableBuilder.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Worksheet/PivotTable/PivotTableBuilder.php

281 lines 8.5 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Worksheet\PivotTable;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate;
6	use TablePress\PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
7	use TablePress\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)
59	{
60	$this->sourceWorksheet = $sourceWorksheet;
61	$this->sourceRange = str_replace('$', '', $sourceRange);
62	$this->fieldNames = $this->readHeaderNames();
63	}
64
65	/**
66	* Place a field on the row axis.
67	*/
68	public function addRowField(string $fieldName): self
69	{
70	return $this->place($fieldName, PivotField::AXIS_ROW, null, null);
71	}
72
73	/**
74	* Place a field on the column axis.
75	*/
76	public function addColumnField(string $fieldName): self
77	{
78	return $this->place($fieldName, PivotField::AXIS_COLUMN, null, null);
79	}
80
81	/**
82	* Place a field on the page (report filter) axis.
83	*/
84	public function addPageField(string $fieldName): self
85	{
86	return $this->place($fieldName, PivotField::AXIS_PAGE, null, null);
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
97	{
98	$this->assertFieldExists($fieldName);
99	$this->groups[$fieldName] = PivotFieldGroup::numeric($interval, $startNum, $endNum);
100
101	return $this;
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, $groupBy, ?string $startDate = null, ?string $endDate = null): self
112	{
113	$this->assertFieldExists($fieldName);
114	$this->groups[$fieldName] = PivotFieldGroup::date($groupBy, $startDate, $endDate);
115
116	return $this;
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
126	{
127	return $this->place($fieldName, PivotField::AXIS_VALUES, $subtotal, $caption);
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
136	{
137	$this->assertHasDataField();
138
139	$cacheDefinition = new PivotCacheDefinition(1);
140	$cacheDefinition->setSourceWorksheet($this->sourceWorksheet->getTitle());
141	$cacheDefinition->setSourceRange($this->sourceRange);
142	$cacheDefinition->setCacheFields($this->fieldNames);
143	foreach ($this->fieldNames as $fieldName) {
144	$cacheDefinition->setSharedItems($fieldName, $this->distinctValues($fieldName));
145	if (isset($this->groups[$fieldName])) {
146	$cacheDefinition->setFieldGroup($fieldName, $this->groups[$fieldName]);
147	}
148	}
149
150	$pivotTable = new PivotTable($name);
151	$pivotTable->setGenerated(true);
152	$pivotTable->setCacheDefinition($cacheDefinition);
153	$pivotTable->setLocation($this->targetLocation($targetCell));
154
155	foreach ($this->fieldNames as $index => $fieldName) {
156	$field = new PivotField($index, $fieldName);
157	if (isset($this->placements[$fieldName])) {
158	$placement = $this->placements[$fieldName];
159	if ($placement['axis'] === PivotField::AXIS_VALUES) {
160	$field->setDataField(true);
161	$field->setSubtotal($placement['subtotal']);
162	$field->setDataFieldCaption($placement['caption'] ?? $this->defaultCaption($placement['subtotal'], $fieldName));
163	} else {
164	$field->setAxis($placement['axis']);
165	}
166	}
167	$pivotTable->addField($field);
168	}
169
170	$targetWorksheet->addPivotTable($pivotTable);
171
172	return $pivotTable;
173	}
174
175	private function place(string $fieldName, string $axis, ?string $subtotal, ?string $caption): self
176	{
177	$this->assertFieldExists($fieldName);
178	$this->placements[$fieldName] = ['axis' => $axis, 'subtotal' => $subtotal, 'caption' => $caption];
179
180	return $this;
181	}
182
183	private function assertFieldExists(string $fieldName): void
184	{
185	if (!in_array($fieldName, $this->fieldNames, true)) {
186	throw new PhpSpreadsheetException("Pivot source field '{$fieldName}' does not exist in range {$this->sourceRange}");
187	}
188	}
189
190	private function assertHasDataField(): void
191	{
192	foreach ($this->placements as $placement) {
193	if ($placement['axis'] === PivotField::AXIS_VALUES) {
194	return;
195	}
196	}
197
198	throw new PhpSpreadsheetException('A pivot table requires at least one data (value) field');
199	}
200
201	/**
202	* @return string[]
203	*/
204	private function readHeaderNames(): array
205	{
206	[$start, $end] = Coordinate::rangeBoundaries($this->sourceRange);
207	$startColumn = (int) $start[0];
208	$endColumn = (int) $end[0];
209	$headerRow = (int) $start[1];
210	$names = [];
211	for ($col = $startColumn; $col <= $endColumn; ++$col) {
212	$cell = Coordinate::stringFromColumnIndex($col) . $headerRow;
213	$names[] = $this->sourceWorksheet->getCell($cell)->getValueString();
214	}
215
216	return $names;
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
225	{
226	$fieldIndex = array_search($fieldName, $this->fieldNames, true);
227	$result = [];
228	if ($fieldIndex !== false) {
229	[$start, $end] = Coordinate::rangeBoundaries($this->sourceRange);
230	$column = Coordinate::stringFromColumnIndex((int) $start[0] + (int) $fieldIndex);
231	$firstDataRow = (int) $start[1] + 1;
232	$lastRow = (int) $end[1];
233
234	$values = [];
235	for ($row = $firstDataRow; $row <= $lastRow; ++$row) {
236	$value = $this->sourceWorksheet->getCell($column . $row)->getValueString();
237	if ($value !== '') {
238	$values[$value] = true;
239	}
240	}
241
242	/** @var string[] $result */
243	$result = array_keys($values);
244	}
245
246	return $result;
247	}
248
249	/**
250	* Resolve the pivot table's rendered range. The exact extent is recomputed
251	* by the spreadsheet application on refresh; a single-cell ref is a valid
252	* starting point that Excel expands.
253	*/
254	private function targetLocation(string $targetCell): string
255	{
256	$targetCell = str_replace('$', '', $targetCell);
257
258	return "$targetCell:$targetCell";
259	}
260
261	private function defaultCaption(?string $subtotal, string $fieldName): string
262	{
263	$labels = [
264	PivotField::SUBTOTAL_SUM => 'Sum',
265	PivotField::SUBTOTAL_COUNT => 'Count',
266	PivotField::SUBTOTAL_AVERAGE => 'Average',
267	PivotField::SUBTOTAL_MAX => 'Max',
268	PivotField::SUBTOTAL_MIN => 'Min',
269	PivotField::SUBTOTAL_PRODUCT => 'Product',
270	PivotField::SUBTOTAL_COUNT_NUMS => 'Count',
271	PivotField::SUBTOTAL_STD_DEV => 'StdDev',
272	PivotField::SUBTOTAL_STD_DEV_P => 'StdDevp',
273	PivotField::SUBTOTAL_VAR => 'Var',
274	PivotField::SUBTOTAL_VAR_P => 'Varp',
275	];
276	$label = $labels[$subtotal] ?? 'Sum';
277
278	return "{$label} of {$fieldName}";
279	}
280	}
281