How to export MySQL database into Excel sheet using PHP?

  • 16511

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', '[email protected]'),
(2, 'Amitab Bachan', '[email protected]'),
(3, 'Amir Khan', '[email protected]'),
(4, 'Boby Deol', '[email protected]'),
(5, 'Baban Irani', '[email protected]'),
(6, 'Sahrukh Khan', '[email protected]'),
(7, 'Salman Khan ', '[email protected]'),
(8, 'Sonam Kapoor', '[email protected]'),
(9, 'Karina Kapoor', '[email protected]'),
(10, 'Kaitrina Kaif', '[email protected]'),
(11, 'Irfan Khan', '[email protected]');

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.