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 generating excel file)

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

12345678910111213CREATE 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.

12345INSERT 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.

1234567891011121314151617?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.

Click Here To Free Access : https://phpgurukul.com/how-to-fetch-data-in-excel-or-generate-excel-file-in-php/

12345678910111213141516171819202122232425262728293031323334353637383940<?php// Database Connection fileinclude('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 filessheader("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: 0The value 0 indicates that the content expires immediately and would have to be re-requested before being displayed again.

How to run this script

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

Database Configuration

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

Click Here to Video Download : https://phpgurukul.com/?smd_process_download=1&download_id=7952

PHP Gurukul

Welcome to PHPGurukul. We are a web development team striving our best to provide you with an unusual experience with PHP. Some technologies never fade, and PHP is one of them. From the time it has been introduced, the demand for PHP Projects and PHP developers is growing since 1994. We are here to make your PHP journey more exciting and useful.

Website : https://phpgurukul.com