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

VN:F [1.9.22_1171]
Rating: 7.5/10 (2 votes cast)

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.
Prerequisites
Please download the PEAR excel for PHP
http://pear.php.net/package/Spreadsheet_Excel_Writer/redirected

chdir('phpxls');
require_once 'Writer.php';
chdir('..');
$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");
$name="_report";
$filename="$file$name.xls";
$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();
$format_bold->setBold();
$format_bold->setSize(12);
$format_bold->setFgColor('blue');
$worksheet =& $workbook->addWorksheet('My Sample WorkSheet');
$radius = 20;
$worksheet->setColumn(0,$radius*2,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);
$i=1;
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]");
$i++;
}
$sheet1='Calendar';
$workbook->send($filename);
$workbook->close();
VN:F [1.9.22_1171]
Rating: 7.5/10 (2 votes cast)

Leave a Reply

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