Is It Possible Exporting Database To Excel From MySQL?
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>
Finally, save the code & execute it from browser.
The script will automatically opening a download window consist of a file (mysql2xls.xls). You may select Open option & see what will appear next.
Voila, an excel sheet will shown up where all the value from MySQL are transferred into it. From here, do as you like to modify. Good day.
| PS: If you've benefit from this blog, you can support it by making a small contribution. |
Sunday, September 14, 2008 9:29:00 AM
Assalamu'alaikum Wr.Wb
Gimana kabarnya ko? kirain ninggalin jogja udah berenti coding, ternyata masih conding juga :D Tambah keren pula heheheheh..
Ni aku, Agus (mungkin dah lupa kau) yg dulu satu rumah ma David (abangnya arman).
Tak cari2in di jogja gak nemu2, padahal pengen berbagi proyek :D, soale aku dah total berenti coding euy.. malah tertarik ke dunia jualan wakakaka.. ntar klo OL, YM ya.. aku dah add ID mu.
O iya.. di cariin Mas Agus (AKPRIND) juga tuh ;)) peace
Saturday, July 13, 2013 4:53:00 PM
in a field have more text so when i genereate excel from db then open it will be show error "data may have been lost"