[Resolvido] Relatorio com PHPExcel

[diego.baiao 0]

Olá bom dia a todos! 

 

Pessoal preciso fazer um relatório que venha a gerar um arquivo xlsx automaticamente, de uma consulta do banco de dados, estou usando o "PHPEXcel" para isso. 

Tenho  essa necessidade abaixo:

>>  Esse arquivo tem que gerar abas automaticamente quando o limite chegar a 1 milhão de linhas sabemos que o excel a partir de 2003 o limite de linhas por "planilha" ou "aba" é de "um milhão" 1k,  estou arredondado para baixo, o certo é 1.048.576 linhas por 16.384 colunas por aba/planilha.  

Exemplo: Então resumindo vou tirar um relatório deu 3 milhões e meio de registros, serão 4 abas/planilhas, sendo 3 abas com um milhão cada e mais 1 aba com meio milhão. Sendo que ele deve fazer tudo sozinho se tiver 10 milhões e assim vai, eu consigo já gerar o arquivo com a consulta vindo do banco, mais não consigo gravar todas as linhas na tabela e muito menos fazer criar as abas automaticamente. 

*** estou seguindo essa referencia abaixo com uso no phpexcel,  neste ensina o uso para criar novas "abas/planilhas" no arquivo a ser gerado mais não consigo colocar em pratica. 

(http://interessespessoais.com/programacaoweb/criar-varias-folhas-num-ficheiro-excel-com-a-classe-phpexcel/)

 

Dados importantes: 

1 - número de linhas por aba é 1 milhão de linhas; 

2 - as colunas são fixas então nunca irá ter mais ou menos ao que já tem criado( vai da coluna "A" a "O' são 15 colunas o total);

3 - possui um cabeçalho fixo, vou deixar fictício esses dados não são relevantes ( serão eles mesmos "A', "B", "C" ... "O"). 

>> no código abaixo o cabeçalho vai ser sempre a primeira linha logo abaixo da coluna como fiz ali "A1" chamei de "id" seria a primeira delas. 

 

>> nesse caso para não ficar pesado a consulta e demora no resultado eu estou trazendo  15 linhas(registros) minha tentativa é gera 3 abas com 5 registros em cada uma delas.

Dando certo ai mudo para a suposta quantidade de registros que queira alcançar. hehe

 

Segue meu código abaixo já gerando o relatório porem com uma linha editada rs.

"Mais ao menos gera no formato certo sem erros". 

<?php
/* 	Rodando esta gravando o xlsx */
	require_once "conexao.php"; 
	require_once 'Classes/PHPExcel.php';

	// DEFINE O FUSO HORARIO COMO O HORARIO DE BRASILIA
    date_default_timezone_set('America/Sao_Paulo');

	//cria a conexao com o banco
	$conexao_pdo = new PDO("pgsql:host=$host dbname=$dbname user=$user password=$senha"); 

		//laço para exibir se a conesão foi bem sucedida
	 	if ($conexao_pdo) 
		   { echo 'Conexão com o PostgreSQL realizada com sucesso!!<br>'; }
		   else
		   { echo 'Falha na conexão com o PostgreSQL!'; }

		//	grava milesegundo no arquivo gerado		
		$data_hora  = date('d/m/Y H:i:s', time());
		$microtime	= microtime(true);
        $time 		= explode(".", $microtime);
       	$mSecs   	= $time[1]; 
		
		if (strlen($mSecs) == 3){
			$mSecs = $mSecs."0";
		}elseif (strlen($mSecs) == 2){
			$mSecs = $mSecs."00";
		}

		function limpaData($data_hora){
	    	$rem = array ('-',':',' ','/','.');
	    	$sub = array ('_','_','_','_','_');
	    	return str_replace($rem, $sub, $data_hora);
		}

		$data = limpaData($data_hora)."_".$mSecs; //pega data e concatena com microsegundos

		//consulta via pdo
		// nesse caso para nao ficar pesado trago 15 linhas minha tentativa é gera 3 abas com 5 registros em cada uma delas
	 	$consulta = $conexao_pdo->query(
	 		"SELECT * FROM ENVIOS.ENVIO_CONTATO LIMIT 15"
	 	);

	 	//muda o modo padrão de busca para um objeto PDOStatement
	 	$consulta->setFetchMode(PDO::FETCH_ASSOC);

	 	$count = $consulta->rowCount(); // pega o numero de registros na consulta

		if ($count > 0) {

			while($row = $consulta->fetch()) {
			//	Criar um novo objecto PHPExcel
			$objPHPExcel = new PHPExcel();
 
		 	// Inserir dados nas células A1 e A2
			$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A1',  'id')
			->setCellValue('A2',  $row['id']);	
				
			}

		}

		// // Indicação da criação do ficheiro
		$file = $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$file->save("downloads\periodo_" .$data. ".xlsx");

?>

O arquivo é gerado na pasta "downloads" dentro do projeto, porém com apenas 1 dado ( so fiz ele ir na primeira celular "A" e gravar um dado no caso um "id" da minha consulta, teria que fazer isso com o resto das informações, "B" os "nomes", "C" as "idades" e assim vai...   não sei como fazer um loop nessa situação com foreach, while, for, etc... 

 

* O meu problema esta sendo mais com logica de programação vou estudar para ver se consigo ir adiante, mais se conseguirem derem uma força eu agradeço.

 

Segue duas prints abaixo: 

1 - do arquivo gerado >> http://prntscr.com/hv37mh

2 - do arquivo aberto >> http://prntscr.com/hv385u 

 

[ygor.anjos 23]

Troque

// Inserir dados nas células A1 e A2
			$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A1',  'id')
			->setCellValue('A2',  $row['id']);	

por:

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $linha, $informacao);

ele vai adicionar a informação na coluna 0 (A) na linha selecionada

ai você faz 15 desses e vai mudando as colunas 1 (B) , 2(C) e etc
você pode usar o count pra ir selecionando a linha, no começo do while você adiciona um

 if($count > 100000000)(
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($sheet)
$sheet++;
)

n esqueçe de antes do while criar a variavel $sheet=0, ela que vai controlar a criação de abas.

[diego.baiao 0]

3 horas atrás, ygor.anjos disse:

Troque

// Inserir dados nas células A1 e A2
			$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A1',  'id')
			->setCellValue('A2',  $row['id']);	

por:

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $linha, $informacao);

ele vai adicionar a informação na coluna 0 (A) na linha selecionada

ai você faz 15 desses e vai mudando as colunas 1 (B) , 2(C) e etc
você pode usar o count pra ir selecionando a linha, no começo do while você adiciona um

 if($count > 100000000)(
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($sheet)
$sheet++;
)

n esqueçe de antes do while criar a variavel $sheet=0, ela que vai controlar a criação de abas.

Show vou ver aqui e dou um feedback amanhã, 2018 começou bombando, sabia que estava perto rs... tenho que treinar linguagem O.O. faço tudo estruturado uma ou outra que faço com O.O. e pegar um pouco na lógica de programação, agradeço a tua ajuda vou tentar fazer o esquema aqui e te falo... vlw

[diego.baiao 0]

Opa segunda posto o resultado ainda não consegui mexer nesse código.

[gabrieldarezzo 255]

Em 02/01/2018 at 10:42, diego.baiao disse:

10 milhões

Não tem muita conversa.

 

Pensou em volumes gigantes é necessário estudar em FILA / Processamento paralelo.

 

https://aws.amazon.com/pt/sqs/

 

--- 

 

Sou meio contra recomendar FW de cara, mas achei a documentação bem completa do Laravel.

https://laravel.com/docs/5.5/queues

 

 

 

 

 

 

[Matheus Tavares 167]

Também não custa dizer... PHPExcel "não existe" mais.

Veja: https://github.com/PHPOffice/PHPExcel

 

Agora é: https://github.com/PHPOffice/PhpSpreadsheet

 

Eles corrigiram muitos bugs.

Eu mesmo precisei atualizar essa biblioteca em alguns projetos há algum tempo atrás.

 

Aqui tem um guia de migração: https://phpspreadsheet.readthedocs.io/en/develop/topics/migration-from-PHPExcel/

[Almir_Santos 7]

Olá.Percebi sua grande demanda qual você relatou:

" Pessoal preciso fazer um relatório que venha a gerar um arquivo xlsx automaticamente, de uma consulta do banco de dados, estou usando o "PHPEXcel" para isso. "

 

Eu entendi que sua DEMANDA PRINCIPAL é gerar relatórios apartir de uma consulta em banco de dados e que tais relatórios possam ser abertos em planilha (Excel especificamente).Você se propôs a usar PHP para tal demanda.Você não citou o banco usado.

Para solucionar sua DEMANDA PRINCIPAL outros meios podem ser usados.Vou citar uma solução que conheço:

Você deveria ter um banco de dados online e usar a linguagem CQL para fazer a consulta de busca e com os resultados da consulta fazer o tratamento(Testes com 5 milhoes de registros e 15 mil campos ja foram feitos)

Suponha que tem a tabela "EMPREGADOS" conforme abaixo.

ID ; EMPREGADO ; SALARIO ;FUNCAO

001 ;José Silva ; 1220,00 ; Aux.Produção

002;Maria Santos ; 1230,00 ; Diretora

...     ;   ..      .    ;      centenas de rows    ;        ......

999;Sandro Maciel ; 5900,00 ; Presidente

Então você faria uma busca :

 

{none;@coment;(Vai selecionar os valores entre 1200 e 1250 do campo Salario (índice 2 da tabela));0;0;0;0;query=0;destino=0}
{
       c:\Path_servidor\htdocs\EMPREGADOS;
       @min_max;
       (*);
       (1200,00);
       (1250,00);
       0;
       query=2;
       destino=0
}
{none;@coment;(Apos a busca,poderá fazer um relatório online mesmo em html borda 1,fonte 12 ,cor de fonte orange,folha de estilo);0;0;0;0;query=0;destino=0}
{
       c:\Path_servidor\htdocs\EMPREGADOS;
       @html;
       1;
       12;
      ('hostpath\sua_folha_de_estilos.css');
       Orange;
       query=0;
       destino=0
}
Poderá baixar o resultados e abrir diretamente no openOficce (Calc) ou Excel (importação),bastaria apenas copiar os resultados da busca para uma nova tabela temporária.(2 linhas de código para fazer isso)

Poderá até manipular a base que se encontra remota por uma aplicação personalizada como nas fotos anexadas:

(Com essa tecnologia não é necessário manipular xls,pois a base inteira é em formato CSV.So usaríamos o Excel ou Calc para abrir o arquivo).

Mais informações no blog do autor

 

 

Fiz o exemplo do video abaixo hoje,especialmente para você ter noção:

 

[diego.baiao 0]

Opa obrigado, vou ler sobre, agora que iria voltar a implementação desse cara.

[diego.baiao 0]

Em 04/01/2018 at 16:54, Matheus Tavares disse:

Também não custa dizer... PHPExcel "não existe" mais.

Veja: https://github.com/PHPOffice/PHPExcel

 

Agora é: https://github.com/PHPOffice/PhpSpreadsheet

 

Eles corrigiram muitos bugs.

Eu mesmo precisei atualizar essa biblioteca em alguns projetos há algum tempo atrás.

 

Aqui tem um guia de migração: https://phpspreadsheet.readthedocs.io/en/develop/topics/migration-from-PHPExcel/

 

Em 02/01/2018 at 11:21, ygor.anjos disse:

Troque

// Inserir dados nas células A1 e A2
			$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A1',  'id')
			->setCellValue('A2',  $row['id']);	

por:

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $linha, $informacao);

ele vai adicionar a informação na coluna 0 (A) na linha selecionada

ai você faz 15 desses e vai mudando as colunas 1 (B) , 2(C) e etc
você pode usar o count pra ir selecionando a linha, no começo do while você adiciona um

 if($count > 100000000)(
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($sheet)
$sheet++;
)

n esqueçe de antes do while criar a variavel $sheet=0, ela que vai controlar a criação de abas.

 

[diego.baiao 0]

Opa, só informando recebi varias ajudas dos amigo acima e agradeço a todos porem a necessidade é que crie um relatório em xlsx mesmo, o usuário faz o download do arquivo para usar filtros e afins... mais vi muitas reclamações que para gerar arquivos grandes o "PHP Excel não é tão bom e caiu em desuso, costuma dar erro e demora muito tendo o sucessor dele o spreadsheet " e procurando um pouco mais fundo descobri o "XLsxWriter" (https://github.com/mk-j/PHP_XLSXWriter) estou estudando ele e já consegui fazer grande parte, assim que finalizar o código inteiro eu posto para ajudar alguém que precise também.  

Já consigo gravar 1k (demora por volta de 6 a 7 minutos vai ser executado em segundo plano e ao terminar vai ser gerando um link para donwload a ideia é essa) vou fazer para gerar mais abas automaticamente e ver a parte de config de execução de script como memory_limit e max_execution_time

 

Vi também o "spreadsheet" porém não achei muito exemplos de como usar e achei um pouco confuso as classes, eu não tenho tanta experiência por isso deve ter ficado confuso e com esse outro achei bem mais fácil e parece ser bem ágil conforme informações do git, os relatórios não irão passar tão cedo dos 5 milhões hoje fica em torno de 1,5k a 3,5k .

 

Obrigado a todos

[Almir_Santos 7]

O exemplo mostrado por mim ,com office.org consegue gerar em poucos segundos.

[diego.baiao 0]

2 horas atrás, Almir_Santos disse:

O exemplo mostrado por mim ,com office.org consegue gerar em poucos segundos.

Entendi vou ver se chego a alguma conclusão, como seu codigo

 

Tinha perguntado sobre o banco eu uso "postgresql". (muitos inner joins hehehe)

[diego.baiao 0]

Em 10/01/2018 at 12:00, Almir_Santos disse:

O exemplo mostrado por mim ,com office.org consegue gerar em poucos segundos.

Te agradeço mais não vou ter tempo de implementar algo com essa linguagem, mais pensei em outra maneira só estou enrolado com o código. Desisti da outra forma pois consome muita memoria, mais ao invés de quebrar em varias abas posso quebrar o processo por arquivo, já testei aqui vai ficar bom... só preciso de uma ajuda a modificar meu código nas interações quando atingir um certo limite de registros... vou posta abaixo. 

[diego.baiao 0]

Opa pessoal mudei o conceito da coisa de gerar em várias abas vai ser problemático acima de 1k de registros já se torna inviável para qualquer aplicação. 

Queria pegar o meu código abaixo e fazer o seguinte gerar para vários arquivos, exemplo meu $count deu 1,5k de registros, vou quebrar em cada arquivo 0,5k que daria um total de 3 arquivos com 0,5k . Até ai blz.

Eu estou usando o XlsxWriter é bém fácil de usar e achei leve, para gerar um arquivo desse uso em media 400MB mais tenho um servidor potente e não vou ter problema com esse relatório não é todo cliente que gera 1,5k na verdade é somente 1 "muito chato"... 

Então simulando meu $count = 1500000; 

Preciso de um laço que independente do meu $count crie esses arquivos sem perder o fio da meada... "digo sendo continuo os registros no proximo arquivo" sem repetir os mesmos... ai vi alguns laços mais não consegui implementar  while, for e if que usam o esquema de "break" e "continue" não sei se teria como usar esses karas para fazer o que quero. 

 

<?php 
ini_set("memory_limit", '2000M');
date_default_timezone_set('America/Sao_Paulo');

// ========== info de data e hora de inicio ===================== //
$t = microtime(true);
$micro = sprintf("%06d",($t - floor($t)) * 1000000);
$d = new DateTime( date('Y-m-d H:i:s.'.$micro, $t) );
var_dump($d->format("Y-m-d H:i:s.u"));
// ================================================ //
echo "==============================================<br>";
// =============================================================== /

require_once ('xlswriter/xlsxwriter.class.php');
require_once ('conexao.php');

	//cria a conexao com o banco
	$conexao_pdo = new PDO("pgsql:host=$host dbname=$dbname user=$user password=$senha"); 

		//laço para exibir se a conesão foi bem sucedida
	 	if ($conexao_pdo) 
		   { echo 'Conexão com o PostgreSQL realizada com sucesso!!<br>'; }
		   else
		   { echo 'Falha na conexão com o PostgreSQL!'; }

		//grava data e hora com milesegundo no arquivo que vai ser gerado		
		$data_hora  = date('d/m/Y H:i:s', time());
		$microtime	= microtime(true);
        $time 		= explode(".", $microtime);
       	$mSecs   	= $time[1]; 
		
		if (strlen($mSecs) == 3){
			$mSecs = $mSecs."0";
		}elseif (strlen($mSecs) == 2){
			$mSecs = $mSecs."00";
		}

		function limpaData($data_hora){
	    	$rem = array ('-',':',' ','/','.');
	    	$sub = array ('_','_','_','_','_');
	    	return str_replace($rem, $sub, $data_hora);
		}

		$data = limpaData($data_hora)."_".$mSecs;

		//consulta via pdo
	 	$consulta = $conexao_pdo->query(
	 		"SELECT * FROM ENVIOS.ENVIO_CONTATO LIMIT 1500000"
	 	);

	 	//muda o modo padrão de busca para um objeto PDOStatement
	 	$consulta->setFetchMode(PDO::FETCH_ASSOC);

	 	$count = $consulta->rowCount(); //pega o numero de registros na consulta (1,5k) exemplo dado
		
		//inicio do tratamento para gerar o arquivo xlsx
	 	if ($count > 0 ) {

	 		$array = array();
	 		$sheet = 1; //esse kara nao influencia em nada
			
            //instancia a abertura de um documento novo xlsx
	 		$writer = new XLSXWriter();

	 			$loop = null;
		 		while($row = $consulta->fetch()) {
		 			//Pega os dados da consulta e armazenas nas variaveis especificas dentro do array
		 			$array[$loop]["id"] 					= $row['id'];
				 	$array[$loop]["data_hora_execucao"] 	= $row['data_hora_execucao'];
				 	$array[$loop]["mensagem"] 				= $row['mensagem'];
				 	$array[$loop]["ddd_movel"]				= $row['ddd_movel'];
				 	$array[$loop]["movel"] 					= $row['movel'];
				 	$array[$loop]["id_relatorio_status"]	= $row['id_relatorio_status'];

				 	//Cria a head do excel - nomemclatura de cada coluna e o tipo de dado a se importar
		 			$writer->writeSheetHeader($sheet, array('id'=>'integer','data_hora_execucao'=>'datetime','mensagem'=>'string','ddd_movel'=>'integer','movel'=>'integer','id_relatorio_status'=>'integer'));

		 			//Grava o valor das celulas
					$writer->writeSheetRow($sheet, array($array[$loop]["id"],$array[$loop]["data_hora_execucao"],$array[$loop]["mensagem"],$array[$loop]["ddd_movel"],$array[$loop]["movel"],$array[$loop]["id_relatorio_status"] ));

				 	$loop ++;

				}
            //gera o arquivo xlsx baseado da interação do $writer->writeSheetRow
			$writer->writeToFile("periodo_" .$data. ".xlsx");
			echo '#'.floor((memory_get_peak_usage())/1024/1024)."MB"."\n";  // so imprimo o consumo da memoria a fins informativos
		}

echo "<br>";
// ========= info de data e hora de termino =================== //
$t = microtime(true);
$micro = sprintf("%06d",($t - floor($t)) * 1000000);
$d = new DateTime( date('Y-m-d H:i:s.'.$micro, $t) );
var_dump($d->format("Y-m-d H:i:s.u"));
// ================================================ //
echo "==============================================<br>";
// =============================================================== /

?>

Segue abaixo a print screen de saida. 

http://prntscr.com/hz4hk0

http://prntscr.com/hz4kwf

 

Não sei se deu pra entender bem o que queria e se seria possível, senão vou ter que gravar no banco a consulta e marcar o registro inicio e final de cada arquivo a ser gerado.(e queria que fosse direto sem ter que usar este método)

[diego.baiao 0]

Só pra constar foi criado um servidor temporário para gerar esse relatório que não é nada viável o negocio é montar e gravar em um banco não relacional ex: mongo db e dele gerar o relatório conforme comentário do Almir Santos é uma alternativa.  

O bom mesmo que vi seria o cliente baixar e solicitar com o "Power BI" um amigo de trabalho mostrou e é uma ferramenta top sendo que ele baixa o power bi na maquina dele e configuramos para ele faça a chamada da requisição do relatório onde é baixado um arquivo na extensão do programa que ele abrirá no mesmo e terá todos os dados que ele precisa analítico e sintético e com vários gráficos. 

 

Agradeço a ajuda de todos, topico encerrado.!