Export MySQL Table Data into Excel Sheet Format in PHP

[button-blue url=”http://demos.eggslab.net/downloads/1″ target=”_blank” position=””]DOWNLOAD[/button-blue][button-green url=”http://demos.eggslab.net/ExportDatabaseToExcel/” target=”_blank” position=””]DEMO[/button-green]

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:

1. index.php
2. connection.php (to connect with database)
3. ExportToExcel.php (it contain function to export excel sheet)
4. 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.

[button-blue url=”http://demos.eggslab.net/downloads/1″ target=”_blank” position=””]DOWNLOAD[/button-blue][button-green url=”http://demos.eggslab.net/ExportDatabaseToExcel/” target=”_blank” position=””]DEMO[/button-green]

(Also Read: Import Excel File Data in MySQL Database using PHP)