Export Data To Excel In PHP

No votes yet.
Please wait...

Hello guys,
PHP HURDLES is once again here with yet another intersting and commonly asked topic Export Data to Excel in PHP.

Exporting data from an Access database to in a file format that can be read by Excel. This article shows you how to prepare and export your data to Excel, and also gives you some troubleshooting tips for common problems that might occur.
Sometimes we need to save data list on our computer for offline use. Export data feature is very useful for this purpose.It makes our web application more user friendly and helps the user to maintain the data list. Excel is the best format to Export data in a file and you can easily export data to Excel using PHP.

In this tutorial, we’ll show you how to export data to Excel in PHP. Our simple PHP script lets you implement export data to excel functionality. By one click, the user can export data to Excel and download it in a .xls file.

lets start with the topic,

First of all we will fetch data from database for that purpose we will create a file user_info.php and in that file we will create a function userinfo() which will return a array $info with values.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php function userinfo()
{
$con = mysqli_connect("localhost","root","","phphurdles");
// Check connection if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql="SELECT * FROM userinfo ";
$info=array();
 if ($result=mysqli_query($con,$sql))
{
 $i=0; while ($row=mysqli_fetch_array($result))
 {
 $info[$i]['firstname']=$row['firstname'];
 $info[$i]['lastname']=$row['lastname'];
 $info[$i]['email']=$row['email'];
$info[$i]['phone']=$row['phone'];
 $info[$i]['gender']=$row['gender'];
$i++;
}
 return $info;
 }
}
?>

And finally we will create index.php which will contain export_data() function and a export link for exporting data

//code for export_data() function in index.php which will export data into excel on export link click
export_data()function will have a filterData() function for filter string provided by the data array and headers for the download

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
function filterData(&amp;$str)//We have created filterData() function for filter string provided by the data array.
{
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}
$fileName variable hold the name of file for download and also content header is defined for download.

// file name for download
$fileName = "phphurdles_export_data" . date('Ymd') . ".xls";

// headers for download
header("Content-Disposition: attachment; filename="$fileName"");
header("Content-Type: application/vnd.ms-excel");
Now we have run loop through each key/value pair in $data. Using of $flag variable we will display column names as first row. array_walk() is used to filter the data together with filterData() function.

$flag = false;
foreach($user as $row)//$user is the array which we will call from user_info.php {
if(!$flag){
// display column names as first row
echo implode("\t", array_keys($row)) . "\n";
$flag = true;
}
// filter data
array_walk($row, 'filterData');
echo implode("\t", array_values($row)) . "\n";
}

exit;

//code for export link on index.php which on click will export datalist in excel

1
2
<div style="text-align: center;"><a href="index.php?export">
<button style="color: #fff; width: 200px; height: 40px; background-color: #0000ff;" type="submit">Export</button></a></div>

So this is how we can export data into excel using a simple PHP script.we have tried to use the most easy PHP script so that the export data functinality can be understood by our fellow developers easily.
For the working Demo please download our demoDownload Here

Guys,
Hope this tutorial will help our fellow developers dont hesitate to comment in case of any doubt or suggestions.

keep moving
Because hurdles aren’t really hurdles

6 Comments

  1. Saurabh Dubey said:

    Thank You

    Rating: 4.7. From 3 votes.
    Please wait...
    April 22, 2017
    Reply
  2. You should be a part of a contest for one of
    the finest websites online. I am going to recommend this blog!

    Rating: 1.0. From 1 vote.
    Please wait...
    May 21, 2017
    Reply
    • Deepak Shah said:

      thankyou so much for that much of support and appreciation

      Rating: 2.5. From 2 votes.
      Please wait...
      May 21, 2017
      Reply
  3. Raj Roshan said:

    Nice bro really helpful

    Rating: 1.0. From 1 vote.
    Please wait...
    July 1, 2017
    Reply
    • Saurabh Dubey said:

      Thank You, Raj! We will continue helping you in future

      Rating: 1.0. From 1 vote.
      Please wait...
      July 1, 2017
      Reply
  4. reference said:

    Awesome post.

    No votes yet.
    Please wait...
    August 16, 2017
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *