๐Ÿš€ DevOps & SRE Certification Program ๐Ÿ“… Starting: 1st of Every Month ๐Ÿค +91 8409492687 ๐Ÿ” Contact@DevOpsSchool.com

Upgrade & Secure Your Future with DevOps, SRE, DevSecOps, MLOps!

We spend hours scrolling social media and waste money on things we forget, but wonโ€™t spend 30 minutes a day earning certifications that can change our lives.
Master in DevOps, SRE, DevSecOps & MLOps by DevOpsSchool!

Learn from Guru Rajesh Kumar and double your salary in just one year.


Get Started Now!

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.

Subscribe
Notify of
guest


0 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments

Certification Courses

DevOpsSchool has introduced a series of professional certification courses designed to enhance your skills and expertise in cutting-edge technologies and methodologies. Whether you are aiming to excel in development, security, or operations, these certifications provide a comprehensive learning experience. Explore the following programs:

DevOps Certification, SRE Certification, and DevSecOps Certification by DevOpsSchool

Explore our DevOps Certification, SRE Certification, and DevSecOps Certification programs at DevOpsSchool. Gain the expertise needed to excel in your career with hands-on training and globally recognized certifications.

0
Would love your thoughts, please comment.x
()
x