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 .
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.
|
1 2 3 4 5 |
INSERT INTO `tblemploye` (`id`, `fullName`, `emailId`, `phoneNumber`, `department`, `joiningDate`) VALUES (1, 'Ogbugbu Ejiroghene, 'ogbugbu14@gmail.com', 08154761214, 'IT', '2018-05-01'), (2, 'Ejiro', 'john@test.com', 45455454, 'HR', '2017-08-12'), (3, 'Kefe', '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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
?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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
<?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> |
