In this article we will show you the solution of source code PHP export to excel, now we are exporting database table data to excel sheet by using header() method in php.

The header() method force the excel file to download by Content-Type, Content-Disposition header specifications and using implode() method we exporting all data to excel.

Step By Step Guide On Source Code PHP Export To Excel :-

Here we defined database server details in mysqli_connect() method because it executes database connection i.e referred as $con variable and if they not executed then it throws error.

Variable ‘$fn’ defined for store excel file name with current date string and we defined header columns names into array that is stored on variable ‘$hn’.

Using implode() method we inserting header array ‘$hn’ values as in excel structure.

Using mysqli_query() method we selected all values in table ‘info’ by select query and it result stored on variable ‘$qry’ then we iterates all values using while loop and inserted row by row values using implode method.

At last we forces header method to download excel with $fn name and then we printing result of retrieved data on excel.

<?php
$con=mysqli_connect("localhost","root","","dbase");
if($con->connect_error){
    echo $con->connect_error;
}
$fn="Sample".date('Y-m-d').".xls";
$hn=array("ID","Name","Password");
$excelH=implode("\t",array_values($hn)) . "\n";
$qry=mysqli_query($con,"SELECT * FROM info ORDER BY id ASC");
if($qry->num_rows > 0){
    while($row=$qry->fetch_assoc()){
        $rdata=array($row['ID'],$row['Name'],$row['Password']);
        $excelH .=implode("\t",array_values($rdata)) . "\n";
    }
}
else{
    $excelH.="No records found";
}
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$fn\"");
echo $excelH;
exit;
?>

1. A php script can be placed anywhere in the document. A php script starts with <?php and end with ?>.
2. The default file extension for php files is “.php” and php statements end with ‘;’ semicolon.
3. Here we defined database server details for executes database connection using mysqli_connect() method then this connection referred by variable ‘$con’.
4. Using if condition we checking whether database connection exists or not if they not present then it throws error on webpage.
5. The variable ‘$fn’ defined for holds excel file name with current date string and column header names ‘ID,Name,Password’ defined as array then stored on variable ‘$hn’.
6. Using implode() method inserting header array values in table structure then it is stored on variable ‘$excelH’.
7. The mysqli_query() method will executes defined select query on database ‘dbase’ to collect all table ‘info’ data’s by $con then stored on variable ‘$qry’.
8. Using if condition we checking whether $qry value is greater than ‘0’ then we proceeding while loop there we using ‘fetch_assoc()’ method it helps us to retrieve data from table row by row.
9. Then we inserting each row’s column value on array and stored on variable ‘$rdata’ now we joining those row records on ‘$excelH’ variable in table structure.
10. If $qry value not greater than ‘0’ then it inserts ‘No records found’ message on ‘$excelH’ variable. At last we specified ‘Content-Type as EXCEL, Content-Disposition as attachment type with ‘$fn’as file name’.
11. It forces to download excel with defined file name then echo statement prints ‘$excelH’ value on excel so we are finally exported all retrieved data on excel file.

Conclusion :-

In conclusion now we are able to know how to export data to excel in php.

When work with php we need to create and process php files at server location and then we need to start the server before execute the program.

When we executing this program on browser it will downloads excel file with name ‘Sample2022-09-05.xls’ user needs to open this file there we can see all data of database ‘dbase’ table ‘info’ records printed on excel file.

I hope this article on source code PHP export to excel helps you and the steps and method mentioned above are easy to follow and implement.