/**
* _getTimeValue
*
* @param string $timeValue
* @return mixed Excel date/time serial value, or string if error
*/
private static function _getTimeValue($timeValue)
{
$saveReturnDateType = PHPExcel_Calculation_Functions::getReturnDateType();
PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
$timeValue = self::TIMEVALUE($timeValue);
PHPExcel_Calculation_Functions::setReturnDateType($saveReturnDateType);
return $timeValue;
}
/**
* 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.");
}
}
/**
* 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();
}
}
/**
* 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;
}
/**
* 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));
}
/**
* 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 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 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;
}
public function testEOMONTHtoPHPObject()
{
PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT);
$result = PHPExcel_Calculation_DateTime::EOMONTH('2012-1-26', -1);
PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
// Must return an object...
$this->assertTrue(is_object($result));
// ... of the correct type
$this->assertTrue(is_a($result, 'DateTime'));
// ... with the correct value
$this->assertEquals($result->format('d-M-Y'), '31-Dec-2011');
}
/**
* 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)
{
$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();
}
