mysql PHP Code

How to create excel file with mysql data using php code

Hello friends, I have code for how to create a excel file with mysql data using php code. By following 5 steps to create excel file.We can implement it in admin section of websites for user list excel file.
PHP Solutions: Dynamic Web Design Made Easy

How to create excel file with mysql data using php code by Anil Kumar Panigrahi

How to create excel file with mysql data using php code by Anil Kumar Panigrahi

Follow the below steps to retrieve the data from database:

 

1) Connect to database:

$dbhost= "Mysql server name"; //your MySQL Server
$dbuser = "Username"; //your MySQL User Name
$dbpass = "password"; //your MySQL Password
$dbname = "database name";
//your MySQL Database Name of which database to use this
$tablename = "table"; //your MySQL Table Name which one you have to create excel file
// your mysql query here , we can edit this for your requirement
$sql = "Select * from $tablename ";
//create  code for connecting to mysql
$Connect = @mysql_connect($dbhost, $dbuser, $dbpass)
or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($dbname, $Connect)
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
//execute query
$result = @mysql_query($sql,$Connect)
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());

 

2) Define a filename of excel

//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
$fp = fopen('database.xls', "w");
$schema_insert = "";
$schema_insert_rows = "";
//start of printing column names as names of MySQL fields

 

3) Set the column name of excel file

//start of adding column names as names of MySQL fields
for ($i = 1; $i < mysql_num_fields($result); $i++)
{
$schema_insert_rows.=mysql_field_name($result,$i) . "\t";
}
$schema_insert_rows.="\n";
echo $schema_insert_rows;
fwrite($fp, $schema_insert_rows);
//end of adding column names

 

4) Getting data from database and adding to the excel file

//start while loop to get data
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); //
$schema_insert = "";
for($j=1; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= strip_tags("$row[$j]").$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);

//this corrects output in excel when table fields contain \n or \r
//these two characters are now replaced with a space

$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\n";
//$schema_insert = (trim($schema_insert));
//print $schema_insert .= "\n";
//print "\n";

 

5) After adding data to excel file is completed then close the file

fwrite($fp, $schema_insert);
}
fclose($fp);

This is the steps to create excel file with mysql data using php code.

You Might Also Like...

28 Comments

  • Reply
    How to create excel file with mysql data using php code « ANIL …
    March 6, 2010 at 1:23 am

    […] How to create excel file with mysql data using php code « ANIL … Posted in Uncategorized | Tags: connect, data, excel-file, follow-the-below, […]

  • Reply
    Mark Baker
    March 6, 2010 at 10:19 am

    You could always explain how to create a real Excel file rather than a simple tab-separated file. Yes, Excel can read tab- or comma-separated value files, as can many applications… giving that file an extension of .xls does not make it an Excel file.

    And if you do create a tab-separated file, don’t make it such complex code, with all kinds of unnecessary overhead: use PHP’s fputcsv() function which does the hard work for you. This has the advantage of escaping quotes and other special characters in your data, which your code doesn’t.

    If you want to create a real Excel file, which has the benefits of cell formatting, frozen heading rows, filter headings, and all the other features of Excel itself, then it can be done (using less code than your example) with one of the myriad of free PHP libraries such as PHPExcel.

  • Reply
    Jagjit Singh
    February 22, 2011 at 6:45 am

    it is good .but not working online .please suggest something

  • Reply
    SMSCubano.com
    March 11, 2011 at 6:59 pm

    It could be not the ideal solutions but it works very good and it’s simple to understand.. I have been more than 2 hours reading other codes and they all included to download api and were more complicated when you use a shared hosting… so, this is short code, easy to understand and use, just copy and paste and it works… anyway when you download the file, Microsft Excel ask you to convert it to a good xls format, you say yes and your file is converted to a legitime excel without any problem…. I think this code is very practical.

    thanks to Anil..
    leo.

  • Reply
    Tushar Garg
    June 21, 2011 at 7:45 am

    Hey all this code sure is great for a simple excel file creation and has worked for me , having said that is there any way we can format the output??? more precisely can i oput in bold text … please help if if u can … thank you!!

  • Reply
    Raj
    June 25, 2011 at 11:08 am

    It is hot cake .helped me very well .Thank you anil

  • Reply
    Raj
    July 7, 2011 at 10:52 am

    plz tell me how to apply bold to text in excelsheet generated by above code.
    Thank you in Advance

  • Reply
    rupesh
    July 11, 2011 at 12:20 pm

    i want code for opening existing excel file….code for updating excel file…

  • Reply
    vijay
    September 29, 2011 at 5:07 pm

    thanx, it work fine!

  • Reply
    zencool
    October 22, 2011 at 7:57 am

    how to do database field name, Excel File title name bold

  • Reply
    Paul
    October 24, 2011 at 3:28 pm

    All this did for me is display in the web page the column names in the database. No excel file and no content from the database other than the headings?

    What am I missing here?

  • Reply
    Top 10 most viewed posts in anil labs - Anil Labs
    January 8, 2012 at 11:05 am

    […] 2)How to create excel file with mysql data using php code […]

  • Reply
    fisal
    February 7, 2012 at 9:06 am

    Thank You . i found the first field not appear to modfiy that you must make first $i value in loop is 0 instead of one so it should be like that for ($i = 0; $i < mysql_num_fields($result); $i++)

  • Reply
    rasmi
    March 28, 2012 at 11:07 am

    how to store data in a xml file through php ?

  • Reply
    rahul kumar tripathi
    April 17, 2012 at 9:28 am

    very good site

  • Reply
    shri
    June 7, 2012 at 10:04 am

    hi…
    i want know the ………..
    how to create .xls file or software ….
    but step by step means which software or etc and another thing also 1 by 1 ok
    plz any1 spcly anil plz help yar plz………..

  • Reply
    xjshiya
    August 15, 2012 at 1:45 am

    i want to put headers/title in my generated excel report. how can i customize the title/header like centering the title with bold text and underline? thanks in advance.

  • Reply
    Silpa Sadanandan
    September 13, 2012 at 3:42 am

    It is a nice tutorial. I just want to make the excel file stored in a different location other than the project folder . Can you please suggest the code for that also?
    Thanks in advance

  • Reply
    Ayush
    October 11, 2012 at 10:17 am

    Dude this is great
    But im getting duplicate values and also im using odbc connection instead of mysql_connect
    everything is working fine except for the duplicate values can u tell me how can i omit duplicate values thankx..

  • Reply
    Ayush
    October 14, 2012 at 11:09 am

    Can u tell me now how to bold the values
    Will be much help
    Thankx

  • Reply
    shubhbandh
    January 4, 2013 at 9:15 am

    Thanks for help, really nice post it’s useful for me…

  • Reply
    meet
    March 15, 2013 at 9:32 am

    hay this generates the excel sheet but not showing me any of data..just shows the column name..
    i have just copy paste your code…should i have to do modification inside it???

  • Reply
    meet
    March 15, 2013 at 9:43 am

    i think it’s not going inside the while loop

  • Reply
    suresh
    August 22, 2013 at 8:44 am

    very helpful post admin thank u so much

  • Reply
    reza
    October 7, 2014 at 6:58 pm

    hi . thank you . this is very good .

    please help me . how to create excel file with persian or arabic (utf-8) mysql .

  • Reply
    Ajesh
    April 26, 2016 at 11:54 am

    Thanks its working perfectly

  • Reply
    aditya
    February 1, 2017 at 6:12 am

    i am able to upload my excel sheet..even data is inserting..but if i took rows-wise excel sheet,m not able to insert data from excel sheet..please if you have any code refer me. http://shoppersmill.com/

  • Reply
    Jisha
    September 20, 2017 at 2:11 pm

    Sir,Thank you very much for this perfect code

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.