PHP Excel Export Code (Data to File)

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.

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.

structure.sql

--
-- 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`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_products`
--
ALTER TABLE `tbl_products`
  MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

2) Download the code and configure the database

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

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.

<?php 
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.

index.php

<?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);
}
?>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="./style.css" type="text/css" rel="stylesheet" />
</head>
<body>
    <div id="table-container">
        <table id="tab">
            <thead>
                <tr>
                    <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>
                </tr>
            </thead>
            <tbody>
            <?php
            if (! empty($postResult)) {
                foreach ($postResult as $key => $value) {
                    ?>
                <tr>
                    <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>
                </tr>
            <?php
                }
            }
            ?>
            </tbody>
        </table>
        <div class="btn">
            <form action="" method="POST">
                <a
                    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>
            </form>
        </div>
    </div>
</body>
</html>

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.

lib/Post.php

<?php
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.

lib/ExportService.php

<?php
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->getProperties()->setTitle("excelsheet");
        $spreadsheet->setActiveSheetIndex(0);
        $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"], '_')));
        $spreadsheet->getActiveSheet()
            ->getStyle("A1:F1")
            ->getFont()
            ->setBold(true);
        $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 ++;
            }
            $spreadsheet->getActiveSheet()
                ->getStyle('A:F')
                ->getAlignment()
                ->setWrapText(true);

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

Download

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.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
    • How to Export Contact as vCard using PHP
    • Export DataTables Data using PHP and MySQL
    • Extract images from URL in excel with PHP using PhpSpreadsheet
    • Database Data Export to Excel File using PHP
    • PHP Excel Export Code (Data to File)
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js