/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // check for iconv support if (!function_exists('iconv')) { throw new Exception("Cannot write .xls file without PHP support for iconv"); } $phpExcel = $this->_phpExcel; $workbook = new PHPExcel_Writer_Excel5_Workbook($pFilename, $phpExcel); $workbook->setVersion(8); // Set temp dir if ($this->_tempDir != '') { $workbook->setTempDir($this->_tempDir); } $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // Add 15 style Xf's plus 1 cell Xf. Why? for ($i = 0; $i < 15; ++$i) { $workbook->addXfWriter($phpExcel->getSheet(0)->getDefaultStyle(), true); } $workbook->addXfWriter($phpExcel->getSheet(0)->getDefaultStyle()); // Style dictionary $xfIndexes = array(); $allStyles = $this->_allStyles($this->_phpExcel); $cellStyleHashes = new PHPExcel_HashTable(); $cellStyleHashes->addFromSource($allStyles); $addedStyles = array(); foreach ($allStyles as $style) { $styleHashIndex = $style->getHashIndex(); if (isset($addedStyles[$styleHashIndex])) { continue; } // mapping between PHPExcel style hash index and BIFF XF index $xfIndexes[$styleHashIndex] = $workbook->addXfWriter($style); $addedStyles[$style->getHashIndex()] = true; } // Add empty sheets foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) { $phpSheet = $phpExcel->getSheet($sheetIndex); $worksheet = $workbook->addWorksheet($phpSheet, $xfIndexes); } PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); $workbook->close(); }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // check for iconv support if (!function_exists('iconv')) { throw new Exception("Cannot write .xls file without PHP support for iconv"); } $this->_colors = array(); $phpExcel = $this->_phpExcel; $workbook = new PHPExcel_Writer_Excel5_Workbook($pFilename, $phpExcel); $workbook->setVersion(8); // Set temp dir if ($this->_tempDir != '') { $workbook->setTempDir($this->_tempDir); } $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // Add empty sheets foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) { $phpSheet = $phpExcel->getSheet($sheetIndex); $worksheet = $workbook->addWorksheet($sheetName, $phpSheet); } $allWorksheets = $workbook->worksheets(); $formats = array(); // Add full sheet data foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) { $phpSheet = $phpExcel->getSheet($sheetIndex); $worksheet = $allWorksheets[$sheetIndex]; // Default style $emptyStyle = $phpSheet->getDefaultStyle(); $aStyles = $phpSheet->getStyles(); // Calculate cell style hashes $cellStyleHashes = new PHPExcel_HashTable(); $aStyles = $phpSheet->getStyles(); $cellStyleHashes->addFromSource($aStyles); $addedStyles = array(); foreach ($aStyles as $style) { $styleHashIndex = $style->getHashIndex(); if (isset($addedStyles[$styleHashIndex])) { continue; } $formats[$styleHashIndex] = $workbook->addFormat(array('HAlign' => $style->getAlignment()->getHorizontal(), 'VAlign' => $this->_mapVAlign($style->getAlignment()->getVertical()), 'TextRotation' => $style->getAlignment()->getTextRotation(), 'Bold' => $style->getFont()->getBold(), 'FontFamily' => $style->getFont()->getName(), 'Color' => $this->_addColor($workbook, $style->getFont()->getColor()->getRGB()), 'Underline' => $this->_mapUnderline($style->getFont()->getUnderline()), 'Size' => $style->getFont()->getSize(), 'NumFormat' => $style->getNumberFormat()->getFormatCode(), 'Bottom' => $this->_mapBorderStyle($style->getBorders()->getBottom()->getBorderStyle()), 'Top' => $this->_mapBorderStyle($style->getBorders()->getTop()->getBorderStyle()), 'Left' => $this->_mapBorderStyle($style->getBorders()->getLeft()->getBorderStyle()), 'Right' => $this->_mapBorderStyle($style->getBorders()->getRight()->getBorderStyle()), 'BottomColor' => $this->_addColor($workbook, $style->getBorders()->getBottom()->getColor()->getRGB()), 'TopColor' => $this->_addColor($workbook, $style->getBorders()->getTop()->getColor()->getRGB()), 'RightColor' => $this->_addColor($workbook, $style->getBorders()->getRight()->getColor()->getRGB()), 'LeftColor' => $this->_addColor($workbook, $style->getBorders()->getLeft()->getColor()->getRGB()), 'FgColor' => $this->_addColor($workbook, $style->getFill()->getStartColor()->getRGB()), 'BgColor' => $this->_addColor($workbook, $style->getFill()->getEndColor()->getRGB()), 'Pattern' => $this->_mapFillType($style->getFill()->getFillType()))); if ($style->getAlignment()->getWrapText()) { $formats[$styleHashIndex]->setTextWrap(); } $formats[$styleHashIndex]->setIndent($style->getAlignment()->getIndent()); if ($style->getAlignment()->getShrinkToFit()) { $formats[$styleHashIndex]->setShrinkToFit(); } if ($style->getFont()->getItalic()) { $formats[$styleHashIndex]->setItalic(); } if ($style->getFont()->getStriketrough()) { $formats[$styleHashIndex]->setStrikeOut(); } if ($style->getProtection()->getLocked() == PHPExcel_Style_Protection::PROTECTION_UNPROTECTED) { $formats[$styleHashIndex]->setUnlocked(); } if ($style->getProtection()->getHidden() == PHPExcel_Style_Protection::PROTECTION_PROTECTED) { $formats[$styleHashIndex]->setHidden(); } $addedStyles[$style->getHashIndex()] = true; } // Active sheet if ($sheetIndex == $phpExcel->getActiveSheetIndex()) { $worksheet->activate(); } // initialize first, last, row and column index, needed for DIMENSION record $firstRowIndex = 0; $lastRowIndex = -1; $firstColumnIndex = 0; $lastColumnIndex = -1; foreach ($phpSheet->getCellCollection() as $cell) { $row = $cell->getRow() - 1; $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1; // Don't break Excel! if ($row + 1 >= 65569) { break; } $firstRowIndex = min($firstRowIndex, $row); $lastRowIndex = max($lastRowIndex, $row); $firstColumnIndex = min($firstColumnIndex, $column); $lastColumnIndex = max($lastColumnIndex, $column); $style = $emptyStyle; if (isset($aStyles[$cell->getCoordinate()])) { $style = $aStyles[$cell->getCoordinate()]; } $styleHashIndex = $style->getHashIndex(); // Write cell value if ($cell->getValue() instanceof PHPExcel_RichText) { $worksheet->write($row, $column, $cell->getValue()->getPlainText(), $formats[$styleHashIndex]); } else { switch ($cell->getDatatype()) { case PHPExcel_Cell_DataType::TYPE_STRING: if ($cell->getValue() === '' or $cell->getValue() === null) { $worksheet->writeBlank($row, $column, $formats[$styleHashIndex]); } else { $worksheet->writeString($row, $column, $cell->getValue(), $formats[$styleHashIndex]); } break; case PHPExcel_Cell_DataType::TYPE_FORMULA: $worksheet->writeFormula($row, $column, $cell->getValue(), $formats[$styleHashIndex]); break; case PHPExcel_Cell_DataType::TYPE_BOOL: $worksheet->writeBoolErr($row, $column, $cell->getValue(), 0, $formats[$styleHashIndex]); break; case PHPExcel_Cell_DataType::TYPE_ERROR: $worksheet->writeBoolErr($row, $column, $this->_mapErrorCode($cell->getValue()), 1, $formats[$styleHashIndex]); break; default: $worksheet->write($row, $column, $cell->getValue(), $formats[$styleHashIndex], $style->getNumberFormat()->getFormatCode()); break; } // Hyperlink? if ($cell->hasHyperlink()) { $worksheet->writeUrl($row, $column, str_replace('sheet://', 'internal:', $cell->getHyperlink()->getUrl())); } } } // set the worksheet dimension for the DIMENSION record $worksheet->setDimensions($firstRowIndex, $lastRowIndex, $firstColumnIndex, $lastColumnIndex); $phpSheet->calculateColumnWidths(); // Column dimensions foreach ($phpSheet->getColumnDimensions() as $columnDimension) { $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1; if ($column < 256) { if ($columnDimension->getWidth() >= 0) { $width = $columnDimension->getWidth(); } else { if ($phpSheet->getDefaultColumnDimension()->getWidth() >= 0) { $width = $phpSheet->getDefaultColumnDimension()->getWidth(); } else { $width = 8; } } $worksheet->setColumn($column, $column, $width, null, $columnDimension->getVisible() ? '0' : '1', $columnDimension->getOutlineLevel()); } } // Row dimensions foreach ($phpSheet->getRowDimensions() as $rowDimension) { $worksheet->setRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), null, $rowDimension->getVisible() ? '0' : '1', $rowDimension->getOutlineLevel()); } foreach ($phpSheet->getDrawingCollection() as $drawing) { list($column, $row) = PHPExcel_Cell::coordinateFromString($drawing->getCoordinates()); if ($drawing instanceof PHPExcel_Worksheet_Drawing) { $filename = $drawing->getPath(); list($imagesx, $imagesy, $imageFormat) = getimagesize($filename); switch ($imageFormat) { case 1: $image = imagecreatefromgif($filename); break; case 2: $image = imagecreatefromjpeg($filename); break; case 3: $image = imagecreatefrompng($filename); break; default: continue 2; } } else { if ($drawing instanceof PHPExcel_Worksheet_MemoryDrawing) { $image = $drawing->getImageResource(); $imagesx = imagesx($image); $imagesy = imagesy($image); } } $worksheet->insertBitmap($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $image, $drawing->getOffsetX(), $drawing->getOffsetY(), $drawing->getWidth() / $imagesx, $drawing->getHeight() / $imagesy); } } PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); $workbook->close(); }