We often need to generate reports in excel sheet, but sometime we have our data stored in MySQL database. It is quiet easy to fetch records from MySQL and export the result in .csv file or .xls file and let user to force download it. We will make use of PHP and MySQL to do this functionality. This downloading option is very important in web application. Using this code we can generate a report from our MySQL data.
This tutorial will explain about downloading the MySQL data into excel sheet format using PHP MySQL. Here we will select all the data as per our MySQL query and generate a excel file.
This tutorial contain three files and one folder as follow:
- index.php
- connection.php (to connect with database)
- ExportToExcel.php (it contain function to export excel sheet)
- uploads (folder for uploading exported excel sheet)
Here is connection.php file, it will contain our script to database.
$hostname = "localhost"; $username = "root"; $password = ""; $database = "test"; $conn = mysql_connect("$hostname","$username","$password") or die(mysql_error()); mysql_select_db("$database", $conn) or die(mysql_error());
ExportToExcel.php is main file which contain function to export excel sheet. You can simply include this file to your file from which you want to export excel sheet. Include this file like this:
include ("ExportToExcel.php");
And then call this function:
ExportExcel("table_name");
Replace table_name with your required table.
(Also Read: Import Excel File Data in MySQL Database using PHP)
Here is ExportToExcel.php file:
function ExportExcel($table) { $filename = "uploads/".strtotime("now").'.csv'; $sql = mysql_query("SELECT * FROM $table") or die(mysql_error()); $num_rows = mysql_num_rows($sql); if($num_rows >= 1) { $row = mysql_fetch_assoc($sql); $fp = fopen($filename, "w"); $seperator = ""; $comma = ""; foreach ($row as $name => $value) { $seperator .= $comma . '' .str_replace('', '""', $name); $comma = ","; } $seperator .= "\n"; fputs($fp, $seperator); mysql_data_seek($sql, 0); while($row = mysql_fetch_assoc($sql)) { $seperator = ""; $comma = ""; foreach ($row as $name => $value) { $seperator .= $comma . '' .str_replace('', '""', $value); $comma = ","; } $seperator .= "\n"; fputs($fp, $seperator); } fclose($fp); echo "Your file is ready. You can download it from <a href='$filename'>here!</a>"; } else { echo "There is no record in your Database"; } }
I have made a simple HTML form which contain one input button and after hitting this button your excel sheet will generate and will upload in you “uploads” folder and then you will see a “Download Link” to download your excel sheet. Here is what I have made:
<form name="export" method="post"> <input type="submit" value="Click Me!" name="submit"> </form> <?php if(isset($_POST["submit"])) { ExportExcel("csv"); } ?>
Note: It will generate .csv file.
(Also Read: Import Excel File Data in MySQL Database using PHP)
Thanks alot
Your warmly welcome 🙂
Thousand of thanks, with your code I can develop it as my need. I spend my weeks to find it and you give me a great enlightenment
Your warmly welcome 🙂
Hi. Should I just change the [$filename = “uploads/”.strtotime(“now”).’.csv’;] into [$filename = “uploads/”.strtotime(“now”).’.XLS’;] for me to get an xls file?
No, there will be other method for this extension.
how to group records having same question description when selecting random rows
Limit five records on an excel file ?
Help me ..!
There is no limit.
Hello I need a method of .XLS format is there a way to do so can you please let me know as I searched a lot but not happening I need .xls format file what should i DO FOR it as i changed format to .xls but not working brother can you help me out
Great Tutorial Thanks a lot
Hello, thank you for a great script. How can I export it with Turkish characters? It also causes problems while importing same file to database with your import script.
I found this answer on stackoverflow.com you can try this method Turkish character issue while inserting into MySQL database with PHP
how to download arabic db with this code ?
I set this all up almost exactly but instead of just altering the table I have an entire SQL Statement however when i run it the file does not get uploaded I had php echo the statement and it is going through but i just cant get it to upload. Are there limitations that I don’t know of?
thanks a lot brother. may ALLAH give u success.
Ameen and thank you brother 🙂
Hello if I want to update the same csv file, what I must do ? I want the data to be exported to the same csv file
Thanks in advance
You have to check each row and then select what to do with it. If it present then UPDATE it otherwise INSERT it
The requested URL /ExportExcel/abc.csv was not found on this server.
Hello. Tanks a lot for code, u helped me very much 🙂 but I have one problem, i am downloading excel file and it doesn’t divide them to columns, everything in one row and column for each ID. What should I do divide them for different columns, can u help me plz?
The best , thanks alot , your solutionn was really resourceful,God bless you (Amen)
thank you a lot!
Thanks…
very usefull tutorial.
thank you very much!
simple and clear code
hellow Baghirli Orkhan,
Have you allready got solution on your problem in excell
Hi ,
Thanks for the code..
probably the only code i found that works for me…
only 1 small issue
data gets shifted to next tab for values if any cell value is a statement.
Thanks a lot brother for the code
this help me a lot.
This snippet was amazing!..thank you soooo much