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;
}
public function testGetLayoutTarget()
{
$LayoutTargetValue = 'String';
$testInstance = new PHPExcel_Chart_Layout();
$setValue = $testInstance->setLayoutTarget($LayoutTargetValue);
$result = $testInstance->getLayoutTarget();
$this->assertEquals($LayoutTargetValue, $result);
}
/**
* Write Data Labels
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Chart_Layout $chartLayout Chart layout
* @throws PHPExcel_Writer_Exception
*/
private function _writeDataLbls($objWriter, $chartLayout)
{
$objWriter->startElement('c:dLbls');
$objWriter->startElement('c:showLegendKey');
$showLegendKey = empty($chartLayout) ? 0 : $chartLayout->getShowLegendKey();
$objWriter->writeAttribute('val', empty($showLegendKey) ? 0 : 1);
$objWriter->endElement();
$objWriter->startElement('c:showVal');
$showVal = empty($chartLayout) ? 0 : $chartLayout->getShowVal();
$objWriter->writeAttribute('val', empty($showVal) ? 0 : 1);
$objWriter->endElement();
$objWriter->startElement('c:showCatName');
$showCatName = empty($chartLayout) ? 0 : $chartLayout->getShowCatName();
$objWriter->writeAttribute('val', empty($showCatName) ? 0 : 1);
$objWriter->endElement();
$objWriter->startElement('c:showSerName');
$showSerName = empty($chartLayout) ? 0 : $chartLayout->getShowSerName();
$objWriter->writeAttribute('val', empty($showSerName) ? 0 : 1);
$objWriter->endElement();
$objWriter->startElement('c:showPercent');
$showPercent = empty($chartLayout) ? 0 : $chartLayout->getShowPercent();
$objWriter->writeAttribute('val', empty($showPercent) ? 0 : 1);
$objWriter->endElement();
$objWriter->startElement('c:showBubbleSize');
$showBubbleSize = empty($chartLayout) ? 0 : $chartLayout->getShowBubbleSize();
$objWriter->writeAttribute('val', empty($showBubbleSize) ? 0 : 1);
$objWriter->endElement();
$objWriter->startElement('c:showLeaderLines');
$showLeaderLines = empty($chartLayout) ? 1 : $chartLayout->getShowLeaderLines();
$objWriter->writeAttribute('val', empty($showLeaderLines) ? 0 : 1);
$objWriter->endElement();
$objWriter->endElement();
}
// 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!$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');
// 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!$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');
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";
}
}
new PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$B$2:$B$4',null,3),//取一班的数据
new PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$C$2:$C$4',null,3),//取二班的数据
new PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$D$2:$D$4',null,3)//取三班的数据
);//取得绘图所需的数据
$series=array(
new PHPExcel_Chart_DataSeries(
PHPExcel_Chart_DataSeries::TYPE_LINECHART,
PHPExcel_Chart_DataSeries::GROUPING_STANDARD,
range(0,count($labels)-1),
$labels,
$xLabels,
$datas
)
);//根据取得的东西做出一个图表的框架
$layout=new PHPExcel_Chart_Layout();
$layout->setShowVal(true);
$areas=new PHPExcel_Chart_PlotArea($layout,$series);
$legend=new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT,$layout,false);
$title=new PHPExcel_Chart_Title("高一学生成绩分布");
$ytitle=new PHPExcel_Chart_Title("value(人数)");
$chart=new PHPExcel_Chart(
'line_chart',
$title,
$legend,
$areas,
true,
false,
null,
$ytitle
);//生成一个图标
$rows = $this->db->get()->result_array();
$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);
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);
}
