PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites

15 Jul 2014

Create excel spreadsheet using PHP and Mysql

                       You can generate excel spreadsheets easily using PHP. You need to follow below steps to create excel file.

Add content type:


                    First you need to add excel sheet content type to create excel file using PHP.

header("Content-Type: application/vnd.ms-excel");

Add name and download excel file:


                    You can provide name for excel spreadsheet and make your sheet as downloadable file on coding. The Content-Disposition is used to force browser to save a file. For that, your file should be attached following line:

header("Content-disposition: attachment; filename=spreadsheet.xls");

Add content to excel spreadsheet:


                   Finally you can add contents to your excel file. Consider the following PHP script for generate excel file:

<?php
header("Content-Type: application/vnd.ms-excel");
header("Content-disposition: attachment; filename=spreadsheet.xls");
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$q=mysql_query("select * from table1")  or die(mysql_error());
if(mysql_num_rows($q)>0)
{
echo"<table><tr><th>Name</th><th>Address</th><th>Phone</th></tr>";
 while($res=mysql_fetch_array($q))
 {
  echo"<tr><td>".$res['name']."</td><td>".$res['address']."</td><td>".$res['phone']."</td></tr>";
 }
echo"</table>";
}
?>

     Where,
                 mysql_fetch_array() return values as both an associative and numeric array.
                      while executing file, it required you to save excel file. After save file, you can view it. Now you got excel spreadsheet with named spreadsheet.xls which is created by PHP.

Related Post:
How to fetch result from mysql table using mysql_fetch_array in PHP
Create json file using PHP and MySQL
PHP : Parse data from XML Sitemap using simplexml_load_file and file_get_elements
Read data from JSON file using PHP
Parse data from XML file using PHP