In this tutorial you are going to learn how to download an excel report after filling a HTML form.
MySQL is not used in this tutorial, just a simple example showing how to generate Excel file using core PHP functions.
Lets see the basic code here:
<?php
//give a filename
$filename = "myexcel.csv";
//set headers to download the file
header( 'Content-Type: text/csv' );
header( 'Content-Disposition: attachment;filename='.$filename);
//set an array
$cells = array("Column1", "Column2", "Column3");
$file = fopen('php://output', 'w');
fputcsv($file,$cells);
fclose($file);
?>
When you run this code on a web browser, a CSV (myexcel.csv) file will immediately download with the values given in the array $cells[].
I have used php://output stream which sends the output from Apache server to the browser.
fputcsv() – this function plays the main role in the above script, it helps us to create the CSV file from the array $cells[].
Now, lets move to the dynamic part,
Create a form in HTML, see the below (index.html):
<html> <body> <form action="excel.php" method="post"> <table> <tr><td>Name</td><td><input type="text" name="name" /></td></tr> <tr><td>Email</td><td><input type="text" name="email" /></td></tr> <tr><td>Location</td><td><input type="text" name="location" /></td></tr> <tr><td> </td><td><input type="submit" /></td></tr> </table> </form> </body> </html>
PHP script – slightly modified from the first script (excel.php):
<?php
//give a filename
$filename = "myexcel.csv";
//set headers to download file
header( 'Content-Type: text/csv' );
header( 'Content-Disposition: attachment;filename='.$filename);
$file = fopen('php://output', 'w');
//set the column names
$cells[] = array('Name', 'Email', 'Location');
//pass all the form values
$cells[] = array($_POST['name'], $_POST['email'], $_POST['location']);
foreach($cells as $cell){
fputcsv($file,$cell);
}
fclose($file);
?>
if you compare the first script and this one above, you can see there is a difference in $cells[] variable which became multi-dimensional array and also I’m looping through each array using foreach()
Basically first iteration will pass the column names which is hard coded, the second iteration will pass the dynamic values from $_POST.
See the demo and download the script here:
hi,
how can we store data in one single excel sheet every time a user submit the form.
I got it but in the excel sheet it is printing warning stream in the excel sheet
what if we want export data in columns instead of rows in excel?