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

File structure for this tutorial :

1. config.php (database connection file)
2. index.php (For fetching data )
3. genrate-excel.php (For genrating excel file )

Create a sql table tblemployee.
Structure of sql table employe .

[code language=”sql”]
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;
[/code]

Now insert some data into this table.
[code language=”sql”]
INSERT INTO `tblemploye` (`id`, `fullName`, `emailId`, `phoneNumber`, `department`, `joiningDate`) VALUES
(1, ‘Amardeep Dubey’, ‘ad@gmail.com’, 1234567890, ‘IT’, ‘2018-05-01’),
(2, ‘Chandan Kumar’, ‘ck@gmail.com’, 45455454, ‘Director’, ‘2017-08-12’),
(3, ‘Mantosh Singh’, ‘ms@gmail.com’, 23423423, ‘Account’, ‘2016-10-01’),
(4, ‘Bittu Kumar’, ‘bittu@gmail.com’, 834856384, ‘SEO’, ‘2017-12-01’);
(5, ‘Vikash Verma’, ‘vv@gmail.com’, 1234567890, ‘SEO’, ‘2018-05-01’),
(6, ‘Narayan Rajak’, ‘ck@gmail.com’, 45455454, ‘IT’, ‘2017-08-12’),
[/code]

Index.php in this file we will read the data from database.
[code language=”php”]
<?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>
[/code]
genrate-excel.php in this file we will fetch the data from database and generate excel file for the same data.
[code language=”php”]
<?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>
[/code]

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.

Rajesh Kumar

I’m a DevOps/SRE/DevSecOps/Cloud Expert passionate about sharing knowledge and experiences. I have worked at Cotocus. I share tech blog at DevOps School, travel stories at Holiday Landmark, stock market tips at Stocks Mantra, health and fitness guidance at My Medic Plus, product reviews at TrueReviewNow , and SEO strategies at Wizbrand.

Do you want to learn Quantum Computing?

Please find my social handles as below;

Rajesh Kumar Personal Website
Rajesh Kumar at YOUTUBE
Rajesh Kumar at INSTAGRAM
Rajesh Kumar at X
Rajesh Kumar at FACEBOOK
Rajesh Kumar at LINKEDIN
Rajesh Kumar at WIZBRAND

Rajesh Kumar DailyLogs