Recently I published an article on How to Read CSV and Excel Files in PHP Using PhpSpreadsheet.  One of the readers asked how to use PhpSpreadsheet to export MySQL database records to the Excel file. The user may need their MySQL data in the Excel or CSV file to read or share it easily. In this article, we discussed how one can export data from a database to Excel and CSV files using PHP. 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 the PhpSpreadsheet library. I recommend using Composer for the 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 the supported file formats.

Our end goal is to export database table records to the Excel/CSV file. For this, we require a few entries in our database. As an example, I am creating the ‘products’ table by running the below SQL 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` (`product_name`, `product_sku`, `product_price`) VALUES
('Apple', 'app_111', '$1000'),
('Lenovo', 'len_222', '$999'),
('Samsung', 'sam_689', '$1200'),
('Acer', 'ace_620', '$900');

After this create a config.php file where we write the code for database connection.

config.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. This file will be included later to fetch records from the products table.

In the next part of the tutorial, I am allocating a separate section for exporting data to Excel, exporting data 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 also have a database table with few entries. Now you can go ahead and write the actual code which will export an Excel file with data filled in it.

Create export-to-excel.php file in the root directory. In this file, I will write a SQL query to fetch data from the database. This fetched data will be written to Excel and sent the Excel file to the browser to download automatically.

export-to-excel.php

<?php
require_once "vendor/autoload.php";
require_once "config.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");
 
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 code on the browser, an Excel file will be downloaded automatically and the Excel sheet will have the following entries.

Export MySQL Database Data to CSV Using PHP

In the previous section, we exported data to an 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.

export-to-csv.php

<?php
require_once "vendor/autoload.php";
require_once "config.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");
 
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 the File as an Attachment

We have seen how to download the file with data in Excel/CSV 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’s SMTP server, AWS SES, or Gmail SMTP server. If you are going with the Gmail SMTP server read our article Send Email Using Gmail API and PHPMailer which explains the configuration needed for it.

The previous code download Excel/CSV 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 write the 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 the attachment in an email will be as follows:

<?php
require_once "vendor/autoload.php";
require_once "config.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");
 
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 = 'Products Sheet';
    $mail->Body    = 'Products Sheet';
 
    $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 an email will be sent with an attachment to the recipient’s email address.

I hope you understand how to export MySQL database data 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

• How to Transfer Files to Remote Server in PHP
• Send Email Using Mailjet in PHP
• How to Convert HTML to PDF in PHP

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