PHP Excel Export Code (Data to File)

by Vincy. Last modified on September 23rd, 2022.

Export data to an excel file is mainly used for taking a backup. When taking database backup, excel format is a convenient one to read and manage easily. For some applications exporting data is important to take a backup or an offline copy of the server database.

This article shows how to export data to excel using PHP. There are many ways to implement this functionality. We have already seen an example of data export from MySQL.

This article uses the PHPSpreadSheet library for implementing PHP excel export.

It is a popular library that supports reading, and writing excel files. It will smoothen the excel import-export operations through its built-in functions.

The complete example in this article will let create your own export tool or your application.
php excel export

About this Example

It will show a minimal interface with the list of database records and an “Export to Excel” button. By clicking this button, it will call the custom ExportService created for this example.

This service instantiates the PHPSpreadsheet library class and sets the column header and values. Then it creates a writer object by setting the PHPSpreadsheet instance to output the data to excel.

Follow the below steps to let this example run in your environment.

  1. Create and set up the database with data exported to excel.
  2. Download the code at the end of this article and configure the database.
  3. Add PHPSpreadSheet library and other dependencies into the application.

We have already used the PHPSpreadsheet library to store extracted image URLs.

1) Create and set up the database with data exported to excel

Create a database named “db_excel_export” and import the below SQL script into it.


-- Table structure for table `tbl_products`

CREATE TABLE `tbl_products` (
  `id` int(8) NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` double(10,2) NOT NULL,
  `category` varchar(255) NOT NULL,
  `product_image` text NOT NULL,
  `average_rating` float(3,1) NOT NULL

-- Dumping data for table `tbl_products`

INSERT INTO `tbl_products` (`id`, `name`, `price`, `category`, `product_image`, `average_rating`) VALUES
(1, 'Tiny Handbags', 100.00, 'Fashion', 'gallery/handbag.jpeg', 5.0),
(2, 'Men\'s Watch', 300.00, 'Generic', 'gallery/watch.jpeg', 4.0),
(3, 'Trendy Watch', 550.00, 'Generic', 'gallery/trendy-watch.jpeg', 4.0),
(4, 'Travel Bag', 820.00, 'Travel', 'gallery/travel-bag.jpeg', 5.0),
(5, 'Plastic Ducklings', 200.00, 'Toys', 'gallery/ducklings.jpeg', 4.0),
(6, 'Wooden Dolls', 290.00, 'Toys', 'gallery/wooden-dolls.jpeg', 5.0),
(7, 'Advanced Camera', 600.00, 'Gadget', 'gallery/camera.jpeg', 4.0),
(8, 'Jewel Box', 180.00, 'Fashion', 'gallery/jewel-box.jpeg', 5.0),
(9, 'Perl Jewellery', 940.00, 'Fashion', 'gallery/perls.jpeg', 5.0);

-- Indexes for dumped tables

-- Indexes for table `tbl_products`
ALTER TABLE `tbl_products`

-- AUTO_INCREMENT for dumped tables

-- AUTO_INCREMENT for table `tbl_products`
ALTER TABLE `tbl_products`

2) Download the code and configure the database

The source code contains the following files. This section explains the database configuration.

excel export file structure

Once you download the excel export code from this page, you can find DataSource.php file in the lib folder. Open it and configure the database details in it as below.

class DataSource

    const HOST = 'localhost';

    const USERNAME = 'root';

    const PASSWORD = '';

    const DATABASENAME = 'db_excel_export';


3) Add PHPSpreadSheet library and other dependencies into the application

When you see the PHPSpreadsheet documentation, it provides an easy to follow installation steps.

It gives the composer command to add the PHPSpreadsheet and related dependencies into the application.

composer require phpoffice/phpspreadsheet

For PHP version 7

Add the below specification to the composer.json file.

    "require": {
        "phpoffice/phpspreadsheet": "^1.23"
    "config": {
        "platform": {
            "php": "7.3"

then run

composer update

Note: PHPSpreadsheet requires at least PHP 7.3 version.

How it works

Simple interface with export option

This page fetches the data from the MySQL database and displays it in a grid form. Below the data grid, this page shows an “Excel Export” button.

By clicking this button the action parameter is sent to the URL to call the excel export service in PHP.


require_once __DIR__ . '/lib/Post.php';
$post = new post();
$postResult = $post->getAllPost();
$columnResult = $post->getColumnName();
if (! empty($_GET["action"])) {
    require_once __DIR__ . '/lib/ExportService.php';
    $exportService = new ExportService();
    $result = $exportService->exportExcel($postResult, $columnResult);
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="./style.css" type="text/css" rel="stylesheet" />
    <div id="table-container">
        <table id="tab">
                    <th width="5%">Id</th>
                    <th width="35%">Name</th>
                    <th width="20%">Price</th>
                    <th width="25%">Category</th>
                    <th width="25%">product Image</th>
                    <th width="20%">Average Rating</th>
            if (! empty($postResult)) {
                foreach ($postResult as $key => $value) {
                    <td><?php echo $postResult[$key]["id"]; ?></td>
                    <td><?php echo $postResult[$key]["name"]; ?></td>
                    <td><?php echo $postResult[$key]["price"]; ?></td>
                    <td><?php echo $postResult[$key]["category"]; ?></td>
                    <td><?php echo $postResult[$key]["product_image"]; ?></td>
                    <td><?php echo $postResult[$key]["average_rating"]; ?></td>
        <div class="btn">
            <form action="" method="POST">
                    href="<?php echo strtok($_SERVER["REQUEST_URI"]);?><?php echo $_SERVER["QUERY_STRING"];?>?action=export"><button
                        type="button" id="btnExport" name="Export"
                        value="Export to Excel" class="btn btn-info">Export
                        to Excel</button></a>

PHP model calls prepare queries to fetch data to export

This is a PHP model class that is called to read data from the database. The data array will be sent to the export service to build the excel sheet object.

The getColumnName() reads the database table column name array. This array will supply data to form the first row in excel to create a column header.

The getAllPost() reads the data rows that will be iterated and set the data cells with the values.


class Post

    private $ds;

    public function __construct()
        require_once __DIR__ . '/DataSource.php';
        $this->ds = new DataSource();

    public function getAllPost()
        $query = "select * from tbl_products";
        $result = $this->ds->select($query);
        return $result;

    public function getColumnName()
        $query = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=N'tbl_products'";
        $result = $this->ds->select($query);
        return $result;

PHP excel export service

This service helps to export data to the excel sheet. The resultant file will be downloaded to the browser by setting the PHP header() properties.

The $postResult has the row data and the $columnResult has the column data.

This example instantiates the PHPSpreadSheet library class and sets the column header and values. Then it creates a writer object by setting the spreadsheet instance to output the data to excel.


use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Calculation\TextData\Replace;
require_once __DIR__ . '/../vendor/autoload.php';

class ExportService

    public function exportExcel($postResult, $columnResult)
        $spreadsheet = new Spreadsheet();
        $spreadsheet->getActiveSheet()->SetCellValue('A1', ucwords($columnResult[0]["COLUMN_NAME"]));
        $spreadsheet->getActiveSheet()->SetCellValue('B1', ucwords($columnResult[1]["COLUMN_NAME"]));
        $spreadsheet->getActiveSheet()->SetCellValue('C1', ucwords($columnResult[2]["COLUMN_NAME"]));
        $spreadsheet->getActiveSheet()->SetCellValue('D1', ucwords($columnResult[3]["COLUMN_NAME"]));
        $spreadsheet->getActiveSheet()->SetCellValue('E1', str_replace('_', ' ', ucwords($columnResult[4]["COLUMN_NAME"], '_')));
        $spreadsheet->getActiveSheet()->SetCellValue('F1', str_replace('_', ' ', ucwords($columnResult[5]["COLUMN_NAME"], '_')));
        $rowCount = 2;
        if (! empty($postResult)) {
            foreach ($postResult as $k => $v) {
                $spreadsheet->getActiveSheet()->setCellValue("A" . $rowCount, $postResult[$k]["id"]);
                $spreadsheet->getActiveSheet()->setCellValue("B" . $rowCount, $postResult[$k]["name"]);
                $spreadsheet->getActiveSheet()->setCellValue("C" . $rowCount, $postResult[$k]["price"]);
                $spreadsheet->getActiveSheet()->setCellValue("D" . $rowCount, $postResult[$k]["category"]);
                $spreadsheet->getActiveSheet()->setCellValue("E" . $rowCount, $postResult[$k]["product_image"]);
                $spreadsheet->getActiveSheet()->setCellValue("F" . $rowCount, $postResult[$k]["average_rating"]);
                $rowCount ++;

                ->setRowHeight(- 1);
        $writer = IOFactory::createWriter($spreadsheet, 'Xls');
        header('Content-Type: text/xls');
        $fileName = 'exported_excel_' . time() . '.xls';
        $headerContent = 'Content-Disposition: attachment;filename="' . $fileName . '"';


Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

Leave a Reply

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

↑ Back to Top

Share this page