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.

Key Takeaways

• PHPExcel, a subset of PHPOffice, enables pure PHP manipulation of Excel files, avoiding the need for Interop and Windows dependency.
• The tutorial demonstrates creating an Excel file with game data, additional analytics, and charts using PHPExcel to enhance data presentation and analysis.
• Required setup includes PHP version above 5.2.0, enabling specific PHP extensions, and using Composer for PHPExcel installation.
• PHPExcel allows for detailed Excel file manipulations including setting properties, populating worksheets, inserting formulas, and creating visually appealing charts.
• Final output involves saving the Excel file in a downloadable format, ensuring charts are included, with a note on PHPExcel’s current inability to handle pie charts effectively in Excel 2013.

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:

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

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:

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

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:

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

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:

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

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.

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

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:

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

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:

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

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:

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

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:

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

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:

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

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

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

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:

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

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:

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

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.

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

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:

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

Next, we will create the Plot Area and Legend:

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

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:

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

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:

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

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:

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

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

How Can I Install PHPExcel in My Project?

To install PHPExcel in your project, you need to use Composer, a dependency management tool in PHP. First, you need to install Composer if you haven’t done so. After installing Composer, navigate to your project directory in the terminal and run the command composer require phpoffice/phpexcel. This command will download and install PHPExcel in your project.

How Can I Create a Basic Excel File Using PHPExcel?

To create a basic Excel file using PHPExcel, you need to first create an instance of the PHPExcel class. Then, you can set the properties of the Excel file such as the title, description, and author. After that, you can add data to the Excel file by accessing the cells and setting their values. Finally, you can save the Excel file by creating a writer and calling the save method.

How Can I Add a Chart to an Excel File Using PHPExcel?

To add a chart to an Excel file using PHPExcel, you need to first create a data series. The data series represents the data that will be displayed in the chart. After creating the data series, you can create a chart and add the data series to it. Then, you can set the properties of the chart such as the title and the legend. Finally, you can add the chart to a worksheet by calling the addChart method.

How Can I Read Data from an Excel File Using PHPExcel?

To read data from an Excel file using PHPExcel, you need to first create a reader. The reader is responsible for opening the Excel file and reading its contents. After creating the reader, you can load the Excel file by calling the load method. Then, you can access the data in the Excel file by accessing the cells and getting their values.

How Can I Write Data to an Existing Excel File Using PHPExcel?

To write data to an existing Excel file using PHPExcel, you need to first create a reader and load the Excel file. Then, you can access the cells in the Excel file and set their values. After modifying the data, you can save the changes by creating a writer and calling the save method.

How Can I Format Cells in an Excel File Using PHPExcel?

PHPExcel provides a rich set of methods for formatting cells in an Excel file. You can set the font, color, alignment, border, and number format of a cell. You can also merge cells, set the width and height of a cell, and apply styles to a cell.

How Can I Handle Large Excel Files Using PHPExcel?

Handling large Excel files with PHPExcel can be challenging due to memory limitations. However, PHPExcel provides a cell caching feature that can help reduce memory usage. By enabling cell caching, PHPExcel will store cell data in cache instead of memory, which can significantly reduce memory usage.

How Can I Generate a PDF from an Excel File Using PHPExcel?

PHPExcel supports generating PDFs from Excel files. To generate a PDF, you need to create a writer of type PDF and call the save method. Note that you need to have the appropriate PDF rendering library installed in your project.

How Can I Add Images to an Excel File Using PHPExcel?

PHPExcel allows you to add images to an Excel file. To add an image, you need to create a drawing object, set the path of the image, and specify the coordinates where the image should be placed in the worksheet.

How Can I Handle Errors and Exceptions in PHPExcel?

PHPExcel uses exceptions to handle errors. When an error occurs, PHPExcel will throw an exception. You can catch these exceptions using a try-catch block and handle them appropriately. This allows you to control the flow of your program and provide meaningful error messages to the user.

The above is the detailed content of Generate Excel Files and Charts with PHPExcel. For more information, please follow other related articles on the PHP Chinese website!