Export MySQL Data to Excel using PHP 8

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 8 export Excel 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).

Export Data to Excel with PHP

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 using PHP.

• The $fileName variable defines the name of the excel file.
• The Content-Disposition and Content-Type headers force the excel file to download.
• Run the loop through each key/value pair in the $data array.
• Display column names as the first row using the $flag variable.
• The PHP array_walk() function is used to filter the data together with filterData() function.

// Excel file name for download 
$filename = "data_export_" . date('Ymd_His') . ".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;

Export Data from Database to Excel (.xls) with PHP and MySQL

In this example script, we will export data from the MySQL database to an excel file using PHP.

Create Database Table:
For this example, we will create a members table in the MySQL database from which data will be exported to Excel.

Here is the SQL statement to create a new MySQL table named members with the specified fields:

CREATE TABLE members (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    gender ENUM('Male', 'Female', 'Other') DEFAULT NULL,
    country VARCHAR(50) NOT NULL,
    created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Field Details:

• id: Integer, primary key, auto-incremented for unique user identification.
• first_name / last_name: Variable-length strings up to 50 characters.
• email: Variable-length string (max 100 chars), must be unique.
• gender: Enum field restricted to ‘Male’, ‘Female’, or ‘Other’ for controlled input.
• country: Variable-length string, stores user’s country.
• created: Date and time of record creation, defaults to the current timestamp.
• status: Boolean-style field (1 = Active, 0 = Inactive), with a default of 1 for active users.

Database Configuration (dbConfig.php):
Create a new file named dbConfig.php to store the database connection configuration.

• Specify the database host (DB_HOST), username (DB_USER), password (DB_PASS), and name (DB_NAME) as per your database credentials.

<?php 
 
// Database configuration - update these values to match your environment 
define('DB_HOST', 'localhost'); 
define('DB_USER', 'root'); 
define('DB_PASS', 'root'); 
define('DB_NAME', 'codexworld_db'); 
 
// Create a new mysqli connection 
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME); 
 
// Check connection 
if ($conn->connect_error) { 
    // In production, avoid echoing raw errors. Log them instead. 
    die('Database connection failed: ' . $conn->connect_error); 
} 
 
?>

Export Data from Database:
Create a new file named index.php to export data from the MySQL database and download it as an excel file using PHP.

• The filterData() function is used to filter string before added to the excel data row.
• $filename – Define the name of the excel file to be downloaded.
• $fields – Define the column named of the excel sheet.
• $excelData – Add the first row to the excel sheet as a column name.
• Fetch member’s data from the database and add to the row of the excel sheet.
• Define headers to force the file to download.
• Render data of excel sheet.

<?php  
 
// Include database configuration file  
require_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_export_' . date('Ymd_His') . '.xls'; 
  
// Column names  
$fields = array('ID', 'NAME', 'EMAIL', 'GENDER', 'COUNTRY', 'CREATED', 'STATUS');  
  
// Display column names as first row  
$excelData = implode("\t", array_values($fields)) . "\n";  
  
// Query to fetch records from database  
$sql = "SELECT id, first_name, last_name, email, gender, country, created, status FROM members ORDER BY id ASC"; 
$result = $conn->query($sql); 
 
// Output each row of the data  
if ($result && $result->num_rows > 0) { 
    while ($row = $result->fetch_assoc()) { 
        $status = $row['status'] == 1 ? 'Active' : 'Inactive'; 
        $name = trim($row['first_name'] . ' ' . $row['last_name']); 
 
        $lineData = array($row['id'], $name, $row['email'], $row['gender'], $row['country'], $row['created'], $status); 
        array_walk($lineData, 'filterData');  
        $excelData .= implode("\t", array_values($lineData)) . "\n";  
    }  
} 
  
// Headers for download  
header("Content-Type: application/vnd.ms-excel");  
header("Content-Disposition: attachment; filename=\"$filename\"");  
  
// Render excel data  
echo $excelData;  
  
// cleanup 
if ($result) $result->free(); 
$conn->close(); 
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.

Export Data to Excel and Save it in an XLSX file using PHP

In this example script, we will export data from the MySQL database in an excel file using PHP and save it as a .xlsx file.

PHP XLSX Generator Library (PhpXlsxGenerator.class.php):
The PhpXlsxGenerator class handle the Excel file generation process and export data in a .xlsx file using PHP.

• You can download the PhpXlsxGenerator library file from here.

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 will export data from the MySQL database and download it as an excel file (.xlsx) using PHP.

• Include the database configuration file to connect to the MySQL database.
• Include the PhpXlsxGenerator class file to generate the Excel file.
• Define the name of the Excel file to be downloaded.
• Define the column headings in an array and add it as the first row of the export data array.
• Fetch records from the database and store them in the export data array.
• Create an instance of the PhpXlsxGenerator class using the fromArray() method and pass the export data array.
• Call the downloadAs() method and set the name of the Excel file to download it.
• Finally, free the result set and close the database connection.

<?php 
 
// Include database configuration file 
require_once 'dbConfig.php'; 
 
// Include XLSX generator library 
require_once 'PhpXlsxGenerator.class.php'; 
 
// Excel file name for download 
$filename = 'members_export_' . date('Ymd_His') . '.xlsx'; 
 
// Define header row 
$excelData[] = array('ID', 'NAME', 'EMAIL', 'GENDER', 'COUNTRY', 'CREATED', 'STATUS'); 
 
// Query to fetch the same fields used in the UI 
$sql = "SELECT id, first_name, last_name, email, gender, country, created, status FROM members ORDER BY id ASC"; 
$result = $conn->query($sql); 
 
// Add row's data to excel data array 
if ($result && $result->num_rows > 0) { 
    while ($row = $result->fetch_assoc()) { 
        $status = $row['status'] == 1 ? 'Active' : 'Inactive'; 
        $name = trim($row['first_name'] . ' ' . $row['last_name']); 
 
        $lineData = array($row['id'], $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); 
 
// cleanup 
if ($result) $result->free(); 
$conn->close(); 
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');

Export HTML Table Data to Excel using JavaScript

Conclusion

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 PHP 8 export Excel code to add export data functionality in the web application using PHP.
Download the complete source code from Download Source Code button below.