How to export MySQL database into Excel sheet using PHP?

  • 11325

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"; 
?>
Tags:
Varun Singh
Varun Singh

Thank you for reading this article, and if you have any problem, have a another better useful solution about this article, please write message in the comment section. We will be very happy to hear that.

If you like my tutorial, please follow us on facebook, Insta, LinkedIn and Twitter.
I need your support to continue.