wpdatatables / lib / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Writer / Xlsx / DefinedNames.php

DefinedNames.php in wpDataTables – WordPress Data Table, Dynamic Tables & Table Charts Plugin 6.5.1.7, at lib/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/DefinedNames.php

245 lines 8.5 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Writer\Xlsx;
4
5	use Exception;
6	use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
7	use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
8	use PhpOffice\PhpSpreadsheet\DefinedName;
9	use PhpOffice\PhpSpreadsheet\Shared\XMLWriter;
10	use PhpOffice\PhpSpreadsheet\Spreadsheet;
11	use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet as ActualWorksheet;
12
13	class DefinedNames
14	{
15	/** @var XMLWriter */
16	private $objWriter;
17
18	/** @var Spreadsheet */
19	private $spreadsheet;
20
21	public function __construct(XMLWriter $objWriter, Spreadsheet $spreadsheet)
22	{
23	$this->objWriter = $objWriter;
24	$this->spreadsheet = $spreadsheet;
25	}
26
27	public function write(): void
28	{
29	// Write defined names
30	$this->objWriter->startElement('definedNames');
31
32	// Named ranges
33	if (count($this->spreadsheet->getDefinedNames()) > 0) {
34	// Named ranges
35	$this->writeNamedRangesAndFormulae();
36	}
37
38	// Other defined names
39	$sheetCount = $this->spreadsheet->getSheetCount();
40	for ($i = 0; $i < $sheetCount; ++$i) {
41	// NamedRange for autoFilter
42	$this->writeNamedRangeForAutofilter($this->spreadsheet->getSheet($i), $i);
43
44	// NamedRange for Print_Titles
45	$this->writeNamedRangeForPrintTitles($this->spreadsheet->getSheet($i), $i);
46
47	// NamedRange for Print_Area
48	$this->writeNamedRangeForPrintArea($this->spreadsheet->getSheet($i), $i);
49	}
50
51	$this->objWriter->endElement();
52	}
53
54	/**
55	* Write defined names.
56	*/
57	private function writeNamedRangesAndFormulae(): void
58	{
59	// Loop named ranges
60	$definedNames = $this->spreadsheet->getDefinedNames();
61	foreach ($definedNames as $definedName) {
62	$this->writeDefinedName($definedName);
63	}
64	}
65
66	/**
67	* Write Defined Name for named range.
68	*/
69	private function writeDefinedName(DefinedName $definedName): void
70	{
71	// definedName for named range
72	$local = -1;
73	if ($definedName->getLocalOnly() && $definedName->getScope() !== null) {
74	try {
75	$local = $definedName->getScope()->getParentOrThrow()->getIndex($definedName->getScope());
76	} catch (Exception $e) {
77	// See issue 2266 - deleting sheet which contains
78	// defined names will cause Exception above.
79	return;
80	}
81	}
82	$this->objWriter->startElement('definedName');
83	$this->objWriter->writeAttribute('name', $definedName->getName());
84	if ($local >= 0) {
85	$this->objWriter->writeAttribute(
86	'localSheetId',
87	"$local"
88	);
89	}
90
91	$definedRange = $this->getDefinedRange($definedName);
92
93	$this->objWriter->writeRawData($definedRange);
94
95	$this->objWriter->endElement();
96	}
97
98	/**
99	* Write Defined Name for autoFilter.
100	*/
101	private function writeNamedRangeForAutofilter(ActualWorksheet $worksheet, int $worksheetId = 0): void
102	{
103	// NamedRange for autoFilter
104	$autoFilterRange = $worksheet->getAutoFilter()->getRange();
105	if (!empty($autoFilterRange)) {
106	$this->objWriter->startElement('definedName');
107	$this->objWriter->writeAttribute('name', '_xlnm._FilterDatabase');
108	$this->objWriter->writeAttribute('localSheetId', "$worksheetId");
109	$this->objWriter->writeAttribute('hidden', '1');
110
111	// Create absolute coordinate and write as raw text
112	$range = Coordinate::splitRange($autoFilterRange);
113	$range = $range[0];
114	// Strip any worksheet ref so we can make the cell ref absolute
115	[, $range[0]] = ActualWorksheet::extractSheetTitle($range[0], true);
116
117	$range[0] = Coordinate::absoluteCoordinate($range[0]);
118	if (count($range) > 1) {
119	$range[1] = Coordinate::absoluteCoordinate($range[1]);
120	}
121	$range = implode(':', $range);
122
123	$this->objWriter->writeRawData('\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!' . $range);
124
125	$this->objWriter->endElement();
126	}
127	}
128
129	/**
130	* Write Defined Name for PrintTitles.
131	*/
132	private function writeNamedRangeForPrintTitles(ActualWorksheet $worksheet, int $worksheetId = 0): void
133	{
134	// NamedRange for PrintTitles
135	if ($worksheet->getPageSetup()->isColumnsToRepeatAtLeftSet() \|\| $worksheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
136	$this->objWriter->startElement('definedName');
137	$this->objWriter->writeAttribute('name', '_xlnm.Print_Titles');
138	$this->objWriter->writeAttribute('localSheetId', "$worksheetId");
139
140	// Setting string
141	$settingString = '';
142
143	// Columns to repeat
144	if ($worksheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
145	$repeat = $worksheet->getPageSetup()->getColumnsToRepeatAtLeft();
146
147	$settingString .= '\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
148	}
149
150	// Rows to repeat
151	if ($worksheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
152	if ($worksheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
153	$settingString .= ',';
154	}
155
156	$repeat = $worksheet->getPageSetup()->getRowsToRepeatAtTop();
157
158	$settingString .= '\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
159	}
160
161	$this->objWriter->writeRawData($settingString);
162
163	$this->objWriter->endElement();
164	}
165	}
166
167	/**
168	* Write Defined Name for PrintTitles.
169	*/
170	private function writeNamedRangeForPrintArea(ActualWorksheet $worksheet, int $worksheetId = 0): void
171	{
172	// NamedRange for PrintArea
173	if ($worksheet->getPageSetup()->isPrintAreaSet()) {
174	$this->objWriter->startElement('definedName');
175	$this->objWriter->writeAttribute('name', '_xlnm.Print_Area');
176	$this->objWriter->writeAttribute('localSheetId', "$worksheetId");
177
178	// Print area
179	$printArea = Coordinate::splitRange($worksheet->getPageSetup()->getPrintArea());
180
181	$chunks = [];
182	foreach ($printArea as $printAreaRect) {
183	$printAreaRect[0] = Coordinate::absoluteReference($printAreaRect[0]);
184	$printAreaRect[1] = Coordinate::absoluteReference($printAreaRect[1]);
185	$chunks[] = '\'' . str_replace("'", "''", $worksheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
186	}
187
188	$this->objWriter->writeRawData(implode(',', $chunks));
189
190	$this->objWriter->endElement();
191	}
192	}
193
194	private function getDefinedRange(DefinedName $definedName): string
195	{
196	$definedRange = $definedName->getValue();
197	$splitCount = preg_match_all(
198	'/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/mui',
199	$definedRange,
200	$splitRanges,
201	PREG_OFFSET_CAPTURE
202	);
203
204	$lengths = array_map('strlen', array_column($splitRanges[0], 0));
205	$offsets = array_column($splitRanges[0], 1);
206
207	$worksheets = $splitRanges[2];
208	$columns = $splitRanges[6];
209	$rows = $splitRanges[7];
210
211	while ($splitCount > 0) {
212	--$splitCount;
213	$length = $lengths[$splitCount];
214	$offset = $offsets[$splitCount];
215	$worksheet = $worksheets[$splitCount][0];
216	$column = $columns[$splitCount][0];
217	$row = $rows[$splitCount][0];
218
219	$newRange = '';
220	if (empty($worksheet)) {
221	if (($offset === 0) \|\| ($definedRange[$offset - 1] !== ':')) {
222	// We should have a worksheet
223	$ws = $definedName->getWorksheet();
224	$worksheet = ($ws === null) ? null : $ws->getTitle();
225	}
226	} else {
227	$worksheet = str_replace("''", "'", trim($worksheet, "'"));
228	}
229
230	if (!empty($worksheet)) {
231	$newRange = "'" . str_replace("'", "''", $worksheet) . "'!";
232	}
233	$newRange = "{$newRange}{$column}{$row}";
234
235	$definedRange = substr($definedRange, 0, $offset) . $newRange . substr($definedRange, $offset + $length);
236	}
237
238	if (substr($definedRange, 0, 1) === '=') {
239	$definedRange = substr($definedRange, 1);
240	}
241
242	return $definedRange;
243	}
244	}
245