Phpexcel generates broken spreadsheet

Question

I’m using the Phpexcel class to export a BD table. But I’m having trouble downloading the spreadsheet. To test, enter values manually without making any select in the BD. However when it generates the spreadsheet and makes the download, the broken spreadsheet opens.

CONTROLLER:

    <?php
require "models/class.exportacaomodel.php";

  class ExportacaoController extends Controller{

function __construct(){
    parent::__construct();
}


 public function listarCampos(){
    $ExportacaoModel = new ExportacaoModel();
    $retorno    = $ExportacaoModel -> listarCamposPersonalizadosContato();        
    $this -> view -> retorno = $retorno;
    $this -> view -> setView("viewsTest/exportacao/exportacao");
}

public function exportar(){

  $nome = $_POST['nome'];
  $sobrenome = $_POST['sobrenome'];
  $email = $_POST['email'];
  $dataNascimento = $_POST['dataNascimento'];
  $camposPersonalizados = $_POST['campos_personalizados'];     


  $ExportacaoModel = new ExportacaoModel();
  $retorno    = $ExportacaoModel -> exportar();        
  $this -> view -> retorno = $retorno;
  $this -> view -> setView("viewsTest/exportacao/passo_2");
}


 }

    ?>

MODEL:

<?php  

  require_once "/util/exportacao/PHPExcel_1.7.9/Classes/PHPExcel.php";

  class ExportacaoModel extends Model{

    function __construct(){
      parent::__construct();
    }


public function listarCamposPersonalizadosContato(){

    $stmt = $this -> db -> prepare("SELECT  id, nome, tipo_personalizado  FROM tblrlzcampospersonalizadoscontato WHERE tipo_formulario = 'contato' ");
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $rows;

}

public function exportar(){

    $query_str  = "SELECT DISTINCT ass.IdAssinante, ass.Nome, ass.Email FROM tblrlzassinante AS ass";
    $query_str .= " INNER JOIN tblrlzassinanteareadeinteresse AS aai ON aai.IdAssinante = ass.IdAssinante";   
    $query_str .= " INNER JOIN tblrlzvalorescampospersonalizadoscontato AS cpc ON cpc.id_contato = ass.IdAssinante";

    $stmt = $this -> db -> prepare($query_str);
    $stmt -> execute();
    $rows = $stmt -> fetchAll(PDO::FETCH_ASSOC);

    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();

    // Set document properties
    $objPHPExcel->getProperties()->setCreator("Teste")
                                 ->setLastModifiedBy("Teste")
                                 ->setTitle("Relatorio")
                                 ->setSubject("Relatorio")
                                 ->setDescription("Relatorio.")
                                 ->setKeywords("relatorio ")
                                 ->setCategory("arquivo relatorio");


    // Add some data
    $objPHPExcel->setActiveSheetIndex(0);

    $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Id');
    $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Nome');
    $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Email');  

    $linha = 2;
    foreach ( $rows as $item => $contato) { 
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$linha, $contato["IdAssinante"] ); //var_dump( $contato["IdAssinante"] );
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$linha, $contato["Nome"] ); //var_dump( $contato["Nome"] );
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$linha, $contato["Email"] );   var_dump( $contato["Email"] );           
    }

    // Set column widths
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);   
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(100);          

    //Set bold
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);  
    $objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true); 

    // Rename worksheet
    $objPHPExcel->getActiveSheet()->setTitle('Simple');

    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);

    $arquivo = date("Y-m-d-H-i-s")."-".rand(1, 9999).".xls";
    // Redirect output to a client’s web browser (Excel5)
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$arquivo.'"');
    header('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');

    // If you're serving to IE over SSL, then the following may be needed
    header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
    header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header ('Pragma: public'); // HTTP/1.0

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;

}

  }

 ?>

How the spreadsheet looks after the download:

Answer 1

After testing the updated code you posted, I noticed that the problem occurred on Office2007 also, so I noticed this excerpt:

$linha = 2;
foreach ( $rows as $item => $contato) { 
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$linha, $contato["IdAssinante"] ); //var_dump( $contato["IdAssinante"] );
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$linha, $contato["Nome"] ); //var_dump( $contato["Nome"] );
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$linha, $contato["Email"] );   var_dump( $contato["Email"] );           
}

Note that you have a var_dump( $contato["Email"] ); not commented, it was a typo your, if you do this it will work:

$linha = 2;
foreach ( $rows as $item => $contato) { 
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$linha, $contato["IdAssinante"] );
    //var_dump( $contato["IdAssinante"] );
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$linha, $contato["Nome"] );
    //var_dump( $contato["Nome"] );
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$linha, $contato["Email"] );
    //var_dump( $contato["Email"] );           
}

The problem may also be utf8, so you can use the utf8_decode:

$linha = 2;
foreach ( $rows as $item => $contato) { 
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$linha, utf8_decode($contato["IdAssinante"]) );
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$linha, utf8_decode($contato["Nome"]) );
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$linha, utf8_decode($contato["Email"]) );        
}

I tested your code and it worked perfectly using Microsoft Office 2007. By the images that posted you are used the Broffice was extended and its last released version was 3.3.2 (22 March 2011; 4 years ago)

How was the process

_{Source: https://en.wikipedia.org/wiki/LibreOffice}

On 17 March 2011, a decision was taken at a meeting in Rio de Janeiro to abolish the Broffice.org Association by unanimous vote of the members present, ending social activities on April 30, 2011 and complying with all legal obligations until May 16, 2011. All the assets of the Broffice.org Association, being only movable assets and financial resources in accounts linked to the Association’s CNPJ, was reverted to entities that promote free software, with the authorization of a donation to the Debian Brazil community and the payment of the Postgresql 8.4 documentation translation service. It was suggested by Cláudio Ferreira Filho, in a note published on Broffice.org’s website on the extinction, that the moment of the extinction of the Association was also the moment for an alignment of the efforts of the Brazilian community with the international project Libreoffice, including the replacement of the name in Brazil for Libreoffice. Since then, the Brazilian community has worked on various documentation activities for the effective change of the Broffice name to Libreoffice and on the needs of transferring the content present on the official website of the Brazilian project Broffice to a Portuguese version from the Libreoffice website. Version 3.4, after version 3.3.2, already had the name Libreoffice for the Brazilian Portuguese version.

Download the Libreoffice

Follow the download link https://pt-br.libreoffice.org

Completion

The problem is not with the Phpexcel and yes that you are using a very outdated software that no longer has support, being now your success the Libreoffice.

Answer 2

I solved the problem as follows:

Instead of downloading the spreadsheet directly. I save it on the server, and then download.

I commented on the header, and made the following changes:

 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    //$objWriter->save('php://output');
    //exit;

    $objWriter->save(UPLOAD_ABSPATH.$arquivo);

    header("Location:".HOME_URI."/viewsTest/_uploads/".$arquivo);

Thus, the spreadsheet is downloaded normal, without breaks.