/** * */ protected function _initExcel() { $this->_excel = new PHPExcel(); $this->_excel->setActiveSheetIndex(0); $this->_mainSheet = $this->_excel->getActiveSheet(); $styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_NONE, 'color' => array('argb' => 'FFFFFFFF'))), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFFFF')), 'font' => array('name' => 'Calibri', 'size' => 10)); $this->_mainSheet->getDefaultStyle()->applyFromArray($styleArray); $this->_mainSheet->getColumnDimension('A')->setVisible(false); }
/** * Insert a new column, updating all possible related data * * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to insert * @param int $pNumRows Number of rows to insert * @throws Exception */ public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null) { // Get a copy of the cell collection /*$aTemp = $pSheet->getCellCollection(); $aCellCollection = array(); foreach ($aTemp as $key => $value) { $aCellCollection[$key] = clone $value; }*/ $aCellCollection = $pSheet->getCellCollection(); // Get coordinates of $pBefore $beforeColumn = 'A'; $beforeRow = 1; list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString( $pBefore ); // Remove cell styles? $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols < 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) { for ($i = 1; $i <= $highestRow - 1; $i++) { $pSheet->duplicateStyle( new PHPExcel_Style(), (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols ) . $i) . ':' . (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 ) . $i) ); } } if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) { for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; $i++) { $pSheet->duplicateStyle( new PHPExcel_Style(), (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow + $pNumRows)) . ':' . (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1)) ); } } // Loop trough cells, bottom-up, and change cell coordinates while ( ($cell = ($pNumCols < 0 || $pNumRows < 0) ? array_shift($aCellCollection) : array_pop($aCellCollection)) ) { // New coordinates $newCoordinates = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols ) . ($cell->getRow() + $pNumRows); // Should the cell be updated? if ( (PHPExcel_Cell::columnIndexFromString( $cell->getColumn() ) >= PHPExcel_Cell::columnIndexFromString($beforeColumn)) && ($cell->getRow() >= $beforeRow) ) { // Update cell styles $pSheet->duplicateStyle( $pSheet->getStyle($cell->getCoordinate()), $newCoordinates . ':' . $newCoordinates ); $pSheet->duplicateStyle( $pSheet->getDefaultStyle(), $cell->getCoordinate() . ':' . $cell->getCoordinate() ); // Insert this cell at its new location if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) { // Formula should be adjusted $pSheet->setCellValue( $newCoordinates , $this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows) ); } else { // Formula should not be adjusted $pSheet->setCellValue($newCoordinates, $cell->getValue()); } // Clear the original cell $pSheet->setCellValue($cell->getCoordinate(), ''); } } // Duplicate styles for the newly inserted cells $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols > 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 > 0) { for ($i = $beforeRow; $i <= $highestRow - 1; $i++) { // Style $pSheet->duplicateStyle( $pSheet->getStyle( (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 ) . $i) ), ($beforeColumn . $i) . ':' . (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols ) . $i) ); } } if ($pNumRows > 0 && $beforeRow - 1 > 0) { for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; $i++) { // Style $pSheet->duplicateStyle( $pSheet->getStyle( (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1)) ), (PHPExcel_Cell::stringFromColumnIndex($i) . $beforeRow) . ':' . (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1 + $pNumRows)) ); } } // Update worksheet: column dimensions $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true); foreach ($aColumnDimensions as $objColumnDimension) { $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows); list($newReference) = PHPExcel_Cell::coordinateFromString($newReference); if ($objColumnDimension->getColumnIndex() != $newReference) { $objColumnDimension->setColumnIndex($newReference); } } $pSheet->refreshColumnDimensions(); // Update worksheet: row dimensions $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true); foreach ($aRowDimensions as $objRowDimension) { $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows); list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference); if ($objRowDimension->getRowIndex() != $newReference) { $objRowDimension->setRowIndex($newReference); } } $pSheet->refreshRowDimensions(); $copyDimension = $pSheet->getRowDimension($beforeRow - 1); for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; $i++) { $newDimension = $pSheet->getRowDimension($i); $newDimension->setRowHeight($copyDimension->getRowHeight()); $newDimension->setVisible($copyDimension->getVisible()); $newDimension->setOutlineLevel($copyDimension->getOutlineLevel()); $newDimension->setCollapsed($copyDimension->getCollapsed()); } // Update worksheet: breaks $aBreaks = array_reverse($pSheet->getBreaks(), true); foreach ($aBreaks as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->setBreak( $newReference, $value ); $pSheet->setBreak( $key, PHPExcel_Worksheet::BREAK_NONE ); } } // Update worksheet: merge cells $aMergeCells = array_reverse($pSheet->getMergeCells(), true); foreach ($aMergeCells as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->mergeCells( $newReference ); $pSheet->unmergeCells( $key ); } } // Update worksheet: protected cells $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true); foreach ($aProtectedCells as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->protectCells( $newReference, $value, true ); $pSheet->unprotectCells( $key ); } } // Update worksheet: autofilter if ($pSheet->getAutoFilter() != '') { $pSheet->setAutoFilter( $this->updateCellReference($pSheet->getAutoFilter(), $pBefore, $pNumCols, $pNumRows) ); } // Update worksheet: freeze pane if ($pSheet->getFreezePane() != '') { $pSheet->setFreezePane( $this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows) ); } // Page setup if ($pSheet->getPageSetup()->isPrintAreaSet()) { $pSheet->getPageSetup()->setPrintArea( $this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows) ); } // Update worksheet: drawings $aDrawings = $pSheet->getDrawingCollection(); foreach ($aDrawings as $objDrawing) { $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows); if ($objDrawing->getCoordinates() != $newReference) { $objDrawing->setCoordinates($newReference); } } // Update workbook: named ranges if (count($pSheet->getParent()->getNamedRanges()) > 0) { foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) { if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) { $namedRange->setRange( $this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows) ); } } } // Garbage collect $pSheet->garbageCollect(); }
/** * Write Cell * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Worksheet $pSheet Worksheet * @param PHPExcel_Cell $pCell Cell * @param string[] $pStringTable String table * @param string[] $pFlippedStringTable String table (flipped), for faster index searching * @throws Exception */ private function _writeCell(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, PHPExcel_Cell $pCell = null, $pStringTable = null, $pFlippedStringTable = null) { if (is_array($pStringTable) && is_array($pFlippedStringTable)) { // Cell $objWriter->startElement('c'); $objWriter->writeAttribute('r', $pCell->getCoordinate()); // Sheet styles $aStyles = $pSheet->getStyles(); $styleIndex = ''; if (isset($aStyles[$pCell->getCoordinate()])) { $styleIndex = $aStyles[$pCell->getCoordinate()]->getHashIndex(); } else { $styleIndex = $pSheet->getDefaultStyle()->getHashIndex(); } if ($styleIndex != '') { $objWriter->writeAttribute('s', $styleIndex); } // If cell value is supplied, write cell value if (is_object($pCell->getValue()) || $pCell->getValue() !== '') { // Map type $mappedType = $pCell->getDataType(); // Write data type depending on its type switch (strtolower($mappedType)) { case 'inlinestr': // Inline string $objWriter->writeAttribute('t', $mappedType); break; case 's': // String $objWriter->writeAttribute('t', $mappedType); break; case 'b': // Boolean $objWriter->writeAttribute('t', $mappedType); break; case 'f': // Formula $calculatedValue = null; if ($this->getParentWriter()->getPreCalculateFormulas()) { $calculatedValue = $pCell->getCalculatedValue(); } else { $calculatedValue = $pCell->getValue(); } if (is_string($calculatedValue)) { $objWriter->writeAttribute('t', 'str'); } break; case 'e': // Error $objWriter->writeAttribute('t', $mappedType); } // Write data depending on its type switch (strtolower($mappedType)) { case 'inlinestr': // Inline string if (!$pCell->getValue() instanceof PHPExcel_RichText) { $objWriter->writeElement('t', PHPExcel_Shared_String::ControlCharacterPHP2OOXML(htmlspecialchars($pCell->getValue()))); } else { if ($pCell->getValue() instanceof PHPExcel_RichText) { $objWriter->startElement('is'); $this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $pCell->getValue()); $objWriter->endElement(); } } break; case 's': // String if (!$pCell->getValue() instanceof PHPExcel_RichText) { if (isset($pFlippedStringTable[$pCell->getValue()])) { $objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()]); } } else { if ($pCell->getValue() instanceof PHPExcel_RichText) { $objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()->getHashCode()]); } } break; case 'f': // Formula $objWriter->writeElement('f', substr($pCell->getValue(), 1)); if ($this->getParentWriter()->getOffice2003Compatibility() === false) { if ($this->getParentWriter()->getPreCalculateFormulas()) { $calculatedValue = $pCell->getCalculatedValue(); if (!is_array($calculatedValue) && substr($calculatedValue, 0, 1) != '#') { $objWriter->writeElement('v', $calculatedValue); } else { $objWriter->writeElement('v', '0'); } } else { $objWriter->writeElement('v', '0'); } } break; case 'n': // Numeric $objWriter->writeElement('v', $pCell->getValue()); break; case 'b': // Boolean $objWriter->writeElement('v', $pCell->getValue() ? '1' : '0'); break; case 'e': // Error if (substr($pCell->getValue(), 0, 1) == '=') { $objWriter->writeElement('f', substr($pCell->getValue(), 1)); $objWriter->writeElement('v', substr($pCell->getValue(), 1)); } else { $objWriter->writeElement('v', $pCell->getValue()); } break; } } $objWriter->endElement(); } else { throw new Exception("Invalid parameters passed."); } }
/** * Loads PHPExcel from file * * @param string $pFilename * @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 $this->_phpExcel = new PHPExcel(); $this->_phpExcel->removeSheetByIndex(0); // Use ParseXL for the hard work. $this->_ole = new PHPExcel_Shared_OLERead(); $this->_encoderFunction = function_exists('mb_convert_encoding') ? 'mb_convert_encoding' : 'iconv'; // get excel data $res = $this->_ole->read($pFilename); // oops, something goes wrong (Darko Miljanovic) if ($res === false) { // check error code if ($this->_ole->error == 1) { // bad file throw new Exception('The filename ' . $pFilename . ' is not readable'); } elseif ($this->_ole->error == 2) { throw new Exception('The filename ' . $pFilename . ' is not recognised as an Excel file'); } // check other error codes here (eg bad fileformat, etc...) } $this->_data = $this->_ole->getWorkBook(); // total byte size of Excel data (workbook global substream + sheet substreams) $this->_dataSize = strlen($this->_data); $this->_pos = 0; // Parse Workbook Global Substream while ($this->_pos < $this->_dataSize) { $code = $this->_GetInt2d($this->_data, $this->_pos); switch ($code) { case self::XLS_Type_BOF: $pos = $this->_pos; $length = $this->_GetInt2d($this->_data, $pos + 2); $recordData = substr($this->_data, $pos + 4, $length); // offset: 0; size: 2; BIFF version $this->_version = $this->_GetInt2d($this->_data, $pos + 4); if ($this->_version != self::XLS_BIFF8 && $this->_version != self::XLS_BIFF7) { return false; } // offset: 2; size: 2; type of stream $substreamType = $this->_GetInt2d($this->_data, $pos + 6); if ($substreamType != self::XLS_WorkbookGlobals) { return false; } $this->_pos += 4 + $length; break; case self::XLS_Type_FILEPASS: $this->_readFilepass(); break; case self::XLS_Type_CODEPAGE: $this->_readCodepage(); break; case self::XLS_Type_DATEMODE: $this->_readDateMode(); break; case self::XLS_Type_FONT: $this->_readFont(); break; case self::XLS_Type_FORMAT: $this->_readFormat(); break; case self::XLS_Type_XF: $this->_readXf(); break; case self::XLS_Type_STYLE: $this->_readStyle(); break; case self::XLS_Type_PALETTE: $this->_readPalette(); break; case self::XLS_Type_SHEET: $this->_readSheet(); break; case self::XLS_Type_EXTERNALBOOK: $this->_readExternalBook(); break; case self::XLS_Type_EXTERNSHEET: $this->_readExternSheet(); break; case self::XLS_Type_DEFINEDNAME: $this->_readDefinedName(); break; case self::XLS_Type_MSODRAWINGGROUP: $this->_readMsoDrawingGroup(); break; case self::XLS_Type_SST: $this->_readSst(); break; case self::XLS_Type_EOF: $this->_readDefault(); break 2; default: $this->_readDefault(); break; } } // Resolve indexed colors for font, fill, and border colors // Cannot be resolved already in XF record, because PALETTE record comes afterwards if (!$this->_readDataOnly) { foreach ($this->_fonts as &$font) { $font['color'] = $this->_readColor($font['colorIndex']); } foreach ($this->_xf as &$xf) { // fonts $xf['font']['color'] = $this->_readColor($xf['font']['colorIndex']); // fill start and end color $xf['fill']['startcolor'] = $this->_readColor($xf['fill']['startcolorIndex']); $xf['fill']['endcolor'] = $this->_readColor($xf['fill']['endcolorIndex']); // border colors $xf['borders']['top']['color'] = $this->_readColor($xf['borders']['top']['colorIndex']); $xf['borders']['right']['color'] = $this->_readColor($xf['borders']['right']['colorIndex']); $xf['borders']['bottom']['color'] = $this->_readColor($xf['borders']['bottom']['colorIndex']); $xf['borders']['left']['color'] = $this->_readColor($xf['borders']['left']['colorIndex']); } foreach ($this->_builtInStyles as &$builtInStyle) { // fonts $builtInStyle['font']['color'] = $this->_readColor($builtInStyle['font']['colorIndex']); // fill start and end color $builtInStyle['fill']['startcolor'] = $this->_readColor($builtInStyle['fill']['startcolorIndex']); $builtInStyle['fill']['endcolor'] = $this->_readColor($builtInStyle['fill']['endcolorIndex']); // border colors $builtInStyle['borders']['top']['color'] = $this->_readColor($builtInStyle['borders']['top']['colorIndex']); $builtInStyle['borders']['right']['color'] = $this->_readColor($builtInStyle['borders']['right']['colorIndex']); $builtInStyle['borders']['bottom']['color'] = $this->_readColor($builtInStyle['borders']['bottom']['colorIndex']); $builtInStyle['borders']['left']['color'] = $this->_readColor($builtInStyle['borders']['left']['colorIndex']); } } // treat MSODRAWINGGROUP records, workbook-level Escher if (!$this->_readDataOnly && $this->_drawingGroupData) { $escherWorkbook = new PHPExcel_Shared_Escher(); $reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook); $escherWorkbook = $reader->load($this->_drawingGroupData); // debug Escher stream //$debug = new Debug_Escher(new PHPExcel_Shared_Escher()); //$debug->load($this->_drawingGroupData); } // Parse the individual sheets foreach ($this->_sheets as $sheet) { // check if sheet should be skipped if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) { continue; } // add sheet to PHPExcel object $this->_phpSheet = $this->_phpExcel->createSheet(); $this->_phpSheet->setTitle($sheet['name']); // default style if (!$this->_readDataOnly && isset($this->_builtInStyles[0])) { $this->_phpSheet->getDefaultStyle()->applyFromArray($this->_builtInStyles[0]); } $this->_pos = $sheet['offset']; // Initialize isFitToPages. May change after reading SHEETPR record. $this->_isFitToPages = false; // Initialize drawingData $this->_drawingData = ''; // Initialize objs $this->_objs = array(); while ($this->_pos < $this->_dataSize) { $code = $this->_GetInt2d($this->_data, $this->_pos); switch ($code) { case self::XLS_Type_BOF: $length = $this->_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; // do not use this version information for anything // it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream // offset: 2; size: 2; type of the following data $substreamType = $this->_GetInt2d($recordData, 2); if ($substreamType != self::XLS_Worksheet) { break 2; } break; case self::XLS_Type_PRINTGRIDLINES: $this->_readPrintGridlines(); break; case self::XLS_Type_DEFAULTROWHEIGHT: $this->_readDefaultRowHeight(); break; case self::XLS_Type_SHEETPR: $this->_readSheetPr(); break; case self::XLS_Type_HORIZONTALPAGEBREAKS: $this->_readHorizontalPageBreaks(); break; case self::XLS_Type_VERTICALPAGEBREAKS: $this->_readVerticalPageBreaks(); break; case self::XLS_Type_HEADER: $this->_readHeader(); break; case self::XLS_Type_FOOTER: $this->_readFooter(); break; case self::XLS_Type_HCENTER: $this->_readHcenter(); break; case self::XLS_Type_VCENTER: $this->_readVcenter(); break; case self::XLS_Type_LEFTMARGIN: $this->_readLeftMargin(); break; case self::XLS_Type_RIGHTMARGIN: $this->_readRightMargin(); break; case self::XLS_Type_TOPMARGIN: $this->_readTopMargin(); break; case self::XLS_Type_BOTTOMMARGIN: $this->_readBottomMargin(); break; case self::XLS_Type_PAGESETUP: $this->_readPageSetup(); break; case self::XLS_Type_PROTECT: $this->_readProtect(); break; case self::XLS_Type_PASSWORD: $this->_readPassword(); break; case self::XLS_Type_DEFCOLWIDTH: $this->_readDefColWidth(); break; case self::XLS_Type_COLINFO: $this->_readColInfo(); break; case self::XLS_Type_DIMENSION: $this->_readDefault(); break; case self::XLS_Type_ROW: $this->_readRow(); break; case self::XLS_Type_DBCELL: $this->_readDefault(); break; case self::XLS_Type_RK: $this->_readRk(); break; case self::XLS_Type_LABELSST: $this->_readLabelSst(); break; case self::XLS_Type_MULRK: $this->_readMulRk(); break; case self::XLS_Type_NUMBER: $this->_readNumber(); break; case self::XLS_Type_FORMULA: $this->_readFormula(); break; case self::XLS_Type_BOOLERR: $this->_readBoolErr(); break; case self::XLS_Type_MULBLANK: $this->_readMulBlank(); break; case self::XLS_Type_LABEL: $this->_readLabel(); break; case self::XLS_Type_BLANK: $this->_readBlank(); break; case self::XLS_Type_MSODRAWING: $this->_readMsoDrawing(); break; case self::XLS_Type_OBJ: $this->_readObj(); break; case self::XLS_Type_WINDOW2: $this->_readWindow2(); break; case self::XLS_Type_SCL: $this->_readScl(); break; case self::XLS_Type_PANE: $this->_readPane(); break; case self::XLS_Type_MERGEDCELLS: $this->_readMergedCells(); break; case self::XLS_Type_HYPERLINK: $this->_readHyperLink(); break; case self::XLS_Type_RANGEPROTECTION: $this->_readRangeProtection(); break; //case self::XLS_Type_IMDATA: $this->_readImData(); break; //case self::XLS_Type_IMDATA: $this->_readImData(); break; case self::XLS_Type_EOF: $this->_readDefault(); break 2; default: $this->_readDefault(); break; } } // treat MSODRAWING records, sheet-level Escher if (!$this->_readDataOnly && $this->_drawingData) { $escherWorksheet = new PHPExcel_Shared_Escher(); $reader = new PHPExcel_Reader_Excel5_Escher($escherWorksheet); $escherWorksheet = $reader->load($this->_drawingData); // debug Escher stream //$debug = new Debug_Escher(new PHPExcel_Shared_Escher()); //$debug->load($this->_drawingData); $spContainerCollection = $escherWorksheet->getDgContainer()->getSpgrContainer()->getSpContainerCollection(); } // treat OBJ records foreach ($this->_objs as $n => $obj) { // skip first shape container which holds the shape group, hence $n + 1 $spContainer = $spContainerCollection[$n + 1]; switch ($obj['type']) { case 0x8: // picture // get index to BSE entry (1-based) $BSEindex = $spContainer->getOPT(0x104); $BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection(); $BSE = $BSECollection[$BSEindex - 1]; $blipType = $BSE->getBlipType(); $blip = $BSE->getBlip(); $ih = imagecreatefromstring($blip->getData()); $drawing = new PHPExcel_Worksheet_MemoryDrawing(); $drawing->setImageResource($ih); switch ($blipType) { case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG: $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_JPEG); break; case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG: $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG); $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG); break; } $drawing->setWorksheet($this->_phpSheet); $drawing->setCoordinates($spContainer->getStartCoordinates()); break; default: // other object type break; } } } // add the named ranges (defined names) foreach ($this->_definedname as $definedName) { if ($definedName['isBuiltInName']) { switch ($definedName['name']) { case pack('C', 0x6): // print area // in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2 $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma? foreach ($ranges as $range) { // $range should look like this one of these // Foo!$C$7:$J$66 // Bar!$A$1:$IV$2 $explodes = explode('!', $range); if (count($explodes) == 2) { if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) { $extractedRange = $explodes[1]; $extractedRange = str_replace('$', '', $extractedRange); $docSheet->getPageSetup()->setPrintArea($extractedRange); } } } break; case pack('C', 0x7): // print titles (repeating rows) // Assuming BIFF8, there are 3 cases // 1. repeating rows // formula looks like this: Sheet!$A$1:$IV$2 // rows 1-2 repeat // 2. repeating columns // formula looks like this: Sheet!$A$1:$B$65536 // columns A-B repeat // 3. both repeating rows and repeating columns // formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2 $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma? foreach ($ranges as $range) { // $range should look like this one of these // Sheet!$A$1:$B$65536 // Sheet!$A$1:$IV$2 $explodes = explode('!', $range); if (count($explodes) == 2) { if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) { $extractedRange = $explodes[1]; $extractedRange = str_replace('$', '', $extractedRange); $coordinateStrings = explode(':', $extractedRange); if (count($coordinateStrings) == 2) { list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[0]); list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[1]); if ($firstColumn == 'A' and $lastColumn == 'IV') { // then we have repeating rows $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($firstRow, $lastRow)); } elseif ($firstRow == 1 and $lastRow == 65536) { // then we have repeating columns $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($firstColumn, $lastColumn)); } } } } } break; } } else { // Extract range $explodes = explode('!', $definedName['formula']); if (count($explodes) == 2) { if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) { $extractedRange = $explodes[1]; $extractedRange = str_replace('$', '', $extractedRange); $this->_phpExcel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $docSheet, $extractedRange, true)); } } } } return $this->_phpExcel; }
/** * Add data to the beginning of the workbook (note the reverse order) * and to the end of the workbook. * * @access public * @see PHPExcel_Writer_Excel5_Workbook::storeWorkbook() */ function close() { $num_sheets = count($this->_phpSheet->getParent()->getAllSheets()); // Write BOF record $this->_storeBof(0x10); // Write DEFCOLWIDTH record $this->_storeDefcol(); // Calculate column widths $this->_phpSheet->calculateColumnWidths(); // Column dimensions foreach ($this->_phpSheet->getColumnDimensions() as $columnDimension) { $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1; if ($column < 256) { if ($columnDimension->getWidth() >= 0) { $width = $columnDimension->getWidth(); } else { if ($this->_phpSheet->getDefaultColumnDimension()->getWidth() >= 0) { $width = $this->_phpSheet->getDefaultColumnDimension()->getWidth(); } else { $width = 8; } } $this->_setColumn($column, $column, $width, null, $columnDimension->getVisible() ? '0' : '1', $columnDimension->getOutlineLevel()); } } // Write the COLINFO records if they exist if (!empty($this->_colinfo)) { $colcount = count($this->_colinfo); for ($i = 0; $i < $colcount; ++$i) { $this->_storeColinfo($this->_colinfo[$i]); } } // Write EXTERNCOUNT of external references if ($this->_BIFF_version == 0x500) { $this->_storeExterncount($num_sheets); } // Write EXTERNSHEET references if ($this->_BIFF_version == 0x500) { for ($i = 0; $i < $num_sheets; ++$i) { $this->_storeExternsheet($this->_phpSheet->getParent()->getSheet($i)->getTitle()); } } // Write PRINTHEADERS $this->_storePrintHeaders(); // Write PRINTGRIDLINES $this->_storePrintGridlines(); // Write GUTS $this->_storeGuts(); // Write GRIDSET $this->_storeGridset(); // Write DEFAULTROWHEIGHT if ($this->_BIFF_version == 0x600) { $this->_storeDefaultRowHeight(); } // Write WSBOOL $this->_storeWsbool(); // Write horizontal and vertical page breaks $this->_storeBreaks(); // Write page header $this->_storeHeader(); // Write page footer $this->_storeFooter(); // Write page horizontal centering $this->_storeHcenter(); // Write page vertical centering $this->_storeVcenter(); // Write left margin $this->_storeMarginLeft(); // Write right margin $this->_storeMarginRight(); // Write top margin $this->_storeMarginTop(); /* FIXME: margins are actually appended */ // Write bottom margin $this->_storeMarginBottom(); // Write page setup $this->_storeSetup(); // Write sheet protection $this->_storeProtect(); // Write sheet password $this->_storePassword(); // Write sheet dimensions $this->_storeDimensions(); // Write Cells $aStyles = $this->_phpSheet->getStyles(); $emptyStyle = $this->_phpSheet->getDefaultStyle(); foreach ($this->_phpSheet->getCellCollection() as $cell) { $row = $cell->getRow() - 1; $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1; // Don't break Excel! if ($row + 1 > 65536 or $column + 1 > 256) { break; } $style = $emptyStyle; if (isset($aStyles[$cell->getCoordinate()])) { $style = $aStyles[$cell->getCoordinate()]; } $styleHashIndex = $style->getHashIndex(); // Write cell value if ($cell->getValue() instanceof PHPExcel_RichText) { $this->_writeString($row, $column, $cell->getValue()->getPlainText(), $this->_xfIndexes[$styleHashIndex]); } else { switch ($cell->getDatatype()) { case PHPExcel_Cell_DataType::TYPE_STRING: if ($cell->getValue() === '' or $cell->getValue() === null) { $this->_writeBlank($row, $column, $this->_xfIndexes[$styleHashIndex]); } else { $this->_writeString($row, $column, $cell->getValue(), $this->_xfIndexes[$styleHashIndex]); } break; case PHPExcel_Cell_DataType::TYPE_FORMULA: $this->_writeFormula($row, $column, $cell->getValue(), $this->_xfIndexes[$styleHashIndex]); break; case PHPExcel_Cell_DataType::TYPE_BOOL: $this->_writeBoolErr($row, $column, $cell->getValue(), 0, $this->_xfIndexes[$styleHashIndex]); break; case PHPExcel_Cell_DataType::TYPE_ERROR: $this->_writeBoolErr($row, $column, $this->_mapErrorCode($cell->getValue()), 1, $this->_xfIndexes[$styleHashIndex]); break; default: $this->_write($row, $column, $cell->getValue(), $this->_xfIndexes[$styleHashIndex], $style->getNumberFormat()->getFormatCode()); break; } // Hyperlink? if ($cell->hasHyperlink()) { $this->_writeUrl($row, $column, str_replace('sheet://', 'internal:', $cell->getHyperlink()->getUrl())); } } } // Row dimensions foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) { $this->_setRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), null, $rowDimension->getVisible() ? '0' : '1', $rowDimension->getOutlineLevel()); } // Append if ($this->_BIFF_version == 0x600) { $this->_storeMsoDrawing(); } $this->_storeWindow2(); $this->_storeZoom(); if ($this->_phpSheet->getFreezePane()) { $this->_storePanes(); } $this->_storeSelection($this->_selection); $this->_storeMergedCells(); /* TODO: add data validity */ /*if ($this->_BIFF_version == 0x0600) { $this->_storeDataValidity(); }*/ if ($this->_BIFF_version == 0x600) { $this->_storeRangeProtection(); } $this->_storeEof(); }