Nirutechnolabs

Learning & Integrating web technology and code help directory

How to use PHP & Export Data from Database to Excel and Report/Print Format (Excel.Application)

Unknown 01:38 1 comment
How to use PHP & Export Data from Database to Excel and Report/Print Format (Excel.Application)The Learn / tutorial php programming how to using  PHP Export Data from Database to Excel and Report/Print Format.
ShotDev Focus:
- PHP  & Export Data from Database to Excel and Report/Print Format.
Example
php_excel_export.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. //*** Width & Height (A1:A1) ***//  
  28. $xlApp->ActiveSheet->Range("A1:A1")->ColumnWidth = 10.0;  
  29. $xlApp->ActiveSheet->Range("B1:B1")->ColumnWidth = 13.0;  
  30. $xlApp->ActiveSheet->Range("C1:C1")->ColumnWidth = 23.0;  
  31. $xlApp->ActiveSheet->Range("D1:D1")->ColumnWidth = 12.0;  
  32. $xlApp->ActiveSheet->Range("E1:E1")->ColumnWidth = 13.0;  
  33. $xlApp->ActiveSheet->Range("F1:F1")->ColumnWidth = 12.0;  
  34.   
  35. //*** Report Title ***//  
  36. $xlApp->ActiveSheet->Range("A1:F1")->BORDERS->Weight = 1;  
  37. $xlApp->ActiveSheet->Range("A1:F1")->MergeCells = True;  
  38. $xlApp->ActiveSheet->Range("A1:F1")->Font->Bold = True;  
  39. $xlApp->ActiveSheet->Range("A1:F1")->Font->Size = 20;  
  40. $xlApp->ActiveSheet->Range("A1:F1")->HorizontalAlignment = -4108;  
  41. $xlApp->ActiveSheet->Cells(1,1)->Value = "Customer Report";  
  42.   
  43. //*** Header ***//  
  44. $xlApp->ActiveSheet->Cells(3,1)->Value = "CustomerID";  
  45. $xlApp->ActiveSheet->Cells(3,1)->Font->Bold = True;  
  46. $xlApp->ActiveSheet->Cells(3,1)->VerticalAlignment = -4108;  
  47. $xlApp->ActiveSheet->Cells(3,1)->HorizontalAlignment = -4108;  
  48. $xlApp->ActiveSheet->Cells(3,1)->BORDERS->Weight = 1;  
  49.   
  50. $xlApp->ActiveSheet->Cells(3,2)->Value = "Name";  
  51. $xlApp->ActiveSheet->Cells(3,2)->Font->Bold = True;  
  52. $xlApp->ActiveSheet->Cells(3,2)->VerticalAlignment = -4108;  
  53. $xlApp->ActiveSheet->Cells(3,2)->HorizontalAlignment = -4108;  
  54. $xlApp->ActiveSheet->Cells(3,2)->BORDERS->Weight = 1;  
  55.   
  56. $xlApp->ActiveSheet->Cells(3,3)->Value = "Email";  
  57. $xlApp->ActiveSheet->Cells(3,3)->Font->Bold = True;  
  58. $xlApp->ActiveSheet->Cells(3,3)->VerticalAlignment = -4108;  
  59. $xlApp->ActiveSheet->Cells(3,3)->HorizontalAlignment = -4108;  
  60. $xlApp->ActiveSheet->Cells(3,3)->BORDERS->Weight = 1;  
  61.   
  62. $xlApp->ActiveSheet->Cells(3,4)->Value = "CountryCode";  
  63. $xlApp->ActiveSheet->Cells(3,4)->Font->Bold = True;  
  64. $xlApp->ActiveSheet->Cells(3,4)->VerticalAlignment = -4108;  
  65. $xlApp->ActiveSheet->Cells(3,4)->HorizontalAlignment = -4108;  
  66. $xlApp->ActiveSheet->Cells(3,4)->BORDERS->Weight = 1;  
  67.   
  68. $xlApp->ActiveSheet->Cells(3,5)->Value = "Budget";  
  69. $xlApp->ActiveSheet->Cells(3,5)->Font->Bold = True;  
  70. $xlApp->ActiveSheet->Cells(3,5)->VerticalAlignment = -4108;  
  71. $xlApp->ActiveSheet->Cells(3,5)->HorizontalAlignment = -4108;  
  72. $xlApp->ActiveSheet->Cells(3,5)->BORDERS->Weight = 1;  
  73.   
  74. $xlApp->ActiveSheet->Cells(3,6)->Value = "Used";  
  75. $xlApp->ActiveSheet->Cells(3,6)->Font->Bold = True;  
  76. $xlApp->ActiveSheet->Cells(3,6)->VerticalAlignment = -4108;  
  77. $xlApp->ActiveSheet->Cells(3,6)->HorizontalAlignment = -4108;  
  78. $xlApp->ActiveSheet->Cells(3,6)->BORDERS->Weight = 1;  
  79.   
  80. //***********//  
  81.   
  82. $intRows = 4;  
  83. while($objResult = mysql_fetch_array($objQuery))  
  84. {  
  85.   
  86. //*** Detail ***//  
  87. $xlApp->ActiveSheet->Cells($intRows,1)->Value = $objResult["CustomerID"];  
  88. $xlApp->ActiveSheet->Cells($intRows,1)->BORDERS->Weight = 1;  
  89. $xlApp->ActiveSheet->Cells($intRows,1)->HorizontalAlignment = -4108;  
  90.   
  91. $xlApp->ActiveSheet->Cells($intRows,2)->Value = $objResult["Name"];  
  92. $xlApp->ActiveSheet->Cells($intRows,2)->BORDERS->Weight = 1;  
  93.   
  94. $xlApp->ActiveSheet->Cells($intRows,3)->Value = $objResult["Email"];  
  95. $xlApp->ActiveSheet->Cells($intRows,3)->BORDERS->Weight = 1;  
  96.   
  97. $xlApp->ActiveSheet->Cells($intRows,4)->Value = $objResult["CountryCode"];  
  98. $xlApp->ActiveSheet->Cells($intRows,4)->HorizontalAlignment = -4108;  
  99. $xlApp->ActiveSheet->Cells($intRows,4)->BORDERS->Weight = 1;  
  100.   
  101. $xlApp->ActiveSheet->Cells($intRows,5)->Value = $objResult["Budget"];  
  102. $xlApp->ActiveSheet->Cells($intRows,5)->BORDERS->Weight = 1;  
  103. $xlApp->ActiveSheet->Cells($intRows,5)->NumberFormat = "$#,##0.00";  
  104.   
  105. $xlApp->ActiveSheet->Cells($intRows,6)->Value = $objResult["Used"];  
  106. $xlApp->ActiveSheet->Cells($intRows,6)->BORDERS->Weight = 1;  
  107.   
  108. $intRows++;  
  109. }  
  110.   
  111. @unlink($strFileName); //*** Delete old files ***//  
  112.   
  113. $xlBook->SaveAs($strPath."/".$strFileName); //*** Save to Path ***//  
  114. //$xlBook->SaveAs(realpath($strFileName)); //*** Save to Path ***//  
  115.   
  116. //*** Close & Quit ***//  
  117. $xlApp->Application->Quit();  
  118. $xlApp = null;  
  119. $xlBook = null;  
  120. $xlSheet1 = null;  
  121.   
  122. }  
  123. mysql_close($objConnect);  
  124. ?>  
  125. Excel Created <a href="<?=$strFileName?>">Click here</a> to Download.  
  126. </body>  
  127. </html>  
Create a php file and save to path root-path/myphp/
Screenshot
PHP & Add Style & Export Data from Database to Excel and Report/Print Format
.

1 comment :

  1. Unknown6 September 2018 at 05:17

    Fatal error: Uncaught Error: Class 'COM' not found in C:\xampp\htdocs\Admin_New_29_08_2018\test3.php:20 Stack trace: #0 {main} thrown in C:\xampp\htdocs\Admin_New_29_08_2018\test3.php on line 20

    ReplyDelete

Subscribe to: Post Comments ( Atom )