Export PHP data to .xls file

Data is any set of characters that is translated for some purpose, like for analysis. It can be any character or value, including text and numbers, pictures, sound, or video. If data is not put into context then it does not do anything to a human being or machine.

We generally export data from the websites to save it on the computer for offline used like to maintain list data and to analysis it. Excel is the best format to Export data in a file because reading large files is much easier in Excel for the user. Also, you can use additional functions like selecting individual cells to import the data, convert dates and time automatically, filters, sorting, reading formulas and their results etc.

In this blog we will learn how to export PHP data to .xls file step by step. With the help of simple PHP script you can export data to Excel and download it in a .xls file.

STEP 1: Create an Array to store the data

I have created an array ($customers_data) to store the customers details. You can create your own array as per your requirement:

$customers_data = array(
array(
'customers_id' => '1',
'customers_firstname' => 'Chris',
'customers_lastname' => 'Cavagin',
'customers_email' => 'chriscavagin@gmail.com',
'customers_telephone' => '9911223388'
),
array(
'customers_id' => '2',
'customers_firstname' => 'Richard',
'customers_lastname' => 'Simmons',
'customers_email' => 'rsimmons@media.com',
'customers_telephone' => '9911224455'
),
array(
'customers_id' => '3',
'customers_firstname' => 'Steve',
'customers_lastname' => 'Beaven',
'customers_email' => 'ateavebeaven@gmail.com',
'customers_telephone' => '8855223388'
),
array(
'customers_id' => '4',
'customers_firstname' => 'Howard',
'customers_lastname' => 'Rawson',
'customers_email' => 'howardraw@gmail.com',
'customers_telephone' => '9911334488'
),
array(
'customers_id' => '5',
'customers_firstname' => 'Rachel',
'customers_lastname' => 'Dyson',
'customers_email' => 'racheldyson@gmail.com',
'customers_telephone' => '9912345388'
)
);

STEP 2: Create a function to filter the data:

This function is used to detect double-quotes and escape any value that contains them. Without this an uneven number of quotes in a string can confuse Excel.

// Filter Customer Data
function filterCustomerData(&$str) {
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}

STEP 3: Define file name and content header for download:

$file_name variable is used to hold the name of the file and also define content header for download.

// File Name & Content Header For Download
$file_name = "customers_data.xls";
header("Content-Disposition: attachment; filename=\"$file_name\"");
header("Content-Type: application/vnd.ms-excel");

STEP 4: Define Loop through each row in  $customers_data

//To define column name in first row.
$column_names = false;
// run loop through each row in $customers_data
foreach($customers_data as $row) {
if(!$column_names) {
echo implode("\t", array_keys($row)) . "\n";
$column_names = true;
}
// The array_walk() function runs each array element in a user-defined function.
array_walk($row, 'filterCustomerData');
echo implode("\t", array_values($row)) . "\n";
}
exit;

1. Define a variable $column_names which is used to print the column name in first row of excel.
2. Run a loop through each row and used array_walk()  function to run each row in a user defined function.

STEP 5: Run script file in your environment:

If all goes well then the filename will be named “customers_data.xls” downloaded and saved in your computer. You can open this file in Excel which look like as following:

Click here to download the complete code.

With 13+ years of excellence in information technology, we provide web and app development technologies on various frameworks. You can also explore a wide range of Prestashop, Opencart, WooCommerce, Shopify, and Magento 2 ready-to-use eCommerce extensions.

In case you have queries/suggestions, you can reach out to our technical support portal at our email address support@knowband.com. Take a look at our bestseller module Prestashop One Page supercheckout  and  OpenCart eCommerce Mobile App.

You can also watch our latest videos here: