/tine20/Tinebase/Export/Spreadsheet/Xls.php
PHP | 421 lines | 236 code | 65 blank | 120 comment | 45 complexity | 5465a9765b78dddd452524282281eeab MD5 | raw file
- <?php
- /**
- * Tinebase xls generation class
- *
- * @package Tinebase
- * @subpackage Export
- * @license http://www.gnu.org/licenses/agpl.html AGPL Version 3
- * @author Philipp Schüle <p.schuele@metaways.de>
- * @copyright Copyright (c) 2009-2011 Metaways Infosystems GmbH (http://www.metaways.de)
- *
- */
- // set include path for phpexcel
- set_include_path(dirname(dirname(dirname(dirname(__FILE__)))) . '/library/PHPExcel' . PATH_SEPARATOR . get_include_path() );
- /**
- * Tinebase xls generation class
- *
- * @package Tinebase
- * @subpackage Export
- *
- */
- class Tinebase_Export_Spreadsheet_Xls extends Tinebase_Export_Spreadsheet_Abstract implements Tinebase_Record_IteratableInterface
- {
- /**
- * current row number
- *
- * @var integer
- */
- protected $_currentRowIndex = 0;
-
- /**
- * the phpexcel object
- *
- * @var PHPExcel
- */
- protected $_excelObject = NULL;
-
- /**
- * format strings
- *
- * @var string
- */
- protected $_format = 'xls';
-
- /**
- * generate export
- *
- * @return PHPExcel
- */
- public function generate()
- {
- $this->_createDocument();
- $this->_setDocumentProperties();
-
- $this->_addHeader();
- $this->_exportRecords();
-
- $this->_setColumnWidths();
- return $this->getDocument();
- }
-
- /**
- * sets the colunm widths by config column->width
- */
- protected function _setColumnWidths()
- {
- $index = 0;
- foreach($this->_config->columns->column as $field) {
- if ($this->_groupBy !== NULL && $this->_groupBy == $field->identifier) {
- continue;
- }
- if (isset($field->width)) {
- $this->_excelObject->getActiveSheet()->getColumnDimensionByColumn($index)->setWidth((string) $field->width);
- }
- $index++;
- }
- }
- /**
- * add header
- */
- protected function _addHeader()
- {
- $patterns = array(
- '/\{date\}/',
- '/\{user\}/',
- );
- $replacements = array(
- Zend_Date::now()->toString(Zend_Locale_Format::getDateFormat($this->_locale), $this->_locale),
- Tinebase_Core::getUser()->accountDisplayName,
- );
- $this->_currentRowIndex = 1;
- $columnId = 0;
- if ($this->_config->headers) {
- foreach($this->_config->headers->header as $headerCell) {
- // replace data
- $value = preg_replace($patterns, $replacements, $headerCell);
- $this->_excelObject->getActiveSheet()->setCellValueByColumnAndRow(0, $this->_currentRowIndex, $value);
- $this->_currentRowIndex++;
- }
- $this->_currentRowIndex++;
- }
- if (isset($this->_config->header) && $this->_config->header) {
- $this->_addHead();
- }
- }
-
- /**
- * get excel object
- *
- * @return PHPExcel
- */
- public function getDocument()
- {
- return $this->_excelObject;
- }
-
- /**
- * get export content type
- *
- * @return string
- */
- public function getDownloadContentType()
- {
- $contentType = ($this->_config->writer == 'Excel2007')
- // Excel 2007 content type
- ? 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
- // Excel 5 content type or other
- : 'application/vnd.ms-excel';
-
- return $contentType;
- }
-
- /**
- * return download filename
- * @param string $_appName
- * @param string $_format
- */
- public function getDownloadFilename($_appName, $_format)
- {
- $result = parent::getDownloadFilename($_appName, $_format);
-
- if ($this->_config->writer == 'Excel2007') {
- // excel2007 extension is .xlsx
- $result .= 'x';
- }
-
- return $result;
- }
-
- /**
- * output result
- */
- public function write()
- {
- $xlsFormat = ($this->_config->writer) ? $this->_config->writer : 'Excel5';
- Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' Creating and sending xls to client (Format: ' . $xlsFormat . ').');
- $xlswriter = PHPExcel_IOFactory::createWriter($this->_excelObject, $xlsFormat);
-
- // precalcualting formula values costs tons of time, because sum formulas are like SUM C1:C65000
- $xlswriter->setPreCalculateFormulas(FALSE);
-
- $xlswriter->save('php://output');
- }
-
- /**
- * create new excel document
- *
- * @return void
- */
- protected function _createDocument()
- {
- $templateFile = $this->_getTemplateFilename();
-
- if ($templateFile !== NULL) {
-
- if (! $this->_config->reader || $this->_config->reader == 'autodetection') {
- $this->_excelObject = PHPExcel_IOFactory::load($templateFile);
- } else {
- $reader = PHPExcel_IOFactory::createReader($this->_config->reader);
- $this->_excelObject = $reader->load($templateFile);
- }
-
- // need to unregister the zip stream wrapper because it is overwritten by PHPExcel!
- // TODO file a bugreport to PHPExcel
- @stream_wrapper_restore("zip");
-
- $activeSheet = isset($this->_config->sheet) ? $this->_config->sheet : 1;
- $this->_excelObject->setActiveSheetIndex($activeSheet);
- } else {
- Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' Creating new PHPExcel object.');
- $this->_excelObject = new PHPExcel();
- }
- }
-
- /**
- * get cell value
- *
- * @param Zend_Config $_field
- * @param Tinebase_Record_Interface $_record
- * @param string $_cellType
- * @return string
- */
- protected function _getCellValue(Zend_Config $_field, Tinebase_Record_Interface $_record, &$_cellType)
- {
- switch($_field->type) {
- case 'datetime':
- case 'date':
- if ($_record->{$_field->identifier} instanceof DateTime) {
- if (! isset($_field->timestamp) || $_field->timestamp == 1) {
- $result = PHPExcel_Shared_Date::PHPToExcel($_record->{$_field->identifier}->getTimestamp());
- } else {
- $result = parent::_getCellValue($_field, $_record, $_cellType);
- }
- } else {
- $result = $_record->{$_field->identifier};
- }
-
- // empty date cells, get displayed as 30.12.1899
- if(empty($result)) {
- $result = NULL;
- }
- break;
- default:
- $result = parent::_getCellValue($_field, $_record, $_cellType);
- break;
- }
-
- return $result;
- }
-
- /**
- * set properties
- *
- * @return void
- */
- protected function _setDocumentProperties()
- {
- // set metadata/properties
- if ($this->_config->writer == 'Excel2007') {
- $this->_excelObject->getProperties()
- ->setCreator(Tinebase_Core::getUser()->accountDisplayName)
- ->setLastModifiedBy(Tinebase_Core::getUser()->accountDisplayName)
- ->setTitle('Tine 2.0 ' . $this->_applicationName . ' Export')
- ->setSubject('Office 2007 XLSX Test Document')
- ->setDescription('Export for ' . $this->_applicationName . ', generated using PHP classes.')
- ->setKeywords("tine20 openxml php")
- ->setCreated(Zend_Date::now()->get());
- //if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' ' . print_r($this->_excelObject->getProperties(), true));
- }
- }
-
- /**
- * add xls head (headline, column styles)
- */
- protected function _addHead()
- {
- $columnId = 0;
- foreach($this->_config->columns->column as $field) {
- if ($this->_groupBy !== NULL && $this->_groupBy == $field->identifier) {
- continue;
- }
- $headerValue = ($field->header) ? $this->_translate->translate($field->header) : $field->identifier;
- $this->_excelObject->getActiveSheet()->setCellValueByColumnAndRow($columnId++, $this->_currentRowIndex, $headerValue);
- }
-
- $this->_currentRowIndex++;
- }
-
- /**
- * adds a header for each group
- *
- * @param Tinebase_Record_Interface $record
- */
- protected function _addGroupHeader($group)
- {
- // find out fieldconfig, if not found already
- if (! $this->_groupByFieldConfig) {
- $this->_columnCount = 0;
- foreach ($this->_config->columns->column as $field) {
- if ($field->identifier == $this->_groupBy) {
- $this->_groupByFieldConfig = $field;
- $this->_groupByFieldType = (isset($field->type)) ? $field->type : 'string';
- }
- $this->_columnCount++;
- }
- } else {
- $this->_currentRowIndex++;
- $this->_currentRowIndex++;
- }
- $fontColor = 'b79511';
- $backgroundColor = '008bcf';
- $fontSize = 16;
- if ($this->_config->grouping->groupheader) {#
- $gh = $this->_config->grouping->groupheader;
- $fontColor = $gh->fontcolor ? (string) $gh->fontcolor : $fontColor;
- $backgroundColor = $gh->backgroundcolor ? (string) $gh->backgroundcolor : $backgroundColor;
- $fontSize = $gh->fontsize ? (int) $gh->fontsize : $fontSize;
- }
- $cell = $this->_excelObject->getActiveSheet()->setCellValueByColumnAndRow(0, $this->_currentRowIndex, $group, TRUE);
- $styleArray = array(
- 'font' => array(
- 'bold' => true,
- 'color' => array('rgb' => $fontColor),
- 'size' => $fontSize,
- ),
- 'fill' => array(
- 'type' => PHPExcel_Style_Fill::FILL_SOLID,
- 'color' => array('rgb' => $backgroundColor)
- )
- );
- $this->_excelObject->getActiveSheet()->getStyle($cell->getCoordinate())->applyFromArray($styleArray);
- $this->_excelObject->getActiveSheet()->mergeCellsByColumnAndRow(0, $this->_currentRowIndex, ($this->_columnCount - 2), $this->_currentRowIndex);
- $this->_currentRowIndex++;
- if ($this->_config->grouping->header) {
- $this->_addHead();
- }
- $this->_currentRowIndex++;
- }
- /**
- * (non-PHPdoc)
- * @see Tinebase_Export_Abstract::_onAfterExportRecords()
- */
- protected function _onAfterExportRecords($result)
- {
- // save number of records (only if we have more than 1 sheets / records are on the second sheet by default)
- if ($this->_excelObject->getSheetCount() > 1) {
- $this->_excelObject->setActiveSheetIndex(0);
- $this->_excelObject->getActiveSheet()->setCellValueByColumnAndRow(5, 2, $result['totalcount']);
- }
- }
- /**
- * add body rows
- *
- * @param Tinebase_Record_RecordSet $records
- *
- * @todo add formulas
- */
- public function processIteration($_records)
- {
- $this->_resolveRecords($_records);
-
- $lastGroup = NULL;
- $woString = $this->_translate->_('Without company assigned');
- // add record rows
- $i = 0;
- foreach ($_records as $record) {
- if ($this->_groupBy !== NULL && $lastGroup !== $record->{$this->_groupBy}
- && (! (empty($record->{$this->_groupBy}) && $record->{$this->_groupBy} == $woString)))
- {
- $lastGroup = empty($record->{$this->_groupBy}) ? $woString : $record->{$this->_groupBy};
- $this->_addGroupHeader($lastGroup);
- }
-
- $columnId = 0;
-
- foreach ($this->_config->columns->column as $field) {
- // don't show group by field
- if ($this->_groupBy !== NULL && $field->identifier == $this->_groupBy) {
- continue;
- }
- // get type and value for cell
- $cellType = (isset($field->type)) ? $field->type : 'string';
- $cellValue = $this->_getCellValue($field, $record, $cellType);
-
- // add formula
- if ($field->formula) {
- //if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Adding formula: ' . $field->formula);
- $cellValue = $field->formula;
- }
-
- $this->_excelObject->getActiveSheet()->setCellValueByColumnAndRow($columnId++, $this->_currentRowIndex, $cellValue);
- }
-
- $i++;
- $this->_currentRowIndex++;
- }
- }
- /**
- * (non-PHPdoc)
- * @see Tinebase_Export_Abstract::_exportRecords()
- */
- protected function _exportRecords()
- {
- parent::_exportRecords();
- $sheet = $this->_excelObject->getActiveSheet();
- for ($i = 0; $i < $this->_columnCount; $i++) {
- $sheet->getColumnDimension($i)->setAutoSize(TRUE);
- }
- }
- }