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
<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; //*** Width & Height (A1:A1) ***// $xlApp->ActiveSheet->Range("A1:A1")->ColumnWidth = 10.0; $xlApp->ActiveSheet->Range("B1:B1")->ColumnWidth = 13.0; $xlApp->ActiveSheet->Range("C1:C1")->ColumnWidth = 23.0; $xlApp->ActiveSheet->Range("D1:D1")->ColumnWidth = 12.0; $xlApp->ActiveSheet->Range("E1:E1")->ColumnWidth = 13.0; $xlApp->ActiveSheet->Range("F1:F1")->ColumnWidth = 12.0; //*** Report Title ***// $xlApp->ActiveSheet->Range("A1:F1")->BORDERS->Weight = 1; $xlApp->ActiveSheet->Range("A1:F1")->MergeCells = True; $xlApp->ActiveSheet->Range("A1:F1")->Font->Bold = True; $xlApp->ActiveSheet->Range("A1:F1")->Font->Size = 20; $xlApp->ActiveSheet->Range("A1:F1")->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(1,1)->Value = "Customer Report"; //*** Header ***// $xlApp->ActiveSheet->Cells(3,1)->Value = "CustomerID"; $xlApp->ActiveSheet->Cells(3,1)->Font->Bold = True; $xlApp->ActiveSheet->Cells(3,1)->VerticalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,1)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,1)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells(3,2)->Value = "Name"; $xlApp->ActiveSheet->Cells(3,2)->Font->Bold = True; $xlApp->ActiveSheet->Cells(3,2)->VerticalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,2)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,2)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells(3,3)->Value = "Email"; $xlApp->ActiveSheet->Cells(3,3)->Font->Bold = True; $xlApp->ActiveSheet->Cells(3,3)->VerticalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,3)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,3)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells(3,4)->Value = "CountryCode"; $xlApp->ActiveSheet->Cells(3,4)->Font->Bold = True; $xlApp->ActiveSheet->Cells(3,4)->VerticalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,4)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,4)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells(3,5)->Value = "Budget"; $xlApp->ActiveSheet->Cells(3,5)->Font->Bold = True; $xlApp->ActiveSheet->Cells(3,5)->VerticalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,5)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,5)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells(3,6)->Value = "Used"; $xlApp->ActiveSheet->Cells(3,6)->Font->Bold = True; $xlApp->ActiveSheet->Cells(3,6)->VerticalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,6)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,6)->BORDERS->Weight = 1; //***********// $intRows = 4; while($objResult = mysql_fetch_array($objQuery)) { //*** Detail ***// $xlApp->ActiveSheet->Cells($intRows,1)->Value = $objResult["CustomerID"]; $xlApp->ActiveSheet->Cells($intRows,1)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells($intRows,1)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells($intRows,2)->Value = $objResult["Name"]; $xlApp->ActiveSheet->Cells($intRows,2)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells($intRows,3)->Value = $objResult["Email"]; $xlApp->ActiveSheet->Cells($intRows,3)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells($intRows,4)->Value = $objResult["CountryCode"]; $xlApp->ActiveSheet->Cells($intRows,4)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells($intRows,4)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells($intRows,5)->Value = $objResult["Budget"]; $xlApp->ActiveSheet->Cells($intRows,5)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells($intRows,5)->NumberFormat = "$#,##0.00"; $xlApp->ActiveSheet->Cells($intRows,6)->Value = $objResult["Used"]; $xlApp->ActiveSheet->Cells($intRows,6)->BORDERS->Weight = 1; $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_excel_export.php
Screenshot
3efficiency…
…