How to export MySQL data to excel sheet using PHP?

POSTED

AUTHOR: VARUN SINGH

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.

export mysql data to excel sheet in php

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"; 
?>



mysql to pdf download Export Mysql data to excel sheet



The End:

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, Google + and Twitter. I need your support to continue.

« Previous Page Next Page »

Comments: