Generate Excel Files and Charts with PHPExcel

After my article “How To Make Microsoft Word Documents with PHP” (using Interop capability under Windows), there were quite a few comments urging a pure PHP implementation, i.e., only using a universal PHP library to manipulate Office files.

In this article, we will see how to use a PHPExcel library to provide an “Export to Excel” function in a web app so that the user can export the data into an Excel 2007/2013 file for further analysis.

NOTE: There are a few PHP libraries that can provide Excel (and Office) file manipulations. The lib we use here is called PHPExcel, a subset of PHPOffice, which can be cloned here.

Objectives

After this tutorial, we will get:

• A sheet showing the game information (date played, teams, score, win/lose status) of my favorite NBA team – LA Lakers, in its 2013-14 season.
• A button that will export the data into an Excel 2013 file.
• That Excel file will be populated with some additional analytic data and a chart also generated by PHP and Excel.

Let’s get started.

Preparation

To use PHPExcel, we must have PHP version above 5.2.0. There are also 3 PHP extensions to be enabled: php_zip (which is essential to operate Office 2007 formats), php_xml and php_gd2 (optional, but required for exact column width auto-calculation).

Next, install the library via Composer.

Of course, we should have our database up and running. The data dump for this tutorial (lakers.sql) has been uploaded to the repo associated with this article. The data is retrieved with a simple SQL statement: “select * from lakers” (total 90 records, including 8 pre-season and 82 regular season games).

Also, this demo uses Silex as the MVC framework. Twig will be used as the template engine. Make sure the necessary dependencies are correctly specified in your composer.json file.

The index file

index.php will be the entry point for our Silex application. There will be two routes defined:

$app->get('/', function () use ($app)
{
    $c=new trExcel\Excel();
    return $c->index($app);
});

$app->post('/export', function () use ($app)
{
    $c=new trExcel\Excel();
    return $c->export($app);
});

Route '/' will be our entry point and display the data and the “Export” button. Route '/export' will do the back end handling process that actually exports to Excel. Both functions are wrapped in a user-defined class (classExcel.php). In the rest of this article, we will focus on this file – or more precisely, the export function and related functions defined in this file and discuss several important aspects of Excel manipulation using the PHPExcel library.

Excel application and its meta data

When we click the icon to launch Excel, the Excel application starts. Under normal circumstances, it will also contain a workbook with 3 (in Excel 2013, only 1) worksheets. The worksheet is the “canvas” we play with. These are the two most important terms in Excel. Other important terms may include: cell, range, etc.

To instantiate an Excel file, we use:

$ea = new \PHPExcel(); // ea is short for Excel Application

An Excel application instance usually is mapped to a physical Excel file. It has its own meta data to describe the file that we create. The meta data is shown when we “Alt-Enter” an Excel file (or right click on that file and choose “Properties”):

The properties shown in the above dialog will have corresponding setXXXX methods to set these properties, where XXXX is almost identical to the property names listed in the dialog:

$ea->getProperties()
   ->setCreator('Taylor Ren')
   ->setTitle('PHPExcel Demo')
   ->setLastModifiedBy('Taylor Ren')
   ->setDescription('A demo to show how to use PHPExcel to manipulate an Excel file')
   ->setSubject('PHP Excel manipulation')
   ->setKeywords('excel php office phpexcel lakers')
   ->setCategory('programming')
   ;

The methods (setXXXX) are quite self explanatory and map to the “Properties” dialog quite well. There are some discrepancies in the mapping, but they are not too difficult for us to make the connection (e.g., “Authors” will be mapped to setCreator).

Worksheet and cell population

The worksheet is probably the object that we’ll manipulate the most: populating cells with data or formulas, applying styles, doing data filtering, inserting a chart, etc.

To get the reference to a worksheet, we use:

$ews = $ea->getSheet(0);
$ews->setTitle('Data');

The sheets in a workbook are always 0-indexed. So the 1st (and up to now the only) sheet will be Sheet Zero. The default name of this sheet is always “Worksheet” and we can change it with the setTitle method.

To populate a cell/cells, we have at least two options:

1. For those headings, titles, other descriptive items, we will populate them one by one using setCellValue method.
2. For structured data, most of which comes from an SQL select statement, we will use the fromArray method.

$ews->setCellValue('a1', 'ID'); // Sets cell 'a1' to value 'ID 
    $ews->setCellValue('b1', 'Season');
	...
    //Fill data 
    $ews->fromArray($data, ' ', 'A2');

The fromArray method takes 3 parameters:
1. the data source, in array form;
2. a “filler” value in case the data is NULL;
3. a cell reference to start the filling (from left to right, then up to down).

NOTE: When we use PDO to fetch the data, a simple $res = $q->fetchAll(\PDO::FETCH_ASSOC); call will force the returned result data set to contain an associated array only, without index. If fetchall is called without the option PDO::FETCH_ASSOC, the result set will actually contain two sets of identical data, one in associated array form, one in indexed form and will create duplicates in the Excel file when using fromArray.

We may also want to style the header row (ID, Season, etc). To do that, we also have two ways:

$header = 'a1:h1';
$ews->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('00ffff00');
$style = array(
    'font' => array('bold' => true,),
    'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,),
    );
$ews->getStyle($header)->applyFromArray($style);

One way is to use some get methods to retrieve the style object that we want to change and change it. We do this for the “background fill” style.

The other is to declare a “style” array and specify the styles we want to change and what to change them to. Then, we use applyFromArray to apply the styles in a batch. Here we changed the font and the alignment.

Both methods support range as their parameter ($header='a1:h1';), which is very convenient.

Finally, we may want to adjust the column width so they will fit the max displayed length in each column:

for ($col = ord('a'); $col <= ord('h'); $col++)
{
    $ews->getColumnDimension(chr($col))->setAutoSize(true);
}

Unfortunately, this does not support a range parameter, so we use a for loop to make this happen.

If we save the file now – we will discuss saving later – we will see that the XLSX file is filled with the data and properly formatted:

Adding another sheet and inserting formulas

I always use a separate sheet to store the original data and at least one more sheet to display the summary and/or analytic information.

To insert a new worksheet, we do:

$ews2 = new \PHPExcel_Worksheet($ea, 'Summary');
$ea->addSheet($ews2, 0);
$ews2->setTitle('Summary');

The addSheet method takes two parameters.

• $ews2: the Excel worksheet instance that we are to insert;
• $location: the index of this worksheet. So 0 means it should become the 1st one. -1 means it should be the last one.

With the worksheet inserted, we can populate the cells in this worksheet as usual and apply styles. In this sheet, we will use formulas:

$ews2->setCellValue('b2', '=COUNTIF(Data!G2:G91, "W")-COUNTIF(Data!G2:G9, "W")');
$ews2->setCellValue('b3', '=COUNTIF(Data!G2:G91, "L")-COUNTIF(Data!G2:G9, "L")');
$ews2->setCellValue('b4', '=b2/(b2+b3)');

You see, it is no different from what we have done in the previous section. The formula string is just like the one we will input in an Excel file to perform the necessary calculation.

NOTE: Please pay special attention to the cell reference (G2:G91). A lazy way of writing that formula is to use a range like G:G. This works fine when there is NO chart in the sheet. If there is a chart, the G:G notation will fail, throwing an exception.

This “Summary” sheet looks like this:

The % shown in cell B4 is set by the following code:

$ews->getStyle('b4')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);

Please note some styling issues here. For A1, I have applied the following style:

$ews2->setCellValue('a1', 'Lakers 2013-2014 Season');
$style = array(
    'font' => array('bold' => true, 'size' => 20,),
    'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT,),
);
$ews2->mergeCells('a1:b1');
$ews2->getStyle('a1')->applyFromArray($style);
$ews2->getColumnDimension('a')->setAutoSize(true);

The result shows that the font weight, font size, and alignment are correctly applied. The merging of A1 and B1 into A1 is also done correctly. But, the setAutoSize method fails on this merged cell. The result is that this cell (A1) is still squeezed. This means auto width calculation will not always work. Well, not a big deal, anyway.

A picture is worth a thousand of words

It is always nice to have a visual representation of our data, so the chart will come in handy. Excel has a rich set of built-in charts for us to choose from. PHPExcel can tap into almost all of these. The first chart we are to create is a line chart showing the ups and downs of the scores in each game of the Lakers team and its opponent.

Creating a chart is a lengthy coding job, even with the support of a library. The full code of this process can be found in the addChart1 and addChart2 methods that reside in our classExcel.php file. I will just explain the key steps.

• Data Series Labels

A data series label identifies data series by giving it a name (label). In our case, if we want to show the scores of Lakers and their opponent, we are looking at two labels: Self Score and Opponent Score. Their labels can be found in D1 and E1 respectively:

$dsl=array(
                new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$D$1', NULL, 1),
                new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$E$1', NULL, 1),
                
            );

A Data Series Label is actually a \PHPExcel_Chart_DataSeriesValues instance. The constructor contains four parameters:

1. The type. For a label, no doubt it should be “String”;
2. Source. It is in D1 or E1;
3. Format. Normally, providing NULL is sufficient and the default format will be used;
4. Count. How much data in the Source. It should normally be 1.

---

• X Axis Value Label

This identifies the label for the X Axis. For example, on “2013-11-15”, Lakers scored 86 and their opponent scored 89. The “2013-11-15” is the label for those two scores. For our case, we will use the “Date Played” column from row 2 to row 91:

$xal=array(
                new \PHPExcel_Chart_DataSeriesValues('String', 'Data!$F$2:$F$91', NULL, 90),
            );

The constructor is the same, so are the parameters.

• Data Series Values

We will use “Self Score” (column D) and “Opponent Score” (column E). Both are from row 2 to row 91.

$dsv=array(
                new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$D$2:$D$91', NULL, 90),
                new \PHPExcel_Chart_DataSeriesValues('Number', 'Data!$E$2:$E$91', NULL, 90),
            );

After we have the above 3 critical variables, we can set up the Data Series. In Excel, a data series contains the following information essential to creating a chart:

• Chart Type
• Grouping
• Count of data series values
• Data Series Label
• X Axis Value Label
• Data Series Values

And the constructor is called by simply passing all these parameters:

$ds=new \PHPExcel_Chart_DataSeries(
                    \PHPExcel_Chart_DataSeries::TYPE_LINECHART,
                    \PHPExcel_Chart_DataSeries::GROUPING_STANDARD,
                    range(0, count($dsv)-1),
                    $dsl,
                    $xal,
                    $dsv
                    );

Next, we will create the Plot Area and Legend:

$pa=new \PHPExcel_Chart_PlotArea(NULL, array($ds));
$legend=new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);

A plot area contains a layout of the chart and the Data Series. The layout can specify whether the chart shall show values, percentages, etc. We can use NULL so that a default layout will be used.

A legend is used to provide a visual representation of the data groups.

And now, finally, we can create the chart:

$chart= new \PHPExcel_Chart(
                    'chart1',
                    $title,
                    $legend,
                    $pa,
                    true,
                    0,
                    NULL, 
                    NULL
                    );

$chart->setTopLeftPosition('K1');
$chart->setBottomRightPosition('M5');
$ews->addChart($chart);

The only new parameter in this constructor is the name of the chart. “chart1” will be good enough. A title of the chart can be created via:

$title=new \PHPExcel_Chart_Title('Any literal string');

After the chart is created, we set its position and size by setting its top-left and bottom right corners’ coordinate and insert it into the worksheet.

NOTE: Most of the time, a cell reference is case insensitive, but please use CAPITAL letter + number when there is a chart in the sheet.

Save the file

As the last step, we save the file so that the user can download it:

$writer = \PHPExcel_IOFactory::createWriter($ea, 'Excel2007');
            
$writer->setIncludeCharts(true);
$writer->save('output.xlsx');

It uses a factory pattern to create a writer object to save the file. A format will be specified (we use “Excel2007” format in our case).

Be sure that we have setIncludeCharts(true) in the saving process, otherwise the chart won’t be there.

Remember when I said PHPExcel could tap into almost all chart types? One exception that this lib can’t do well in Excel 2013 is that it does not produce a usable pie chart. In our output.xlsx and our code, we have actually created a pie chart (done in addChart1) but when opening the output.xlsx, Excel 2013 will prompt an error. If we choose continue, the pie chart will be lost and only the line chart (done in addChart2) will be preserved. A bug report has already been filed into its Git repo.

Now the “Data” sheet will look like this:

and a zoom-in view of the chart. It is correctly positioned and sized:

Conclusion

In this article, we demonstrated how to use pure PHP and a pure PHP lib (PHPExcel) to manipulate Excel. We created a new file, populated the data, added in a new sheet and analytic data, inserted a chart, and finally saved the file for download.

In general, I found this PHPExcel lib worth trying and easy to learn. Its code insight in an IDE can help us a lot when programming.

We have not covered other common features in an Excel file – we’ll write a followup article on those if the interest is high enough. Let us know!

Its official documentation and examples are a nice place to find detailed API usage explanations and workable code snippets for common tasks. Read them thoroughly. They can be found in the cloned repo. Give this library a try and let us know of your own use cases!

Frequently Asked Questions (FAQs) about Generating Excel Files and Charts with PHPExcel