/** * Write VML comment to XML format * * @param \PHPExcel\Shared\XMLWriter $objWriter XML Writer * @param string $pCellReference Cell reference * @param \PHPExcel\Comment $pComment Comment * @throws \PHPExcel\Writer\Exception */ private function writeVMLComment(\PHPExcel\Shared\XMLWriter $objWriter = null, $pCellReference = 'A1', \PHPExcel\Comment $pComment = null) { // Metadata list($column, $row) = \PHPExcel\Cell::coordinateFromString($pCellReference); $column = \PHPExcel\Cell::columnIndexFromString($column); $id = 1024 + $column + $row; $id = substr($id, 0, 4); // v:shape $objWriter->startElement('v:shape'); $objWriter->writeAttribute('id', '_x0000_s' . $id); $objWriter->writeAttribute('type', '#_x0000_t202'); $objWriter->writeAttribute('style', 'position:absolute;margin-left:' . $pComment->getMarginLeft() . ';margin-top:' . $pComment->getMarginTop() . ';width:' . $pComment->getWidth() . ';height:' . $pComment->getHeight() . ';z-index:1;visibility:' . ($pComment->getVisible() ? 'visible' : 'hidden')); $objWriter->writeAttribute('fillcolor', '#' . $pComment->getFillColor()->getRGB()); $objWriter->writeAttribute('o:insetmode', 'auto'); // v:fill $objWriter->startElement('v:fill'); $objWriter->writeAttribute('color2', '#' . $pComment->getFillColor()->getRGB()); $objWriter->endElement(); // v:shadow $objWriter->startElement('v:shadow'); $objWriter->writeAttribute('on', 't'); $objWriter->writeAttribute('color', 'black'); $objWriter->writeAttribute('obscured', 't'); $objWriter->endElement(); // v:path $objWriter->startElement('v:path'); $objWriter->writeAttribute('o:connecttype', 'none'); $objWriter->endElement(); // v:textbox $objWriter->startElement('v:textbox'); $objWriter->writeAttribute('style', 'mso-direction-alt:auto'); // div $objWriter->startElement('div'); $objWriter->writeAttribute('style', 'text-align:left'); $objWriter->endElement(); $objWriter->endElement(); // x:ClientData $objWriter->startElement('x:ClientData'); $objWriter->writeAttribute('ObjectType', 'Note'); // x:MoveWithCells $objWriter->writeElement('x:MoveWithCells', ''); // x:SizeWithCells $objWriter->writeElement('x:SizeWithCells', ''); // x:Anchor //$objWriter->writeElement('x:Anchor', $column . ', 15, ' . ($row - 2) . ', 10, ' . ($column + 4) . ', 15, ' . ($row + 5) . ', 18'); // x:AutoFill $objWriter->writeElement('x:AutoFill', 'False'); // x:Row $objWriter->writeElement('x:Row', $row - 1); // x:Column $objWriter->writeElement('x:Column', $column - 1); $objWriter->endElement(); $objWriter->endElement(); }
/** * Writes all the DEFINEDNAME records (BIFF8). * So far this is only used for repeating rows/columns (print titles) and print areas */ private function writeAllDefinedNamesBiff8() { $chunk = ''; // Named ranges if (count($this->phpExcel->getNamedRanges()) > 0) { // Loop named ranges $namedRanges = $this->phpExcel->getNamedRanges(); foreach ($namedRanges as $namedRange) { // Create absolute coordinate $range = \PHPExcel\Cell::splitRange($namedRange->getRange()); for ($i = 0; $i < count($range); $i++) { $range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . \PHPExcel\Cell::absoluteCoordinate($range[$i][0]); if (isset($range[$i][1])) { $range[$i][1] = \PHPExcel\Cell::absoluteCoordinate($range[$i][1]); } } $range = \PHPExcel\Cell::buildRange($range); // e.g. Sheet1!$A$1:$B$2 // parse formula try { $error = $this->parser->parse($range); $formulaData = $this->parser->toReversePolish(); // make sure tRef3d is of type tRef3dR (0x3A) if (isset($formulaData[0]) and ($formulaData[0] == "z" or $formulaData[0] == "Z")) { $formulaData = ":" . substr($formulaData, 1); } if ($namedRange->getLocalOnly()) { // local scope $scope = $this->phpExcel->getIndex($namedRange->getScope()) + 1; } else { // global scope $scope = 0; } $chunk .= $this->writeData($this->writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false)); } catch (\PHPExcel\Exception $e) { // do nothing } } } // total number of sheets $total_worksheets = $this->phpExcel->getSheetCount(); // write the print titles (repeating rows, columns), if any for ($i = 0; $i < $total_worksheets; ++$i) { $sheetSetup = $this->phpExcel->getSheet($i)->getPageSetup(); // simultaneous repeatColumns repeatRows if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) { $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); $colmin = \PHPExcel\Cell::columnIndexFromString($repeat[0]) - 1; $colmax = \PHPExcel\Cell::columnIndexFromString($repeat[1]) - 1; $repeat = $sheetSetup->getRowsToRepeatAtTop(); $rowmin = $repeat[0] - 1; $rowmax = $repeat[1] - 1; // construct formula data manually $formulaData = pack('Cv', 0x29, 0x17); // tMemFunc $formulaData .= pack('Cvvvvv', 0x3b, $i, 0, 65535, $colmin, $colmax); // tArea3d $formulaData .= pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, 0, 255); // tArea3d $formulaData .= pack('C', 0x10); // tList // store the DEFINEDNAME record $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true)); // (exclusive) either repeatColumns or repeatRows } elseif ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) { // Columns to repeat if ($sheetSetup->isColumnsToRepeatAtLeftSet()) { $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); $colmin = \PHPExcel\Cell::columnIndexFromString($repeat[0]) - 1; $colmax = \PHPExcel\Cell::columnIndexFromString($repeat[1]) - 1; } else { $colmin = 0; $colmax = 255; } // Rows to repeat if ($sheetSetup->isRowsToRepeatAtTopSet()) { $repeat = $sheetSetup->getRowsToRepeatAtTop(); $rowmin = $repeat[0] - 1; $rowmax = $repeat[1] - 1; } else { $rowmin = 0; $rowmax = 65535; } // construct formula data manually because parser does not recognize absolute 3d cell references $formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax); // store the DEFINEDNAME record $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true)); } } // write the print areas, if any for ($i = 0; $i < $total_worksheets; ++$i) { $sheetSetup = $this->phpExcel->getSheet($i)->getPageSetup(); if ($sheetSetup->isPrintAreaSet()) { // Print area, e.g. A3:J6,H1:X20 $printArea = \PHPExcel\Cell::splitRange($sheetSetup->getPrintArea()); $countPrintArea = count($printArea); $formulaData = ''; for ($j = 0; $j < $countPrintArea; ++$j) { $printAreaRect = $printArea[$j]; // e.g. A3:J6 $printAreaRect[0] = \PHPExcel\Cell::coordinateFromString($printAreaRect[0]); $printAreaRect[1] = \PHPExcel\Cell::coordinateFromString($printAreaRect[1]); $print_rowmin = $printAreaRect[0][1] - 1; $print_rowmax = $printAreaRect[1][1] - 1; $print_colmin = \PHPExcel\Cell::columnIndexFromString($printAreaRect[0][0]) - 1; $print_colmax = \PHPExcel\Cell::columnIndexFromString($printAreaRect[1][0]) - 1; // construct formula data manually because parser does not recognize absolute 3d cell references $formulaData .= pack('Cvvvvv', 0x3b, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax); if ($j > 0) { $formulaData .= pack('C', 0x10); // list operator token ',' } } // store the DEFINEDNAME record $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true)); } } // write autofilters, if any for ($i = 0; $i < $total_worksheets; ++$i) { $sheetAutoFilter = $this->phpExcel->getSheet($i)->getAutoFilter(); $autoFilterRange = $sheetAutoFilter->getRange(); if (!empty($autoFilterRange)) { $rangeBounds = \PHPExcel\Cell::rangeBoundaries($autoFilterRange); //Autofilter built in name $name = pack('C', 0xd); $chunk .= $this->writeData($this->writeShortNameBiff8($name, $i + 1, $rangeBounds, true)); } } return $chunk; }
/** * OFFSET * * Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. * The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and * the number of columns to be returned. * * Excel Function: * =OFFSET(cellAddress, rows, cols, [height], [width]) * * @param cellAddress The reference from which you want to base the offset. Reference must refer to a cell or * range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value. * @param rows The number of rows, up or down, that you want the upper-left cell to refer to. * Using 5 as the rows argument specifies that the upper-left cell in the reference is * five rows below reference. Rows can be positive (which means below the starting reference) * or negative (which means above the starting reference). * @param cols The number of columns, to the left or right, that you want the upper-left cell of the result * to refer to. Using 5 as the cols argument specifies that the upper-left cell in the * reference is five columns to the right of reference. Cols can be positive (which means * to the right of the starting reference) or negative (which means to the left of the * starting reference). * @param height The height, in number of rows, that you want the returned reference to be. Height must be a positive number. * @param width The width, in number of columns, that you want the returned reference to be. Width must be a positive number. * @return string A reference to a cell or range of cells */ public static function OFFSET($cellAddress = null, $rows = 0, $columns = 0, $height = null, $width = null) { $rows = Functions::flattenSingleValue($rows); $columns = Functions::flattenSingleValue($columns); $height = Functions::flattenSingleValue($height); $width = Functions::flattenSingleValue($width); if ($cellAddress == null) { return 0; } $args = func_get_args(); $pCell = array_pop($args); if (!is_object($pCell)) { return Functions::REF(); } $sheetName = null; if (strpos($cellAddress, "!")) { list($sheetName, $cellAddress) = explode("!", $cellAddress); $sheetName = trim($sheetName, "'"); } if (strpos($cellAddress, ":")) { list($startCell, $endCell) = explode(":", $cellAddress); } else { $startCell = $endCell = $cellAddress; } list($startCellColumn, $startCellRow) = \PHPExcel\Cell::coordinateFromString($startCell); list($endCellColumn, $endCellRow) = \PHPExcel\Cell::coordinateFromString($endCell); $startCellRow += $rows; $startCellColumn = \PHPExcel\Cell::columnIndexFromString($startCellColumn) - 1; $startCellColumn += $columns; if ($startCellRow <= 0 || $startCellColumn < 0) { return Functions::REF(); } $endCellColumn = \PHPExcel\Cell::columnIndexFromString($endCellColumn) - 1; if ($width != null && !is_object($width)) { $endCellColumn = $startCellColumn + $width - 1; } else { $endCellColumn += $columns; } $startCellColumn = \PHPExcel\Cell::stringFromColumnIndex($startCellColumn); if ($height != null && !is_object($height)) { $endCellRow = $startCellRow + $height - 1; } else { $endCellRow += $rows; } if ($endCellRow <= 0 || $endCellColumn < 0) { return Functions::REF(); } $endCellColumn = \PHPExcel\Cell::stringFromColumnIndex($endCellColumn); $cellAddress = $startCellColumn . $startCellRow; if ($startCellColumn != $endCellColumn || $startCellRow != $endCellRow) { $cellAddress .= ':' . $endCellColumn . $endCellRow; } if ($sheetName !== null) { $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName); } else { $pSheet = $pCell->getWorksheet(); } return \PHPExcel\Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, false); }
/** * Write CFHeader record */ private function writeCFHeader() { $record = 0x1b0; // Record identifier $length = 0x16; // Bytes to follow $numColumnMin = null; $numColumnMax = null; $numRowMin = null; $numRowMax = null; $arrConditional = array(); foreach ($this->phpSheet->getConditionalStylesCollection() as $cellCoordinate => $conditionalStyles) { foreach ($conditionalStyles as $conditional) { if ($conditional->getConditionType() == \PHPExcel\Style\Conditional::CONDITION_EXPRESSION || $conditional->getConditionType() == \PHPExcel\Style\Conditional::CONDITION_CELLIS) { if (!in_array($conditional->getHashCode(), $arrConditional)) { $arrConditional[] = $conditional->getHashCode(); } // Cells $arrCoord = \PHPExcel\Cell::coordinateFromString($cellCoordinate); if (!is_numeric($arrCoord[0])) { $arrCoord[0] = \PHPExcel\Cell::columnIndexFromString($arrCoord[0]); } if (is_null($numColumnMin) || $numColumnMin > $arrCoord[0]) { $numColumnMin = $arrCoord[0]; } if (is_null($numColumnMax) || $numColumnMax < $arrCoord[0]) { $numColumnMax = $arrCoord[0]; } if (is_null($numRowMin) || $numRowMin > $arrCoord[1]) { $numRowMin = $arrCoord[1]; } if (is_null($numRowMax) || $numRowMax < $arrCoord[1]) { $numRowMax = $arrCoord[1]; } } } } $needRedraw = 1; $cellRange = pack('vvvv', $numRowMin - 1, $numRowMax - 1, $numColumnMin - 1, $numColumnMax - 1); $header = pack('vv', $record, $length); $data = pack('vv', count($arrConditional), $needRedraw); $data .= $cellRange; $data .= pack('v', 0x1); $data .= $cellRange; $this->append($header . $data); }
/** * Write drawings to XML format * * @param \PHPExcel\Shared\XMLWriter $objWriter XML Writer * @param \PHPExcel\Worksheet\BaseDrawing $pDrawing * @param int $pRelationId * @throws \PHPExcel\Writer\Exception */ public function writeDrawing(\PHPExcel\Shared\XMLWriter $objWriter = null, \PHPExcel\Worksheet\BaseDrawing $pDrawing = null, $pRelationId = -1) { if ($pRelationId >= 0) { // xdr:oneCellAnchor $objWriter->startElement('xdr:oneCellAnchor'); // Image location $aCoordinates = \PHPExcel\Cell::coordinateFromString($pDrawing->getCoordinates()); $aCoordinates[0] = \PHPExcel\Cell::columnIndexFromString($aCoordinates[0]); // xdr:from $objWriter->startElement('xdr:from'); $objWriter->writeElement('xdr:col', $aCoordinates[0] - 1); $objWriter->writeElement('xdr:colOff', \PHPExcel\Shared\Drawing::pixelsToEMU($pDrawing->getOffsetX())); $objWriter->writeElement('xdr:row', $aCoordinates[1] - 1); $objWriter->writeElement('xdr:rowOff', \PHPExcel\Shared\Drawing::pixelsToEMU($pDrawing->getOffsetY())); $objWriter->endElement(); // xdr:ext $objWriter->startElement('xdr:ext'); $objWriter->writeAttribute('cx', \PHPExcel\Shared\Drawing::pixelsToEMU($pDrawing->getWidth())); $objWriter->writeAttribute('cy', \PHPExcel\Shared\Drawing::pixelsToEMU($pDrawing->getHeight())); $objWriter->endElement(); // xdr:pic $objWriter->startElement('xdr:pic'); // xdr:nvPicPr $objWriter->startElement('xdr:nvPicPr'); // xdr:cNvPr $objWriter->startElement('xdr:cNvPr'); $objWriter->writeAttribute('id', $pRelationId); $objWriter->writeAttribute('name', $pDrawing->getName()); $objWriter->writeAttribute('descr', $pDrawing->getDescription()); $objWriter->endElement(); // xdr:cNvPicPr $objWriter->startElement('xdr:cNvPicPr'); // a:picLocks $objWriter->startElement('a:picLocks'); $objWriter->writeAttribute('noChangeAspect', '1'); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); // xdr:blipFill $objWriter->startElement('xdr:blipFill'); // a:blip $objWriter->startElement('a:blip'); $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'); $objWriter->writeAttribute('r:embed', 'rId' . $pRelationId); $objWriter->endElement(); // a:stretch $objWriter->startElement('a:stretch'); $objWriter->writeElement('a:fillRect', null); $objWriter->endElement(); $objWriter->endElement(); // xdr:spPr $objWriter->startElement('xdr:spPr'); // a:xfrm $objWriter->startElement('a:xfrm'); $objWriter->writeAttribute('rot', \PHPExcel\Shared\Drawing::degreesToAngle($pDrawing->getRotation())); $objWriter->endElement(); // a:prstGeom $objWriter->startElement('a:prstGeom'); $objWriter->writeAttribute('prst', 'rect'); // a:avLst $objWriter->writeElement('a:avLst', null); $objWriter->endElement(); // // a:solidFill // $objWriter->startElement('a:solidFill'); // // a:srgbClr // $objWriter->startElement('a:srgbClr'); // $objWriter->writeAttribute('val', 'FFFFFF'); ///* SHADE // // a:shade // $objWriter->startElement('a:shade'); // $objWriter->writeAttribute('val', '85000'); // $objWriter->endElement(); //*/ // $objWriter->endElement(); // $objWriter->endElement(); /* // a:ln $objWriter->startElement('a:ln'); $objWriter->writeAttribute('w', '88900'); $objWriter->writeAttribute('cap', 'sq'); // a:solidFill $objWriter->startElement('a:solidFill'); // a:srgbClr $objWriter->startElement('a:srgbClr'); $objWriter->writeAttribute('val', 'FFFFFF'); $objWriter->endElement(); $objWriter->endElement(); // a:miter $objWriter->startElement('a:miter'); $objWriter->writeAttribute('lim', '800000'); $objWriter->endElement(); $objWriter->endElement(); */ if ($pDrawing->getShadow()->getVisible()) { // a:effectLst $objWriter->startElement('a:effectLst'); // a:outerShdw $objWriter->startElement('a:outerShdw'); $objWriter->writeAttribute('blurRad', \PHPExcel\Shared\Drawing::pixelsToEMU($pDrawing->getShadow()->getBlurRadius())); $objWriter->writeAttribute('dist', \PHPExcel\Shared\Drawing::pixelsToEMU($pDrawing->getShadow()->getDistance())); $objWriter->writeAttribute('dir', \PHPExcel\Shared\Drawing::degreesToAngle($pDrawing->getShadow()->getDirection())); $objWriter->writeAttribute('algn', $pDrawing->getShadow()->getAlignment()); $objWriter->writeAttribute('rotWithShape', '0'); // a:srgbClr $objWriter->startElement('a:srgbClr'); $objWriter->writeAttribute('val', $pDrawing->getShadow()->getColor()->getRGB()); // a:alpha $objWriter->startElement('a:alpha'); $objWriter->writeAttribute('val', $pDrawing->getShadow()->getAlpha() * 1000); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); } /* // a:scene3d $objWriter->startElement('a:scene3d'); // a:camera $objWriter->startElement('a:camera'); $objWriter->writeAttribute('prst', 'orthographicFront'); $objWriter->endElement(); // a:lightRig $objWriter->startElement('a:lightRig'); $objWriter->writeAttribute('rig', 'twoPt'); $objWriter->writeAttribute('dir', 't'); // a:rot $objWriter->startElement('a:rot'); $objWriter->writeAttribute('lat', '0'); $objWriter->writeAttribute('lon', '0'); $objWriter->writeAttribute('rev', '0'); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); */ /* // a:sp3d $objWriter->startElement('a:sp3d'); // a:bevelT $objWriter->startElement('a:bevelT'); $objWriter->writeAttribute('w', '25400'); $objWriter->writeAttribute('h', '19050'); $objWriter->endElement(); // a:contourClr $objWriter->startElement('a:contourClr'); // a:srgbClr $objWriter->startElement('a:srgbClr'); $objWriter->writeAttribute('val', 'FFFFFF'); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); */ $objWriter->endElement(); $objWriter->endElement(); // xdr:clientData $objWriter->writeElement('xdr:clientData', null); $objWriter->endElement(); } else { throw new \PHPExcel\Writer\Exception("Invalid parameters passed."); } }
/** * Loads Spreadsheet from file * * @param string $pFilename * @return Spreadsheet * @throws Exception */ public function load($pFilename) { // Check if file exists if (!file_exists($pFilename)) { throw new Exception("Could not open " . $pFilename . " for reading! File does not exist."); } // Initialisations $excel = new \PHPExcel\Spreadsheet(); $excel->removeSheetByIndex(0); if (!$this->readDataOnly) { $excel->removeCellStyleXfByIndex(0); // remove the default style $excel->removeCellXfByIndex(0); // remove the default style } $zipClass = \PHPExcel\Settings::getZipClass(); $zip = new $zipClass(); $zip->open($pFilename); // Read the theme first, because we need the colour scheme when reading the styles $wbRels = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "xl/_rels/workbook.xml.rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); foreach ($wbRels->Relationship as $rel) { switch ($rel["Type"]) { case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme": $themeOrderArray = array('lt1', 'dk1', 'lt2', 'dk2'); $themeOrderAdditional = count($themeOrderArray); $xmlTheme = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "xl/{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); if (is_object($xmlTheme)) { $xmlThemeName = $xmlTheme->attributes(); $xmlTheme = $xmlTheme->children("http://schemas.openxmlformats.org/drawingml/2006/main"); $themeName = (string) $xmlThemeName['name']; $colourScheme = $xmlTheme->themeElements->clrScheme->attributes(); $colourSchemeName = (string) $colourScheme['name']; $colourScheme = $xmlTheme->themeElements->clrScheme->children("http://schemas.openxmlformats.org/drawingml/2006/main"); $themeColours = array(); foreach ($colourScheme as $k => $xmlColour) { $themePos = array_search($k, $themeOrderArray); if ($themePos === false) { $themePos = $themeOrderAdditional++; } if (isset($xmlColour->sysClr)) { $xmlColourData = $xmlColour->sysClr->attributes(); $themeColours[$themePos] = $xmlColourData['lastClr']; } elseif (isset($xmlColour->srgbClr)) { $xmlColourData = $xmlColour->srgbClr->attributes(); $themeColours[$themePos] = $xmlColourData['val']; } } self::$theme = new Excel2007\Theme($themeName, $colourSchemeName, $themeColours); } break; } } $rels = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "_rels/.rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); foreach ($rels->Relationship as $rel) { switch ($rel["Type"]) { case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties": $xmlCore = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); if (is_object($xmlCore)) { $xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/"); $xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/"); $xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties"); $docProps = $excel->getProperties(); $docProps->setCreator((string) self::getArrayItem($xmlCore->xpath("dc:creator"))); $docProps->setLastModifiedBy((string) self::getArrayItem($xmlCore->xpath("cp:lastModifiedBy"))); $docProps->setCreated(strtotime(self::getArrayItem($xmlCore->xpath("dcterms:created")))); //! respect xsi:type $docProps->setModified(strtotime(self::getArrayItem($xmlCore->xpath("dcterms:modified")))); //! respect xsi:type $docProps->setTitle((string) self::getArrayItem($xmlCore->xpath("dc:title"))); $docProps->setDescription((string) self::getArrayItem($xmlCore->xpath("dc:description"))); $docProps->setSubject((string) self::getArrayItem($xmlCore->xpath("dc:subject"))); $docProps->setKeywords((string) self::getArrayItem($xmlCore->xpath("cp:keywords"))); $docProps->setCategory((string) self::getArrayItem($xmlCore->xpath("cp:category"))); } break; case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties": $xmlCore = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); if (is_object($xmlCore)) { $docProps = $excel->getProperties(); if (isset($xmlCore->Company)) { $docProps->setCompany((string) $xmlCore->Company); } if (isset($xmlCore->Manager)) { $docProps->setManager((string) $xmlCore->Manager); } } break; case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties": $xmlCore = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); if (is_object($xmlCore)) { $docProps = $excel->getProperties(); foreach ($xmlCore as $xmlProperty) { $cellDataOfficeAttributes = $xmlProperty->attributes(); if (isset($cellDataOfficeAttributes['name'])) { $propertyName = (string) $cellDataOfficeAttributes['name']; $cellDataOfficeChildren = $xmlProperty->children('http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes'); $attributeType = $cellDataOfficeChildren->getName(); $attributeValue = (string) $cellDataOfficeChildren->{$attributeType}; $attributeValue = \PHPExcel\Document\Properties::convertProperty($attributeValue, $attributeType); $attributeType = \PHPExcel\Document\Properties::convertPropertyType($attributeType); $docProps->setCustomProperty($propertyName, $attributeValue, $attributeType); } } } break; //Ribbon //Ribbon case "http://schemas.microsoft.com/office/2006/relationships/ui/extensibility": $customUI = $rel['Target']; if (!is_null($customUI)) { $this->readRibbon($excel, $customUI, $zip); } break; case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument": $dir = dirname($rel["Target"]); $relsWorkbook = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$dir}/_rels/" . basename($rel["Target"]) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships"); $sharedStrings = array(); $xpath = self::getArrayItem($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']")); $xmlStrings = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$dir}/{$xpath['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); if (isset($xmlStrings) && isset($xmlStrings->si)) { foreach ($xmlStrings->si as $val) { if (isset($val->t)) { $sharedStrings[] = \PHPExcel\Shared\StringHelper::controlCharacterOOXML2PHP((string) $val->t); } elseif (isset($val->r)) { $sharedStrings[] = $this->parseRichText($val); } } } $worksheets = array(); $macros = $customUI = null; foreach ($relsWorkbook->Relationship as $ele) { switch ($ele['Type']) { case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet": $worksheets[(string) $ele["Id"]] = $ele["Target"]; break; // a vbaProject ? (: some macros) // a vbaProject ? (: some macros) case "http://schemas.microsoft.com/office/2006/relationships/vbaProject": $macros = $ele["Target"]; break; } } if (!is_null($macros)) { $macrosCode = $this->getFromZipArchive($zip, 'xl/vbaProject.bin'); //vbaProject.bin always in 'xl' dir and always named vbaProject.bin if ($macrosCode !== false) { $excel->setMacrosCode($macrosCode); $excel->setHasMacros(true); //short-circuit : not reading vbaProject.bin.rel to get Signature =>allways vbaProjectSignature.bin in 'xl' dir $Certificate = $this->getFromZipArchive($zip, 'xl/vbaProjectSignature.bin'); if ($Certificate !== false) { $excel->setMacrosCertificate($Certificate); } } } $styles = array(); $cellStyles = array(); $xpath = self::getArrayItem($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']")); $xmlStyles = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$dir}/{$xpath['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $numFmts = null; if ($xmlStyles && $xmlStyles->numFmts[0]) { $numFmts = $xmlStyles->numFmts[0]; } if (isset($numFmts) && $numFmts !== null) { $numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main"); } if (!$this->readDataOnly && $xmlStyles) { foreach ($xmlStyles->cellXfs->xf as $xf) { $numFmt = \PHPExcel\Style\NumberFormat::FORMAT_GENERAL; if ($xf["numFmtId"]) { if (isset($numFmts)) { $tmpNumFmt = self::getArrayItem($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]")); if (isset($tmpNumFmt["formatCode"])) { $numFmt = (string) $tmpNumFmt["formatCode"]; } } // We shouldn't override any of the built-in MS Excel values (values below id 164) // But there's a lot of naughty homebrew xlsx writers that do use "reserved" id values that aren't actually used // So we make allowance for them rather than lose formatting masks if ((int) $xf["numFmtId"] < 164 && \PHPExcel\Style\NumberFormat::builtInFormatCode((int) $xf["numFmtId"]) !== '') { $numFmt = \PHPExcel\Style\NumberFormat::builtInFormatCode((int) $xf["numFmtId"]); } } $quotePrefix = false; if (isset($xf["quotePrefix"])) { $quotePrefix = (bool) $xf["quotePrefix"]; } $style = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection, "quotePrefix" => $quotePrefix); $styles[] = $style; // add style to cellXf collection $objStyle = new \PHPExcel\Style(); self::readStyle($objStyle, $style); $excel->addCellXf($objStyle); } foreach ($xmlStyles->cellStyleXfs->xf as $xf) { $numFmt = \PHPExcel\Style\NumberFormat::FORMAT_GENERAL; if ($numFmts && $xf["numFmtId"]) { $tmpNumFmt = self::getArrayItem($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]")); if (isset($tmpNumFmt["formatCode"])) { $numFmt = (string) $tmpNumFmt["formatCode"]; } elseif ((int) $xf["numFmtId"] < 165) { $numFmt = \PHPExcel\Style\NumberFormat::builtInFormatCode((int) $xf["numFmtId"]); } } $cellStyle = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection, "quotePrefix" => $quotePrefix); $cellStyles[] = $cellStyle; // add style to cellStyleXf collection $objStyle = new \PHPExcel\Style(); self::readStyle($objStyle, $cellStyle); $excel->addCellStyleXf($objStyle); } } $dxfs = array(); if (!$this->readDataOnly && $xmlStyles) { // Conditional Styles if ($xmlStyles->dxfs) { foreach ($xmlStyles->dxfs->dxf as $dxf) { $style = new \PHPExcel\Style(false, true); self::readStyle($style, $dxf); $dxfs[] = $style; } } // Cell Styles if ($xmlStyles->cellStyles) { foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) { if (intval($cellStyle['builtinId']) == 0) { if (isset($cellStyles[intval($cellStyle['xfId'])])) { // Set default style $style = new \PHPExcel\Style(); self::readStyle($style, $cellStyles[intval($cellStyle['xfId'])]); // normal style, currently not using it for anything } } } } } $xmlWorkbook = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); // Set base date if ($xmlWorkbook->workbookPr) { \PHPExcel\Shared\Date::setExcelCalendar(\PHPExcel\Shared\Date::CALENDAR_WINDOWS_1900); if (isset($xmlWorkbook->workbookPr['date1904'])) { if (self::boolean((string) $xmlWorkbook->workbookPr['date1904'])) { \PHPExcel\Shared\Date::setExcelCalendar(\PHPExcel\Shared\Date::CALENDAR_MAC_1904); } } } $sheetId = 0; // keep track of new sheet id in final workbook $oldSheetId = -1; // keep track of old sheet id in final workbook $countSkippedSheets = 0; // keep track of number of skipped sheets $mapSheetId = array(); // mapping of sheet ids from old to new $charts = $chartDetails = array(); if ($xmlWorkbook->sheets) { foreach ($xmlWorkbook->sheets->sheet as $eleSheet) { ++$oldSheetId; // Check if sheet should be skipped if (isset($this->loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->loadSheetsOnly)) { ++$countSkippedSheets; $mapSheetId[$oldSheetId] = null; continue; } // Map old sheet id in original workbook to new sheet id. // They will differ if loadSheetsOnly() is being used $mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets; // Load sheet $docSheet = $excel->createSheet(); // 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 $docSheet->setTitle((string) $eleSheet["name"], false); $fileWorksheet = $worksheets[(string) self::getArrayItem($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")]; $xmlSheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$dir}/{$fileWorksheet}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $sharedFormulas = array(); if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') { $docSheet->setSheetState((string) $eleSheet["state"]); } if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) { if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) { $docSheet->getSheetView()->setZoomScale(intval($xmlSheet->sheetViews->sheetView['zoomScale'])); } if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) { $docSheet->getSheetView()->setZoomScaleNormal(intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])); } if (isset($xmlSheet->sheetViews->sheetView['view'])) { $docSheet->getSheetView()->setView((string) $xmlSheet->sheetViews->sheetView['view']); } if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) { $docSheet->setShowGridLines(self::boolean((string) $xmlSheet->sheetViews->sheetView['showGridLines'])); } if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) { $docSheet->setShowRowColHeaders(self::boolean((string) $xmlSheet->sheetViews->sheetView['showRowColHeaders'])); } if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) { $docSheet->setRightToLeft(self::boolean((string) $xmlSheet->sheetViews->sheetView['rightToLeft'])); } if (isset($xmlSheet->sheetViews->sheetView->pane)) { if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) { $docSheet->freezePane((string) $xmlSheet->sheetViews->sheetView->pane['topLeftCell']); } else { $xSplit = 0; $ySplit = 0; if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) { $xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']); } if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) { $ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']); } $docSheet->freezePaneByColumnAndRow($xSplit, $ySplit); } } if (isset($xmlSheet->sheetViews->sheetView->selection)) { if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) { $sqref = (string) $xmlSheet->sheetViews->sheetView->selection['sqref']; $sqref = explode(' ', $sqref); $sqref = $sqref[0]; $docSheet->setSelectedCells($sqref); } } } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) { if (isset($xmlSheet->sheetPr->tabColor['rgb'])) { $docSheet->getTabColor()->setARGB((string) $xmlSheet->sheetPr->tabColor['rgb']); } } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr['codeName'])) { $docSheet->setCodeName((string) $xmlSheet->sheetPr['codeName']); } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) { if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryRight'])) { $docSheet->setShowSummaryRight(false); } else { $docSheet->setShowSummaryRight(true); } if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryBelow'])) { $docSheet->setShowSummaryBelow(false); } else { $docSheet->setShowSummaryBelow(true); } } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) { if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && !self::boolean((string) $xmlSheet->sheetPr->pageSetUpPr['fitToPage'])) { $docSheet->getPageSetup()->setFitToPage(false); } else { $docSheet->getPageSetup()->setFitToPage(true); } } if (isset($xmlSheet->sheetFormatPr)) { if (isset($xmlSheet->sheetFormatPr['customHeight']) && self::boolean((string) $xmlSheet->sheetFormatPr['customHeight']) && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) { $docSheet->getDefaultRowDimension()->setRowHeight((double) $xmlSheet->sheetFormatPr['defaultRowHeight']); } if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) { $docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']); } if (isset($xmlSheet->sheetFormatPr['zeroHeight']) && (string) $xmlSheet->sheetFormatPr['zeroHeight'] == '1') { $docSheet->getDefaultRowDimension()->setZeroHeight(true); } } if (isset($xmlSheet->cols) && !$this->readDataOnly) { foreach ($xmlSheet->cols->col as $col) { for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) { if ($col["style"] && !$this->readDataOnly) { $docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"])); } if (self::boolean($col["bestFit"])) { //$docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setAutoSize(true); } if (self::boolean($col["hidden"])) { // echo \PHPExcel\Cell::stringFromColumnIndex($i), ': HIDDEN COLUMN',PHP_EOL; $docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setVisible(false); } if (self::boolean($col["collapsed"])) { $docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setCollapsed(true); } if ($col["outlineLevel"] > 0) { $docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"])); } $docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"])); if (intval($col["max"]) == 16384) { break; } } } } if (isset($xmlSheet->printOptions) && !$this->readDataOnly) { if (self::boolean((string) $xmlSheet->printOptions['gridLinesSet'])) { $docSheet->setShowGridlines(true); } if (self::boolean((string) $xmlSheet->printOptions['gridLines'])) { $docSheet->setPrintGridlines(true); } if (self::boolean((string) $xmlSheet->printOptions['horizontalCentered'])) { $docSheet->getPageSetup()->setHorizontalCentered(true); } if (self::boolean((string) $xmlSheet->printOptions['verticalCentered'])) { $docSheet->getPageSetup()->setVerticalCentered(true); } } if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) { foreach ($xmlSheet->sheetData->row as $row) { if ($row["ht"] && !$this->readDataOnly) { $docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"])); } if (self::boolean($row["hidden"]) && !$this->readDataOnly) { $docSheet->getRowDimension(intval($row["r"]))->setVisible(false); } if (self::boolean($row["collapsed"])) { $docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true); } if ($row["outlineLevel"] > 0) { $docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"])); } if ($row["s"] && !$this->readDataOnly) { $docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"])); } foreach ($row->c as $c) { $r = (string) $c["r"]; $cellDataType = (string) $c["t"]; $value = null; $calculatedValue = null; // Read cell? if ($this->getReadFilter() !== null) { $coordinates = \PHPExcel\Cell::coordinateFromString($r); if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) { continue; } } // echo 'Reading cell ', $coordinates[0], $coordinates[1], PHP_EOL; // print_r($c); // echo PHP_EOL; // echo 'Cell Data Type is ', $cellDataType, ': '; // // Read cell! switch ($cellDataType) { case "s": // echo 'String', PHP_EOL; if ((string) $c->v != '') { $value = $sharedStrings[intval($c->v)]; if ($value instanceof \PHPExcel\RichText) { $value = clone $value; } } else { $value = ''; } break; case "b": // echo 'Boolean', PHP_EOL; if (!isset($c->f)) { $value = self::castToBoolean($c); } else { // Formula $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToBoolean'); if (isset($c->f['t'])) { $att = array(); $att = $c->f; $docSheet->getCell($r)->setFormulaAttributes($att); } // echo '$calculatedValue = ', $calculatedValue, PHP_EOL; } break; case "inlineStr": // echo 'Inline String', PHP_EOL; if (isset($c->f)) { $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToError'); } else { $value = $this->parseRichText($c->is); } break; case "e": // echo 'Error', PHP_EOL; if (!isset($c->f)) { $value = self::castToError($c); } else { // Formula $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToError'); // echo '$calculatedValue = ', $calculatedValue, PHP_EOL; } break; default: // echo 'Default', PHP_EOL; if (!isset($c->f)) { // echo 'Not a Formula', PHP_EOL; $value = self::castToString($c); } else { // echo 'Treat as Formula', PHP_EOL; // Formula $this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToString'); // echo '$calculatedValue = ', $calculatedValue, PHP_EOL; } break; } // echo 'Value is ', $value, PHP_EOL; // Check for numeric values if (is_numeric($value) && $cellDataType != 's') { if ($value == (int) $value) { $value = (int) $value; } elseif ($value == (double) $value) { $value = (double) $value; } elseif ($value == (double) $value) { $value = (double) $value; } } // Rich text? if ($value instanceof \PHPExcel\RichText && $this->readDataOnly) { $value = $value->getPlainText(); } $cell = $docSheet->getCell($r); // Assign value if ($cellDataType != '') { $cell->setValueExplicit($value, $cellDataType); } else { $cell->setValue($value); } if ($calculatedValue !== null) { $cell->setCalculatedValue($calculatedValue); } // Style information? if ($c["s"] && !$this->readDataOnly) { // no style index means 0, it seems $cell->setXfIndex(isset($styles[intval($c["s"])]) ? intval($c["s"]) : 0); } } } } $conditionals = array(); if (!$this->readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) { foreach ($xmlSheet->conditionalFormatting as $conditional) { foreach ($conditional->cfRule as $cfRule) { if (((string) $cfRule["type"] == \PHPExcel\Style\Conditional::CONDITION_NONE || (string) $cfRule["type"] == \PHPExcel\Style\Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == \PHPExcel\Style\Conditional::CONDITION_CONTAINSTEXT || (string) $cfRule["type"] == \PHPExcel\Style\Conditional::CONDITION_EXPRESSION) && isset($dxfs[intval($cfRule["dxfId"])])) { $conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule; } } } foreach ($conditionals as $ref => $cfRules) { ksort($cfRules); $conditionalStyles = array(); foreach ($cfRules as $cfRule) { $objConditional = new \PHPExcel\Style\Conditional(); $objConditional->setConditionType((string) $cfRule["type"]); $objConditional->setOperatorType((string) $cfRule["operator"]); if ((string) $cfRule["text"] != '') { $objConditional->setText((string) $cfRule["text"]); } if (count($cfRule->formula) > 1) { foreach ($cfRule->formula as $formula) { $objConditional->addCondition((string) $formula); } } else { $objConditional->addCondition((string) $cfRule->formula); } $objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]); $conditionalStyles[] = $objConditional; } // Extract all cell references in $ref $cellBlocks = explode(' ', str_replace('$', '', strtoupper($ref))); foreach ($cellBlocks as $cellBlock) { $docSheet->getStyle($cellBlock)->setConditionalStyles($conditionalStyles); } } } $aKeys = ["sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells"]; if (!$this->readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) { foreach ($aKeys as $key) { $method = "set" . ucfirst($key); $docSheet->getProtection()->{$method}(self::boolean((string) $xmlSheet->sheetProtection[$key])); } } if (!$this->readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) { $docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true); if ($xmlSheet->protectedRanges->protectedRange) { foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) { $docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true); } } } if ($xmlSheet && $xmlSheet->autoFilter && !$this->readDataOnly) { $autoFilterRange = (string) $xmlSheet->autoFilter["ref"]; if (strpos($autoFilterRange, ':') !== false) { $autoFilter = $docSheet->getAutoFilter(); $autoFilter->setRange($autoFilterRange); foreach ($xmlSheet->autoFilter->filterColumn as $filterColumn) { $column = $autoFilter->getColumnByOffset((int) $filterColumn["colId"]); // Check for standard filters if ($filterColumn->filters) { $column->setFilterType(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER); $filters = $filterColumn->filters; if (isset($filters["blank"]) && $filters["blank"] == 1) { // Operator is undefined, but always treated as EQUAL $column->createRule()->setRule(null, '')->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_FILTER); } // Standard filters are always an OR join, so no join rule needs to be set // Entries can be either filter elements foreach ($filters->filter as $filterRule) { // Operator is undefined, but always treated as EQUAL $column->createRule()->setRule(null, (string) $filterRule["val"])->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_FILTER); } // Or Date Group elements foreach ($filters->dateGroupItem as $dateGroupItem) { $column->createRule()->setRule(null, array('year' => (string) $dateGroupItem["year"], 'month' => (string) $dateGroupItem["month"], 'day' => (string) $dateGroupItem["day"], 'hour' => (string) $dateGroupItem["hour"], 'minute' => (string) $dateGroupItem["minute"], 'second' => (string) $dateGroupItem["second"]), (string) $dateGroupItem["dateTimeGrouping"])->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP); } } // Check for custom filters if ($filterColumn->customFilters) { $column->setFilterType(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER); $customFilters = $filterColumn->customFilters; // Custom filters can an AND or an OR join; // and there should only ever be one or two entries if (isset($customFilters["and"]) && $customFilters["and"] == 1) { $column->setJoin(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND); } foreach ($customFilters->customFilter as $filterRule) { $column->createRule()->setRule((string) $filterRule["operator"], (string) $filterRule["val"])->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); } } // Check for dynamic filters if ($filterColumn->dynamicFilter) { $column->setFilterType(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER); // We should only ever have one dynamic filter foreach ($filterColumn->dynamicFilter as $filterRule) { $column->createRule()->setRule(null, (string) $filterRule["val"], (string) $filterRule["type"])->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER); if (isset($filterRule["val"])) { $column->setAttribute('val', (string) $filterRule["val"]); } if (isset($filterRule["maxVal"])) { $column->setAttribute('maxVal', (string) $filterRule["maxVal"]); } } } // Check for dynamic filters if ($filterColumn->top10) { $column->setFilterType(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER); // We should only ever have one top10 filter foreach ($filterColumn->top10 as $filterRule) { $column->createRule()->setRule(isset($filterRule["percent"]) && $filterRule["percent"] == 1 ? \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT : \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE, (string) $filterRule["val"], isset($filterRule["top"]) && $filterRule["top"] == 1 ? \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP : \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM)->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_TOPTENFILTER); } } } } } if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->readDataOnly) { foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) { $mergeRef = (string) $mergeCell["ref"]; if (strpos($mergeRef, ':') !== false) { $docSheet->mergeCells((string) $mergeCell["ref"]); } } } if ($xmlSheet && $xmlSheet->pageMargins && !$this->readDataOnly) { $docPageMargins = $docSheet->getPageMargins(); $docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"])); $docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"])); $docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"])); $docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"])); $docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"])); $docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"])); } if ($xmlSheet && $xmlSheet->pageSetup && !$this->readDataOnly) { $docPageSetup = $docSheet->getPageSetup(); if (isset($xmlSheet->pageSetup["orientation"])) { $docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]); } if (isset($xmlSheet->pageSetup["paperSize"])) { $docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"])); } if (isset($xmlSheet->pageSetup["scale"])) { $docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), false); } if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) { $docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), false); } if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) { $docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), false); } if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) && self::boolean((string) $xmlSheet->pageSetup["useFirstPageNumber"])) { $docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"])); } } if ($xmlSheet && $xmlSheet->headerFooter && !$this->readDataOnly) { $docHeaderFooter = $docSheet->getHeaderFooter(); if (isset($xmlSheet->headerFooter["differentOddEven"]) && self::boolean((string) $xmlSheet->headerFooter["differentOddEven"])) { $docHeaderFooter->setDifferentOddEven(true); } else { $docHeaderFooter->setDifferentOddEven(false); } if (isset($xmlSheet->headerFooter["differentFirst"]) && self::boolean((string) $xmlSheet->headerFooter["differentFirst"])) { $docHeaderFooter->setDifferentFirst(true); } else { $docHeaderFooter->setDifferentFirst(false); } if (isset($xmlSheet->headerFooter["scaleWithDoc"]) && !self::boolean((string) $xmlSheet->headerFooter["scaleWithDoc"])) { $docHeaderFooter->setScaleWithDocument(false); } else { $docHeaderFooter->setScaleWithDocument(true); } if (isset($xmlSheet->headerFooter["alignWithMargins"]) && !self::boolean((string) $xmlSheet->headerFooter["alignWithMargins"])) { $docHeaderFooter->setAlignWithMargins(false); } else { $docHeaderFooter->setAlignWithMargins(true); } $docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader); $docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter); $docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader); $docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter); $docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader); $docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter); } if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->readDataOnly) { foreach ($xmlSheet->rowBreaks->brk as $brk) { if ($brk["man"]) { $docSheet->setBreak("A{$brk['id']}", \PHPExcel\Worksheet::BREAK_ROW); } } } if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->readDataOnly) { foreach ($xmlSheet->colBreaks->brk as $brk) { if ($brk["man"]) { $docSheet->setBreak(\PHPExcel\Cell::stringFromColumnIndex((string) $brk["id"]) . "1", \PHPExcel\Worksheet::BREAK_COLUMN); } } } if ($xmlSheet && $xmlSheet->dataValidations && !$this->readDataOnly) { foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) { // Uppercase coordinate $range = strtoupper($dataValidation["sqref"]); $rangeSet = explode(' ', $range); foreach ($rangeSet as $range) { $stRange = $docSheet->shrinkRangeToFit($range); // Extract all cell references in $range foreach (\PHPExcel\Cell::extractAllCellReferencesInRange($stRange) as $reference) { // Create validation $docValidation = $docSheet->getCell($reference)->getDataValidation(); $docValidation->setType((string) $dataValidation["type"]); $docValidation->setErrorStyle((string) $dataValidation["errorStyle"]); $docValidation->setOperator((string) $dataValidation["operator"]); $docValidation->setAllowBlank($dataValidation["allowBlank"] != 0); $docValidation->setShowDropDown($dataValidation["showDropDown"] == 0); $docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0); $docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0); $docValidation->setErrorTitle((string) $dataValidation["errorTitle"]); $docValidation->setError((string) $dataValidation["error"]); $docValidation->setPromptTitle((string) $dataValidation["promptTitle"]); $docValidation->setPrompt((string) $dataValidation["prompt"]); $docValidation->setFormula1((string) $dataValidation->formula1); $docValidation->setFormula2((string) $dataValidation->formula2); } } } } // Add hyperlinks $hyperlinks = array(); if (!$this->readDataOnly) { // Locate hyperlink relations if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") { $hyperlinks[(string) $ele["Id"]] = (string) $ele["Target"]; } } } // Loop through hyperlinks if ($xmlSheet && $xmlSheet->hyperlinks) { foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) { // Link url $linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships'); foreach (\PHPExcel\Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) { $cell = $docSheet->getCell($cellReference); if (isset($linkRel['id'])) { $hyperlinkUrl = $hyperlinks[(string) $linkRel['id']]; if (isset($hyperlink['location'])) { $hyperlinkUrl .= '#' . (string) $hyperlink['location']; } $cell->getHyperlink()->setUrl($hyperlinkUrl); } elseif (isset($hyperlink['location'])) { $cell->getHyperlink()->setUrl('sheet://' . (string) $hyperlink['location']); } // Tooltip if (isset($hyperlink['tooltip'])) { $cell->getHyperlink()->setTooltip((string) $hyperlink['tooltip']); } } } } } // Add comments $comments = array(); $vmlComments = array(); if (!$this->readDataOnly) { // Locate comment relations if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") { $comments[(string) $ele["Id"]] = (string) $ele["Target"]; } if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") { $vmlComments[(string) $ele["Id"]] = (string) $ele["Target"]; } } } // Loop through comments foreach ($comments as $relName => $relPath) { // Load comments file $relPath = \PHPExcel\Shared\File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath); $commentsFile = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $relPath)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); // Utility variables $authors = array(); // Loop through authors foreach ($commentsFile->authors->author as $author) { $authors[] = (string) $author; } // Loop through contents foreach ($commentsFile->commentList->comment as $comment) { if (!empty($comment['authorId'])) { $docSheet->getComment((string) $comment['ref'])->setAuthor($authors[(string) $comment['authorId']]); } $docSheet->getComment((string) $comment['ref'])->setText($this->parseRichText($comment->text)); } } // Loop through VML comments foreach ($vmlComments as $relName => $relPath) { // Load VML comments file $relPath = \PHPExcel\Shared\File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath); $vmlCommentsFile = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $relPath)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); $shapes = $vmlCommentsFile->xpath('//v:shape'); foreach ($shapes as $shape) { $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); if (isset($shape['style'])) { $style = (string) $shape['style']; $fillColor = strtoupper(substr((string) $shape['fillcolor'], 1)); $column = null; $row = null; $clientData = $shape->xpath('.//x:ClientData'); if (is_array($clientData) && !empty($clientData)) { $clientData = $clientData[0]; if (isset($clientData['ObjectType']) && (string) $clientData['ObjectType'] == 'Note') { $temp = $clientData->xpath('.//x:Row'); if (is_array($temp)) { $row = $temp[0]; } $temp = $clientData->xpath('.//x:Column'); if (is_array($temp)) { $column = $temp[0]; } } } if ($column !== null && $row !== null) { // Set comment properties $comment = $docSheet->getCommentByColumnAndRow((string) $column, $row + 1); $comment->getFillColor()->setRGB($fillColor); // Parse style $styleArray = explode(';', str_replace(' ', '', $style)); foreach ($styleArray as $stylePair) { $stylePair = explode(':', $stylePair); if ($stylePair[0] == 'margin-left') { $comment->setMarginLeft($stylePair[1]); } if ($stylePair[0] == 'margin-top') { $comment->setMarginTop($stylePair[1]); } if ($stylePair[0] == 'width') { $comment->setWidth($stylePair[1]); } if ($stylePair[0] == 'height') { $comment->setHeight($stylePair[1]); } if ($stylePair[0] == 'visibility') { $comment->setVisible($stylePair[1] == 'visible'); } } } } } } // Header/footer images if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->readDataOnly) { if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $vmlRelationship = ''; foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") { $vmlRelationship = self::dirAdd("{$dir}/{$fileWorksheet}", $ele["Target"]); } } if ($vmlRelationship != '') { // Fetch linked images $relsVML = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels')), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $drawings = array(); foreach ($relsVML->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") { $drawings[(string) $ele["Id"]] = self::dirAdd($vmlRelationship, $ele["Target"]); } } // Fetch VML document $vmlDrawing = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $vmlRelationship)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); $hfImages = array(); $shapes = $vmlDrawing->xpath('//v:shape'); foreach ($shapes as $idx => $shape) { $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); $imageData = $shape->xpath('//v:imagedata'); $imageData = $imageData[$idx]; $imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office'); $style = self::toCSSArray((string) $shape['style']); $hfImages[(string) $shape['id']] = new \PHPExcel\Worksheet\HeaderFooterDrawing(); if (isset($imageData['title'])) { $hfImages[(string) $shape['id']]->setName((string) $imageData['title']); } $hfImages[(string) $shape['id']]->setPath("zip://" . \PHPExcel\Shared_File::realpath($pFilename) . "#" . $drawings[(string) $imageData['relid']], false); $hfImages[(string) $shape['id']]->setResizeProportional(false); $hfImages[(string) $shape['id']]->setWidth($style['width']); $hfImages[(string) $shape['id']]->setHeight($style['height']); if (isset($style['margin-left'])) { $hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']); } $hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']); $hfImages[(string) $shape['id']]->setResizeProportional(true); } $docSheet->getHeaderFooter()->setImages($hfImages); } } } } // TODO: Autoshapes from twoCellAnchors! if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $drawings = array(); foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") { $drawings[(string) $ele["Id"]] = self::dirAdd("{$dir}/{$fileWorksheet}", $ele["Target"]); } } if ($xmlSheet->drawing && !$this->readDataOnly) { foreach ($xmlSheet->drawing as $drawing) { $fileDrawing = $drawings[(string) self::getArrayItem($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")]; $relsDrawing = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $images = array(); if ($relsDrawing && $relsDrawing->Relationship) { foreach ($relsDrawing->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") { $images[(string) $ele["Id"]] = self::dirAdd($fileDrawing, $ele["Target"]); } elseif ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart") { if ($this->includeCharts) { $charts[self::dirAdd($fileDrawing, $ele["Target"])] = array('id' => (string) $ele["Id"], 'sheet' => $docSheet->getTitle()); } } } } $xmlDrawing = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $fileDrawing)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions())->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); if ($xmlDrawing->oneCellAnchor) { foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) { if ($oneCellAnchor->pic->blipFill) { $blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip; $xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm; $outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw; $objDrawing = new \PHPExcel\Worksheet\Drawing(); $objDrawing->setName((string) self::getArrayItem($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name")); $objDrawing->setDescription((string) self::getArrayItem($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr")); $objDrawing->setPath("zip://" . \PHPExcel\Shared\File::realpath($pFilename) . "#" . $images[(string) self::getArrayItem($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false); $objDrawing->setCoordinates(\PHPExcel\Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1)); $objDrawing->setOffsetX(\PHPExcel\Shared\Drawing::EMUToPixels($oneCellAnchor->from->colOff)); $objDrawing->setOffsetY(\PHPExcel\Shared\Drawing::EMUToPixels($oneCellAnchor->from->rowOff)); $objDrawing->setResizeProportional(false); $objDrawing->setWidth(\PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($oneCellAnchor->ext->attributes(), "cx"))); $objDrawing->setHeight(\PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($oneCellAnchor->ext->attributes(), "cy"))); if ($xfrm) { $objDrawing->setRotation(\PHPExcel\Shared\Drawing::angleToDegrees(self::getArrayItem($xfrm->attributes(), "rot"))); } if ($outerShdw) { $shadow = $objDrawing->getShadow(); $shadow->setVisible(true); $shadow->setBlurRadius(\PHPExcel\Shared\Drawing::EMUTopixels(self::getArrayItem($outerShdw->attributes(), "blurRad"))); $shadow->setDistance(\PHPExcel\Shared\Drawing::EMUTopixels(self::getArrayItem($outerShdw->attributes(), "dist"))); $shadow->setDirection(\PHPExcel\Shared\Drawing::angleToDegrees(self::getArrayItem($outerShdw->attributes(), "dir"))); $shadow->setAlignment((string) self::getArrayItem($outerShdw->attributes(), "algn")); $shadow->getColor()->setRGB(self::getArrayItem($outerShdw->srgbClr->attributes(), "val")); $shadow->setAlpha(self::getArrayItem($outerShdw->srgbClr->alpha->attributes(), "val") / 1000); } $objDrawing->setWorksheet($docSheet); } else { // ? Can charts be positioned with a oneCellAnchor ? $coordinates = \PHPExcel\Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1); $offsetX = \PHPExcel\Shared\Drawing::EMUToPixels($oneCellAnchor->from->colOff); $offsetY = \PHPExcel\Shared\Drawing::EMUToPixels($oneCellAnchor->from->rowOff); $width = \PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($oneCellAnchor->ext->attributes(), "cx")); $height = \PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($oneCellAnchor->ext->attributes(), "cy")); } } } if ($xmlDrawing->twoCellAnchor) { foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) { if ($twoCellAnchor->pic->blipFill) { $blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip; $xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm; $outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw; $objDrawing = new \PHPExcel\Worksheet\Drawing(); $objDrawing->setName((string) self::getArrayItem($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name")); $objDrawing->setDescription((string) self::getArrayItem($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr")); $objDrawing->setPath("zip://" . \PHPExcel\Shared\File::realpath($pFilename) . "#" . $images[(string) self::getArrayItem($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false); $objDrawing->setCoordinates(\PHPExcel\Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1)); $objDrawing->setOffsetX(\PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->from->colOff)); $objDrawing->setOffsetY(\PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->from->rowOff)); $objDrawing->setResizeProportional(false); if ($xfrm) { $objDrawing->setWidth(\PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($xfrm->ext->attributes(), "cx"))); $objDrawing->setHeight(\PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($xfrm->ext->attributes(), "cy"))); $objDrawing->setRotation(\PHPExcel\Shared\Drawing::angleToDegrees(self::getArrayItem($xfrm->attributes(), "rot"))); } if ($outerShdw) { $shadow = $objDrawing->getShadow(); $shadow->setVisible(true); $shadow->setBlurRadius(\PHPExcel\Shared\Drawing::EMUTopixels(self::getArrayItem($outerShdw->attributes(), "blurRad"))); $shadow->setDistance(\PHPExcel\Shared\Drawing::EMUTopixels(self::getArrayItem($outerShdw->attributes(), "dist"))); $shadow->setDirection(\PHPExcel\Shared\Drawing::angleToDegrees(self::getArrayItem($outerShdw->attributes(), "dir"))); $shadow->setAlignment((string) self::getArrayItem($outerShdw->attributes(), "algn")); $shadow->getColor()->setRGB(self::getArrayItem($outerShdw->srgbClr->attributes(), "val")); $shadow->setAlpha(self::getArrayItem($outerShdw->srgbClr->alpha->attributes(), "val") / 1000); } $objDrawing->setWorksheet($docSheet); } elseif ($this->includeCharts && $twoCellAnchor->graphicFrame) { $fromCoordinate = \PHPExcel\Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1); $fromOffsetX = \PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->from->colOff); $fromOffsetY = \PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->from->rowOff); $toCoordinate = \PHPExcel\Cell::stringFromColumnIndex((string) $twoCellAnchor->to->col) . ($twoCellAnchor->to->row + 1); $toOffsetX = \PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->to->colOff); $toOffsetY = \PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->to->rowOff); $graphic = $twoCellAnchor->graphicFrame->children("http://schemas.openxmlformats.org/drawingml/2006/main")->graphic; $chartRef = $graphic->graphicData->children("http://schemas.openxmlformats.org/drawingml/2006/chart")->chart; $thisChart = (string) $chartRef->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"); $chartDetails[$docSheet->getTitle() . '!' . $thisChart] = array('fromCoordinate' => $fromCoordinate, 'fromOffsetX' => $fromOffsetX, 'fromOffsetY' => $fromOffsetY, 'toCoordinate' => $toCoordinate, 'toOffsetX' => $toOffsetX, 'toOffsetY' => $toOffsetY, 'worksheetTitle' => $docSheet->getTitle()); } } } } } } // Loop through definedNames if ($xmlWorkbook->definedNames) { foreach ($xmlWorkbook->definedNames->definedName as $definedName) { // Extract range $extractedRange = (string) $definedName; $extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange); if (($spos = strpos($extractedRange, '!')) !== false) { $extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos)); } else { $extractedRange = str_replace('$', '', $extractedRange); } // Valid range? if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') { continue; } // Some definedNames are only applicable if we are on the same sheet... if ((string) $definedName['localSheetId'] != '' && (string) $definedName['localSheetId'] == $sheetId) { // Switch on type switch ((string) $definedName['name']) { case '_xlnm._FilterDatabase': if ((string) $definedName['hidden'] !== '1') { $extractedRange = explode(',', $extractedRange); foreach ($extractedRange as $range) { $autoFilterRange = $range; if (strpos($autoFilterRange, ':') !== false) { $docSheet->getAutoFilter()->setRange($autoFilterRange); } } } break; case '_xlnm.Print_Titles': // Split $extractedRange $extractedRange = explode(',', $extractedRange); // Set print titles foreach ($extractedRange as $range) { $matches = array(); $range = str_replace('$', '', $range); // check for repeating columns, e g. 'A:A' or 'A:D' if (preg_match('/!?([A-Z]+)\\:([A-Z]+)$/', $range, $matches)) { $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2])); } elseif (preg_match('/!?(\\d+)\\:(\\d+)$/', $range, $matches)) { // check for repeating rows, e.g. '1:1' or '1:5' $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2])); } } break; case '_xlnm.Print_Area': $rangeSets = explode(',', $extractedRange); // FIXME: what if sheetname contains comma? $newRangeSets = array(); foreach ($rangeSets as $rangeSet) { $range = explode('!', $rangeSet); // FIXME: what if sheetname contains exclamation mark? $rangeSet = isset($range[1]) ? $range[1] : $range[0]; if (strpos($rangeSet, ':') === false) { $rangeSet = $rangeSet . ':' . $rangeSet; } $newRangeSets[] = str_replace('$', '', $rangeSet); } $docSheet->getPageSetup()->setPrintArea(implode(',', $newRangeSets)); break; default: break; } } } } // Next sheet id ++$sheetId; } // Loop through definedNames if ($xmlWorkbook->definedNames) { foreach ($xmlWorkbook->definedNames->definedName as $definedName) { // Extract range $extractedRange = (string) $definedName; $extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange); if (($spos = strpos($extractedRange, '!')) !== false) { $extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos)); } else { $extractedRange = str_replace('$', '', $extractedRange); } // Valid range? if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') { continue; } // Some definedNames are only applicable if we are on the same sheet... if ((string) $definedName['localSheetId'] != '') { // Local defined name // Switch on type switch ((string) $definedName['name']) { case '_xlnm._FilterDatabase': case '_xlnm.Print_Titles': case '_xlnm.Print_Area': break; default: if ($mapSheetId[(int) $definedName['localSheetId']] !== null) { $range = explode('!', (string) $definedName); if (count($range) == 2) { $range[0] = str_replace("''", "'", $range[0]); $range[0] = str_replace("'", "", $range[0]); if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) { $extractedRange = str_replace('$', '', $range[1]); $scope = $docSheet->getParent()->getSheet($mapSheetId[(int) $definedName['localSheetId']]); $excel->addNamedRange(new \PHPExcel\NamedRange((string) $definedName['name'], $worksheet, $extractedRange, true, $scope)); } } } break; } } elseif (!isset($definedName['localSheetId'])) { // "Global" definedNames $locatedSheet = null; $extractedSheetName = ''; if (strpos((string) $definedName, '!') !== false) { // Extract sheet name $extractedSheetName = \PHPExcel\Worksheet::extractSheetTitle((string) $definedName, true); $extractedSheetName = $extractedSheetName[0]; // Locate sheet $locatedSheet = $excel->getSheetByName($extractedSheetName); // Modify range $range = explode('!', $extractedRange); $extractedRange = isset($range[1]) ? $range[1] : $range[0]; } if ($locatedSheet !== null) { $excel->addNamedRange(new \PHPExcel\NamedRange((string) $definedName['name'], $locatedSheet, $extractedRange, false)); } } } } } if (!$this->readDataOnly || !empty($this->loadSheetsOnly)) { // active sheet index $activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]); // refers to old sheet index // keep active sheet index if sheet is still loaded, else first sheet is set as the active if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) { $excel->setActiveSheetIndex($mapSheetId[$activeTab]); } else { if ($excel->getSheetCount() == 0) { $excel->createSheet(); } $excel->setActiveSheetIndex(0); } } break; } } if (!$this->readDataOnly) { $contentTypes = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "[Content_Types].xml")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); foreach ($contentTypes->Override as $contentType) { switch ($contentType["ContentType"]) { case "application/vnd.openxmlformats-officedocument.drawingml.chart+xml": if ($this->includeCharts) { $chartEntryRef = ltrim($contentType['PartName'], '/'); $chartElements = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $chartEntryRef)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $objChart = \PHPExcel\Reader\Excel2007\Chart::readChart($chartElements, basename($chartEntryRef, '.xml')); // echo 'Chart ', $chartEntryRef, '<br />'; // var_dump($charts[$chartEntryRef]); // if (isset($charts[$chartEntryRef])) { $chartPositionRef = $charts[$chartEntryRef]['sheet'] . '!' . $charts[$chartEntryRef]['id']; // echo 'Position Ref ', $chartPositionRef, '<br />'; if (isset($chartDetails[$chartPositionRef])) { // var_dump($chartDetails[$chartPositionRef]); $excel->getSheetByName($charts[$chartEntryRef]['sheet'])->addChart($objChart); $objChart->setWorksheet($excel->getSheetByName($charts[$chartEntryRef]['sheet'])); $objChart->setTopLeftPosition($chartDetails[$chartPositionRef]['fromCoordinate'], $chartDetails[$chartPositionRef]['fromOffsetX'], $chartDetails[$chartPositionRef]['fromOffsetY']); $objChart->setBottomRightPosition($chartDetails[$chartPositionRef]['toCoordinate'], $chartDetails[$chartPositionRef]['toOffsetX'], $chartDetails[$chartPositionRef]['toOffsetY']); } } } } } } $zip->close(); return $excel; }
/** * Convert 1-cell anchor coordinates to 2-cell anchor coordinates * This function is ported from PEAR Spreadsheet_Writer_Excel with small modifications * * Calculate the vertices that define the position of the image as required by * the OBJ record. * * +------------+------------+ * | A | B | * +-----+------------+------------+ * | |(x1,y1) | | * | 1 |(A1)._______|______ | * | | | | | * | | | | | * +-----+----| BITMAP |-----+ * | | | | | * | 2 | |______________. | * | | | (B2)| * | | | (x2,y2)| * +---- +------------+------------+ * * Example of a bitmap that covers some of the area from cell A1 to cell B2. * * Based on the width and height of the bitmap we need to calculate 8 vars: * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2. * The width and height of the cells are also variable and have to be taken into * account. * The values of $col_start and $row_start are passed in from the calling * function. The values of $col_end and $row_end are calculated by subtracting * the width and height of the bitmap from the width and height of the * underlying cells. * The vertices are expressed as a percentage of the underlying cell width as * follows (rhs values are in pixels): * * x1 = X / W *1024 * y1 = Y / H *256 * x2 = (X-1) / W *1024 * y2 = (Y-1) / H *256 * * Where: X is distance from the left side of the underlying cell * Y is distance from the top of the underlying cell * W is the width of the cell * H is the height of the cell * * @param \PHPExcel\Worksheet $sheet * @param string $coordinates E.g. 'A1' * @param integer $offsetX Horizontal offset in pixels * @param integer $offsetY Vertical offset in pixels * @param integer $width Width in pixels * @param integer $height Height in pixels * @return array */ public static function oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height) { list($column, $row) = \PHPExcel\Cell::coordinateFromString($coordinates); $col_start = \PHPExcel\Cell::columnIndexFromString($column) - 1; $row_start = $row - 1; $x1 = $offsetX; $y1 = $offsetY; // Initialise end cell to the same as the start cell $col_end = $col_start; // Col containing lower right corner of object $row_end = $row_start; // Row containing bottom right corner of object // Zero the specified offset if greater than the cell dimensions if ($x1 >= self::sizeCol($sheet, \PHPExcel\Cell::stringFromColumnIndex($col_start))) { $x1 = 0; } if ($y1 >= self::sizeRow($sheet, $row_start + 1)) { $y1 = 0; } $width = $width + $x1 - 1; $height = $height + $y1 - 1; // Subtract the underlying cell widths to find the end cell of the image while ($width >= self::sizeCol($sheet, \PHPExcel\Cell::stringFromColumnIndex($col_end))) { $width -= self::sizeCol($sheet, \PHPExcel\Cell::stringFromColumnIndex($col_end)); ++$col_end; } // Subtract the underlying cell heights to find the end cell of the image while ($height >= self::sizeRow($sheet, $row_end + 1)) { $height -= self::sizeRow($sheet, $row_end + 1); ++$row_end; } // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell // with zero height or width. if (self::sizeCol($sheet, \PHPExcel\Cell::stringFromColumnIndex($col_start)) == 0) { return; } if (self::sizeCol($sheet, \PHPExcel\Cell::stringFromColumnIndex($col_end)) == 0) { return; } if (self::sizeRow($sheet, $row_start + 1) == 0) { return; } if (self::sizeRow($sheet, $row_end + 1) == 0) { return; } // Convert the pixel values to the percentage value expected by Excel $x1 = $x1 / self::sizeCol($sheet, \PHPExcel\Cell::stringFromColumnIndex($col_start)) * 1024; $y1 = $y1 / self::sizeRow($sheet, $row_start + 1) * 256; $x2 = ($width + 1) / self::sizeCol($sheet, \PHPExcel\Cell::stringFromColumnIndex($col_end)) * 1024; // Distance to right side of object $y2 = ($height + 1) / self::sizeRow($sheet, $row_end + 1) * 256; // Distance to bottom of object $startCoordinates = \PHPExcel\Cell::stringFromColumnIndex($col_start) . ($row_start + 1); $endCoordinates = \PHPExcel\Cell::stringFromColumnIndex($col_end) . ($row_end + 1); $twoAnchor = array('startCoordinates' => $startCoordinates, 'startOffsetX' => $x1, 'startOffsetY' => $y1, 'endCoordinates' => $endCoordinates, 'endOffsetX' => $x2, 'endOffsetY' => $y2); return $twoAnchor; }
/** * Calculate information about HTML colspan and rowspan which is not always the same as Excel's */ private function calculateSpans() { // Identify all cells that should be omitted in HTML due to cell merge. // In HTML only the upper-left cell should be written and it should have // appropriate rowspan / colspan attribute $sheetIndexes = $this->sheetIndex !== null ? array($this->sheetIndex) : range(0, $this->phpExcel->getSheetCount() - 1); foreach ($sheetIndexes as $sheetIndex) { $sheet = $this->phpExcel->getSheet($sheetIndex); $candidateSpannedRow = array(); // loop through all Excel merged cells foreach ($sheet->getMergeCells() as $cells) { list($cells, ) = \PHPExcel\Cell::splitRange($cells); $first = $cells[0]; $last = $cells[1]; list($fc, $fr) = \PHPExcel\Cell::coordinateFromString($first); $fc = \PHPExcel\Cell::columnIndexFromString($fc) - 1; list($lc, $lr) = \PHPExcel\Cell::coordinateFromString($last); $lc = \PHPExcel\Cell::columnIndexFromString($lc) - 1; // loop through the individual cells in the individual merge $r = $fr - 1; while ($r++ < $lr) { // also, flag this row as a HTML row that is candidate to be omitted $candidateSpannedRow[$r] = $r; $c = $fc - 1; while ($c++ < $lc) { if (!($c == $fc && $r == $fr)) { // not the upper-left cell (should not be written in HTML) $this->isSpannedCell[$sheetIndex][$r][$c] = array('baseCell' => array($fr, $fc)); } else { // upper-left is the base cell that should hold the colspan/rowspan attribute $this->isBaseCell[$sheetIndex][$r][$c] = array('xlrowspan' => $lr - $fr + 1, 'rowspan' => $lr - $fr + 1, 'xlcolspan' => $lc - $fc + 1, 'colspan' => $lc - $fc + 1); } } } } // Identify which rows should be omitted in HTML. These are the rows where all the cells // participate in a merge and the where base cells are somewhere above. $countColumns = \PHPExcel\Cell::columnIndexFromString($sheet->getHighestColumn()); foreach ($candidateSpannedRow as $rowIndex) { if (isset($this->isSpannedCell[$sheetIndex][$rowIndex])) { if (count($this->isSpannedCell[$sheetIndex][$rowIndex]) == $countColumns) { $this->isSpannedRow[$sheetIndex][$rowIndex] = $rowIndex; } } } // For each of the omitted rows we found above, the affected rowspans should be subtracted by 1 if (isset($this->isSpannedRow[$sheetIndex])) { foreach ($this->isSpannedRow[$sheetIndex] as $rowIndex) { $adjustedBaseCells = array(); $c = -1; $e = $countColumns - 1; while ($c++ < $e) { $baseCell = $this->isSpannedCell[$sheetIndex][$rowIndex][$c]['baseCell']; if (!in_array($baseCell, $adjustedBaseCells)) { // subtract rowspan by 1 --$this->isBaseCell[$sheetIndex][$baseCell[0]][$baseCell[1]]['rowspan']; $adjustedBaseCells[] = $baseCell; } } } } // TODO: Same for columns } // We have calculated the spans $this->spansAreCalculated = true; }
/** * Write SheetData * * @param \PHPExcel\Shared\XMLWriter $objWriter XML Writer * @param \PHPExcel\Worksheet $pSheet Worksheet * @param string[] $pStringTable String table * @throws \PHPExcel\Writer\Exception */ private function writeSheetData(\PHPExcel\Shared\XMLWriter $objWriter = null, \PHPExcel\Worksheet $pSheet = null, $pStringTable = null) { if (is_array($pStringTable)) { // Flipped stringtable, for faster index searching $aFlippedStringTable = $this->getParentWriter()->getWriterPart('stringtable')->flipStringTable($pStringTable); // sheetData $objWriter->startElement('sheetData'); // Get column count $colCount = \PHPExcel\Cell::columnIndexFromString($pSheet->getHighestColumn()); // Highest row number $highestRow = $pSheet->getHighestRow(); // Loop through cells $cellsByRow = array(); foreach ($pSheet->getCellCollection() as $cellID) { $cellAddress = \PHPExcel\Cell::coordinateFromString($cellID); $cellsByRow[$cellAddress[1]][] = $cellID; } $currentRow = 0; while ($currentRow++ < $highestRow) { // Get row dimension $rowDimension = $pSheet->getRowDimension($currentRow); // Write current row? $writeCurrentRow = isset($cellsByRow[$currentRow]) || $rowDimension->getRowHeight() >= 0 || $rowDimension->getVisible() == false || $rowDimension->getCollapsed() == true || $rowDimension->getOutlineLevel() > 0 || $rowDimension->getXfIndex() !== null; if ($writeCurrentRow) { // Start a new row $objWriter->startElement('row'); $objWriter->writeAttribute('r', $currentRow); $objWriter->writeAttribute('spans', '1:' . $colCount); // Row dimensions if ($rowDimension->getRowHeight() >= 0) { $objWriter->writeAttribute('customHeight', '1'); $objWriter->writeAttribute('ht', \PHPExcel\Shared\StringHelper::formatNumber($rowDimension->getRowHeight())); } // Row visibility if ($rowDimension->getVisible() == false) { $objWriter->writeAttribute('hidden', 'true'); } // Collapsed if ($rowDimension->getCollapsed() == true) { $objWriter->writeAttribute('collapsed', 'true'); } // Outline level if ($rowDimension->getOutlineLevel() > 0) { $objWriter->writeAttribute('outlineLevel', $rowDimension->getOutlineLevel()); } // Style if ($rowDimension->getXfIndex() !== null) { $objWriter->writeAttribute('s', $rowDimension->getXfIndex()); $objWriter->writeAttribute('customFormat', '1'); } // Write cells if (isset($cellsByRow[$currentRow])) { foreach ($cellsByRow[$currentRow] as $cellAddress) { // Write cell $this->writeCell($objWriter, $pSheet, $cellAddress, $pStringTable, $aFlippedStringTable); } } // End row $objWriter->endElement(); } } $objWriter->endElement(); } else { throw new \PHPExcel\Writer\Exception("Invalid parameters passed."); } }
/** * Process the object to be written */ public function close() { // initialize $this->data = ''; switch (get_class($this->object)) { case '\\PHPExcel\\Shared\\Escher': if ($dggContainer = $this->object->getDggContainer()) { $writer = new Escher($dggContainer); $this->data = $writer->close(); } elseif ($dgContainer = $this->object->getDgContainer()) { $writer = new Escher($dgContainer); $this->data = $writer->close(); $this->spOffsets = $writer->getSpOffsets(); $this->spTypes = $writer->getSpTypes(); } break; case '\\PHPExcel\\Shared\\Escher\\DggContainer': // this is a container record // initialize $innerData = ''; // write the dgg $recVer = 0x0; $recInstance = 0x0; $recType = 0xf006; $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; // dgg data $dggData = pack('VVVV', $this->object->getSpIdMax(), $this->object->getCDgSaved() + 1, $this->object->getCSpSaved(), $this->object->getCDgSaved()); // add file identifier clusters (one per drawing) $IDCLs = $this->object->getIDCLs(); foreach ($IDCLs as $dgId => $maxReducedSpId) { $dggData .= pack('VV', $dgId, $maxReducedSpId + 1); } $header = pack('vvV', $recVerInstance, $recType, strlen($dggData)); $innerData .= $header . $dggData; // write the bstoreContainer if ($bstoreContainer = $this->object->getBstoreContainer()) { $writer = new Escher($bstoreContainer); $innerData .= $writer->close(); } // write the record $recVer = 0xf; $recInstance = 0x0; $recType = 0xf000; $length = strlen($innerData); $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $this->data = $header . $innerData; break; case '\\PHPExcel\\Shared\\Escher\\DggContainer\\BstoreContainer': // this is a container record // initialize $innerData = ''; // treat the inner data if ($BSECollection = $this->object->getBSECollection()) { foreach ($BSECollection as $BSE) { $writer = new Escher($BSE); $innerData .= $writer->close(); } } // write the record $recVer = 0xf; $recInstance = count($this->object->getBSECollection()); $recType = 0xf001; $length = strlen($innerData); $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $this->data = $header . $innerData; break; case '\\PHPExcel\\Shared\\Escher\\DggContainer\\BstoreContainer\\BSE': // this is a semi-container record // initialize $innerData = ''; // here we treat the inner data if ($blip = $this->object->getBlip()) { $writer = new Escher($blip); $innerData .= $writer->close(); } // initialize $data = ''; $btWin32 = $this->object->getBlipType(); $btMacOS = $this->object->getBlipType(); $data .= pack('CC', $btWin32, $btMacOS); $rgbUid = pack('VVVV', 0, 0, 0, 0); // todo $data .= $rgbUid; $tag = 0; $size = strlen($innerData); $cRef = 1; $foDelay = 0; //todo $unused1 = 0x0; $cbName = 0x0; $unused2 = 0x0; $unused3 = 0x0; $data .= pack('vVVVCCCC', $tag, $size, $cRef, $foDelay, $unused1, $cbName, $unused2, $unused3); $data .= $innerData; // write the record $recVer = 0x2; $recInstance = $this->object->getBlipType(); $recType = 0xf007; $length = strlen($data); $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $this->data = $header; $this->data .= $data; break; case '\\PHPExcel\\Shared\\Escher\\DggContainer\\BstoreContainer\\BSE\\Blip': // this is an atom record // write the record switch ($this->object->getParent()->getBlipType()) { case \PHPExcel\Shared\Escher\DggContainer\BstoreContainer\BSE::BLIPTYPE_JPEG: // initialize $innerData = ''; $rgbUid1 = pack('VVVV', 0, 0, 0, 0); // todo $innerData .= $rgbUid1; $tag = 0xff; // todo $innerData .= pack('C', $tag); $innerData .= $this->object->getData(); $recVer = 0x0; $recInstance = 0x46a; $recType = 0xf01d; $length = strlen($innerData); $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $this->data = $header; $this->data .= $innerData; break; case \PHPExcel\Shared\Escher\DggContainer\BstoreContainer\BSE::BLIPTYPE_PNG: // initialize $innerData = ''; $rgbUid1 = pack('VVVV', 0, 0, 0, 0); // todo $innerData .= $rgbUid1; $tag = 0xff; // todo $innerData .= pack('C', $tag); $innerData .= $this->object->getData(); $recVer = 0x0; $recInstance = 0x6e0; $recType = 0xf01e; $length = strlen($innerData); $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $this->data = $header; $this->data .= $innerData; break; } break; case '\\PHPExcel\\Shared\\Escher\\DgContainer': // this is a container record // initialize $innerData = ''; // write the dg $recVer = 0x0; $recInstance = $this->object->getDgId(); $recType = 0xf008; $length = 8; $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); // number of shapes in this drawing (including group shape) $countShapes = count($this->object->getSpgrContainer()->getChildren()); $innerData .= $header . pack('VV', $countShapes, $this->object->getLastSpId()); //$innerData .= $header . pack('VV', 0, 0); // write the spgrContainer if ($spgrContainer = $this->object->getSpgrContainer()) { $writer = new Escher($spgrContainer); $innerData .= $writer->close(); // get the shape offsets relative to the spgrContainer record $spOffsets = $writer->getSpOffsets(); $spTypes = $writer->getSpTypes(); // save the shape offsets relative to dgContainer foreach ($spOffsets as &$spOffset) { $spOffset += 24; // add length of dgContainer header data (8 bytes) plus dg data (16 bytes) } $this->spOffsets = $spOffsets; $this->spTypes = $spTypes; } // write the record $recVer = 0xf; $recInstance = 0x0; $recType = 0xf002; $length = strlen($innerData); $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $this->data = $header . $innerData; break; case '\\PHPExcel\\Shared\\Escher\\DgContainer\\SpgrContainer': // this is a container record // initialize $innerData = ''; // initialize spape offsets $totalSize = 8; $spOffsets = array(); $spTypes = array(); // treat the inner data foreach ($this->object->getChildren() as $spContainer) { $writer = new Escher($spContainer); $spData = $writer->close(); $innerData .= $spData; // save the shape offsets (where new shape records begin) $totalSize += strlen($spData); $spOffsets[] = $totalSize; $spTypes = array_merge($spTypes, $writer->getSpTypes()); } // write the record $recVer = 0xf; $recInstance = 0x0; $recType = 0xf003; $length = strlen($innerData); $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $this->data = $header . $innerData; $this->spOffsets = $spOffsets; $this->spTypes = $spTypes; break; case '\\PHPExcel\\Shared\\Escher\\DgContainer\\SpgrContainer\\SpContainer': // initialize $data = ''; // build the data // write group shape record, if necessary? if ($this->object->getSpgr()) { $recVer = 0x1; $recInstance = 0x0; $recType = 0xf009; $length = 0x10; $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $data .= $header . pack('VVVV', 0, 0, 0, 0); } $this->spTypes[] = $this->object->getSpType(); // write the shape record $recVer = 0x2; $recInstance = $this->object->getSpType(); // shape type $recType = 0xf00a; $length = 0x8; $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $data .= $header . pack('VV', $this->object->getSpId(), $this->object->getSpgr() ? 0x5 : 0xa00); // the options if ($this->object->getOPTCollection()) { $optData = ''; $recVer = 0x3; $recInstance = count($this->object->getOPTCollection()); $recType = 0xf00b; foreach ($this->object->getOPTCollection() as $property => $value) { $optData .= pack('vV', $property, $value); } $length = strlen($optData); $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $data .= $header . $optData; } // the client anchor if ($this->object->getStartCoordinates()) { $clientAnchorData = ''; $recVer = 0x0; $recInstance = 0x0; $recType = 0xf010; // start coordinates list($column, $row) = \PHPExcel\Cell::coordinateFromString($this->object->getStartCoordinates()); $c1 = \PHPExcel\Cell::columnIndexFromString($column) - 1; $r1 = $row - 1; // start offsetX $startOffsetX = $this->object->getStartOffsetX(); // start offsetY $startOffsetY = $this->object->getStartOffsetY(); // end coordinates list($column, $row) = \PHPExcel\Cell::coordinateFromString($this->object->getEndCoordinates()); $c2 = \PHPExcel\Cell::columnIndexFromString($column) - 1; $r2 = $row - 1; // end offsetX $endOffsetX = $this->object->getEndOffsetX(); // end offsetY $endOffsetY = $this->object->getEndOffsetY(); $clientAnchorData = pack('vvvvvvvvv', $this->object->getSpFlag(), $c1, $startOffsetX, $r1, $startOffsetY, $c2, $endOffsetX, $r2, $endOffsetY); $length = strlen($clientAnchorData); $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $data .= $header . $clientAnchorData; } // the client data, just empty for now if (!$this->object->getSpgr()) { $clientDataData = ''; $recVer = 0x0; $recInstance = 0x0; $recType = 0xf011; $length = strlen($clientDataData); $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $data .= $header . $clientDataData; } // write the record $recVer = 0xf; $recInstance = 0x0; $recType = 0xf004; $length = strlen($data); $recVerInstance = $recVer; $recVerInstance |= $recInstance << 4; $header = pack('vvV', $recVerInstance, $recType, $length); $this->data = $header . $data; break; } return $this->data; }