Effortlessly Export MySQL Data to Excel with PHP

Exporting data from MySQL to Excel can be a common requirement in many applications. Whether it’s generating reports, data analysis, or simply sharing records, being able to convert MySQL data into Excel format is indispensable. In this post, we will walk through how to efficiently export MySQL data to Excel using PHP.

Table of Contents

1. Understanding the Basics
2. Setting Up Your Environment
3. Fetching Data from MySQL
4. Creating the Excel File
5. Code Example
6. Conclusion

Understanding the Basics

Before diving into the code, it’s important to grasp how PHP interacts with MySQL and exports data. PHP is a server-side scripting language, often used in conjunction with MySQL databases to create dynamic web applications.

Excel files are typically saved in CSV (Comma-Separated Values) format or XLSX (Microsoft Excel Open XML Format Spreadsheet). While different, CSV files are easier to create since they are plain text. However, we will focus on generating Excel files for their advanced features like styling, formulas, and compatibility.

Setting Up Your Environment

For this tutorial, you will need:

• A web server (like Apache or Nginx).
• PHP installed on your server (preferably version 7.0 or newer).
• A MySQL database with some data to work with.
• PHPExcel library (or PhpSpreadsheet for newer projects).

Installation of PhpSpreadsheet

Instead of PHPExcel, it’s best to use PhpSpreadsheet, as it is actively maintained. You can install it using Composer. Run the following command in your terminal:

composer require phpoffice/phpspreadsheet

Make sure you're already comfortable with Composer and have it set up in your project.

Fetching Data from MySQL

The next step is to connect to your MySQL database and retrieve the data you intend to export. Below is a simple example of how to do this.

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        print_r($row); // For debugging
    }
} else {
    echo "0 results";
}
$conn->close();

Explanation

• First, we establish a connection with the database using credentials.
• We then execute a SQL query to fetch all records from the users table.
• The results are then looped through to display or handle as necessary.

Creating the Excel File

We can now create an Excel file using the PhpSpreadsheet library. Below is a step-by-step guide to do this.

Basic Structure to Generate an Excel File

Here’s how you can structure your PHP script to create an Excel file from MySQL data:

require 'vendor/autoload.php'; // Add this to include PhpSpreadsheet

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Set header row
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Email');

// Fetch data and populate Excel
$rowNum = 2; // Start from the second row because the first is the header
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $sheet->setCellValue('A' . $rowNum, $row['id']);
        $sheet->setCellValue('B' . $rowNum, $row['name']);
        $sheet->setCellValue('C' . $rowNum, $row['email']);
        $rowNum++;
    }
}

// Save the file
$writer = new Xlsx($spreadsheet);
$filename = 'users.xlsx';

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');

$writer->save('php://output');
exit();

Explanation

• We load the PhpSpreadsheet library using Composer's autoload functionality.
• A new spreadsheet object is created, and we set up the header row.
• We fetch data from the database, looping through each row to populate the Excel file.
• Finally, we set the headers for our response to ensure the browser prompts a download for our generated Excel file.

Code Example

Combining everything, your final PHP script might look something like this:

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM users";
$result = $conn->query($sql);

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Email');

$rowNum = 2;
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $sheet->setCellValue('A' . $rowNum, $row['id']);
        $sheet->setCellValue('B' . $rowNum, $row['name']);
        $sheet->setCellValue('C' . $rowNum, $row['email']);
        $rowNum++;
    }
}

$writer = new Xlsx($spreadsheet);
$filename = 'users.xlsx';

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');

$writer->save('php://output');
$conn->close();
exit();
?>

To Wrap Things Up

Exporting MySQL data to Excel with PHP is a straightforward process that enhances data reporting capabilities in web applications. The combination of MySQL's powerful data management features and PHP's flexibility ensures that you can automate and streamline the export process.

For more detailed documentation on PhpSpreadsheet, you can refer to the official documentation here. Additionally, if you're interested in further exploring how PHP interacts with different databases, visit PHP.net.

By following this guide, you can efficiently create Excel files from your MySQL database, making data sharing and reporting easier than ever. Happy coding!