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'); }
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; }
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; }
// 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!$C$2:$C$5', NULL, 4)); // Build the dataseries $series2 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_DONUTCHART, NULL, range(0, count($dataSeriesValues2) - 1), $dataSeriesLabels2, $xAxisTickValues2, $dataSeriesValues2); // Set up a layout object for the Pie chart $layout2 = new PHPExcel_Chart_Layout(); $layout2->setShowVal(TRUE); $layout2->setShowCatName(TRUE); // Set the series in the plot area $plotArea2 = new PHPExcel_Chart_PlotArea($layout2, array($series2)); $title2 = new PHPExcel_Chart_Title('Test Donut Chart'); // Create the chart $chart2 = new PHPExcel_Chart('chart2', $title2, NULL, $plotArea2, true, 0, NULL, NULL); // Set the position where the chart should appear in the worksheet $chart2->setTopLeftPosition('I7'); $chart2->setBottomRightPosition('P20'); // Add the chart to the worksheet $objWorksheet->addChart($chart2); // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->setIncludeCharts(TRUE);
// 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!$C$2:$C$5', null, 4)); // Build the dataseries $series2 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_DONUTCHART, PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dataSeriesValues2) - 1), $dataseriesLabels2, $xAxisTickValues2, $dataSeriesValues2); // Set up a layout object for the Pie chart $layout2 = new PHPExcel_Chart_Layout(); $layout2->setShowVal(true); $layout2->setShowCatName(true); // Set the series in the plot area $plotarea2 = new PHPExcel_Chart_PlotArea($layout2, array($series2)); $title2 = new PHPExcel_Chart_Title('Test Donut Chart'); // Create the chart $chart2 = new PHPExcel_Chart('chart2', $title2, null, $plotarea2, true, 0, null, null); // Set the position where the chart should appear in the worksheet $chart2->setTopLeftPosition('I7'); $chart2->setBottomRightPosition('P20'); // Add the chart to the worksheet $objWorksheet->addChart($chart2); // Save Excel 2007 file echo date('H:i:s'), " Write to Excel2007 format", EOL; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->setIncludeCharts(true);
$line = 2; foreach ($rows as $row) { $sheet->setCellValue('A' . $line, $row['type_name']); $sheet->setCellValue('B' . $line, $row['number']); $line++; } //Autofit foreach (range('A', 'B') as $colD) { $sheet->getColumnDimension($colD)->setAutoSize(TRUE); } $dataseriesLabels1 = array(new PHPExcel_Chart_DataSeriesValues('String', $title . '!$A$1', null, 1)); $xAxisTickValues1 = array(new PHPExcel_Chart_DataSeriesValues('String', $title . '!$A$2:$A$' . $line, null, 4)); $dataSeriesValues1 = array(new PHPExcel_Chart_DataSeriesValues('Number', $title . '!$B$2:$B$' . $line, null, 4)); $series1 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_PIECHART, PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dataSeriesValues1) - 1), $dataseriesLabels1, $xAxisTickValues1, $dataSeriesValues1); $layout1 = new PHPExcel_Chart_Layout(); $layout1->setShowVal(TRUE); $layout1->setShowPercent(TRUE); $plotarea1 = new PHPExcel_Chart_PlotArea($layout1, array($series1)); $legend1 = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false); $title1 = new PHPExcel_Chart_Title($chartTitle); $chart1 = new PHPExcel_Chart('chart1', $title1, $legend1, $plotarea1, true, 0, null, null); $chart1->setTopLeftPosition('E3'); $chart1->setBottomRightPosition('K20'); $sheet->addChart($chart1); $filename = 'excel-export.xlsx'; header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $filename . '"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($ci->excel, 'Excel2007'); $objWriter->setIncludeCharts(TRUE); $objWriter->save('php://output');
private function addChart1(\PHPExcel $ea, \PHPExcel_Worksheet $ews) { //The below line should be moved into addAnalysis but we move this here to show that $ews is actually referring to the sheet $ews->getStyle('b4')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE); $title = new \PHPExcel_Chart_Title($ews->getTitle()); // Set the data serie labels $dsl = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Summary!A1', NULL, 1)); // Set X-Axis Labels $xal = array(new \PHPExcel_Chart_DataSeriesValues('String', 'Summary!A2:A3', NULL, 2)); // Set data serie values $dsv = array(new \PHPExcel_Chart_DataSeriesValues('Number', 'Summary!B2:B3', NULL, 2)); // Build a dataserie $ds = new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_PIECHART, null, range(0, count($dsv) - 1), $dsl, $xal, $dsv); // A layout for the Pie Chart $layout = new \PHPExcel_Chart_Layout(); $layout->setShowVal(true); $layout->setShowPercent(true); // Set series in the plot area $pa = new \PHPExcel_Chart_PlotArea($layout, array($ds)); // Set legend $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); //Create Chart $chart = new \PHPExcel_Chart('chart1', $title, $legend, $pa, true, 0, NULL, NULL); $chart->setTopLeftPosition('K1'); $chart->setBottomRightPosition('U30'); $ews->addChart($chart); }