PHP: Exporting MySQL data to Excel

Following code snippet would explain how to export data from MySQL database to Excel spread sheet.

SQL query is used to fetch the desired data from the MySQL database. Then using PHP, HTML table is constructed for each data element pulled from the resulting SQL output array. Each row from the result array is converted to an HTML table row and individual fields from each row are moved to HTML table column. This HTML table constructed is then stored as excel spread sheet.

That’s pretty much it !!

ExportToExcel.class.php

<?php

class ExportToExcel
{  
function exportWithPage($php_page,$excel_file_name)  
{  
$this->setHeader($excel_file_name);   require_once "$php_page";   
}    

function setHeader($excel_file_name)  
{   
header("Content-type: application/octet-stream");  
header("Content-Disposition: attachment;
filename=$excel_file_name");  
header("Pragma: no-cache");  
header("Expires: 0");    
}    

function exportWithQuery($qry,$excel_file_name,$conn)  
{  
$tmprst=mysql_query($qry,$conn);  
$header="<center><table border=1px><th>Exported Data</th>";  
$num_field=mysql_num_fields($tmprst);  
$body="";  
while($row=mysql_fetch_array($tmprst,MYSQL_BOTH))  
{    $body.="<tr>";   
for($i=0;$i<$num_field;$i++)   
{     $body.="<td>".$row[$i]."</td>";    }   
$body.="</tr>";   
}  
$this->setHeader($excel_file_name);  
echo $header.$body."</table>";  
}
} ?>

dabasecon.php

<?php $conn=mysql_connect('localhost','DBUserID','DBPassword')or die('Sorry Could not make connection');
mysql_select_db('DBName'); ?>

testclass.php

<?PHP include_once('ExportToExcel.class.php'); include_once('dabasecon.php'); $exp=new ExportToExcel();

$qry="select * from YourTableName"; $exp->exportWithQuery($qry,"ExportData.xls",$conn);

?>