40        parent::__construct();
 
   74            '<?xml version="1.0"',
 
   75            'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet',
 
   79        $data = file_get_contents($pFilename);
 
   85        foreach ($signature as $match) {
 
   87            if (strpos(
$data, $match) === 
false) {
 
   95        if (preg_match(
'/<?xml.*encoding=[\'"](.*?)[\'"].*?>/m', 
$data, $matches)) {
 
   96            $charSet = strtoupper($matches[1]);
 
   97            if (1 == preg_match(
'/^ISO-8859-\d[\dL]?$/i', $charSet)) {
 
   99                $data = preg_replace(
'/(<?xml.*encoding=[\'"]).*?([\'"].*?>)/um', 
'$1' . 
'UTF-8' . 
'$2', 
$data, 1);
 
  102        $this->fileContents = 
$data;
 
  117            $xml = simplexml_load_string(
 
  118                $this->securityScanner->scan($this->fileContents ?: file_get_contents($pFilename)),
 
  123            throw new Exception(
'Cannot load invalid XML file: ' . $pFilename, 0, $e);
 
  125        $this->fileContents = 
'';
 
  144        $worksheetNames = [];
 
  147        if (
$xml === 
false) {
 
  148            throw new Exception(
"Problem reading {$filename}");
 
  151        $namespaces = 
$xml->getNamespaces(
true);
 
  153        $xml_ss = 
$xml->children($namespaces[
'ss']);
 
  154        foreach ($xml_ss->Worksheet as $worksheet) {
 
  156            $worksheetNames[] = (string) $worksheet_ss[
'Name'];
 
  159        return $worksheetNames;
 
  179        if (
$xml === 
false) {
 
  180            throw new Exception(
"Problem reading {$filename}");
 
  183        $namespaces = 
$xml->getNamespaces(
true);
 
  186        $xml_ss = 
$xml->children($namespaces[
'ss']);
 
  187        foreach ($xml_ss->Worksheet as $worksheet) {
 
  191            $tmpInfo[
'worksheetName'] = 
'';
 
  192            $tmpInfo[
'lastColumnLetter'] = 
'A';
 
  193            $tmpInfo[
'lastColumnIndex'] = 0;
 
  194            $tmpInfo[
'totalRows'] = 0;
 
  195            $tmpInfo[
'totalColumns'] = 0;
 
  197            $tmpInfo[
'worksheetName'] = 
"Worksheet_{$worksheetID}";
 
  198            if (isset($worksheet_ss[
'Name'])) {
 
  199                $tmpInfo[
'worksheetName'] = (string) $worksheet_ss[
'Name'];
 
  202            if (isset($worksheet->Table->Row)) {
 
  205                foreach ($worksheet->Table->Row as $rowData) {
 
  209                    foreach ($rowData->Cell as $cell) {
 
  210                        if (isset($cell->Data)) {
 
  211                            $tmpInfo[
'lastColumnIndex'] = max($tmpInfo[
'lastColumnIndex'], $columnIndex);
 
  221                        $tmpInfo[
'totalRows'] = max($tmpInfo[
'totalRows'], $rowIndex);
 
  227            $tmpInfo[
'totalColumns'] = $tmpInfo[
'lastColumnIndex'] + 1;
 
  229            $worksheetInfo[] = $tmpInfo;
 
  233        return $worksheetInfo;
 
  247        $spreadsheet->removeSheetByIndex(0);
 
  250        return $this->loadIntoExisting(
$filename, $spreadsheet);
 
  268        if (
$xml === 
false) {
 
  269            throw new Exception(
"Problem reading {$filename}");
 
  272        $namespaces = 
$xml->getNamespaces(
true);
 
  274        (
new Properties($spreadsheet))->readProperties(
$xml, $namespaces);
 
  276        $this->styles = (
new Style())->parseStyles(
$xml, $namespaces);
 
  279        $xml_ss = 
$xml->children($namespaces[
'ss']);
 
  282        foreach ($xml_ss->Worksheet as $worksheetx) {
 
  283            $worksheet = $worksheetx ?? 
new SimpleXMLElement(
'<xml></xml>');
 
  287                isset($this->loadSheetsOnly, $worksheet_ss[
'Name']) &&
 
  288                (!in_array($worksheet_ss[
'Name'], $this->loadSheetsOnly))
 
  297            if (isset($worksheet_ss[
'Name'])) {
 
  298                $worksheetName = (string) $worksheet_ss[
'Name'];
 
  302                $spreadsheet->
getActiveSheet()->setTitle($worksheetName, 
false, 
false);
 
  306            if (isset($worksheet->Names[0])) {
 
  307                foreach ($worksheet->Names[0] as $definedName) {
 
  309                    $name = (string) $definedName_ss[
'Name'];
 
  310                    $definedValue = (string) $definedName_ss[
'RefersTo'];
 
  312                    if ($convertedValue[0] === 
'=') {
 
  313                        $convertedValue = substr($convertedValue, 1);
 
  320            if (isset($worksheet->Table->Column)) {
 
  321                foreach ($worksheet->Table->Column as $columnData) {
 
  323                    if (isset($columnData_ss[
'Index'])) {
 
  326                    if (isset($columnData_ss[
'Width'])) {
 
  327                        $columnWidth = $columnData_ss[
'Width'];
 
  328                        $spreadsheet->
getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
 
  335            if (isset($worksheet->Table->Row)) {
 
  336                $additionalMergedCells = 0;
 
  337                foreach ($worksheet->Table->Row as $rowData) {
 
  340                    if (isset($row_ss[
'Index'])) {
 
  341                        $rowID = (int) $row_ss[
'Index'];
 
  345                    foreach ($rowData->Cell as $cell) {
 
  347                        if (isset($cell_ss[
'Index'])) {
 
  350                        $cellRange = $columnID . $rowID;
 
  353                            if (!$this->
getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
 
  360                        if (isset($cell_ss[
'HRef'])) {
 
  361                            $spreadsheet->
getActiveSheet()->getCell($cellRange)->getHyperlink()->setUrl((
string) $cell_ss[
'HRef']);
 
  364                        if ((isset($cell_ss[
'MergeAcross'])) || (isset($cell_ss[
'MergeDown']))) {
 
  365                            $columnTo = $columnID;
 
  366                            if (isset($cell_ss[
'MergeAcross'])) {
 
  367                                $additionalMergedCells += (int) $cell_ss[
'MergeAcross'];
 
  371                            if (isset($cell_ss[
'MergeDown'])) {
 
  372                                $rowTo = $rowTo + $cell_ss[
'MergeDown'];
 
  374                            $cellRange .= 
':' . $columnTo . $rowTo;
 
  378                        $hasCalculatedValue = 
false;
 
  379                        $cellDataFormula = 
'';
 
  380                        if (isset($cell_ss[
'Formula'])) {
 
  381                            $cellDataFormula = $cell_ss[
'Formula'];
 
  382                            $hasCalculatedValue = 
true;
 
  384                        if (isset($cell->Data)) {
 
  385                            $cellData = $cell->Data;
 
  386                            $cellValue = (string) $cellData;
 
  389                            if (isset($cellData_ss[
'Type'])) {
 
  390                                $cellDataType = $cellData_ss[
'Type'];
 
  391                                switch ($cellDataType) {
 
  407                                        $cellValue = (float) $cellValue;
 
  408                                        if (floor($cellValue) == $cellValue) {
 
  409                                            $cellValue = (int) $cellValue;
 
  415                                        $cellValue = ($cellValue != 0);
 
  420                                        $dateTime = 
new DateTime($cellValue, 
new DateTimeZone(
'UTC'));
 
  426                                        $hasCalculatedValue = 
false;
 
  432                            if ($hasCalculatedValue) {
 
  438                            $spreadsheet->
getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue), 
$type);
 
  439                            if ($hasCalculatedValue) {
 
  440                                $spreadsheet->
getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue);
 
  445                        if (isset($cell->Comment)) {
 
  446                            $this->
parseCellComment($cell->Comment, $namespaces, $spreadsheet, $columnID, $rowID);
 
  449                        if (isset($cell_ss[
'StyleID'])) {
 
  450                            $style = (string) $cell_ss[
'StyleID'];
 
  451                            if ((isset($this->styles[
$style])) && (!empty($this->styles[
$style]))) {
 
  456                                    ->applyFromArray($this->styles[
$style]);
 
  460                        while ($additionalMergedCells > 0) {
 
  462                            --$additionalMergedCells;
 
  467                        if (isset($row_ss[
'Height'])) {
 
  468                            $rowHeight = $row_ss[
'Height'];
 
  469                            $spreadsheet->
getActiveSheet()->getRowDimension($rowID)->setRowHeight((
float) $rowHeight);
 
  476                if (isset($namespaces[
'x'])) {
 
  477                    $xmlX = $worksheet->children($namespaces[
'x']);
 
  478                    if (isset($xmlX->WorksheetOptions)) {
 
  479                        (
new PageSettings($xmlX, $namespaces))->loadPageSettings($spreadsheet);
 
  488        if (isset(
$xml->Names[0])) {
 
  489            foreach (
$xml->Names[0] as $definedName) {
 
  491                $name = (string) $definedName_ss[
'Name'];
 
  492                $definedValue = (string) $definedName_ss[
'RefersTo'];
 
  494                if ($convertedValue[0] === 
'=') {
 
  495                    $convertedValue = substr($convertedValue, 1);
 
  512        $commentAttributes = 
$comment->attributes($namespaces[
'ss']);
 
  514        if (isset($commentAttributes->Author)) {
 
  515            $author = (string) $commentAttributes->Author;
 
  519        $annotation = strip_tags((
string) $node);
 
  529        $value->createText($annotation);
 
  534    private static function getAttributes(?SimpleXMLElement $simple, 
string $node): SimpleXMLElement
 
  536        return ($simple === 
null)
 
  537            ? 
new SimpleXMLElement(
'<xml></xml>')
 
  538            : ($simple->attributes($node) ?? 
new SimpleXMLElement(
'<xml></xml>'));
 
An exception for terminatinating execution or to throw for unit testing.
static convertFormulaToA1(string $formula, int $currentRowNumber=1, int $currentColumnNumber=1)
Converts a formula that uses R1C1/SpreadsheetXML format cell address to an A1 format cell address.
Helper class to manipulate cell coordinates.
static columnIndexFromString($pString)
Column index from string.
static stringFromColumnIndex($columnIndex)
String from column index.
static createInstance(string $name, ?Worksheet $worksheet=null, ?string $value=null, bool $localOnly=false, ?Worksheet $scope=null)
Create a new defined name, either a range or a formula.
getReadFilter()
Read filter.
static getInstance(Reader\IReader $reader)
Reader for SpreadsheetML, the XML schema for Microsoft Office Excel 2003.
static getAttributes(?SimpleXMLElement $simple, string $node)
listWorksheetInfo($filename)
Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns).
trySimpleXMLLoadString($pFilename)
Check if the file is a valid SimpleXML.
__construct()
Create a new Excel2003XML Reader instance.
canRead($pFilename)
Can the current IReader read the file?
parseCellComment(SimpleXMLElement $comment, array $namespaces, Spreadsheet $spreadsheet, string $columnID, int $rowID)
load($filename)
Loads Spreadsheet from file.
listWorksheetNames($filename)
Reads names of the worksheets from a file, without parsing the whole file to a Spreadsheet object.
parseRichText(string $annotation)
static getLibXmlLoaderOptions()
Get default options for libxml loader.
static PHPToExcel($dateValue)
Convert a date from PHP to an MS Excel serialized date/time value.
static assertFile($filename)
Assert that given path is an existing file and is readable, otherwise throw exception.
static convertEncoding($value, $to, $from)
Convert string from one encoding to another.
createSheet($sheetIndex=null)
Create sheet and add it to this workbook.
setActiveSheetIndex($pIndex)
Set active sheet index.
addDefinedName(DefinedName $definedName)
Add a defined name (either a named range or a named formula).
getActiveSheet()
Get active sheet.