Export data feature is very useful where the data is saved on the local drive for offline uses. Export data to file functionality provides a user-friendly way to maintain a large number of data in the web application. There are various file formats are available to export data and download it as a file. Microsoft Excel is a widely used spreadsheet format that organizes and maintains data.
Generally, export data functionality is used in the data management section of the web application. 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 will show you how to export data to Excel in PHP.
The example PHP script lets you integrate export data to excel functionality. With one click, the user can export data from the MySQL database to Excel and download it in MS Excel file format (.xls/.xlsx).
In this example script, we will export data from the array (defined in the script) to an excel file.
The $data variable holds the data in array format which will be exported to Excel using PHP.
$data = array(
array("NAME" => "John Doe", "EMAIL" => "john.doe@gmail.com", "GENDER" => "Male", "COUNTRY" => "United States"),
array("NAME" => "Gary Riley", "EMAIL" => "gary@hotmail.com", "GENDER" => "Male", "COUNTRY" => "United Kingdom"),
array("NAME" => "Edward Siu", "EMAIL" => "siu.edward@gmail.com", "GENDER" => "Male", "COUNTRY" => "Switzerland"),
array("NAME" => "Betty Simons", "EMAIL" => "simons@example.com", "GENDER" => "Female", "COUNTRY" => "Australia"),
array("NAME" => "Frances Lieberman", "EMAIL" => "lieberman@gmail.com", "GENDER" => "Female", "COUNTRY" => "United Kingdom")
);
The filterData() function is used to filter string before added to the excel sheet row.
function filterData(&$str) <
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
>
The following code helps to export data in excel and download it as a file.
// Excel file name for download
$fileName = "codexworld_export_data-" . date('Ymd') . ".xlsx";
// 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;
In this example script, we will export data from the MySQL database in an excel file using PHP.
Create Database Table:
For this example, we will create a members table with some basic fields in the MySQL database. The members table holds the records which will be exported to excel.
CREATE TABLE `members` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL, `country` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `created` datetime NOT NULL, `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active | 0=Inactive', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Database Configuration (dbConfig.php):
The following code is used to connect the database using PHP and MySQL. Specify the database host ( $dbHost ), username ( $dbUsername ), password ( $dbPassword ), and name ( $dbName ) as per your database credentials.
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName = "codexworld";
// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
// Check connection
if ($db->connect_error) <
die("Connection failed: " . $db->connect_error);
>
Export Data from Database:
The following code helps to export data from the MySQL database and download it as an excel file.
// Load the database configuration file
include_once 'dbConfig.php';
// Filter the excel data
function filterData(&$str) <
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
>
// Excel file name for download
$fileName = "members-data_" . date('Y-m-d') . ".xls";
// Column names
$fields = array('ID', 'FIRST NAME', 'LAST NAME', 'EMAIL', 'GENDER', 'COUNTRY', 'CREATED', 'STATUS');
// Display column names as first row
$excelData = implode("\t", array_values($fields)) . "\n";
// Fetch records from database
$query = $db->query("SELECT * FROM members ORDER BY id ASC");
if($query->num_rows > 0) <
// Output each row of the data
while($row = $query->fetch_assoc()) <
$status = ($row['status'] == 1)?'Active':'Inactive';
$lineData = array($row['id'], $row['first_name'], $row['last_name'], $row['email'], $row['gender'], $row['country'], $row['created'], $status);
array_walk($lineData, 'filterData');
$excelData .= implode("\t", array_values($lineData)) . "\n";
>
>else <
$excelData .= 'No records found. '. "\n";
>
// Headers for download
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$fileName\"");
// Render excel data
echo $excelData;
exit;
The above-mentioned solution helps you export data from the database to Excel and save it as a .xls file. If you want to export to Excel in an XLSX file, use the following process.
PHP XLSX Generator Library:
The PhpXlsxGenerator class handle the Excel file generation process and export data in a .xlsx file using PHP.
Note that: All the required files including this PhpXlsxGenerator library are included in our source code, you do not require to install it separately.
Export Data from Database to Excel in PHP:
The following code export data from the database and save it as an Excel file (.xlsx) using PHP and MySQL.
// Load the database configuration file
include_once 'dbConfig.php';
// Include XLSX generator library
require_once 'PhpXlsxGenerator.php';
// Excel file name for download
$fileName = "members-data_" . date('Y-m-d') . ".xlsx";
// Define column names
$excelData[] = array('ID', 'FIRST NAME', 'LAST NAME', 'EMAIL', 'GENDER', 'COUNTRY', 'CREATED', 'STATUS');
// Fetch records from database and store in an array
$query = $db->query("SELECT * FROM members ORDER BY id ASC");
if($query->num_rows > 0) <
while($row = $query->fetch_assoc()) <
$status = ($row['status'] == 1)?'Active':'Inactive';
$lineData = array($row['id'], $row['first_name'], $row['last_name'], $row['email'], $row['gender'], $row['country'], $row['created'], $status);
$excelData[] = $lineData;
>
>
// Export data to excel and download as xlsx file
$xlsx = CodexWorld\PhpXlsxGenerator::fromArray( $excelData );
$xlsx->downloadAs($fileName);
exit;
?>
If you want to save XLSX file on the server instead of downloading it, use the saveAs() method of PhpXlsxGenerator class.
$xlsx->saveAs('members-data.xlsx');
If you want to add an export option to the data list, the export to excel feature is perfect for it. With the export option, the user can download the data in an excel file and save it in a local drive. You can use this simple code to add export data functionality in the web application using PHP.
Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request