/**
  *
  * @param array $data
  * @param $plotType PHPExcel_Chart_DataSeries::TYPE_*
  * @param $plotGrouping PHPExcel_Chart_DataSeries::GROUPING_*
  * @param array $columns
  * @param string $format format for values
  * @param int $positionOffset
  * @return \PHPExcel_Chart
  */
 public static function getChart($data, $plotType, $plotGrouping, $columns = null, $format = null, $positionOffset = 0)
 {
     $columnCount = count($data[0]);
     $rowCount = count($data);
     $keys = array_keys($data[0]);
     $labels = array();
     $categories = array();
     $values = array();
     for ($i = 1; $i < $columnCount; $i++) {
         if (!is_array($columns) || in_array($keys[$i], $columns)) {
             $col = PHPExcel_Cell::stringFromColumnIndex($i);
             $labels[] = new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$' . $col . '$1', null, 1);
             $categories[] = new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$' . ($rowCount + 1), null, $rowCount);
             $values[] = new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$' . $col . '$2:$' . $col . '$' . ($rowCount + 1), $format, $rowCount);
         }
     }
     $series = new PHPExcel_Chart_DataSeries($plotType, $plotGrouping, range(0, count($values) - 1), $labels, $categories, $values);
     $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
     $plotarea = new PHPExcel_Chart_PlotArea(null, array($series));
     $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false);
     $chart = new PHPExcel_Chart('chart' . uniqid(), null, $legend, $plotarea, true, 0, null, null);
     $chart->setTopLeftPosition(PHPExcel_Cell::stringFromColumnIndex($columnCount + 1) . '' . (2 + $positionOffset));
     $chart->setBottomRightPosition(PHPExcel_Cell::stringFromColumnIndex($columnCount + 19) . '' . (20 + $positionOffset));
     return $chart;
 }
Example #2
2
 public function reporteEvaluacion($num)
 {
     $this->load->model("Aula_model", "Aula");
     $this->load->model("Evaluacion_model", "Evaluacion");
     $evaluaciones_all = $this->Evaluacion->getEvaluacioNumero($num);
     //todas
     for ($i = 0, $len = count($evaluaciones_all); $i < $len; $i++) {
         $aulas[$i] = $this->Evaluacion->count_diagnostico($evaluaciones_all[$i]->idAula, $evaluaciones_all[$i]->id);
     }
     // configuramos las propiedades del documento
     $this->phpexcel->getProperties()->setCreator("SoftGroup Perú")->setLastModifiedBy("SoftGroup Perú")->setTitle("Reporte Evaluacion")->setSubject("Evaluacion Nutricional")->setDescription("Reporte Total de Evaluacion")->setKeywords("office 2007 openxml php")->setCategory("Reporte");
     //agrego estilos
     $border_style = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000'))));
     $center_style = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
     $color1_style = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'CEECF5')));
     $this->phpexcel->setActiveSheetIndex(0)->setCellValue('B1', 'I.E.I. “DIVINO NIÑO JESÚS”')->setCellValue('B2', 'REPORTE DE LAS EVALUACIONES N°' . $num)->setCellValue('B3', '')->setCellValue('B4', '');
     $fila = 4;
     // a partir de que fila empezara el listado
     $f2 = $fila + 1;
     $this->phpexcel->getActiveSheet()->getColumnDimension('A')->setWidth(7);
     $this->phpexcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
     $this->phpexcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
     $this->phpexcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
     $this->phpexcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
     $this->phpexcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
     $this->phpexcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
     $this->phpexcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
     $this->phpexcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
     $this->phpexcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
     $sheet = $this->phpexcel->getActiveSheet();
     $this->phpexcel->setActiveSheetIndex(0)->setCellValue('A' . $f2, '#')->setCellValue('B' . $f2, 'Aula')->setCellValue('C' . $f2, 'Normal')->setCellValue('D' . $f2, 'Obeso')->setCellValue('E' . $f2, 'Sobrepeso')->setCellValue('F' . $f2, 'D. Aguda')->setCellValue('G' . $f2, 'D. Severa')->setCellValue('H' . $f2, 'D. Crónica')->setCellValue('I' . $f2, 'Sin Diagnóstico')->setCellValue('J' . $f2, 'Total');
     $con = 1;
     $t_normales = 0;
     $t_obesos = 0;
     $t_sobrepesos = 0;
     $t_agudas = 0;
     $t_severos = 0;
     $t_cronicos = 0;
     $t_sindiag = 0;
     $t_totales = 0;
     foreach ($aulas as $key) {
         //fila + 1
         foreach ($key as $v) {
             $this->phpexcel->setActiveSheetIndex(0)->setCellValue('A' . ($f2 + $con), $con)->setCellValue('B' . ($f2 + $con), $v->aula)->setCellValue('C' . ($f2 + $con), $v->normales)->setCellValue('D' . ($f2 + $con), $v->obesos)->setCellValue('E' . ($f2 + $con), $v->sobrepesos)->setCellValue('F' . ($f2 + $con), $v->agudas)->setCellValue('G' . ($f2 + $con), $v->severos)->setCellValue('H' . ($f2 + $con), $v->cronicos)->setCellValue('I' . ($f2 + $con), $v->sindiag)->setCellValue('J' . ($f2 + $con), $v->totales);
             $t_normales += $v->normales;
             $t_obesos += $v->obesos;
             $t_sobrepesos += $v->sobrepesos;
             $t_agudas += $v->agudas;
             $t_severos += $v->severos;
             $t_cronicos += $v->cronicos;
             $t_sindiag += $v->sindiag;
             $t_totales += $v->totales;
         }
         //end foreach
         $con++;
     }
     //end forreach $aula
     $f2 += $con;
     $this->phpexcel->setActiveSheetIndex(0)->setCellValue('A' . $f2, ' ')->setCellValue('B' . $f2, 'Total')->setCellValue('C' . $f2, $t_normales)->setCellValue('D' . $f2, $t_obesos)->setCellValue('E' . $f2, $t_sobrepesos)->setCellValue('F' . $f2, $t_agudas)->setCellValue('G' . $f2, $t_severos)->setCellValue('H' . $f2, $t_cronicos)->setCellValue('I' . $f2, $t_sindiag)->setCellValue('J' . $f2, $t_totales);
     //GRAFICAS
     $dataseriesLabels1 = array(new PHPExcel_Chart_DataSeriesValues('String', 'Reporte!$J$' . $f2, NULL, 1));
     $xAxisTickValues = array(new PHPExcel_Chart_DataSeriesValues('String', 'Reporte!$C$' . ($fila + 1) . ':$J$' . ($fila + 1), NULL, 5));
     $dataSeriesValues1 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Reporte!$C$' . $f2 . ':$J$' . $f2, NULL, 5));
     //  Build the dataseries
     $series1 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_BARCHART, PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED, range(0, count($dataSeriesValues1) - 1), $dataseriesLabels1, $xAxisTickValues, $dataSeriesValues1);
     $series1->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
     //  Set the series in the plot area
     $plotarea = new PHPExcel_Chart_PlotArea(NULL, array($series1));
     //  Set the chart legend
     $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
     $title = new PHPExcel_Chart_Title('Reporte de las Evaluaciones N°' . $num);
     //  Create the chart
     $chart = new PHPExcel_Chart('chart1', $title, $legend, $plotarea, true, 0, NULL, NULL);
     //  Set the position where the chart should appear in the worksheet
     $chart->setTopLeftPosition('A' . ($f2 + 2));
     $chart->setBottomRightPosition('G' . ($f2 + 14));
     //  Add the chart to the worksheet
     $sheet->addChart($chart);
     //PIE CHART
     $series2 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_PIECHART, null, range(0, count($dataSeriesValues1) - 1), $dataseriesLabels1, $xAxisTickValues, $dataSeriesValues1);
     //	Set up a layout object for the Pie chart
     $layout1 = new PHPExcel_Chart_Layout();
     $layout1->setShowVal(TRUE);
     $layout1->setShowPercent(TRUE);
     //	Set the series in the plot area
     $plotarea2 = new PHPExcel_Chart_PlotArea($layout1, array($series2));
     //  Set the chart legend
     $chart2 = new PHPExcel_Chart('chart2', $title, $legend, $plotarea2, true, 0, NULL, NULL);
     //  Set the position where the chart should appear in the worksheet
     $chart2->setTopLeftPosition('A' . ($f2 + 15));
     $chart2->setBottomRightPosition('G' . ($f2 + 35));
     //  Add the chart to the worksheet
     $sheet->addChart($chart2);
     // renombro la hoja de trabajo con el nombre del aula
     $this->phpexcel->getActiveSheet()->setTitle('Reporte');
     $sheet->getStyle("A" . ($fila + 1) . ":J" . $f2)->applyFromArray($border_style);
     $sheet->getStyle("A" . ($fila + 1) . ":J" . ($fila + 1))->applyFromArray($center_style)->getFont()->setBold(true);
     $sheet->getStyle("A" . $f2 . ":J" . $f2)->applyFromArray($center_style)->getFont()->setBold(true);
     $sheet->getStyle("A" . ($fila + 1) . ":J" . ($fila + 1))->applyFromArray($color1_style);
     $sheet->getStyle("A" . $f2 . ":J" . $f2)->applyFromArray($color1_style);
     $this->phpexcel->setActiveSheetIndex(0);
     //redireccionamos la salida al navegador del cliente (Excel2007)
     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     header('Content-Disposition: attachment;filename="Reporte Evaluacion ' . $num . '.xlsx"');
     header('Cache-Control: max-age=0');
     $objWriter = PHPExcel_IOFactory::createWriter($this->phpexcel, 'Excel2007');
     $objWriter->setIncludeCharts(TRUE);
     $objWriter->save('php://output');
 }
Example #3
2
 public function getExportMintic()
 {
     $param = (object) $_POST;
     if (!isset($param->percentStartDownload) || !is_numeric($param->percentStartDownload)) {
         $param->percentStartDownload = 95;
     }
     if (!isset($param->percentEndDownload) || !is_numeric($param->percentEndDownload)) {
         $param->percentEndDownload = 110;
     }
     if (!isset($param->percentStartUpload) || !is_numeric($param->percentStartUpload)) {
         $param->percentStartUpload = 95;
     }
     if (!isset($param->percentEndUpload) || !is_numeric($param->percentEndUpload)) {
         $param->percentEndUpload = 110;
     }
     $where = "AND ( `pdownload` BETWEEN '" . $param->percentStartDownload . "' AND '" . $param->percentEndDownload . "' )" . " AND ( `pupload` BETWEEN '" . $param->percentStartUpload . "' AND '" . $param->percentEndUpload . "' )" . " AND ( `testDate` BETWEEN '" . $param->from . " 00:00:00' AND '" . $param->to . " 23:59:59')";
     $getSpeedtestSQL = "SELECT H.`id_host`, HD1.idLocation, \r\n\t\t\t\t\t\t\t'100' as 'mEESperada',\r\n\t\t\t\t\t\t\tSUM(IF(RP.`pdownload` >= 100,1,0)) AS 'mDownloadOk',  \r\n\t\t\t\t \t\t\tSUM(IF(RP.`pdownload` < 100,1,0)) AS 'mDownloadFallidas' ,  \r\n\t\t\t\t \t\t\tSUM(IF(RP.`pupload` >= 100,1,0)) AS 'mUploadOk',  \r\n\t\t\t\t \t\t\tSUM(IF(RP.`pupload` < 100,1,0)) AS 'mUploadFallidas', \r\n\t\t\t\t \t\t\tROUND(AVG(RP.`download`) / 1024)  AS 'vavgdown', \r\n\t\t\t\t \t\t\tROUND(AVG(RP.`upload`)  / 1024) AS 'vavgupload',\r\n\t\t\t\t\t\t\t'0' as 'cumplimientoDown',\r\n\t\t\t\t \t\t\t'0' as 'cumplimientoUpload' , \r\n\t\t\t\t \t\t\tL.`additionalForm` , \r\n\t\t\t\t \t\t\tL.`minTest` , \r\n\t\t\t\t \t\t\tCONCAT('SDS ', HD2.city ,' (', L.`city`,')') AS 'name'\r\n\t\t\t\t\t\t\tFROM `bm_host` H \r\n\t\t\t\t\t\t\t\tLEFT OUTER JOIN (SELECT  `id_host`, `value` as 'idLocation' FROM `bm_host_detalle` WHERE `id_feature` = 78) HD1 ON ( H.`id_host`=HD1.`id_host`)\r\n\t\t\t\t\t\t\t\tLEFT OUTER JOIN (SELECT  `id_host`, `value` as 'city' FROM `bm_host_detalle` WHERE `id_feature` = 79) HD2 ON ( H.`id_host`=HD2.`id_host`)\r\n\t\t\t\t\t\t\t\tLEFT OUTER JOIN `bm_location` L ON L.`idLocation`=HD1.`idLocation`\r\n\t\t\t\t\t\t\t\tLEFT OUTER JOIN `report_speedtest` RP ON RP.`idHost`=H.`id_host` {$where}\r\n\t\t\t\t\t\tWHERE  H.`groupid` =  " . $param->groupid . " AND H.`borrado` = 0 AND  L.`additionalForm` IS NOT NULL  \r\n\t\t\t\t\t\tGROUP BY name";
     $getSpeedtestRESULT = $this->conexion->queryFetch($getSpeedtestSQL);
     if ($getSpeedtestRESULT) {
         foreach ($getSpeedtestRESULT as $key => $value) {
             $detailJson = json_decode($value['additionalForm']);
             foreach ($detailJson as $keyJson => $valueJson) {
                 $detail[$valueJson->key] = $valueJson->value;
             }
             $cumplimientoDown = 100 * $value['mDownloadOk'] / $value['minTest'];
             $cumplimientoUpload = 100 * $value['mUploadOk'] / $value['minTest'];
             $pageData[$detail['division']][] = array('name' => $value['name'], 'mEESperada' => $value['minTest'], 'mDownloadOk' => $value['mDownloadOk'], 'mDownloadFallidas' => $value['mDownloadFallidas'], 'mUploadOk' => $value['mUploadOk'], 'mUploadFallidas' => $value['mUploadFallidas'], 'vavgdown' => is_null($value['vavgdown']) ? 0 : $value['vavgdown'], 'vavgupload' => is_null($value['vavgupload']) ? 0 : $value['vavgupload'], 'cumplimientoDown' => number_format($cumplimientoDown, 0, ',', ''), 'cumplimientoUpload' => number_format($cumplimientoUpload, 0, ',', ''));
         }
     }
     $header = array('SDS (Municipio)', 'Muestras a Realizar', 'Muestras Down Ok', 'Muestras Down Fallidas', 'Muestras Up Ok', 'Muestras Up Fallidas', 'Vel. prom. Down', 'Vel. prom. Up', 'Cumplimiento Cantidad Muestras Down OK', 'Cumplimiento Cantidad Muestras Up OK');
     include APPS . "plugins/PHPExcel.php";
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->getProperties()->setCreator("Baking software")->setLastModifiedBy("bMonitor")->setTitle($this->language->GENERAL_REPORT)->setSubject("Export")->setDescription("Documento generado por report manager de bMonitor")->setKeywords("report")->setCategory("bMonitor");
     $page = 0;
     $lineStart = 38;
     $styleHeader = array('font' => array('bold' => true, 'size' => '10'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 90, 'startcolor' => array('argb' => 'e3ae8c'), 'endcolor' => array('argb' => 'e3ae8c')));
     $styleCell = array('font' => array('size' => '10'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 90, 'startcolor' => array('argb' => 'a0e8bd'), 'endcolor' => array('argb' => 'a0e8bd')));
     $styleCellWarning = array('font' => array('size' => '10'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('argb' => 'F47C00'), 'endcolor' => array('argb' => 'F47C00')));
     $styleCellCritical = array('font' => array('size' => '10'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('argb' => 'D11600'), 'endcolor' => array('argb' => 'D11600')));
     //Llenado paginas
     foreach ($pageData as $key => $value) {
         $letter = 65;
         $line = $lineStart;
         if ($page > 0) {
             $objPHPExcel->createSheet();
         }
         $objPHPExcel->setActiveSheetIndex($page);
         $objPHPExcel->getActiveSheet()->setTitle($key);
         $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
         $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(6);
         $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(6);
         $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(14);
         $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(16);
         $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(31);
         $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Resultados Pruebas MinTic desde SDS - ' . $key);
         $objPHPExcel->getActiveSheet()->mergeCells('A1:J1');
         $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         //Graficos
         $locationCount = count($value);
         $dataseriesLabels1 = array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$38', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$38', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$38', NULL, 1));
         $dataseriesLabels2 = array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$38', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$E$38', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$F$38', NULL, 1));
         $xAxisTickValues = array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$39:$A$' . (39 + $locationCount), NULL, $locationCount));
         $dataSeriesValues1 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$39:$B$' . (39 + $locationCount), NULL, $locationCount), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$39:$C$' . (39 + $locationCount), NULL, $locationCount), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$39:$D$' . (39 + $locationCount), NULL, $locationCount));
         $dataSeriesValues2 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$39:$B$' . (39 + $locationCount), NULL, $locationCount), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$E$39:$E$' . (39 + $locationCount), NULL, $locationCount), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$F$39:$F$' . (39 + $locationCount), NULL, $locationCount));
         $series1 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_BARCHART, PHPExcel_Chart_DataSeries::GROUPING_STACKED, range(0, count($dataSeriesValues1) - 1), $dataseriesLabels1, $xAxisTickValues, $dataSeriesValues1);
         $series2 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_BARCHART, PHPExcel_Chart_DataSeries::GROUPING_STACKED, range(0, count($dataSeriesValues2) - 1), $dataseriesLabels2, $xAxisTickValues, $dataSeriesValues2);
         $series1->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
         $series2->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
         $plotarea1 = new PHPExcel_Chart_PlotArea(NULL, array($series1));
         $plotarea2 = new PHPExcel_Chart_PlotArea(NULL, array($series2));
         $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_BOTTOM, NULL, false);
         $title1 = new PHPExcel_Chart_Title('Muestras Down');
         $title2 = new PHPExcel_Chart_Title('Muestras UP');
         //	Create the chart
         $chart1 = new PHPExcel_Chart('chart1', $title1, $legend, $plotarea1, true, 0, NULL, NULL);
         $chart2 = new PHPExcel_Chart('chart2', $title2, $legend, $plotarea2, true, 0, NULL, NULL);
         $chart1->setTopLeftPosition('A12');
         $chart1->setBottomRightPosition('F31');
         $chart2->setTopLeftPosition('H12');
         $chart2->setBottomRightPosition('N31');
         $objPHPExcel->getActiveSheet()->addChart($chart1);
         $objPHPExcel->getActiveSheet()->addChart($chart2);
         //Tabla
         foreach ($header as $keyHeader => $valueHeader) {
             $objPHPExcel->setActiveSheetIndex($page)->setCellValue(chr($letter) . $line, $valueHeader);
             $letter++;
         }
         $objPHPExcel->setActiveSheetIndex($page)->getStyle(chr(65) . $line . ":" . chr($letter - 1) . $line)->applyFromArray($styleHeader);
         $objPHPExcel->setActiveSheetIndex($page)->getStyle(chr(66) . $line . ":" . chr($letter - 1) . $line)->getAlignment()->setWrapText(true);
         $line++;
         foreach ($value as $keyLine => $valueLine) {
             $letter = 65;
             foreach ($valueLine as $celdaKey => $celdaValue) {
                 if ($celdaKey == 'cumplimientoDown' || $celdaKey == 'cumplimientoUpload') {
                     $unit = '%';
                 } else {
                     $unit = '';
                 }
                 $objPHPExcel->setActiveSheetIndex($page)->setCellValue(chr($letter) . $line, $celdaValue . $unit);
                 $letter++;
             }
             $objPHPExcel->setActiveSheetIndex($page)->getStyle(chr(71) . $line . ":" . chr($letter - 1) . $line)->applyFromArray($styleCell);
             $objPHPExcel->setActiveSheetIndex($page)->getStyle(chr(66) . $line . ":" . chr($letter - 1) . $line)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
             if ($valueLine['cumplimientoDown'] < 100 && $valueLine['cumplimientoDown'] > 0) {
                 $objPHPExcel->setActiveSheetIndex($page)->getStyle(chr(73) . $line . ":" . chr($letter - 1) . $line)->applyFromArray($styleCellWarning);
             } elseif ($valueLine['cumplimientoDown'] == 0) {
                 $objPHPExcel->setActiveSheetIndex($page)->getStyle(chr(71) . $line . ":" . chr($letter - 1) . $line)->applyFromArray($styleCellCritical);
             }
             $line++;
         }
         $page++;
     }
     $objPHPExcel->setActiveSheetIndex(0);
     $file = 'qoe_report_mintic_' . $param->from . "_" . $param->to . "_" . date("Ymd") . '.xlsx';
     $path = site_path . '/upload/';
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     $objWriter->setIncludeCharts(TRUE);
     $objWriter->save($path . $file);
     $result['status'] = true;
     $result['file'] = $file;
     $this->basic->jsonEncode($result);
     exit;
 }
Example #4
1
 protected function getChart($first_row, $last_row, $top_chart, $bottom_chart)
 {
     $first_row += 1;
     $labels = array(new DataSeriesValues('String', 'Encuesta!$C$' . $first_row, null, 1), new DataSeriesValues('String', 'Encuesta!$C$' . $last_row, null, 1));
     $xLabels = array(new DataSeriesValues('String', 'Encuesta!$C$' . $first_row, null, 1), new DataSeriesValues('String', 'Encuesta!$C$' . $last_row, null, 1));
     $yValues = array(new DataSeriesValues('Number', 'Encuesta!$D$' . $first_row, null, 1), new DataSeriesValues('Number', 'Encuesta!$D$' . $last_row, null, 1));
     $series1 = new DataSeries(DataSeries::TYPE_BARCHART, DataSeries::GROUPING_CLUSTERED, range(0, count($yValues) - 1), $labels, $xLabels, $yValues);
     $series1->setPlotDirection(DataSeries::DIRECTION_BAR);
     $layout1 = new Layout();
     $layout1->setShowVal(true);
     $layout1->setShowPercent(true);
     $plotarea1 = new PlotArea($layout1, array($series1));
     $legend1 = new Legend(Legend::POSITION_RIGHT, null, false);
     $titulo = new PHPExcel_Chart_Title(utf8_encode("Gráfico - " . $this->getOrden()));
     $chart1 = new PHPExcel_Chart('chart' . $this->getSerial(), $titulo, $legend1, $plotarea1, true, 0, null, null);
     $chart1->setTopLeftPosition("F" . $top_chart);
     $chart1->setBottomRightPosition("J" . $bottom_chart);
     return $chart1;
 }
Example #5
1
 public function excel_create_chartsheet_for_faults($charttype, $datasheetname, $dataendrow, $graphname)
 {
     //	Set the Labels for each data series we want to plot
     //		Datatype
     //		Cell reference for data
     //		Format Code
     //		Number of datapoints in series
     //		Data values
     //		Data Marker
     $datasheetname = $datasheetname;
     $dataseriesLabels = array(new PHPExcel_Chart_DataSeriesValues('String', $datasheetname . '!$B$2:$D$2', NULL, 1));
     $xAxis_count = $dataendrow;
     //	Set the X-Axis Labels
     //		Datatype
     //		Cell reference for data
     //		Format Code
     //		Number of datapoints in series
     //		Data values
     //		Data Marker
     $xAxisTickValues = array(new PHPExcel_Chart_DataSeriesValues('String', $datasheetname . '!$A$3:$C$' . $xAxis_count, NULL, $xAxis_count - 1));
     //	Set the Data values for each data series we want to plot
     //		Datatype
     //		Cell reference for data
     //		Format Code
     //		Number of datapoints in series
     //		Data values
     //		Data Marker
     $dataSeriesValues = array(new PHPExcel_Chart_DataSeriesValues('Number', $datasheetname . '!$D$3:$D$' . $xAxis_count, NULL, $xAxis_count - 1));
     //	Build the dataseries
     if ($charttype == "饼状图") {
         $series = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_PIECHART, PHPExcel_Chart_DataSeries::GROUPING_STACKED, range(0, count($dataSeriesValues) - 1), $dataseriesLabels, $xAxisTickValues, $dataSeriesValues);
         //	Set up a layout object for the Pie chart
         $layout1 = new PHPExcel_Chart_Layout();
         $layout1->setShowVal(TRUE);
         $layout1->setShowPercent(TRUE);
     }
     /*
     		else if($charttype == "曲线图"){
     			$series = new PHPExcel_Chart_DataSeries( 
     				PHPExcel_Chart_DataSeries::TYPE_LINECHART,		// plotType
     				PHPExcel_Chart_DataSeries::GROUPING_STACKED,	// plotGrouping
     				range(0, count($dataSeriesValues)-1),			// plotOrder
     				$dataseriesLabels,								// plotLabel
     				$xAxisTickValues,								// plotCategory
     				$dataSeriesValues								// plotValues
     			);
     		}
     		else{	
     			$series = new PHPExcel_Chart_DataSeries(
     				PHPExcel_Chart_DataSeries::TYPE_BARCHART,		// plotType
     				PHPExcel_Chart_DataSeries::GROUPING_STANDARD,	// plotGrouping
     	//			PHPExcel_Chart_DataSeries::TYPE_LINECHART,		// plotType
     	//			PHPExcel_Chart_DataSeries::GROUPING_STACKED,	// plotGrouping
     				range(0, count($dataSeriesValues)-1),			// plotOrder
     				$dataseriesLabels,								// plotLabel
     				$xAxisTickValues,								// plotCategory
     				$dataSeriesValues								// plotValues
     			);
     		}*/
     $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
     //	Set the series in the plot area
     if ($charttype == "饼状图") {
         $plotarea = new PHPExcel_Chart_PlotArea($layout1, array($series));
     } else {
         //	$plotarea = new PHPExcel_Chart_PlotArea(NULL, array($series));
     }
     //	Set the chart legend
     $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_TOPRIGHT, NULL, false);
     //		$title = new PHPExcel_Chart_Title('Test Stacked Line Chart');
     //		$yAxisLabel = new PHPExcel_Chart_Title('Value ($k)');
     $title = new PHPExcel_Chart_Title($graphname);
     $yAxisLabel = new PHPExcel_Chart_Title('');
     //	Create the chart
     $chart = new PHPExcel_Chart('chart1', $title, $legend, $plotarea, true, 0, NULL, $yAxisLabel);
     //	Set the position where the chart should appear in the worksheet
     $chart->setTopLeftPosition('F2');
     $chart->setBottomRightPosition('S26');
     return $chart;
 }
 public function _makeChartFromSheetData($dataStartCol, $dataStartRow, $dataEndCol, $dataEndRow, $chartHeight, $chartWidth)
 {
     $worksheetName = $this->objWorksheet->getTitle();
     $seriesStartRow = $dataStartRow + 1;
     $seriesTotalCols = $dataStartCol + ($dataEndCol - 1);
     $dataSeriesLabels = $xAxisTickValues = $dataSeriesValues = array();
     $totalRows = $dataEndRow - 1;
     // Basic structure  of sheet data
     /* $objWorksheet->fromArray(
        array(
        array('', 2010, 2011, 2012),
        array('Q1', 12, 15, 21),
        array('Q2', 56, 73, 86),
        array('Q3', 52, 61, 69),
        array('Q4', 30, 32, 0),
        )
        ); */
     // Labels for dataseries.  Direction ===>
     for ($i = $dataStartCol; $i <= $seriesTotalCols; $i++) {
         array_push($dataSeriesLabels, new PHPExcel_Chart_DataSeriesValues('String', $worksheetName . '!$' . $this->alphabets[$dataStartCol] . '$' . $i, NULL, 1));
     }
     for ($j = $dataStartRow; $j <= $totalRows; $j++) {
         array_push($xAxisTickValues, new PHPExcel_Chart_DataSeriesValues('String', $worksheetName . '!$' . $this->alphabets[$j] . '$' . $dataStartCol, NULL, 1));
     }
     for ($i = $dataStart; $i <= $dataEnd; $i++) {
         array_push($dataSeriesValues, new PHPExcel_Chart_DataSeriesValues('Number', $worksheetName . '!$' . $this->alphabets[$col + 1] . '$' . $i . ':$' . $this->alphabets[$col + 3] . '$' . $i, NULL, 3));
     }
     $series = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_BARCHART, PHPExcel_Chart_DataSeries::GROUPING_STACKED, range(0, count($dataSeriesValues) - 1), $dataSeriesLabels, $xAxisTickValues, $dataSeriesValues);
     // new PHPExcel_Chart_DataSeries($plotType, $plotGrouping, $plotOrder, $plotLabel, $plotCategory, $plotValues, $smoothLine, $plotStyle);
     $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
     //	Set the series in the plot area
     $plotArea = new PHPExcel_Chart_PlotArea(NULL, array($series));
     //	Set the chart legend
     if ($type == 3) {
         $legend = null;
     } else {
         $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
     }
     //$legend->
     //$legend->setOverlay(true);
     $chrtTitle = ' ';
     $title = new PHPExcel_Chart_Title($chrtTitle);
     $yAxisLabel = new PHPExcel_Chart_Title('');
     $chart = new PHPExcel_Chart(null, $title, $legend, $plotArea, true, 0, NULL, $yAxisLabel);
     //	Set the position where the chart should appear in the worksheet
     $chart->setTopLeftPosition($this->alphabets[$col] . $row);
     $chart->setBottomRightPosition($this->alphabets[$col + $width] . ($row + $chartHeight));
     //
     //	Add the chart to the worksheet
     $this->objWorksheet->addChart($chart);
 }
//		Cell reference for data
//		Format Code
//		Number of datapoints in series
//		Data values
//		Data Marker
$xAxisTickValues2 = array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$5', NULL, 4));
//	Set the Data values for each data series we want to plot
//		Datatype
//		Cell reference for data
//		Format Code
//		Number of datapoints in series
//		Data values
//		Data Marker
$dataSeriesValues2 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$5', NULL, 4), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$5', NULL, 4), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$5', NULL, 4));
//	Build the dataseries
$series2 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_BARCHART, PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dataSeriesValues2) - 1), $dataseriesLabels2, $xAxisTickValues2, $dataSeriesValues2);
//	Set additional dataseries parameters
//		Make it a vertical column rather than a horizontal bar graph
$series2->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
//	Set the series in the plot area
$plotarea2 = new PHPExcel_Chart_PlotArea(NULL, array($series2));
//	Set the chart legend
$legend2 = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
$title2 = new PHPExcel_Chart_Title('Test Column Chart');
$yAxisLabel2 = new PHPExcel_Chart_Title('Value ($k)');
//	Create the chart
$chart2 = new PHPExcel_Chart('chart2', $title2, $legend2, $plotarea2, true, 0, NULL, $yAxisLabel2);
//	Set the position where the chart should appear in the worksheet
$chart2->setTopLeftPosition('I7');
$chart2->setBottomRightPosition('P20');
//	Add the chart to the worksheet
//		Cell reference for data
//		Format Code
//		Number of datapoints in series
//		Data values
//		Data Marker
$xAxisTickValues = array(new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$5', null, 4));
//	Set the Data values for each data series we want to plot
//		Datatype
//		Cell reference for data
//		Format Code
//		Number of datapoints in series
//		Data values
//		Data Marker
$dataSeriesValues = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$5', null, 4), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$5', null, 4), new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$5', null, 4));
//	Build the dataseries
$series = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_BARCHART, PHPExcel_Chart_DataSeries::GROUPING_STACKED, range(0, count($dataSeriesValues) - 1), $dataseriesLabels, $xAxisTickValues, $dataSeriesValues);
//	Set additional dataseries parameters
//		Make it a horizontal bar rather than a vertical column graph
$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_BAR);
//	Set the series in the plot area
$plotarea = new PHPExcel_Chart_PlotArea(null, array($series));
//	Set the chart legend
$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false);
$title = new PHPExcel_Chart_Title('Test Chart');
$yAxisLabel = new PHPExcel_Chart_Title('Value ($k)');
//	Create the chart
$chart = new PHPExcel_Chart('chart1', $title, $legend, $plotarea, true, 0, null, $yAxisLabel);
//	Set the position where the chart should appear in the worksheet
$chart->setTopLeftPosition('A7');
$chart->setBottomRightPosition('H20');
//	Add the chart to the worksheet
Example #9
0
 public function exportAnualFile()
 {
     $this->load->library('excel');
     //get Info Evaluados
     $colaboradores = $this->evaluacion_model->getEvaluados();
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->getProperties()->setCreator("Portal de Evaluación Advanzer de México")->setLastModifiedBy("Portal de Evaluación")->setTitle("Reporte Anual de Evaluación")->setSubject("Reporte Anual de Evaluación")->setDescription("Concentrado de resultados de evaluación anual y proyectos durante el año en cuestión");
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     $objSheet = $objPHPExcel->getActiveSheet(0);
     $objSheet->setTitle('Junta_Anual');
     $objSheet->getStyle('A1:Q1')->getFont()->setBold(true)->setName('Verdana')->setSize(11)->getColor()->setRGB('FFFFFF');
     $objSheet->getStyle('A1:Q1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
     $objSheet->getStyle('A1:Q1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
     $objSheet->getStyle('A1:Q1')->getFill('')->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => '000A75')));
     $objSheet->getRowDimension(1)->setRowHeight(50);
     // write header
     $objSheet->getCell('A1')->setValue('No.');
     $objSheet->getCell('B1')->setValue('Colaborador');
     $objSheet->getCell('C1')->setValue('Fecha de Ingreso');
     $objSheet->getCell('D1')->setValue('Track');
     $objSheet->getCell('E1')->setValue('Posición');
     $objSheet->getCell('F1')->setValue('Evaluadores');
     $objSheet->getCell('G1')->setValue('Competencias');
     $objSheet->getCell('H1')->setValue('Responsabilidades');
     $objSheet->getCell('I1')->setValue('Promedio');
     $objSheet->getCell('J1')->setValue('Rating 2012');
     $objSheet->getCell('K1')->setValue('Rating 2013');
     $objSheet->getCell('L1')->setValue('Rating 2014');
     $objSheet->getCell('M1')->setValue('Rating 2015');
     $objSheet->getCell('N1')->setValue('Encargado de Feedback');
     $objSheet->getCell('O1')->setValue('Comentarios de Desempeño');
     $objSheet->getCell('P1')->setValue('Observaciones de la Junta');
     $objSheet->getCell('Q1')->setValue('Acciones a Tomar');
     $column = 1;
     foreach ($colaboradores as $colaborador) {
         $column++;
         $total_c = 0;
         $total_r = 0;
         if ($colaborador->nivel_posicion <= 5) {
             $total_c = ($colaborador->autoevaluacion + $colaborador->tres60 + $colaborador->competencias) / 3;
         } else {
             $total_c = ($colaborador->autoevaluacion + $colaborador->competencias) / 2;
         }
         if (isset($colaborador->proyectos)) {
             $total_r = ($colaborador->responsabilidades + $colaborador->proyectos) / 2;
         } else {
             $total_r = $colaborador->responsabilidades;
         }
         ($res = $this->user_model->getHistorialByIdAnio($colaborador->id, '2012')) ? $colaborador->rating_2012 = $res->rating : ($colaborador->rating_2012 = null);
         ($res = $this->user_model->getHistorialByIdAnio($colaborador->id, '2013')) ? $colaborador->rating_2013 = $res->rating : ($colaborador->rating_2013 = null);
         ($res = $this->user_model->getHistorialByIdAnio($colaborador->id, '2014')) ? $colaborador->rating_2014 = $res->rating : ($colaborador->rating_2014 = null);
         $evaluadores = "";
         $comentarios = "";
         foreach ($colaborador->evaluadores as $evaluador) {
             $evaluadores .= $evaluador->nombre . "\n";
             $comentarios .= $evaluador->comentarios . "\n";
         }
         if (isset($colaborador->evaluadoresProyecto)) {
             foreach ($colaborador->evaluadoresProyecto as $evaluador) {
                 $evaluadores .= $evaluador->nombre . "\n";
                 $comentarios .= $evaluador->comentarios . "\n";
             }
         }
         if ($colaborador->nivel_posicion <= 5) {
             $cont = 0;
             if (isset($colaborador->evaluadores360)) {
                 foreach ($colaborador->evaluadores360 as $evaluador) {
                     $evaluadores .= $evaluador->nombre . "\n";
                     $comentarios .= $evaluador->comentarios . "\n";
                     $cont++;
                 }
             }
         }
         $objSheet->getCell('A' . $column)->setValue($colaborador->nomina);
         $objSheet->getCell('B' . $column)->setValue($colaborador->nombre);
         $objSheet->getCell('C' . $column)->setValue($colaborador->fecha_ingreso);
         $objSheet->getCell('D' . $column)->setValue($colaborador->track);
         $objSheet->getCell('E' . $column)->setValue($colaborador->posicion);
         $objSheet->getCell('F' . $column)->setValue($evaluadores);
         $objSheet->getCell('G' . $column)->setValue(number_format(floor($total_c * 100) / 100, 2));
         $objSheet->getCell('H' . $column)->setValue(number_format(floor($total_r * 100) / 100, 2));
         $objSheet->getCell('I' . $column)->setValue(number_format(floor($colaborador->total * 100) / 100, 2));
         $objSheet->getCell('J' . $column)->setValue($colaborador->rating_2012);
         $objSheet->getCell('K' . $column)->setValue($colaborador->rating_2013);
         $objSheet->getCell('L' . $column)->setValue($colaborador->rating_2014);
         $objSheet->getCell('O' . $column)->setValue($comentarios);
         $objSheet->getStyle('A' . $column . ':' . 'Q' . $column)->getAlignment()->setWrapText(true);
         $objSheet->getRowDimension($column)->setRowHeight(-1);
     }
     $objSheet->getStyle('C2:C' . $column)->getNumberFormat()->setFormatCode('yyyy-mm-dd');
     $objSheet->getStyle('A2:Q' . $column)->getFont()->setSize(12);
     $objSheet->getStyle('G2:H' . $column)->getFont()->setSize(16);
     $objSheet->getStyle('I2:I' . $column)->getFont()->setSize(18);
     $objSheet->getStyle('M2:M' . $column)->getFont()->setSize(24);
     $objSheet->getStyle('A2:Q' . $column)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
     // create some borders
     // first, create the whole grid around the table
     $objSheet->getStyle('A1:Q' . $column)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
     // create medium border around the table
     $objSheet->getStyle('A1:Q' . $column)->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
     // create a medium border on the header line
     $objSheet->getStyle('A1:Q1')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
     // autosize the columns
     $objSheet->getColumnDimension('A')->setAutoSize(true);
     $objSheet->getColumnDimension('B')->setAutoSize(true);
     $objSheet->getColumnDimension('C')->setAutoSize(true);
     $objSheet->getColumnDimension('D')->setAutoSize(true);
     $objSheet->getColumnDimension('E')->setAutoSize(true);
     $objSheet->getColumnDimension('F')->setWidth(40);
     $objSheet->getColumnDimension('G')->setAutoSize(true);
     $objSheet->getColumnDimension('H')->setAutoSize(true);
     $objSheet->getColumnDimension('I')->setAutoSize(true);
     $objSheet->getColumnDimension('J')->setAutoSize(true);
     $objSheet->getColumnDimension('K')->setAutoSize(true);
     $objSheet->getColumnDimension('L')->setAutoSize(true);
     $objSheet->getColumnDimension('M')->setAutoSize(true);
     $objSheet->getColumnDimension('N')->setWidth(20);
     $objSheet->getColumnDimension('O')->setWidth(40);
     $objSheet->getColumnDimension('P')->setWidth(40);
     $objSheet->getColumnDimension('Q')->setWidth(40);
     //tabla de resumen en hoja principal
     $column += 5;
     $objSheet->getStyle('G' . $column . ':J' . ($column + 5))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
     // create medium border around the table
     $objSheet->getStyle('G' . $column . ':J' . ($column + 5))->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
     // create a medium border on the header line
     $objSheet->getStyle('G' . $column . ':J' . $column)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
     $objSheet->getStyle('G' . $column . ':J' . ($column + 6))->getFont()->setBold(true)->setName('Verdana')->setSize(10);
     $objSheet->getStyle('I' . ($column + 1) . ':I' . ($column + 6))->getFont()->setBold(true)->setName('Verdana')->setSize(10)->getColor()->setRGB('FF0000');
     $objSheet->getCell('G' . $column)->setValue('Rating');
     $objSheet->getCell('H' . $column)->setValue('Conteo');
     $objSheet->getCell('I' . $column)->setValue('% Real');
     $objSheet->getCell('J' . $column)->setValue('% Requerida');
     $objSheet->getStyle('I' . ($column + 1) . ':J' . ($column + 6))->getNumberFormat()->applyFromArray(array('code' => PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00));
     $letras = array('A', 'B', 'C', 'D', 'E', '.05', '.15', '.65', '.10', '.05');
     for ($i = 1; $i <= 5; $i++) {
         $objSheet->getCell('G' . ($column + $i))->setValue($letras[$i - 1]);
         $objSheet->getCell('H' . ($column + $i))->setValue('=COUNTIF($M$2:$M$' . ($column - 5) . ',"' . $letras[$i - 1] . '")');
         $objSheet->getCell('I' . ($column + $i))->setValue('=H' . ($column + $i) . '/$H$' . ($column + 6));
         $objSheet->getCell('J' . ($column + $i))->setValue($letras[$i + 4]);
     }
     $column += 6;
     $objSheet->getCell('H' . $column)->setValue('=SUM(H' . ($column - 5) . ':H' . ($column - 1) . ')');
     $objSheet->getCell('I' . $column)->setValue('=SUBTOTAL(9,I' . ($column - 5) . ':I' . ($column - 1) . ')');
     $objSheet->getCell('J' . $column)->setValue('=SUBTOTAL(9,J' . ($column - 5) . ':J' . ($column - 1) . ')');
     //line chart
     $objSheet = $objPHPExcel->createSheet(1);
     $objSheet->setTitle('Resumen');
     //data series label
     $dsl = array(new PHPExcel_Chart_DataSeriesValues('String', 'Junta_Anual!I' . ($column - 6), null, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Junta_Anual!J' . ($column - 6), null, 1));
     //X axis value label
     $xal = array(new PHPExcel_Chart_DataSeriesValues('String', 'Junta_Anual!$G$' . ($column - 5) . ':$G$' . ($column - 1), NULL, 5));
     //data series values
     $dsv = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Junta_Anual!$I$' . ($column - 5) . ':$I$' . ($column - 1), NULL, 5), new PHPExcel_Chart_DataSeriesValues('Number', 'Junta_Anual!$J$' . ($column - 5) . ':$J$' . ($column - 1), NULL, 5));
     //data series values
     $ds = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_LINECHART, PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv) - 1), $dsl, $xal, $dsv, PHPExcel_Chart_DataSeries::STYLE_SMOOTHMARKER);
     $layout = new PHPExcel_Chart_Layout();
     $ds->setSmoothLine(PHPExcel_Chart_DataSeries::STYLE_SMOOTHMARKER);
     $layout->setShowPercent(TRUE);
     //plot area & legend
     $pa = new PHPExcel_Chart_PlotArea($layout, array($ds));
     $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
     //title of chart
     $title = new PHPExcel_Chart_Title('Curva de Desempeño');
     //chart
     $chart = new PHPExcel_Chart('chart1', $title, $legend, $pa, null, 0, NULL, NULL);
     $chart->setTopLeftPosition('B3');
     $chart->setBottomRightPosition('K25');
     $objSheet->addChart($chart);
     $file_name = "Reporte_Anual.xlsx";
     $objWriter->setPreCalculateFormulas(true);
     $objWriter->setIncludeCharts(true);
     /*//output to browser
     		header('Content-Type: application/vnd.ms-excel');
     		header('Content-Disposition: attachment;filename="'.$file_name.'"');
     		header('Cache-Control: max-age=0');
     		$objWriter->save('php://output');*/
     //output to server
     $objWriter->save(getcwd() . "/assets/docs/{$file_name}");
     $this->load->library("email");
     //configuracion para gmail
     $config = array('protocol' => 'smtp', 'smtp_host' => 'ssl://smtp.gmail.com', 'smtp_port' => 465, 'smtp_user' => '*****@*****.**', 'smtp_pass' => 'CapitalAdv16', 'mailtype' => 'html', 'charset' => 'utf-8', 'newline' => "\r\n");
     $this->email->initialize($config);
     $this->email->clear(TRUE);
     $this->email->from('*****@*****.**', 'Portal de Evaluación Advanzer-Entuizer');
     $this->email->to("*****@*****.**");
     /*$this->email->to("*****@*****.**");
     		$this->email->bcc(array('*****@*****.**', '*****@*****.**'));
     		$this->email->to("*****@*****.**");*/
     $this->email->subject('Reporte de Evaluación para Junta Anual');
     $this->email->message('<h2>Se ha generado el archivo de Reporte de Evaluación para la Junta Anual</h2><hr>');
     $this->email->attach(base_url("assets/docs/{$file_name}"));
     if (!$this->email->send()) {
         var_dump($this->email->print_debugger());
     } else {
         return "Se ha enviado reporte anual";
     }
 }