Create and Download Excel File Using PHP

For creating and downloading an excel file in PHP, we use the PHPExcel library. This library is used to read as well as write to an excel file. It provides a set of classes for the PHP programming language, which allow you to write to and read from different spreadsheet file formats, like Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML etc.

In this tutorial, we will import an MS Excel 2007 file which has xlsx extension. Note that the xls extension also represents excel file but it refers to MS Excel 2003 files and since it has become outdated, we will not work on that.

We create two files – index.php where we show the contents of the excel file in a table and excel-download.php where we create the excel file using classes provided in PHPExcel and then download the excel file. The following code explains how to use this library to create an excel file, write data to it and download the file.

index.php – Excel File Contents

<h2>Excel File Downloader</h2>

<center>
    <table border=1 style="width:50%;">

        <tr>
            <th>Product</th>
            <th>Quantity</th>
            <th>Price</th>
            <th>Total Price</th>
        </tr>

        <tr>
            <td>Motherboard</td>
            <td>10</td>
            <td>5</td>
            <td>50</td>
        </tr>

        <tr>
            <td>Processor</td>
            <td>6</td>
            <td>3</td>
            <td>18</td>
        </tr>

        <tr>
            <td>Memory</td>
            <td>10</td>
            <td>2.5</td>
            <td>25</td>
        </tr>

        <tr>
            <td>Total</td>
            <td>26</td>
            <td>-</td>
            <td>93</td>
        </tr>

    </table>

<br><br>

<button onclick="window.location='excel-download.php'">Click here to download this data in excel file</button>

</center>

excel-download.php – Creating the Excel File

<?php

/** Set default timezone (will throw a notice otherwise) */
date_default_timezone_set('Asia/Kolkata');

// include PHPExcel
require('../PHPExcel.php');

// create new PHPExcel object
$objPHPExcel = new PHPExcel;

// set default font
$objPHPExcel->getDefaultStyle()->getFont()->setName('Calibri');

// set default font size
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);

// create the writer
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");



/**

 * Define currency and number format.

 */

// currency format, € with < 0 being in red color
$currencyFormat = '#,#0.## \€;[Red]-#,#0.## \€';

// number format, with thousands separator and two decimal points.
$numberFormat = '#,#0.##;[Red]-#,#0.##';



// writer already created the first sheet for us, let's get it
$objSheet = $objPHPExcel->getActiveSheet();

// rename the sheet
$objSheet->setTitle('My sales report');



// let's bold and size the header font and write the header
// as you can see, we can specify a range of cells, like here: cells from A1 to A4
$objSheet->getStyle('A1:D1')->getFont()->setBold(true)->setSize(12);



// write header

$objSheet->getCell('A1')->setValue('Product');
$objSheet->getCell('B1')->setValue('Quanity');
$objSheet->getCell('C1')->setValue('Price');
$objSheet->getCell('D1')->setValue('Total Price');

// we could get this data from database, but here we are writing for simplicity

$objSheet->getCell('A2')->setValue('Motherboard');
$objSheet->getCell('B2')->setValue(10);
$objSheet->getCell('C2')->setValue(5);
$objSheet->getCell('D2')->setValue('=B2*C2');

$objSheet->getCell('A3')->setValue('Processor');
$objSheet->getCell('B3')->setValue(6);
$objSheet->getCell('C3')->setValue(3);
$objSheet->getCell('D3')->setValue('=B3*C3');

$objSheet->getCell('A4')->setValue('Memory');
$objSheet->getCell('B4')->setValue(10);
$objSheet->getCell('C4')->setValue(2.5);
$objSheet->getCell('D4')->setValue('=B4*C4');

$objSheet->getCell('A5')->setValue('TOTAL');
$objSheet->getCell('B5')->setValue('=SUM(B2:B4)');
$objSheet->getCell('C5')->setValue('-');
$objSheet->getCell('D5')->setValue('=SUM(D2:D4)');


// autosize the columns
$objSheet->getColumnDimension('A')->setAutoSize(true);
$objSheet->getColumnDimension('B')->setAutoSize(true);
$objSheet->getColumnDimension('C')->setAutoSize(true);
$objSheet->getColumnDimension('D')->setAutoSize(true);


//Setting the header type
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="file.xlsx"');
header('Cache-Control: max-age=0');

$objWriter->save('php://output');

/* If you want to save the file on the server instead of downloading, replace the last 4 lines by 
    $objWriter->save('file.xlsx');
*/

?>

In this code, the index page simply shows the data which will be shown in the excel file.

Working of excel-download.php file

Step 1 – Specify time zone

We start by specifying a time zone. This is necessary because PHPExcel issues a warning if it is not specified.

Step2 – Create object

We create a new PHPExcel object

Step 3 – Font family and size

We then set the default font size and font family that we want in the excel file.

Step 4 – Create writer

In this step, the writer to write to the file is created and the type of file is also specified. The writer also creates the first sheet of the excel file.

Step 5 – Currency and number format

Although this is not important, we can create a currency and number format if we are going to use them in the file.

Step 6 – Rename active sheet

Since the writer has already created the first sheet, we get the sheet and rename it.

Step 7 – Set cell values

We can now specify the values of the cells. We can also perform mathematical operations involving two cells here. We can fetch data from database and then set the values of the cells. However, for simplicity, we are simply using hard-coded data to populate the cells.

Step 8 – Autosize the cell size

In this step, we automate the cell size according the length of the data in that column in which the cell is located.

Step 9 – Specify headers

Lastly, we specify the headers of the MS Excel 2007 file and produce the output. However, we can also save the file on the server itself but for that we use a little different code in this step.

Index Page

Index page

Downloading File

Downloading File

Downloaded File contents

Downloaded File Contents

Please feel free to download the source code for reference.

View Demo Download source

Other related article can be found at this link Importing Excel File Using PHP

This content has been helpful to you?

Thanks for contributing!

Yes No