How to Export Data from MySQL to Excel using PHP

Export mysql data to excel in PHP; In this tutorial, you will learn how to export data from mysql to excel file using PHP.

How to Export Data from MySQL to Excel using PHP

Steps to export data from MySQL database to Excel file using PHP MySQL:

Step 1: Install PHPExcel Library
Step 2: Connect to MySQL Database
Step 3: Fetch Data from MySQL
Step 4: Generate Excel File
Step 5: Export/Download the Excel File
Step 6: Run PHP Script

Step 1: Install PHPExcel Library

First of all, open your terminal or cmd and execute the following command into it to install the PHPExcel library into your php project:

cd /your-php-project
composer require phpoffice/phpexcel

Step 2: Connect to MySQL Database

Next, you need to create a PHP file name db.php, which is used to establish a connection to your MySQL database using the mysqli extension.

<?php
// Replace with your MySQL database credentials
$host = 'localhost';
$user = 'your_mysql_username';
$password = 'your_mysql_password';
$database = 'your_database_name';

$conn = new mysqli($host, $user, $password, $database);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

Step 3: Fetch Data from MySQL

Next, you need to retrieve the data you want to export from the MySQL database. So, create export.php file and add the following code into it:

<?php

$sql = "SELECT id, name, email, salary FROM employees";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    $data = array();
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
}

Step 4: Generate Excel File

Next, create the Excel file and add the data to it using the PHPExcel library.

<?php
// ... (Previous code)
require 'vendor/autoload.php'; // Path to autoload.php generated by Composer

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

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

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

// Adding data rows
$row = 2;
foreach ($data as $employee) {
    $sheet->setCellValue('A' . $row, $employee['id']);
    $sheet->setCellValue('B' . $row, $employee['name']);
    $sheet->setCellValue('C' . $row, $employee['email']);
    $sheet->setCellValue('D' . $row, $employee['salary']);
    $row++;
}

// Save the Excel file
$writer = new Xlsx($spreadsheet);
$filename = 'employees_data.xlsx'; // Change the filename if needed
$writer->save($filename);

Step 5: Export/Download the Excel File

Finally, offer the generated Excel file for download.

<?php
// ... (Previous code)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
readfile($filename);
exit;

Step 6: Run PHP Script

Now, you can visit export.php in your browser, and the browser will prompt you to download the Excel file containing the MySQL data.

Conclusion

That’s it! You have learned how to export data from a MySQL database to an Excel file using php.