How to Export data from MySQL to Excel using PHP?

Decade back MS-Excel is one of most popular data inter-exchange platform. Looking into it’s popularity Excel file is supported by Window, Linux, Mac OS X, Android & iOS. Compare to Other spreadsheets Excel is rich with many advanced functionalities such as Calculation, Formula Feeding, Pivot Tables, Graphics tools, Data visuals or Macro programming. This is the reason Customer wants Export to Excel facility in their application.

Generally during we display tabular data in a web page for sharing purpose we do implement export to excel button. For every user it’s many not possible to give access to the System. The cause using an excel sheet we can easily share data to the respective authority. In the below demo I am fetching data from a MySQL Database table and exporting those data to an excel file using PHP programming.

To export data from MySQL to excel, here under MySQL Database “temp_db” I created a Table “ManagerDetails”. Which holds managers details.

Create Table

CREATE TABLE IF NOT EXISTS ManagerDetails (
Manager_ID varchar(10) NOT NULL DEFAULT '', 
Manager_Name varchar(200) NOT NULL DEFAULT '', 
Manager_Designation varchar(200) NOT NULL DEFAULT '', 
Manager_Salary varchar(20) NOT NULL DEFAULT '0', 
PRIMARY KEY (Manager_ID) 
)

Insert Records for Demo purpose

INSERT INTO ManagerDetails (Manager_ID, Manager_Name, Manager_Designation, Manager_Salary) VALUES
(1, 'Sujata Mohapatra', 'Sr. Manager', '28000'),
(2, 'Ravi Ranjan Dash', 'Program Manager', '32000'),
(3, 'Bijaylakshmi Dash', 'Business Manager', '48000'),
(4, 'Rosalin Roy', 'Sr. Program Manager', '62000'),
(5, 'Nibedita Mahapatra', 'Jr. Manager', '18000'),
(6, 'Meghana Roy', 'Sr. Manager', '35000'),
(7, 'Manamohan Mohanty', 'Sr. Team Lead', '23000'),
(8, 'Rupak Maharana', 'Business Head', '36000'),
(9, 'Ramkrishna Dalei', 'QA Manager', '18000'),
(10, 'Puspashree Mishra', 'Team Leader', '15000'),
(11, 'Jayshree Moharana', 'Sr. Project Manager', '55000'),
(12, 'Ravi Prakash Dash', 'Business Head', '42000');

Then to Fetch Data from “ManagerDetails.php” first I am establishing Database Connection. Once after the Successful Connection executing a SQL query to fetch data. To customize output Excel File Name here I declared a variable $FileName. Currently here I decided the File Name as “ManagerDetails.xls”. You can update this as per your requirements.

To generate an Excel File after File Name I am declaring Excel File Header information. Then inside a while loop I am storing “mysql_fetch_assoc($manager_query)” to $row variable. Which holds database records. To print those values to an Excel File I am using echo command. To create several columns in Excel during echo command I am using “/t” (Tab) after each column of $row. Similarly for new line at the end of echo command using “/n”.

ManagerDetails.php

<?php
/* Establishing MySQL Database Connection */
$sql_query=mysql_connect("localhost", "root");
mysql_select_db("temp_db", $sql_query);

/* Executing query to Fetch Data from MySQL Table */
$manager_query=mysql_query("SELECT * FROM ManagerDetails");

/* Function to Filter each data row */
function FilterSpecialChars(&$str)
{
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}

/* Place to Define File Name for your Excel File */
$FileName = "ManagerDetails" . ".xls";

/*Setting File Header information for Excel*/
header("Content-Disposition: attachment; filename=\"$FileName\"");
header("Content-Type: application/vnd.ms-excel");

/* Running loop to display each row of Data */
while($row = mysql_fetch_assoc($manager_query)){
// filter data
array_walk($row, 'FilterSpecialChars');
echo $row['Manager_Name'] . "\t" . $row['Manager_Designation'] . "\t" . $row['Manager_Salary'] . "\n";
}

exit;
?>

Updates

Create Table

Insert Records for Demo purpose

ManagerDetails.php