How to Export MySQL Database Data to Excel Using PHP

Recently I published an article How to Read CSV and Excel File in PHP Using PhpSpreadsheet and one of the readers asked about exporting MySQL database records to Excel file. The user may need their data in the Excel or CSV file so they can read or share it easily. In this article, we discussed how one can export data from a database to Excel and CSV files. In addition to this, we will also study how one can send this exported file as an attachment in the email.

Getting Started

For getting started, you first need to install PhpSpreadsheet library. I recommend to use Composer for installation of the library. Open the terminal in your project root directory and run the command:

composer require phpoffice/phpspreadsheet

PhpSpreadsheet is the library that provides support for reading and writing different types of file formats. Below is the screenshot of supported file formats.

File Supported

Our end goal is exporting database table records to Excel/CSV file. I am taking the ‘products’ table as an example. Let’s create a table ‘products’ in the database by running the below query.

CREATE TABLE `products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `product_name` varchar(255) NOT NULL,
 `product_sku` varchar(255) NOT NULL,
 `product_price` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Next, add some dummy entries in the table so you will see a few records in the exported file.

INSERT INTO `products` (`id`, `product_name`, `product_sku`, `product_price`) VALUES
(1, 'Apple', 'app_111', '$1000'),
(2, 'Lenovo', 'len_222', '$999'),
(3, 'Samsung', 'sam_689', '$1200'),
(4, 'Acer', 'ace_620', '$900');

After this create a db.php file where we would write the database connection.

db.php

<?php
$db_host = 'DATABASE_HOST';
$db_username = 'DATABASE_USERNAME';
$db_password = 'DATABASE_PASSWORD';
$db_name = 'DATABASE_NAME';

$db = new mysqli($db_host, $db_username, $db_password, $db_name);

if($db->connect_error){
    die("Unable to connect database: " . $db->connect_error);
}

Make sure to replace the placeholders with the actual values in the above code. In the next part of the tutorial, I am allocating a separate section on exporting data to Excel, to CSV, and sending the email with an exported file as an attachment.

Export MySQL Database Data to Excel Using PHP

You have installed the library and are ready with a database table with dummy entries. Now you can go ahead and write the actual code which will export an Excel file with data filled in it.

Create a index.php file in the root directory. In this file, I will write a SELECT query to fetch data from the database, write this data to Excel, and finally make it download automatically.

index.php

<?php
require_once "vendor/autoload.php";
require_once "db.php";

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

$spreadsheet = new Spreadsheet();
$Excel_writer = new Xlsx($spreadsheet);

$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();

$activeSheet->setCellValue('A1', 'Product Name');
$activeSheet->setCellValue('B1', 'Product SKU');
$activeSheet->setCellValue('C1', 'Product Price');

$query = $db->query("SELECT * FROM products ORDER BY id DESC");

if($query->num_rows > 0) {
    $i = 2;
    while($row = $query->fetch_assoc()) {
        $activeSheet->setCellValue('A'.$i , $row['product_name']);
        $activeSheet->setCellValue('B'.$i , $row['product_sku']);
        $activeSheet->setCellValue('C'.$i , $row['product_price']);
        $i++;
    }
}

$filename = 'products.xlsx';

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='. $filename);
header('Cache-Control: max-age=0');
$Excel_writer->save('php://output');

When you run this PHP file on a browser, an Excel file will start downloading automatically and this Excel sheet will have the following output.

Excel

Export MySQL Database Data to CSV Using PHP

In the previous section, we exported data to Excel file. If someone is looking to export data in a CSV file then you need to change a few lines in the above code.

csv.php

<?php
require_once "vendor/autoload.php";
require_once "db.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

$spreadsheet = new Spreadsheet();
$Excel_writer = new Csv($spreadsheet);

$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();

$activeSheet->setCellValue('A1', 'Product Name');
$activeSheet->setCellValue('B1', 'Product SKU');
$activeSheet->setCellValue('C1', 'Product Price');

$query = $db->query("SELECT * FROM products ORDER BY id DESC");

if($query->num_rows > 0) {
    $i = 2;
    while($row = $query->fetch_assoc()) {
        $activeSheet->setCellValue('A'.$i , $row['product_name']);
        $activeSheet->setCellValue('B'.$i , $row['product_sku']);
        $activeSheet->setCellValue('C'.$i , $row['product_price']);
        $i++;
    }
}

$filename = 'products.csv';

header('Content-Type: application/text-csv');
header('Content-Disposition: attachment;filename='. $filename);
header('Cache-Control: max-age=0');
$Excel_writer->save('php://output');

Send an Email with File as an Attachment

So far in this article, we have seen how to download the file as a CSV and Excel format. Some users may want to send the exported file as an attachment in the email. Let’s see how to achieve it.

First install the PHPMailer library using the command:

composer require phpmailer/phpmailer

After installing the library, you can use any SMTP server to send an email. It’s up to you. You may use your hosting provided SMTP server, AWS SES, or Gmail SMTP server. If you are going with the Gmail SMTP server read our article Send Email Using Gmail SMTP Server From PHP Script which explains configuration needed for it.

The code is written previously downloads the file automatically. But now, instead of making it downloadable, we will save the file in the directory and then send it as an attachment. In short, we will have code below for saving the file.

...
$filename = 'products.csv';

if (!file_exists('files')) {
    mkdir('files', 0755);
}
$Excel_writer->save('files/'.$filename);

Our final code to send attachment in an email will be as follows:

<?php
require_once "vendor/autoload.php";
require_once "db.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

//Import PHPMailer classes into the global namespace
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

$spreadsheet = new Spreadsheet();
$Excel_writer = new Csv($spreadsheet);

$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();

$activeSheet->setCellValue('A1', 'Product Name');
$activeSheet->setCellValue('B1', 'Product SKU');
$activeSheet->setCellValue('C1', 'Product Price');

$query = $db->query("SELECT * FROM products ORDER BY id DESC");

if($query->num_rows > 0) {
    $i = 2;
    while($row = $query->fetch_assoc()) {
        $activeSheet->setCellValue('A'.$i , $row['product_name']);
        $activeSheet->setCellValue('B'.$i , $row['product_sku']);
        $activeSheet->setCellValue('C'.$i , $row['product_price']);
        $i++;
    }
}

$filename = 'products.csv';

if (!file_exists('files')) {
    mkdir('files', 0755);
}
$Excel_writer->save('files/'.$filename);

// send as an attachment
$mail = new PHPMailer(true);
try {
    $mail->isSMTP();
    $mail->Host = 'SMTP_HOST';
    $mail->SMTPAuth = true;
    $mail->Username = 'SMTP_USERNAME';   //username
    $mail->Password = 'SMTP_PASSWORD';   //password
    $mail->SMTPSecure = 'ssl';
    $mail->Port = 465;

    $mail->setFrom('FROM_EMAIL_ADDRESS', 'FROM_NAME');
    $mail->addAddress('RECEPIENT_EMAIL_ADDRESS', 'RECEPIENT_NAME');
 
    $mail->addAttachment('files/'.$filename);
 
    $mail->isHTML(true);
    $mail->Subject = 'Our Exported File';
    $mail->Body    = 'Our Exported File';
 
    $mail->send();
    echo 'Message has been sent';
} catch (Exception $e) {
    echo 'Message could not be sent. Mailer Error: '. $mail->ErrorInfo;
}

Replace the placeholders with the actual values and email will be sent with an attachment to the recipient’s email address.

I hope you understand how to export MySQL database records to the Excel or CSV file and also send it as an attachment. Please share your thoughts and suggestions in the comment section below.

Related Articles

If you liked this article, then please subscribe to our YouTube Channel for video tutorials.

11 thoughts on “How to Export MySQL Database Data to Excel Using PHP

  1. Hye,

    I think there is a little mistake at line 33 :
    header(‘Content-Disposition: attachment;filename=”‘. $filename);
    you should better write :
    header(‘Content-Disposition: attachment;filename=”‘. $filename.'”‘);

    Thanks

  2. Hi, thank you for this amazing script, the script is working fine on my local developpement but when i migrate it on my website it doesnt work it give an http error 500, i dont know why?
    Can you please help me to fix this ? thank you in advance

Leave a Reply

Your email address will not be published. Required fields are marked *