asked on

Could you point what is avoiding the PHPExcel library to fill the sheet?

Hi Experts

Could you point what is avoiding the PHPExcel library to fill the sheet?

Accordingly to:

					 
		// This produces data in MySQL			 
$query['query'] = "
	SELECT  
		sg.nome_seguradora as cliente_corporativo,
		DATE_FORMAT(data, '%d/%m/%y') as mes,
		it._name as analista,
		IF(interna_externa = 'I', 'Interna',IF(interna_externa ='E', 'Externa','-')) as interna_externa,
		enviado_por,
		nome_reclamante,
		IF(genero = 'M','Masculino',IF(genero = 'F','Feminio','-')) as genero,
		IF(fisica_juridica = 'F','Física',IF(genero = 'J','Jurídica','-')) as fisica_juridica,
		cpf_cnpj as cpf_cnpj,
		DATE_FORMAT(data_atendimento, '%d/%m/%Y') as data_atendimento,
		sise,
		cc.descricao as nome_produto,
		se.descricao as servico,
		su.descricao as sub_servico,
		cidade,
		uf,
		mo.motivo as motivo,
		de.descricao as origem_problema,
		bp.descricao as base_prestador,
		enviado_por as agente,
		DATE_FORMAT(enviado_dpto_em, '%d/%m/%Y %H:%i:%s') as enviado_dpto_em,
		DATE_FORMAT(resposta_dpto_em, '%d/%m/%Y %H:%i:%s') as resposta_dpto_em,
		responsavel,
		acoes_dpto,
		comentarios,
		parecer,
		DATE_FORMAT(recebido_em, '%d/%m/%Y %H:%i:%s') as recebido_em,
		DATE_FORMAT(respondido_em, '%d/%m/%Y %H:%i:%s') as respondido_em,
		tempo_resposta,
		IF(procedencia = 'A', 'Em Análise', IF(procedencia = 'P', 'Procedente', IF(procedencia = 'I', 'Improcedente', '-'))) as procedencia,
		IF((custo IS NULL) OR  (custo=0), 'Não' , 'Sim') as houve_custo,
		custo,
		IF((reembolso IS NULL) OR  (reembolso=0), 'Não' , 'Sim') as houve_reembolso,
		reembolso
   FROM
			system_reclamacao sr
		LEFT OUTER JOIN system_cliente_corporativo cc
			ON sr.fk_nome_produto = cc.id_cliente_corporativo
		LEFT OUTER JOIN system_servico se
			ON sr.fk_servico = se.id_servico
		LEFT OUTER JOIN system_sub_servico su
			ON sr.fk_sub_servico = su.id_sub_servico
		LEFT OUTER JOIN system_motivo mo
			ON sr.fk_motivo_reclamacao = mo.id_motivo
		LEFT OUTER JOIN system_departamento de
			ON sr.fk_origem_problema = de.id_dpto
		LEFT OUTER JOIN system_base_prestador bp
			ON sr.fk_base_prestador = bp.id_base_prestador
		LEFT OUTER JOIN system_seguradora sg
			ON sr.fk_seguradora = sg.id_seguradora
		LEFT OUTER JOIN interface_users it
			ON sr.created_by = it.idinterface_users";

        $get_reclamacao = $this->reclamacao_model->new_get($query);
      
        $colunas = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH");

        //Starts library
        $this->load->library('excel');
        $objPHPExcel = new PHPExcel();

        //File informations
        $objPHPExcel->getProperties()->setCreator("Project");
        $objPHPExcel->getProperties()->setLastModifiedBy("Project");
        $objPHPExcel->getProperties()->setTitle("Exportação do Sistema");
   
        //Header
        $objPHPExcel->setActiveSheetIndex(0);


        $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Cliente Corporativo');
        $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Data');
        $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Analista');
        $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Interna ou Externa');
        $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Enviado Por?');
        $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Nome do Cliente. (Reclamante)');
        $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Genero');
        $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Pessoa Física/ Jurídica');
        $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'CPF/CNPJ');
        $objPHPExcel->getActiveSheet()->SetCellValue('J1', 'Data Atendimento');
        $objPHPExcel->getActiveSheet()->SetCellValue('K1', 'SISE');
        $objPHPExcel->getActiveSheet()->SetCellValue('L1', 'Nome do Produto');
        $objPHPExcel->getActiveSheet()->SetCellValue('M1', 'Serviço');
        $objPHPExcel->getActiveSheet()->SetCellValue('N1', 'Sub-Serviço');
        $objPHPExcel->getActiveSheet()->SetCellValue('O1', 'Cidade');
        $objPHPExcel->getActiveSheet()->SetCellValue('P1', 'UF');
        $objPHPExcel->getActiveSheet()->SetCellValue('Q1', 'Motivo Reclamação');
        $objPHPExcel->getActiveSheet()->SetCellValue('R1', 'Origem do Problema');
        $objPHPExcel->getActiveSheet()->SetCellValue('S1', 'Base Prestador');
        $objPHPExcel->getActiveSheet()->SetCellValue('T1', 'Nome do Agente');
        $objPHPExcel->getActiveSheet()->SetCellValue('U1', 'Enviado ao Departamento em');
        $objPHPExcel->getActiveSheet()->SetCellValue('V1', 'Resposta do Departamento em');
        $objPHPExcel->getActiveSheet()->SetCellValue('W1', 'Responsável');
        $objPHPExcel->getActiveSheet()->SetCellValue('X1', 'Ações do Depto.');
        $objPHPExcel->getActiveSheet()->SetCellValue('Y1', 'Comentarios/Reclamação/Elogio');
        $objPHPExcel->getActiveSheet()->SetCellValue('Z1', 'Parecer da Qualidade');
        $objPHPExcel->getActiveSheet()->SetCellValue('AA1', 'Recebido Em:');
        $objPHPExcel->getActiveSheet()->SetCellValue('AB1', 'Respondido em:');
        $objPHPExcel->getActiveSheet()->SetCellValue('AC1', 'Tempo de Resposta');
        $objPHPExcel->getActiveSheet()->SetCellValue('AD1', 'Procedencia:');
        $objPHPExcel->getActiveSheet()->SetCellValue('AE1', 'Houve Custo?');
        $objPHPExcel->getActiveSheet()->SetCellValue('AF1', 'Custo');
        $objPHPExcel->getActiveSheet()->SetCellValue('AG1', 'Houve Reembolso?');
        $objPHPExcel->getActiveSheet()->SetCellValue('AH1', 'Reembolso');


        //Dinamically mounts table
        $linha = 2;
        foreach ($get_reclamacao as $registers) {
            $col = 0;
            foreach ($registers as $value) {
                //print_r($colunas[$col] . $linha);
                $objPHPExcel->getActiveSheet()->SetCellValue($colunas[$col] . $linha, $value);
                $col++;
            }
            $linha ++;
        }

        $headerStyle = array(
            'font' => array(
                'bold' => true,
                'color' => array(
                    'argb' => 'FFFFFFFF'
                )
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'startcolor' => array(
                    'argb' => 'FF002060',
                ),
            ),
        );

        $tableStyle = array(
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FF000000'),
                ),
            ),
        );

        //Ajusta automaticamente o tamanho das colunas
        foreach ($colunas as $coluna) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($coluna)->setAutoSize(true);
        }
        $objPHPExcel->getActiveSheet()->getStyle('A1:AH1')->applyFromArray($headerStyle);
        $objPHPExcel->getActiveSheet()->getStyle('A1:AH' . ($linha - 1))->applyFromArray($tableStyle);
        $objPHPExcel->getActiveSheet()->setTitle('RECLAMAÇÕES');

        $linhas = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
        $objPHPExcel->getActiveSheet()->getStyle('AF2:AF' . $linhas)->getNumberFormat()->setFormatCode('#,##0.00');
        $objPHPExcel->getActiveSheet()->getStyle('AH2:AH' . $linhas)->getNumberFormat()->setFormatCode('#,##0.00');


        $savePath = $_SERVER['DOCUMENT_ROOT'] . "/meeta_ike/public/excel/export.xlsx";
        $downloadPath = base_url() . "public/excel/export.xlsx";

        //Grava a tabela no local especificado
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($savePath);
        echo $downloadPath;

Open in new window

The query produces data in MySQL

But Excel sheet is clean:

Thanks in advance

ASKER CERTIFIED SOLUTION

gr8gonzo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eduardo Fuerte

ASKER

Hi

$get_reclamacao has values.

$linha = 2;
foreach ($get_reclamacao as $registers) {
	$col = 0;
	foreach ($registers as $value) {
		
		// This has value
		//print_r($colunas[$col] . $linha);
		//die;
		
		// This has value
		//print_r($value);
		//die;
	
		$objPHPExcel->getActiveSheet()->SetCellValue($colunas[$col] . $linha, $value);
		$col++;
	}
	$linha ++;
}

Open in new window

I guess something after is avoiding the sheet to be loaded...

Eduardo Fuerte

ASKER

Hi gr8gonzo

What I found is that special characters made PHPExcell to save an empty sheet...

So opened this question, to continue.

https://www.experts-exchange.com/questions/29000839/Could-you-point-what's-necessary-to-make-PHPExceel-to-consider-special-characters-when-saving-the-sheets.html

Eduardo Fuerte

ASKER

Thanks, it made to advance.

hielo

On your initial post you have a screen shot of a mysql tool that you use to run/test your queries. Out of curiosity, what tool is that?

As for your problem, on the mysql tool above, try running:

SHOW VARIABLES LIKE  'char%';

Open in new window

What's the result of the mysql tool above?

On your php script, you have:

...
$query['query']='...';
$get_reclamacao = $this->reclamacao_model->new_get($query);
...

Open in new window

It's not clear where you are connecting to the db (my guess would be that you are doing so within the model) but try executing SHOW VARIABLES LIKE 'char%'; immediately after you connect to the db and then echo the output to the browser. What's the result of the php script?

For both methods, you should be seeing something similar to:

Variable_name              Value
character_set_client       utf8
character_set_connection   utf8
....
character_set_system       utf8

Open in new window

but the values may be different.

gr8gonzo

@hielo - that's HeidiSQL. Great, free tool. The GUI sort of feels like the older Enterprise Manager for MSSQL (which was an awesomely-user-friendly GUI, in my opinion).

hielo

Thanks gr8gonzo. I'll give it a try.

Eduardo Fuerte

ASKER

Hi hielo

Sorry the delay.

The tool I'm using is dbForge Studio for MySQL - I guess the screen is very near HeidiSQL - that I don't know yet.

It's a paid tool, not too much expensive, excelent!