Export Data to Excel in PHP


Export data feature is very useful where the data list need to be saved on the computer for offline used. Export data functionality makes your web application user-friendly and helps the user to maintain list data. 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.

Here is the data array ($data) and we will export these data to Excel using PHP.

$data = array(
    array(
"First Name" => "Natly""Last Name" => "Jones""Email" => "natly@gmail.com""Message" => "Test message by Natly"),
    array(
"First Name" => "Codex""Last Name" => "World""Email" => "info@codexworld.com""Message" => "Test message by CodexWorld"),
    array(
"First Name" => "John""Last Name" => "Thomas""Email" => "john@gmail.com""Message" => "Test message by John"),
    array(
"First Name" => "Michael""Last Name" => "Vicktor""Email" => "michael@gmail.com""Message" => "Test message by Michael"),
    array(
"First Name" => "Sarah""Last Name" => "David""Email" => "sarah@gmail.com""Message" => "Test message by Sarah")
);

We have created filterData() function for filter string provided by the data array.

function filterData(&$str)
{
    
$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 "codexworld_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(
$data as $row) {
    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;

Full script is given below.

<?php
    $data 
= array(
        array(
"First Name" => "Natly""Last Name" => "Jones""Email" => "natly@gmail.com""Message" => "Test message by Natly"),
        array(
"First Name" => "Codex""Last Name" => "World""Email" => "info@codexworld.com""Message" => "Test message by CodexWorld"),
        array(
"First Name" => "John""Last Name" => "Thomas""Email" => "john@gmail.com""Message" => "Test message by John"),
        array(
"First Name" => "Michael""Last Name" => "Vicktor""Email" => "michael@gmail.com""Message" => "Test message by Michael"),
        array(
"First Name" => "Sarah""Last Name" => "David""Email" => "sarah@gmail.com""Message" => "Test message by Sarah")
    );
    
    function 
filterData(&$str)
    {
        
$str preg_replace("/\t/""\\t"$str);
        
$str preg_replace("/\r?\n/""\\n"$str);
        if(
strstr($str'"')) $str '"' str_replace('"''""'$str) . '"';
    }
    
    
// file name for download
    
$fileName "codexworld_export_data" date('Ymd') . ".xls";
    
    
// headers for download
    
header("Content-Disposition: attachment; filename=\"$fileName\"");
    
header("Content-Type: application/vnd.ms-excel");
    
    
$flag false;
    foreach(
$data as $row) {
        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;
?>

 

 If you have any query about this tutorial and scripts, feel free to comment here.

Recommended Tutorials For You

3 Comments

  1. Bunty Agrawal Said...
  2. Deitrik Said...
  3. MAD Said...

Leave a reply

CONNECT WITH CODEXWORLD