/** * Format a recordset * * @param Garp_Model $model * @param array $rowset * @return string */ public function format(Garp_Model $model, array $rowset) { $phpexcel = new PHPExcel(); PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); // set metadata $props = $phpexcel->getProperties(); if (Garp_Auth::getInstance()->isLoggedIn()) { $userData = Garp_Auth::getInstance()->getUserData(); $bootstrap = Zend_Controller_Front::getInstance()->getParam('bootstrap'); if ($bootstrap) { $view = $bootstrap->getResource('view'); $userName = $view->fullName($userData); $props->setCreator($userName)->setLastModifiedBy($userName); } } $props->setTitle('Garp content export – ' . $model->getName()); if (count($rowset)) { $this->_addContent($phpexcel, $model, $rowset); } /** * Hm, PHPExcel seems to only be able to write to a file (instead of returning * an XLS binary string). Therefore, we save a temporary file, read its contents * and return those, after which we unlink the temp file. */ $tmpFileName = APPLICATION_PATH . '/data/logs/tmp.xls'; $writer = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5'); $writer->save($tmpFileName); $contents = file_get_contents($tmpFileName); unlink($tmpFileName); return $contents; }
public function __construct($config = array()) { PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); if (isset($config['cacheMethod'])) { $this->_cacheMethod = $config['cacheMethod']; } $this->_setCache(); parent::__construct(); }
/** * Return the template * @param string $templateFileName * @return PHPExcel */ protected function getTemplate($templateFileName) { $startTime = microtime(true); require_once __DIR__ . '/PHPExcel/PHPExcel.php'; require_once __DIR__ . '/PHPExcel/PHPExcel/Cell/AdvancedValueBinder.php'; PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); $this->logger->log(LOG_INFO, 'getTemplate()'); $this->logger->step(R3_PAES_READ_TEMPLATE, null, null); $this->xls = PHPExcel_IOFactory::load($templateFileName); $this->logger->log(LOG_INFO, sprintf('getTemplate() - Done [%.2fsec]', microtime(true) - $startTime)); }
/** * Constructor * * @param \Cake\Network\Request $request Request instance. * @param \Cake\Network\Response $response Response instance. * @param \Cake\Event\EventManager $eventManager Event manager instance. * @param array $viewOptions View options. See View::$_passedVars for list of * options which get set as class properties. * * @throws \Cake\Core\Exception\Exception */ public function __construct(Request $request = null, Response $response = null, EventManager $eventManager = null, array $viewOptions = []) { parent::__construct($request, $response, $eventManager, $viewOptions); if (isset($viewOptions['name']) && $viewOptions['name'] == 'Error') { $this->subDir = null; $this->layoutPath = null; $response->type('html'); return; } \PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder()); $this->PhpExcel = new \PHPExcel(); }
function file_to_obj_php_excel($inputFileName) { $CI =& get_instance(); PHPExcel_Shared_File::setUseUploadTempDirectory(true); if ($CI->config->item('spreadsheet_format') == 'XLSX') { $objReader = new PHPExcel_Reader_Excel2007(); } else { $objReader = new PHPExcel_Reader_CSV(); PHPExcel_Cell::setValueBinder(new TextValueBinder()); } $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($inputFileName); return $objPHPExcel; }
/** * Constructor * * @param \Cake\Network\Request $request Request instance. * @param \Cake\Network\Response $response Response instance. * @param \Cake\Event\EventManager $eventManager Event manager instance. * @param array $viewOptions View options. See View::$_passedVars for list of * options which get set as class properties. * * @throws \Cake\Core\Exception\Exception */ public function __construct(Request $request = null, Response $response = null, EventManager $eventManager = null, array $viewOptions = []) { parent::__construct($request, $response, $eventManager, $viewOptions); if (isset($viewOptions['templatePath']) && $viewOptions['templatePath'] == 'Error') { $this->layoutPath = null; $this->subDir = null; $response->type('html'); return; } // intitialize PHPExcel-Object \PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder()); $this->PHPExcel = new \PHPExcel(); $this->currentSheetIndex = 0; }
/** * reads a file PHPExcel can understand and converts a contained worksheet into an array * which can be used to build entities. If the File contains more than one worksheet and it is not named like the Controller * you have to provide the name of the workshhet to load in the options array. * If you set $options['append'] to true, the primary key will be deleted. * @todo Find a way to make to handle primary keys anmed other than id. * * @param string $file name of Excel-File with full path. Must be of a readable Filetype (xls, xlsx, csv, ods) * @param array $options Override Worksheet name, set append Mode * @return array . The Array has the same structure as provided by request->data * @throws MissingTableClassException */ public function prepareEntityData($file = null, array $options = []) { /** load and configure PHPExcelReader * */ \PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder()); $fileType = \PHPExcel_IOFactory::identify($file); $PhpExcelReader = \PHPExcel_IOFactory::createReader($fileType); $PhpExcelReader->setReadDataOnly(true); if ($fileType !== 'CSV') { // csv-files have only one 'worksheet' /** identify worksheets in file * */ $worksheets = $PhpExcelReader->listWorksheetNames($file); $worksheetToLoad = null; if (count($worksheets) === 1) { $worksheetToLoad = $worksheets[0]; //first option: if there is only one worksheet, use it } elseif (isset($options['worksheet'])) { $worksheetToLoad = $options['worksheet']; //second option: desired worksheet was provided as option } else { $worksheetToLoad = $this->_registry->getController()->name; //last option: try to load worksheet with the name of current controller } if (!in_array($worksheetToLoad, $worksheets)) { throw new MissingTableClassException(__('No proper named worksheet found')); } /** load the sheet and convert data to an array */ $PhpExcelReader->setLoadSheetsOnly($worksheetToLoad); } $PhpExcel = $PhpExcelReader->load($file); $data = $PhpExcel->getSheet(0)->toArray(); /** convert data for building entities */ $result = []; $properties = array_shift($data); //first row columns are the properties foreach ($data as $row) { $record = array_combine($properties, $row); if (isset($record['modified'])) { unset($record['modified']); } if (isset($options['type']) && $options['type'] == 'append' && isset($record['id'])) { unset($record['id']); } $result[] = $record; } /** log in debug mode */ $this->log(count($result) . ' records were extracted from File ' . $file, 'debug'); return $result; }
public function report($dates, $officials) { $results = new AvailReporterResults(); // For wrapping text \PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder()); $this->wb = $wb = new \PHPExcel(); $ws = $wb->getSheet(); $ws->setTitle('RefAvail'); $ws->getCell('A1')->setValue('Referee Name'); $ws->getCell('B1')->setValue('Referee Info'); $ws->getColumnDimension('A')->setWidth(20); $ws->getColumnDimension('B')->setWidth(20); $col = 'C'; foreach ($dates as $date) { $dt = \DateTime::createFromFormat('Y-m-d', $date); $ws->getCell($col . '1')->setValue($dt->format('D M d')); $ws->getColumnDimension($col)->setWidth(25); $col++; } $row = 2; foreach ($officials as $official) { $ws->getCell('A' . $row)->setValue($official['name']); $info = sprintf("F: %s\n%s\n%s\nR: %s", $official['city'], $official['cell'], $official['home'], $official['rank']); $ws->getCell('B' . $row)->setValue($info); $col = 'C'; foreach ($dates as $date) { $cr = $col . $row; $avail = implode("\n", $official['avail'][$date]); $ws->getCell($cr)->setValue($avail); if ($avail === 'Blocked ALL DAY') { $style = $ws->getStyle($cr); $fill = $style->getFill(); $fill->setFillType(\PHPExcel_Style_Fill::FILL_SOLID); $fill->getStartColor()->setARGB('FFFF6666'); } if ($avail === 'Open All Day') { $style = $ws->getStyle($cr); $fill = $style->getFill(); $fill->setFillType(\PHPExcel_Style_Fill::FILL_SOLID); $fill->getStartColor()->setARGB('FFCCFFCC'); } $col++; } $row++; } $wb->setActiveSheetIndex(0); return $results; }
/** * Set data to active sheet. * * @param array $data * @param array $title * @param PHPExcel_Chart $charts */ public function setData($data, $title = array(), $charts = null) { if (!empty($title)) { array_unshift($data, $title); } PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_RCUPValueBinder()); $this->objPHPExcel->getActiveSheet()->fromArray($data); if (!empty($charts)) { if (!is_array($charts)) { $charts = array($charts); } foreach ($charts as $chart) { $this->objPHPExcel->getActiveSheet()->addChart($chart); } } }
public function download($export_type, $offset = null, $rows = null, $min_id = null, $max_id = null) { // we use our own error handler global $registry; $registry = $this->registry; set_error_handler('error_handler_for_export_import', E_ALL); register_shutdown_function('fatal_error_shutdown_handler_for_export_import'); // Use the PHPExcel package from http://phpexcel.codeplex.com/ $cwd = getcwd(); chdir(DIR_SYSTEM . 'PHPExcel'); require_once 'Classes/PHPExcel.php'; PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_ExportImportValueBinder()); chdir($cwd); // find out whether all data is to be downloaded $all = !isset($offset) && !isset($rows) && !isset($min_id) && !isset($max_id); // Memory Optimization if ($this->config->get('export_import_settings_use_export_cache')) { $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; $cacheSettings = array('memoryCacheSize' => '16MB'); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); } try { // set appropriate timeout limit set_time_limit(1800); $languages = $this->getLanguages(); $default_language_id = $this->getDefaultLanguageId(); // create a new workbook $workbook = new PHPExcel(); // set some default styles $workbook->getDefaultStyle()->getFont()->setName('Arial'); $workbook->getDefaultStyle()->getFont()->setSize(10); //$workbook->getDefaultStyle()->getAlignment()->setIndent(0.5); $workbook->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $workbook->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $workbook->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL); // pre-define some commonly used styles $box_format = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'F0F0F0'))); $text_format = array('numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_TEXT)); $price_format = array('numberformat' => array('code' => '######0.00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)); $weight_format = array('numberformat' => array('code' => '##0.00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)); // create the worksheets $worksheet_index = 0; switch ($export_type) { case 'c': // creating the Categories worksheet $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Categories'); $this->populateCategoriesWorksheet($worksheet, $languages, $box_format, $text_format, $offset, $rows, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the CategoryFilters worksheet if ($this->existFilter()) { $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('CategoryFilters'); $this->populateCategoryFiltersWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); } break; case 'p': // creating the Products worksheet $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Products'); $this->populateProductsWorksheet($worksheet, $languages, $default_language_id, $price_format, $box_format, $weight_format, $text_format, $offset, $rows, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the AdditionalImages worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('AdditionalImages'); $this->populateAdditionalImagesWorksheet($worksheet, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the Specials worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Specials'); $this->populateSpecialsWorksheet($worksheet, $default_language_id, $price_format, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the Discounts worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Discounts'); $this->populateDiscountsWorksheet($worksheet, $default_language_id, $price_format, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the Rewards worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Rewards'); $this->populateRewardsWorksheet($worksheet, $default_language_id, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the ProductOptions worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('ProductOptions'); $this->populateProductOptionsWorksheet($worksheet, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the ProductOptionValues worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('ProductOptionValues'); $this->populateProductOptionValuesWorksheet($worksheet, $price_format, $box_format, $weight_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the ProductAttributes worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('ProductAttributes'); $this->populateProductAttributesWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the ProductFilters worksheet if ($this->existFilter()) { $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('ProductFilters'); $this->populateProductFiltersWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id); $worksheet->freezePaneByColumnAndRow(1, 2); } break; case 'o': // creating the Options worksheet $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Options'); $this->populateOptionsWorksheet($worksheet, $languages, $box_format, $text_format); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the OptionValues worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('OptionValues'); $this->populateOptionValuesWorksheet($worksheet, $languages, $box_format, $text_format); $worksheet->freezePaneByColumnAndRow(1, 2); break; case 'a': // creating the AttributeGroups worksheet $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('AttributeGroups'); $this->populateAttributeGroupsWorksheet($worksheet, $languages, $box_format, $text_format); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the Attributes worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Attributes'); $this->populateAttributesWorksheet($worksheet, $languages, $box_format, $text_format); $worksheet->freezePaneByColumnAndRow(1, 2); break; case 'f': if (!$this->existFilter()) { throw new Exception($this->language->get('error_filter_not_supported')); break; } // creating the FilterGroups worksheet $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('FilterGroups'); $this->populateFilterGroupsWorksheet($worksheet, $languages, $box_format, $text_format); $worksheet->freezePaneByColumnAndRow(1, 2); // creating the Filters worksheet $workbook->createSheet(); $workbook->setActiveSheetIndex($worksheet_index++); $worksheet = $workbook->getActiveSheet(); $worksheet->setTitle('Filters'); $this->populateFiltersWorksheet($worksheet, $languages, $box_format, $text_format); $worksheet->freezePaneByColumnAndRow(1, 2); break; default: break; } $workbook->setActiveSheetIndex(0); // redirect output to client browser $datetime = date('Y-m-d'); switch ($export_type) { case 'c': $filename = 'categories-' . $datetime; if (!$all) { if (isset($offset)) { $filename .= "-offset-{$offset}"; } else { if (isset($min_id)) { $filename .= "-start-{$min_id}"; } } if (isset($rows)) { $filename .= "-rows-{$rows}"; } else { if (isset($max_id)) { $filename .= "-end-{$max_id}"; } } } $filename .= '.xlsx'; break; case 'p': $filename = 'products-' . $datetime; if (!$all) { if (isset($offset)) { $filename .= "-offset-{$offset}"; } else { if (isset($min_id)) { $filename .= "-start-{$min_id}"; } } if (isset($rows)) { $filename .= "-rows-{$rows}"; } else { if (isset($max_id)) { $filename .= "-end-{$max_id}"; } } } $filename .= '.xlsx'; break; case 'o': $filename = 'options-' . $datetime . '.xlsx'; break; case 'a': $filename = 'attributes-' . $datetime . '.xlsx'; break; case 'f': if (!$this->existFilter()) { throw new Exception($this->language->get('error_filter_not_supported')); break; } $filename = 'filters-' . $datetime . '.xlsx'; break; default: $filename = $datetime . '.xlsx'; break; } header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $filename . '"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($workbook, 'Excel2007'); $objWriter->setPreCalculateFormulas(false); $objWriter->save('php://output'); // Clear the spreadsheet caches $this->clearSpreadsheetCache(); exit; } catch (Exception $e) { $errstr = $e->getMessage(); $errline = $e->getLine(); $errfile = $e->getFile(); $errno = $e->getCode(); $this->session->data['export_import_error'] = array('errstr' => $errstr, 'errno' => $errno, 'errfile' => $errfile, 'errline' => $errline); if ($this->config->get('config_error_log')) { $this->log->write('PHP ' . get_class($e) . ': ' . $errstr . ' in ' . $errfile . ' on line ' . $errline); } return; } }
/** * Returns an initialized PHPExcel object * * @return \PHPExcel */ private function getPhpExcelObject() { require_once ROOT . DS . 'vendor' . DS . 'phpoffice' . DS . 'phpexcel' . DS . 'Classes' . DS . 'PHPExcel.php'; \PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder()); $objPHPExcel = new \PHPExcel(); $objPHPExcel->setActiveSheetIndex(0); return $objPHPExcel; }
function change_xlsx($file, $docname, $leadData, $ddtype) { //@file_put_contents("now-wc.txt","point2:change_xlsx:".$docname,FILE_APPEND); if (is_file('files/' . $docname)) { unlink('files/' . $docname); } //if (!copy($file, 'files/'.$docname)) { } else { if (!copy($file, $docname)) { } else { //продолжаем обработку //работа с xlsx ini_set('include_path', ini_get('include_path') . ';../PHPExcel-1.8/Classes/'); require_once './PHPExcel-1.8/Classes/PHPExcel.php'; require_once './PHPExcel-1.8/Classes/PHPExcel/Writer/Excel2007.php'; require_once './PHPExcel-1.8/Classes/PHPExcel/IOFactory.php'; require_once './PHPExcel-1.8/Classes/PHPExcel/Cell/AdvancedValueBinder.php'; require_once './PHPExcel-1.8/Classes/PHPExcel/Writer/IWriter.php'; $objPHPExcel = new PHPExcel(); $objReader = PHPExcel_IOFactory::createReader('Excel2007'); $filenamestr = $docname; //$filenamestr = "invoice1.xlsx"; $objPHPExcel = $objReader->load($filenamestr); PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); // Add some data //insert row //$objPHPExcel->getActiveSheet()->insertNewRowBefore(7, 2); //объединить ячейки //$objPHPExcel->getActiveSheet()->mergeCells('A18:E22'); $objPHPExcel->setActiveSheetIndex(0); $dname = ""; if ($ddtype == 'tradeact') { $dname = 'Акт'; } else { $dname = 'Счет'; } $strB11val = $dname . " №" . $leadData['ddnumber'] . ' от ' . $leadData['ddate']; $objPHPExcel->getActiveSheet()->SetCellValue('B11', $strB11val); $objPHPExcel->getActiveSheet()->SetCellValue('G17', "" . $leadData['recieve']); $objPHPExcel->getActiveSheet()->SetCellValue('G19', "" . $leadData['recieve']); $mainarr = explode("!-!", $leadData['alltoinvioce']); $i = 0; $inum = 22; foreach ($mainarr as $elementarr) { if ("" . $elementarr === "") { } else { $inum = 22 + $i; $objPHPExcel->getActiveSheet()->insertNewRowBefore($inum, 1); //1 row before22 $strB = 'B' . $inum; $strBVal = "" . ($i + 1); $strC = 'C' . $inum; $strCVal = "" . $elementarr; $strW = 'W' . $inum; $strX = 'X' . $inum; $strY = 'Y' . $inum; $strZ = 'Z' . $inum; $strAA = 'AA' . $inum; $objPHPExcel->getActiveSheet()->SetCellValue($strB, $strBVal); $objPHPExcel->getActiveSheet()->SetCellValue($strC, $strCVal); $objPHPExcel->getActiveSheet()->SetCellValue($strX, ''); $objPHPExcel->getActiveSheet()->SetCellValue($strY, ''); $objPHPExcel->getActiveSheet()->SetCellValue($strZ, ''); $objPHPExcel->getActiveSheet()->SetCellValue($strAA, ''); //слипляем 2 строчки $objPHPExcel->getActiveSheet()->mergeCells($strC . ':' . $strW); $i++; } } //стили таблички $styleArray = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FFFF0000')))); $stylerange = 'B23:AA' . $i; $objPHPExcel->getActiveSheet()->getStyle($stylerange)->applyFromArray($styleArray); //стили таблички =============== $inum = 22 + $i + 4; $strB = 'B' . $inum; $objPHPExcel->getActiveSheet()->SetCellValue($strB, 'Всего наименований ' . $i . ', на сумму'); $inum = 22 + $i + 5; $strB = 'B' . $inum; $objPHPExcel->getActiveSheet()->SetCellValue($strB, ''); // Rename sheet $objPHPExcel->getActiveSheet()->setTitle('invoice'); // Save Excel 2007 file $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save("files/" . $docname); unset($objPHPExcel); unset($objReader); if (is_file($docname)) { unlink($docname); } } }
public static function setColWidth(&$objPHPExcel, $width, $col, $sheet = 0) { \PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder()); $objPHPExcel->setActiveSheetIndex($sheet)->getColumnDimension($col)->setWidth($width); }
/** * function setValueBinder() = Set the 'default' value binder to be used by objects generated * whose file handler was instantiated by this factory class * Cyril Ogana - 2012-07-17 * @param string $binderName This is the name of the value binder to activate * @return void * @access public */ public function setValueBinder($binderName = 'default') { if (!is_string($binderName) || !array_key_exists($binderName, self::$valueBinders)) { return false; } \PHPExcel_Cell::setValueBinder(new self::$valueBinders[$binderName]()); self::$valueBinder = $binderName; //append binder name to static property return true; }
/** * Create sheet from query and dump named file to browser. * @param $query the query. */ function writeXlsx($query) { PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); $objPHPExcel = new PHPExcel(); if (!isset($this->rowParser)) { $this->rowParser = new DefaultRowParser(); } $objPHPExcel->getProperties()->setCreator($this->creator); $objPHPExcel->getProperties()->setLastModifiedBy($this->author); $objPHPExcel->getProperties()->setTitle($this->title); $objPHPExcel->getProperties()->setSubject($this->subject); $objPHPExcel->getProperties()->setDescription($this->description); $objPHPExcel->getProperties()->setKeywords($this->keywords); $objPHPExcel->getProperties()->setCategory($this->catagory); global $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; $resultSet = $this->dbConn->Execute($query); if ($resultSet === false) { die("<br>Cannot get spreadsheet data with <pre>" . $query . "</pre> reason " . $this->dbConn->ErrorMsg() . "<br>"); } //echo $query; //$colcount = $resultSet->FieldCount(); // start writing in 3rd row, top isf for title and link. $row = 3; $this->tableHeader = $this->rowParser->parseToTableHeader($resultSet); $headCount = count($this->tableHeader); $headerStyles = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'color' => array('argb' => 'FFC0C0C0'))); for ($i = 0; $i < $headCount; $i++) { $name = $this->tableHeader[$i]; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $row, $name); $coor = XLSWriter::cellCoordinate($i, $row); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); } $row++; // get types $this->columnTypes = $this->rowParser->parseTypes($resultSet); $XlsTypes = array(); //error_log('there are ' . count($this->columnTypes) . ' types from db =' . print_r($this->columnTypes, true), 0); for ($i = 0; $i < count($this->columnTypes); $i++) { $ftype = PHPExcel_Cell_DataType::TYPE_NUMERIC; //error_log("found type = {$this->columnTypes[$i]} for column {$i}", 0); switch ($this->columnTypes[$i]) { case 'char': case 'bpchar': case 'varchar': case 'text': case 'date': $ftype = PHPExcel_Cell_DataType::TYPE_STRING; break; case 'int2': case 'int4': case 'int8': case '_numeric': case 'numeric': case 'float8': $ftype = PHPExcel_Cell_DataType::TYPE_NUMERIC; break; default: $ftype = PHPExcel_Cell_DataType::TYPE_STRING; break; } $XlsTypes[] = $ftype; } $cellStyleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'color' => array('argb' => 'FF0000'))); $oldValue = ''; if ($this->firstWeightColumn > 0) { // add weights row $this->weigthsRow = $row; $coor = XLSWriter::cellCoordinate($this->firstWeightColumn - 1, $row); $objPHPExcel->getActiveSheet()->setCellValue($coor, 'Weights', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); $weightSum = 0; $w = 0; ${$weightLast} = count($this->weights) - 1; for (; $w < count($this->weights); $w++) { $coor = XLSWriter::cellCoordinate($this->firstWeightColumn + $w, $row); $weightSum += $this->weights[$w]; $objPHPExcel->getActiveSheet()->setCellValue($coor, $this->weights[$w], PHPExcel_Cell_DataType::TYPE_NUMERIC); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); } $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row); $wBegin = XLSWriter::cellCoordinate($this->firstWeightColumn, $row); $wEnd = XLSWriter::cellCoordinate($this->firstWeightColumn + ${$weightLast}, $row); $formula = "=SUM({$wBegin}:{$wEnd})"; $objPHPExcel->getActiveSheet()->setCellValue($coor, $formula, PHPExcel_Cell_DataType::TYPE_FORMULA); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row - 1); $objPHPExcel->getActiveSheet()->setCellValue($coor, 'Total WT', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); $row++; } while (!$resultSet->EOF) { $rowData = $this->rowParser->parse($resultSet); $headCount = count($this->tableHeader); //$resultSet->FieldCount(); $changeColor = false; if ($this->colorChangerColumn >= 0) { if ($oldValue != $rowData[$this->colorChangerColumn]) { $changeColor = true; $oldValue = $rowData[$this->colorChangerColumn]; } } else { if ($this->autoZebra) { $changeColor = true; } } if ($changeColor) { $cellStyleArray['fill']['color']['argb'] = $this->rainBow->getCurrentAsARGBString(); $this->rainBow->getNext(); } $i = 0; for (; $i < $headCount; $i++) { $value = $rowData[$i]; $coor = XLSWriter::cellCoordinate($i, $row); $xlstype = isset($XlsTypes[$i]) ? $XlsTypes[$i] : PHPExcel_Cell_DataType::TYPE_STRING; //error_log("writing cell type = {$xlstype} for column {$i}, value {$value}", 0); $objPHPExcel->getActiveSheet()->setCellValueExplicit($coor, $value, $xlstype); if ($this->columnTypes[$i] == 'date') { $objPHPExcel->getActiveSheet()->getStyle($coor)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2); } else { if ($this->columnTypes[$i] == 'time') { $objPHPExcel->getActiveSheet()->getStyle($coor)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8); } } $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($cellStyleArray); } if ($this->weightedSumsColumn >= 0) { $weightLast = count($this->weights) - 1; $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row); $wBegin = XLSWriter::cellCoordinateAbsoluteRow($this->firstWeightColumn, $this->weigthsRow); $wEnd = XLSWriter::cellCoordinateAbsoluteRow($this->firstWeightColumn + $weightLast, $this->weigthsRow); $rBegin = XLSWriter::cellCoordinate($this->firstWeightColumn, $row); $rEnd = XLSWriter::cellCoordinate($this->firstWeightColumn + $weightLast, $row); $wSumCoor = XLSWriter::cellCoordinateAbsolute($this->weightedSumsColumn, $this->weigthsRow); $formula = "=SUMPRODUCT({$wBegin}:{$wEnd},{$rBegin}:{$rEnd})/{$wSumCoor}"; $objPHPExcel->getActiveSheet()->setCellValueExplicit($coor, $formula, PHPExcel_Cell_DataType::TYPE_FORMULA); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($cellStyleArray); } $row++; $resultSet->moveNext(); } $row = 1; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $this->linkText); $objPHPExcel->getActiveSheet()->getCell('A' . $row)->getHyperlink()->setUrl($this->linkUrl); $row++; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $this->title); $objPHPExcel->getActiveSheet()->getStyle('A' . $row)->applyFromArray($headerStyles); $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($headerStyles); $rightCell1 = XLSWriter::cellCoordinate(min($headCount - 1, 10), $row); $objPHPExcel->getActiveSheet()->mergeCells('A' . $row . ':' . $rightCell1); $rightCell2 = XLSWriter::cellCoordinate(min($headCount - 1, 10), 1); $objPHPExcel->getActiveSheet()->mergeCells('A1:' . $rightCell2); // set format $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1); $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0); for ($i = 'A', $j = 0; $i <= 'Z' && $j < $headCount; $i++, $j++) { $objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(true); // $objPHPExcel->getActiveSheet()->getStyle($i . '2')->applyFromArray($styleArray); } PHPExcel_Calculation::getInstance()->clearCalculationCache(); PHPExcel_Calculation::getInstance()->disableCalculationCache(); PHPExcel_Calculation::getInstance()->calculate(); switch ($this->excelFormat) { case 'Excel2007': $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $this->mimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'; break; case 'Excel5': $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $this->mimeType = 'application/vnd.ms-excel'; break; default: $objWriter = new PHPExcel_Writer_CSV($objPHPExcel); $this->mimeType = 'text/comma-separated-values'; break; } $tempFile = tempnam('/tmp/', 'PHPEXCEL'); // '/tmp/'.$filename; $objWriter->setPreCalculateFormulas(true); $objWriter->save($tempFile); $fp = @fopen($tempFile, 'r'); if ($fp != false) { header("Content-type: " . $this->mimeType); header("Pragma: public"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Length: " . filesize($tempFile)); header("Content-Disposition: attachment; filename=\"{$this->filename}\""); fpassthru($fp); fclose($fp); $objPHPExcel->disconnectWorksheets(); unset($objPHPExcel); unlink($tempFile); exit(0); } else { echo "cannot copy file {$tempFile} to out stream\n"; } }
public function utilization_report_excelv2() { $filter = array(); $filter['start_date']=(time()-(60*60*24*30)); if (isset($_POST['start_time'])) { $filter['start_date']=$this->m_time->datepicker_to_unix($_POST['start_time']); } $filter['end_date']=(time()+(60*60*24*30)); if (isset($_POST['end_time'])) { $filter['end_date']=$this->m_time->datepicker_to_unix($_POST['end_time'])+(60*60*24)-60; } $all_child1=$this->get_all_user_child_node(); $all_child1[$this->user_data->username]=$this->user_data; $data_head['user_data'] = $this->user_data; $all_child = array(); $position=$this->m_position->get_all_position(); $lenght_time=(int)(($filter['end_date']-$filter['start_date'])/(60*60*24)); $mod_time=(int)(($filter['end_date']-$filter['start_date'])%(60*60*24)); if ($mod_time>0) { $lenght_time+=1; } $lenght_time=$lenght_time; foreach ($all_child1 as $key => $value) { if (isset($position[$value->position])&&$position[$value->position]->non_productive=="n") { $all_child[$key]=$value; $all_child[$key]->report=$this->m_utilization_report->get_work_sheet_by_usn($value->username,$filter); $all_child[$key]->projects=$this->m_utilization_report->group_assign_to_project($all_child[$key]->report); $all_child[$key]->available_Hours=$this->m_utilization_report->cal_Available_Hours($filter['start_date'],$filter['end_date'],$value->username); } } /** Error reporting */ error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); if (PHP_SAPI == 'cli') die('This example should only be run from a Web Browser'); /** Include PHPExcel */ require_once './PHPExcel/Classes/PHPExcel.php'; require_once './PHPExcel/Classes/PHPExcel/Cell/AdvancedValueBinder.php'; PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() ); // Create new PHPExcel object $objPHPExcel = new PHPExcel(); $styleblack = array( 'font' => array( 'bold' => false, 'color' => array('rgb' => 'FFFFFF'), 'size' => 11, 'name' => 'Calibri' ), 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '000000') ) ); $style_center = array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, ) ); $stylegray = array( 'font' => array( 'bold' => false, 'color' => array('rgb' => '000000'), 'size' => 11, 'name' => 'Calibri' ), 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'E2E2E2') ) ); // Set document properties $objPHPExcel->getProperties()->setCreator("Rcal Neumerlin Group") ->setLastModifiedBy("DekGym3Atom") ->setTitle("Office 2007 XLSX User report") ->setSubject("Office 2007 XLSX User report") ->setDescription("User report document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("User report"); // Add some data $cur_col=0; $cur_row=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Date '.date("d F Y"), PHPExcel_Cell_DataType::TYPE_STRING); $cur_row+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit(date("d F Y",$filter['start_date']).' - '.date("d F Y",$filter['end_date']), PHPExcel_Cell_DataType::TYPE_STRING); $cur_row+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Resources / Project', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(0))->setAutoSize(true); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit('Available Hours', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(1))->setAutoSize(true); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(2, $cur_row)->setValueExplicit('Allocation Hour', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(2))->setAutoSize(true); $objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(2).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(4).($cur_row)); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(5, $cur_row)->setValueExplicit('Spent', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(5))->setAutoSize(true); $objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(5).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(7).($cur_row)); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(8, $cur_row)->setValueExplicit('Utilization (%)', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(8))->setAutoSize(true); $objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(8).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(10).($cur_row)); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(10).$cur_row)->applyFromArray($stylegray); foreach ($all_child as $key => $value) { $cur_row+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit($value->nickname, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit('Available Hours', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(2, $cur_row)->setValueExplicit('TOTAL', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(3, $cur_row)->setValueExplicit('BUDGET', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(4, $cur_row)->setValueExplicit('OVER', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(5, $cur_row)->setValueExplicit('TOTAL', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(6, $cur_row)->setValueExplicit('on BUDGET', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(7, $cur_row)->setValueExplicit('on TOTAL', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(8, $cur_row)->setValueExplicit('by Budget', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(9, $cur_row)->setValueExplicit('by Spent', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(10, $cur_row)->setValueExplicit('GAP', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(10).$cur_row)->applyFromArray($stylegray); $flag_first=true; foreach ($value->projects as $key2 => $value2) { $cur_row+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit($value2->project_name, PHPExcel_Cell_DataType::TYPE_STRING); if ($flag_first) { $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit($value->available_Hours, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($style_center); $objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(1).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(1).($cur_row+count($value->projects)-1)); } $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(2, $cur_row)->setValueExplicit($value2->hour_amount, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(3, $cur_row)->setValueExplicit($value2->budget, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(4, $cur_row)->setValueExplicit($value2->hour_over, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(5, $cur_row)->setValueExplicit($value2->spend_amount, PHPExcel_Cell_DataType::TYPE_STRING); $spend_rate="0%"; if ($value2->budget!=0) { $spend_rate=number_format(($value2->spend_amount/$value2->budget)*100)."%"; } $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(6, $cur_row)->setValueExplicit($spend_rate, PHPExcel_Cell_DataType::TYPE_STRING); $spend_rate="0%"; if ($value2->hour_amount!=0) { $spend_rate=number_format(($value2->spend_amount/$value2->hour_amount)*100)."%"; } $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(7, $cur_row)->setValueExplicit($spend_rate, PHPExcel_Cell_DataType::TYPE_STRING); if ($flag_first) { $gap1="0%"; if ($value2->hour_amount!=0) { $gap1=number_format(($value->report->budget/$value->available_Hours)*100)."%"; } $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(8, $cur_row)->setValueExplicit($gap1, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(8).$cur_row)->applyFromArray($style_center); $objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(8).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(8).($cur_row+count($value->projects)-1)); $gap2="0%"; if ($value2->hour_amount!=0) { $gap2=number_format(($value->report->spend_amount/$value->available_Hours)*100)."%"; } $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(9, $cur_row)->setValueExplicit($gap2, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(9).$cur_row)->applyFromArray($style_center); $objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(9).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(9).($cur_row+count($value->projects)-1)); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(10, $cur_row)->setValueExplicit(number_format($gap2-$gap1)."%", PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(10).$cur_row)->applyFromArray($style_center); $objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(10).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(10).($cur_row+count($value->projects)-1)); } $flag_first=false; } $cur_row+=1; $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(10).$cur_row)->applyFromArray($stylegray); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit($value->nickname." Total", PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit($lenght_time.' Days', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(2, $cur_row)->setValueExplicit($value->report->hour_amount, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(3, $cur_row)->setValueExplicit($value->report->budget, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(4, $cur_row)->setValueExplicit($value->report->hour_over, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(5, $cur_row)->setValueExplicit($value->report->spend_amount, PHPExcel_Cell_DataType::TYPE_STRING); $tspend_rate="0%"; if ($value->report->budget!=0) { $tspend_rate=number_format(($value->report->spend_amount/$value->report->budget)*100)."%"; } $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(6, $cur_row)->setValueExplicit($tspend_rate, PHPExcel_Cell_DataType::TYPE_STRING); $tspend_rate="0%"; if ($value->report->hour_amount!=0) { $tspend_rate=number_format(($value->report->spend_amount/$value->report->hour_amount)*100)."%"; } $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(7, $cur_row)->setValueExplicit($tspend_rate, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(8, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(9, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(10, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING); } // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('utilization_report'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (Excel2007) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="utilization_report.xlsx"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header ('Pragma: public'); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; }
/** * Utility function for export2csv and CRM_Report_Form::endPostProcess * - make XLS file content and return as string. * * @param Object &$form CRM_Report_Form object. * @param Array &$rows Resulting rows from the report. * @param String Full path to the filename to write in (for mailing reports). * * See @CRM_Report_Utils_Report::makeCsv(). */ static function generateFile(&$form, &$rows, &$stats, $filename = 'php://output') { $config = CRM_Core_Config::singleton(); $csv = ''; // Generate an array with { 0=>A, 1=>B, 2=>C, ... } $foo = array(0 => '', 1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E', 6 => 'F', 7 => 'G', 8 => 'H', 9 => 'I', 10 => 'J', 11 => 'K', 12 => 'L', 13 => 'M'); $a = ord('A'); $cells = array(); for ($i = 0; $i < count($foo); $i++) { for ($j = 0; $j < 26; $j++) { $cells[$j + $i * 26] = $foo[$i] . chr($j + $a); } } include 'PHPExcel/Classes/PHPExcel.php'; $objPHPExcel = new PHPExcel(); // Does magic things for date cells // https://phpexcel.codeplex.com/discussions/331005 PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); // FIXME Set the locale of the XLS file // might not really be necessary (concerns mostly functions? not dates?) // $validLocale = PHPExcel_Settings::setLocale('fr'); // Set document properties $objPHPExcel->getProperties()->setCreator("CiviCRM")->setLastModifiedBy("CiviCRM")->setTitle(ts('Report'))->setSubject(ts('Report'))->setDescription(ts('Report')); $sheet = $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setTitle('Report'); // Add headers if this is the first row. $columnHeaders = array_keys($form->_columnHeaders); // Replace internal header names with friendly ones, where available. foreach ($columnHeaders as $header) { if (isset($form->_columnHeaders[$header])) { $headers[] = html_entity_decode(strip_tags($form->_columnHeaders[$header]['title'])); } } // Add the column headers. $col = 0; $cpt = 1; foreach ($headers as $h) { $objPHPExcel->getActiveSheet()->setCellValue($cells[$col] . $cpt, $h); $col++; } // Add rows. $cpt = 2; foreach ($rows as $row) { $displayRows = array(); $col = 0; foreach ($columnHeaders as $k => $v) { $value = CRM_Utils_Array::value($v, $row); if (!isset($value)) { $col++; continue; } // Remove HTML, unencode entities $value = html_entity_decode(strip_tags($value)); // Data transformation before adding it to the cell if (CRM_Utils_Array::value('type', $form->_columnHeaders[$v]) & CRM_Utils_Type::T_DATE) { $group_by = CRM_Utils_Array::value('group_by', $form->_columnHeaders[$v]); if ($group_by == 'MONTH' || $group_by == 'QUARTER') { $value = CRM_Utils_Date::customFormat($value, $config->dateformatPartial); } elseif ($group_by == 'YEAR') { $value = CRM_Utils_Date::customFormat($value, $config->dateformatYear); } else { $value = CRM_Utils_Date::customFormat($value, '%Y-%m-%d'); } } $objPHPExcel->getActiveSheet()->setCellValue($cells[$col] . $cpt, $value); // Cell formats if (CRM_Utils_Array::value('type', $form->_columnHeaders[$v]) & CRM_Utils_Type::T_DATE) { $objPHPExcel->getActiveSheet()->getStyle($cells[$col] . $cpt)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD); // Set autosize on date columns. // We only do it for dates because we know they have a fixed width, unlike strings. // For eco-friendlyness, this should only be done once, perhaps when processing the headers initially $objPHPExcel->getActiveSheet()->getColumnDimension($cells[$col])->setAutoSize(true); } elseif (CRM_Utils_Array::value('type', $form->_columnHeaders[$v]) & CRM_Utils_Type::T_MONEY) { $objPHPExcel->getActiveSheet()->getStyle($cells[$col])->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL); } $col++; } $cpt++; } // Add report statistics on a separate Excel sheet. if (!empty($stats) && !empty($stats['counts'])) { $cpt = 1; $objWorkSheet = $objPHPExcel->createSheet(1); $objWorkSheet->setTitle(ts('Statistics')); foreach ($stats['counts'] as $key => $val) { $objWorkSheet->setCellValue('A' . $cpt, $val['title'])->setCellValue('B' . $cpt, $val['value']); $cpt++; } $objWorkSheet->getColumnDimension('A')->setWidth(30); } $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save($filename); return ''; // FIXME }
/** * * See @CRM_Report_Utils_SearchExport::export2excel2007(). */ static function makeExcel(&$headers, &$columnTypes, &$rows) { $config = CRM_Core_Config::singleton(); $csv = ''; // Generate an array with { 0=>A, 1=>B, 2=>C, ... } $foo = array(0 => '', 1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E'); $a = ord('A'); $cells = array(); for ($i = 0; $i < count($foo); $i++) { for ($j = 0; $j < 26; $j++) { $cells[$j + $i * 26] = $foo[$i] . chr($j + $a); } } include 'PHPExcel/Classes/PHPExcel.php'; $objPHPExcel = new PHPExcel(); // Does magic things for date cells // https://phpexcel.codeplex.com/discussions/331005 PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); // Set document properties $objPHPExcel->getProperties()->setCreator("CiviCRM")->setLastModifiedBy("CiviCRM")->setTitle(ts('Export'))->setSubject(ts('Export'))->setDescription(ts('Export')); $sheet = $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setTitle('Export'); // Add the column headers. $col = 0; $cpt = 1; foreach ($headers as $h) { try { $objPHPExcel->getActiveSheet()->setCellValue($cells[$col] . $cpt, $h); } catch (Exception $e) { die(print_r($e, 1)); } $col++; } // Add rows. $cpt = 2; // Convert the sql headers to civi types $columnHeaders = CRM_CiviExportExcel_Utils_SearchExport::sqlTypesToCivi($columnTypes); foreach ($rows as $row) { $displayRows = array(); $col = 0; foreach ($columnTypes as $k => $v) { $value = CRM_Utils_Array::value($k, $row); if (!isset($value)) { $col++; continue; } // Remove HTML, unencode entities $value = html_entity_decode(strip_tags($value)); $objPHPExcel->getActiveSheet()->setCellValue($cells[$col] . $cpt, $value); // Cell formats if (CRM_Utils_Array::value('type', $columnHeaders[$k]) & CRM_Utils_Type::T_DATE) { $objPHPExcel->getActiveSheet()->getStyle($cells[$col] . $cpt)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD); // Set autosize on date columns. // We only do it for dates because we know they have a fixed width, unlike strings. // For eco-friendlyness, this should only be done once, perhaps when processing the headers initially $objPHPExcel->getActiveSheet()->getColumnDimension($cells[$col])->setAutoSize(true); } elseif (CRM_Utils_Array::value('type', $columnHeaders[$k]) & CRM_Utils_Type::T_MONEY) { $objPHPExcel->getActiveSheet()->getStyle($cells[$col])->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL); } $col++; } $cpt++; } $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); return ''; // FIXME }
protected function addHeader($from, $to) { $this->_row = 1; $structure = Doctrine::getTable('Structure')->createQuery('a')->fetchOne(); PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); $active_sheet = $this->getDocument()->getActiveSheet(); //Add the logo of the structure at the top left of the page: $logo = new PHPExcel_Worksheet_Drawing(); $logo->setName('Logo'); $logo->setDescription('Logo'); $logo->setPath('./uploads/images/logo.png'); $logo->setHeight(60); $logo->setWidth(129); $logo->setCoordinates('A' . $this->_row); $logo->setWorksheet($active_sheet); //Merge the cells at the top right of the page: $active_sheet->mergeCells('B' . $this->_row . ':D' . $this->_row); //and put inside the informations about the structure: $active_sheet->getCell('B' . $this->_row)->setValue($structure->getName() . "\n" . $structure->getAddress()->getStreet() . "\n" . $structure->getAddress()->getAddressCity()->getPostalCode() . " " . $structure->getAddress()->getAddressCity()->getName() . "\n" . "Tel: " . $structure->getTelephoneNumber() . "\n" . "Tel2: " . $structure->getAddress()->getTelephoneNumber() . "\n" . "e-mail: " . $structure->getEmail() . "\n" . "site: " . $structure->getWebsite() . "\n"); $active_sheet->getStyle('B' . $this->_row)->getFont()->setBold(true); $this->nextLine(); //Write the chosen period at the date of document generation: $active_sheet->getCell('A' . $this->_row)->setValue($this->_translate("Period") . ":\n" . $this->_translate("From") . " " . date($this->_translate('m/d/y'), strtotime($from)) . " " . $this->_translate("to") . " " . date($this->_translate('m/d/y'), strtotime($to))); $active_sheet->getCell('B' . $this->_row)->setValue($this->_translate("Generated") . ":\n" . date($this->_translate('m/d/y')) . " " . $this->_translate("at") . " " . date('H:i:s')); $active_sheet->getStyle('A' . $this->_row . ':B' . $this->_row)->getFont()->setSize(8); //General specifications of the page: //Footer: $active_sheet->getHeaderFooter()->setOddFooter('&L&D&RPage &P/&N'); $this->nextLine(2); }
public function operatingGPReport_excel_client_mode() { $data_view['business_unit_id'] = "no"; $start_time = 0; $end_carlendar_unix = 0; $bus_unit = ""; $company = array(); $bu = array(); $company_tmp = $this->m_company->get_all_company(); $bu_tmp = $this->m_company->get_all_bu(); foreach ($company_tmp as $key => $value) { $company[$value->id] = $value; } foreach ($bu_tmp as $key => $value) { $bu[$value->id] = $value; } $business_list = $this->m_business->get_all_business(); if (isset($_POST['start_time']) && isset($_POST['end_time'])) { $start_time = $this->m_time->datepicker_to_unix($_POST['start_time']); $end_carlendar_unix = $this->m_time->datepicker_to_unix($_POST['end_time']); } else { $start_time = mktime(0, 0, 1, 1, 1, date("Y")); $end_carlendar_unix = mktime(0, 0, 1, 12, 31, date("Y")); } if (isset($_POST['business_unit_id'])) { $bus_unit = $_POST['business_unit_id']; } else { $bus_unit = "all"; } if (isset($_POST['mode'])) { $mode = (int) $_POST['mode']; } else { $mode = 1; } $multi_usn = array("all" => "all"); if (isset($_POST['multi_usn'])) { unset($multi_usn['all']); $multi_usn[$_POST['multi_usn']] = $_POST['multi_usn']; } $forcast_report = $this->m_forcast->get_forcast_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $pce_report = $this->m_forcast->get_forcast_pce_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $target_bill_report = $this->m_forcast->get_forcast_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $actual_bill_report = $this->m_forcast->get_forcast_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $forcast_out_report = $this->m_account->get_forcast_outsource_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $outsource_report = $this->m_account->get_outsource_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $target_out_report = $this->m_account->get_outsource_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $actual_out_report = $this->m_account->get_outsource_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $forcast_report_cash = $this->m_forcast->get_forcast_receive_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $pce_report_cash = $this->m_forcast->get_forcast_receive_pce_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $target_bill_report_cash = $this->m_forcast->get_forcast_receive_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $actual_bill_report_cash = $this->m_forcast->get_forcast_receive_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $forcast_out_report_cash = $this->m_account->get_forcast_outsource_paid_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $outsource_report_cash = $this->m_account->get_outsource_paid_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $target_out_report_cash = $this->m_account->get_outsource_paid_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); $actual_out_report_cash = $this->m_account->get_outsource_paid_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode); if (isset($this->user_data->prem['cs']) && (!isset($this->user_data->prem['admin']) && !isset($this->user_data->prem['account']) && !isset($this->user_data->prem['csd']))) { $all_user_under = $this->m_user->get_all_user_by_super_usn_all_lv($this->user_data->username, $this->user_data->username); $array_user = $this->m_user->change_node_user_to_array($all_user_under); $array_user[$this->user_data->username] = $this->user_data->username; $forcast_report = $this->m_forcast->get_forcast_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $pce_report = $this->m_forcast->get_forcast_pce_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $target_bill_report = $this->m_forcast->get_forcast_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $actual_bill_report = $this->m_forcast->get_forcast_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $forcast_out_report = $this->m_account->get_forcast_outsource_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $outsource_report = $this->m_account->get_outsource_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $target_out_report = $this->m_account->get_outsource_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $actual_out_report = $this->m_account->get_outsource_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $forcast_report_cash = $this->m_forcast->get_forcast_receive_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $pce_report_cash = $this->m_forcast->get_forcast_receive_pce_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $target_bill_report_cash = $this->m_forcast->get_forcast_receive_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $actual_bill_report_cash = $this->m_forcast->get_forcast_receive_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $forcast_out_report_cash = $this->m_account->get_forcast_outsource_paid_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $outsource_report_cash = $this->m_account->get_outsource_paid_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $target_out_report_cash = $this->m_account->get_outsource_paid_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); $actual_out_report_cash = $this->m_account->get_outsource_paid_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode); } $pce_report = $this->m_forcast->gd_equal_array($outsource_report, $pce_report); $target_bill_report = $this->m_forcast->gd_equal_array($target_out_report, $target_bill_report); $actual_bill_report = $this->m_forcast->gd_equal_array($actual_out_report, $actual_bill_report); $pce_report_cash = $this->m_forcast->gd_equal_array($outsource_report_cash, $pce_report_cash); $target_bill_report_cash = $this->m_forcast->gd_equal_array($target_out_report_cash, $target_bill_report_cash); $actual_bill_report_cash = $this->m_forcast->gd_equal_array($actual_out_report_cash, $actual_bill_report_cash); /** Error reporting */ error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); if (PHP_SAPI == 'cli') { die('This example should only be run from a Web Browser'); } /** Include PHPExcel */ require_once './PHPExcel/Classes/PHPExcel.php'; require_once './PHPExcel/Classes/PHPExcel/Cell/AdvancedValueBinder.php'; PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); // Create new PHPExcel object $objPHPExcel = new PHPExcel(); $styleblack = array('font' => array('bold' => false, 'color' => array('rgb' => 'FFFFFF'), 'size' => 11, 'name' => 'Calibri'), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '000000'))); $style_center = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)); $stylegray = array('font' => array('bold' => false, 'color' => array('rgb' => '000000'), 'size' => 11, 'name' => 'Calibri'), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'E2E2E2'))); // Set document properties $objPHPExcel->getProperties()->setCreator("Rcal Neumerlin Group")->setLastModifiedBy("DekGym3Atom")->setTitle("Office 2007 XLSX User report")->setSubject("Office 2007 XLSX User report")->setDescription("User report document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("User report"); // Add some data $total_horizon_array = array(); $cur_col = 0; $cur_row = 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('GROSS PROFIT FORECAST', PHPExcel_Cell_DataType::TYPE_STRING); $cur_row = 2; $num = 0; $sumval = 0; $sumvalr = 0; $current_time = $start_time; $cur_month = 1000; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Month->', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit('Client', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setAutoSize(true); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setAutoSize(true); $cur_col += 1; while ($current_time <= $end_carlendar_unix) { if ($cur_month != date("n", $current_time)) { $cur_month = date("n", $current_time); $cur_day = date("j", $current_time); $numday = date("t", $current_time); $cur_col += 1; $total_horizon_array[date("Yn", $current_time)] = 0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(date("F", $current_time), PHPExcel_Cell_DataType::TYPE_STRING); } $current_time += 60 * 60 * 24 * $numday; } $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit("Total", PHPExcel_Cell_DataType::TYPE_STRING); $cur_row += 1; $cur_col = 0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Forcast', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center); if (count($forcast_report) > 1) { $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($forcast_report) - 1)); } foreach ($forcast_report as $key => $value) { $cur_col = 1; if ($mode == 3) { $str1 = explode("_", $key); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING); } else { $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING); } $current_time = $start_time; $cur_month = 1000; $month_amount = 0; $sum_total = 0; while ($current_time <= $end_carlendar_unix) { $cur_day = date("j", $current_time); $numday = date("t", $current_time); if (isset($value[$current_time])) { foreach ($value[$current_time] as $key2 => $value2) { $month_amount += $value2->project_value; } } if (isset($forcast_out_report[$key][$current_time])) { foreach ($forcast_out_report[$key][$current_time] as $key2 => $value2) { $month_amount -= $value2->outsource_value; } } if ($cur_month != date("n", $current_time) && $cur_day == $numday) { $cur_month = date("n", $current_time); $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true); $sum_total += $month_amount; $total_horizon_array[date("Yn", $current_time)] += $month_amount; $month_amount = 0; } $current_time += 60 * 60 * 24; } $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true); $cur_row += 1; } /////////////////////////////////////////////// PCE ////////////////////////////////////////// $cur_col = 0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('PCE', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center); if (count($pce_report) > 1) { $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($pce_report) - 1)); } foreach ($pce_report as $key => $value) { $cur_col = 1; if ($mode == 3) { $str1 = explode("_", $key); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING); } else { $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING); } $current_time = $start_time; $cur_month = 1000; $month_amount = 0; $sum_total = 0; while ($current_time <= $end_carlendar_unix) { $cur_day = date("j", $current_time); $numday = date("t", $current_time); if (isset($value[$current_time])) { foreach ($value[$current_time] as $key2 => $value2) { foreach ($value2->pce as $key3 => $value3) { $month_amount += $value3->pce_amount; } } } if (isset($outsource_report[$key][$current_time])) { foreach ($outsource_report[$key][$current_time] as $key2 => $value2) { foreach ($value2->outsource as $key3 => $value3) { $month_amount -= $value3->qt_cost; } } } if ($cur_month != date("n", $current_time) && $cur_day == $numday) { $cur_month = date("n", $current_time); $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $sum_total += $month_amount; $total_horizon_array[date("Yn", $current_time)] += $month_amount; $month_amount = 0; } $current_time += 60 * 60 * 24; } $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $cur_row += 1; } /////////////////////////////////////////////// Target Billing (OC) ////////////////////////////////////////// $cur_col = 0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Target Billing (OC)', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center); if (count($target_bill_report) > 1) { $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($target_bill_report) - 1)); } foreach ($target_bill_report as $key => $value) { $cur_col = 1; if ($mode == 3) { $str1 = explode("_", $key); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING); } else { $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING); } $current_time = $start_time; $cur_month = 1000; $month_amount = 0; $sum_total = 0; while ($current_time <= $end_carlendar_unix) { $cur_day = date("j", $current_time); $numday = date("t", $current_time); foreach ($value as $key2 => $value2) { foreach ($value2->oc as $key3 => $value3) { if (isset($value3->oc_bill[$current_time])) { foreach ($value3->oc_bill[$current_time] as $key4 => $value4) { $month_amount += $value4->amount; } } } } if (isset($target_out_report[$key])) { foreach ($target_out_report[$key] as $key2 => $value2) { foreach ($value2->outsource as $key3 => $value3) { if (isset($value3->bill[$current_time])) { foreach ($value3->bill[$current_time] as $key4 => $value4) { $month_amount -= $value4->amount - $value4->paid_amount; } } } } } if ($cur_month != date("n", $current_time) && $cur_day == $numday) { $cur_month = date("n", $current_time); $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $sum_total += $month_amount; $total_horizon_array[date("Yn", $current_time)] += $month_amount; $month_amount = 0; } $current_time += 60 * 60 * 24; } $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $cur_row += 1; } /////////////////////////////////////////////// Actual Billing ////////////////////////////////////////// $cur_col = 0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Actual Billing', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center); if (count($actual_bill_report) > 1) { $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($actual_bill_report) - 1)); } foreach ($actual_bill_report as $key => $value) { $cur_col = 1; if ($mode == 3) { $str1 = explode("_", $key); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING); } else { $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING); } $current_time = $start_time; $cur_month = 1000; $month_amount = 0; $sum_total = 0; while ($current_time <= $end_carlendar_unix) { $cur_day = date("j", $current_time); $numday = date("t", $current_time); foreach ($value as $key2 => $value2) { foreach ($value2->oc as $key3 => $value3) { if (isset($value3->oc_bill[$current_time])) { foreach ($value3->oc_bill[$current_time] as $key4 => $value4) { $month_amount += $value4->paid_amount; } } } } if (isset($actual_out_report[$key])) { foreach ($actual_out_report[$key] as $key2 => $value2) { foreach ($value2->outsource as $key3 => $value3) { if (isset($value3->bill[$current_time])) { foreach ($value3->bill[$current_time] as $key4 => $value4) { $month_amount -= $value4->amount; } } } } } if ($cur_month != date("n", $current_time) && $cur_day == $numday) { $cur_month = date("n", $current_time); $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $sum_total += $month_amount; $total_horizon_array[date("Yn", $current_time)] += $month_amount; $month_amount = 0; } $current_time += 60 * 60 * 24; } $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $cur_row += 1; } $cur_col = 0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($styleblack); $cur_col += 1; $current_time = $start_time; $cur_month = 1000; $sum_total = 0; while ($current_time <= $end_carlendar_unix) { $cur_day = date("j", $current_time); $numday = date("t", $current_time); if ($cur_month != date("n", $current_time) && $cur_day == $numday) { $cur_month = date("n", $current_time); $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($total_horizon_array[date("Yn", $current_time)]), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true); $sum_total += $total_horizon_array[date("Yn", $current_time)]; } $current_time += 60 * 60 * 24; } $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true); $cur_row += 1; /////////////////////////////////////////////////////////////////////// section 2 /////////////////////////////////////////////////// $cur_col = 0; $cur_row += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('OPERATING CASH FLOW FORECAST', PHPExcel_Cell_DataType::TYPE_STRING); $cur_row += 1; $num = 0; $sumval = 0; $sumvalr = 0; $current_time = $start_time; $cur_month = 1000; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Month->', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setAutoSize(true); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setAutoSize(true); $cur_col += 1; while ($current_time <= $end_carlendar_unix) { if ($cur_month != date("n", $current_time)) { $cur_month = date("n", $current_time); $cur_day = date("j", $current_time); $numday = date("t", $current_time); $cur_col += 1; $total_horizon_array[date("Yn", $current_time)] = 0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(date("F", $current_time), PHPExcel_Cell_DataType::TYPE_STRING); } $current_time += 60 * 60 * 24 * $numday; } $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit("Total", PHPExcel_Cell_DataType::TYPE_STRING); $cur_row += 1; $cur_col = 0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Forcast', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center); if (count($forcast_report_cash) > 1) { $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($forcast_report_cash) - 1)); } foreach ($forcast_report_cash as $key => $value) { $cur_col = 1; if ($mode == 3) { $str1 = explode("_", $key); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING); } else { $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING); } $current_time = $start_time; $cur_month = 1000; $month_amount = 0; $sum_total = 0; while ($current_time <= $end_carlendar_unix) { $cur_day = date("j", $current_time); $numday = date("t", $current_time); if (isset($value[$current_time])) { foreach ($value[$current_time] as $key2 => $value2) { $month_amount += $value2->project_value; } } if (isset($forcast_out_report_cash[$key][$current_time])) { foreach ($forcast_out_report_cash[$key][$current_time] as $key2 => $value2) { $month_amount -= $value2->outsource_value; } } if ($cur_month != date("n", $current_time) && $cur_day == $numday) { $cur_month = date("n", $current_time); $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true); $sum_total += $month_amount; $total_horizon_array[date("Yn", $current_time)] += $month_amount; $month_amount = 0; } $current_time += 60 * 60 * 24; } $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true); $cur_row += 1; } /////////////////////////////////////////////// PCE ////////////////////////////////////////// $cur_col = 0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('PCE', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center); if (count($pce_report_cash) > 1) { $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($pce_report_cash) - 1)); } foreach ($pce_report_cash as $key => $value) { $cur_col = 1; if ($mode == 3) { $str1 = explode("_", $key); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING); } else { $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING); } $current_time = $start_time; $cur_month = 1000; $month_amount = 0; $sum_total = 0; while ($current_time <= $end_carlendar_unix) { $cur_day = date("j", $current_time); $numday = date("t", $current_time); if (isset($value[$current_time])) { foreach ($value[$current_time] as $key2 => $value2) { foreach ($value2->pce as $key3 => $value3) { $month_amount += $value3->pce_amount; } } } if (isset($outsource_report_cash[$key][$current_time])) { foreach ($outsource_report_cash[$key][$current_time] as $key2 => $value2) { foreach ($value2->outsource as $key3 => $value3) { $month_amount -= $value3->qt_cost; } } } if ($cur_month != date("n", $current_time) && $cur_day == $numday) { $cur_month = date("n", $current_time); $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $sum_total += $month_amount; $total_horizon_array[date("Yn", $current_time)] += $month_amount; $month_amount = 0; } $current_time += 60 * 60 * 24; } $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $cur_row += 1; } /////////////////////////////////////////////// Target Billing (OC) ////////////////////////////////////////// $cur_col = 0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Target Billing (OC)', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center); if (count($target_bill_report_cash) > 1) { $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($target_bill_report_cash) - 1)); } foreach ($target_bill_report_cash as $key => $value) { $cur_col = 1; if ($mode == 3) { $str1 = explode("_", $key); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING); } else { $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING); } $current_time = $start_time; $cur_month = 1000; $month_amount = 0; $sum_total = 0; while ($current_time <= $end_carlendar_unix) { $cur_day = date("j", $current_time); $numday = date("t", $current_time); foreach ($value as $key2 => $value2) { foreach ($value2->oc as $key3 => $value3) { if (isset($value3->oc_bill[$current_time])) { foreach ($value3->oc_bill[$current_time] as $key4 => $value4) { $month_amount += $value4->amount; } } } } if (isset($target_out_report_cash[$key])) { foreach ($target_out_report_cash[$key] as $key2 => $value2) { foreach ($value2->outsource as $key3 => $value3) { if (isset($value3->bill[$current_time])) { foreach ($value3->bill[$current_time] as $key4 => $value4) { $month_amount -= $value4->amount - $value4->paid_amount; } } } } } if ($cur_month != date("n", $current_time) && $cur_day == $numday) { $cur_month = date("n", $current_time); $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $sum_total += $month_amount; $total_horizon_array[date("Yn", $current_time)] += $month_amount; $month_amount = 0; } $current_time += 60 * 60 * 24; } $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $cur_row += 1; } /////////////////////////////////////////////// Actual Billing ////////////////////////////////////////// $cur_col = 0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Actual Billing', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center); if (count($actual_bill_report_cash) > 1) { $objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($actual_bill_report_cash) - 1)); } foreach ($actual_bill_report_cash as $key => $value) { $cur_col = 1; if ($mode == 3) { $str1 = explode("_", $key); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING); } else { $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING); } $current_time = $start_time; $cur_month = 1000; $month_amount = 0; $sum_total = 0; while ($current_time <= $end_carlendar_unix) { $cur_day = date("j", $current_time); $numday = date("t", $current_time); foreach ($value as $key2 => $value2) { foreach ($value2->oc as $key3 => $value3) { if (isset($value3->oc_bill[$current_time])) { foreach ($value3->oc_bill[$current_time] as $key4 => $value4) { $month_amount += $value4->paid_amount; } } } } if (isset($actual_out_report_cash[$key])) { foreach ($actual_out_report_cash[$key] as $key2 => $value2) { foreach ($value2->outsource as $key3 => $value3) { if (isset($value3->bill[$current_time])) { foreach ($value3->bill[$current_time] as $key4 => $value4) { $month_amount -= $value4->amount; } } } } } if ($cur_month != date("n", $current_time) && $cur_day == $numday) { $cur_month = date("n", $current_time); $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $sum_total += $month_amount; $total_horizon_array[date("Yn", $current_time)] += $month_amount; $month_amount = 0; } $current_time += 60 * 60 * 24; } $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $cur_row += 1; } $cur_col = 0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($styleblack); $cur_col += 1; $current_time = $start_time; $cur_month = 1000; $sum_total = 0; while ($current_time <= $end_carlendar_unix) { $cur_day = date("j", $current_time); $numday = date("t", $current_time); if ($cur_month != date("n", $current_time) && $cur_day == $numday) { $cur_month = date("n", $current_time); $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($total_horizon_array[date("Yn", $current_time)]), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true); $sum_total += $total_horizon_array[date("Yn", $current_time)]; } $current_time += 60 * 60 * 24; } $cur_col += 1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); //$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true); $cur_row += 1; // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Operation GD report'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (Excel2007) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="Operation_GD_report.xlsx"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; }
public function forcest_receive_excel() { $data_view['business_unit_id']="no"; $start_time=0; $end_carlendar_unix=0; $bus_unit=""; $company = array(); $bu = array(); $company_tmp=$this->m_company->get_all_company(); $bu_tmp=$this->m_company->get_all_bu(); foreach ($company_tmp as $key => $value) { $company[$value->id]=$value; } foreach ($bu_tmp as $key => $value) { $bu[$value->id]=$value; } $business_list = $this->m_business->get_all_business(); if (isset($_POST['start_time'])&&isset($_POST['end_time'])) { $start_time=$this->m_time->datepicker_to_unix($_POST['start_time']); $end_carlendar_unix=$this->m_time->datepicker_to_unix($_POST['end_time']); }else{ $start_time=mktime(0,0,1,1,1,date("Y")); $end_carlendar_unix=mktime(0,0,1,12,31,date("Y")); } if (isset($_POST['business_unit_id'])) { $bus_unit=$_POST['business_unit_id']; }else{ $bus_unit="all"; } $forcast_report=$this->m_forcast->get_forcast_receive_report($start_time,$end_carlendar_unix,$bus_unit); $pce_report=$this->m_forcast->get_forcast_receive_pce_report($start_time,$end_carlendar_unix,$bus_unit); $target_bill_report=$this->m_forcast->get_forcast_receive_target_bill_report($start_time,$end_carlendar_unix,$bus_unit); $actual_bill_report=$this->m_forcast->get_forcast_receive_actual_bill_report($start_time,$end_carlendar_unix,$bus_unit); /** Error reporting */ error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); if (PHP_SAPI == 'cli') die('This example should only be run from a Web Browser'); /** Include PHPExcel */ require_once './PHPExcel/Classes/PHPExcel.php'; require_once './PHPExcel/Classes/PHPExcel/Cell/AdvancedValueBinder.php'; PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() ); // Create new PHPExcel object $objPHPExcel = new PHPExcel(); $styleblack = array( 'font' => array( 'bold' => false, 'color' => array('rgb' => 'FFFFFF'), 'size' => 11, 'name' => 'Calibri' ), 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '000000') ) ); $stylegray = array( 'font' => array( 'bold' => false, 'color' => array('rgb' => '000000'), 'size' => 11, 'name' => 'Calibri' ), 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'E2E2E2') ) ); // Set document properties $objPHPExcel->getProperties()->setCreator("Rcal Neumerlin Group") ->setLastModifiedBy("DekGym3Atom") ->setTitle("Office 2007 XLSX User report") ->setSubject("Office 2007 XLSX User report") ->setDescription("User report document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("User report"); // Add some data $cur_col=0; $cur_row=1; $num=0; $sumval=0; $sumvalr=0; $current_time=$start_time; $cur_month=1000; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Month->', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setAutoSize(true); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setAutoSize(true); $cur_col+=2; while ($current_time<=$end_carlendar_unix) { if ($cur_month!=date("n",$current_time)) { $cur_month=date("n",$current_time); $cur_day=date("j",$current_time); $numday=date("t",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(date("F",$current_time), PHPExcel_Cell_DataType::TYPE_STRING); } $current_time+=(60*60*24*$numday); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit("Total", PHPExcel_Cell_DataType::TYPE_STRING); $cur_row+=1; $cur_col=0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Forcast', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(2).$cur_row)->applyFromArray($styleblack); $cur_col+=2; $current_time=$start_time; $cur_month=1000; $month_amount=0; $sum_total=0; while ($current_time<=$end_carlendar_unix) { $cur_day=date("j",$current_time); $numday=date("t",$current_time); foreach ($forcast_report as $key => $value) { if (isset($value->forcast_list[$current_time])) { foreach ($value->forcast_list[$current_time] as $key2 => $value2) { $month_amount+=$value2->project_value; } } } if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) { $cur_month=date("n",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true); $sum_total+=$month_amount; $month_amount=0; } $current_time+=(60*60*24); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true); $cur_row+=1; foreach ($forcast_report as $key => $value) { $cur_col=0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value->firstname." ".$value->lastname, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+1).$cur_row)->applyFromArray($stylegray); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+2).$cur_row)->applyFromArray($stylegray); $cur_col+=2; $current_time=$start_time; $cur_month=1000; $month_amount=0; $sum_total=0; while ($current_time<=$end_carlendar_unix) { $cur_day=date("j",$current_time); $numday=date("t",$current_time); if (isset($value->forcast_list[$current_time])) { foreach ($value->forcast_list[$current_time] as $key2 => $value2) { $month_amount+=$value2->project_value; } } if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) { $cur_month=date("n",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray); $sum_total+=$month_amount; $month_amount=0; } $current_time+=(60*60*24); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray); $cur_row+=1; foreach ($value->forcast_list as $key2 => $value2) { foreach ($value2 as $key3 => $value3) { $cur_col=0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value3->project_name, PHPExcel_Cell_DataType::TYPE_STRING); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($company[$value3->project_client]->name." ".$bu[$value3->project_bu]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING); $current_time=$start_time; $cur_month=1000; $month_amount=0; $sum_total=0; while ($current_time<=$end_carlendar_unix) { $cur_day=date("j",$current_time); $numday=date("t",$current_time); if ($current_time==$value3->project_end) { $month_amount+=$value3->project_value; } if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) { $cur_month=date("n",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); $sum_total+=$month_amount; $month_amount=0; } $current_time+=(60*60*24); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); $cur_row+=1; } } } /////////////////////////////////////////////// PCE ////////////////////////////////////////// $cur_col=0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('PCE', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(2).$cur_row)->applyFromArray($styleblack); $cur_col+=2; $current_time=$start_time; $cur_month=1000; $month_amount=0; $sum_total=0; while ($current_time<=$end_carlendar_unix) { $cur_day=date("j",$current_time); $numday=date("t",$current_time); foreach ($pce_report as $key => $value) { if (isset($value->forcast_list[$current_time])) { foreach ($value->forcast_list[$current_time] as $key2 => $value2) { foreach ($value2->pce as $key3 => $value3) { $month_amount+=$value3->pce_amount; } } } } if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) { $cur_month=date("n",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $sum_total+=$month_amount; $month_amount=0; } $current_time+=(60*60*24); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $cur_row+=1; foreach ($pce_report as $key => $value) { $cur_col=0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value->firstname." ".$value->lastname, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+1).$cur_row)->applyFromArray($stylegray); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+2).$cur_row)->applyFromArray($stylegray); $cur_col+=2; $current_time=$start_time; $cur_month=1000; $month_amount=0; $sum_total=0; while ($current_time<=$end_carlendar_unix) { $cur_day=date("j",$current_time); $numday=date("t",$current_time); if (isset($value->forcast_list[$current_time])) { foreach ($value->forcast_list[$current_time] as $key2 => $value2) { foreach ($value2->pce as $key3 => $value3) { $month_amount+=$value3->pce_amount; } } } if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) { $cur_month=date("n",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray); $sum_total+=$month_amount; $month_amount=0; } $current_time+=(60*60*24); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray); $cur_row+=1; foreach ($value->forcast_list as $key2 => $value2) { foreach ($value2 as $key3 => $value3) { $cur_col=0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value3->project_name, PHPExcel_Cell_DataType::TYPE_STRING); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($company[$value3->project_client]->name." ".$bu[$value3->project_bu]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING); $cur_col+=1; $pce_no_str=""; foreach ($value3->pce as $key4 => $value4) { $pce_no_str.=$value4->pce_no.","; } $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($pce_no_str, PHPExcel_Cell_DataType::TYPE_STRING); $current_time=$start_time; $cur_month=1000; $month_amount=0; $sum_total=0; while ($current_time<=$end_carlendar_unix) { $cur_day=date("j",$current_time); $numday=date("t",$current_time); if ($current_time==$value3->project_end) { foreach ($value3->pce as $key4 => $value4) { $month_amount+=$value4->pce_amount; } } if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) { $cur_month=date("n",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); $sum_total+=$month_amount; $month_amount=0; } $current_time+=(60*60*24); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); $cur_row+=1; } } } /////////////////////////////////////////////// Target Billing (OC) ////////////////////////////////////////// $cur_col=0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Target Billing (OC)', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(2).$cur_row)->applyFromArray($styleblack); $cur_col+=2; $current_time=$start_time; $cur_month=1000; $month_amount=0; $sum_total=0; while ($current_time<=$end_carlendar_unix) { $cur_day=date("j",$current_time); $numday=date("t",$current_time); foreach ($target_bill_report as $key => $value) { foreach ($value->forcast_list as $key2 => $value2) { foreach ($value2->oc as $key3 => $value3) { if (isset($value3->oc_bill[$current_time])) { foreach ($value3->oc_bill[$current_time] as $key4 => $value4) { $month_amount+=$value4->amount; } } } } } if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) { $cur_month=date("n",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $sum_total+=$month_amount; $month_amount=0; } $current_time+=(60*60*24); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $cur_row+=1; foreach ($target_bill_report as $key => $value) { $cur_col=0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value->firstname." ".$value->lastname, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+1).$cur_row)->applyFromArray($stylegray); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+2).$cur_row)->applyFromArray($stylegray); $cur_col+=2; $current_time=$start_time; $cur_month=1000; $month_amount=0; $sum_total=0; while ($current_time<=$end_carlendar_unix) { $cur_day=date("j",$current_time); $numday=date("t",$current_time); foreach ($value->forcast_list as $key2 => $value2) { foreach ($value2->oc as $key3 => $value3) { if (isset($value3->oc_bill[$current_time])) { foreach ($value3->oc_bill[$current_time] as $key4 => $value4) { $month_amount+=$value4->amount; } } } } if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) { $cur_month=date("n",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray); $sum_total+=$month_amount; $month_amount=0; } $current_time+=(60*60*24); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray); $cur_row+=1; foreach ($value->forcast_list as $key2 => $value2) { $cur_col=0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value2->project_name, PHPExcel_Cell_DataType::TYPE_STRING); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($company[$value2->project_client]->name." ".$bu[$value2->project_bu]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING); $cur_col+=1; $oc_no_str=""; foreach ($value2->oc as $key3 => $value3) { $oc_no_str.=$value3->oc_no.","; } $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($oc_no_str, PHPExcel_Cell_DataType::TYPE_STRING); $current_time=$start_time; $cur_month=1000; $month_amount=0; $sum_total=0; while ($current_time<=$end_carlendar_unix) { $cur_day=date("j",$current_time); $numday=date("t",$current_time); foreach ($value2->oc as $key3 => $value3) { if (isset($value3->oc_bill[$current_time])) { foreach ($value3->oc_bill[$current_time] as $key4 => $value4) { $month_amount+=$value4->amount; } } } if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) { $cur_month=date("n",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); $sum_total+=$month_amount; $month_amount=0; } $current_time+=(60*60*24); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); $cur_row+=1; } } /////////////////////////////////////////////// Actual Billing ////////////////////////////////////////// $cur_col=0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Actual Billing', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($styleblack); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(2).$cur_row)->applyFromArray($styleblack); $cur_col+=2; $current_time=$start_time; $cur_month=1000; $month_amount=0; $sum_total=0; while ($current_time<=$end_carlendar_unix) { $cur_day=date("j",$current_time); $numday=date("t",$current_time); foreach ($actual_bill_report as $key => $value) { foreach ($value->forcast_list as $key2 => $value2) { foreach ($value2->oc as $key3 => $value3) { if (isset($value3->oc_bill[$current_time])) { foreach ($value3->oc_bill[$current_time] as $key4 => $value4) { $month_amount+=$value4->paid_amount; } } } } } if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) { $cur_month=date("n",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $sum_total+=$month_amount; $month_amount=0; } $current_time+=(60*60*24); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack); $cur_row+=1; foreach ($actual_bill_report as $key => $value) { $cur_col=0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value->firstname." ".$value->lastname, PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+1).$cur_row)->applyFromArray($stylegray); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+2).$cur_row)->applyFromArray($stylegray); $cur_col+=2; $current_time=$start_time; $cur_month=1000; $month_amount=0; $sum_total=0; while ($current_time<=$end_carlendar_unix) { $cur_day=date("j",$current_time); $numday=date("t",$current_time); foreach ($value->forcast_list as $key2 => $value2) { foreach ($value2->oc as $key3 => $value3) { if (isset($value3->oc_bill[$current_time])) { foreach ($value3->oc_bill[$current_time] as $key4 => $value4) { $month_amount+=$value4->paid_amount; } } } } if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) { $cur_month=date("n",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray); $sum_total+=$month_amount; $month_amount=0; } $current_time+=(60*60*24); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray); $cur_row+=1; foreach ($value->forcast_list as $key2 => $value2) { $cur_col=0; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value2->project_name, PHPExcel_Cell_DataType::TYPE_STRING); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($company[$value2->project_client]->name." ".$bu[$value2->project_bu]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING); $cur_col+=1; $oc_no_str=""; foreach ($value2->oc as $key3 => $value3) { $oc_no_str.=$value3->oc_no.","; } $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($oc_no_str, PHPExcel_Cell_DataType::TYPE_STRING); $current_time=$start_time; $cur_month=1000; $month_amount=0; $sum_total=0; while ($current_time<=$end_carlendar_unix) { $cur_day=date("j",$current_time); $numday=date("t",$current_time); foreach ($value2->oc as $key3 => $value3) { if (isset($value3->oc_bill[$current_time])) { foreach ($value3->oc_bill[$current_time] as $key4 => $value4) { $month_amount+=$value4->paid_amount; } } } if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) { $cur_month=date("n",$current_time); $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING); $sum_total+=$month_amount; $month_amount=0; } $current_time+=(60*60*24); } $cur_col+=1; $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING); $cur_row+=1; } } // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Forcast'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (Excel2007) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="Forcast_receive.xlsx"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header ('Pragma: public'); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; }
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL * @version ##VERSION##, ##DATE## */ /** Error reporting */ error_reporting(E_ALL); ini_set('display_errors', true); ini_set('display_startup_errors', true); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); /** PHPExcel */ require_once dirname(__FILE__) . '/../Classes/PHPExcel.php'; // Set timezone echo date('H:i:s'), " Set timezone", EOL; date_default_timezone_set('UTC'); // Set value binder echo date('H:i:s'), " Set value binder", EOL; PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new PHPExcel(); // Set document properties echo date('H:i:s'), " Set document properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Set default font echo date('H:i:s'), " Set default font", EOL; $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10); // Set column widths echo date('H:i:s'), " Set column widths", EOL; $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(14); // Add some data, resembling some different data types
/** * @param \PHPExcel_Cell_IValueBinder $ValueBinder */ private function setConfiguration(\PHPExcel_Cell_IValueBinder $ValueBinder = null) { \PHPExcel_Settings::setCacheStorageMethod(\PHPExcel_CachedObjectStorageFactory::cache_in_memory, array('cacheTime' => 3600)); if (null !== $ValueBinder) { \PHPExcel_Cell::setValueBinder($ValueBinder); } }