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); }