Nirutechnolabs

Learning & Integrating web technology and code help directory

How to use PHP & Export Data from Database to Excel (Excel.Application)

Unknown 01:36 No comments
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

  1. <html>  
  2. <head>  
  3. <title>ShotDev.Com Tutorial</title>  
  4. </head>  
  5. <body>  
  6. <?  
  7. $objConnect = mysql_connect("localhost","root","root") or die(mysql_error());  
  8. $objDB = mysql_select_db("mydatabase");  
  9. $strSQL = "SELECT * FROM customer";  
  10. $objQuery = mysql_query($strSQL);  
  11. if($objQuery)  
  12. {  
  13. //*** Get Document Path ***//  
  14. $strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"]))); // C:/AppServ/www/myphp  
  15.   
  16. //*** Excel Document Root ***//  
  17. $strFileName = "MyXls/MyExcel.xls";  
  18.   
  19. //*** Connect to Excel.Application ***//  
  20. $xlApp = new COM("Excel.Application");  
  21. $xlBook = $xlApp->Workbooks->Add();  
  22.   
  23. //*** Create Sheet 1 ***//  
  24. $xlBook->Worksheets(1)->Name = "My Customer";  
  25. $xlBook->Worksheets(1)->Select;  
  26.   
  27. //*** Header ***//  
  28. $xlApp->ActiveSheet->Cells(1,1)->Value = "CustomerID";  
  29. $xlApp->ActiveSheet->Cells(1,2)->Value = "Name";  
  30. $xlApp->ActiveSheet->Cells(1,3)->Value = "Email";  
  31. $xlApp->ActiveSheet->Cells(1,4)->Value = "CountryCode";  
  32. $xlApp->ActiveSheet->Cells(1,5)->Value = "Budget";  
  33. $xlApp->ActiveSheet->Cells(1,6)->Value = "Used";  
  34. //***********//  
  35.   
  36. $intRows = 2;  
  37. while($objResult = mysql_fetch_array($objQuery))  
  38. {  
  39. //*** Detail ***//  
  40. $xlApp->ActiveSheet->Cells($intRows,1)->Value = $objResult["CustomerID"];  
  41. $xlApp->ActiveSheet->Cells($intRows,2)->Value = $objResult["Name"];  
  42. $xlApp->ActiveSheet->Cells($intRows,3)->Value = $objResult["Email"];  
  43. $xlApp->ActiveSheet->Cells($intRows,4)->Value = $objResult["CountryCode"];  
  44. $xlApp->ActiveSheet->Cells($intRows,5)->Value = $objResult["Budget"];  
  45. $xlApp->ActiveSheet->Cells($intRows,6)->Value = $objResult["Used"];  
  46. $intRows++;  
  47. }  
  48.   
  49. @unlink($strFileName); //*** Delete old files ***//  
  50.   
  51. $xlBook->SaveAs($strPath."/".$strFileName); //*** Save to Path ***//  
  52. //$xlBook->SaveAs(realpath($strFileName)); //*** Save to Path ***//  
  53.   
  54. //*** Close & Quit ***//  
  55. $xlApp->Application->Quit();  
  56. $xlApp = null;  
  57. $xlBook = null;  
  58. $xlSheet1 = null;  
  59.   
  60. }  
  61.   
  62. mysql_close($objConnect);  
  63. ?>  
  64. Excel Created <a href="<?=$strFileName?>">Click here</a> to Download.  
  65. </body>  
  66. </html>  
Create a php file and save to path root-path/myphp/
Screenshot
PHP & Add Style & Export Data from Database to Excel

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )