<?php
namespace PhpOffice\PhpSpreadsheet\Reader;
< use DateTime;
< use DateTimeZone;
< use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
> use DOMAttr;
> use DOMDocument;
> use DOMElement;
> use DOMNode;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
> use PhpOffice\PhpSpreadsheet\Helper\Dimension as HelperDimension;
use PhpOffice\PhpSpreadsheet\Reader\Ods\Properties as DocumentProperties;
> use PhpOffice\PhpSpreadsheet\Reader\Ods\AutoFilter;
use PhpOffice\PhpSpreadsheet\Reader\Security\XmlScanner;
> use PhpOffice\PhpSpreadsheet\Reader\Ods\DefinedNames;
use PhpOffice\PhpSpreadsheet\RichText\RichText;
> use PhpOffice\PhpSpreadsheet\Reader\Ods\FormulaTranslator;
use PhpOffice\PhpSpreadsheet\Settings;
> use PhpOffice\PhpSpreadsheet\Reader\Ods\PageSettings;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Shared\File;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
> use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use XMLReader;
> use Throwable;
use ZipArchive;
class Ods extends BaseReader
{
> const INITIAL_FILE = 'content.xml';
/**
>
* Create a new Ods Reader instance.
*/
public function __construct()
{
parent::__construct();
$this->securityScanner = XmlScanner::getInstance($this);
}
/**
* Can the current IReader read the file?
< *
< * @param string $pFilename
< *
< * @throws Exception
< *
< * @return bool
*/
< public function canRead($pFilename)
> public function canRead(string $filename): bool
{
< File::assertFile($pFilename);
<
$mimeType = 'UNKNOWN';
// Load file
> if (File::testFileNoThrow($filename, '')) {
$zip = new ZipArchive();
< if ($zip->open($pFilename) === true) {
> if ($zip->open($filename) === true) {
// check if it is an OOXML archive
$stat = $zip->statName('mimetype');
< if ($stat && ($stat['size'] <= 255)) {
> if (!empty($stat) && ($stat['size'] <= 255)) {
$mimeType = $zip->getFromName($stat['name']);
} elseif ($zip->statName('META-INF/manifest.xml')) {
$xml = simplexml_load_string(
< $this->securityScanner->scan($zip->getFromName('META-INF/manifest.xml')),
> $this->getSecurityScannerOrThrow()->scan($zip->getFromName('META-INF/manifest.xml')),
'SimpleXMLElement',
Settings::getLibXmlLoaderOptions()
);
> if ($xml !== false) {
$namespacesContent = $xml->getNamespaces(true);
if (isset($namespacesContent['manifest'])) {
$manifest = $xml->children($namespacesContent['manifest']);
foreach ($manifest as $manifestDataSet) {
> /** @scrutinizer ignore-call */
$manifestAttributes = $manifestDataSet->attributes($namespacesContent['manifest']);
< if ($manifestAttributes->{'full-path'} == '/') {
> if ($manifestAttributes && $manifestAttributes->{'full-path'} == '/') {
$mimeType = (string) $manifestAttributes->{'media-type'};
break;
}
}
}
}
> }
$zip->close();
<
< return $mimeType === 'application/vnd.oasis.opendocument.spreadsheet';
> }
}
< return false;
> return $mimeType === 'application/vnd.oasis.opendocument.spreadsheet';
}
/**
* Reads names of the worksheets from a file, without parsing the whole file to a PhpSpreadsheet object.
*
< * @param string $pFilename
< *
< * @throws Exception
> * @param string $filename
*
* @return string[]
*/
< public function listWorksheetNames($pFilename)
> public function listWorksheetNames($filename)
{
< File::assertFile($pFilename);
<
< $zip = new ZipArchive();
< if (!$zip->open($pFilename)) {
< throw new Exception('Could not open ' . $pFilename . ' for reading! Error opening file.');
< }
> File::assertFile($filename, self::INITIAL_FILE);
$worksheetNames = [];
$xml = new XMLReader();
$xml->xml(
< $this->securityScanner->scanFile('zip://' . realpath($pFilename) . '#content.xml'),
> $this->getSecurityScannerOrThrow()->scanFile('zip://' . realpath($filename) . '#' . self::INITIAL_FILE),
null,
Settings::getLibXmlLoaderOptions()
);
$xml->setParserProperty(2, true);
// Step into the first level of content of the XML
$xml->read();
while ($xml->read()) {
// Quickly jump through to the office:body node
< while ($xml->name !== 'office:body') {
> while (self::getXmlName($xml) !== 'office:body') {
if ($xml->isEmptyElement) {
$xml->read();
} else {
$xml->next();
}
}
// Now read each node until we find our first table:table node
while ($xml->read()) {
< if ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) {
> $xmlName = self::getXmlName($xml);
> if ($xmlName == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) {
// Loop through each table:table node reading the table:name attribute for each worksheet name
do {
< $worksheetNames[] = $xml->getAttribute('table:name');
> $worksheetName = $xml->getAttribute('table:name');
> if (!empty($worksheetName)) {
> $worksheetNames[] = $worksheetName;
> }
$xml->next();
< } while ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT);
> } while (self::getXmlName($xml) == 'table:table' && $xml->nodeType == XMLReader::ELEMENT);
}
}
}
return $worksheetNames;
}
/**
* Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
*
< * @param string $pFilename
< *
< * @throws Exception
> * @param string $filename
*
* @return array
*/
< public function listWorksheetInfo($pFilename)
> public function listWorksheetInfo($filename)
{
< File::assertFile($pFilename);
> File::assertFile($filename, self::INITIAL_FILE);
$worksheetInfo = [];
< $zip = new ZipArchive();
< if (!$zip->open($pFilename)) {
< throw new Exception('Could not open ' . $pFilename . ' for reading! Error opening file.');
< }
<
$xml = new XMLReader();
$xml->xml(
< $this->securityScanner->scanFile('zip://' . realpath($pFilename) . '#content.xml'),
> $this->getSecurityScannerOrThrow()->scanFile('zip://' . realpath($filename) . '#' . self::INITIAL_FILE),
null,
Settings::getLibXmlLoaderOptions()
);
$xml->setParserProperty(2, true);
// Step into the first level of content of the XML
$xml->read();
while ($xml->read()) {
// Quickly jump through to the office:body node
< while ($xml->name !== 'office:body') {
> while (self::getXmlName($xml) !== 'office:body') {
if ($xml->isEmptyElement) {
$xml->read();
} else {
$xml->next();
}
}
// Now read each node until we find our first table:table node
while ($xml->read()) {
< if ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) {
> if (self::getXmlName($xml) == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) {
$worksheetNames[] = $xml->getAttribute('table:name');
$tmpInfo = [
'worksheetName' => $xml->getAttribute('table:name'),
'lastColumnLetter' => 'A',
'lastColumnIndex' => 0,
'totalRows' => 0,
'totalColumns' => 0,
];
// Loop through each child node of the table:table element reading
$currCells = 0;
do {
$xml->read();
< if ($xml->name == 'table:table-row' && $xml->nodeType == XMLReader::ELEMENT) {
> if (self::getXmlName($xml) == 'table:table-row' && $xml->nodeType == XMLReader::ELEMENT) {
$rowspan = $xml->getAttribute('table:number-rows-repeated');
$rowspan = empty($rowspan) ? 1 : $rowspan;
$tmpInfo['totalRows'] += $rowspan;
$tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells);
$currCells = 0;
// Step into the row
$xml->read();
do {
< if ($xml->name == 'table:table-cell' && $xml->nodeType == XMLReader::ELEMENT) {
> $doread = true;
> if (self::getXmlName($xml) == 'table:table-cell' && $xml->nodeType == XMLReader::ELEMENT) {
if (!$xml->isEmptyElement) {
++$currCells;
$xml->next();
< } else {
< $xml->read();
> $doread = false;
}
< } elseif ($xml->name == 'table:covered-table-cell' && $xml->nodeType == XMLReader::ELEMENT) {
> } elseif (self::getXmlName($xml) == 'table:covered-table-cell' && $xml->nodeType == XMLReader::ELEMENT) {
$mergeSize = $xml->getAttribute('table:number-columns-repeated');
$currCells += (int) $mergeSize;
< $xml->read();
< } else {
> }
> if ($doread) {
$xml->read();
}
< } while ($xml->name != 'table:table-row');
> } while (self::getXmlName($xml) != 'table:table-row');
}
< } while ($xml->name != 'table:table');
> } while (self::getXmlName($xml) != 'table:table');
$tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells);
$tmpInfo['lastColumnIndex'] = $tmpInfo['totalColumns'] - 1;
$tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1);
$worksheetInfo[] = $tmpInfo;
}
}
}
return $worksheetInfo;
}
/**
< * Loads PhpSpreadsheet from file.
< *
< * @param string $pFilename
< *
< * @throws Exception
> * Counteract Phpstan caching.
*
< * @return Spreadsheet
> * @phpstan-impure
> */
> private static function getXmlName(XMLReader $xml): string
> {
> return $xml->name;
> }
>
> /**
> * Loads PhpSpreadsheet from file.
*/
< public function load($pFilename)
> protected function loadSpreadsheetFromFile(string $filename): Spreadsheet
{
// Create new Spreadsheet
$spreadsheet = new Spreadsheet();
// Load into this instance
< return $this->loadIntoExisting($pFilename, $spreadsheet);
> return $this->loadIntoExisting($filename, $spreadsheet);
}
/**
* Loads PhpSpreadsheet from file into PhpSpreadsheet instance.
*
< * @param string $pFilename
< * @param Spreadsheet $spreadsheet
< *
< * @throws Exception
> * @param string $filename
*
* @return Spreadsheet
*/
< public function loadIntoExisting($pFilename, Spreadsheet $spreadsheet)
> public function loadIntoExisting($filename, Spreadsheet $spreadsheet)
{
< File::assertFile($pFilename);
<
< $timezoneObj = new DateTimeZone('Europe/London');
< $GMT = new \DateTimeZone('UTC');
> File::assertFile($filename, self::INITIAL_FILE);
$zip = new ZipArchive();
< if (!$zip->open($pFilename)) {
< throw new Exception("Could not open {$pFilename} for reading! Error opening file.");
< }
> $zip->open($filename);
// Meta
< $xml = simplexml_load_string(
< $this->securityScanner->scan($zip->getFromName('meta.xml')),
> $xml = @simplexml_load_string(
> $this->getSecurityScannerOrThrow()->scan($zip->getFromName('meta.xml')),
'SimpleXMLElement',
Settings::getLibXmlLoaderOptions()
);
if ($xml === false) {
throw new Exception('Unable to read data from {$pFilename}');
}
$namespacesMeta = $xml->getNamespaces(true);
(new DocumentProperties($spreadsheet))->load($xml, $namespacesMeta);
< // Content
> // Styles
>
> $dom = new DOMDocument('1.01', 'UTF-8');
> $dom->loadXML(
> $this->getSecurityScannerOrThrow()->scan($zip->getFromName('styles.xml')),
> Settings::getLibXmlLoaderOptions()
> );
< $dom = new \DOMDocument('1.01', 'UTF-8');
> $pageSettings = new PageSettings($dom);
>
> // Main Content
>
> $dom = new DOMDocument('1.01', 'UTF-8');
$dom->loadXML(
< $this->securityScanner->scan($zip->getFromName('content.xml')),
> $this->getSecurityScannerOrThrow()->scan($zip->getFromName(self::INITIAL_FILE)),
Settings::getLibXmlLoaderOptions()
);
$officeNs = $dom->lookupNamespaceUri('office');
$tableNs = $dom->lookupNamespaceUri('table');
$textNs = $dom->lookupNamespaceUri('text');
$xlinkNs = $dom->lookupNamespaceUri('xlink');
> $styleNs = $dom->lookupNamespaceUri('style');
< $spreadsheets = $dom->getElementsByTagNameNS($officeNs, 'body')
< ->item(0)
< ->getElementsByTagNameNS($officeNs, 'spreadsheet');
> $pageSettings->readStyleCrossReferences($dom);
>
> $autoFilterReader = new AutoFilter($spreadsheet, $tableNs);
> $definedNameReader = new DefinedNames($spreadsheet, $tableNs);
> $columnWidths = [];
> $automaticStyle0 = $dom->getElementsByTagNameNS($officeNs, 'automatic-styles')->item(0);
> $automaticStyles = ($automaticStyle0 === null) ? [] : $automaticStyle0->getElementsByTagNameNS($styleNs, 'style');
> foreach ($automaticStyles as $automaticStyle) {
> $styleName = $automaticStyle->getAttributeNS($styleNs, 'name');
> $styleFamily = $automaticStyle->getAttributeNS($styleNs, 'family');
> if ($styleFamily === 'table-column') {
> $tcprops = $automaticStyle->getElementsByTagNameNS($styleNs, 'table-column-properties');
> if ($tcprops !== null) {
> $tcprop = $tcprops->item(0);
> if ($tcprop !== null) {
> $columnWidth = $tcprop->getAttributeNs($styleNs, 'column-width');
> $columnWidths[$styleName] = $columnWidth;
> }
> }
> }
> }
>
> // Content
> $item0 = $dom->getElementsByTagNameNS($officeNs, 'body')->item(0);
> $spreadsheets = ($item0 === null) ? [] : $item0->getElementsByTagNameNS($officeNs, 'spreadsheet');
foreach ($spreadsheets as $workbookData) {
< /** @var \DOMElement $workbookData */
> /** @var DOMElement $workbookData */
$tables = $workbookData->getElementsByTagNameNS($tableNs, 'table');
$worksheetID = 0;
foreach ($tables as $worksheetDataSet) {
< /** @var \DOMElement $worksheetDataSet */
> /** @var DOMElement $worksheetDataSet */
$worksheetName = $worksheetDataSet->getAttributeNS($tableNs, 'name');
// Check loadSheetsOnly
< if (isset($this->loadSheetsOnly)
> if (
> $this->loadSheetsOnly !== null
&& $worksheetName
< && !in_array($worksheetName, $this->loadSheetsOnly)) {
> && !in_array($worksheetName, $this->loadSheetsOnly)
> ) {
continue;
}
> $worksheetStyleName = $worksheetDataSet->getAttributeNS($tableNs, 'style-name');
// Create sheet
>
if ($worksheetID > 0) {
$spreadsheet->createSheet(); // First sheet is added by default
}
$spreadsheet->setActiveSheetIndex($worksheetID);
< if ($worksheetName) {
> if ($worksheetName || is_numeric($worksheetName)) {
// Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in
// formula cells... during the load, all formulae should be correct, and we're simply
// bringing the worksheet name in line with the formula, not the reverse
< $spreadsheet->getActiveSheet()->setTitle($worksheetName, false, false);
> $spreadsheet->getActiveSheet()->setTitle((string) $worksheetName, false, false);
}
// Go through every child of table element
$rowID = 1;
> $tableColumnIndex = 1;
foreach ($worksheetDataSet->childNodes as $childNode) {
< /** @var \DOMElement $childNode */
> /** @var DOMElement $childNode */
// Filter elements which are not under the "table" ns
if ($childNode->namespaceURI != $tableNs) {
continue;
}
$key = $childNode->nodeName;
// Remove ns from node name
if (strpos($key, ':') !== false) {
$keyChunks = explode(':', $key);
$key = array_pop($keyChunks);
}
switch ($key) {
case 'table-header-rows':
/// TODO :: Figure this out. This is only a partial implementation I guess.
// ($rowData it's not used at all and I'm not sure that PHPExcel
// has an API for this)
// foreach ($rowData as $keyRowData => $cellData) {
// $rowData = $cellData;
// break;
// }
break;
> case 'table-column':
case 'table-row':
> if ($childNode->hasAttributeNS($tableNs, 'number-columns-repeated')) {
if ($childNode->hasAttributeNS($tableNs, 'number-rows-repeated')) {
> $rowRepeats = (int) $childNode->getAttributeNS($tableNs, 'number-columns-repeated');
$rowRepeats = $childNode->getAttributeNS($tableNs, 'number-rows-repeated');
> } else {
} else {
> $rowRepeats = 1;
$rowRepeats = 1;
> }
}
> $tableStyleName = $childNode->getAttributeNS($tableNs, 'style-name');
> if (isset($columnWidths[$tableStyleName])) {
$columnID = 'A';
> $columnWidth = new HelperDimension($columnWidths[$tableStyleName]);
foreach ($childNode->childNodes as $key => $cellData) {
> $tableColumnString = Coordinate::stringFromColumnIndex($tableColumnIndex);
// @var \DOMElement $cellData
> for ($rowRepeats2 = $rowRepeats; $rowRepeats2 > 0; --$rowRepeats2) {
> $spreadsheet->getActiveSheet()
if ($this->getReadFilter() !== null) {
> ->getColumnDimension($tableColumnString)
if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
> ->setWidth($columnWidth->toUnit('cm'), 'cm');
++$columnID;
> ++$tableColumnString;
> }
continue;
> }
}
> $tableColumnIndex += $rowRepeats;
}
>
> break;
< $rowRepeats = $childNode->getAttributeNS($tableNs, 'number-rows-repeated');
> $rowRepeats = (int) $childNode->getAttributeNS($tableNs, 'number-rows-repeated');
< foreach ($childNode->childNodes as $key => $cellData) {
< // @var \DOMElement $cellData
<
> /** @var DOMElement $cellData */
> foreach ($childNode->childNodes as $cellData) {
> if ($cellData->hasAttributeNS($tableNs, 'number-columns-repeated')) {
if ($cellData->hasAttributeNS($tableNs, 'formula')) {
> $colRepeats = (int) $cellData->getAttributeNS($tableNs, 'number-columns-repeated');
$cellDataFormula = $cellData->getAttributeNS($tableNs, 'formula');
> } else {
$hasCalculatedValue = true;
> $colRepeats = 1;
}
> }
>
// Annotations
> for ($i = 0; $i < $colRepeats; ++$i) {
$annotation = $cellData->getElementsByTagNameNS($officeNs, 'annotation');
> }
< if ($annotation->length > 0) {
> if ($annotation->length > 0 && $annotation->item(0) !== null) {
$textNode = $annotation->item(0)->getElementsByTagNameNS($textNs, 'p');
< if ($textNode->length > 0) {
> if ($textNode->length > 0 && $textNode->item(0) !== null) {
$text = $this->scanElementForText($textNode->item(0));
$spreadsheet->getActiveSheet()
->getComment($columnID . $rowID)
->setText($this->parseRichText($text));
// ->setAuthor( $author )
}
}
// Content
< /** @var \DOMElement[] $paragraphs */
> /** @var DOMElement[] $paragraphs */
$paragraphs = [];
foreach ($cellData->childNodes as $item) {
< /** @var \DOMElement $item */
> /** @var DOMElement $item */
// Filter text:p elements
if ($item->nodeName == 'text:p') {
$paragraphs[] = $item;
}
}
if (count($paragraphs) > 0) {
// Consolidate if there are multiple p records (maybe with spans as well)
$dataArray = [];
// Text can have multiple text:p and within those, multiple text:span.
// text:p newlines, but text:span does not.
// Also, here we assume there is no text data is span fields are specified, since
// we have no way of knowing proper positioning anyway.
foreach ($paragraphs as $pData) {
$dataArray[] = $this->scanElementForText($pData);
}
$allCellDataText = implode("\n", $dataArray);
$type = $cellData->getAttributeNS($officeNs, 'value-type');
switch ($type) {
case 'string':
$type = DataType::TYPE_STRING;
$dataValue = $allCellDataText;
foreach ($paragraphs as $paragraph) {
$link = $paragraph->getElementsByTagNameNS($textNs, 'a');
< if ($link->length > 0) {
> if ($link->length > 0 && $link->item(0) !== null) {
$hyperlink = $link->item(0)->getAttributeNS($xlinkNs, 'href');
}
}
break;
case 'boolean':
$type = DataType::TYPE_BOOL;
$dataValue = ($allCellDataText == 'TRUE') ? true : false;
break;
case 'percentage':
$type = DataType::TYPE_NUMERIC;
$dataValue = (float) $cellData->getAttributeNS($officeNs, 'value');
< if (floor($dataValue) == $dataValue) {
< $dataValue = (int) $dataValue;
< }
> // percentage should always be float
> //if (floor($dataValue) == $dataValue) {
> // $dataValue = (int) $dataValue;
> //}
$formatting = NumberFormat::FORMAT_PERCENTAGE_00;
break;
case 'currency':
$type = DataType::TYPE_NUMERIC;
$dataValue = (float) $cellData->getAttributeNS($officeNs, 'value');
if (floor($dataValue) == $dataValue) {
$dataValue = (int) $dataValue;
}
< $formatting = NumberFormat::FORMAT_CURRENCY_USD_SIMPLE;
> $formatting = NumberFormat::FORMAT_CURRENCY_USD_INTEGER;
break;
case 'float':
$type = DataType::TYPE_NUMERIC;
$dataValue = (float) $cellData->getAttributeNS($officeNs, 'value');
if (floor($dataValue) == $dataValue) {
if ($dataValue == (int) $dataValue) {
$dataValue = (int) $dataValue;
< } else {
< $dataValue = (float) $dataValue;
}
}
break;
case 'date':
$type = DataType::TYPE_NUMERIC;
$value = $cellData->getAttributeNS($officeNs, 'date-value');
<
< $dateObj = new DateTime($value, $GMT);
< $dateObj->setTimeZone($timezoneObj);
< [$year, $month, $day, $hour, $minute, $second] = explode(
< ' ',
< $dateObj->format('Y m d H i s')
< );
<
< $dataValue = Date::formattedPHPToExcel(
< (int) $year,
< (int) $month,
< (int) $day,
< (int) $hour,
< (int) $minute,
< (int) $second
< );
> $dataValue = Date::convertIsoDate($value);
if ($dataValue != floor($dataValue)) {
$formatting = NumberFormat::FORMAT_DATE_XLSX15
. ' '
. NumberFormat::FORMAT_DATE_TIME4;
} else {
$formatting = NumberFormat::FORMAT_DATE_XLSX15;
}
break;
case 'time':
$type = DataType::TYPE_NUMERIC;
$timeValue = $cellData->getAttributeNS($officeNs, 'time-value');
$dataValue = Date::PHPToExcel(
strtotime(
< '01-01-1970 ' . implode(':', sscanf($timeValue, 'PT%dH%dM%dS'))
> '01-01-1970 ' . implode(':', /** @scrutinizer ignore-type */ sscanf($timeValue, 'PT%dH%dM%dS') ?? [])
)
);
$formatting = NumberFormat::FORMAT_DATE_TIME4;
break;
default:
$dataValue = null;
}
} else {
$type = DataType::TYPE_NULL;
$dataValue = null;
}
if ($hasCalculatedValue) {
$type = DataType::TYPE_FORMULA;
$cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1);
< $temp = explode('"', $cellDataFormula);
< $tKey = false;
< foreach ($temp as &$value) {
< // Only replace in alternate array entries (i.e. non-quoted blocks)
< if ($tKey = !$tKey) {
< // Cell range reference in another sheet
< $value = preg_replace('/\[([^\.]+)\.([^\.]+):\.([^\.]+)\]/U', '$1!$2:$3', $value);
<
< // Cell reference in another sheet
< $value = preg_replace('/\[([^\.]+)\.([^\.]+)\]/U', '$1!$2', $value);
<
< // Cell range reference
< $value = preg_replace('/\[\.([^\.]+):\.([^\.]+)\]/U', '$1:$2', $value);
<
< // Simple cell reference
< $value = preg_replace('/\[\.([^\.]+)\]/U', '$1', $value);
<
< $value = Calculation::translateSeparator(';', ',', $value, $inBraces);
< }
< }
< unset($value);
<
< // Then rebuild the formula string
< $cellDataFormula = implode('"', $temp);
> $cellDataFormula = FormulaTranslator::convertToExcelFormulaValue($cellDataFormula);
}
if ($cellData->hasAttributeNS($tableNs, 'number-columns-repeated')) {
$colRepeats = (int) $cellData->getAttributeNS($tableNs, 'number-columns-repeated');
} else {
$colRepeats = 1;
}
if ($type !== null) {
for ($i = 0; $i < $colRepeats; ++$i) {
if ($i > 0) {
++$columnID;
}
if ($type !== DataType::TYPE_NULL) {
for ($rowAdjust = 0; $rowAdjust < $rowRepeats; ++$rowAdjust) {
$rID = $rowID + $rowAdjust;
$cell = $spreadsheet->getActiveSheet()
->getCell($columnID . $rID);
// Set value
if ($hasCalculatedValue) {
$cell->setValueExplicit($cellDataFormula, $type);
} else {
$cell->setValueExplicit($dataValue, $type);
}
if ($hasCalculatedValue) {
$cell->setCalculatedValue($dataValue);
}
// Set other properties
if ($formatting !== null) {
$spreadsheet->getActiveSheet()
->getStyle($columnID . $rID)
->getNumberFormat()
->setFormatCode($formatting);
} else {
$spreadsheet->getActiveSheet()
->getStyle($columnID . $rID)
->getNumberFormat()
->setFormatCode(NumberFormat::FORMAT_GENERAL);
}
if ($hyperlink !== null) {
$cell->getHyperlink()
->setUrl($hyperlink);
}
}
}
}
}
// Merged cells
< if ($cellData->hasAttributeNS($tableNs, 'number-columns-spanned')
< || $cellData->hasAttributeNS($tableNs, 'number-rows-spanned')
< ) {
< if (($type !== DataType::TYPE_NULL) || (!$this->readDataOnly)) {
< $columnTo = $columnID;
> $this->processMergedCells($cellData, $tableNs, $type, $columnID, $rowID, $spreadsheet);
< if ($cellData->hasAttributeNS($tableNs, 'number-columns-spanned')) {
< $columnIndex = Coordinate::columnIndexFromString($columnID);
< $columnIndex += (int) $cellData->getAttributeNS($tableNs, 'number-columns-spanned');
< $columnIndex -= 2;
> ++$columnID;
> }
> $rowID += $rowRepeats;
< $columnTo = Coordinate::stringFromColumnIndex($columnIndex + 1);
> break;
> }
> }
> $pageSettings->setVisibilityForWorksheet($spreadsheet->getActiveSheet(), $worksheetStyleName);
> $pageSettings->setPrintSettingsForWorksheet($spreadsheet->getActiveSheet(), $worksheetStyleName);
> ++$worksheetID;
}
< $rowTo = $rowID;
> $autoFilterReader->read($workbookData);
> $definedNameReader->read($workbookData);
> }
> $spreadsheet->setActiveSheetIndex(0);
< if ($cellData->hasAttributeNS($tableNs, 'number-rows-spanned')) {
< $rowTo = $rowTo + (int) $cellData->getAttributeNS($tableNs, 'number-rows-spanned') - 1;
> if ($zip->locateName('settings.xml') !== false) {
> $this->processSettings($zip, $spreadsheet);
}
< $cellRange = $columnID . $rowID . ':' . $columnTo . $rowTo;
< $spreadsheet->getActiveSheet()->mergeCells($cellRange);
> // Return
> return $spreadsheet;
> }
>
> private function processSettings(ZipArchive $zip, Spreadsheet $spreadsheet): void
> {
> $dom = new DOMDocument('1.01', 'UTF-8');
> $dom->loadXML(
> $this->getSecurityScannerOrThrow()->scan($zip->getFromName('settings.xml')),
> Settings::getLibXmlLoaderOptions()
> );
> //$xlinkNs = $dom->lookupNamespaceUri('xlink');
> $configNs = $dom->lookupNamespaceUri('config');
> //$oooNs = $dom->lookupNamespaceUri('ooo');
> $officeNs = $dom->lookupNamespaceUri('office');
> $settings = $dom->getElementsByTagNameNS($officeNs, 'settings')
> ->item(0);
> if ($settings !== null) {
> $this->lookForActiveSheet($settings, $spreadsheet, $configNs);
> $this->lookForSelectedCells($settings, $spreadsheet, $configNs);
}
}
< ++$columnID;
> private function lookForActiveSheet(DOMElement $settings, Spreadsheet $spreadsheet, string $configNs): void
> {
> /** @var DOMElement $t */
> foreach ($settings->getElementsByTagNameNS($configNs, 'config-item') as $t) {
> if ($t->getAttributeNs($configNs, 'name') === 'ActiveTable') {
> try {
> $spreadsheet->setActiveSheetIndexByName($t->nodeValue ?? '');
> } catch (Throwable $e) {
> // do nothing
}
< $rowID += $rowRepeats;
break;
}
}
< ++$worksheetID;
}
>
}
> private function lookForSelectedCells(DOMElement $settings, Spreadsheet $spreadsheet, string $configNs): void
> {
// Return
> /** @var DOMElement $t */
return $spreadsheet;
> foreach ($settings->getElementsByTagNameNS($configNs, 'config-item-map-named') as $t) {
}
> if ($t->getAttributeNs($configNs, 'name') === 'Tables') {
> foreach ($t->getElementsByTagNameNS($configNs, 'config-item-map-entry') as $ws) {
/**
> $setRow = $setCol = '';
* Recursively scan element.
> $wsname = $ws->getAttributeNs($configNs, 'name');
*
> foreach ($ws->getElementsByTagNameNS($configNs, 'config-item') as $configItem) {
* @param \DOMNode $element
> $attrName = $configItem->getAttributeNs($configNs, 'name');
*
> if ($attrName === 'CursorPositionX') {
* @return string
> $setCol = $configItem->nodeValue;
*/
> }
protected function scanElementForText(\DOMNode $element)
> if ($attrName === 'CursorPositionY') {
{
> $setRow = $configItem->nodeValue;
$str = '';
> }
foreach ($element->childNodes as $child) {
> }
/** @var \DOMNode $child */
> $this->setSelected($spreadsheet, $wsname, "$setCol", "$setRow");
< // Return
< return $spreadsheet;
> break;
> }
> }
> }
>
> private function setSelected(Spreadsheet $spreadsheet, string $wsname, string $setCol, string $setRow): void
> {
> if (is_numeric($setCol) && is_numeric($setRow)) {
> $sheet = $spreadsheet->getSheetByName($wsname);
> if ($sheet !== null) {
> $sheet->setSelectedCells([(int) $setCol + 1, (int) $setRow + 1]);
> }
> }
< * @param \DOMNode $element
< *
< protected function scanElementForText(\DOMNode $element)
> protected function scanElementForText(DOMNode $element)
< /** @var \DOMNode $child */
> /** @var DOMNode $child */
< /** @var \DOMAttr $cAttr */
< $cAttr = $child->attributes->getNamedItem('c');
< if ($cAttr) {
< $multiplier = (int) $cAttr->nodeValue;
< } else {
< $multiplier = 1;
< }
<
> $attributes = $child->attributes;
> /** @var ?DOMAttr $cAttr */
> $cAttr = ($attributes === null) ? null : $attributes->getNamedItem('c');
> $multiplier = self::getMultiplier($cAttr);
$str .= str_repeat(' ', $multiplier);
}
if ($child->hasChildNodes()) {
$str .= $this->scanElementForText($child);
}
}
return $str;
}
> private static function getMultiplier(?DOMAttr $cAttr): int
/**
> {
* @param string $is
> if ($cAttr) {
*
> $multiplier = (int) $cAttr->nodeValue;
* @return RichText
> } else {
*/
> $multiplier = 1;
private function parseRichText($is)
> }
{
>
$value = new RichText();
> return $multiplier;
$value->createText($is);
> }
>
return $value;
> }
}
>
}
> private function processMergedCells(
> DOMElement $cellData,
> string $tableNs,
> string $type,
> string $columnID,
> int $rowID,
> Spreadsheet $spreadsheet
> ): void {
> if (
> $cellData->hasAttributeNS($tableNs, 'number-columns-spanned')
> || $cellData->hasAttributeNS($tableNs, 'number-rows-spanned')
> ) {
> if (($type !== DataType::TYPE_NULL) || ($this->readDataOnly === false)) {
> $columnTo = $columnID;
>
> if ($cellData->hasAttributeNS($tableNs, 'number-columns-spanned')) {
> $columnIndex = Coordinate::columnIndexFromString($columnID);
> $columnIndex += (int) $cellData->getAttributeNS($tableNs, 'number-columns-spanned');
> $columnIndex -= 2;
>
> $columnTo = Coordinate::stringFromColumnIndex($columnIndex + 1);
> }
>
> $rowTo = $rowID;
>
> if ($cellData->hasAttributeNS($tableNs, 'number-rows-spanned')) {
> $rowTo = $rowTo + (int) $cellData->getAttributeNS($tableNs, 'number-rows-spanned') - 1;
> }
>
> $cellRange = $columnID . $rowID . ':' . $columnTo . $rowTo;
> $spreadsheet->getActiveSheet()->mergeCells($cellRange, Worksheet::MERGE_CELL_CONTENT_HIDE);
> }
> }