/** * Create a new row iterator * * @param \PHPExcel\Worksheet $subject The worksheet to iterate over * @param string $columnIndex The column that we want to iterate * @param integer $startRow The row number at which to start iterating * @param integer $endRow Optionally, the row number at which to stop iterating */ public function __construct(\PHPExcel\Worksheet $subject = null, $columnIndex = 'A', $startRow = 1, $endRow = null) { // Set subject $this->subject = $subject; $this->columnIndex = \PHPExcel\Cell::columnIndexFromString($columnIndex) - 1; $this->resetEnd($endRow); $this->resetStart($startRow); }
/** * Loads PHPExcel from file into PHPExcel instance * * @param string $pFilename * @param PHPExcel $objPHPExcel * @return PHPExcel * @throws Exception */ public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) { $fromFormats = array('\\-', '\\ '); $toFormats = array('-', ' '); $underlineStyles = array(\PHPExcel\Style\Font::UNDERLINE_NONE, \PHPExcel\Style\Font::UNDERLINE_DOUBLE, \PHPExcel\Style\Font::UNDERLINE_DOUBLEACCOUNTING, \PHPExcel\Style\Font::UNDERLINE_SINGLE, \PHPExcel\Style\Font::UNDERLINE_SINGLEACCOUNTING); $verticalAlignmentStyles = array(\PHPExcel\Style\Alignment::VERTICAL_BOTTOM, \PHPExcel\Style\Alignment::VERTICAL_TOP, \PHPExcel\Style\Alignment::VERTICAL_CENTER, \PHPExcel\Style\Alignment::VERTICAL_JUSTIFY); $horizontalAlignmentStyles = array(\PHPExcel\Style\Alignment::HORIZONTAL_GENERAL, \PHPExcel\Style\Alignment::HORIZONTAL_LEFT, \PHPExcel\Style\Alignment::HORIZONTAL_RIGHT, \PHPExcel\Style\Alignment::HORIZONTAL_CENTER, \PHPExcel\Style\Alignment::HORIZONTAL_CENTER_CONTINUOUS, \PHPExcel\Style\Alignment::HORIZONTAL_JUSTIFY); $timezoneObj = new \DateTimeZone('Europe/London'); $GMT = new \DateTimeZone('UTC'); // Check if file exists if (!file_exists($pFilename)) { throw new Exception("Could not open " . $pFilename . " for reading! File does not exist."); } if (!$this->canRead($pFilename)) { throw new Exception($pFilename . " is an Invalid Spreadsheet file."); } $xml = simplexml_load_string($this->securityScan(file_get_contents($pFilename)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $namespaces = $xml->getNamespaces(true); $docProps = $objPHPExcel->getProperties(); if (isset($xml->DocumentProperties[0])) { foreach ($xml->DocumentProperties[0] as $propertyName => $propertyValue) { switch ($propertyName) { case 'Title': $docProps->setTitle(self::convertStringEncoding($propertyValue, $this->charSet)); break; case 'Subject': $docProps->setSubject(self::convertStringEncoding($propertyValue, $this->charSet)); break; case 'Author': $docProps->setCreator(self::convertStringEncoding($propertyValue, $this->charSet)); break; case 'Created': $creationDate = strtotime($propertyValue); $docProps->setCreated($creationDate); break; case 'LastAuthor': $docProps->setLastModifiedBy(self::convertStringEncoding($propertyValue, $this->charSet)); break; case 'LastSaved': $lastSaveDate = strtotime($propertyValue); $docProps->setModified($lastSaveDate); break; case 'Company': $docProps->setCompany(self::convertStringEncoding($propertyValue, $this->charSet)); break; case 'Category': $docProps->setCategory(self::convertStringEncoding($propertyValue, $this->charSet)); break; case 'Manager': $docProps->setManager(self::convertStringEncoding($propertyValue, $this->charSet)); break; case 'Keywords': $docProps->setKeywords(self::convertStringEncoding($propertyValue, $this->charSet)); break; case 'Description': $docProps->setDescription(self::convertStringEncoding($propertyValue, $this->charSet)); break; } } } if (isset($xml->CustomDocumentProperties)) { foreach ($xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) { $propertyAttributes = $propertyValue->attributes($namespaces['dt']); $propertyName = preg_replace_callback('/_x([0-9a-z]{4})_/', '\\PHPExcel\\Reader\\Excel2003XML::hex2str', $propertyName); $propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_UNKNOWN; switch ((string) $propertyAttributes) { case 'string': $propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_STRING; $propertyValue = trim($propertyValue); break; case 'boolean': $propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_BOOLEAN; $propertyValue = (bool) $propertyValue; break; case 'integer': $propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_INTEGER; $propertyValue = intval($propertyValue); break; case 'float': $propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_FLOAT; $propertyValue = floatval($propertyValue); break; case 'dateTime.tz': $propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_DATE; $propertyValue = strtotime(trim($propertyValue)); break; } $docProps->setCustomProperty($propertyName, $propertyValue, $propertyType); } } foreach ($xml->Styles[0] as $style) { $style_ss = $style->attributes($namespaces['ss']); $styleID = (string) $style_ss['ID']; // echo 'Style ID = '.$styleID.'<br />'; $this->styles[$styleID] = isset($this->styles['Default']) ? $this->styles['Default'] : array(); foreach ($style as $styleType => $styleData) { $styleAttributes = $styleData->attributes($namespaces['ss']); // echo $styleType.'<br />'; switch ($styleType) { case 'Alignment': foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; $styleAttributeValue = (string) $styleAttributeValue; switch ($styleAttributeKey) { case 'Vertical': if (self::identifyFixedStyleValue($verticalAlignmentStyles, $styleAttributeValue)) { $this->styles[$styleID]['alignment']['vertical'] = $styleAttributeValue; } break; case 'Horizontal': if (self::identifyFixedStyleValue($horizontalAlignmentStyles, $styleAttributeValue)) { $this->styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue; } break; case 'WrapText': $this->styles[$styleID]['alignment']['wrap'] = true; break; } } break; case 'Borders': foreach ($styleData->Border as $borderStyle) { $borderAttributes = $borderStyle->attributes($namespaces['ss']); $thisBorder = array(); foreach ($borderAttributes as $borderStyleKey => $borderStyleValue) { // echo $borderStyleKey.' = '.$borderStyleValue.'<br />'; switch ($borderStyleKey) { case 'LineStyle': $thisBorder['style'] = \PHPExcel\Style\Border::BORDER_MEDIUM; // $thisBorder['style'] = $borderStyleValue; break; case 'Weight': // $thisBorder['style'] = $borderStyleValue; break; case 'Position': $borderPosition = strtolower($borderStyleValue); break; case 'Color': $borderColour = substr($borderStyleValue, 1); $thisBorder['color']['rgb'] = $borderColour; break; } } if (!empty($thisBorder)) { if ($borderPosition == 'left' || $borderPosition == 'right' || $borderPosition == 'top' || $borderPosition == 'bottom') { $this->styles[$styleID]['borders'][$borderPosition] = $thisBorder; } } } break; case 'Font': foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; $styleAttributeValue = (string) $styleAttributeValue; switch ($styleAttributeKey) { case 'FontName': $this->styles[$styleID]['font']['name'] = $styleAttributeValue; break; case 'Size': $this->styles[$styleID]['font']['size'] = $styleAttributeValue; break; case 'Color': $this->styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue, 1); break; case 'Bold': $this->styles[$styleID]['font']['bold'] = true; break; case 'Italic': $this->styles[$styleID]['font']['italic'] = true; break; case 'Underline': if (self::identifyFixedStyleValue($underlineStyles, $styleAttributeValue)) { $this->styles[$styleID]['font']['underline'] = $styleAttributeValue; } break; } } break; case 'Interior': foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; switch ($styleAttributeKey) { case 'Color': $this->styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue, 1); break; } } break; case 'NumberFormat': foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; $styleAttributeValue = str_replace($fromFormats, $toFormats, $styleAttributeValue); switch ($styleAttributeValue) { case 'Short Date': $styleAttributeValue = 'dd/mm/yyyy'; break; } if ($styleAttributeValue > '') { $this->styles[$styleID]['numberformat']['code'] = $styleAttributeValue; } } break; case 'Protection': foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; } break; } } // print_r($this->styles[$styleID]); // echo '<hr />'; } // echo '<hr />'; $worksheetID = 0; $xml_ss = $xml->children($namespaces['ss']); foreach ($xml_ss->Worksheet as $worksheet) { $worksheet_ss = $worksheet->attributes($namespaces['ss']); if (isset($this->loadSheetsOnly) && isset($worksheet_ss['Name']) && !in_array($worksheet_ss['Name'], $this->loadSheetsOnly)) { continue; } // echo '<h3>Worksheet: ', $worksheet_ss['Name'],'<h3>'; // // Create new Worksheet $objPHPExcel->createSheet(); $objPHPExcel->setActiveSheetIndex($worksheetID); if (isset($worksheet_ss['Name'])) { $worksheetName = self::convertStringEncoding((string) $worksheet_ss['Name'], $this->charSet); // 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 $objPHPExcel->getActiveSheet()->setTitle($worksheetName, false); } $columnID = 'A'; if (isset($worksheet->Table->Column)) { foreach ($worksheet->Table->Column as $columnData) { $columnData_ss = $columnData->attributes($namespaces['ss']); if (isset($columnData_ss['Index'])) { $columnID = \PHPExcel\Cell::stringFromColumnIndex($columnData_ss['Index'] - 1); } if (isset($columnData_ss['Width'])) { $columnWidth = $columnData_ss['Width']; // echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />'; $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4); } ++$columnID; } } $rowID = 1; if (isset($worksheet->Table->Row)) { $additionalMergedCells = 0; foreach ($worksheet->Table->Row as $rowData) { $rowHasData = false; $row_ss = $rowData->attributes($namespaces['ss']); if (isset($row_ss['Index'])) { $rowID = (int) $row_ss['Index']; } // echo '<b>Row '.$rowID.'</b><br />'; $columnID = 'A'; foreach ($rowData->Cell as $cell) { $cell_ss = $cell->attributes($namespaces['ss']); if (isset($cell_ss['Index'])) { $columnID = \PHPExcel\Cell::stringFromColumnIndex($cell_ss['Index'] - 1); } $cellRange = $columnID . $rowID; if ($this->getReadFilter() !== null) { if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { continue; } } if (isset($cell_ss['MergeAcross']) || isset($cell_ss['MergeDown'])) { $columnTo = $columnID; if (isset($cell_ss['MergeAcross'])) { $additionalMergedCells += (int) $cell_ss['MergeAcross']; $columnTo = \PHPExcel\Cell::stringFromColumnIndex(\PHPExcel\Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] - 1); } $rowTo = $rowID; if (isset($cell_ss['MergeDown'])) { $rowTo = $rowTo + $cell_ss['MergeDown']; } $cellRange .= ':' . $columnTo . $rowTo; $objPHPExcel->getActiveSheet()->mergeCells($cellRange); } $cellIsSet = $hasCalculatedValue = false; $cellDataFormula = ''; if (isset($cell_ss['Formula'])) { $cellDataFormula = $cell_ss['Formula']; // added this as a check for array formulas if (isset($cell_ss['ArrayRange'])) { $cellDataCSEFormula = $cell_ss['ArrayRange']; // echo "found an array formula at ".$columnID.$rowID."<br />"; } $hasCalculatedValue = true; } if (isset($cell->Data)) { $cellValue = $cellData = $cell->Data; $type = \PHPExcel\Cell_DataType::TYPE_NULL; $cellData_ss = $cellData->attributes($namespaces['ss']); if (isset($cellData_ss['Type'])) { $cellDataType = $cellData_ss['Type']; switch ($cellDataType) { /* const TYPE_STRING = 's'; const TYPE_FORMULA = 'f'; const TYPE_NUMERIC = 'n'; const TYPE_BOOL = 'b'; const TYPE_NULL = 'null'; const TYPE_INLINE = 'inlineStr'; const TYPE_ERROR = 'e'; */ case 'String': $cellValue = self::convertStringEncoding($cellValue, $this->charSet); $type = \PHPExcel\Cell\DataType::TYPE_STRING; break; case 'Number': $type = \PHPExcel\Cell\DataType::TYPE_NUMERIC; $cellValue = (double) $cellValue; if (floor($cellValue) == $cellValue) { $cellValue = (int) $cellValue; } break; case 'Boolean': $type = \PHPExcel\Cell\DataType::TYPE_BOOL; $cellValue = $cellValue != 0; break; case 'DateTime': $type = \PHPExcel\Cell\DataType::TYPE_NUMERIC; $cellValue = \PHPExcel\Shared\Date::PHPToExcel(strtotime($cellValue)); break; case 'Error': $type = \PHPExcel\Cell\DataType::TYPE_ERROR; break; } } if ($hasCalculatedValue) { // echo 'FORMULA<br />'; $type = \PHPExcel\Cell\DataType::TYPE_FORMULA; $columnNumber = \PHPExcel\Cell::columnIndexFromString($columnID); if (substr($cellDataFormula, 0, 3) == 'of:') { $cellDataFormula = substr($cellDataFormula, 3); // echo 'Before: ', $cellDataFormula,'<br />'; $temp = explode('"', $cellDataFormula); $key = false; foreach ($temp as &$value) { // Only replace in alternate array entries (i.e. non-quoted blocks) if ($key = !$key) { $value = str_replace(array('[.', '.', ']'), '', $value); } } } else { // Convert R1C1 style references to A1 style references (but only when not quoted) // echo 'Before: ', $cellDataFormula,'<br />'; $temp = explode('"', $cellDataFormula); $key = false; foreach ($temp as &$value) { // Only replace in alternate array entries (i.e. non-quoted blocks) if ($key = !$key) { preg_match_all('/(R(\\[?-?\\d*\\]?))(C(\\[?-?\\d*\\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE); // Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way // through the formula from left to right. Reversing means that we work right to left.through // the formula $cellReferences = array_reverse($cellReferences); // Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent, // then modify the formula to use that new reference foreach ($cellReferences as $cellReference) { $rowReference = $cellReference[2][0]; // Empty R reference is the current row if ($rowReference == '') { $rowReference = $rowID; } // Bracketed R references are relative to the current row if ($rowReference[0] == '[') { $rowReference = $rowID + trim($rowReference, '[]'); } $columnReference = $cellReference[4][0]; // Empty C reference is the current column if ($columnReference == '') { $columnReference = $columnNumber; } // Bracketed C references are relative to the current column if ($columnReference[0] == '[') { $columnReference = $columnNumber + trim($columnReference, '[]'); } $A1CellReference = \PHPExcel\Cell::stringFromColumnIndex($columnReference - 1) . $rowReference; $value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0])); } } } } unset($value); // Then rebuild the formula string $cellDataFormula = implode('"', $temp); // echo 'After: ', $cellDataFormula,'<br />'; } // echo 'Cell '.$columnID.$rowID.' is a '.$type.' with a value of '.(($hasCalculatedValue) ? $cellDataFormula : $cellValue).'<br />'; // $objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $cellValue, $type); if ($hasCalculatedValue) { // echo 'Formula result is '.$cellValue.'<br />'; $objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue); } $cellIsSet = $rowHasData = true; } if (isset($cell->Comment)) { // echo '<b>comment found</b><br />'; $commentAttributes = $cell->Comment->attributes($namespaces['ss']); $author = 'unknown'; if (isset($commentAttributes->Author)) { $author = (string) $commentAttributes->Author; // echo 'Author: ', $author,'<br />'; } $node = $cell->Comment->Data->asXML(); // $annotation = str_replace('html:','',substr($node,49,-10)); // echo $annotation,'<br />'; $annotation = strip_tags($node); // echo 'Annotation: ', $annotation,'<br />'; $objPHPExcel->getActiveSheet()->getComment($columnID . $rowID)->setAuthor(self::convertStringEncoding($author, $this->charSet))->setText($this->parseRichText($annotation)); } if ($cellIsSet && isset($cell_ss['StyleID'])) { $style = (string) $cell_ss['StyleID']; // echo 'Cell style for '.$columnID.$rowID.' is '.$style.'<br />'; if (isset($this->styles[$style]) && !empty($this->styles[$style])) { // echo 'Cell '.$columnID.$rowID.'<br />'; // print_r($this->styles[$style]); // echo '<br />'; if (!$objPHPExcel->getActiveSheet()->cellExists($columnID . $rowID)) { $objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValue(null); } $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->styles[$style]); } } ++$columnID; while ($additionalMergedCells > 0) { ++$columnID; $additionalMergedCells--; } } if ($rowHasData) { if (isset($row_ss['StyleID'])) { $rowStyle = $row_ss['StyleID']; } if (isset($row_ss['Height'])) { $rowHeight = $row_ss['Height']; // echo '<b>Setting row height to '.$rowHeight.'</b><br />'; $objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight); } } ++$rowID; } } ++$worksheetID; } // Return return $objPHPExcel; }
/** * Validate that the specified column is in the AutoFilter range * * @param string $column Column name (e.g. A) * @throws \PHPExcel\Exception * @return integer The column offset within the autofilter range */ public function testColumnInRange($column) { if (empty($this->range)) { throw new \PHPExcel\Exception("No autofilter range is defined."); } $columnIndex = \PHPExcel\Cell::columnIndexFromString($column); list($rangeStart, $rangeEnd) = \PHPExcel\Cell::rangeBoundaries($this->range); if ($rangeStart[0] > $columnIndex || $rangeEnd[0] < $columnIndex) { throw new \PHPExcel\Exception("Column is outside of current autofilter range."); } return $columnIndex - $rangeStart[0]; }
/** * 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 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."); } }
/** * 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; }
/** * 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 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."); } }
private function castToFormula($c, $r, &$cellDataType, &$value, &$calculatedValue, &$sharedFormulas, $castBaseType) { // echo 'Formula', PHP_EOL; // echo '$c->f is ', $c->f, PHP_EOL; $cellDataType = 'f'; $value = "={$c->f}"; $calculatedValue = self::$castBaseType($c); // Shared formula? if (isset($c->f['t']) && strtolower((string) $c->f['t']) == 'shared') { // echo 'SHARED FORMULA', PHP_EOL; $instance = (string) $c->f['si']; // echo 'Instance ID = ', $instance, PHP_EOL; // // echo 'Shared Formula Array:', PHP_EOL; // print_r($sharedFormulas); if (!isset($sharedFormulas[(string) $c->f['si']])) { // echo 'SETTING NEW SHARED FORMULA', PHP_EOL; // echo 'Master is ', $r, PHP_EOL; // echo 'Formula is ', $value, PHP_EOL; $sharedFormulas[$instance] = array('master' => $r, 'formula' => $value); // echo 'New Shared Formula Array:', PHP_EOL; // print_r($sharedFormulas); } else { // echo 'GETTING SHARED FORMULA', PHP_EOL; // echo 'Master is ', $sharedFormulas[$instance]['master'], PHP_EOL; // echo 'Formula is ', $sharedFormulas[$instance]['formula'], PHP_EOL; $master = \PHPExcel\Cell::coordinateFromString($sharedFormulas[$instance]['master']); $current = \PHPExcel\Cell::coordinateFromString($r); $difference = array(0, 0); $difference[0] = \PHPExcel\Cell::columnIndexFromString($current[0]) - \PHPExcel\Cell::columnIndexFromString($master[0]); $difference[1] = $current[1] - $master[1]; $value = $this->referenceHelper->updateFormulaReferences($sharedFormulas[$instance]['formula'], 'A1', $difference[0], $difference[1]); // echo 'Adjusted Formula is ', $value, PHP_EOL; } } }
/** * Set the column pointer to the selected column * * @param string $column The column address to set the current pointer at * @return ColumnIterator * @throws \PHPExcel\Exception */ public function seek($column = 'A') { $column = \PHPExcel\Cell::columnIndexFromString($column) - 1; if ($column < $this->startColumn || $column > $this->endColumn) { throw new \PHPExcel\Exception("Column {$column} is out of range ({$this->startColumn} - {$this->endColumn})"); } $this->position = $column; return $this; }
/** * 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; }
/** * Set the column pointer to the selected column * * @param string $column The column address to set the current pointer at * @return RowCellIterator * @throws \PHPExcel\Exception */ public function seek($column = 'A') { $column = \PHPExcel\Cell::columnIndexFromString($column) - 1; if ($column < $this->startColumn || $column > $this->endColumn) { throw new \PHPExcel\Exception("Column {$column} is out of range ({$this->startColumn} - {$this->endColumn})"); } elseif ($this->onlyExistingCells && !$this->subject->cellExistsByColumnAndRow($column, $this->rowIndex)) { throw new \PHPExcel\Exception('In "IterateOnlyExistingCells" mode and Cell does not exist'); } $this->position = $column; return $this; }
/** * 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(); }
/** * Loads PHPExcel from file into PHPExcel instance * * @param string $pFilename * @param \PHPExcel\Spreadsheet $objPHPExcel * @return \PHPExcel\Spreadsheet * @throws Exception */ public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) { // Check if file exists if (!file_exists($pFilename)) { throw new Exception("Could not open " . $pFilename . " for reading! File does not exist."); } $timezoneObj = new DateTimeZone('Europe/London'); $GMT = new \DateTimeZone('UTC'); $zipClass = \PHPExcel\Settings::getZipClass(); $zip = new $zipClass(); if (!$zip->open($pFilename)) { throw new Exception("Could not open " . $pFilename . " for reading! Error opening file."); } // echo '<h1>Meta Information</h1>'; $xml = simplexml_load_string($this->securityScan($zip->getFromName("meta.xml")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $namespacesMeta = $xml->getNamespaces(true); // echo '<pre>'; // print_r($namespacesMeta); // echo '</pre><hr />'; $docProps = $objPHPExcel->getProperties(); $officeProperty = $xml->children($namespacesMeta['office']); foreach ($officeProperty as $officePropertyData) { $officePropertyDC = array(); if (isset($namespacesMeta['dc'])) { $officePropertyDC = $officePropertyData->children($namespacesMeta['dc']); } foreach ($officePropertyDC as $propertyName => $propertyValue) { $propertyValue = (string) $propertyValue; switch ($propertyName) { case 'title': $docProps->setTitle($propertyValue); break; case 'subject': $docProps->setSubject($propertyValue); break; case 'creator': $docProps->setCreator($propertyValue); $docProps->setLastModifiedBy($propertyValue); break; case 'date': $creationDate = strtotime($propertyValue); $docProps->setCreated($creationDate); $docProps->setModified($creationDate); break; case 'description': $docProps->setDescription($propertyValue); break; } } $officePropertyMeta = array(); if (isset($namespacesMeta['dc'])) { $officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']); } foreach ($officePropertyMeta as $propertyName => $propertyValue) { $propertyValueAttributes = $propertyValue->attributes($namespacesMeta['meta']); $propertyValue = (string) $propertyValue; switch ($propertyName) { case 'initial-creator': $docProps->setCreator($propertyValue); break; case 'keyword': $docProps->setKeywords($propertyValue); break; case 'creation-date': $creationDate = strtotime($propertyValue); $docProps->setCreated($creationDate); break; case 'user-defined': $propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_STRING; foreach ($propertyValueAttributes as $key => $value) { if ($key == 'name') { $propertyValueName = (string) $value; } elseif ($key == 'value-type') { switch ($value) { case 'date': $propertyValue = \PHPExcel\Document\Properties::convertProperty($propertyValue, 'date'); $propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_DATE; break; case 'boolean': $propertyValue = \PHPExcel\Document\Properties::convertProperty($propertyValue, 'bool'); $propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_BOOLEAN; break; case 'float': $propertyValue = \PHPExcel\Document\Properties::convertProperty($propertyValue, 'r4'); $propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_FLOAT; break; default: $propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_STRING; } } } $docProps->setCustomProperty($propertyValueName, $propertyValue, $propertyValueType); break; } } } // echo '<h1>Workbook Content</h1>'; $xml = simplexml_load_string($this->securityScan($zip->getFromName("content.xml")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions()); $namespacesContent = $xml->getNamespaces(true); // echo '<pre>'; // print_r($namespacesContent); // echo '</pre><hr />'; $workbook = $xml->children($namespacesContent['office']); foreach ($workbook->body->spreadsheet as $workbookData) { $workbookData = $workbookData->children($namespacesContent['table']); $worksheetID = 0; foreach ($workbookData->table as $worksheetDataSet) { $worksheetData = $worksheetDataSet->children($namespacesContent['table']); // print_r($worksheetData); // echo '<br />'; $worksheetDataAttributes = $worksheetDataSet->attributes($namespacesContent['table']); // print_r($worksheetDataAttributes); // echo '<br />'; if (isset($this->loadSheetsOnly) && isset($worksheetDataAttributes['name']) && !in_array($worksheetDataAttributes['name'], $this->loadSheetsOnly)) { continue; } // echo '<h2>Worksheet '.$worksheetDataAttributes['name'].'</h2>'; // Create new Worksheet $objPHPExcel->createSheet(); $objPHPExcel->setActiveSheetIndex($worksheetID); if (isset($worksheetDataAttributes['name'])) { $worksheetName = (string) $worksheetDataAttributes['name']; // 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 $objPHPExcel->getActiveSheet()->setTitle($worksheetName, false); } $rowID = 1; foreach ($worksheetData as $key => $rowData) { // echo '<b>'.$key.'</b><br />'; switch ($key) { case 'table-header-rows': foreach ($rowData as $keyRowData => $cellData) { $rowData = $cellData; break; } break; case 'table-row': $rowDataTableAttributes = $rowData->attributes($namespacesContent['table']); $rowRepeats = isset($rowDataTableAttributes['number-rows-repeated']) ? $rowDataTableAttributes['number-rows-repeated'] : 1; $columnID = 'A'; foreach ($rowData as $key => $cellData) { if ($this->getReadFilter() !== null) { if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { continue; } } // echo '<b>'.$columnID.$rowID.'</b><br />'; $cellDataText = isset($namespacesContent['text']) ? $cellData->children($namespacesContent['text']) : ''; $cellDataOffice = $cellData->children($namespacesContent['office']); $cellDataOfficeAttributes = $cellData->attributes($namespacesContent['office']); $cellDataTableAttributes = $cellData->attributes($namespacesContent['table']); // echo 'Office Attributes: '; // print_r($cellDataOfficeAttributes); // echo '<br />Table Attributes: '; // print_r($cellDataTableAttributes); // echo '<br />Cell Data Text'; // print_r($cellDataText); // echo '<br />'; // $type = $formatting = $hyperlink = null; $hasCalculatedValue = false; $cellDataFormula = ''; if (isset($cellDataTableAttributes['formula'])) { $cellDataFormula = $cellDataTableAttributes['formula']; $hasCalculatedValue = true; } if (isset($cellDataOffice->annotation)) { // echo 'Cell has comment<br />'; $annotationText = $cellDataOffice->annotation->children($namespacesContent['text']); $textArray = array(); foreach ($annotationText as $t) { if (isset($t->span)) { foreach ($t->span as $text) { $textArray[] = (string) $text; } } else { $textArray[] = (string) $t; } } $text = implode("\n", $textArray); // echo $text, '<br />'; $objPHPExcel->getActiveSheet()->getComment($columnID . $rowID)->setText($this->parseRichText($text)); // ->setAuthor( $author ) } if (isset($cellDataText->p)) { // Consolidate if there are multiple p records (maybe with spans as well) $dataArray = array(); // Text can have multiple text:p and within those, multiple text:span. // text:p newlines, but text:span does not. // Also, here we assume there is no text data is span fields are specified, since // we have no way of knowing proper positioning anyway. foreach ($cellDataText->p as $pData) { if (isset($pData->span)) { // span sections do not newline, so we just create one large string here $spanSection = ""; foreach ($pData->span as $spanData) { $spanSection .= $spanData; } array_push($dataArray, $spanSection); } else { array_push($dataArray, $pData); } } $allCellDataText = implode($dataArray, "\n"); // echo 'Value Type is '.$cellDataOfficeAttributes['value-type'].'<br />'; switch ($cellDataOfficeAttributes['value-type']) { case 'string': $type = \PHPExcel\Cell\DataType::TYPE_STRING; $dataValue = $allCellDataText; if (isset($dataValue->a)) { $dataValue = $dataValue->a; $cellXLinkAttributes = $dataValue->attributes($namespacesContent['xlink']); $hyperlink = $cellXLinkAttributes['href']; } break; case 'boolean': $type = \PHPExcel\Cell\DataType::TYPE_BOOL; $dataValue = $allCellDataText == 'TRUE' ? true : false; break; case 'percentage': $type = \PHPExcel\Cell\DataType::TYPE_NUMERIC; $dataValue = (double) $cellDataOfficeAttributes['value']; if (floor($dataValue) == $dataValue) { $dataValue = (int) $dataValue; } $formatting = \PHPExcel\Style\NumberFormat::FORMAT_PERCENTAGE_00; break; case 'currency': $type = \PHPExcel\Cell\DataType::TYPE_NUMERIC; $dataValue = (double) $cellDataOfficeAttributes['value']; if (floor($dataValue) == $dataValue) { $dataValue = (int) $dataValue; } $formatting = \PHPExcel\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE; break; case 'float': $type = \PHPExcel\Cell\DataType::TYPE_NUMERIC; $dataValue = (double) $cellDataOfficeAttributes['value']; if (floor($dataValue) == $dataValue) { if ($dataValue == (int) $dataValue) { $dataValue = (int) $dataValue; } else { $dataValue = (double) $dataValue; } } break; case 'date': $type = \PHPExcel\Cell\DataType::TYPE_NUMERIC; $dateObj = new DateTime($cellDataOfficeAttributes['date-value'], $GMT); $dateObj->setTimeZone($timezoneObj); list($year, $month, $day, $hour, $minute, $second) = explode(' ', $dateObj->format('Y m d H i s')); $dataValue = \PHPExcel\Shared\Date::formattedPHPToExcel($year, $month, $day, $hour, $minute, $second); if ($dataValue != floor($dataValue)) { $formatting = \PHPExcel\Style\NumberFormat::FORMAT_DATE_XLSX15 . ' ' . \PHPExcel\Style\NumberFormat::FORMAT_DATE_TIME4; } else { $formatting = \PHPExcel\Style\NumberFormat::FORMAT_DATE_XLSX15; } break; case 'time': $type = \PHPExcel\Cell\DataType::TYPE_NUMERIC; $dataValue = \PHPExcel\Shared\Date::PHPToExcel(strtotime('01-01-1970 ' . implode(':', sscanf($cellDataOfficeAttributes['time-value'], 'PT%dH%dM%dS')))); $formatting = \PHPExcel\Style\NumberFormat::FORMAT_DATE_TIME4; break; } // echo 'Data value is '.$dataValue.'<br />'; // if ($hyperlink !== null) { // echo 'Hyperlink is '.$hyperlink.'<br />'; // } } else { $type = \PHPExcel\Cell\DataType::TYPE_NULL; $dataValue = null; } if ($hasCalculatedValue) { $type = \PHPExcel\Cell\DataType::TYPE_FORMULA; // echo 'Formula: ', $cellDataFormula, PHP_EOL; $cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1); $temp = explode('"', $cellDataFormula); $tKey = false; foreach ($temp as &$value) { // Only replace in alternate array entries (i.e. non-quoted blocks) if ($tKey = !$tKey) { $value = preg_replace('/\\[([^\\.]+)\\.([^\\.]+):\\.([^\\.]+)\\]/Ui', '$1!$2:$3', $value); // Cell range reference in another sheet $value = preg_replace('/\\[([^\\.]+)\\.([^\\.]+)\\]/Ui', '$1!$2', $value); // Cell reference in another sheet $value = preg_replace('/\\[\\.([^\\.]+):\\.([^\\.]+)\\]/Ui', '$1:$2', $value); // Cell range reference $value = preg_replace('/\\[\\.([^\\.]+)\\]/Ui', '$1', $value); // Simple cell reference $value = \PHPExcel\Calculation::translateSeparator(';', ',', $value, $inBraces); } } unset($value); // Then rebuild the formula string $cellDataFormula = implode('"', $temp); // echo 'Adjusted Formula: ', $cellDataFormula, PHP_EOL; } $colRepeats = isset($cellDataTableAttributes['number-columns-repeated']) ? $cellDataTableAttributes['number-columns-repeated'] : 1; if ($type !== null) { for ($i = 0; $i < $colRepeats; ++$i) { if ($i > 0) { ++$columnID; } if ($type !== \PHPExcel\Cell\DataType::TYPE_NULL) { for ($rowAdjust = 0; $rowAdjust < $rowRepeats; ++$rowAdjust) { $rID = $rowID + $rowAdjust; $objPHPExcel->getActiveSheet()->getCell($columnID . $rID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $dataValue, $type); if ($hasCalculatedValue) { // echo 'Forumla result is '.$dataValue.'<br />'; $objPHPExcel->getActiveSheet()->getCell($columnID . $rID)->setCalculatedValue($dataValue); } if ($formatting !== null) { $objPHPExcel->getActiveSheet()->getStyle($columnID . $rID)->getNumberFormat()->setFormatCode($formatting); } else { $objPHPExcel->getActiveSheet()->getStyle($columnID . $rID)->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_GENERAL); } if ($hyperlink !== null) { $objPHPExcel->getActiveSheet()->getCell($columnID . $rID)->getHyperlink()->setUrl($hyperlink); } } } } } // Merged cells if (isset($cellDataTableAttributes['number-columns-spanned']) || isset($cellDataTableAttributes['number-rows-spanned'])) { if ($type !== \PHPExcel\Cell\DataType::TYPE_NULL || !$this->readDataOnly) { $columnTo = $columnID; if (isset($cellDataTableAttributes['number-columns-spanned'])) { $columnTo = \PHPExcel\Cell::stringFromColumnIndex(\PHPExcel\Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-spanned'] - 2); } $rowTo = $rowID; if (isset($cellDataTableAttributes['number-rows-spanned'])) { $rowTo = $rowTo + $cellDataTableAttributes['number-rows-spanned'] - 1; } $cellRange = $columnID . $rowID . ':' . $columnTo . $rowTo; $objPHPExcel->getActiveSheet()->mergeCells($cellRange); } } ++$columnID; } $rowID += $rowRepeats; break; } } ++$worksheetID; } } // Return return $objPHPExcel; }
/** * Get highest worksheet column * * @param string $row Return the highest column for the specified row, * or the highest column of any row if no row number is passed * @return string Highest column name */ public function getHighestColumn($row = null) { if ($row == null) { $colRow = $this->getHighestRowAndColumn(); return $colRow['column']; } $columnList = array(1); foreach ($this->getCellList() as $coord) { sscanf($coord, '%[A-Z]%d', $c, $r); if ($r != $row) { continue; } $columnList[] = \PHPExcel\Cell::columnIndexFromString($c); } return \PHPExcel\Cell::stringFromColumnIndex(max($columnList) - 1); }
/** * 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; }