MySQL-to-Excel usando o PHP

[studdiox 0]

E aee galera ... blza

Bom procurei e nao achei nenhum que desse certo ....

Queria pegar os dados de uma tabela MySQL e EXPORTAR para o formato do Excel usando o php para isso ....

sera que alguem pode dar uma maozinha com esse meu probleminha !!!!

[~TiuTalk~ 7]

É só fazer na formatação normal de uma tabela HTML usando <table> e 'Salvar como' .xls ou usar o header() pra dar content type em XLS. ;)

 

Qualquer dúvida é só falar!

 

Tente usar esse código após todo o HTML da table:

 

$export_file = "tabela.xls";
	ob_end_clean();
	ini_set('zlib.output_compression','Off');
   
	header('Pragma: public');
	header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");				  // Data no passado
	header('Last-Modified: '.gmdate('D, d M Y H:i:s') . ' GMT');
	header('Cache-Control: no-store, no-cache, must-revalidate');	 // HTTP/1.1
	header('Cache-Control: pre-check=0, post-check=0, max-age=0');	// HTTP/1.1
	header ("Pragma: no-cache");
	header("Expires: 0");
	header('Content-Transfer-Encoding: none');
	header('Content-Type: application/vnd.ms-excel;');				 // IE & Opera
	header("Content-type: application/x-msexcel");					// Outros
	header('Content-Disposition: attachment; filename="'.basename($export_file).'"');

http://forum.imasters.com.br/public/style_emoticons/default/bye1.gif

[Beraldo 864]

Postado recentemente:

http://imasters.com.br/artigo/11505/ph...excel_pelo_php/

 

Não testei, mas parece ser uma boa solução

[studdiox 0]

Postado recentemente:

http://imasters.com.br/artigo/11505/ph...excel_pelo_php/

 

Não testei, mas parece ser uma boa solução

 

 

Opa Beraldo blza ... tentei com o post do imaster mas nao consegui vou postar um aqui que eu achei so q ele nao ta exportando pra ver se alguem descobre onde tah o erro.

[studdiox 0]

É só fazer na formatação normal de uma tabela HTML usando <table> e 'Salvar como' .xls ou usar o header() pra dar content type em XLS. ;)

 

Qualquer dúvida é só falar!

 

Tente usar esse código após todo o HTML da table:

 

$export_file = "tabela.xls";
	ob_end_clean();
	ini_set('zlib.output_compression','Off');
   
	header('Pragma: public');
	header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");				  // Data no passado
	header('Last-Modified: '.gmdate('D, d M Y H:i:s') . ' GMT');
	header('Cache-Control: no-store, no-cache, must-revalidate');	 // HTTP/1.1
	header('Cache-Control: pre-check=0, post-check=0, max-age=0');	// HTTP/1.1
	header ("Pragma: no-cache");
	header("Expires: 0");
	header('Content-Transfer-Encoding: none');
	header('Content-Type: application/vnd.ms-excel;');				 // IE & Opera
	header("Content-type: application/x-msexcel");					// Outros
	header('Content-Disposition: attachment; filename="'.basename($export_file).'"');

http://forum.imasters.com.br/public/style_emoticons/default/bye1.gif

 

E aee blza ~TiuTalk~

Cara consegue me dar mais detalhes sobre sua ideia não entendi direito http://forum.imasters.com.br/public/style_emoticons/default/ermm.gif

[studdiox 0]

Bom achei este aqui ele seria para listar tanto em .xls quanto .doc mas esta ocorrendo um erro e nao sei o que é vou por a materio de onde tirei este escript

esta em portugues para que todos tbm possam usar posteriormente isso que no meu ver é muito útil

 

Matréria Original

 

----------------------------------CLIQUE AQUI PARA VER A MATERIA ORIGINAL EM PORTUGUES MySQL to Excel

 

 

<?php
/*
--Dynamic Excel or Word File from MySQL--
php-doc-xls-gen for php/MySQL: (.doc or .xls dumper):

This script takes the contents of a MySQL table and dumps it to
either a dynamically-generated MS Word File (file with ending '.doc')
or a dynamically-generated MS Excel File (file with ending '.xls').

Prerequisites:  You Must have MS Word and/or MS Excel installed on
the same computer as your web browser for this to work (although
the script can be placed on any Unix/Linux server, you have to access it using
a browser on a Windows machine with either Word or Excel installed).

How to use:
1)edit the MySQL Connection Info below for your MySQL connection
  & for the name of the MySQL table that you would like to make the dump for
2)save this file somewhere on your server
3)link to this file from another page:
  a)for Word dumps:
	<a href="this_file_name.php?w=1">link to word dump</a>
  b)for Excel dumps:
	<a href="this_file_name.php">link to excel dump</a>
  --or else--
  create a Bookmark to this page (include any of the optional parameters
  described below as part of the query string for the bookmarked URL)
4)how to reuse this code to create a dump for ANY MySQL table on your server:
  a)comment-out this line below under MySQL Connection Info:
	//$DB_TBLName = "your_table_name";
  b)include the name of your MySQL table in links (or bookmarks) to this page
	as an extra parameter:
	ie: for word dump--
	<a href="this_file_name.php?w=1&DB_TBLName=your_table_name">link to word dump</a>
	ie: for excel dump--
	<a href="this_file_name.php?DB_TBLName=your_table_name">link to excel dump</a>
  c)all of the above also holds true for the name of the Database:
	you could pass along the name of the Database as a parameter to this script
	in order to use it on many different databases on your server:
	comment out //$DB_DBName = "your database"; in this script below 
	and then link to this file like:
	"this_file_name.php?$DB_DBName=your_database&DB_TBLName=your_table_name..."
5)if you're resourceful, you could also pass the sql statement to be used for this
  script as a parameter: "this_file_name.php?sql=..."
  but you might have to URL-ENCODE your sql statement before passing it to this script,
  and then URL-DECODE it in the beginning of this script for it to work.

To change the formatting of the Word or Excel File generated:
change the respective parts of the coding for the word or the excel file that format
the database info sent to the browser.  Most useful for this are the escape characters
for tabs ('\t') & line returns ('\n').  Experiment with these until you get the formatting
that you desire.

If you're going to be using this script with SSL, please see the comments marked
'A NOTE ABOUT USING THIS SCRIPT WITH SSL' found below!

This code is freeware (GPL).  Please feel free to do with it what you'd like.
Comments, bugs, fixes to:
churmtom@hotmail.com

Originally: Nov. 25th, 2001
Updated:	May  12th, 2002
Updated:	July  1st, 2002
Updated:	Jan	 19th, 2003 - SSL Fix for MSIE

Thanks to Josue & Steven d.B. for helping point out
improvements for this code!
*/

//EDIT YOUR MySQL Connection Info:

$DB_Server = "mysql.site.com.br";		//your MySQL Server 
$DB_Username = "root";				 //your MySQL User Name 
$DB_Password = " ";				//your MySQL Password 
$DB_DBName = "banco";				//your MySQL Database Name 



// get the right table/year
if(substr($table, 0, 3) == "ev_"){$table = "$ev_year";} 
if(substr($table, 0, 3) == "jn_"){$table = "$jrn_year";} 
if(substr($table, 0, 3) == "cs_"){$table = "$cs_year";} 

$DB_TBLName = "tb_veiculos";				//your MySQL Table Name 


//$DB_TBLName,  $DB_DBName, may also be commented out & passed to the browser
//as parameters in a query string, so that this code may be easily reused for
//any MySQL table or any MySQL database on your server

//DEFINE SQL QUERY:
//you can use just about ANY kind of select statement you want - 
//edit this to suit your needs!


// switch column name if exporting the 'pm_post' table.
if ($table == "pm_post"){
	$userkey = "groupname";
}else{
	$userkey = "uemail";
}

// if column name exists, export it
 if ($table != ""){

  $DB_TBLName = "$table"; 
  $sql = "Select * from $DB_TBLName WHERE $userkey = '$userkey'";
 }else{
  $DB_TBLName = "$trk"; 
  $sql = "Select * from $DB_TBLName";  

 }


//Optional: print out title to top of Excel or Word file with Timestamp
//for when file was generated:
//set $Use_Titel = 1 to generate title, 0 not to use title
$Use_Title = 1;
//define date for title: EDIT this to create the time-format you need
$now_date = date('m-d-Y H:i');
//define title for .doc or .xls file: EDIT this if you want
$title = "Data from the '$ttype' for account #$userID. Exported from the XXXXXXXXXXXXX System on $now_date";


/*
Leave the connection info below as it is:
just edit the above.

(Editing of code past this point recommended only for advanced users.)
*/
//create MySQL connection
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
	or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
	or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
//execute query
$result = @mysql_query($sql,$Connect)
	or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());

//if this parameter is included ($w=1), file returned will be in word format ('.doc')
//if parameter is not included, file returned will be in excel format ('.xls')
if (isset($w) && ($w==1)){
$file_type = "msword";
$file_ending = "doc";
}
else {
$file_type = "vnd.ms-excel";
$file_ending = "xls";
}
//header info for browser: determines file type ('.doc' or '.xls')
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=$ttype.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");

/*
A NOTE ABOUT USING THIS SCRIPT WITH SSL:
==============================================
To get this script to work properly in MSIE,
do the following:

//delete this header:
header("Pragma: no-cache");

//and add these headers just after the "Expires: 0" header:
header("Keep-Alive: timeout=15, max=100");
header("Connection: Keep-Alive");		 
header("Transfer-Encoding: chunked");

Thanks to Christopher Owens for this!
==============================================
*/

/*	Start of Formatting for Word or Excel	*/

if (isset($w) && ($w==1)) //check for $w again
/*	FORMATTING FOR WORD DOCUMENTS ('.doc')   */
{
//create title with timestamp:
if ($Use_Title == 1){
echo("$title\n\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\n"; //new line character

	while($row = mysql_fetch_row($result))
	{
		//set_time_limit(60); // HaRa
		$schema_insert = "";
		for($j=0; $j<mysql_num_fields($result);$j++)
		{
		//define field names
		$field_name = mysql_field_name($result,$j);
		//will show name of fields
		$schema_insert .= "$field_name:\t";
			if(!isset($row[$j])) {
				$schema_insert .= "NULL".$sep;
				}
			elseif ($row[$j] != "") {
				$schema_insert .= "$row[$j]".$sep;
				}
			else {
				$schema_insert .= "".$sep;
				}
		}
		$schema_insert = str_replace($sep."$", "", $schema_insert);
		$schema_insert .= "\t";
		print(trim($schema_insert));
		//end of each mysql row
		//creates line to separate data from each MySQL table row
		print "\n----------------------------------------------------\n";
}
}
else
/*	FORMATTING FOR EXCEL DOCUMENTS ('.xls')   */
{
//create title with timestamp:
if ($Use_Title == 1){
echo("$title\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character

//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names

//start while loop to get data
/*
note: the following while-loop was taken from phpMyAdmin 2.1.0.
--from the file "lib.inc.php".
*/
	while($row = mysql_fetch_row($result))
	{
		//set_time_limit(60); // HaRa
		$schema_insert = "";
		for($j=0; $j<mysql_num_fields($result);$j++)
		{
			if(!isset($row[$j]))
				$schema_insert .= "NULL".$sep;
			elseif ($row[$j] != "")
				$schema_insert .= "$row[$j]".$sep;
			else
				$schema_insert .= "".$sep;
		}
		$schema_insert = str_replace($sep."$", "", $schema_insert);
		//following fix suggested by Josue (thanks, Josue!)
		//this corrects output in excel when table fields contain \n or \r
		//these two characters are now replaced with a space
		$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
		$schema_insert .= "\t";
		print(trim($schema_insert));
		print "\n";
	}
}
?>

[Mário Monteiro 179]

nao precisa e nao pode postar a mesma coisa em varios foruns

 

os seus outros topicos foram removidos por estarem em duplicidade

[Will Fernando 2]

sei la ... acho q pelo excelwriter fazendo com OO eh mais simples ... e funciona ^^

 

<?php
   //Incluir a classe excelwriter
   include("excelwriter.inc.php");
   //Você pode colocar aqui o nome do arquivo que você deseja salvar.
	$excel=new ExcelWriter("lista.xls");
	if($excel==false){
		echo $excel->error;
   }
   //Escreve o nome dos campos de uma tabela
   $myArr=array('CODIGO','NOME','NIVEL DE ACESSO');
   $excel->writeLine($myArr);
   //Seleciona os campos de uma tabela
	$conn = mysql_connect("localhost", "root", "") or die ('Não foi possivel conectar ao banco de dados! Erro: ' . mysql_error());
	if($conn)
	{
	mysql_select_db("empresa", $conn);
	}

   $consulta = "select * from usuarios order by permissao_usuario";
   //echo($consulta);
   $resultado = mysql_query($consulta);
   if($resultado==true){
	  while($linha = mysql_fetch_array($resultado)){
			//print_r($linha);
		 $myArr=array($linha['cod_usuario'],$linha['nome_usuario'],$linha['permissao_usuario']);
		 $excel->writeLine($myArr);
	  }
   }
	$excel->close();
	echo "O arquivo foi salvo com sucesso. <a href=\"lista.xls\">lista.xls</a>";
?>

valww espero ter ajudado

[studdiox 0]

E aee blza Fernando

Cara vou tentar mas tu consegue postar pra min aqui o outro cod do include ----- excelwriter.inc.php

ai vou testar e posto aqui se consegui ok

Valewwww

[studdiox 0]

Bom achei o aruivo excelwriter.inc.php na net ja tinha tetado ele antes so nao lembrava ele na primeira tela mosta assim

O arquivo foi salvo com sucesso. lista.xls sendo um link para baixar a lista.xls quando clico nela vai para a outra paguina

e da pagina nao encontrada !!!!! O pode ser pois como você mesmo disse " Da certo ..." entao entende se que você ja conceguiu fazer funcionar se puder me dar essa mao ai pra acharmos o erro agradeço muito ook

[edu_php 0]

EXPORTAR PARA EXCEL:

 

Arquivo "exportatoexcel.php"

 

<?php

 

class exporta_excel{

function arquivo($query,$nome_arquivo){

 

$registro= mysql_query($query);

 

$colunas = mysql_num_fields($registro);

 

$rows = mysql_num_rows($registro);

 

$date = date("d/m/Y");

 

 

header("Content-Type: application/force-download");

header('Content-Disposition: attachment; filename='.$nome_arquivo.'.xls');

header('Pragma: no-cache');

header('Expires: 0');

 

$table.="<table border='1'>";

$table.="<caption> Listagem de Cliente - $date</caption>";

 

$table.="<tr>";

 

for($i=0; $i < $colunas ; $i++){

$nome_coluna= mysql_field_name($registro,$i);

 

$table.="<th>".strtoupper($nome_coluna)."</th>";

}

 

$table.="</tr>";

 

while($data= mysql_fetch_array($registro,MYSQL_NUM)){

 

$table.="<tr>";

 

for($j=0;$j<$colunas;$j++){

 

$table.= "<td>".strtoupper($data[$j])."</font>";

}

$table.="</tr>";

}

 

$table.="</table>";

 

print "$table";

}

}

 

?>

 

 

 

 

Arquivo "index.php"

 

<?php

 

//inclua o arquivo (exportatoexcel.php) que tem a classe exporta_excel

include_once('exportatoexcel.php');

 

//crie ou inclua a conexao com o mysql

$conexao = mysql_connect("localhost","root","");

 

//selecione o banco de dados

mysql_db_query("edu_db",$conexao);

 

//Crie a query para buscar os dados

$query_busca="select * from noticias";

 

//crie uma variavel para chamar a class

$exporta= new exporta_excel;

 

//com a variavel criada use a função "arquivo" e informe a query e o nome da tabela que a query consultou

$exporta->arquivos($query_busca,'noticias');

 

?>

 

 

At++