/** * 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(); }
/** * EOMONTH * * Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. * Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. * * @param long $dateValue Excel date serial value or a standard date string * @param int $adjustmentMonths Number of months to adjust by * @return long Excel date serial value */ public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0) { $dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue); $adjustmentMonths = floor(PHPExcel_Calculation_Functions::flattenSingleValue($adjustmentMonths)); if (!is_numeric($adjustmentMonths)) { return PHPExcel_Calculation_Functions::VALUE(); } if (is_string($dateValue = self::_getDateValue($dateValue))) { return PHPExcel_Calculation_Functions::VALUE(); } // Execute function $PHPDateObject = self::_adjustDateByMonths($dateValue, $adjustmentMonths + 1); $adjustDays = (int) $PHPDateObject->format('d'); $adjustDaysString = '-' . $adjustDays . ' days'; $PHPDateObject->modify($adjustDaysString); switch (PHPExcel_Calculation_Functions::getReturnDateType()) { case PHPExcel_Calculation_Functions::RETURNDATE_EXCEL: return (double) PHPExcel_Shared_Date::PHPToExcel($PHPDateObject); break; case PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC: return (int) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::PHPToExcel($PHPDateObject)); break; case PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT: return $PHPDateObject; break; } }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // check mbstring.func_overload if (ini_get('mbstring.func_overload') != 0) { throw new Exception('Multibyte string function overloading in PHP must be disabled.'); } // garbage collect $this->_phpExcel->garbageCollect(); $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // Initialise workbook writer $this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_tempDir); // Initialise worksheet writers $countSheets = count($this->_phpExcel->getAllSheets()); for ($i = 0; $i < $countSheets; ++$i) { $phpSheet = $this->_phpExcel->getSheet($i); $writerWorksheet = new PHPExcel_Writer_Excel5_Worksheet($this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_tempDir, $phpSheet); $this->_writerWorksheets[$i] = $writerWorksheet; } // add 15 identical cell style Xfs // for now, we use the first cellXf instead of cellStyleXf $cellXfCollection = $this->_phpExcel->getCellXfCollection(); for ($i = 0; $i < 15; ++$i) { $this->_writerWorkbook->addXfWriter($cellXfCollection[0], true); } // add all the cell Xfs foreach ($this->_phpExcel->getCellXfCollection() as $style) { $this->_writerWorkbook->addXfWriter($style, false); } // initialize OLE file $workbookStreamName = $this->_BIFF_version == 0x600 ? 'Workbook' : 'Book'; $OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName)); if ($this->_tempDir != '') { $OLE->setTempDir($this->_tempDir); } $res = $OLE->init(); // Write the worksheet streams before the global workbook stream, // because the byte sizes of these are needed in the global workbook stream $worksheetSizes = array(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i]->close(); $worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize; } // add binary data for global workbook stream $OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes)); // add binary data for sheet streams for ($i = 0; $i < $countSheets; ++$i) { while (($tmp = $this->_writerWorksheets[$i]->getData()) !== false) { $OLE->append($tmp); } } $root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), array($OLE)); if ($this->_tempDir != '') { $root->setTempDir($this->_tempDir); } // save the OLE file $res = $root->save($pFilename); PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); // clean up foreach ($this->_writerWorksheets as $sheet) { $sheet->cleanup(); } }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { if (!is_null($this->_spreadSheet)) { // garbage collect $this->_spreadSheet->garbageCollect(); // If $pFilename is php://output or php://stdout, make it a temporary file... $originalFilename = $pFilename; if (strtolower($pFilename) == 'php://output' || strtolower($pFilename) == 'php://stdout') { $pFilename = @tempnam('./', 'phpxltmp'); if ($pFilename == '') { $pFilename = $originalFilename; } } $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // Create string lookup table $this->_stringTable = array(); for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) { $this->_stringTable = $this->getWriterPart('StringTable')->createStringTable($this->_spreadSheet->getSheet($i), $this->_stringTable); } // Create styles dictionaries $this->_stylesConditionalHashTable->addFromSource($this->getWriterPart('Style')->allConditionalStyles($this->_spreadSheet)); $this->_fillHashTable->addFromSource($this->getWriterPart('Style')->allFills($this->_spreadSheet)); $this->_fontHashTable->addFromSource($this->getWriterPart('Style')->allFonts($this->_spreadSheet)); $this->_bordersHashTable->addFromSource($this->getWriterPart('Style')->allBorders($this->_spreadSheet)); $this->_numFmtHashTable->addFromSource($this->getWriterPart('Style')->allNumberFormats($this->_spreadSheet)); // Create drawing dictionary $this->_drawingHashTable->addFromSource($this->getWriterPart('Drawing')->allDrawings($this->_spreadSheet)); // Create new ZIP file and open it for writing $objZip = new ZipArchive(); // Try opening the ZIP file if ($objZip->open($pFilename, ZIPARCHIVE::OVERWRITE) !== true) { if ($objZip->open($pFilename, ZIPARCHIVE::CREATE) !== true) { throw new Exception("Could not open " . $pFilename . " for writing."); } } // Add [Content_Types].xml to ZIP file $objZip->addFromString('[Content_Types].xml', $this->getWriterPart('ContentTypes')->writeContentTypes($this->_spreadSheet)); // Add relationships to ZIP file $objZip->addFromString('_rels/.rels', $this->getWriterPart('Rels')->writeRelationships($this->_spreadSheet)); $objZip->addFromString('xl/_rels/workbook.xml.rels', $this->getWriterPart('Rels')->writeWorkbookRelationships($this->_spreadSheet)); // Add document properties to ZIP file $objZip->addFromString('docProps/app.xml', $this->getWriterPart('DocProps')->writeDocPropsApp($this->_spreadSheet)); $objZip->addFromString('docProps/core.xml', $this->getWriterPart('DocProps')->writeDocPropsCore($this->_spreadSheet)); // Add theme to ZIP file $objZip->addFromString('xl/theme/theme1.xml', $this->getWriterPart('Theme')->writeTheme($this->_spreadSheet)); // Add string table to ZIP file $objZip->addFromString('xl/sharedStrings.xml', $this->getWriterPart('StringTable')->writeStringTable($this->_stringTable)); // Add styles to ZIP file $objZip->addFromString('xl/styles.xml', $this->getWriterPart('Style')->writeStyles($this->_spreadSheet)); // Add workbook to ZIP file $objZip->addFromString('xl/workbook.xml', $this->getWriterPart('Workbook')->writeWorkbook($this->_spreadSheet)); // Add worksheets for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) { $objZip->addFromString('xl/worksheets/sheet' . ($i + 1) . '.xml', $this->getWriterPart('Worksheet')->writeWorksheet($this->_spreadSheet->getSheet($i), $this->_stringTable)); } // Add worksheet relationships (drawings, ...) for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) { // Add relationships $objZip->addFromString('xl/worksheets/_rels/sheet' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeWorksheetRelationships($this->_spreadSheet->getSheet($i), $i + 1)); // Add drawing relationship parts if ($this->_spreadSheet->getSheet($i)->getDrawingCollection()->count() > 0) { // Drawing relationships $objZip->addFromString('xl/drawings/_rels/drawing' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeDrawingRelationships($this->_spreadSheet->getSheet($i))); // Drawings $objZip->addFromString('xl/drawings/drawing' . ($i + 1) . '.xml', $this->getWriterPart('Drawing')->writeDrawings($this->_spreadSheet->getSheet($i))); } // Add comment relationship parts if (count($this->_spreadSheet->getSheet($i)->getComments()) > 0) { // VML Comments $objZip->addFromString('xl/drawings/vmlDrawing' . ($i + 1) . '.vml', $this->getWriterPart('Comments')->writeVMLComments($this->_spreadSheet->getSheet($i))); // Comments $objZip->addFromString('xl/comments' . ($i + 1) . '.xml', $this->getWriterPart('Comments')->writeComments($this->_spreadSheet->getSheet($i))); } // Add header/footer relationship parts if (count($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages()) > 0) { // VML Drawings $objZip->addFromString('xl/drawings/vmlDrawingHF' . ($i + 1) . '.vml', $this->getWriterPart('Drawing')->writeVMLHeaderFooterImages($this->_spreadSheet->getSheet($i))); // VML Drawing relationships $objZip->addFromString('xl/drawings/_rels/vmlDrawingHF' . ($i + 1) . '.vml.rels', $this->getWriterPart('Rels')->writeHeaderFooterDrawingRelationships($this->_spreadSheet->getSheet($i))); // Media foreach ($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages() as $image) { $objZip->addFromString('xl/media/' . $image->getIndexedFilename(), file_get_contents($image->getPath())); } } } // Add media for ($i = 0; $i < $this->getDrawingHashTable()->count(); ++$i) { if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_Drawing) { $imageContents = null; $imagePath = $this->getDrawingHashTable()->getByIndex($i)->getPath(); if (strpos($imagePath, 'zip://') !== false) { $imagePath = substr($imagePath, 6); $imagePathSplitted = explode('#', $imagePath); $imageZip = new ZipArchive(); $imageZip->open($imagePathSplitted[0]); $imageContents = $imageZip->getFromName($imagePathSplitted[1]); $imageZip->close(); unset($imageZip); } else { $imageContents = file_get_contents($imagePath); } $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents); } else { if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_MemoryDrawing) { ob_start(); call_user_func($this->getDrawingHashTable()->getByIndex($i)->getRenderingFunction(), $this->getDrawingHashTable()->getByIndex($i)->getImageResource()); $imageContents = ob_get_contents(); ob_end_clean(); $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents); } } } PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); // Close file if ($objZip->close() === false) { throw new Exception("Could not close zip file {$pFilename}."); } // If a temporary file was used, copy it to the correct file stream if ($originalFilename != $pFilename) { if (copy($pFilename, $originalFilename) === false) { throw new Exception("Could not copy temporary zip file {$pFilename} to {$originalFilename}."); } @unlink($pFilename); } } else { throw new Exception("PHPExcel object unassigned."); } }
/** * Convert a dynamic rule daterange to a custom filter range expression for ease of calculation * * @param string $dynamicRuleType * @param PHPExcel_Worksheet_AutoFilter_Column &$filterColumn * @return mixed[] */ private function _dynamicFilterDateRange($dynamicRuleType, &$filterColumn) { $rDateType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC); $val = $maxVal = NULL; $ruleValues = array(); $baseDate = PHPExcel_Calculation_DateTime::DATENOW(); // Calculate start/end dates for the required date range based on current date switch ($dynamicRuleType) { case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK: $baseDate = strtotime('-7 days', $baseDate); break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK: $baseDate = strtotime('-7 days', $baseDate); break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH: $baseDate = strtotime('-1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH: $baseDate = strtotime('+1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER: $baseDate = strtotime('-3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER: $baseDate = strtotime('+3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR: $baseDate = strtotime('-1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR: $baseDate = strtotime('+1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); break; } switch ($dynamicRuleType) { case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TODAY: case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY: case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW: $maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(strtotime('+1 day', $baseDate)); $val = (int) PHPExcel_Shared_Date::PHPToExcel($baseDate); break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE: $maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(strtotime('+1 day', $baseDate)); $val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate))); break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR: case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR: case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR: $maxVal = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 31, 12, date('Y', $baseDate))); ++$maxVal; $val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate))); break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER: case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER: case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER: $thisMonth = date('m', $baseDate); $thisQuarter = floor(--$thisMonth / 3); $maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), (1 + $thisQuarter) * 3, date('Y', $baseDate))); ++$maxVal; $val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1 + $thisQuarter * 3, date('Y', $baseDate))); break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH: case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH: case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH: $maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), date('m', $baseDate), date('Y', $baseDate))); ++$maxVal; $val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK: case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK: case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK: $dayOfWeek = date('w', $baseDate); $val = (int) PHPExcel_Shared_Date::PHPToExcel($baseDate) - $dayOfWeek; $maxVal = $val + 7; break; } switch ($dynamicRuleType) { // Adjust Today dates for Yesterday and Tomorrow case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY: --$maxVal; --$val; break; case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW: ++$maxVal; ++$val; break; } // Set the filter column rule attributes ready for writing $filterColumn->setAttributes(array('val' => $val, 'maxVal' => $maxVal)); // Set the rules for identifying rows for hide/show $ruleValues[] = array('operator' => PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL, 'value' => $val); $ruleValues[] = array('operator' => PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN, 'value' => $maxVal); PHPExcel_Calculation_Functions::setReturnDateType($rDateType); return array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND)); }
/** * VALUE * * @param mixed $value Value to check * @return boolean */ public static function VALUE($value = '') { $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); if (!is_numeric($value)) { $numberValue = str_replace(PHPExcel_Shared_String::getThousandsSeparator(), '', trim($value, " \t\n\r\v" . PHPExcel_Shared_String::getCurrencyCode())); if (is_numeric($numberValue)) { return (double) $numberValue; } $dateSetting = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); if (strpos($value, ':') !== false) { $timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($value); if ($timeValue !== PHPExcel_Calculation_Functions::VALUE()) { PHPExcel_Calculation_Functions::setReturnDateType($dateSetting); return $timeValue; } } $dateValue = PHPExcel_Calculation_DateTime::DATEVALUE($value); if ($dateValue !== PHPExcel_Calculation_Functions::VALUE()) { PHPExcel_Calculation_Functions::setReturnDateType($dateSetting); return $dateValue; } PHPExcel_Calculation_Functions::setReturnDateType($dateSetting); return PHPExcel_Calculation_Functions::VALUE(); } return (double) $value; }
/** * Save PHPExcel to file * * @param string $pFilename * @throws PHPExcel_Writer_Exception */ public function save($pFilename = null) { if ($this->_spreadSheet !== NULL) { // garbage collect $this->_spreadSheet->garbageCollect(); // If $pFilename is php://output or php://stdout, make it a temporary file... $originalFilename = $pFilename; if (strtolower($pFilename) == 'php://output' || strtolower($pFilename) == 'php://stdout') { $pFilename = @tempnam(PHPExcel_Shared_File::sys_get_temp_dir(), 'phpxltmp'); if ($pFilename == '') { $pFilename = $originalFilename; } } $saveDebugLog = PHPExcel_Calculation::getInstance($this->_spreadSheet)->getDebugLog()->getWriteDebugLog(); PHPExcel_Calculation::getInstance($this->_spreadSheet)->getDebugLog()->setWriteDebugLog(FALSE); $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // Create string lookup table $this->_stringTable = array(); for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) { $this->_stringTable = $this->getWriterPart('StringTable')->createStringTable($this->_spreadSheet->getSheet($i), $this->_stringTable); } // Create styles dictionaries $this->_styleHashTable->addFromSource($this->getWriterPart('Style')->allStyles($this->_spreadSheet)); $this->_stylesConditionalHashTable->addFromSource($this->getWriterPart('Style')->allConditionalStyles($this->_spreadSheet)); $this->_fillHashTable->addFromSource($this->getWriterPart('Style')->allFills($this->_spreadSheet)); $this->_fontHashTable->addFromSource($this->getWriterPart('Style')->allFonts($this->_spreadSheet)); $this->_bordersHashTable->addFromSource($this->getWriterPart('Style')->allBorders($this->_spreadSheet)); $this->_numFmtHashTable->addFromSource($this->getWriterPart('Style')->allNumberFormats($this->_spreadSheet)); // Create drawing dictionary $this->_drawingHashTable->addFromSource($this->getWriterPart('Drawing')->allDrawings($this->_spreadSheet)); // Create new ZIP file and open it for writing $zipClass = PHPExcel_Settings::getZipClass(); $objZip = new $zipClass(); // Retrieve OVERWRITE and CREATE constants from the instantiated zip class // This method of accessing constant values from a dynamic class should work with all appropriate versions of PHP $ro = new ReflectionObject($objZip); $zipOverWrite = $ro->getConstant('OVERWRITE'); $zipCreate = $ro->getConstant('CREATE'); if (file_exists($pFilename)) { unlink($pFilename); } // Try opening the ZIP file if ($objZip->open($pFilename, $zipOverWrite) !== true) { if ($objZip->open($pFilename, $zipCreate) !== true) { throw new PHPExcel_Writer_Exception("Could not open " . $pFilename . " for writing."); } } // Add [Content_Types].xml to ZIP file $objZip->addFromString('[Content_Types].xml', $this->getWriterPart('ContentTypes')->writeContentTypes($this->_spreadSheet, $this->_includeCharts)); //if hasMacros, add the vbaProject.bin file, Certificate file(if exists) if ($this->_spreadSheet->hasMacros()) { $macrosCode = $this->_spreadSheet->getMacrosCode(); if (!is_null($macrosCode)) { // we have the code ? $objZip->addFromString('xl/vbaProject.bin', $macrosCode); //allways in 'xl', allways named vbaProject.bin if ($this->_spreadSheet->hasMacrosCertificate()) { //signed macros ? // Yes : add the certificate file and the related rels file $objZip->addFromString('xl/vbaProjectSignature.bin', $this->_spreadSheet->getMacrosCertificate()); $objZip->addFromString('xl/_rels/vbaProject.bin.rels', $this->getWriterPart('RelsVBA')->writeVBARelationships($this->_spreadSheet)); } } } //a custom UI in this workbook ? add it ("base" xml and additional objects (pictures) and rels) if ($this->_spreadSheet->hasRibbon()) { $tmpRibbonTarget = $this->_spreadSheet->getRibbonXMLData('target'); $objZip->addFromString($tmpRibbonTarget, $this->_spreadSheet->getRibbonXMLData('data')); if ($this->_spreadSheet->hasRibbonBinObjects()) { $tmpRootPath = dirname($tmpRibbonTarget) . '/'; $ribbonBinObjects = $this->_spreadSheet->getRibbonBinObjects('data'); //the files to write foreach ($ribbonBinObjects as $aPath => $aContent) { $objZip->addFromString($tmpRootPath . $aPath, $aContent); } //the rels for files $objZip->addFromString($tmpRootPath . '_rels/' . basename($tmpRibbonTarget) . '.rels', $this->getWriterPart('RelsRibbonObjects')->writeRibbonRelationships($this->_spreadSheet)); } } // Add relationships to ZIP file $objZip->addFromString('_rels/.rels', $this->getWriterPart('Rels')->writeRelationships($this->_spreadSheet)); $objZip->addFromString('xl/_rels/workbook.xml.rels', $this->getWriterPart('Rels')->writeWorkbookRelationships($this->_spreadSheet)); // Add document properties to ZIP file $objZip->addFromString('docProps/app.xml', $this->getWriterPart('DocProps')->writeDocPropsApp($this->_spreadSheet)); $objZip->addFromString('docProps/core.xml', $this->getWriterPart('DocProps')->writeDocPropsCore($this->_spreadSheet)); $customPropertiesPart = $this->getWriterPart('DocProps')->writeDocPropsCustom($this->_spreadSheet); if ($customPropertiesPart !== NULL) { $objZip->addFromString('docProps/custom.xml', $customPropertiesPart); } // Add theme to ZIP file $objZip->addFromString('xl/theme/theme1.xml', $this->getWriterPart('Theme')->writeTheme($this->_spreadSheet)); // Add string table to ZIP file $objZip->addFromString('xl/sharedStrings.xml', $this->getWriterPart('StringTable')->writeStringTable($this->_stringTable)); // Add styles to ZIP file $objZip->addFromString('xl/styles.xml', $this->getWriterPart('Style')->writeStyles($this->_spreadSheet)); // Add workbook to ZIP file $objZip->addFromString('xl/workbook.xml', $this->getWriterPart('Workbook')->writeWorkbook($this->_spreadSheet, $this->_preCalculateFormulas)); $chartCount = 0; // Add worksheets for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) { $objZip->addFromString('xl/worksheets/sheet' . ($i + 1) . '.xml', $this->getWriterPart('Worksheet')->writeWorksheet($this->_spreadSheet->getSheet($i), $this->_stringTable, $this->_includeCharts)); if ($this->_includeCharts) { $charts = $this->_spreadSheet->getSheet($i)->getChartCollection(); if (count($charts) > 0) { foreach ($charts as $chart) { $objZip->addFromString('xl/charts/chart' . ($chartCount + 1) . '.xml', $this->getWriterPart('Chart')->writeChart($chart)); $chartCount++; } } } } $chartRef1 = $chartRef2 = 0; // Add worksheet relationships (drawings, ...) for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) { // Add relationships $objZip->addFromString('xl/worksheets/_rels/sheet' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeWorksheetRelationships($this->_spreadSheet->getSheet($i), $i + 1, $this->_includeCharts)); $drawings = $this->_spreadSheet->getSheet($i)->getDrawingCollection(); $drawingCount = count($drawings); if ($this->_includeCharts) { $chartCount = $this->_spreadSheet->getSheet($i)->getChartCount(); } // Add drawing and image relationship parts if ($drawingCount > 0 || $chartCount > 0) { // Drawing relationships $objZip->addFromString('xl/drawings/_rels/drawing' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeDrawingRelationships($this->_spreadSheet->getSheet($i), $chartRef1, $this->_includeCharts)); // Drawings $objZip->addFromString('xl/drawings/drawing' . ($i + 1) . '.xml', $this->getWriterPart('Drawing')->writeDrawings($this->_spreadSheet->getSheet($i), $chartRef2, $this->_includeCharts)); } // Add comment relationship parts if (count($this->_spreadSheet->getSheet($i)->getComments()) > 0) { // VML Comments $objZip->addFromString('xl/drawings/vmlDrawing' . ($i + 1) . '.vml', $this->getWriterPart('Comments')->writeVMLComments($this->_spreadSheet->getSheet($i))); // Comments $objZip->addFromString('xl/comments' . ($i + 1) . '.xml', $this->getWriterPart('Comments')->writeComments($this->_spreadSheet->getSheet($i))); } // Add header/footer relationship parts if (count($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages()) > 0) { // VML Drawings $objZip->addFromString('xl/drawings/vmlDrawingHF' . ($i + 1) . '.vml', $this->getWriterPart('Drawing')->writeVMLHeaderFooterImages($this->_spreadSheet->getSheet($i))); // VML Drawing relationships $objZip->addFromString('xl/drawings/_rels/vmlDrawingHF' . ($i + 1) . '.vml.rels', $this->getWriterPart('Rels')->writeHeaderFooterDrawingRelationships($this->_spreadSheet->getSheet($i))); // Media foreach ($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages() as $image) { $objZip->addFromString('xl/media/' . $image->getIndexedFilename(), file_get_contents($image->getPath())); } } } // Add media for ($i = 0; $i < $this->getDrawingHashTable()->count(); ++$i) { if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_Drawing) { $imageContents = null; $imagePath = $this->getDrawingHashTable()->getByIndex($i)->getPath(); if (strpos($imagePath, 'zip://') !== false) { $imagePath = substr($imagePath, 6); $imagePathSplitted = explode('#', $imagePath); $imageZip = new ZipArchive(); $imageZip->open($imagePathSplitted[0]); $imageContents = $imageZip->getFromName($imagePathSplitted[1]); $imageZip->close(); unset($imageZip); } else { $imageContents = file_get_contents($imagePath); } $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents); } else { if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_MemoryDrawing) { ob_start(); call_user_func($this->getDrawingHashTable()->getByIndex($i)->getRenderingFunction(), $this->getDrawingHashTable()->getByIndex($i)->getImageResource()); $imageContents = ob_get_contents(); ob_end_clean(); $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents); } } } PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); PHPExcel_Calculation::getInstance($this->_spreadSheet)->getDebugLog()->setWriteDebugLog($saveDebugLog); // Close file if ($objZip->close() === false) { throw new PHPExcel_Writer_Exception("Could not close zip file {$pFilename}."); } // If a temporary file was used, copy it to the correct file stream if ($originalFilename != $pFilename) { if (copy($pFilename, $originalFilename) === false) { throw new PHPExcel_Writer_Exception("Could not copy temporary zip file {$pFilename} to {$originalFilename}."); } @unlink($pFilename); } } else { throw new PHPExcel_Writer_Exception("PHPExcel object unassigned."); } }
/** * Save PHPExcel to file * * @param string $pFilename * * @throws PHPExcel_Writer_Exception */ public function save($pFilename = null) { // garbage collect $this->_phpExcel->garbageCollect(); $saveDebugLog = PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->getWriteDebugLog(); PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog(false); $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // initialize colors array $this->_colors = array(); // Initialise workbook writer $this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser); // Initialise worksheet writers $countSheets = $this->_phpExcel->getSheetCount(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i] = new PHPExcel_Writer_Excel5_Worksheet($this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_preCalculateFormulas, $this->_phpExcel->getSheet($i)); } // build Escher objects. Escher objects for workbooks needs to be build before Escher object for workbook. $this->_buildWorksheetEschers(); $this->_buildWorkbookEscher(); // add 15 identical cell style Xfs // for now, we use the first cellXf instead of cellStyleXf $cellXfCollection = $this->_phpExcel->getCellXfCollection(); for ($i = 0; $i < 15; ++$i) { $this->_writerWorkbook->addXfWriter($cellXfCollection[0], true); } // add all the cell Xfs foreach ($this->_phpExcel->getCellXfCollection() as $style) { $this->_writerWorkbook->addXfWriter($style, false); } // add fonts from rich text eleemnts for ($i = 0; $i < $countSheets; ++$i) { foreach ($this->_writerWorksheets[$i]->_phpSheet->getCellCollection() as $cellID) { $cell = $this->_writerWorksheets[$i]->_phpSheet->getCell($cellID); $cVal = $cell->getValue(); if ($cVal instanceof PHPExcel_RichText) { $elements = $cVal->getRichTextElements(); foreach ($elements as $element) { if ($element instanceof PHPExcel_RichText_Run) { $font = $element->getFont(); $this->_writerWorksheets[$i]->_fntHashIndex[$font->getHashCode()] = $this->_writerWorkbook->_addFont($font); } } } } } // initialize OLE file $workbookStreamName = 'Workbook'; $OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName)); // Write the worksheet streams before the global workbook stream, // because the byte sizes of these are needed in the global workbook stream $worksheetSizes = array(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i]->close(); $worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize; } // add binary data for global workbook stream $OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes)); // add binary data for sheet streams for ($i = 0; $i < $countSheets; ++$i) { $OLE->append($this->_writerWorksheets[$i]->getData()); } $this->_documentSummaryInformation = $this->_writeDocumentSummaryInformation(); // initialize OLE Document Summary Information if (isset($this->_documentSummaryInformation) && !empty($this->_documentSummaryInformation)) { $OLE_DocumentSummaryInformation = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs(chr(5) . 'DocumentSummaryInformation')); $OLE_DocumentSummaryInformation->append($this->_documentSummaryInformation); } $this->_summaryInformation = $this->_writeSummaryInformation(); // initialize OLE Summary Information if (isset($this->_summaryInformation) && !empty($this->_summaryInformation)) { $OLE_SummaryInformation = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs(chr(5) . 'SummaryInformation')); $OLE_SummaryInformation->append($this->_summaryInformation); } // define OLE Parts $arrRootData = array($OLE); // initialize OLE Properties file if (isset($OLE_SummaryInformation)) { $arrRootData[] = $OLE_SummaryInformation; } // initialize OLE Extended Properties file if (isset($OLE_DocumentSummaryInformation)) { $arrRootData[] = $OLE_DocumentSummaryInformation; } $root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), $arrRootData); // save the OLE file $res = $root->save($pFilename); PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog($saveDebugLog); }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // garbage collect $this->_phpExcel->garbageCollect(); $saveDebugLog = PHPExcel_Calculation::getInstance()->writeDebugLog; PHPExcel_Calculation::getInstance()->writeDebugLog = false; $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // initialize colors array $this->_colors = array(); // Initialise workbook writer $this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser); // Initialise worksheet writers $countSheets = $this->_phpExcel->getSheetCount(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i] = new PHPExcel_Writer_Excel5_Worksheet($this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_preCalculateFormulas, $this->_phpExcel->getSheet($i)); } // build Escher objects. Escher objects for workbooks needs to be build before Escher object for workbook. $this->_buildWorksheetEschers(); $this->_buildWorkbookEscher(); // add 15 identical cell style Xfs // for now, we use the first cellXf instead of cellStyleXf $cellXfCollection = $this->_phpExcel->getCellXfCollection(); for ($i = 0; $i < 15; ++$i) { $this->_writerWorkbook->addXfWriter($cellXfCollection[0], true); } // add all the cell Xfs foreach ($this->_phpExcel->getCellXfCollection() as $style) { $this->_writerWorkbook->addXfWriter($style, false); } // initialize OLE file $workbookStreamName = $this->_BIFF_version == 0x600 ? 'Workbook' : 'Book'; $OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName)); // Write the worksheet streams before the global workbook stream, // because the byte sizes of these are needed in the global workbook stream $worksheetSizes = array(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i]->close(); $worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize; } // add binary data for global workbook stream $OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes)); // add binary data for sheet streams for ($i = 0; $i < $countSheets; ++$i) { $OLE->append($this->_writerWorksheets[$i]->getData()); } $root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), array($OLE)); // save the OLE file $res = $root->save($pFilename); PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); PHPExcel_Calculation::getInstance()->writeDebugLog = $saveDebugLog; }
/** * 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) { // 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(); }