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