PHPExcel generates broken worksheet [closed]

2

I'm using the PHPExcel class to export a DB table. But I'm having trouble downloading the spreadsheet. To test, insert values manually without doing any select in the DB. But when it generates the spreadsheet and downloads it, the spreadsheet opens broken.

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 worksheet is after download:

    
asked by anonymous 03.12.2015 / 19:46

2 answers

1

I solved the problem as follows:

Instead of downloading the worksheet directly. I saved it on the server, and then down.

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

So, the worksheet is downloaded normal, with no breaks.

    
14.12.2015 / 14:51
4

After testing the code that posted updated, I noticed that the problem occurred in 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 it has a var_dump( $contato["Email"] ); not commented out, it was a typo of yours, 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 can also be utf8, so you can use 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 . For the images you posted you used BrOffice was estinto and its last release was 3.3.2 (March 22, 2011; 4 years ago)

How was the process

Source: link

On March 17, 2011, a meeting in Rio de Janeiro decided to abolish the BrOffice.org Association by unanimous vote of the members present, ending the social activities on April 30, 2011 and complying with all the legal obligations up to May 16, 2011. All assets of the BrOffice.org Association, being only movable assets and financial resources in accounts linked to the CNPJ of the Association, were reverted to entities that promote free software, with the authorization of a donation to the Debian Brasil community and payment for the PostgreSQL 8.4 documentation translation service. It was suggested by Cláudio Ferreira Filho, in a note published on the website of BrOffice.org 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 substitution of the name in Brazil for LibreOffice. Since then, the Brazilian community has worked on various documentation activities for the effective change of the name BrOffice to LibreOffice and in the needs of the transfer of the content present in the official site of the Brazilian project BrOffice to a Brazilian Portuguese version of the LibreOffice website. Version 3.4, later than version 3.3.2, already presented the name LibreOffice for the version in Brazilian Portuguese.

Download LibreOffice

Follow the link link

Conclusion

The problem is not with PHPExcel but rather that you are using a very outdated software that is no longer supported, and your success is now LibreOffice .

    
03.12.2015 / 20:23