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: