The Wahyudiharto's Blog

Is It Possible Exporting Database To Excel From MySQL?

Friday, September 12, 2008

The answer is absolutely yes, it is possible to do that. This problem faced me when I had to solve how to move some group of values from a table into an excel sheet on one of my earlier web based project couple weeks ago.This article would describe you step by step on how to make it done.

First of all, let’s assumed that you have a compact table just like my example below. It is only contains 2 column (kd_bank & nm_bank) and let call the table with tm_bank.

Then, create a PHP project from your favorite editor. Take a look on complete code below:

<?
$link=mysql_connect("your_db_host","your_db_user","your_db_password");
mysql_select_db("your_db_name");
// Functions for exporting to excel.
function xlsBOF()
{
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
function xlsEOF()
{
echo pack("ss", 0x0A, 0x00);
return;
}
function xlsWriteNumber($Row, $Col, $Value)
{
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}
function xlsWriteLabel($Row, $Col, $Value )
{
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=mysql2xls.xls");
header("Content-Transfer-Encoding: binary ");

xlsBOF();
xlsWriteLabel(0,0,"Hasil Export Excel:");
// Make column labels. (at line 3)
xlsWriteLabel(2,0,"Kode Bank");
xlsWriteLabel(2,1,"Nama Bank");
$xlsRow = 3;
$perintah="select * from tm_bank order by nm_bank";
$hasil=mysql_query($perintah);
while($row=mysql_fetch_array($hasil))
{
$kd_bank=$row["kd_bank"];
$nm_bank=$row["nm_bank"];

xlsWriteLabel($xlsRow,0,$kd_bank);
xlsWriteLabel($xlsRow,1,$nm_bank);
$xlsRow++;
}
xlsEOF();
?>
<html>
<head>
<title>MySQL2XLS</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
Hit F5 To Re-Download the XLS
</body>
</html>