How to use PHP & Export Data from Database to Excel (Excel.Application) The Learn / tutorial php programming how to using PHP Export Data from Database to Excel.
ShotDev Focus:
- PHP & Export Data from Database to Excel.
Example
php_database_excel.php
<html> <head> <title>ShotDev.Com Tutorial</title> </head> <body> <? $objConnect = mysql_connect("localhost","root","root") or die(mysql_error()); $objDB = mysql_select_db("mydatabase"); $strSQL = "SELECT * FROM customer"; $objQuery = mysql_query($strSQL); if($objQuery) { //*** Get Document Path ***// $strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"]))); // C:/AppServ/www/myphp //*** Excel Document Root ***// $strFileName = "MyXls/MyExcel.xls"; //*** Connect to Excel.Application ***// $xlApp = new COM("Excel.Application"); $xlBook = $xlApp->Workbooks->Add(); //*** Create Sheet 1 ***// $xlBook->Worksheets(1)->Name = "My Customer"; $xlBook->Worksheets(1)->Select; //*** Header ***// $xlApp->ActiveSheet->Cells(1,1)->Value = "CustomerID"; $xlApp->ActiveSheet->Cells(1,2)->Value = "Name"; $xlApp->ActiveSheet->Cells(1,3)->Value = "Email"; $xlApp->ActiveSheet->Cells(1,4)->Value = "CountryCode"; $xlApp->ActiveSheet->Cells(1,5)->Value = "Budget"; $xlApp->ActiveSheet->Cells(1,6)->Value = "Used"; //***********// $intRows = 2; while($objResult = mysql_fetch_array($objQuery)) { //*** Detail ***// $xlApp->ActiveSheet->Cells($intRows,1)->Value = $objResult["CustomerID"]; $xlApp->ActiveSheet->Cells($intRows,2)->Value = $objResult["Name"]; $xlApp->ActiveSheet->Cells($intRows,3)->Value = $objResult["Email"]; $xlApp->ActiveSheet->Cells($intRows,4)->Value = $objResult["CountryCode"]; $xlApp->ActiveSheet->Cells($intRows,5)->Value = $objResult["Budget"]; $xlApp->ActiveSheet->Cells($intRows,6)->Value = $objResult["Used"]; $intRows++; } @unlink($strFileName); //*** Delete old files ***// $xlBook->SaveAs($strPath."/".$strFileName); //*** Save to Path ***// //$xlBook->SaveAs(realpath($strFileName)); //*** Save to Path ***// //*** Close & Quit ***// $xlApp->Application->Quit(); $xlApp = null; $xlBook = null; $xlSheet1 = null; } mysql_close($objConnect); ?> Excel Created <a href="<?=$strFileName?>">Click here</a> to Download. </body> </html>
Create a php file and save to path root-path/myphp/
Run
http://localhost/myphp/php_database_excel.php
Screenshot