Nirutechnolabs

Learning & Integrating web technology and code help directory

How to use PHP & Chart/Graph (Excel.Application)

Unknown 01:44 No comments
How to use PHP & Chart/Graph  (Excel.Application) To use Excel.Application. Will need to install Microsoft Excel and is compatible with Windows Server only
Screenshot
PHP & Excel.Application
And check the status of the right to access to DCOM. If the problem please read the article
php_chart_excel1.php

  1. <html>  
  2. <head>  
  3. <title>ShotDev.Com Tutorial</title>  
  4. </head>  
  5. <body>  
  6. <?  
  7. //*** Connect to MySQL Database ***//  
  8. $objConnect = mysql_connect("localhost","root","root") or die(mysql_error());  
  9. $objDB = mysql_select_db("mydatabase");  
  10. $strSQL = "SELECT * FROM customer";  
  11. $objQuery = mysql_query($strSQL);  
  12. if($objQuery)  
  13. {  
  14. //*** Get Document Path ***//  
  15. $strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"]))); // C:/AppServ/www/myphp  
  16.   
  17. //*** File Name Gif,Jpeg,... ***//  
  18. $FileName = "MyXls/MyChart.xls";  
  19.   
  20. //*** Connect to Excel.Application ***//  
  21. $xlApp = new COM("Excel.Application");  
  22. $xlBook = $xlApp->Workbooks->Add();  
  23.   
  24. $intStartRows = 3;  
  25. $intEndRows = mysql_num_rows($objQuery)+($intStartRows-1);  
  26.   
  27. $xlSheet = $xlBook->Worksheets(1);  
  28.   
  29. $xlApp->Application->Visible = False;  
  30.   
  31. //*** Delete Sheet (2,3) - Sheet Default ***//  
  32. $xlBook->Worksheets(2)->Select;  
  33. $xlBook->Worksheets(2)->Delete;  
  34. $xlBook->Worksheets(2)->Select;  
  35. $xlBook->Worksheets(2)->Delete;  
  36.   
  37. //*** Sheet Data Rows ***//  
  38. $xlBook->Worksheets(1)->Name = "MyReport";  
  39. $xlBook->Worksheets(1)->Select;  
  40.   
  41. $xlBook->ActiveSheet->Cells(1,1)->Value = "My Customer";  
  42. $xlBook->ActiveSheet->Cells(1,1)->Font->Bold = True;  
  43. $xlBook->ActiveSheet->Cells(1,1)->Font->Name = "Tahoma";  
  44. $xlBook->ActiveSheet->Cells(1,1)->Font->Size = 16;  
  45.   
  46. $xlBook->ActiveSheet->Cells(2,1)->Value = "Customer Name";  
  47. $xlBook->ActiveSheet->Cells(2,1)->Font->Name = "Tahoma";  
  48. $xlBook->ActiveSheet->Cells(2,1)->BORDERS->Weight = 1;  
  49. $xlBook->ActiveSheet->Cells(2,1)->Font->Size = 10;  
  50. $xlBook->ActiveSheet->Cells(2,1)->MergeCells = True;  
  51.   
  52. $xlBook->ActiveSheet->Cells(2,2)->Value = "Used";  
  53. $xlBook->ActiveSheet->Cells(2,2)->BORDERS->Weight = 1;  
  54. $xlBook->ActiveSheet->Cells(2,2)->Font->Name = "Tahoma";  
  55. $xlBook->ActiveSheet->Cells(2,2)->Font->Size = 10;  
  56. $xlBook->ActiveSheet->Cells(2,2)->MergeCells = True;  
  57.   
  58. //*** Data Rows ***//  
  59. $i = 0;  
  60. While($result = mysql_fetch_array($objQuery))  
  61. {  
  62. $xlBook->ActiveSheet->Cells($intStartRows+$i,1)->Value = $result["Name"];  
  63. $xlBook->ActiveSheet->Cells($intStartRows+$i,2)->Value = $result["Used"];  
  64. $xlBook->ActiveSheet->Cells($intStartRows+$i,2)->NumberFormat = "$#,##0.00";  
  65. $i++;  
  66. }  
  67. //*** End Data Rows ***//  
  68.   
  69. //*** Creating Chart ***//  
  70. $xlBook->Charts->Add;  
  71. $xlBook->ActiveChart->Name = "MyChart";  
  72. $xlBook->ActiveChart->ChartType = 54;  
  73. $xlBook->ActiveChart->SetSourceData ($xlBook->Sheets("MyReport")->Range("A".$intStartRows.":B".$intEndRows.""));  
  74.   
  75. //*** Sheet Properties ***//  
  76. $xlBook->ActiveChart->HasTitle = True;  
  77. $xlBook->ActiveChart->ChartTitle->Characters->Text = "My Chart";  
  78.   
  79. //*** Save Excel ***//  
  80. @unlink($strPath."/".$FileName);  
  81. $xlBook->SaveAs($strPath."/".$FileName);  
  82.   
  83. $xlApp->Application->Quit;  
  84. }  
  85. ?>  
  86. Excel Created <a href="<?=$FileName?>">Click here</a> to Download.  
  87. </body>  
  88. </html>  
Create a php file and save to path root-path/myphp/
Screenshot
PHP & Chart/Graph (Excel.Application)
.
.
Example 2
php_chart_excel2.php


  1. <html>  
  2. <head>  
  3. <title>ShotDev.Com Tutorial</title>  
  4. </head>  
  5. <body>  
  6. <?  
  7. //*** Connect to MySQL Database ***//  
  8. $objConnect = mysql_connect("localhost","root","root") or die(mysql_error());  
  9. $objDB = mysql_select_db("mydatabase");  
  10. $strSQL = "SELECT * FROM customer";  
  11. $objQuery = mysql_query($strSQL);  
  12. if($objQuery)  
  13. {  
  14. //*** Get Document Path ***//  
  15. $strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"]))); // C:/AppServ/www/myphp  
  16.   
  17. //*** File Name Gif,Jpeg,... ***//  
  18. $FileName = "MyXls/MyChart.xls";  
  19.   
  20. //*** Connect to Excel.Application ***//  
  21. $xlApp = new COM("Excel.Application");  
  22. $xlBook = $xlApp->Workbooks->Add();  
  23.   
  24. $intStartRows = 3;  
  25. $intEndRows = mysql_num_rows($objQuery)+($intStartRows-1);  
  26.   
  27. $xlSheet = $xlBook->Worksheets(1);  
  28.   
  29. $xlApp->Application->Visible = False;  
  30.   
  31. //*** Delete Sheet (2,3) - Sheet Default ***//  
  32. $xlBook->Worksheets(2)->Select;  
  33. $xlBook->Worksheets(2)->Delete;  
  34. $xlBook->Worksheets(2)->Select;  
  35. $xlBook->Worksheets(2)->Delete;  
  36.   
  37. //*** Sheet Data Rows ***//  
  38. $xlBook->Worksheets(1)->Name = "MyReport";  
  39. $xlBook->Worksheets(1)->Select;  
  40.   
  41. $xlBook->ActiveSheet->Cells(1,1)->Value = "My Customer";  
  42. $xlBook->ActiveSheet->Cells(1,1)->Font->Bold = True;  
  43. $xlBook->ActiveSheet->Cells(1,1)->Font->Name = "Tahoma";  
  44. $xlBook->ActiveSheet->Cells(1,1)->Font->Size = 16;  
  45.   
  46. $xlBook->ActiveSheet->Cells(2,1)->Value = "Customer Name";  
  47. $xlBook->ActiveSheet->Cells(2,1)->Font->Name = "Tahoma";  
  48. $xlBook->ActiveSheet->Cells(2,1)->BORDERS->Weight = 1;  
  49. $xlBook->ActiveSheet->Cells(2,1)->Font->Size = 10;  
  50. $xlBook->ActiveSheet->Cells(2,1)->MergeCells = True;  
  51.   
  52. $xlBook->ActiveSheet->Cells(2,2)->Value = "Used";  
  53. $xlBook->ActiveSheet->Cells(2,2)->BORDERS->Weight = 1;  
  54. $xlBook->ActiveSheet->Cells(2,2)->Font->Name = "Tahoma";  
  55. $xlBook->ActiveSheet->Cells(2,2)->Font->Size = 10;  
  56. $xlBook->ActiveSheet->Cells(2,2)->MergeCells = True;  
  57.   
  58. $i = 0;  
  59. While($result = mysql_fetch_array($objQuery))  
  60. {  
  61. $xlBook->ActiveSheet->Cells($intStartRows+$i,1)->Value = $result["Name"];  
  62. $xlBook->ActiveSheet->Cells($intStartRows+$i,2)->Value = $result["Used"];  
  63. $xlBook->ActiveSheet->Cells($intStartRows+$i,2)->NumberFormat = "$#,##0.00";  
  64. $i++;  
  65. }  
  66. //*** End Data Rows ***//  
  67.   
  68. //*** Creating Chart ***//  
  69. $xlBook->Charts->Add;  
  70. $xlBook->ActiveChart->ChartType = 54;  
  71. $xlBook->ActiveChart->SetSourceData ($xlBook->Sheets("MyReport")->Range("A".$intStartRows.":B".$intEndRows.""));  
  72.   
  73. //*** Set Localtion Sheet ***//  
  74. $xlBook->ActiveChart->Location(2,"MyReport");  
  75.   
  76. //*** Sheet Properties ***//  
  77. $xlBook->ActiveChart->HasTitle = True;  
  78. $xlBook->ActiveChart->ChartTitle->Characters->Text = "My Chart";  
  79.   
  80. //*** Location  ***//  
  81. $xlBook->ActiveSheet->Shapes("Chart 1")->IncrementLeft(20);  
  82. $xlBook->ActiveSheet->Shapes("Chart 1")->IncrementTop(-97.5);  
  83.   
  84. //'*** Set Height & Width ***'  
  85. $xlBook->ActiveSheet->Shapes("Chart 1")->ScaleHeight(1.0, 0,0);  
  86. $xlBook->ActiveSheet->Shapes("Chart 1")->ScaleWidth(1.0, 0,0);  
  87.   
  88. //*** Save Excel ***//  
  89. @unlink($strPath."/".$FileName);  
  90. $xlBook->SaveAs($strPath."/".$FileName);  
  91. //$xlBook->SaveAs(realpath($FileName));  
  92.   
  93. $xlApp->Application->Quit;  
  94. }  
  95. ?>  
  96. Excel Created <a href="<?=$FileName?>">Click here</a> to Download.  
  97.   
  98. </body>  
  99. </html>  
Create a php file and save to path root-path/myphp/
Screenshot
PHP & Chart/Graph (Excel.Application)
.
.

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )