Error generating phpexcel spreadsheet

Question

Error generating phpexcel spreadsheet

Asked 8 years, 5 months ago

Viewed 445 times

1

Friends, I am using the Phpexcel class and every time I will generate a csv this

come with the characters not figured as shown below, someone already

went through this ?

    <? session_start();

mb_internal_encoding('UTF-8');
mb_http_output('UTF-8');
error_reporting(E_ALL & ~ E_NOTICE);

extract($_POST);
extract($_GET);
//Alteração 
if (!$_SESSION[user])
{
echo "<font face='verdana,helvetica,arial' size=2 color='#BB0000'>";
echo "<p>&nbsp;</p>";
echo "<p align='center'><b>Sessão expirada no Sistema<br><br></p>";
echo "<p align='center'>Clique aqui para efetuar seu <a href='$URL/manutencao_sistema.php' class='vermelho' target='_top'><font color='navy'><b><i>Login</i></b></font></a></p>";
echo "</font>";
exit();
}

$curso = $_REQUEST['curso'];
$ev_cod = $_REQUEST['ev_cod'];
$Curso = $_REQUEST['Curso'];

include ('../header_report.php');
require_once('../verifica_evento_ativo.php');
include('../funcao/dia_semana.php');

$variavelColuna = 'A';
$variavelLinha = '1';

// --- Cria o arquivo ----------------------------------------------------------
/** Error reporting */
error_reporting(E_ALL);

/** PHPExcel */
require_once '../biblioteca/phpexcel/classes/PHPExcel.php';

/** PHPExcel_IOFactory */
require_once '../biblioteca/phpexcel/classes/PHPExcel/IOFactory.php';

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

// Set properties
$objPHPExcel->getProperties()->setCreator("Relatório")
                     ->setLastModifiedBy("Relatório")
                     ->setTitle("Relatório")
                     ->setSubject("Lista de Presença")
                     ->setDescription("Relatório")
                     ->setKeywords("")                       
                     ->setCategory("Exportação de Presença");

$gdImage = imagecreatefromjpeg('../imagem/brasao_bw.jpg');
$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setName('Sample image');
$objDrawing->setDescription('Sample image');
$objDrawing->setImageResource($gdImage);
$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
$objDrawing->setHeight(100);

//$objDrawing->setPath('../imagem/brasao_bw.jpg');
$objDrawing->setCoordinates('B1');           
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);                    

$listaPresenca = array();
$listaMerge = array();

$sql_curso = '';


if ($curso != 'all') {
$Curso = explode('|',$curso);
$sql_curso = "AND C.eixo_cod_fk = '$Curso[0]'
          AND T.tur_cod = '$Curso[2]'
          AND T.eixo_cod_fk = C.eixo_cod_fk
          AND T.cur_cod_fk = '$Curso[1]'";
}

$styleArray = array(
'borders' => array(
 'outline' => array(
        'style' => PHPExcel_Style_Border::BORDER_THIN,
        'color' => array('argb' => '#000000'),
 ),
),
);

// Cabeçalho
function escreverCabecalho($imprimeTabela, $primeiraPagina, $sql ,$tcol_nro, $tcol_nome,  $tcol_data, $nro_colunas, $tcol_datas , $objPHPExcel , &$indColuna , &$indLinha) {

$indColuna = 'A';

$listaPresenca = array();
$listaMerge = array();

$tcol_nro = $tcol_nro;
$tcol_nome = $tcol_nome;
$tcol_data = $tcol_data;
$nro_colunas = $nro_colunas;
$tcol_datas = $tcol_datas;

$styleArray = array(
'borders' => array(
     'outline' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('argb' => '#000000'),
     ),
),
);


if ($primeiraPagina) {

$cons = mysql_query($sql)
    OR DIE("<h5 class='erro'>ERRO NO BANCO DE DADOS - CURSOS</h5>");

if ( $ln = mysql_fetch_array($cons) ) {
    $loc_descr = $ln['loc_descr'];
    $loc_endereco = $ln['loc_endereco'];
    $loc_nro = $ln['loc_nro'];
    $loc_bairro = $ln['loc_bairro'];
    $cur_cod = $ln['cur_cod'];
    $cur_descr = $ln['cur_descr'];
    $cur_professor = $ln['cur_professor'];
    $cur_carga_listapresenca = $ln['cur_carga_listapresenca'];
    $tur_cod = $ln['tur_cod'];
    $tur_vaga = $ln['tur_vaga'];
    $tur_dia = $ln['tur_dia'];
    $tur_horaini = $ln['tur_horaini'];
    $tur_horafin = $ln['tur_horafin'];
    $eixo_cod = $ln['eixo_cod'];
    $eixo_descr = $ln['eixo_descr'];
}

$exibe_curso = $eixo_cod.'.'.$cur_cod.' '.$cur_descr;
$exibe_turma = '';
if ($tur_cod != 'TU') {
        $exibe_turma = 'Turma: '.$tur_cod;
}

$dia = mostrar_dia_semana($tur_dia);
//$sem_descrabrev = $sem_descrabrev;

//$this->SetFillColor(255,255,255); Setar cor de fundo
//$this->SetFont('Arial','',8); Setar fonte
//$this->Image('../imagem/brasao_bw.jpg',112,10,12.75,12.5); // Setar brasão

$listaMerge[] = "A$indLinha:L$indLinha";
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


$listaMerge[] = "A$indLinha:L$indLinha";

$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


$listaMerge[] = "A$indLinha:L$indLinha";
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


$listaMerge[] = "A$indLinha:L$indLinha";
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


$listaMerge[] = "A$indLinha:L$indLinha";
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$indLinha++;

$turma = '';
if (strlen($exibe_turma)>1) {
        $turma = 'Turma: '. $tur_cod;
}

$listaMerge[] = "A$indLinha:L$indLinha";
$listaPresenca[$indColuna][$indLinha++] = $exibe_curso;
$listaMerge[] = "A$indLinha:L$indLinha";
$listaPresenca[$indColuna][$indLinha++] = 'Formador(es): '. $cur_professor;
$listaMerge[] = "A$indLinha:L$indLinha";
$listaPresenca[$indColuna][$indLinha]= 'N° Vagas: '. $tur_vaga;
$listaMerge[] = "A$indLinha:L$indLinha";
$listaPresenca[$indColuna][$indLinha++] = 'Início: ____/____/________  e  Final: ____/____/________';
$listaMerge[] = "A$indLinha:L$indLinha";
$listaPresenca[$indColuna][$indLinha] = 'Carga Horária: '. $cur_carga_listapresenca;
$listaMerge[] = "A$indLinha:L$indLinha";
$listaPresenca[$indColuna][$indLinha++] = 'Local: '. $loc_descr;
$listaMerge[] = "A$indLinha:L$indLinha";
$listaPresenca[$indColuna][$indLinha] = 'Dia e Horário: '. $dia.' - '.$tur_horaini.' - '.$tur_horafin.' horas';
$listaMerge[] = "A$indLinha:L$indLinha";
$listaPresenca[$indColuna][$indLinha++] = 'Endereço: '. $loc_endereco.', '.$loc_nro.' - '.$loc_bairro;
$listaMerge[] = "A$indLinha:L$indLinha";
$listaPresenca[$indColuna][$indLinha++] = 'LISTA DE PRESENÇA';
$listaMerge[] = "A$indLinha:L$indLinha";
$listaPresenca[$indColuna][$indLinha++] = $turma;
$indLinha++;

}

if ($imprimeTabela == 1) {

$proxLinha = $indLinha + 1;
$listaMerge[] = "$indColuna$indLinha:$indColuna$proxLinha";
$objPHPExcel->getActiveSheet()->getStyle("$indColuna$indLinha")->applyFromArray($styleArray);
$listaPresenca[$indColuna++][$indLinha] = 'N°';

$proxLinha = $indLinha + 1;
$listaMerge[] = "$indColuna$indLinha:$indColuna$proxLinha";
$objPHPExcel->getActiveSheet()->getStyle("$indColuna$indLinha")->applyFromArray($styleArray);
$listaPresenca[$indColuna++][$indLinha] = 'Candidatos Selecionado';

$proxColuna = $indColuna;
for( $i = 1 ; $i < $nro_colunas; $i++) {
    $proxColuna++;
}

$listaMerge[] = "$indColuna$indLinha:$proxColuna$indLinha";

$objPHPExcel->getActiveSheet()->getStyle("$indColuna$indLinha")->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("L$indLinha")->applyFromArray($styleArray);


$indColuna++;   
$indLinha++;

$indColuna = 'C';
For ($cont_datas = 1; $cont_datas <= $nro_colunas; $cont_datas++) {

    $objPHPExcel->getActiveSheet()->getStyle("$indColuna$indLinha")->applyFromArray($styleArray);
    $listaPresenca[$indColuna][$indLinha] = '___/___/_____';
    $indColuna++;
}   
}

foreach ( $listaPresenca as $coluna => $valor ) {

foreach( $valor as $celula  => $conteudo ) {

    $posicao = $coluna . $celula;
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($posicao , $conteudo );
    $objPHPExcel->getActiveSheet()->getStyle("$posicao")->getFont()->setSize(7);        

}

}

foreach( $listaMerge as $valorMerge ){
$objPHPExcel->getActiveSheet()->mergeCells("$valorMerge");
}

foreach ( $listaMerge as $indice => $valor ) {
$objPHPExcel->getActiveSheet()->getStyle($valor)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
}       

$indColuna = 'A';   

}


$tcol_nro = 5;
$tcol_nome = 65;
$tcol_data = 272 - $tcol_nro - $tcol_nome;
$nro_colunas = 10;
$tcol_datas = $tcol_data / $nro_colunas;    

$indColuna = 'A';
$indLinha = 1;

$sql = "SELECT C.cur_cod, UPPER(C.cur_descr) AS cur_descr, C.cur_professor, C.cur_carga_listapresenca, T.tur_vaga, T.tur_dia, L.loc_descr,
    L.loc_endereco, loc_nro, loc_bairro, DATE_FORMAT(T.tur_horaini,'%H:%i') as tur_horaini,
    DATE_FORMAT(T.tur_horafin,'%H:%i') as tur_horafin, eixo_cod, eixo_descr, tur_cod
 FROM icc_curso C, icc_turma T, icc_local L, icc_eixo E
WHERE C.cur_cod=T.cur_cod_fk
  AND C.loc_cod_fk = L.loc_cod
  AND C.ev_cod_fk = '$ev_cod'
  AND T.eixo_cod_fk = C.eixo_cod_fk
  AND T.ev_cod_fk = C.ev_cod_fk
  AND T.ev_cod_fk = E.ev_cod_fk   
  AND T.tur_vaga > 0
  AND E.eixo_cod = C.eixo_cod_fk";

$sqlGeral = $sql. " $sql_curso ORDER BY eixo_cod, (cur_cod+0), tur_cod";
$cons_curso = mysql_query($sqlGeral)
OR DIE("<h5 class='erro'>ERRO NO BANCO DE DADOS - CURSOS</h5>");

While ($ln_curso = mysql_fetch_array($cons_curso)) {
$cur_cod = $ln_curso['cur_cod'];
$tur_cod = $ln_curso['tur_cod'];
$tur_vaga = $ln_curso['tur_vaga'];
$eixo_cod = $ln_curso['eixo_cod'];

$sqlInicial = $sql ." AND C.eixo_cod_fk = '$eixo_cod'
AND T.tur_cod = '$tur_cod'
AND T.eixo_cod_fk = C.eixo_cod_fk
AND T.cur_cod_fk = '$cur_cod'";


$imprimeTabela = 1;
$primeiraPagina = 1;
escreverCabecalho($imprimeTabela, $primeiraPagina,$sqlInicial , $tcol_nro, $tcol_nome,  $tcol_data, $nro_colunas, $tcol_datas, $objPHPExcel, $indColuna , $indLinha);
$primeiraPagina = 0;

$sql_alunos = "select LOWER(I.insc_nome) AS insc_nome
FROM icc_inscrito I, icc_inscricao S
WHERE I.insc_cpf=S.insc_cpf_fk
AND S.tur_cod_fk='$tur_cod'
AND S.eixo_cod_fk='$eixo_cod'
AND S.cur_cod_fk='$cur_cod'
AND S.insc_clas='1'
AND S.ev_cod_fk='$evento'
ORDER BY I.insc_nome";

$cons_alunos = mysql_query($sql_alunos.' LIMIT 1');

if (mysql_num_rows($cons_alunos) == 0)
$continue;

$cons_alunos = mysql_query($sql_alunos);
$numero = 0;
$altura = 6.5;

$indColuna = 'A';

$indLinha++;
While ($ln_alunos = mysql_fetch_array($cons_alunos)) {
$numero=$numero+1;
$insc_nome = $ln_alunos['insc_nome'];
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle("B$indLinha")->applyFromArray($styleArray);
$listaPresenca['A'][$indLinha] = $numero;
$listaPresenca['B'][$indLinha] = UcWords($insc_nome);

$indColunaAux =  'C';
For ($cont_datas = 1; $cont_datas <= $nro_colunas; $cont_datas++) {

    $objPHPExcel->getActiveSheet()->getStyle("$indColunaAux$indLinha")->applyFromArray($styleArray);
    $indColunaAux++;
}   

$indLinha++;    

    if ($numero == 22) {
        if ($tur_vaga == 22) {
            $imprimeTabela = 0;
        }
        escreverCabecalho($imprimeTabela, $primeiraPagina,$sqlInicial , $tcol_nro, $tcol_nome,  $tcol_data, $nro_colunas, $tcol_datas, $objPHPExcel, $indColuna , $indLinha);
        $indLinha++;
    }
}

$indColuna = 'A';

for ($cont = $numero+1; $cont <= $tur_vaga; $cont++) {
//echo $indLinha.' => '.$cont.'<br>';
$objPHPExcel->getActiveSheet()->getStyle("A$indLinha")->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle("B$indLinha")->applyFromArray($styleArray);
$listaPresenca[$indColuna][$indLinha] = $cont;


$indColunaAux =  'C';
For ($cont_datas = 1; $cont_datas <= $nro_colunas; $cont_datas++) {

    $objPHPExcel->getActiveSheet()->getStyle("$indColunaAux$indLinha")->applyFromArray($styleArray);
    $indColunaAux++;
}           

$indLinha++;

if ($cont == 22) {
    if ($tur_vaga == 22) {
        $imprimeTabela = 0;
    }
    escreverCabecalho($imprimeTabela, $primeiraPagina,$sqlInicial , $tcol_nro, $tcol_nome,  $tcol_data, $nro_colunas, $tcol_datas, $objPHPExcel, $indColuna , $indLinha);
    $indLinha++;
}
}

$imprimeTabela = 0;
$indLinha++;
$listaMerge[] = "A$indLinha:L$indLinha";
$listaPresenca[$indColuna][$indLinha++] =  'Observações:';
$listaMerge[] = "A$indLinha:L$indLinha";
$listaPresenca[$indColuna][$indLinha++] =  'Data de entrega da lista: ____/____/________  -  Assinatura do responsável pelo Curso/GF:  _____________________________________________________________________';

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);

foreach ( $listaPresenca as $coluna => $valor ) {

foreach( $valor as $celula  => $conteudo ) {

    $posicao = $coluna . $celula;
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue ($posicao, $conteudo );
    $objPHPExcel->getActiveSheet()->getStyle("$posicao")->getFont()->setSize(7);            

}

}

foreach( $listaMerge as $valorMerge ){
$objPHPExcel->getActiveSheet()->mergeCells("$valorMerge");
}   

}



$nome_relatorio = "Lista_Presenca.xls";

// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$nome_relatorio.'"');
header('Cache-Control: max-age=0');

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

Can’t you see anything? have characters mocked in cells? could you put the code?

– rray

2016/01/22 at 13:32
Try to put at the beginning of the file mb_internal_encoding('UTF-8'); mb_http_output('UTF-8'); to say that all strings in the script are utf-8 and if you use string function call it with the prefix mb_*, example: mb_substr()

– Leonardo

2016/01/22 at 15:41
extract($_GET) The.o WTF?

– rray

2016/01/22 at 16:20

No answers

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.