PHP
Article

Generate Excel Files and Charts with PHPExcel

By Taylor Ren

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!

Free Guide:

7 Habits of Successful CTOs

"What makes a great CTO?" Engineering skills? Business savvy? An innate tendency to channel a mythical creature (ahem, unicorn)? All of the above? Discover the top traits of the most successful CTOs in this free guide.

  • Mark Baker

    Nice article, very useful seeing an example using Charts

    Note that GH-426 (the issue with 33chartcreate-pie.php) has now been resolved, and requires that the plotGrouping for Pie and Donut charts should be NULL

    • Taylor Ren

      Noticed that in the response posted to the bug report that I filed. Thanks for the quick fix.

  • http://kanosa.com Free classified ads

    Grate article.

  • llstarscreamll

    Thanks!! Very nice article… ^_^

  • Abdallah Meckki

    Thanks Taylor for sharing, very useful…

  • http://tutsave.com Fabien Zet

    Nice article, can easily make google sheet clone using a combination phpexcel and handsontable :D
    I use phpexcel so long and it’s really more and more powerful

  • http://rakesh.tembhurne.com/ Rakesh Tembhurne

    Really helpful article. Exporting to excel is a common task. The simplest solution people use is exporting to .csv file which can easily opened in Excel. If the data is simple like a single table, do you think using this method will be a better option compared to PHP’s fputcsv() method?

    • Taylor Ren

      Actually, when dumping a large number of structured data, the speed is of top concern. So my blind guess is fputcsv() might be faster if we are talking about millions. But that is rare case, I believe.

      Most of the time, exporting is the only thing that we need to do in PHP so I believe a comparison between different types of exporting will be of help.

  • Taylor Ren

    Just a heads-up:

    The below mentioned fix for Pie Chart in Excel 2013 is working. An updated source file has been pushed to the Git repo.

  • Mr B

    I used PHPExcel on another job a couple of years ago and ran into loads of memory issues with large data sets. Has any of that been remedied?

    • el_crespo

      No, We had to use csv files for big data. (sorry for my english)

    • nwrman

      Same here. I ended up falling back on CSV. I was thinking on may be doing batches, like one Excel file every thousand or so records at a time, and at the end merging all the temp files into a single file, but it seemed a lot of trouble.

  • Ajeesh

    Can we convert a table into a chart on the right side of the table. I mean just telling table start and end cell positions …

    Thanks in advance :)

  • Jon Sanchez Lizarraga

    Thanks a lot for your documentation but I couldn’t do to make the charts fill it the
    $objWorksheet->fromArray because I don’t know how many arrays I have.
    How it is possible to resolve this issue?

    Thanks!

  • http://www.amutechnologies.com/ AMU Technologies

    Thanks Taylor for sharing very informative article.

  • César Camilo Cruz Cáceres

    Thank you!!

  • kanima

    good graph

  • Márlon Etiene

    Hello!
    Very nice article!
    Have some form of export the chart to image?

  • Michael

    How to export to existing excel? i mean i have the template of excel that i’ve already built. But how can i export data from db to the template of excel? I’ve already tried it but the template of excel changed after i exported to excel. ie. the width and the height changed into the new excel but still have the data. Could you help me to export data to existing excel? Thanks before

  • SK

    Very Nice Article, It Helps me a lot.
    Could u please share any example for chart.

    Thankx.

  • Andreea Costa

    Hi, your article is very helpful, but I have two problems now.
    1. I need to create a line chart, but only with data from some cells, I saw you do this to add data

    $dsv=array(
    new PHPExcel_Chart_DataSeriesValues(‘Number’, ‘Data!$D$2:$D$91’, NULL, 90)
    );
    I my case I need to add only data from D2, D8, D60, D90.
    I tried to do this in this way:

    $dsv[]=array(
    new PHPExcel_Chart_DataSeriesValues(‘Number’, ‘Data!$D$2’, NULL, 1) );

    $dsv[]=array(
    new PHPExcel_Chart_DataSeriesValues(‘Number’, ‘Data!$D$8’, NULL, 1) );
    or

    $dsv=array(
    new PHPExcel_Chart_DataSeriesValues(‘Number’, ‘Data!$D$2;$D$8;$D$60;$D$90’, NULL, 4) );
    But does not work. I get an error when try to open the xlsx: the file format or file extension is not valid.

    2. I need to have lines chart like in your example but without the marker on the line (just a simple line)

    Can you help me please!

  • C U Anand

    I was successful in creating the chart, but I want to display this chart directly. How can I do this?

  • Oki Erie Rinaldi

    How to set the angle of x-axis labels? I can’t find the way..

  • Rishi

    i want to add that sheets data in database how should i ? and how to read that sheet
    in silex can you please help me

    • Taylor Ren

      Of course you can. You just hook the data so that it will be retrieved from a database then populate the data row by row. Silex should have no issue doing the same job because the installation of PHPExcel is via composer.

      • Rishi

        i don’t want to create a new sheet
        sheet uploaded by user and the data in sheet i want that data in database
        understand?

        • Taylor Ren

          Then you just read the sheet and dump the data back into database.

          • Rishi

            It would be great if you give a code demo

  • Joseph Harmon

    Thanks for the awesome article. I was wondering if it is possible to inset a new row with the data in an existing .XLSX file instead of creating a new file.

  • Arnold Bryan Tangalan

    I’m new to PHP and I want to learn how to generate an excel file base on the values from a database. I cant seem to find any tutorial. TIA.

Recommended
Sponsors
Because We Like You
Free Ebooks!

Grab SitePoint's top 10 web dev and design ebooks, completely free!

Get the latest in PHP, once a week, for free.