Error message when opening exported excel file in PHP

Question

Error message when opening exported excel file in PHP

Navigation

#1 by (0 votes)

0

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

Opening the following message is displayed:

Afterclicking"YES", the worksheet in some columns is showing the result as 0.

But after clicking the "Enable Edit" button in excel, the values are shown and another message is displayed.

I have tried several solutions so that the messages are not shown and nothing yet. Anyone have any suggestions whatsoever? Thanks in advance.

php excel phpexcel

asked by anonymous 18.04.2017 / 16:11

1 answer

Stop processing in java How to identify a number on the screen with Bitmap?

score 0 · Answer 1

The problem happens because the way you generate the file leads to an invalid XML (as the error message says).

xlsx files are internally collections of XML files. To solve your problem, you must generate a file with your code and rename it so that the extension is .zip. You can then open this file and navigate to the path indicated in the repair dialog ( xl/worksheets/sheet1.xml ). / p>

You want to open this file ("sheet1.xml") with some program that can validate XML, and verify why XML is invalid. Only then will you know which cell (s) your code fills in wrongly.

The code you posted is very large and complex, and should probably fill most of the cells correctly. So I recommend that after discovering which cells are populated with invalid XML, edit the code in the question to only contain the fill of the cells that are invalid.