Export data to Excel in PHP


In this short tutorial we will discuss about export data to Excel in PHP. Many places in our project we are needed to provide export to Excel functionality. With the below simple script you can easily implement the export data to Excel functionality using PHP.
If you want to view the live action of the export to Excel script, click on the Demo link. Also you would be able to download the full PHP export to Excel scripts from the Download link.

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

$data = array(
    array(
"First Name" => "Nitya""Last Name" => "Maity""Email" => "nityamaity87@gmail.com""Message" => "Test message by Nitya"),
    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" => "Nitya""Last Name" => "Maity""Email" => "nityamaity87@gmail.com""Message" => "Test message by Nitya"),
        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.

2 Comments

  1. Deitrik Said...
  2. MAD Said...

Leave a reply

Connect With CodexWorld