How to export MySQL database into Excel sheet using PHP script?

In this tutorial you are going to see step by step how to generate Excel Sheet from MySQL Database using help of PHP. In this example, Firstly we will create employee Table using CREATE TABLE statement and then insert data using INSERT INTO statement. After this I will write a script to export MySql data into Excel file step by step.

You can download FPDF library from here: Download

MySql Script to create Emplyee Table:

 CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `email` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;


INSERT INTO `employee` (`id`, `name`, `email`) VALUES
(1, 'Ajay Devgan', 'ajay_devgan@coderglass.com'),
(2, 'Amitab Bachan', 'amitabh_bachan@coderglass.com'),
(3, 'Amir Khan', 'amir_khan@coderglass.com'),
(4, 'Boby Deol', 'boby_deol@coderglass.com'),
(5, 'Baban Irani', 'baban_irani@coderglass.com'),
(6, 'Sahrukh Khan', 'sahrukh_khan@coderglass.com'),
(7, 'Salman Khan ', 'salman_khan@coderglass.com'),
(8, 'Sonam Kapoor', 'sonam_kapoor@coderglass.com'),
(9, 'Karina Kapoor', 'karina_kapoor@coderglass.com'),
(10, 'Kaitrina Kaif', 'kaitrina_kaif@coderglass.com'),
(11, 'Irfan Khan', 'irfan_khan@coderglass.com');

export.php:

 <?php 
$conn = new mysqli('localhost', 'root', ''); 
mysqli_select_db($conn, 'database_name'); 
$sql = "SELECT `id`,`name`,`email` FROM `employee`"; 
$setRec = mysqli_query($conn, $sql); 
$columnHeader = ''; 
$columnHeader = "Id" . "\t" . "Name" . "\t" . "Email" . "\t"; 
$setData = ''; 
while ($rec = mysqli_fetch_row($setRec)) { 
$rowData = ''; 
foreach ($rec as $value) { 
$value = '"' . $value . '"' . "\t"; 
$rowData .= $value; 
} 
$setData .= trim($rowData) . "\n"; 
} 
header("Content-type: application/octet-stream"); 
header("Content-Disposition: attachment; filename=User_records.xls"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
echo ucwords($columnHeader) . "\n" . $setData . "\n"; 
?>