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