/**
* Save PHPExcel to file
*
* @param string $pFileName
* @throws Exception
*/
public function save($pFilename = null) {
$this->_colors = array();
$phpExcel = $this->_phpExcel;
$workbook = new PHPExcel_Writer_Excel5_Writer($pFilename);
$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);
}
$allWorksheets = $workbook->worksheets();
// Add full sheet data
foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) {
$phpSheet = $phpExcel->getSheet($sheetIndex);
$worksheet = $allWorksheets[$sheetIndex];
$worksheet->setInputEncoding("UTF-8");
// Default style
$emptyStyle = $phpSheet->getDefaultStyle();
$aStyles = $phpSheet->getStyles();
$freeze = $phpSheet->getFreezePane();
if ($freeze) {
list($column, $row) = PHPExcel_Cell::coordinateFromString($freeze);
$worksheet->freezePanes(array($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1));
}
//if ($sheetIndex == $phpExcel->getActiveSheetIndex()) {
// $worksheet->select();
//}
if ($phpSheet->getProtection()->getSheet()) {
$worksheet->protect($phpSheet->getProtection()->getPassword(), true);
}
if (!$phpSheet->getShowGridlines()) {
$worksheet->hideGridLines();
}
$formats = array();
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;
}
$style = $emptyStyle;
if (isset($aStyles[$cell->getCoordinate()])) {
$style = $aStyles[$cell->getCoordinate()];
}
$styleHash = $style->getHashCode();
if (!isset($formats[$styleHash])) {
$formats[$styleHash] = $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(),
//~ 'Script' => $style->getSuperscript(),
'NumFormat' => iconv("UTF-8", "Windows-1252", $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[$styleHash]->setTextWrap();
}
if ($style->getAlignment()->getShrinkToFit()) {
$formats[$styleHash]->setShrinkToFit();
}
if ($style->getFont()->getItalic()) {
$formats[$styleHash]->setItalic();
}
if ($style->getFont()->getStriketrough()) {
$formats[$styleHash]->setStrikeOut();
}
}
// Write cell value
if ($cell->getValue() instanceof PHPExcel_RichText) {
$worksheet->write($row, $column, $cell->getValue()->getPlainText(), $formats[$styleHash]);
} else {
// Hyperlink?
if ($cell->hasHyperlink()) {
$worksheet->writeUrl($row, $column, str_replace('sheet://', 'internal:', $cell->getHyperlink()->getUrl()), $cell->getValue(), $formats[$styleHash]);
} else {
$worksheet->write($row, $column, $cell->getValue(), $formats[$styleHash],$style->getNumberFormat()->getFormatCode());
}
}
}
// Column dimensions
$phpSheet->calculateColumnWidths();
$defaultWidth = null;
if ($phpSheet->getDefaultColumnDimension()->getWidth() >= 0) {
$defaultWidth = $phpSheet->getDefaultColumnDimension()->getWidth();
for ($column = 0; $column < PHPExcel_Cell::columnIndexFromString($phpSheet->getHighestColumn()) - 1; $column++) {
$worksheet->setColumn( $column, $column, $defaultWidth );
}
}
foreach ($phpSheet->getColumnDimensions() as $columnDimension) {
$column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
$worksheet->setColumn( $column, $column, $columnDimension->getWidth(), null, ($columnDimension->getVisible() ? '0' : '1'), $columnDimension->getOutlineLevel());
}
// Row dimensions
$defaultHeight = null;
if ($phpSheet->getDefaultRowDimension()->getRowHeight() >= 0) {
$defaultHeight = $phpSheet->getDefaultRowDimension()->getRowHeight();
for ($i = 0; $i < $phpSheet->getHighestRow() - 1; $i++) {
$worksheet->setRow( $i, $defaultHeight );
}
}
foreach ($phpSheet->getRowDimensions() as $rowDimension) {
$worksheet->setRow( $rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), null, ($rowDimension->getVisible() ? '0' : '1'), $rowDimension->getOutlineLevel() );
}
foreach ($phpSheet->getMergeCells() as $cells) {
list($first, $last) = PHPExcel_Cell::splitRange($cells);
list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first);
list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last);
$worksheet->mergeCells($firstRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1);
}
foreach ($phpSheet->getDrawingCollection() as $drawing) {
if ($drawing instanceof PHPExcel_Worksheet_BaseDrawing) {
$filename = $drawing->getPath();
$imagesize = getimagesize($filename);
switch ($imagesize[2]) {
case 1: $image = imagecreatefromgif($filename); break;
case 2: $image = imagecreatefromjpeg($filename); break;
case 3: $image = imagecreatefrompng($filename); break;
default: continue 2;
}
list($column, $row) = PHPExcel_Cell::coordinateFromString($drawing->getCoordinates());
$worksheet->insertBitmap($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $image, $drawing->getOffsetX(), $drawing->getOffsetY(), $drawing->getWidth() / $imagesize[0], $drawing->getHeight() / $imagesize[1]);
}
}
// page setup
if ($phpSheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) {
$worksheet->setLandscape();
}
$worksheet->setPaper($phpSheet->getPageSetup()->getPaperSize());
$worksheet->setHeader($phpSheet->getHeaderFooter()->getOddHeader(), $phpSheet->getPageMargins()->getHeader());
$worksheet->setFooter($phpSheet->getHeaderFooter()->getOddFooter(), $phpSheet->getPageMargins()->getFooter());
$worksheet->setMarginLeft($phpSheet->getPageMargins()->getLeft());
$worksheet->setMarginRight($phpSheet->getPageMargins()->getRight());
$worksheet->setMarginTop($phpSheet->getPageMargins()->getTop());
$worksheet->setMarginBottom($phpSheet->getPageMargins()->getBottom());
// -------------------------------------------------------------------
// Commented due to bug:
// http://pear.php.net/bugs/bug.php?id=2146
// -------------------------------------------------------------------
// // repeatColumns / repeatRows
// if ($phpSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $phpSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
// // Columns to repeat
// if ($phpSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
// $repeat = $phpSheet->getPageSetup()->getColumnsToRepeatAtLeft();
//
// $worksheet->repeatColumns(PHPExcel_Cell::columnIndexFromString($repeat[0] - 1), PHPExcel_Cell::columnIndexFromString($repeat[1] - 1));
// }
//
// // Rows to repeat
// if ($phpSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
// $repeat = $phpSheet->getPageSetup()->getRowsToRepeatAtTop();
//
// $worksheet->repeatRows($repeat[0], $repeat[1]);
// }
// }
// -------------------------------------------------------------------
// Commented due to bug:
// http://pear.php.net/bugs/bug.php?id=2146
// -------------------------------------------------------------------
// if ($phpSheet->getPageSetup()->isPrintAreaSet()) {
// // Print area
// $printArea = PHPExcel_Cell::splitRange($phpSheet->getPageSetup()->getPrintArea());
// $printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]);
// $printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]);
//
// $worksheet->printArea(
// $printArea[0][1],
// PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1,
// $printArea[1][1],
// PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1
// );
// }
// Support for print scale
if ($phpSheet->getPageSetup()->getScale()) {
$worksheet->setPrintScale($phpSheet->getPageSetup()->getScale());
}
// Support for fitting to pages
if ($phpSheet->getPageSetup()->getFitToWidth()) {
if ($phpSheet->getPageSetup()->getFitToHeight()) {
// Both properties are set, so use them
// Note: This case is double, see below
$worksheet->fitToPages($phpSheet->getPageSetup()->getFitToWidth(), $phpSheet->getPageSetup()->getFitToHeight());
} else {
// Only width given, make assumption about height
$height = 0;
$worksheet->fitToPages($phpSheet->getPageSetup()->getFitToWidth(), $height);
}
} else if ($phpSheet->getPageSetup()->getFitToHeight()) {
if ($phpSheet->getPageSetup()->getFitToWidth()) {
// Both properties are set, so use them
// Note: This case is double, see below
$worksheet->fitToPages($phpSheet->getPageSetup()->getFitToWidth(), $phpSheet->getPageSetup()->getFitToHeight());
} else {
// Only height given, make assumption about width
$width = 0;
$worksheet->fitToPages($width, $phpSheet->getPageSetup()->getFitToHeight());
}
}
// Support for breaks
$vBreaks = array();
$hBreaks = array();
foreach ($phpSheet->getBreaks() as $cell => $breakType) {
// Fetch coordinates
$coordinates = PHPExcel_Cell::coordinateFromString($cell);
// Decide what to do by the type of break
switch ($breakType) {
case PHPExcel_Worksheet::BREAK_COLUMN:
// Add to list of vertical breaks
$vBreaks[] = $coordinates[0];
break;
case PHPExcel_Worksheet::BREAK_ROW:
// Add to list of horizontal breaks
$hBreaks[] = $coordinates[1];
break;
case PHPExcel_Worksheet::BREAK_NONE:
default:
// Nothing to do
break;
}
}
$worksheet->setVPagebreaks($vBreaks);
$worksheet->setHPagebreaks($hBreaks);
}
PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);
$workbook->close();
}
/**
* Save PHPExcel to file
*
* @param string $pFileName
* @throws Exception
*/
public function save($pFilename = null)
{
$this->_colors = array();
$phpExcel = $this->_phpExcel;
$workbook = new PHPExcel_Writer_Excel5_Writer($pFilename);
$workbook->setVersion(8);
// Set temp dir
if ($this->_tempDir != '') {
$workbook->setTempDir($this->_tempDir);
}
// Create empty style
$emptyStyle = new PHPExcel_Style();
// Add empty sheets
foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) {
$phpSheet = $phpExcel->getSheet($sheetIndex);
$worksheet = $workbook->addWorksheet($sheetName);
}
$allWorksheets = $workbook->worksheets();
// Add full sheet data
foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) {
$phpSheet = $phpExcel->getSheet($sheetIndex);
$worksheet = $allWorksheets[$sheetIndex];
$worksheet->setInputEncoding("UTF-8");
$aStyles = $phpSheet->getStyles();
$freeze = $phpSheet->getFreezePane();
if ($freeze) {
list($column, $row) = PHPExcel_Cell::coordinateFromString($freeze);
$worksheet->freezePanes(array($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1));
}
//if ($sheetIndex == $phpExcel->getActiveSheetIndex()) {
// $worksheet->select();
//}
if ($phpSheet->getProtection()->getSheet()) {
$worksheet->protect($phpSheet->getProtection()->getPassword(), true);
}
if (!$phpSheet->getShowGridlines()) {
$worksheet->hideGridLines();
}
$formats = array();
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;
}
$style = $emptyStyle;
if (isset($aStyles[$cell->getCoordinate()])) {
$style = $aStyles[$cell->getCoordinate()];
}
$styleHash = $style->getHashCode();
if (!isset($formats[$styleHash])) {
$formats[$styleHash] = $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' => iconv("UTF-8", "Windows-1252", $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[$styleHash]->setTextWrap();
}
if ($style->getFont()->getItalic()) {
$formats[$styleHash]->setItalic();
}
if ($style->getFont()->getStriketrough()) {
$formats[$styleHash]->setStrikeOut();
}
}
// Write cell value
if ($cell->getValue() instanceof PHPExcel_RichText) {
$worksheet->write($row, $column, $cell->getValue()->getPlainText(), $formats[$styleHash]);
} else {
// Hyperlink?
if ($cell->hasHyperlink()) {
$worksheet->writeUrl($row, $column, $cell->getHyperlink()->getUrl(), $cell->getValue(), $formats[$styleHash]);
} else {
$worksheet->write($row, $column, $cell->getValue(), $formats[$styleHash]);
}
}
}
$phpSheet->calculateColumnWidths();
foreach ($phpSheet->getColumnDimensions() as $columnDimension) {
$column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
$worksheet->setColumn($column, $column, $columnDimension->getWidth(), null, $columnDimension->getVisible() ? '0' : '1');
}
foreach ($phpSheet->getRowDimensions() as $rowDimension) {
$worksheet->setRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), null, $rowDimension->getVisible() ? '0' : '1');
}
foreach ($phpSheet->getMergeCells() as $cells) {
list($first, $last) = PHPExcel_Cell::splitRange($cells);
list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first);
list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last);
$worksheet->mergeCells($firstRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1);
}
foreach ($phpSheet->getDrawingCollection() as $drawing) {
if ($drawing instanceof PHPExcel_Worksheet_BaseDrawing) {
$filename = $drawing->getPath();
$imagesize = getimagesize($filename);
switch ($imagesize[2]) {
case 1:
$image = imagecreatefromgif($filename);
break;
case 2:
$image = imagecreatefromjpeg($filename);
break;
case 3:
$image = imagecreatefrompng($filename);
break;
default:
continue 2;
}
list($column, $row) = PHPExcel_Cell::coordinateFromString($drawing->getCoordinates());
$worksheet->insertBitmap($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $image, $drawing->getOffsetX(), $drawing->getOffsetY(), $drawing->getWidth() / $imagesize[0], $drawing->getHeight() / $imagesize[1]);
}
}
// page setup
if ($phpSheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) {
$worksheet->setLandscape();
}
$worksheet->setPaper($phpSheet->getPageSetup()->getPaperSize());
$worksheet->setHeader($phpSheet->getHeaderFooter()->getOddHeader(), $phpSheet->getPageMargins()->getHeader());
$worksheet->setFooter($phpSheet->getHeaderFooter()->getOddFooter(), $phpSheet->getPageMargins()->getFooter());
$worksheet->setMarginLeft($phpSheet->getPageMargins()->getLeft());
$worksheet->setMarginRight($phpSheet->getPageMargins()->getRight());
$worksheet->setMarginTop($phpSheet->getPageMargins()->getTop());
$worksheet->setMarginBottom($phpSheet->getPageMargins()->getBottom());
}
$workbook->close();
}
