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.
You can reach us at support@knowband.com for any queries. We also provide all kinds of customization needed on an online eCommerce store. You can also find the best modules of Prestashop, Opencart, Magento and Magento 2 at Knowband.
Take a look at our bestseller module Prestashop One Page supercheckout here:
and also visit here: OpenCart eCommerce Mobile App
