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.
Follow the below steps to retrieve the data from database:
1) Connect to database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | $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
1 2 3 4 5 6 | //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
1 2 3 4 5 6 7 8 9 | //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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | //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
1 2 3 | fwrite($fp, $schema_insert); } fclose($fp); |
This is the steps to create excel file with mysql data using php code.
Related Posts
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.
it is good .but not working online .please suggest something
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.
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!!
It is hot cake .helped me very well .Thank you anil
plz tell me how to apply bold to text in excelsheet generated by above code.
Thank you in Advance
i want code for opening existing excel file….code for updating excel file…
thanx, it work fine!
how to do database field name, Excel File title name bold
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?