PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites: Create excel spreadsheet using PHP and Mysql

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

4 comments:

  1. Thanks sir you this tutorial helped me alot. i was searching for this solution and finally today i got what i want to use. thanks once again.

    Kindly do me a favour and write the tutorial for sending email through php. i am using sendmail of linux machine and in php i use mail() funtion but could not got sucess. scripts runs for long amount of time and do nothing.

    Thanks

    ReplyDelete
    Replies
    1. You can send mail only in server. visit this tutorial http://www.phponwebsites.com/2014/06/php-send-mail.html

      Delete
  2. Hi,
    After displaying my content to html table, I want to perform this function on click of a button. I tried using this code to keep on the button. But is not working. Please help.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete