0
use the following code:
$this->excel->getActiveSheet()->setTitle('Matemática');
//set cell A1 content with some text
$this->excel->getActiveSheet()->setCellValue('A1', $opcao);
$this->excel->getActiveSheet()->setCellValue('A2', '#');
$this->excel->getActiveSheet()->setCellValue('B2', 'REGIONAL');
$this->excel->getActiveSheet()->setCellValue('C2', 'TURMAS');
$this->excel->getActiveSheet()->setCellValue('F2', 'ESTUDANTES');
$this->excel->getActiveSheet()->setCellValue('I2', 'MÉDIA DE ESTUDANTES AVALIADOS POR DISCIPLINA');
//$this->excel->getActiveSheet()->setCellValue('J2', 'PERCENTUAL DE ACERTOS POR DISCIPLINAS(%)');
$this->excel->getActiveSheet()->setCellValue('C3', 'TOTAL DE TURMAS');
$this->excel->getActiveSheet()->setCellValue('D3', 'TURMAS COM RESULTADOS PROCESSADOS');
$this->excel->getActiveSheet()->setCellValue('E3', '%');
$this->excel->getActiveSheet()->setCellValue('F3', 'TOTAL DE ESTUDANTES');
$this->excel->getActiveSheet()->setCellValue('G3', 'ESTUDANTES COM RESULTADOS PROCESSADOS');
$this->excel->getActiveSheet()->setCellValue('H3', '%');
$this->excel->getActiveSheet()->setCellValue('B' . $linha, 'TOTAL GERAL');
$this->excel->getActiveSheet()->mergeCells('A2:A3');
$this->excel->getActiveSheet()->mergeCells('B2:B3');
$this->excel->getActiveSheet()->mergeCells('C2:E2');
$this->excel->getActiveSheet()->mergeCells('F2:H2');
$this->excel->getActiveSheet()->mergeCells('I2:I3');
//$this->excel->getActiveSheet()->mergeCells('J2:J3');
$this->excel->getActiveSheet()->getColumnDimension('A')->setWidth(4);
$this->excel->getActiveSheet()->getColumnDimension('B')->setWidth(45);
$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(16);
$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(8);
$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(16);
$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(8);
$this->excel->getActiveSheet()->getColumnDimension('I')->setWidth(22);
$this->excel->getActiveSheet()->getColumnDimension('J')->setWidth(18);
$this->excel->getActiveSheet()->getStyle('C3')->getAlignment()->setWrapText(true);
$this->excel->getActiveSheet()->getStyle('D3')->getAlignment()->setWrapText(true);
$this->excel->getActiveSheet()->getStyle('F3')->getAlignment()->setWrapText(true);
$this->excel->getActiveSheet()->getStyle('G3')->getAlignment()->setWrapText(true);
$this->excel->getActiveSheet()->getStyle('I2')->getAlignment()->setWrapText(true);
//$this->excel->getActiveSheet()->getStyle('J2')->getAlignment()->setWrapText(true);
$this->excel->getActiveSheet()->getStyle('A2:I2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('A2:I2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$this->excel->getActiveSheet()->getStyle('C3:H3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('C3:H3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//$this->excel->getActiveSheet()->getRowDimension('2')->setRowHeight(45);
$this->excel->getActiveSheet()->getStyle('A1:I1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFC0C0C0');
$this->excel->getActiveSheet()->getStyle('A2:I2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFD3D3D3');
$this->excel->getActiveSheet()->getStyle('C3:H3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFD3D3D3');
$styleArray = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array('argb' => '00000000'),
)
)
);
$this->excel->getActiveSheet()->getStyle('A1:I1')->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('A2:I2')->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('A3:I3')->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('Y3:I3')->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('A'.$linha.':I' .$linha)->applyFromArray($styleArray);
$total_acertos = 0;
$pontuacao_maxima = 0;
$porcentagem_acerto = 0;
$porcentagem_acerto_total = 0;
$pontuacao_maxima_total = 0;
$soma_total_acertos = 0;
$pontuacao_maxima_total = 0;
$quantidade_de_alunos = 0;
$rowNumber = 4;
$contador = 1;
foreach ($totalizacao as $row) {
$this->excel->getActiveSheet()->setCellValue('A'.$rowNumber, $contador);
$this->excel->getActiveSheet()->getStyle('A'.$rowNumber)->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('A'.$rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->setCellValue('B'.$rowNumber, $row->nome);
$this->excel->getActiveSheet()->getStyle('B'.$rowNumber)->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('B'.$rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$this->excel->getActiveSheet()->setCellValue('C'.$rowNumber, $row->quantidade_de_turmas);
$this->excel->getActiveSheet()->getStyle('C'.$rowNumber)->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('C'.$rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->setCellValue('D'.$rowNumber, $row->quantidade_de_turmas_com_avaliacao);
$this->excel->getActiveSheet()->getStyle('D'.$rowNumber)->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('D'.$rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$turma_porcent = 0;
if ((int) $row->quantidade_de_turmas > 0) $turma_porcent = ((int) $row->quantidade_de_turmas_com_avaliacao / (int) $row->quantidade_de_turmas) * 100; else $turma_porcent = 0;
$this->excel->getActiveSheet()->setCellValue('E'.$rowNumber, number_format($turma_porcent, 2, '.', '') .'%');
$this->excel->getActiveSheet()->getStyle('E'.$rowNumber)->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('E'.$rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->setCellValue('F'.$rowNumber, $row->quantidade_de_alunos);
$this->excel->getActiveSheet()->getStyle('F'.$rowNumber)->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('F'.$rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->setCellValue('G'.$rowNumber, $row->quantidade_de_alunos_processados);
$this->excel->getActiveSheet()->getStyle('G'.$rowNumber)->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('G'.$rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$alunos_porcent = 0;
if ($row->quantidade_de_alunos > 0) $alunos_porcent = ((int) $row->quantidade_de_alunos_processados / (int) $row->quantidade_de_alunos) * 100; else $alunos_porcent = 0;
$this->excel->getActiveSheet()->setCellValue('H'.$rowNumber, number_format($alunos_porcent, 2, '.', '') . "%");
$this->excel->getActiveSheet()->getStyle('H'.$rowNumber)->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('H'.$rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$media_estudantes = 0;
if ($row->quantidade_de_turmas_com_avaliacao > 0) $media_estudantes = number_format(((int) $row->quantidade_de_alunos_processados / $row->quantidade_de_turmas_com_avaliacao), 2, '.', ''); else $media_estudantes = 0;
$this->excel->getActiveSheet()->setCellValue('I'.$rowNumber, $media_estudantes);
$this->excel->getActiveSheet()->getStyle('I'.$rowNumber)->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('I'.$rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
/* $pontuacao_maxima = $row->quantidade_de_alunos_processados * 20;
$total_acertos = (int) $row->quantidade_de_acertos_por_cre;
$porcentagem_acerto = ($pontuacao_maxima > 0 ? round(($total_acertos / $pontuacao_maxima) * 100, 2) : '0');
$porcentagem_acerto = $porcentagem_acerto.'%';
$this->excel->getActiveSheet()->setCellValue('J'.$rowNumber, $porcentagem_acerto);
$this->excel->getActiveSheet()->getStyle('J'.$rowNumber)->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('J'.$rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);*/
$soma_total_acertos += $total_acertos;
$quantidade_de_turma_cons = $quantidade_de_turma_cons + $row->quantidade_de_turmas_com_avaliacao;
$quantidade_de_turma = $quantidade_de_turma + $row->quantidade_de_turmas;
$quantidade_de_alunos = $quantidade_de_alunos + $row->quantidade_de_alunos;
$quantidde_de_alunos_proc = $quantidde_de_alunos_proc + $row->quantidade_de_alunos_processados;
$quantidade_acertos = $quantidade_acertos + $total_acertos;
$rowNumber++;
$contador++;
}
unset($styleArray);
# % Turmas consolidadas
$perc_total_turma = "0%";
$perc_total_alunos = "0%";
$perc_quantidade_acertos = "0%";
if($quantidade_de_turma > 0)
{
$perc_total_turma = ($quantidade_de_turma_cons * 100) / $quantidade_de_turma;
$perc_total_turma = number_format($perc_total_turma, 2, '.', '') . "%"; //round($perc_total_turma, 2) . "%";
}
if($quantidade_de_alunos > 0)
{
$perc_total_alunos = ($quantidde_de_alunos_proc * 100) / $quantidade_de_alunos;
$perc_total_alunos = number_format($perc_total_alunos, 2, '.', '') . "%";
}
if($quantidade_de_turma_cons)
{
$quantidade_media_estudantes = $quantidde_de_alunos_proc / $quantidade_de_turma_cons;
$quantidade_media_estudantes = number_format($quantidade_media_estudantes, 2, '.', '');
}
if($quantidde_de_alunos_proc > 0)
{
$perc_quantidade_acertos = ($quantidade_acertos * 100) / ($quantidde_de_alunos_proc * 20);
$perc_quantidade_acertos = number_format($perc_quantidade_acertos, 2, '.', '') . "%";
}
$linhaAnt = $linha - 1;
$this->excel->getActiveSheet()->setCellValue("C".$linha, "=SUM(C3:C".$linhaAnt.")");
$this->excel->getActiveSheet()->setCellValue("D".$linha, "=SUM(D3:D".$linhaAnt.")");
$this->excel->getActiveSheet()->setCellValue("E".$linha, $perc_total_turma);
$this->excel->getActiveSheet()->setCellValue("F".$linha, "=SUM(F3:F".$linhaAnt.")");
$this->excel->getActiveSheet()->setCellValue("G".$linha, "=SUM(G3:G".$linhaAnt.")");
$this->excel->getActiveSheet()->setCellValue("H".$linha, $perc_total_alunos);
$this->excel->getActiveSheet()->setCellValue("I".$linha, $quantidade_media_estudantes);
//$this->excel->getActiveSheet()->setCellValue("J19", $perc_quantidade_acertos);
$this->excel->getActiveSheet()->getStyle('A'.$linha.':I'.$linha)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFD3D3D3');
$this->excel->getActiveSheet()->getStyle('A'.$linha.':I'.$linha)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//change the font size
$this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
//make the font become bold
$this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
//merge cell A1 until D1
$this->excel->getActiveSheet()->mergeCells('A1:I1');
//set aligment to center for that merged cell (A1 to D1)
$this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$modalidade_arq = $_SESSION['modalidade'];
$modalidade_arq = ($modalidade_arq == 'medio') ? 'EM' : 'EF';
$filename= 'CONSOLIDAÇÃO DAS TURMAS - MATEMÁTICA (' . $modalidade_arq . ' ' . $this->session->userdata('ano') . ') - '.date("d.m.Y").'.xlsx'; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');
$this->excel->getActiveSheet()->insertNewRowBefore(1, 5);
$this->excel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('G2')->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('G3')->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('G4')->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('G2:G4')->getAlignment()->setIndent(1);
$this->excel->getActiveSheet()->getStyle('I2:I4')->getAlignment()->setIndent(1);
$this->excel->getActiveSheet()->setCellValue('G1', 'PROJETO TELECURSO MG');
$this->excel->getActiveSheet()->setCellValue('G2', 'TURMAS');
$this->excel->getActiveSheet()->setCellValue('G3', 'MODALIDADE');
$this->excel->getActiveSheet()->setCellValue('G4', 'DATA DE REFERÊNCIA');
$this->excel->getActiveSheet()->setCellValue('I2', $this->session->userdata('ano'));
$this->excel->getActiveSheet()->setCellValue('I3', $modalidade);
$this->excel->getActiveSheet()->setCellValue('I4', date("d/m/Y"));
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');
$this->excel->getActiveSheet()->getStyle('G1:J1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFC0C0C0');
$this->excel->getActiveSheet()->getStyle('G1:J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->mergeCells('G1:J1');
$this->excel->getActiveSheet()->mergeCells('G2:H2');
$this->excel->getActiveSheet()->mergeCells('G3:H3');
$this->excel->getActiveSheet()->mergeCells('G4:H4');
$this->excel->getActiveSheet()->mergeCells('I1:J1');
$this->excel->getActiveSheet()->mergeCells('I2:J2');
$this->excel->getActiveSheet()->mergeCells('I3:J3');
$this->excel->getActiveSheet()->mergeCells('I4:J4');
$this->excel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
$this->excel->getActiveSheet()->getStyle('G2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$this->excel->getActiveSheet()->getStyle('I2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$this->excel->getActiveSheet()->getStyle('G3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$this->excel->getActiveSheet()->getStyle('I3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$this->excel->getActiveSheet()->getStyle('G4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$this->excel->getActiveSheet()->getStyle('I4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$this->excel->getActiveSheet()->getStyle('G1')->getFont()->setSize(20);
$styleArray = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array('argb' => '00000000'),
)
)
);
$this->excel->getActiveSheet()->getStyle('G1:J1')->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('G2:J2')->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('G3:J3')->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('G4:J4')->applyFromArray($styleArray);
$this->excel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);
When opening the following message is shown:
After clicking on "YES", in the spreadsheet in some columns this showing the result as 0.
But after clicking on the "Enable Editing" button of excel, the values are shown and another message is shown.
I have tried several solutions for the messages not to be shown and nothing yet. Does anyone have any suggestions of what might be? Thanks in advance.
Thanks Renan! I managed to solve.
– aes