/**
* 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();
}
