PHP Web Development

How to fetch data in excel or generate excel file in PHP

File structure for this tutorial :
  config.php (database connection file)
  index.php (For fetching data )
  genrate-excel.php (For genrating excel file )
Create a sql table tblemployee.
Structure of sql table employe .

CREATE TABLE `tblemploye` (
  `id` int(11) NOT NULL,
  `fullName` varchar(120) NOT NULL,
  `emailId` varchar(150) NOT NULL,
  `phoneNumber` int(11) NOT NULL,
  `department` varchar(100) NOT NULL,
  `joiningDate` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `tblemploye`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `tblemploye`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

Now insert some data into this table.

INSERT INTO `tblemploye` (`id`, `fullName`, `emailId`, `phoneNumber`, `department`, `joiningDate`) VALUES
(1, 'Anuj kumar', 'phpgurukulofficial@gmail.com', 1234567890, 'IT', '2018-05-01'),
(2, 'John', 'john@test.com', 45455454, 'HR', '2017-08-12'),
(3, 'Demo', 'demo@test.com', 23423423, 'Account', '2016-10-01'),
(4, 'Test', 'test@test.com', 834856384, 'IT', '2017-12-01');

Index.php in this file we will  read the data from database.

?php
$query=mysqli_query($con,"select * from tblemploye");
$cnt=1;
while ($row=mysqli_fetch_array($query)) {
?>
  <tr>
                <td><?php echo $cnt;  ?></td>
                <td><?php echo $row['fullName']?></td>
                <td><?php echo $row['emailId']?></td>
                <td><?php echo $row['phoneNumber']?></td>
                <td><?php echo $row['department']?></td>
                <td><?php echo $row['joiningDate']?></td>
            </tr>
<?php
$cnt++;
} ?>
</table>

genrate-excel.php in this file we will fetch the data from database and generate excel file for the same data.

<?php
// Database Connection file
include('config.php');
?>
<table border="1">
<thead>
<tr>
<th>Sr.</th>
<th>Name</th>
<th>Email id</th>
<th>Phone Number</th>
<th>Department</th>
<th>Joining Date</th>
</tr>
</thead>
<?php
// File name
$filename="EmpData";
// Fetching data from data base
$query=mysqli_query($con,"select * from tblemploye");
$cnt=1;
while ($row=mysqli_fetch_array($query)) {
?>
            <tr>
                <td><?php echo $cnt;  ?></td>
                <td><?php echo $row['fullName'];?></td>
                <td><?php echo $row['emailId'];?></td>
                <td><?php echo $row['phoneNumber'];?></td>
                <td><?php echo $row['department'];?></td>
                <td><?php echo $row['joiningDate'];?></td>
            </tr>
<?php
$cnt++;
// Genrating Execel  filess
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=".$filename."-Report.xls");
header("Pragma: no-cache");
header("Expires: 0");
} ?>
</table>

header(“Content-type: application/octet-stream”);
header(“Content-Disposition: attachment; filename=”.$filename.”-Report.xls”);
The content-type should be whatever it is known to be, if you know it. application/octet-stream is defined as “arbitrary binary data” in RFC 2046″.
Means “I don’t know what the hell this is. Please save it as a file, preferably named $filename.”-Report.xls”.
header(“Pragma: no-cache”);
cache-control is the HTTP/1.1 implementation . cache-control  used to prevent the client from caching the response.
header(“Expires: 0”);
The Expires header specifies when content will expire, or how long content is “fresh.” After this time, the portal server will always check back with the remote server to see if the content has changed.
Expires: 0
The value 0 indicates that the content expires immediately and would have to be re-requested before being displayed again.

Demo—————————–

View Demo

How to run this script

1. Download and Unzip file on your local system.
2. Put this file inside root directory
3. Database Configuration

Database Configuration

Open phpmyadmin
Create Database empdata
Import database empdata.sql (available inside zip package)
Open Your browser put inside browser “http://localhost/phpexcl”

Download Full Source Code

How to fetch data in excel in PHP (Full Source Code)
Size: 2.01 KB
Version: V 1.0

Recommended Tutorials for you


Related posts

How to fetch data from mysql using PHP

Anuj Kumar

How to create pagination in PHP

Anuj Kumar

How to send Mail Using PHP

Anuj Kumar

PHP Intro

Anuj Kumar

How to Insert Data in Database using PHP OOPS Concept

Anuj Kumar

How to prevent Cross-Site Request Forgery (CSRF) in PHP

Anuj Kumar

Session In PHP

Anuj Kumar

Date And Time Formatting With PHP

Anuj Kumar

PHP Echo / Print

Anuj Kumar

2 comments

gopi November 13, 2018 at 5:33 pm

Hi
No download link (download link not working)

Reply
Anuj Kumar November 14, 2018 at 1:55 am

Download link is fixed. Now you can download.

Reply

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.