Export mysql tables to excel using php

Last updated on February 1st, 2016 at 01:24 am

Export mysql tables to excel using php xls pear spreadsheet Excel Writer, This is very easy and flexible. Other techniques of creating excel sheet may work with Openoffice but will not work mostly with Microsoft excel viewer. So this approach will be a work around for those who are not able to create excel sheet using the usual method of adding headers.
Here i am going to show you a sample code that generate the complete report from MYSQL database.
Please download the PEAR excel for PHP

require_once 'Writer.php';
$objConnect = mysql_connect($host,$username,$password) or die("Error Connecting to Database, <hr />Known issues:Database may be down or need to contact Michael [[email protected]]");
$file = date("Y-m-d-H_i_s");
$objDB = mysql_select_db("mydb");
$strSQL = "SELECT * FROM trans_db  ORDER BY Id";
$result = mysql_query($strSQL);
$workbook = new Spreadsheet_Excel_Writer();
$format_bold =& $workbook->addFormat();
$worksheet =& $workbook->addWorksheet('My Sample WorkSheet');
$radius = 20;
$worksheet->write(0, 0, "Transaction Name", $format_bold);
$worksheet->write(0, 1, "Account Name", $format_bold);
$worksheet->write(0, 2, "Date", $format_bold);
$worksheet->write(0, 3, "GEO", $format_bold);
$worksheet->write(0, 4, "Bank", $format_bold);
$worksheet->write(0, 5, "Amnt Transferred", $format_bold);
$worksheet->write(0, 6, "Time", $format_bold);
$worksheet->write(0, 7, "Ref Num", $format_bold);
$worksheet->write(0, 8, "Remarks", $format_bold);
while($row = mysql_fetch_array($result)){
$worksheet->write($i, 0, "$row[Transaction_Name]");
$worksheet->write($i, 1, "$row[Account_Name]");
$worksheet->write($i, 2, "$row[Date]");
$worksheet->write($i, 3, "$row[geo]");
$worksheet->write($i, 4, "$row[bank]");
$worksheet->write($i, 5, "$row[amnt_trans]");
$worksheet->write($i, 6, "$row[time]");
$worksheet->write($i, 7, "$row[ref_num]");
$worksheet->write($i, 8, "$row[remarks]");
Click to rate this tutorial!
[Total: 2 Average: 4]

Leave a Reply

Your email address will not be published. Required fields are marked *