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->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'),
// )
// );
/**
* Color Font
*/
// $objPHPExcel->getActiveSheet()->getStyle('A6:F6')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE );
/**
*
* @param $from
* @param $to
*/
protected function _exportDetailedStats($from, $to)
{
/* --------------------- EXPORTATION OF THE DETAILED STATS ------------------------ */
$active_sheet = $this->getDocument()->getActiveSheet();
/* ---------------------------------- BY GENDER ----------------------------------- */
$imputationsGlobal = Doctrine_Query::create()->select('COUNT(*) as number, SUM(TIME_TO_SEC(i.duration)) as duration')->from('ImputationArchive i')->where('i.imputation_date BETWEEN ? AND ?', array($from, $to))->fetchOne();
$numberImputations = $imputationsGlobal->getNumber();
$totalDuration = $imputationsGlobal->getDuration();
$active_sheet->setCellValueByColumnAndRow(0, $this->_row, $this->_translate(self::DETAILED_STATS));
$active_sheet->mergeCells('A' . $this->_row . ':D' . $this->_row);
$active_sheet->getStyle('A' . $this->_row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$active_sheet->getStyle('A' . $this->_row)->getFont()->setBold(true);
$this->nextLine(2);
/* --------------------------------- BY GENDER ---------------------------------- */
$this->_exportDetailedValuesByGender($from, $to, $numberImputations, $totalDuration);
$this->nextLine(3);
/* --------------------------------- BY AGE RANGE ---------------------------------- */
$this->_exportDetailedValuesByAgeRange($from, $to, $numberImputations, $totalDuration);
$this->nextLine(3);
/* ------------------------------ BY COUNTRY AND CITY ------------------------------ */
$this->_exportDetailedValuesByCountryAndCity($from, $to, $numberImputations, $totalDuration);
$this->nextLine(3);
/* ---------------------------------- BY DAY AND TIME ------------------------------ */
$this->_exportDetailedValuesByDay($from, $to, $numberImputations, $totalDuration);
$this->nextLine(3);
/* ---------------------------------- BY PUBLIC CATEGORY ------------------------------ */
$this->_exportDetailedValuesByPublicCategory($from, $to, $numberImputations, $totalDuration);
$this->nextLine(3);
/* ---------------------------------- BY PUBLIC CATEGORY ------------------------------ */
$this->_exportDetailedValuesByAct($from, $to, $numberImputations, $totalDuration);
$this->nextLine(3);
/* ---------------------------------- BY PUBLIC CATEGORY ------------------------------ */
$this->_exportDetailedValuesBySeg($from, $to, $numberImputations, $totalDuration);
$this->nextLine(3);
/* ---------------------------------- BY PUBLIC CATEGORY ------------------------------ */
$this->_exportDetailedValuesByBuilding($from, $to, $numberImputations, $totalDuration);
$this->nextLine(3);
/* ---------------------------------- BY PUBLIC CATEGORY ------------------------------ */
$this->_exportDetailedValuesByTypeOfConnection($from, $to, $numberImputations, $totalDuration);
$this->nextLine(3);
/* ---------------------------------- BY PUBLIC CATEGORY ------------------------------ */
$this->_exportDetailedValuesByAwareness($from, $to, $numberImputations, $totalDuration);
$this->nextLine(3);
$active_sheet->getColumnDimension('A')->setWidth(30);
$active_sheet->getColumnDimension('B')->setAutoSize(true);
$active_sheet->getColumnDimension('C')->setWidth(25);
$active_sheet->getColumnDimension('D')->setWidth(8);
$page_setup = new PHPExcel_Worksheet_PageSetup();
$page_setup->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$page_setup->setFitToPage(true);
$page_setup->setFitToWidth(1);
$page_setup->setFitToHeight(0);
$page_setup->setHorizontalCentered(true);
$page_setup->setPrintArea('A1:D' . $this->_row);
$active_sheet->setPageSetup($page_setup);
$active_sheet->setShowGridlines(false);
$active_sheet->setPrintGridlines(false);
//Margins:
$active_sheet->getPageMargins()->setTop(0.6);
$active_sheet->getPageMargins()->setRight(0.4);
$active_sheet->getPageMargins()->setLeft(0.4);
$active_sheet->getPageMargins()->setBottom(0.7);
}
