1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Reader; |
4
|
|
|
|
5
|
|
|
use DateTime; |
6
|
|
|
use DateTimeZone; |
7
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\AddressHelper; |
8
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
9
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataType; |
10
|
|
|
use PhpOffice\PhpSpreadsheet\DefinedName; |
11
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\Security\XmlScanner; |
12
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\Xlsx\Namespaces; |
13
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\Xml\PageSettings; |
14
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\Xml\Properties; |
15
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\Xml\Style; |
16
|
|
|
use PhpOffice\PhpSpreadsheet\RichText\RichText; |
17
|
|
|
use PhpOffice\PhpSpreadsheet\Settings; |
18
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\Date; |
19
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\File; |
20
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\StringHelper; |
21
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet; |
22
|
|
|
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; |
23
|
|
|
use SimpleXMLElement; |
24
|
|
|
|
25
|
|
|
/** |
26
|
|
|
* Reader for SpreadsheetML, the XML schema for Microsoft Office Excel 2003. |
27
|
|
|
*/ |
28
|
|
|
class Xml extends BaseReader |
29
|
|
|
{ |
30
|
|
|
public const NAMESPACES_SS = 'urn:schemas-microsoft-com:office:spreadsheet'; |
31
|
|
|
|
32
|
|
|
/** |
33
|
|
|
* Formats. |
34
|
|
|
*/ |
35
|
|
|
protected array $styles = []; |
36
|
|
|
|
37
|
|
|
/** |
38
|
|
|
* Create a new Excel2003XML Reader instance. |
39
|
|
|
*/ |
40
|
64 |
|
public function __construct() |
41
|
|
|
{ |
42
|
64 |
|
parent::__construct(); |
43
|
64 |
|
$this->securityScanner = XmlScanner::getInstance($this); |
44
|
|
|
} |
45
|
|
|
|
46
|
|
|
private string $fileContents = ''; |
47
|
|
|
|
48
|
38 |
|
public static function xmlMappings(): array |
49
|
|
|
{ |
50
|
38 |
|
return array_merge( |
51
|
38 |
|
Style\Fill::FILL_MAPPINGS, |
52
|
38 |
|
Style\Border::BORDER_MAPPINGS |
53
|
38 |
|
); |
54
|
|
|
} |
55
|
|
|
|
56
|
|
|
/** |
57
|
|
|
* Can the current IReader read the file? |
58
|
|
|
*/ |
59
|
45 |
|
public function canRead(string $filename): bool |
60
|
|
|
{ |
61
|
|
|
// Office xmlns:o="urn:schemas-microsoft-com:office:office" |
62
|
|
|
// Excel xmlns:x="urn:schemas-microsoft-com:office:excel" |
63
|
|
|
// XML Spreadsheet xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" |
64
|
|
|
// Spreadsheet component xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" |
65
|
|
|
// XML schema xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" |
66
|
|
|
// XML data type xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" |
67
|
|
|
// MS-persist recordset xmlns:rs="urn:schemas-microsoft-com:rowset" |
68
|
|
|
// Rowset xmlns:z="#RowsetSchema" |
69
|
|
|
// |
70
|
|
|
|
71
|
45 |
|
$signature = [ |
72
|
45 |
|
'<?xml version="1.0"', |
73
|
45 |
|
'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet', |
74
|
45 |
|
]; |
75
|
|
|
|
76
|
|
|
// Open file |
77
|
45 |
|
$data = file_get_contents($filename) ?: ''; |
78
|
|
|
|
79
|
|
|
// Why? |
80
|
|
|
//$data = str_replace("'", '"', $data); // fix headers with single quote |
81
|
|
|
|
82
|
45 |
|
$valid = true; |
83
|
45 |
|
foreach ($signature as $match) { |
84
|
|
|
// every part of the signature must be present |
85
|
45 |
|
if (!str_contains($data, $match)) { |
86
|
16 |
|
$valid = false; |
87
|
|
|
|
88
|
16 |
|
break; |
89
|
|
|
} |
90
|
|
|
} |
91
|
|
|
|
92
|
|
|
// Retrieve charset encoding |
93
|
45 |
|
if (preg_match('/<?xml.*encoding=[\'"](.*?)[\'"].*?>/m', $data, $matches)) { |
94
|
10 |
|
$charSet = strtoupper($matches[1]); |
95
|
10 |
|
if (preg_match('/^ISO-8859-\d[\dL]?$/i', $charSet) === 1) { |
96
|
1 |
|
$data = StringHelper::convertEncoding($data, 'UTF-8', $charSet); |
97
|
1 |
|
$data = (string) preg_replace('/(<?xml.*encoding=[\'"]).*?([\'"].*?>)/um', '$1' . 'UTF-8' . '$2', $data, 1); |
98
|
|
|
} |
99
|
|
|
} |
100
|
45 |
|
$this->fileContents = $data; |
101
|
|
|
|
102
|
45 |
|
return $valid; |
103
|
|
|
} |
104
|
|
|
|
105
|
|
|
/** |
106
|
|
|
* Check if the file is a valid SimpleXML. |
107
|
|
|
* |
108
|
|
|
* @return false|SimpleXMLElement |
109
|
|
|
*/ |
110
|
37 |
|
public function trySimpleXMLLoadString(string $filename): SimpleXMLElement|bool |
111
|
|
|
{ |
112
|
|
|
try { |
113
|
37 |
|
$xml = simplexml_load_string( |
114
|
37 |
|
$this->getSecurityScannerOrThrow()->scan($this->fileContents ?: file_get_contents($filename)), |
115
|
37 |
|
'SimpleXMLElement', |
116
|
37 |
|
Settings::getLibXmlLoaderOptions() |
117
|
37 |
|
); |
118
|
1 |
|
} catch (\Exception $e) { |
119
|
1 |
|
throw new Exception('Cannot load invalid XML file: ' . $filename, 0, $e); |
120
|
|
|
} |
121
|
36 |
|
$this->fileContents = ''; |
122
|
|
|
|
123
|
36 |
|
return $xml; |
124
|
|
|
} |
125
|
|
|
|
126
|
|
|
/** |
127
|
|
|
* Reads names of the worksheets from a file, without parsing the whole file to a Spreadsheet object. |
128
|
|
|
*/ |
129
|
4 |
|
public function listWorksheetNames(string $filename): array |
130
|
|
|
{ |
131
|
4 |
|
File::assertFile($filename); |
132
|
4 |
|
if (!$this->canRead($filename)) { |
133
|
2 |
|
throw new Exception($filename . ' is an Invalid Spreadsheet file.'); |
134
|
|
|
} |
135
|
|
|
|
136
|
2 |
|
$worksheetNames = []; |
137
|
|
|
|
138
|
2 |
|
$xml = $this->trySimpleXMLLoadString($filename); |
139
|
2 |
|
if ($xml === false) { |
140
|
1 |
|
throw new Exception("Problem reading {$filename}"); |
141
|
|
|
} |
142
|
|
|
|
143
|
1 |
|
$xml_ss = $xml->children(self::NAMESPACES_SS); |
144
|
1 |
|
foreach ($xml_ss->Worksheet as $worksheet) { |
145
|
1 |
|
$worksheet_ss = self::getAttributes($worksheet, self::NAMESPACES_SS); |
146
|
1 |
|
$worksheetNames[] = (string) $worksheet_ss['Name']; |
147
|
|
|
} |
148
|
|
|
|
149
|
1 |
|
return $worksheetNames; |
150
|
|
|
} |
151
|
|
|
|
152
|
|
|
/** |
153
|
|
|
* Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns). |
154
|
|
|
*/ |
155
|
4 |
|
public function listWorksheetInfo(string $filename): array |
156
|
|
|
{ |
157
|
4 |
|
File::assertFile($filename); |
158
|
4 |
|
if (!$this->canRead($filename)) { |
159
|
2 |
|
throw new Exception($filename . ' is an Invalid Spreadsheet file.'); |
160
|
|
|
} |
161
|
|
|
|
162
|
2 |
|
$worksheetInfo = []; |
163
|
|
|
|
164
|
2 |
|
$xml = $this->trySimpleXMLLoadString($filename); |
165
|
2 |
|
if ($xml === false) { |
166
|
1 |
|
throw new Exception("Problem reading {$filename}"); |
167
|
|
|
} |
168
|
|
|
|
169
|
1 |
|
$worksheetID = 1; |
170
|
1 |
|
$xml_ss = $xml->children(self::NAMESPACES_SS); |
171
|
1 |
|
foreach ($xml_ss->Worksheet as $worksheet) { |
172
|
1 |
|
$worksheet_ss = self::getAttributes($worksheet, self::NAMESPACES_SS); |
173
|
|
|
|
174
|
1 |
|
$tmpInfo = []; |
175
|
1 |
|
$tmpInfo['worksheetName'] = ''; |
176
|
1 |
|
$tmpInfo['lastColumnLetter'] = 'A'; |
177
|
1 |
|
$tmpInfo['lastColumnIndex'] = 0; |
178
|
1 |
|
$tmpInfo['totalRows'] = 0; |
179
|
1 |
|
$tmpInfo['totalColumns'] = 0; |
180
|
|
|
|
181
|
1 |
|
$tmpInfo['worksheetName'] = "Worksheet_{$worksheetID}"; |
182
|
1 |
|
if (isset($worksheet_ss['Name'])) { |
183
|
1 |
|
$tmpInfo['worksheetName'] = (string) $worksheet_ss['Name']; |
184
|
|
|
} |
185
|
|
|
|
186
|
1 |
|
if (isset($worksheet->Table->Row)) { |
187
|
1 |
|
$rowIndex = 0; |
188
|
|
|
|
189
|
1 |
|
foreach ($worksheet->Table->Row as $rowData) { |
190
|
1 |
|
$columnIndex = 0; |
191
|
1 |
|
$rowHasData = false; |
192
|
|
|
|
193
|
1 |
|
foreach ($rowData->Cell as $cell) { |
194
|
1 |
|
if (isset($cell->Data)) { |
195
|
1 |
|
$tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex); |
196
|
1 |
|
$rowHasData = true; |
197
|
|
|
} |
198
|
|
|
|
199
|
1 |
|
++$columnIndex; |
200
|
|
|
} |
201
|
|
|
|
202
|
1 |
|
++$rowIndex; |
203
|
|
|
|
204
|
1 |
|
if ($rowHasData) { |
205
|
1 |
|
$tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex); |
206
|
|
|
} |
207
|
|
|
} |
208
|
|
|
} |
209
|
|
|
|
210
|
1 |
|
$tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1); |
211
|
1 |
|
$tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1; |
212
|
|
|
|
213
|
1 |
|
$worksheetInfo[] = $tmpInfo; |
214
|
1 |
|
++$worksheetID; |
215
|
|
|
} |
216
|
|
|
|
217
|
1 |
|
return $worksheetInfo; |
218
|
|
|
} |
219
|
|
|
|
220
|
|
|
/** |
221
|
|
|
* Loads Spreadsheet from string. |
222
|
|
|
*/ |
223
|
7 |
|
public function loadSpreadsheetFromString(string $contents): Spreadsheet |
224
|
|
|
{ |
225
|
|
|
// Create new Spreadsheet |
226
|
7 |
|
$spreadsheet = new Spreadsheet(); |
227
|
7 |
|
$spreadsheet->removeSheetByIndex(0); |
228
|
|
|
|
229
|
|
|
// Load into this instance |
230
|
7 |
|
return $this->loadIntoExisting($contents, $spreadsheet, true); |
231
|
|
|
} |
232
|
|
|
|
233
|
|
|
/** |
234
|
|
|
* Loads Spreadsheet from file. |
235
|
|
|
*/ |
236
|
27 |
|
protected function loadSpreadsheetFromFile(string $filename): Spreadsheet |
237
|
|
|
{ |
238
|
|
|
// Create new Spreadsheet |
239
|
27 |
|
$spreadsheet = new Spreadsheet(); |
240
|
27 |
|
$spreadsheet->removeSheetByIndex(0); |
241
|
|
|
|
242
|
|
|
// Load into this instance |
243
|
27 |
|
return $this->loadIntoExisting($filename, $spreadsheet); |
244
|
|
|
} |
245
|
|
|
|
246
|
|
|
/** |
247
|
|
|
* Loads from file or contents into Spreadsheet instance. |
248
|
|
|
* |
249
|
|
|
* @param string $filename file name if useContents is false else file contents |
250
|
|
|
*/ |
251
|
34 |
|
public function loadIntoExisting(string $filename, Spreadsheet $spreadsheet, bool $useContents = false): Spreadsheet |
252
|
|
|
{ |
253
|
34 |
|
if ($useContents) { |
254
|
7 |
|
$this->fileContents = $filename; |
255
|
|
|
} else { |
256
|
27 |
|
File::assertFile($filename); |
257
|
27 |
|
if (!$this->canRead($filename)) { |
258
|
2 |
|
throw new Exception($filename . ' is an Invalid Spreadsheet file.'); |
259
|
|
|
} |
260
|
|
|
} |
261
|
|
|
|
262
|
32 |
|
$xml = $this->trySimpleXMLLoadString($filename); |
263
|
32 |
|
if ($xml === false) { |
264
|
1 |
|
throw new Exception("Problem reading {$filename}"); |
265
|
|
|
} |
266
|
|
|
|
267
|
31 |
|
$namespaces = $xml->getNamespaces(true); |
268
|
|
|
|
269
|
31 |
|
(new Properties($spreadsheet))->readProperties($xml, $namespaces); |
270
|
|
|
|
271
|
31 |
|
$this->styles = (new Style())->parseStyles($xml, $namespaces); |
272
|
31 |
|
if (isset($this->styles['Default'])) { |
273
|
27 |
|
$spreadsheet->getCellXfCollection()[0]->applyFromArray($this->styles['Default']); |
274
|
|
|
} |
275
|
|
|
|
276
|
31 |
|
$worksheetID = 0; |
277
|
31 |
|
$xml_ss = $xml->children(self::NAMESPACES_SS); |
278
|
|
|
|
279
|
|
|
/** @var null|SimpleXMLElement $worksheetx */ |
280
|
31 |
|
foreach ($xml_ss->Worksheet as $worksheetx) { |
281
|
31 |
|
$worksheet = $worksheetx ?? new SimpleXMLElement('<xml></xml>'); |
282
|
31 |
|
$worksheet_ss = self::getAttributes($worksheet, self::NAMESPACES_SS); |
283
|
|
|
|
284
|
|
|
if ( |
285
|
31 |
|
isset($this->loadSheetsOnly, $worksheet_ss['Name']) |
286
|
31 |
|
&& (!in_array($worksheet_ss['Name'], $this->loadSheetsOnly)) |
287
|
|
|
) { |
288
|
2 |
|
continue; |
289
|
|
|
} |
290
|
|
|
|
291
|
|
|
// Create new Worksheet |
292
|
30 |
|
$spreadsheet->createSheet(); |
293
|
30 |
|
$spreadsheet->setActiveSheetIndex($worksheetID); |
294
|
30 |
|
$worksheetName = ''; |
295
|
30 |
|
if (isset($worksheet_ss['Name'])) { |
296
|
30 |
|
$worksheetName = (string) $worksheet_ss['Name']; |
297
|
|
|
// Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in |
298
|
|
|
// formula cells... during the load, all formulae should be correct, and we're simply bringing |
299
|
|
|
// the worksheet name in line with the formula, not the reverse |
300
|
30 |
|
$spreadsheet->getActiveSheet()->setTitle($worksheetName, false, false); |
301
|
|
|
} |
302
|
30 |
|
if (isset($worksheet_ss['Protected'])) { |
303
|
2 |
|
$protection = (string) $worksheet_ss['Protected'] === '1'; |
304
|
2 |
|
$spreadsheet->getActiveSheet()->getProtection()->setSheet($protection); |
305
|
|
|
} |
306
|
|
|
|
307
|
|
|
// locally scoped defined names |
308
|
30 |
|
if (isset($worksheet->Names[0])) { |
309
|
2 |
|
foreach ($worksheet->Names[0] as $definedName) { |
310
|
2 |
|
$definedName_ss = self::getAttributes($definedName, self::NAMESPACES_SS); |
311
|
2 |
|
$name = (string) $definedName_ss['Name']; |
312
|
2 |
|
$definedValue = (string) $definedName_ss['RefersTo']; |
313
|
2 |
|
$convertedValue = AddressHelper::convertFormulaToA1($definedValue); |
314
|
2 |
|
if ($convertedValue[0] === '=') { |
315
|
2 |
|
$convertedValue = substr($convertedValue, 1); |
316
|
|
|
} |
317
|
2 |
|
$spreadsheet->addDefinedName(DefinedName::createInstance($name, $spreadsheet->getActiveSheet(), $convertedValue, true)); |
318
|
|
|
} |
319
|
|
|
} |
320
|
|
|
|
321
|
30 |
|
$columnID = 'A'; |
322
|
30 |
|
if (isset($worksheet->Table->Column)) { |
323
|
17 |
|
foreach ($worksheet->Table->Column as $columnData) { |
324
|
17 |
|
$columnData_ss = self::getAttributes($columnData, self::NAMESPACES_SS); |
325
|
17 |
|
$colspan = 0; |
326
|
17 |
|
if (isset($columnData_ss['Span'])) { |
327
|
12 |
|
$spanAttr = (string) $columnData_ss['Span']; |
328
|
12 |
|
if (is_numeric($spanAttr)) { |
329
|
12 |
|
$colspan = max(0, (int) $spanAttr); |
330
|
|
|
} |
331
|
|
|
} |
332
|
17 |
|
if (isset($columnData_ss['Index'])) { |
333
|
14 |
|
$columnID = Coordinate::stringFromColumnIndex((int) $columnData_ss['Index']); |
334
|
|
|
} |
335
|
17 |
|
$columnWidth = null; |
336
|
17 |
|
if (isset($columnData_ss['Width'])) { |
337
|
16 |
|
$columnWidth = $columnData_ss['Width']; |
338
|
|
|
} |
339
|
17 |
|
$columnVisible = null; |
340
|
17 |
|
if (isset($columnData_ss['Hidden'])) { |
341
|
11 |
|
$columnVisible = ((string) $columnData_ss['Hidden']) !== '1'; |
342
|
|
|
} |
343
|
17 |
|
while ($colspan >= 0) { |
344
|
17 |
|
if (isset($columnWidth)) { |
345
|
16 |
|
$spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4); |
346
|
|
|
} |
347
|
17 |
|
if (isset($columnVisible)) { |
348
|
11 |
|
$spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setVisible($columnVisible); |
349
|
|
|
} |
350
|
17 |
|
++$columnID; |
351
|
17 |
|
--$colspan; |
352
|
|
|
} |
353
|
|
|
} |
354
|
|
|
} |
355
|
|
|
|
356
|
30 |
|
$rowID = 1; |
357
|
30 |
|
if (isset($worksheet->Table->Row)) { |
358
|
29 |
|
$additionalMergedCells = 0; |
359
|
29 |
|
foreach ($worksheet->Table->Row as $rowData) { |
360
|
29 |
|
$rowHasData = false; |
361
|
29 |
|
$row_ss = self::getAttributes($rowData, self::NAMESPACES_SS); |
362
|
29 |
|
if (isset($row_ss['Index'])) { |
363
|
5 |
|
$rowID = (int) $row_ss['Index']; |
364
|
|
|
} |
365
|
29 |
|
if (isset($row_ss['Hidden'])) { |
366
|
10 |
|
$rowVisible = ((string) $row_ss['Hidden']) !== '1'; |
367
|
10 |
|
$spreadsheet->getActiveSheet()->getRowDimension($rowID)->setVisible($rowVisible); |
368
|
|
|
} |
369
|
|
|
|
370
|
29 |
|
$columnID = 'A'; |
371
|
29 |
|
foreach ($rowData->Cell as $cell) { |
372
|
29 |
|
$cell_ss = self::getAttributes($cell, self::NAMESPACES_SS); |
373
|
29 |
|
if (isset($cell_ss['Index'])) { |
374
|
14 |
|
$columnID = Coordinate::stringFromColumnIndex((int) $cell_ss['Index']); |
375
|
|
|
} |
376
|
29 |
|
$cellRange = $columnID . $rowID; |
377
|
|
|
|
378
|
29 |
|
if ($this->getReadFilter() !== null) { |
379
|
29 |
|
if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { |
380
|
1 |
|
++$columnID; |
381
|
|
|
|
382
|
1 |
|
continue; |
383
|
|
|
} |
384
|
|
|
} |
385
|
|
|
|
386
|
29 |
|
if (isset($cell_ss['HRef'])) { |
387
|
13 |
|
$spreadsheet->getActiveSheet()->getCell($cellRange)->getHyperlink()->setUrl((string) $cell_ss['HRef']); |
388
|
|
|
} |
389
|
|
|
|
390
|
29 |
|
if ((isset($cell_ss['MergeAcross'])) || (isset($cell_ss['MergeDown']))) { |
391
|
9 |
|
$columnTo = $columnID; |
392
|
9 |
|
if (isset($cell_ss['MergeAcross'])) { |
393
|
9 |
|
$additionalMergedCells += (int) $cell_ss['MergeAcross']; |
394
|
9 |
|
$columnTo = Coordinate::stringFromColumnIndex((int) (Coordinate::columnIndexFromString($columnID) + $cell_ss['MergeAcross'])); |
395
|
|
|
} |
396
|
9 |
|
$rowTo = $rowID; |
397
|
9 |
|
if (isset($cell_ss['MergeDown'])) { |
398
|
9 |
|
$rowTo = $rowTo + $cell_ss['MergeDown']; |
399
|
|
|
} |
400
|
9 |
|
$cellRange .= ':' . $columnTo . $rowTo; |
401
|
9 |
|
$spreadsheet->getActiveSheet()->mergeCells($cellRange, Worksheet::MERGE_CELL_CONTENT_HIDE); |
402
|
|
|
} |
403
|
|
|
|
404
|
29 |
|
$hasCalculatedValue = false; |
405
|
29 |
|
$cellDataFormula = ''; |
406
|
29 |
|
if (isset($cell_ss['Formula'])) { |
407
|
18 |
|
$cellDataFormula = $cell_ss['Formula']; |
408
|
18 |
|
$hasCalculatedValue = true; |
409
|
|
|
} |
410
|
29 |
|
if (isset($cell->Data)) { |
411
|
29 |
|
$cellData = $cell->Data; |
412
|
29 |
|
$cellValue = (string) $cellData; |
413
|
29 |
|
$type = DataType::TYPE_NULL; |
414
|
29 |
|
$cellData_ss = self::getAttributes($cellData, self::NAMESPACES_SS); |
415
|
29 |
|
if (isset($cellData_ss['Type'])) { |
416
|
29 |
|
$cellDataType = $cellData_ss['Type']; |
417
|
|
|
switch ($cellDataType) { |
418
|
|
|
/* |
419
|
|
|
const TYPE_STRING = 's'; |
420
|
|
|
const TYPE_FORMULA = 'f'; |
421
|
|
|
const TYPE_NUMERIC = 'n'; |
422
|
|
|
const TYPE_BOOL = 'b'; |
423
|
|
|
const TYPE_NULL = 'null'; |
424
|
|
|
const TYPE_INLINE = 'inlineStr'; |
425
|
|
|
const TYPE_ERROR = 'e'; |
426
|
|
|
*/ |
427
|
29 |
|
case 'String': |
428
|
25 |
|
$type = DataType::TYPE_STRING; |
429
|
|
|
|
430
|
25 |
|
break; |
431
|
16 |
|
case 'Number': |
432
|
16 |
|
$type = DataType::TYPE_NUMERIC; |
433
|
16 |
|
$cellValue = (float) $cellValue; |
434
|
16 |
|
if (floor($cellValue) == $cellValue) { |
435
|
16 |
|
$cellValue = (int) $cellValue; |
436
|
|
|
} |
437
|
|
|
|
438
|
16 |
|
break; |
439
|
11 |
|
case 'Boolean': |
440
|
9 |
|
$type = DataType::TYPE_BOOL; |
441
|
9 |
|
$cellValue = ($cellValue != 0); |
442
|
|
|
|
443
|
9 |
|
break; |
444
|
11 |
|
case 'DateTime': |
445
|
11 |
|
$type = DataType::TYPE_NUMERIC; |
446
|
11 |
|
$dateTime = new DateTime($cellValue, new DateTimeZone('UTC')); |
447
|
11 |
|
$cellValue = Date::PHPToExcel($dateTime); |
448
|
|
|
|
449
|
11 |
|
break; |
450
|
9 |
|
case 'Error': |
451
|
9 |
|
$type = DataType::TYPE_ERROR; |
452
|
9 |
|
$hasCalculatedValue = false; |
453
|
|
|
|
454
|
9 |
|
break; |
455
|
|
|
} |
456
|
|
|
} |
457
|
|
|
|
458
|
29 |
|
$originalType = $type; |
459
|
29 |
|
if ($hasCalculatedValue) { |
460
|
18 |
|
$type = DataType::TYPE_FORMULA; |
461
|
18 |
|
$columnNumber = Coordinate::columnIndexFromString($columnID); |
462
|
18 |
|
$cellDataFormula = AddressHelper::convertFormulaToA1($cellDataFormula, $rowID, $columnNumber); |
463
|
|
|
} |
464
|
|
|
|
465
|
29 |
|
$spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue), $type); |
466
|
29 |
|
if ($hasCalculatedValue) { |
467
|
18 |
|
$spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue, $originalType === DataType::TYPE_NUMERIC); |
468
|
|
|
} |
469
|
29 |
|
$rowHasData = true; |
470
|
|
|
} |
471
|
|
|
|
472
|
29 |
|
if (isset($cell->Comment)) { |
473
|
10 |
|
$this->parseCellComment($cell->Comment, $spreadsheet, $columnID, $rowID); |
474
|
|
|
} |
475
|
|
|
|
476
|
29 |
|
if (isset($cell_ss['StyleID'])) { |
477
|
17 |
|
$style = (string) $cell_ss['StyleID']; |
478
|
17 |
|
if ((isset($this->styles[$style])) && (!empty($this->styles[$style]))) { |
479
|
|
|
//if (!$spreadsheet->getActiveSheet()->cellExists($columnID . $rowID)) { |
480
|
|
|
// $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValue(null); |
481
|
|
|
//} |
482
|
17 |
|
$spreadsheet->getActiveSheet()->getStyle($cellRange) |
483
|
17 |
|
->applyFromArray($this->styles[$style]); |
484
|
|
|
} |
485
|
|
|
} |
486
|
29 |
|
++$columnID; |
487
|
29 |
|
while ($additionalMergedCells > 0) { |
488
|
9 |
|
++$columnID; |
489
|
9 |
|
--$additionalMergedCells; |
490
|
|
|
} |
491
|
|
|
} |
492
|
|
|
|
493
|
29 |
|
if ($rowHasData) { |
494
|
29 |
|
if (isset($row_ss['Height'])) { |
495
|
12 |
|
$rowHeight = $row_ss['Height']; |
496
|
12 |
|
$spreadsheet->getActiveSheet()->getRowDimension($rowID)->setRowHeight((float) $rowHeight); |
497
|
|
|
} |
498
|
|
|
} |
499
|
|
|
|
500
|
29 |
|
++$rowID; |
501
|
|
|
} |
502
|
|
|
} |
503
|
|
|
|
504
|
30 |
|
$dataValidations = new Xml\DataValidations(); |
505
|
30 |
|
$dataValidations->loadDataValidations($worksheet, $spreadsheet); |
506
|
30 |
|
$xmlX = $worksheet->children(Namespaces::URN_EXCEL); |
507
|
30 |
|
if (isset($xmlX->WorksheetOptions)) { |
508
|
27 |
|
if (isset($xmlX->WorksheetOptions->FreezePanes)) { |
509
|
4 |
|
$freezeRow = $freezeColumn = 1; |
510
|
4 |
|
if (isset($xmlX->WorksheetOptions->SplitHorizontal)) { |
511
|
4 |
|
$freezeRow = (int) $xmlX->WorksheetOptions->SplitHorizontal + 1; |
512
|
|
|
} |
513
|
4 |
|
if (isset($xmlX->WorksheetOptions->SplitVertical)) { |
514
|
4 |
|
$freezeColumn = (int) $xmlX->WorksheetOptions->SplitVertical + 1; |
515
|
|
|
} |
516
|
4 |
|
$leftTopRow = (string) $xmlX->WorksheetOptions->TopRowBottomPane; |
517
|
4 |
|
$leftTopColumn = (string) $xmlX->WorksheetOptions->LeftColumnRightPane; |
518
|
4 |
|
if (is_numeric($leftTopRow) && is_numeric($leftTopColumn)) { |
519
|
4 |
|
$leftTopCoordinate = Coordinate::stringFromColumnIndex((int) $leftTopColumn + 1) . (string) ($leftTopRow + 1); |
520
|
4 |
|
$spreadsheet->getActiveSheet()->freezePane(Coordinate::stringFromColumnIndex($freezeColumn) . (string) $freezeRow, $leftTopCoordinate, !isset($xmlX->WorksheetOptions->FrozenNoSplit)); |
521
|
|
|
} else { |
522
|
4 |
|
$spreadsheet->getActiveSheet()->freezePane(Coordinate::stringFromColumnIndex($freezeColumn) . (string) $freezeRow, null, !isset($xmlX->WorksheetOptions->FrozenNoSplit)); |
523
|
|
|
} |
524
|
26 |
|
} elseif (isset($xmlX->WorksheetOptions->SplitVertical) || isset($xmlX->WorksheetOptions->SplitHorizontal)) { |
525
|
1 |
|
if (isset($xmlX->WorksheetOptions->SplitHorizontal)) { |
526
|
1 |
|
$ySplit = (int) $xmlX->WorksheetOptions->SplitHorizontal; |
527
|
1 |
|
$spreadsheet->getActiveSheet()->setYSplit($ySplit); |
528
|
|
|
} |
529
|
1 |
|
if (isset($xmlX->WorksheetOptions->SplitVertical)) { |
530
|
1 |
|
$xSplit = (int) $xmlX->WorksheetOptions->SplitVertical; |
531
|
1 |
|
$spreadsheet->getActiveSheet()->setXSplit($xSplit); |
532
|
|
|
} |
533
|
1 |
|
if (isset($xmlX->WorksheetOptions->LeftColumnVisible) || isset($xmlX->WorksheetOptions->TopRowVisible)) { |
534
|
1 |
|
$leftTopColumn = $leftTopRow = 1; |
535
|
1 |
|
if (isset($xmlX->WorksheetOptions->LeftColumnVisible)) { |
536
|
1 |
|
$leftTopColumn = 1 + (int) $xmlX->WorksheetOptions->LeftColumnVisible; |
537
|
|
|
} |
538
|
1 |
|
if (isset($xmlX->WorksheetOptions->TopRowVisible)) { |
539
|
1 |
|
$leftTopRow = 1 + (int) $xmlX->WorksheetOptions->TopRowVisible; |
540
|
|
|
} |
541
|
1 |
|
$leftTopCoordinate = Coordinate::stringFromColumnIndex($leftTopColumn) . "$leftTopRow"; |
542
|
1 |
|
$spreadsheet->getActiveSheet()->setTopLeftCell($leftTopCoordinate); |
543
|
|
|
} |
544
|
|
|
|
545
|
1 |
|
$leftTopColumn = $leftTopRow = 1; |
546
|
1 |
|
if (isset($xmlX->WorksheetOptions->LeftColumnRightPane)) { |
547
|
1 |
|
$leftTopColumn = 1 + (int) $xmlX->WorksheetOptions->LeftColumnRightPane; |
548
|
|
|
} |
549
|
1 |
|
if (isset($xmlX->WorksheetOptions->TopRowBottomPane)) { |
550
|
1 |
|
$leftTopRow = 1 + (int) $xmlX->WorksheetOptions->TopRowBottomPane; |
551
|
|
|
} |
552
|
1 |
|
$leftTopCoordinate = Coordinate::stringFromColumnIndex($leftTopColumn) . "$leftTopRow"; |
553
|
1 |
|
$spreadsheet->getActiveSheet()->setPaneTopLeftCell($leftTopCoordinate); |
554
|
|
|
} |
555
|
27 |
|
(new PageSettings($xmlX))->loadPageSettings($spreadsheet); |
556
|
27 |
|
if (isset($xmlX->WorksheetOptions->TopRowVisible, $xmlX->WorksheetOptions->LeftColumnVisible)) { |
557
|
2 |
|
$leftTopRow = (string) $xmlX->WorksheetOptions->TopRowVisible; |
558
|
2 |
|
$leftTopColumn = (string) $xmlX->WorksheetOptions->LeftColumnVisible; |
559
|
2 |
|
if (is_numeric($leftTopRow) && is_numeric($leftTopColumn)) { |
560
|
2 |
|
$leftTopCoordinate = Coordinate::stringFromColumnIndex((int) $leftTopColumn + 1) . (string) ($leftTopRow + 1); |
561
|
2 |
|
$spreadsheet->getActiveSheet()->setTopLeftCell($leftTopCoordinate); |
562
|
|
|
} |
563
|
|
|
} |
564
|
27 |
|
$rangeCalculated = false; |
565
|
27 |
|
if (isset($xmlX->WorksheetOptions->Panes->Pane->RangeSelection)) { |
566
|
13 |
|
if (1 === preg_match('/^R(\d+)C(\d+):R(\d+)C(\d+)$/', (string) $xmlX->WorksheetOptions->Panes->Pane->RangeSelection, $selectionMatches)) { |
567
|
13 |
|
$selectedCell = Coordinate::stringFromColumnIndex((int) $selectionMatches[2]) |
568
|
13 |
|
. $selectionMatches[1] |
569
|
13 |
|
. ':' |
570
|
13 |
|
. Coordinate::stringFromColumnIndex((int) $selectionMatches[4]) |
571
|
13 |
|
. $selectionMatches[3]; |
572
|
13 |
|
$spreadsheet->getActiveSheet()->setSelectedCells($selectedCell); |
573
|
13 |
|
$rangeCalculated = true; |
574
|
|
|
} |
575
|
|
|
} |
576
|
27 |
|
if (!$rangeCalculated) { |
577
|
26 |
|
if (isset($xmlX->WorksheetOptions->Panes->Pane->ActiveRow)) { |
578
|
21 |
|
$activeRow = (string) $xmlX->WorksheetOptions->Panes->Pane->ActiveRow; |
579
|
|
|
} else { |
580
|
7 |
|
$activeRow = 0; |
581
|
|
|
} |
582
|
26 |
|
if (isset($xmlX->WorksheetOptions->Panes->Pane->ActiveCol)) { |
583
|
15 |
|
$activeColumn = (string) $xmlX->WorksheetOptions->Panes->Pane->ActiveCol; |
584
|
|
|
} else { |
585
|
13 |
|
$activeColumn = 0; |
586
|
|
|
} |
587
|
26 |
|
if (is_numeric($activeRow) && is_numeric($activeColumn)) { |
588
|
26 |
|
$selectedCell = Coordinate::stringFromColumnIndex((int) $activeColumn + 1) . (string) ($activeRow + 1); |
589
|
26 |
|
$spreadsheet->getActiveSheet()->setSelectedCells($selectedCell); |
590
|
|
|
} |
591
|
|
|
} |
592
|
|
|
} |
593
|
30 |
|
++$worksheetID; |
594
|
|
|
} |
595
|
|
|
|
596
|
|
|
// Globally scoped defined names |
597
|
31 |
|
$activeSheetIndex = 0; |
598
|
31 |
|
if (isset($xml->ExcelWorkbook->ActiveSheet)) { |
599
|
2 |
|
$activeSheetIndex = (int) (string) $xml->ExcelWorkbook->ActiveSheet; |
600
|
|
|
} |
601
|
31 |
|
$activeWorksheet = $spreadsheet->setActiveSheetIndex($activeSheetIndex); |
602
|
30 |
|
if (isset($xml->Names[0])) { |
603
|
10 |
|
foreach ($xml->Names[0] as $definedName) { |
604
|
10 |
|
$definedName_ss = self::getAttributes($definedName, self::NAMESPACES_SS); |
605
|
10 |
|
$name = (string) $definedName_ss['Name']; |
606
|
10 |
|
$definedValue = (string) $definedName_ss['RefersTo']; |
607
|
10 |
|
$convertedValue = AddressHelper::convertFormulaToA1($definedValue); |
608
|
10 |
|
if ($convertedValue[0] === '=') { |
609
|
10 |
|
$convertedValue = substr($convertedValue, 1); |
610
|
|
|
} |
611
|
10 |
|
$spreadsheet->addDefinedName(DefinedName::createInstance($name, $activeWorksheet, $convertedValue)); |
612
|
|
|
} |
613
|
|
|
} |
614
|
|
|
|
615
|
|
|
// Return |
616
|
30 |
|
return $spreadsheet; |
617
|
|
|
} |
618
|
|
|
|
619
|
10 |
|
protected function parseCellComment( |
620
|
|
|
SimpleXMLElement $comment, |
621
|
|
|
Spreadsheet $spreadsheet, |
622
|
|
|
string $columnID, |
623
|
|
|
int $rowID |
624
|
|
|
): void { |
625
|
10 |
|
$commentAttributes = $comment->attributes(self::NAMESPACES_SS); |
626
|
10 |
|
$author = 'unknown'; |
627
|
10 |
|
if (isset($commentAttributes->Author)) { |
628
|
9 |
|
$author = (string) $commentAttributes->Author; |
629
|
|
|
} |
630
|
|
|
|
631
|
10 |
|
$node = $comment->Data->asXML(); |
632
|
10 |
|
$annotation = strip_tags((string) $node); |
633
|
10 |
|
$spreadsheet->getActiveSheet()->getComment($columnID . $rowID) |
634
|
10 |
|
->setAuthor($author) |
635
|
10 |
|
->setText($this->parseRichText($annotation)); |
636
|
|
|
} |
637
|
|
|
|
638
|
10 |
|
protected function parseRichText(string $annotation): RichText |
639
|
|
|
{ |
640
|
10 |
|
$value = new RichText(); |
641
|
|
|
|
642
|
10 |
|
$value->createText($annotation); |
643
|
|
|
|
644
|
10 |
|
return $value; |
645
|
|
|
} |
646
|
|
|
|
647
|
33 |
|
private static function getAttributes(?SimpleXMLElement $simple, string $node): SimpleXMLElement |
648
|
|
|
{ |
649
|
33 |
|
return ($simple === null) |
650
|
|
|
? new SimpleXMLElement('<xml></xml>') |
651
|
33 |
|
: ($simple->attributes($node) ?? new SimpleXMLElement('<xml></xml>')); |
652
|
|
|
} |
653
|
|
|
} |
654
|
|
|
|