protected function exportExcel($props)
{
$props = array_merge(array('title' => '', 'subtitle' => '', 'headers' => null, 'data' => null), $props);
if ($props['subtitle']) {
$props['title'] .= ' - ' . $props['subtitle'];
}
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
$logoPath = 'images/logo_academy.png';
$props['doc'] = array_merge(array('title' => 'Carus', 'subject' => 'Reporte', 'description' => 'Reporte de sistema', 'keywords' => 'Reporte', 'category' => 'Reporte'), isset($props['doc']) && is_array($props['doc']) ? $props['doc'] : array());
$title = preg_replace('/([^a-z0-9áéíóúàèìòùâêîôûñ\\-_ ]+)/i', '', isset($props['title']) ? $props['title'] : $props['doc']['title']);
if (strlen($title) > 30) {
$title = substr($title, 0, strripos($title, " ", 30 - strlen($title)));
}
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Imagen Digital")->setLastModifiedBy("Imagen Digital")->setTitle($props['doc']['title'])->setSubject($props['doc']['subject'])->setDescription($props['doc']['description'])->setKeywords($props['doc']['keywords'])->setCategory($props['doc']['category']);
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getDefaultStyle()->getFont()->setName('Verdana')->setSize(8)->setColor(new PHPExcel_Style_Color('FF333333'));
$sheet = $objPHPExcel->getActiveSheet();
// Set page orientation, size, Print Area and Fit To Pages
$objPageSetup = new PHPExcel_Worksheet_PageSetup();
$objPageSetup->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER);
$objPageSetup->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPageSetup->setFitToWidth(1);
$sheet->setPageSetup($objPageSetup);
$sheet->setTitle($title);
$sheet->getDefaultStyle()->applyFromArray(array('alignment' => array('wrap' => false)));
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setWorksheet($sheet);
$objDrawing->setName("logo");
$objDrawing->setDescription("Carus");
$objDrawing->setPath($logoPath);
$objDrawing->setCoordinates('A1');
$objDrawing->setWidth(148);
$objDrawing->setHeight(30);
$sheet->getRowDimension(1)->setRowHeight(20);
$styleTitle = array('font' => array('bold' => true, 'color' => array('rgb' => 'FFFFFFFF'), 'size' => 8, 'name' => 'Verdana'), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FF333333')), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF333333'))), 'alignment' => array('wrap' => false, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER));
$styleText = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF0B7C98'))), 'alignment' => array('vertical' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
$col = 0;
$fil = 3;
$colMax = 0;
$sheet->setCellValueByColumnAndRow($col, $fil, $title);
$sheet->getStyleByColumnAndRow($col, $fil)->applyFromArray($styleTitle);
$fil++;
foreach ($props['headers'] as $value) {
$sheet->setCellValueByColumnAndRow($col, $fil, $value);
$sheet->getStyleByColumnAndRow($col, $fil)->applyFromArray($styleTitle);
$col++;
}
$colMax = max($colMax, $col);
$fil++;
foreach ($props['data'] as $row) {
$col = 0;
foreach ($props['headers'] as $key => $value) {
$sheet->setCellValueByColumnAndRow($col, $fil, $row[$value]);
$sheet->getStyleByColumnAndRow($col, $fil)->applyFromArray($styleText);
$col++;
}
$fil++;
}
$colMax = max($colMax, $col);
$sheet->mergeCellsByColumnAndRow(0, 1, $colMax - 1, 1);
$sheet->mergeCellsByColumnAndRow(0, 3, $colMax - 1, 3);
for ($c = 0; $c < $colMax; $c++) {
$sheet->getColumnDimensionByColumn($c)->setAutoSize(true);
$calculatedWidth = $sheet->getColumnDimensionByColumn($c)->getWidth();
$sheet->getColumnDimensionByColumn($c)->setAutoSize(false);
$sheet->getColumnDimensionByColumn($c)->setWidth(max((int) $calculatedWidth * 1.05, 12));
}
$sheet->calculateColumnWidths();
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $title . '.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 = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
$objWriter->save('php://output');
exit;
}
$objPHPExcel->getActiveSheet()->mergeCells('A3:G3');
$objPHPExcel->getActiveSheet()->getStyle('A3:G3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(13);
$objPHPExcel->setActiveSheetIndex(0)->getRowDimension('3')->setRowHeight(20);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A3', 'Historico Estado Docente');
$objPHPExcel->getActiveSheet()->mergeCells('B4:E4');
$objPHPExcel->getActiveSheet()->getStyle('B4:E4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// $objPHPExcel->setActiveSheetIndex ( 0 )->setCellValue ( 'B4', $respuesta [0] [1] );
/**
* Crear nuevo objeto de imagen
*/
/**
* Orientacion, tamaño y escala
*/
$objPageSetup = new PHPExcel_Worksheet_PageSetup();
$objPageSetup->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A2_PAPER);
$objPageSetup->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->setBreak('A40', PHPExcel_Worksheet::BREAK_ROW);
$objPHPExcel->getActiveSheet()->setBreak('O1', PHPExcel_Worksheet::BREAK_COLUMN);
$objPageSetup->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->setPageSetup($objPageSetup);
/**
* Background
*/
// $objPHPExcel->getActiveSheet()
// ->getStyle('A6:F6')->getFill()->applyFromArray(
// array(
// 'type' => PHPExcel_Style_Fill::FILL_SOLID,
// 'startcolor' => array('argb' => '#FF0000'),
// )
/**
*
* @param $from
* @param $to
*/
protected function _exportTemporalStatement($from, $to)
{
$active_sheet = $this->getDocument()->getActiveSheet();
$acts = Doctrine_Query::create()->select('a.designation')->from('Act a')->where('a.disabled = ?', 0)->orderBy('a.designation')->execute();
$numberOfActs = Doctrine_Query::create()->select('COUNT(*) as total')->from('Act a')->where('a.disabled = ?', 0)->fetchOne();
$statement = Statistics::getTemporalStatementValuesByAct($from, $to);
//Format the statement data into times:
$statement = self::formatStatement($statement);
/*Print all the acts at the top:*/
$this->_exportQuantitativeStatementHeader($statement);
$columns = Statistics::getTemporalStatementColumns($from, $to);
$total = Statistics::getTotalTemporalStatementValues($from, $to);
//Format the total data into times:
$total = self::formatTotal($total);
foreach ($columns as $stat_type => $stat) {
$this->_exportQuantitativeStatementRessourceByString($columns, $statement, $stat_type, $total);
}
foreach ($columns as $stat_type => $stat) {
$this->_exportQuantitativeStatementRessourceById($columns, $statement, $stat_type, $total);
}
//Specific exportations:
$this->_exportQuantitativeStatementByCountry($columns, $statement, $total, $from, $to);
$this->nextLine(3);
$this->_exportQuantitativeStatementByDay($columns, $statement, $total);
$this->nextLine(3);
$this->_exportQuantitativeStatementByBuilding($columns, $statement, $total);
$this->nextLine(3);
//Details per category:
$detailsPerCategory = Statistics::getTemporalDetailsPerCategory($from, $to, $total);
$detailsPerCategory = self::formatStatement($detailsPerCategory);
$this->_exportDetailsPerCategory($columns, $detailsPerCategory, $total);
$this->nextLine(3);
//Details per time slot:
$detailsPerTimeSlot = Statistics::getTemporalDetailsPerTimeSlot($from, $to, $total);
$detailsPerTimeSlot = self::formatStatement($detailsPerTimeSlot);
$this->_exportDetailsPerTimeSlot($columns, $detailsPerTimeSlot, $total);
$this->nextLine(3);
//Page setup:
$page_setup = new PHPExcel_Worksheet_PageSetup();
$page_setup->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$page_setup->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$page_setup->setHorizontalCentered(true);
$active_sheet->setPageSetup($page_setup);
$active_sheet->setShowGridlines(true);
$active_sheet->setPrintGridlines(true);
//Margins:
$active_sheet->getPageMargins()->setTop(0.4);
$active_sheet->getPageMargins()->setRight(0.2);
$active_sheet->getPageMargins()->setLeft(0.2);
$active_sheet->getPageMargins()->setBottom(0.4);
}