File structure for this tutorial :<\/p>\n
Create a sql table tblemployee.
\nStructure of sql table employe .<\/p>\n
[code language=”sql”]
\nCREATE TABLE `tblemploye` (
\n `id` int(11) NOT NULL,
\n `fullName` varchar(120) NOT NULL,
\n `emailId` varchar(150) NOT NULL,
\n `phoneNumber` int(11) NOT NULL,
\n `department` varchar(100) NOT NULL,
\n `joiningDate` varchar(100) NOT NULL
\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;
\nALTER TABLE `tblemploye`
\n ADD PRIMARY KEY (`id`);
\nALTER TABLE `tblemploye`
\n MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
\nCOMMIT;
\n[\/code]<\/p>\n
Now insert some data into this table.
\n[code language=”sql”]
\nINSERT INTO `tblemploye` (`id`, `fullName`, `emailId`, `phoneNumber`, `department`, `joiningDate`) VALUES
\n(1, ‘Amardeep Dubey’, ‘ad@gmail.com’, 1234567890, ‘IT’, ‘2018-05-01’),
\n(2, ‘Chandan Kumar’, ‘ck@gmail.com’, 45455454, ‘Director’, ‘2017-08-12’),
\n(3, ‘Mantosh Singh’, ‘ms@gmail.com’, 23423423, ‘Account’, ‘2016-10-01’),
\n(4, ‘Bittu Kumar’, ‘bittu@gmail.com’, 834856384, ‘SEO’, ‘2017-12-01’);
\n(5, ‘Vikash Verma’, ‘vv@gmail.com’, 1234567890, ‘SEO’, ‘2018-05-01’),
\n(6, ‘Narayan Rajak’, ‘ck@gmail.com’, 45455454, ‘IT’, ‘2017-08-12’),
\n[\/code]<\/p>\n
Index.php<\/strong> in this file we will read the data from database. header(\u201cContent-type: application\/octet-stream\u201d);<\/strong>
\n[code language=”php”]
\n<?php
\n$query=mysqli_query($con,”select * from tblemploye”);
\n$cnt=1;
\nwhile ($row=mysqli_fetch_array($query)) {
\n?>
\n <tr>
\n <td><?php echo $cnt; ?><\/td>
\n <td><?php echo $row[‘fullName’]?><\/td>
\n <td><?php echo $row[’emailId’]?><\/td>
\n <td><?php echo $row[‘phoneNumber’]?><\/td>
\n <td><?php echo $row[‘department’]?><\/td>
\n <td><?php echo $row[‘joiningDate’]?><\/td>
\n <\/tr>
\n<?php
\n$cnt++;
\n} ?>
\n<\/table>
\n[\/code]
\ngenrate-excel.php<\/strong> in this file we will fetch the data from database and generate excel file for the same data.
\n[code language=”php”]
\n<?php
\n\/\/ Database Connection file
\ninclude(‘config.php’);
\n?>
\n<table border=”1″>
\n<thead>
\n<tr>
\n<th>Sr.<\/th>
\n<th>Name<\/th>
\n<th>Email id<\/th>
\n<th>Phone Number<\/th>
\n<th>Department<\/th>
\n<th>Joining Date<\/th>
\n<\/tr>
\n<\/thead>
\n<?php
\n\/\/ File name
\n$filename=”EmpData”;
\n\/\/ Fetching data from data base
\n$query=mysqli_query($con,”select * from tblemploye”);
\n$cnt=1;
\nwhile ($row=mysqli_fetch_array($query)) {
\n?>
\n <tr>
\n <td><?php echo $cnt; ?><\/td>
\n <td><?php echo $row[‘fullName’];?><\/td>
\n <td><?php echo $row[’emailId’];?><\/td>
\n <td><?php echo $row[‘phoneNumber’];?><\/td>
\n <td><?php echo $row[‘department’];?><\/td>
\n <td><?php echo $row[‘joiningDate’];?><\/td>
\n <\/tr>
\n<?php
\n$cnt++;
\n\/\/ Genrating Execel filess
\nheader(“Content-type: application\/octet-stream”);
\nheader(“Content-Disposition: attachment; filename=”.$filename.”-Report.xls”);
\nheader(“Pragma: no-cache”);
\nheader(“Expires: 0”);
\n} ?>
\n<\/table>
\n[\/code]<\/p>\n
\nheader(\u201cContent-Disposition: attachment; filename=\u201d.$filename.\u201d-Report.xls\u201d);<\/strong>
\nThe content-type should be whatever it is known to be, if you know it. application\/octet-stream is defined as \u201carbitrary binary data\u201d in RFC 2046\u2033.
\nMeans \u201cI don\u2019t know what the hell this is. Please save it as a file, preferably named $filename.\u201d-Report.xls\u201d.
\nheader(\u201cPragma: no-cache\u201d);<\/strong>
\ncache-control is the HTTP\/1.1 implementation . cache-control used to prevent the client from caching the response.
\nheader(\u201cExpires: 0\u201d);<\/strong>
\nThe Expires header specifies when content will expire, or how long content is \u201cfresh.\u201d After this time, the portal server will always check back with the remote server to see if the content has changed.
\nExpires: 0
\nThe value 0 indicates that the content expires immediately and would have to be re-requested before being displayed again.<\/p>\n\n