|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Reader; |
|
4
|
|
|
|
|
5
|
|
|
use DateTime; |
|
6
|
|
|
use DateTimeZone; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Calculation; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
|
9
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataType; |
|
10
|
|
|
use PhpOffice\PhpSpreadsheet\Document\Properties; |
|
11
|
|
|
use PhpOffice\PhpSpreadsheet\RichText\RichText; |
|
12
|
|
|
use PhpOffice\PhpSpreadsheet\Settings; |
|
13
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\Date; |
|
14
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\File; |
|
15
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet; |
|
16
|
|
|
use PhpOffice\PhpSpreadsheet\Style\NumberFormat; |
|
17
|
|
|
use XMLReader; |
|
18
|
|
|
use ZipArchive; |
|
19
|
|
|
|
|
20
|
|
|
class Ods extends BaseReader |
|
21
|
|
|
{ |
|
22
|
|
|
/** |
|
23
|
|
|
* Create a new Ods Reader instance. |
|
24
|
|
|
*/ |
|
25
|
16 |
|
public function __construct() |
|
26
|
|
|
{ |
|
27
|
16 |
|
$this->readFilter = new DefaultReadFilter(); |
|
28
|
16 |
|
} |
|
29
|
|
|
|
|
30
|
|
|
/** |
|
31
|
|
|
* Can the current IReader read the file? |
|
32
|
|
|
* |
|
33
|
|
|
* @param string $pFilename |
|
34
|
|
|
* |
|
35
|
|
|
* @throws Exception |
|
36
|
|
|
* |
|
37
|
|
|
* @return bool |
|
38
|
|
|
*/ |
|
39
|
3 |
|
public function canRead($pFilename) |
|
40
|
|
|
{ |
|
41
|
3 |
|
File::assertFile($pFilename); |
|
42
|
|
|
|
|
43
|
3 |
|
$mimeType = 'UNKNOWN'; |
|
44
|
|
|
|
|
45
|
|
|
// Load file |
|
46
|
|
|
|
|
47
|
3 |
|
$zip = new ZipArchive(); |
|
48
|
3 |
|
if ($zip->open($pFilename) === true) { |
|
49
|
|
|
// check if it is an OOXML archive |
|
50
|
3 |
|
$stat = $zip->statName('mimetype'); |
|
51
|
3 |
|
if ($stat && ($stat['size'] <= 255)) { |
|
|
|
|
|
|
52
|
3 |
|
$mimeType = $zip->getFromName($stat['name']); |
|
53
|
|
|
} elseif ($stat = $zip->statName('META-INF/manifest.xml')) { |
|
|
|
|
|
|
54
|
|
|
$xml = simplexml_load_string( |
|
55
|
|
|
$this->securityScan($zip->getFromName('META-INF/manifest.xml')), |
|
56
|
|
|
'SimpleXMLElement', |
|
57
|
|
|
Settings::getLibXmlLoaderOptions() |
|
58
|
|
|
); |
|
59
|
|
|
$namespacesContent = $xml->getNamespaces(true); |
|
60
|
|
|
if (isset($namespacesContent['manifest'])) { |
|
61
|
|
|
$manifest = $xml->children($namespacesContent['manifest']); |
|
62
|
|
|
foreach ($manifest as $manifestDataSet) { |
|
63
|
|
|
$manifestAttributes = $manifestDataSet->attributes($namespacesContent['manifest']); |
|
64
|
|
|
if ($manifestAttributes->{'full-path'} == '/') { |
|
65
|
|
|
$mimeType = (string) $manifestAttributes->{'media-type'}; |
|
66
|
|
|
|
|
67
|
|
|
break; |
|
68
|
|
|
} |
|
69
|
|
|
} |
|
70
|
|
|
} |
|
71
|
|
|
} |
|
72
|
|
|
|
|
73
|
3 |
|
$zip->close(); |
|
74
|
|
|
|
|
75
|
3 |
|
return $mimeType === 'application/vnd.oasis.opendocument.spreadsheet'; |
|
76
|
|
|
} |
|
77
|
|
|
|
|
78
|
|
|
return false; |
|
79
|
|
|
} |
|
80
|
|
|
|
|
81
|
|
|
/** |
|
82
|
|
|
* Reads names of the worksheets from a file, without parsing the whole file to a PhpSpreadsheet object. |
|
83
|
|
|
* |
|
84
|
|
|
* @param string $pFilename |
|
85
|
|
|
* |
|
86
|
|
|
* @throws Exception |
|
87
|
|
|
* |
|
88
|
|
|
* @return string[] |
|
89
|
|
|
*/ |
|
90
|
1 |
|
public function listWorksheetNames($pFilename) |
|
91
|
|
|
{ |
|
92
|
1 |
|
File::assertFile($pFilename); |
|
93
|
|
|
|
|
94
|
1 |
|
$zip = new ZipArchive(); |
|
95
|
1 |
|
if (!$zip->open($pFilename)) { |
|
96
|
|
|
throw new Exception('Could not open ' . $pFilename . ' for reading! Error opening file.'); |
|
97
|
|
|
} |
|
98
|
|
|
|
|
99
|
1 |
|
$worksheetNames = []; |
|
100
|
|
|
|
|
101
|
1 |
|
$xml = new XMLReader(); |
|
102
|
1 |
|
$xml->xml( |
|
103
|
1 |
|
$this->securityScanFile('zip://' . realpath($pFilename) . '#content.xml'), |
|
104
|
1 |
|
null, |
|
105
|
1 |
|
Settings::getLibXmlLoaderOptions() |
|
106
|
|
|
); |
|
107
|
1 |
|
$xml->setParserProperty(2, true); |
|
108
|
|
|
|
|
109
|
|
|
// Step into the first level of content of the XML |
|
110
|
1 |
|
$xml->read(); |
|
111
|
1 |
|
while ($xml->read()) { |
|
112
|
|
|
// Quickly jump through to the office:body node |
|
113
|
1 |
|
while ($xml->name !== 'office:body') { |
|
114
|
1 |
|
if ($xml->isEmptyElement) { |
|
115
|
1 |
|
$xml->read(); |
|
116
|
|
|
} else { |
|
117
|
1 |
|
$xml->next(); |
|
118
|
|
|
} |
|
119
|
|
|
} |
|
120
|
|
|
// Now read each node until we find our first table:table node |
|
121
|
1 |
|
while ($xml->read()) { |
|
122
|
1 |
|
if ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) { |
|
123
|
|
|
// Loop through each table:table node reading the table:name attribute for each worksheet name |
|
124
|
|
|
do { |
|
125
|
1 |
|
$worksheetNames[] = $xml->getAttribute('table:name'); |
|
126
|
1 |
|
$xml->next(); |
|
127
|
1 |
|
} while ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT); |
|
128
|
|
|
} |
|
129
|
|
|
} |
|
130
|
|
|
} |
|
131
|
|
|
|
|
132
|
1 |
|
return $worksheetNames; |
|
133
|
|
|
} |
|
134
|
|
|
|
|
135
|
|
|
/** |
|
136
|
|
|
* Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns). |
|
137
|
|
|
* |
|
138
|
|
|
* @param string $pFilename |
|
139
|
|
|
* |
|
140
|
|
|
* @throws Exception |
|
141
|
|
|
* |
|
142
|
|
|
* @return array |
|
143
|
|
|
*/ |
|
144
|
|
|
public function listWorksheetInfo($pFilename) |
|
145
|
|
|
{ |
|
146
|
|
|
File::assertFile($pFilename); |
|
147
|
|
|
|
|
148
|
|
|
$worksheetInfo = []; |
|
149
|
|
|
|
|
150
|
|
|
$zip = new ZipArchive(); |
|
151
|
|
|
if (!$zip->open($pFilename)) { |
|
152
|
|
|
throw new Exception('Could not open ' . $pFilename . ' for reading! Error opening file.'); |
|
153
|
|
|
} |
|
154
|
|
|
|
|
155
|
|
|
$xml = new XMLReader(); |
|
156
|
|
|
$xml->xml( |
|
157
|
|
|
$this->securityScanFile('zip://' . realpath($pFilename) . '#content.xml'), |
|
158
|
|
|
null, |
|
159
|
|
|
Settings::getLibXmlLoaderOptions() |
|
160
|
|
|
); |
|
161
|
|
|
$xml->setParserProperty(2, true); |
|
162
|
|
|
|
|
163
|
|
|
// Step into the first level of content of the XML |
|
164
|
|
|
$xml->read(); |
|
165
|
|
|
while ($xml->read()) { |
|
166
|
|
|
// Quickly jump through to the office:body node |
|
167
|
|
|
while ($xml->name !== 'office:body') { |
|
168
|
|
|
if ($xml->isEmptyElement) { |
|
169
|
|
|
$xml->read(); |
|
170
|
|
|
} else { |
|
171
|
|
|
$xml->next(); |
|
172
|
|
|
} |
|
173
|
|
|
} |
|
174
|
|
|
// Now read each node until we find our first table:table node |
|
175
|
|
|
while ($xml->read()) { |
|
176
|
|
|
if ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) { |
|
177
|
|
|
$worksheetNames[] = $xml->getAttribute('table:name'); |
|
178
|
|
|
|
|
179
|
|
|
$tmpInfo = [ |
|
180
|
|
|
'worksheetName' => $xml->getAttribute('table:name'), |
|
181
|
|
|
'lastColumnLetter' => 'A', |
|
182
|
|
|
'lastColumnIndex' => 0, |
|
183
|
|
|
'totalRows' => 0, |
|
184
|
|
|
'totalColumns' => 0, |
|
185
|
|
|
]; |
|
186
|
|
|
|
|
187
|
|
|
// Loop through each child node of the table:table element reading |
|
188
|
|
|
$currCells = 0; |
|
189
|
|
|
do { |
|
190
|
|
|
$xml->read(); |
|
191
|
|
|
if ($xml->name == 'table:table-row' && $xml->nodeType == XMLReader::ELEMENT) { |
|
192
|
|
|
$rowspan = $xml->getAttribute('table:number-rows-repeated'); |
|
193
|
|
|
$rowspan = empty($rowspan) ? 1 : $rowspan; |
|
194
|
|
|
$tmpInfo['totalRows'] += $rowspan; |
|
195
|
|
|
$tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells); |
|
196
|
|
|
$currCells = 0; |
|
197
|
|
|
// Step into the row |
|
198
|
|
|
$xml->read(); |
|
199
|
|
|
do { |
|
200
|
|
|
if ($xml->name == 'table:table-cell' && $xml->nodeType == XMLReader::ELEMENT) { |
|
201
|
|
|
if (!$xml->isEmptyElement) { |
|
202
|
|
|
++$currCells; |
|
203
|
|
|
$xml->next(); |
|
204
|
|
|
} else { |
|
205
|
|
|
$xml->read(); |
|
206
|
|
|
} |
|
207
|
|
|
} elseif ($xml->name == 'table:covered-table-cell' && $xml->nodeType == XMLReader::ELEMENT) { |
|
208
|
|
|
$mergeSize = $xml->getAttribute('table:number-columns-repeated'); |
|
209
|
|
|
$currCells += (int) $mergeSize; |
|
210
|
|
|
$xml->read(); |
|
211
|
|
|
} |
|
212
|
|
|
} while ($xml->name != 'table:table-row'); |
|
213
|
|
|
} |
|
214
|
|
|
} while ($xml->name != 'table:table'); |
|
215
|
|
|
|
|
216
|
|
|
$tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells); |
|
217
|
|
|
$tmpInfo['lastColumnIndex'] = $tmpInfo['totalColumns'] - 1; |
|
218
|
|
|
$tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1); |
|
219
|
|
|
$worksheetInfo[] = $tmpInfo; |
|
220
|
|
|
} |
|
221
|
|
|
} |
|
222
|
|
|
} |
|
223
|
|
|
|
|
224
|
|
|
return $worksheetInfo; |
|
225
|
|
|
} |
|
226
|
|
|
|
|
227
|
|
|
/** |
|
228
|
|
|
* Loads PhpSpreadsheet from file. |
|
229
|
|
|
* |
|
230
|
|
|
* @param string $pFilename |
|
231
|
|
|
* |
|
232
|
|
|
* @throws Exception |
|
233
|
|
|
* |
|
234
|
|
|
* @return Spreadsheet |
|
235
|
|
|
*/ |
|
236
|
6 |
|
public function load($pFilename) |
|
237
|
|
|
{ |
|
238
|
|
|
// Create new Spreadsheet |
|
239
|
6 |
|
$spreadsheet = new Spreadsheet(); |
|
240
|
|
|
|
|
241
|
|
|
// Load into this instance |
|
242
|
6 |
|
return $this->loadIntoExisting($pFilename, $spreadsheet); |
|
243
|
|
|
} |
|
244
|
|
|
|
|
245
|
|
|
/** |
|
246
|
|
|
* Loads PhpSpreadsheet from file into PhpSpreadsheet instance. |
|
247
|
|
|
* |
|
248
|
|
|
* @param string $pFilename |
|
249
|
|
|
* @param Spreadsheet $spreadsheet |
|
250
|
|
|
* |
|
251
|
|
|
* @throws Exception |
|
252
|
|
|
* |
|
253
|
|
|
* @return Spreadsheet |
|
254
|
|
|
*/ |
|
255
|
12 |
|
public function loadIntoExisting($pFilename, Spreadsheet $spreadsheet) |
|
256
|
|
|
{ |
|
257
|
12 |
|
File::assertFile($pFilename); |
|
258
|
|
|
|
|
259
|
12 |
|
$timezoneObj = new DateTimeZone('Europe/London'); |
|
260
|
12 |
|
$GMT = new \DateTimeZone('UTC'); |
|
261
|
|
|
|
|
262
|
12 |
|
$zip = new ZipArchive(); |
|
263
|
12 |
|
if (!$zip->open($pFilename)) { |
|
264
|
|
|
throw new Exception('Could not open ' . $pFilename . ' for reading! Error opening file.'); |
|
265
|
|
|
} |
|
266
|
|
|
|
|
267
|
|
|
// Meta |
|
268
|
|
|
|
|
269
|
12 |
|
$xml = simplexml_load_string( |
|
270
|
12 |
|
$this->securityScan($zip->getFromName('meta.xml')), |
|
271
|
12 |
|
'SimpleXMLElement', |
|
272
|
12 |
|
Settings::getLibXmlLoaderOptions() |
|
273
|
|
|
); |
|
274
|
12 |
|
$namespacesMeta = $xml->getNamespaces(true); |
|
275
|
|
|
|
|
276
|
12 |
|
$docProps = $spreadsheet->getProperties(); |
|
277
|
12 |
|
$officeProperty = $xml->children($namespacesMeta['office']); |
|
278
|
12 |
|
foreach ($officeProperty as $officePropertyData) { |
|
279
|
12 |
|
$officePropertyDC = []; |
|
280
|
12 |
|
if (isset($namespacesMeta['dc'])) { |
|
281
|
12 |
|
$officePropertyDC = $officePropertyData->children($namespacesMeta['dc']); |
|
282
|
|
|
} |
|
283
|
12 |
|
foreach ($officePropertyDC as $propertyName => $propertyValue) { |
|
284
|
12 |
|
$propertyValue = (string) $propertyValue; |
|
285
|
|
|
switch ($propertyName) { |
|
286
|
12 |
|
case 'title': |
|
287
|
10 |
|
$docProps->setTitle($propertyValue); |
|
288
|
|
|
|
|
289
|
10 |
|
break; |
|
290
|
12 |
|
case 'subject': |
|
291
|
10 |
|
$docProps->setSubject($propertyValue); |
|
292
|
|
|
|
|
293
|
10 |
|
break; |
|
294
|
12 |
|
case 'creator': |
|
295
|
5 |
|
$docProps->setCreator($propertyValue); |
|
296
|
5 |
|
$docProps->setLastModifiedBy($propertyValue); |
|
297
|
|
|
|
|
298
|
5 |
|
break; |
|
299
|
12 |
|
case 'date': |
|
300
|
12 |
|
$creationDate = strtotime($propertyValue); |
|
301
|
12 |
|
$docProps->setCreated($creationDate); |
|
302
|
12 |
|
$docProps->setModified($creationDate); |
|
303
|
|
|
|
|
304
|
12 |
|
break; |
|
305
|
10 |
|
case 'description': |
|
306
|
10 |
|
$docProps->setDescription($propertyValue); |
|
307
|
|
|
|
|
308
|
12 |
|
break; |
|
309
|
|
|
} |
|
310
|
|
|
} |
|
311
|
12 |
|
$officePropertyMeta = []; |
|
312
|
12 |
|
if (isset($namespacesMeta['dc'])) { |
|
313
|
12 |
|
$officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']); |
|
314
|
|
|
} |
|
315
|
12 |
|
foreach ($officePropertyMeta as $propertyName => $propertyValue) { |
|
316
|
12 |
|
$propertyValueAttributes = $propertyValue->attributes($namespacesMeta['meta']); |
|
317
|
12 |
|
$propertyValue = (string) $propertyValue; |
|
318
|
|
|
switch ($propertyName) { |
|
319
|
12 |
|
case 'initial-creator': |
|
320
|
10 |
|
$docProps->setCreator($propertyValue); |
|
321
|
|
|
|
|
322
|
10 |
|
break; |
|
323
|
12 |
|
case 'keyword': |
|
324
|
10 |
|
$docProps->setKeywords($propertyValue); |
|
325
|
|
|
|
|
326
|
10 |
|
break; |
|
327
|
12 |
|
case 'creation-date': |
|
328
|
12 |
|
$creationDate = strtotime($propertyValue); |
|
329
|
12 |
|
$docProps->setCreated($creationDate); |
|
330
|
|
|
|
|
331
|
12 |
|
break; |
|
332
|
12 |
|
case 'user-defined': |
|
333
|
10 |
|
$propertyValueType = Properties::PROPERTY_TYPE_STRING; |
|
334
|
10 |
|
foreach ($propertyValueAttributes as $key => $value) { |
|
335
|
10 |
|
if ($key == 'name') { |
|
336
|
10 |
|
$propertyValueName = (string) $value; |
|
337
|
|
|
} elseif ($key == 'value-type') { |
|
338
|
|
|
switch ($value) { |
|
339
|
|
|
case 'date': |
|
340
|
|
|
$propertyValue = Properties::convertProperty($propertyValue, 'date'); |
|
341
|
|
|
$propertyValueType = Properties::PROPERTY_TYPE_DATE; |
|
342
|
|
|
|
|
343
|
|
|
break; |
|
344
|
|
|
case 'boolean': |
|
345
|
|
|
$propertyValue = Properties::convertProperty($propertyValue, 'bool'); |
|
346
|
|
|
$propertyValueType = Properties::PROPERTY_TYPE_BOOLEAN; |
|
347
|
|
|
|
|
348
|
|
|
break; |
|
349
|
|
|
case 'float': |
|
350
|
|
|
$propertyValue = Properties::convertProperty($propertyValue, 'r4'); |
|
351
|
|
|
$propertyValueType = Properties::PROPERTY_TYPE_FLOAT; |
|
352
|
|
|
|
|
353
|
|
|
break; |
|
354
|
|
|
default: |
|
355
|
10 |
|
$propertyValueType = Properties::PROPERTY_TYPE_STRING; |
|
356
|
|
|
} |
|
357
|
|
|
} |
|
358
|
|
|
} |
|
359
|
10 |
|
$docProps->setCustomProperty($propertyValueName, $propertyValue, $propertyValueType); |
|
|
|
|
|
|
360
|
|
|
|
|
361
|
12 |
|
break; |
|
362
|
|
|
} |
|
363
|
|
|
} |
|
364
|
|
|
} |
|
365
|
|
|
|
|
366
|
|
|
// Content |
|
367
|
|
|
|
|
368
|
12 |
|
$dom = new \DOMDocument('1.01', 'UTF-8'); |
|
369
|
12 |
|
$dom->loadXML( |
|
370
|
12 |
|
$this->securityScan($zip->getFromName('content.xml')), |
|
371
|
12 |
|
Settings::getLibXmlLoaderOptions() |
|
372
|
|
|
); |
|
373
|
|
|
|
|
374
|
12 |
|
$officeNs = $dom->lookupNamespaceUri('office'); |
|
375
|
12 |
|
$tableNs = $dom->lookupNamespaceUri('table'); |
|
376
|
12 |
|
$textNs = $dom->lookupNamespaceUri('text'); |
|
377
|
12 |
|
$xlinkNs = $dom->lookupNamespaceUri('xlink'); |
|
378
|
|
|
|
|
379
|
12 |
|
$spreadsheets = $dom->getElementsByTagNameNS($officeNs, 'body') |
|
380
|
12 |
|
->item(0) |
|
381
|
12 |
|
->getElementsByTagNameNS($officeNs, 'spreadsheet'); |
|
382
|
|
|
|
|
383
|
12 |
|
foreach ($spreadsheets as $workbookData) { |
|
384
|
|
|
/** @var \DOMElement $workbookData */ |
|
385
|
12 |
|
$tables = $workbookData->getElementsByTagNameNS($tableNs, 'table'); |
|
386
|
|
|
|
|
387
|
12 |
|
$worksheetID = 0; |
|
388
|
12 |
|
foreach ($tables as $worksheetDataSet) { |
|
389
|
|
|
/** @var \DOMElement $worksheetDataSet */ |
|
390
|
12 |
|
$worksheetName = $worksheetDataSet->getAttributeNS($tableNs, 'name'); |
|
391
|
|
|
|
|
392
|
|
|
// Check loadSheetsOnly |
|
393
|
12 |
|
if (isset($this->loadSheetsOnly) |
|
394
|
12 |
|
&& $worksheetName |
|
395
|
12 |
|
&& !in_array($worksheetName, $this->loadSheetsOnly)) { |
|
396
|
|
|
continue; |
|
397
|
|
|
} |
|
398
|
|
|
|
|
399
|
|
|
// Create sheet |
|
400
|
12 |
|
if ($worksheetID > 0) { |
|
401
|
7 |
|
$spreadsheet->createSheet(); // First sheet is added by default |
|
402
|
|
|
} |
|
403
|
12 |
|
$spreadsheet->setActiveSheetIndex($worksheetID); |
|
404
|
|
|
|
|
405
|
12 |
|
if ($worksheetName) { |
|
406
|
|
|
// Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in |
|
407
|
|
|
// formula cells... during the load, all formulae should be correct, and we're simply |
|
408
|
|
|
// bringing the worksheet name in line with the formula, not the reverse |
|
409
|
12 |
|
$spreadsheet->getActiveSheet()->setTitle($worksheetName, false, false); |
|
410
|
|
|
} |
|
411
|
|
|
|
|
412
|
|
|
// Go through every child of table element |
|
413
|
12 |
|
$rowID = 1; |
|
414
|
12 |
|
foreach ($worksheetDataSet->childNodes as $childNode) { |
|
415
|
|
|
/** @var \DOMElement $childNode */ |
|
416
|
|
|
|
|
417
|
|
|
// Filter elements which are not under the "table" ns |
|
418
|
12 |
|
if ($childNode->namespaceURI != $tableNs) { |
|
419
|
10 |
|
continue; |
|
420
|
|
|
} |
|
421
|
|
|
|
|
422
|
12 |
|
$key = $childNode->nodeName; |
|
423
|
|
|
|
|
424
|
|
|
// Remove ns from node name |
|
425
|
12 |
|
if (strpos($key, ':') !== false) { |
|
426
|
12 |
|
$keyChunks = explode(':', $key); |
|
427
|
12 |
|
$key = array_pop($keyChunks); |
|
428
|
|
|
} |
|
429
|
|
|
|
|
430
|
|
|
switch ($key) { |
|
431
|
12 |
|
case 'table-header-rows': |
|
432
|
|
|
/// TODO :: Figure this out. This is only a partial implementation I guess. |
|
433
|
|
|
// ($rowData it's not used at all and I'm not sure that PHPExcel |
|
434
|
|
|
// has an API for this) |
|
435
|
|
|
|
|
436
|
|
|
// foreach ($rowData as $keyRowData => $cellData) { |
|
|
|
|
|
|
437
|
|
|
// $rowData = $cellData; |
|
438
|
|
|
// break; |
|
439
|
|
|
// } |
|
440
|
|
|
break; |
|
441
|
12 |
|
case 'table-row': |
|
442
|
12 |
|
if ($childNode->hasAttributeNS($tableNs, 'number-rows-repeated')) { |
|
443
|
7 |
|
$rowRepeats = $childNode->getAttributeNS($tableNs, 'number-rows-repeated'); |
|
444
|
|
|
} else { |
|
445
|
12 |
|
$rowRepeats = 1; |
|
446
|
|
|
} |
|
447
|
|
|
|
|
448
|
12 |
|
$columnID = 'A'; |
|
449
|
12 |
|
foreach ($childNode->childNodes as $key => $cellData) { |
|
450
|
|
|
// @var \DOMElement $cellData |
|
451
|
|
|
|
|
452
|
12 |
|
if ($this->getReadFilter() !== null) { |
|
453
|
12 |
|
if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { |
|
454
|
1 |
|
++$columnID; |
|
455
|
|
|
|
|
456
|
1 |
|
continue; |
|
457
|
|
|
} |
|
458
|
|
|
} |
|
459
|
|
|
|
|
460
|
|
|
// Initialize variables |
|
461
|
12 |
|
$formatting = $hyperlink = null; |
|
462
|
12 |
|
$hasCalculatedValue = false; |
|
463
|
12 |
|
$cellDataFormula = ''; |
|
464
|
|
|
|
|
465
|
12 |
|
if ($cellData->hasAttributeNS($tableNs, 'formula')) { |
|
466
|
5 |
|
$cellDataFormula = $cellData->getAttributeNS($tableNs, 'formula'); |
|
467
|
5 |
|
$hasCalculatedValue = true; |
|
468
|
|
|
} |
|
469
|
|
|
|
|
470
|
|
|
// Annotations |
|
471
|
12 |
|
$annotation = $cellData->getElementsByTagNameNS($officeNs, 'annotation'); |
|
472
|
|
|
|
|
473
|
12 |
|
if ($annotation->length > 0) { |
|
474
|
6 |
|
$textNode = $annotation->item(0)->getElementsByTagNameNS($textNs, 'p'); |
|
475
|
|
|
|
|
476
|
6 |
|
if ($textNode->length > 0) { |
|
477
|
6 |
|
$text = $this->scanElementForText($textNode->item(0)); |
|
478
|
|
|
|
|
479
|
6 |
|
$spreadsheet->getActiveSheet() |
|
480
|
6 |
|
->getComment($columnID . $rowID) |
|
481
|
6 |
|
->setText($this->parseRichText($text)); |
|
482
|
|
|
// ->setAuthor( $author ) |
|
|
|
|
|
|
483
|
|
|
} |
|
484
|
|
|
} |
|
485
|
|
|
|
|
486
|
|
|
// Content |
|
487
|
|
|
|
|
488
|
|
|
/** @var \DOMElement[] $paragraphs */ |
|
489
|
12 |
|
$paragraphs = []; |
|
490
|
|
|
|
|
491
|
12 |
|
foreach ($cellData->childNodes as $item) { |
|
492
|
|
|
/** @var \DOMElement $item */ |
|
493
|
|
|
|
|
494
|
|
|
// Filter text:p elements |
|
495
|
12 |
|
if ($item->nodeName == 'text:p') { |
|
496
|
12 |
|
$paragraphs[] = $item; |
|
497
|
|
|
} |
|
498
|
|
|
} |
|
499
|
|
|
|
|
500
|
12 |
|
if (count($paragraphs) > 0) { |
|
501
|
|
|
// Consolidate if there are multiple p records (maybe with spans as well) |
|
502
|
12 |
|
$dataArray = []; |
|
503
|
|
|
|
|
504
|
|
|
// Text can have multiple text:p and within those, multiple text:span. |
|
505
|
|
|
// text:p newlines, but text:span does not. |
|
506
|
|
|
// Also, here we assume there is no text data is span fields are specified, since |
|
507
|
|
|
// we have no way of knowing proper positioning anyway. |
|
508
|
|
|
|
|
509
|
12 |
|
foreach ($paragraphs as $pData) { |
|
510
|
12 |
|
$dataArray[] = $this->scanElementForText($pData); |
|
511
|
|
|
} |
|
512
|
12 |
|
$allCellDataText = implode($dataArray, "\n"); |
|
|
|
|
|
|
513
|
|
|
|
|
514
|
12 |
|
$type = $cellData->getAttributeNS($officeNs, 'value-type'); |
|
515
|
|
|
|
|
516
|
|
|
switch ($type) { |
|
517
|
12 |
|
case 'string': |
|
518
|
11 |
|
$type = DataType::TYPE_STRING; |
|
519
|
11 |
|
$dataValue = $allCellDataText; |
|
520
|
|
|
|
|
521
|
11 |
|
foreach ($paragraphs as $paragraph) { |
|
522
|
11 |
|
$link = $paragraph->getElementsByTagNameNS($textNs, 'a'); |
|
523
|
11 |
|
if ($link->length > 0) { |
|
524
|
11 |
|
$hyperlink = $link->item(0)->getAttributeNS($xlinkNs, 'href'); |
|
525
|
|
|
} |
|
526
|
|
|
} |
|
527
|
|
|
|
|
528
|
11 |
|
break; |
|
529
|
9 |
|
case 'boolean': |
|
530
|
5 |
|
$type = DataType::TYPE_BOOL; |
|
531
|
5 |
|
$dataValue = ($allCellDataText == 'TRUE') ? true : false; |
|
532
|
|
|
|
|
533
|
5 |
|
break; |
|
534
|
9 |
|
case 'percentage': |
|
535
|
3 |
|
$type = DataType::TYPE_NUMERIC; |
|
536
|
3 |
|
$dataValue = (float) $cellData->getAttributeNS($officeNs, 'value'); |
|
537
|
|
|
|
|
538
|
3 |
|
if (floor($dataValue) == $dataValue) { |
|
539
|
|
|
$dataValue = (int) $dataValue; |
|
540
|
|
|
} |
|
541
|
3 |
|
$formatting = NumberFormat::FORMAT_PERCENTAGE_00; |
|
542
|
|
|
|
|
543
|
3 |
|
break; |
|
544
|
9 |
|
case 'currency': |
|
545
|
3 |
|
$type = DataType::TYPE_NUMERIC; |
|
546
|
3 |
|
$dataValue = (float) $cellData->getAttributeNS($officeNs, 'value'); |
|
547
|
|
|
|
|
548
|
3 |
|
if (floor($dataValue) == $dataValue) { |
|
549
|
3 |
|
$dataValue = (int) $dataValue; |
|
550
|
|
|
} |
|
551
|
3 |
|
$formatting = NumberFormat::FORMAT_CURRENCY_USD_SIMPLE; |
|
552
|
|
|
|
|
553
|
3 |
|
break; |
|
554
|
7 |
|
case 'float': |
|
555
|
7 |
|
$type = DataType::TYPE_NUMERIC; |
|
556
|
7 |
|
$dataValue = (float) $cellData->getAttributeNS($officeNs, 'value'); |
|
557
|
|
|
|
|
558
|
7 |
|
if (floor($dataValue) == $dataValue) { |
|
559
|
7 |
|
if ($dataValue == (int) $dataValue) { |
|
560
|
7 |
|
$dataValue = (int) $dataValue; |
|
561
|
|
|
} else { |
|
562
|
|
|
$dataValue = (float) $dataValue; |
|
563
|
|
|
} |
|
564
|
|
|
} |
|
565
|
|
|
|
|
566
|
7 |
|
break; |
|
567
|
5 |
|
case 'date': |
|
568
|
5 |
|
$type = DataType::TYPE_NUMERIC; |
|
569
|
5 |
|
$value = $cellData->getAttributeNS($officeNs, 'date-value'); |
|
570
|
|
|
|
|
571
|
5 |
|
$dateObj = new DateTime($value, $GMT); |
|
572
|
5 |
|
$dateObj->setTimeZone($timezoneObj); |
|
573
|
5 |
|
list($year, $month, $day, $hour, $minute, $second) = explode( |
|
574
|
5 |
|
' ', |
|
575
|
5 |
|
$dateObj->format('Y m d H i s') |
|
576
|
|
|
); |
|
577
|
|
|
|
|
578
|
5 |
|
$dataValue = Date::formattedPHPToExcel( |
|
579
|
5 |
|
$year, |
|
|
|
|
|
|
580
|
5 |
|
$month, |
|
|
|
|
|
|
581
|
5 |
|
$day, |
|
|
|
|
|
|
582
|
5 |
|
$hour, |
|
|
|
|
|
|
583
|
5 |
|
$minute, |
|
|
|
|
|
|
584
|
5 |
|
$second |
|
|
|
|
|
|
585
|
|
|
); |
|
586
|
|
|
|
|
587
|
5 |
|
if ($dataValue != floor($dataValue)) { |
|
588
|
1 |
|
$formatting = NumberFormat::FORMAT_DATE_XLSX15 |
|
589
|
1 |
|
. ' ' |
|
590
|
5 |
|
. NumberFormat::FORMAT_DATE_TIME4; |
|
591
|
|
|
} else { |
|
592
|
5 |
|
$formatting = NumberFormat::FORMAT_DATE_XLSX15; |
|
593
|
|
|
} |
|
594
|
|
|
|
|
595
|
5 |
|
break; |
|
596
|
5 |
|
case 'time': |
|
597
|
5 |
|
$type = DataType::TYPE_NUMERIC; |
|
598
|
|
|
|
|
599
|
5 |
|
$timeValue = $cellData->getAttributeNS($officeNs, 'time-value'); |
|
600
|
|
|
|
|
601
|
5 |
|
$dataValue = Date::PHPToExcel( |
|
602
|
5 |
|
strtotime( |
|
603
|
5 |
|
'01-01-1970 ' . implode(':', sscanf($timeValue, 'PT%dH%dM%dS')) |
|
604
|
|
|
) |
|
605
|
|
|
); |
|
606
|
5 |
|
$formatting = NumberFormat::FORMAT_DATE_TIME4; |
|
607
|
|
|
|
|
608
|
5 |
|
break; |
|
609
|
|
|
default: |
|
610
|
12 |
|
$dataValue = null; |
|
611
|
|
|
} |
|
612
|
|
|
} else { |
|
613
|
11 |
|
$type = DataType::TYPE_NULL; |
|
614
|
11 |
|
$dataValue = null; |
|
615
|
|
|
} |
|
616
|
|
|
|
|
617
|
12 |
|
if ($hasCalculatedValue) { |
|
618
|
5 |
|
$type = DataType::TYPE_FORMULA; |
|
619
|
5 |
|
$cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1); |
|
620
|
5 |
|
$temp = explode('"', $cellDataFormula); |
|
621
|
5 |
|
$tKey = false; |
|
622
|
5 |
|
foreach ($temp as &$value) { |
|
623
|
|
|
// Only replace in alternate array entries (i.e. non-quoted blocks) |
|
624
|
5 |
|
if ($tKey = !$tKey) { |
|
|
|
|
|
|
625
|
|
|
// Cell range reference in another sheet |
|
626
|
5 |
|
$value = preg_replace('/\[([^\.]+)\.([^\.]+):\.([^\.]+)\]/U', '$1!$2:$3', $value); |
|
627
|
|
|
|
|
628
|
|
|
// Cell reference in another sheet |
|
629
|
5 |
|
$value = preg_replace('/\[([^\.]+)\.([^\.]+)\]/U', '$1!$2', $value); |
|
630
|
|
|
|
|
631
|
|
|
// Cell range reference |
|
632
|
5 |
|
$value = preg_replace('/\[\.([^\.]+):\.([^\.]+)\]/U', '$1:$2', $value); |
|
633
|
|
|
|
|
634
|
|
|
// Simple cell reference |
|
635
|
5 |
|
$value = preg_replace('/\[\.([^\.]+)\]/U', '$1', $value); |
|
636
|
|
|
|
|
637
|
5 |
|
$value = Calculation::translateSeparator(';', ',', $value, $inBraces); |
|
638
|
|
|
} |
|
639
|
|
|
} |
|
640
|
5 |
|
unset($value); |
|
641
|
|
|
|
|
642
|
|
|
// Then rebuild the formula string |
|
643
|
5 |
|
$cellDataFormula = implode('"', $temp); |
|
644
|
|
|
} |
|
645
|
|
|
|
|
646
|
12 |
|
if ($cellData->hasAttributeNS($tableNs, 'number-columns-repeated')) { |
|
647
|
11 |
|
$colRepeats = (int) $cellData->getAttributeNS($tableNs, 'number-columns-repeated'); |
|
648
|
|
|
} else { |
|
649
|
12 |
|
$colRepeats = 1; |
|
650
|
|
|
} |
|
651
|
|
|
|
|
652
|
12 |
|
if ($type !== null) { |
|
653
|
12 |
|
for ($i = 0; $i < $colRepeats; ++$i) { |
|
654
|
12 |
|
if ($i > 0) { |
|
655
|
11 |
|
++$columnID; |
|
656
|
|
|
} |
|
657
|
|
|
|
|
658
|
12 |
|
if ($type !== DataType::TYPE_NULL) { |
|
659
|
12 |
|
for ($rowAdjust = 0; $rowAdjust < $rowRepeats; ++$rowAdjust) { |
|
660
|
12 |
|
$rID = $rowID + $rowAdjust; |
|
661
|
|
|
|
|
662
|
12 |
|
$cell = $spreadsheet->getActiveSheet() |
|
663
|
12 |
|
->getCell($columnID . $rID); |
|
664
|
|
|
|
|
665
|
|
|
// Set value |
|
666
|
12 |
|
if ($hasCalculatedValue) { |
|
667
|
5 |
|
$cell->setValueExplicit($cellDataFormula, $type); |
|
668
|
|
|
} else { |
|
669
|
12 |
|
$cell->setValueExplicit($dataValue, $type); |
|
670
|
|
|
} |
|
671
|
|
|
|
|
672
|
12 |
|
if ($hasCalculatedValue) { |
|
673
|
5 |
|
$cell->setCalculatedValue($dataValue); |
|
674
|
|
|
} |
|
675
|
|
|
|
|
676
|
|
|
// Set other properties |
|
677
|
12 |
|
if ($formatting !== null) { |
|
678
|
7 |
|
$spreadsheet->getActiveSheet() |
|
679
|
7 |
|
->getStyle($columnID . $rID) |
|
680
|
7 |
|
->getNumberFormat() |
|
681
|
7 |
|
->setFormatCode($formatting); |
|
682
|
|
|
} else { |
|
683
|
12 |
|
$spreadsheet->getActiveSheet() |
|
684
|
12 |
|
->getStyle($columnID . $rID) |
|
685
|
12 |
|
->getNumberFormat() |
|
686
|
12 |
|
->setFormatCode(NumberFormat::FORMAT_GENERAL); |
|
687
|
|
|
} |
|
688
|
|
|
|
|
689
|
12 |
|
if ($hyperlink !== null) { |
|
690
|
5 |
|
$cell->getHyperlink() |
|
691
|
5 |
|
->setUrl($hyperlink); |
|
692
|
|
|
} |
|
693
|
|
|
} |
|
694
|
|
|
} |
|
695
|
|
|
} |
|
696
|
|
|
} |
|
697
|
|
|
|
|
698
|
|
|
// Merged cells |
|
699
|
12 |
|
if ($cellData->hasAttributeNS($tableNs, 'number-columns-spanned') |
|
700
|
12 |
|
|| $cellData->hasAttributeNS($tableNs, 'number-rows-spanned') |
|
701
|
|
|
) { |
|
702
|
6 |
|
if (($type !== DataType::TYPE_NULL) || (!$this->readDataOnly)) { |
|
703
|
6 |
|
$columnTo = $columnID; |
|
704
|
|
|
|
|
705
|
6 |
|
if ($cellData->hasAttributeNS($tableNs, 'number-columns-spanned')) { |
|
706
|
6 |
|
$columnIndex = Coordinate::columnIndexFromString($columnID); |
|
707
|
6 |
|
$columnIndex += (int) $cellData->getAttributeNS($tableNs, 'number-columns-spanned'); |
|
708
|
6 |
|
$columnIndex -= 2; |
|
709
|
|
|
|
|
710
|
6 |
|
$columnTo = Coordinate::stringFromColumnIndex($columnIndex + 1); |
|
711
|
|
|
} |
|
712
|
|
|
|
|
713
|
6 |
|
$rowTo = $rowID; |
|
714
|
|
|
|
|
715
|
6 |
|
if ($cellData->hasAttributeNS($tableNs, 'number-rows-spanned')) { |
|
716
|
6 |
|
$rowTo = $rowTo + (int) $cellData->getAttributeNS($tableNs, 'number-rows-spanned') - 1; |
|
717
|
|
|
} |
|
718
|
|
|
|
|
719
|
6 |
|
$cellRange = $columnID . $rowID . ':' . $columnTo . $rowTo; |
|
720
|
6 |
|
$spreadsheet->getActiveSheet()->mergeCells($cellRange); |
|
721
|
|
|
} |
|
722
|
|
|
} |
|
723
|
|
|
|
|
724
|
12 |
|
++$columnID; |
|
725
|
|
|
} |
|
726
|
12 |
|
$rowID += $rowRepeats; |
|
727
|
|
|
|
|
728
|
12 |
|
break; |
|
729
|
|
|
} |
|
730
|
|
|
} |
|
731
|
12 |
|
++$worksheetID; |
|
732
|
|
|
} |
|
733
|
|
|
} |
|
734
|
|
|
|
|
735
|
|
|
// Return |
|
736
|
12 |
|
return $spreadsheet; |
|
737
|
|
|
} |
|
738
|
|
|
|
|
739
|
|
|
/** |
|
740
|
|
|
* Recursively scan element. |
|
741
|
|
|
* |
|
742
|
|
|
* @param \DOMNode $element |
|
743
|
|
|
* |
|
744
|
|
|
* @return string |
|
745
|
|
|
*/ |
|
746
|
12 |
|
protected function scanElementForText(\DOMNode $element) |
|
747
|
|
|
{ |
|
748
|
12 |
|
$str = ''; |
|
749
|
12 |
|
foreach ($element->childNodes as $child) { |
|
750
|
|
|
/** @var \DOMNode $child */ |
|
751
|
12 |
|
if ($child->nodeType == XML_TEXT_NODE) { |
|
752
|
12 |
|
$str .= $child->nodeValue; |
|
753
|
7 |
|
} elseif ($child->nodeType == XML_ELEMENT_NODE && $child->nodeName == 'text:s') { |
|
754
|
|
|
// It's a space |
|
755
|
|
|
|
|
756
|
|
|
// Multiple spaces? |
|
757
|
|
|
/** @var \DOMAttr $cAttr */ |
|
758
|
3 |
|
$cAttr = $child->attributes->getNamedItem('c'); |
|
759
|
3 |
|
if ($cAttr) { |
|
760
|
3 |
|
$multiplier = (int) $cAttr->nodeValue; |
|
761
|
|
|
} else { |
|
762
|
3 |
|
$multiplier = 1; |
|
763
|
|
|
} |
|
764
|
|
|
|
|
765
|
3 |
|
$str .= str_repeat(' ', $multiplier); |
|
766
|
|
|
} |
|
767
|
|
|
|
|
768
|
12 |
|
if ($child->hasChildNodes()) { |
|
769
|
12 |
|
$str .= $this->scanElementForText($child); |
|
770
|
|
|
} |
|
771
|
|
|
} |
|
772
|
|
|
|
|
773
|
12 |
|
return $str; |
|
774
|
|
|
} |
|
775
|
|
|
|
|
776
|
|
|
/** |
|
777
|
|
|
* @param string $is |
|
778
|
|
|
* |
|
779
|
|
|
* @return RichText |
|
780
|
|
|
*/ |
|
781
|
6 |
|
private function parseRichText($is) |
|
782
|
|
|
{ |
|
783
|
6 |
|
$value = new RichText(); |
|
784
|
6 |
|
$value->createText($is); |
|
785
|
|
|
|
|
786
|
6 |
|
return $value; |
|
787
|
|
|
} |
|
788
|
|
|
} |
|
789
|
|
|
|
PHPOffice /
PhpSpreadsheet
Loading history...
This check marks implicit conversions of arrays to boolean values in a comparison. While in PHP an empty array is considered to be equal (but not identical) to false, this is not always apparent.
Consider making the comparison explicit by using
empty(..)or! empty(...)instead.