ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
DefinedNames.php
Go to the documentation of this file.
1<?php
2
4
11
13{
14 private $objWriter;
15
16 private $spreadsheet;
17
19 {
20 $this->objWriter = $objWriter;
21 $this->spreadsheet = $spreadsheet;
22 }
23
24 public function write(): void
25 {
26 // Write defined names
27 $this->objWriter->startElement('definedNames');
28
29 // Named ranges
30 if (count($this->spreadsheet->getDefinedNames()) > 0) {
31 // Named ranges
33 }
34
35 // Other defined names
36 $sheetCount = $this->spreadsheet->getSheetCount();
37 for ($i = 0; $i < $sheetCount; ++$i) {
38 // NamedRange for autoFilter
39 $this->writeNamedRangeForAutofilter($this->spreadsheet->getSheet($i), $i);
40
41 // NamedRange for Print_Titles
42 $this->writeNamedRangeForPrintTitles($this->spreadsheet->getSheet($i), $i);
43
44 // NamedRange for Print_Area
45 $this->writeNamedRangeForPrintArea($this->spreadsheet->getSheet($i), $i);
46 }
47
48 $this->objWriter->endElement();
49 }
50
54 private function writeNamedRangesAndFormulae(): void
55 {
56 // Loop named ranges
57 $definedNames = $this->spreadsheet->getDefinedNames();
58 foreach ($definedNames as $definedName) {
59 $this->writeDefinedName($definedName);
60 }
61 }
62
66 private function writeDefinedName(DefinedName $pDefinedName): void
67 {
68 // definedName for named range
69 $this->objWriter->startElement('definedName');
70 $this->objWriter->writeAttribute('name', $pDefinedName->getName());
71 if ($pDefinedName->getLocalOnly() && $pDefinedName->getScope() !== null) {
72 $this->objWriter->writeAttribute(
73 'localSheetId',
74 $pDefinedName->getScope()->getParent()->getIndex($pDefinedName->getScope())
75 );
76 }
77
78 $definedRange = $this->getDefinedRange($pDefinedName);
79
80 $this->objWriter->writeRawData($definedRange);
81
82 $this->objWriter->endElement();
83 }
84
88 private function writeNamedRangeForAutofilter(Worksheet $pSheet, int $pSheetId = 0): void
89 {
90 // NamedRange for autoFilter
91 $autoFilterRange = $pSheet->getAutoFilter()->getRange();
92 if (!empty($autoFilterRange)) {
93 $this->objWriter->startElement('definedName');
94 $this->objWriter->writeAttribute('name', '_xlnm._FilterDatabase');
95 $this->objWriter->writeAttribute('localSheetId', $pSheetId);
96 $this->objWriter->writeAttribute('hidden', '1');
97
98 // Create absolute coordinate and write as raw text
99 $range = Coordinate::splitRange($autoFilterRange);
100 $range = $range[0];
101 // Strip any worksheet ref so we can make the cell ref absolute
102 [, $range[0]] = Worksheet::extractSheetTitle($range[0], true);
103
104 $range[0] = Coordinate::absoluteCoordinate($range[0]);
105 $range[1] = Coordinate::absoluteCoordinate($range[1]);
106 $range = implode(':', $range);
107
108 $this->objWriter->writeRawData('\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . $range);
109
110 $this->objWriter->endElement();
111 }
112 }
113
117 private function writeNamedRangeForPrintTitles(Worksheet $pSheet, int $pSheetId = 0): void
118 {
119 // NamedRange for PrintTitles
120 if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
121 $this->objWriter->startElement('definedName');
122 $this->objWriter->writeAttribute('name', '_xlnm.Print_Titles');
123 $this->objWriter->writeAttribute('localSheetId', $pSheetId);
124
125 // Setting string
126 $settingString = '';
127
128 // Columns to repeat
129 if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
130 $repeat = $pSheet->getPageSetup()->getColumnsToRepeatAtLeft();
131
132 $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
133 }
134
135 // Rows to repeat
136 if ($pSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
137 if ($pSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
138 $settingString .= ',';
139 }
140
141 $repeat = $pSheet->getPageSetup()->getRowsToRepeatAtTop();
142
143 $settingString .= '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!$' . $repeat[0] . ':$' . $repeat[1];
144 }
145
146 $this->objWriter->writeRawData($settingString);
147
148 $this->objWriter->endElement();
149 }
150 }
151
155 private function writeNamedRangeForPrintArea(Worksheet $pSheet, int $pSheetId = 0): void
156 {
157 // NamedRange for PrintArea
158 if ($pSheet->getPageSetup()->isPrintAreaSet()) {
159 $this->objWriter->startElement('definedName');
160 $this->objWriter->writeAttribute('name', '_xlnm.Print_Area');
161 $this->objWriter->writeAttribute('localSheetId', $pSheetId);
162
163 // Print area
164 $printArea = Coordinate::splitRange($pSheet->getPageSetup()->getPrintArea());
165
166 $chunks = [];
167 foreach ($printArea as $printAreaRect) {
168 $printAreaRect[0] = Coordinate::absoluteReference($printAreaRect[0]);
169 $printAreaRect[1] = Coordinate::absoluteReference($printAreaRect[1]);
170 $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
171 }
172
173 $this->objWriter->writeRawData(implode(',', $chunks));
174
175 $this->objWriter->endElement();
176 }
177 }
178
179 private function getDefinedRange(DefinedName $pDefinedName): string
180 {
181 $definedRange = $pDefinedName->getValue();
182 $splitCount = preg_match_all(
183 '/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/mui',
184 $definedRange,
185 $splitRanges,
186 PREG_OFFSET_CAPTURE
187 );
188
189 $lengths = array_map('strlen', array_column($splitRanges[0], 0));
190 $offsets = array_column($splitRanges[0], 1);
191
192 $worksheets = $splitRanges[2];
193 $columns = $splitRanges[6];
194 $rows = $splitRanges[7];
195
196 while ($splitCount > 0) {
197 --$splitCount;
198 $length = $lengths[$splitCount];
199 $offset = $offsets[$splitCount];
200 $worksheet = $worksheets[$splitCount][0];
201 $column = $columns[$splitCount][0];
202 $row = $rows[$splitCount][0];
203
204 $newRange = '';
205 if (empty($worksheet)) {
206 if (($offset === 0) || ($definedRange[$offset - 1] !== ':')) {
207 // We should have a worksheet
208 $worksheet = $pDefinedName->getWorksheet() ? $pDefinedName->getWorksheet()->getTitle() : null;
209 }
210 } else {
211 $worksheet = str_replace("''", "'", trim($worksheet, "'"));
212 }
213
214 if (!empty($worksheet)) {
215 $newRange = "'" . str_replace("'", "''", $worksheet) . "'!";
216 }
217 $newRange = "{$newRange}{$column}{$row}";
218
219 $definedRange = substr($definedRange, 0, $offset) . $newRange . substr($definedRange, $offset + $length);
220 }
221
222 if (substr($definedRange, 0, 1) === '=') {
223 $definedRange = substr($definedRange, 1);
224 }
225
226 return $definedRange;
227 }
228}
An exception for terminatinating execution or to throw for unit testing.
Helper class to manipulate cell coordinates.
Definition: Coordinate.php:15
static absoluteCoordinate($pCoordinateString)
Make string coordinate absolute.
Definition: Coordinate.php:111
static splitRange($pRange)
Split range into coordinate strings.
Definition: Coordinate.php:140
writeDefinedName(DefinedName $pDefinedName)
Write Defined Name for named range.
writeNamedRangeForAutofilter(Worksheet $pSheet, int $pSheetId=0)
Write Defined Name for autoFilter.
__construct(XMLWriter $objWriter, Spreadsheet $spreadsheet)
writeNamedRangeForPrintTitles(Worksheet $pSheet, int $pSheetId=0)
Write Defined Name for PrintTitles.
writeNamedRangeForPrintArea(Worksheet $pSheet, int $pSheetId=0)
Write Defined Name for PrintTitles.
$i
Definition: disco.tpl.php:19