How to Easily Generate Charts and Excel Files with PHPExcel

Do you need to keep a track on the daily invoices you pay to your supplier? Or do you need to keep an account on all the supplies being used for the production? If yes then Microsoft office has already gifted you with the Excel sheets which make your tracking effortless or submission of daily reports quite non strenuous. Yes! Excel is always helpful when it comes to performing such tasks. You can also maintain a statistical record about the daily requirements for the production or for sales and a lot more. Hence, Excel has made your work easier to perform, and made your life stress free.

With the development of new and advanced programs day in and day out, excel is being taken over by the PHP Excel. PHP Excel also aims at generating reports and analysis of data but in a simpler manner. Other tasks which are supported by PHP excel includes:

• Representing spreadsheets and saving them for future use.
• Eases large calculations
• Converting worksheets to spreadsheets
• Helps in customizing the columns
• Merging cells and also adding formulas and data to the individual cell
• Cells can be protected using password
• Aids in setting cell height and width
• Aids in altering the font style and supports different fonts
• Provides worksheet level protection and secures the entered data
• Helps in adding hyperlinks to the data entered
• Helps in adding images to our worksheet in addition to adding comments to individual cells

Generally all our spreadsheets are saved in Excel format. Following the trend is easy but bringing about a change is the call of the hour. Converting an Excel file into a PHP Excel file is what we are going to focus upon in this article;

Once you finish reading the following article, you would obtain:

• A spreadsheet highlighting the performance of, lets say, NBA team- Los Angeles Lakers in the year 2013-2014 season with teams score, wins and looses status and the number of matches played.
• How to convert the data into an Excel 2013 file
• The excel spreadsheet will generate additional analytic data with the aid of PHP and Excel.
• This article will use Silex as the MVC framework and Twig will be used as the template engine. Keep in mind to specify the necessary dependencies in the composer.json file correctly without any error.

To basic requirements before you begin using PHP Excel are as follows:

You should have a PHP Office version of 5.2.0 or above.

It should enable the 3 PHP Extensions which are most commonly used:

• php_zip which is essential for converting 2007 excel sheets
• php_xm1
• php_gd2

Install the library using the composer. By this time your database should be running.

The data dump for the following file would be set up as lakers.sql and the data can be retrieved by usage of a simple SQL statement, mentioned below:

$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);
});

The entry point for our Silex application would be the index.php code. There are going to be 2 routes defined. Routes are defined by the usage of / symbol. Route combined with the export button “/export” will help in back end handling and exporting the data to Excel. We would be discussing the export and its various functions in order to understand the manipulation of Excel using the PHP Excel library.

Excel application with its Meta data usage

As soon as we click on the Excel icon, a workbook displaying 3 empty worksheets (in Excel 2013, only 1) will appear and you can start filling in your data. There are individual cells for data entry while columns representing a certain category of information. In order to represent an Excel file we can use the following coding:

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

Every Excel file has its own Meta data which is created with the activation of the excel file. The Meta data for each excel file can be viewed by pressing the combination of alt-enter or by right clicking on the file icon and clicking on the properties. For setting the properties in the dialog box the method of “setXXX” needs to be utilized where XXX is the property name to be entered into the dialog box. This method is quite self explanatory and will enter the data directly into the dialog box without any difficulties.

$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’)
;

Worksheet and Cell Population

Worksheet is the empty canvas which you can populate using the data which you need to analyze or report using the Excel sheet. You can manipulate the worksheet either by adding data or formulas to the individual cells, formatting the style or size, doing auto filtering, inserting charts or adding comments to the individual cells.

To get a reference, we use the following coding:

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

The worksheets are always 0 indexed. The first sheet is always titled as worksheet and you can rename it according to your likes using the setTitle method already explained. To enter data into a particular cell we use either of the one way:

For entering the heading for a particular topic or adding any description for a particular cell we will use the “setCellValue” method and enter the data one by one.

We will use the Array form method for entering any structured data which comes from the SQL select statement.

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

The FromArray method is as follows and takes into account the following 3 parameters:

• The source of the data that too in array form,
• A filler value if the data is null in value,
• Or as cell reference in order to start filling up the individual cell

We can also style the rows with our desired heading as we like for this particular sheet we can include the header row as the ID, season and wins or losses.

$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);

The FromArray method can be done either by using the “get” methods to retrieve the style object and change it and is done for the “background fill” style. While the other method is to declare a “style” array and specific the style to change and what you want the style to be changed to. Then “applyfromArray” is applied to style the batches. Through this, we can the font as well as the alignment. Both of the above mentioned methods supports the range as their parameter with the following command ($header=’a1:h1’;) which is the most convenient method.

Finally if we want to adjust the column width so that the maximum displayed length is visible in each of the column.

Unfortunately a range parameter is not supported so we use a “for” loop for this. If we save the file now, the XLSX file will be filled and saved with data and with proper formatting.

Addition of new worksheet along with the insertion of formulas

You can use a separate sheet to depict the original data while using another sheet to display the summary and any other analytic data. For inserting a new sheet we follow the following steps:

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

The “addsheet” method follows the below mentioned parameters:

• $ews2: we are to insert the excel worksheet instance,
• $location: depicts the index of the following worksheet. So it would mean that the 0 will the first one while 1 would be the last one.

With the insertion of the worksheet by the following method we can enter data into the cells and apply styles as mentioned earlier and use the following 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)’);

As you might have observed that it is similar to what we have done in the previous sections. To perform the necessary calculation, we need to input the formula string into the Excel file. The sheet will look like as follows:

The % symbol is inserted by the following code:

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

The font size, weight and alignment are correctly applied and merging is also done as desired but the “setautosize” method has failed. But it is not always important that the width calculation will always work. So don’t worry and relax.

Inserting Charts

There are in-built charts in Excel which are much more effective in representing information. The first chart which we will learn to create is the line chart showing the ups and downs during the Lakers game. The task of coding for charting is a lengthy procedure and full code can be found in the “addchart1” and “addChart2”.

Data Series Labels give a label to each of the data we need to enter. For example we want to show the scores of Lakers and their opponents, then we label it as : Self Score and Opponent Score and will 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),
);

X Axis Value Label helps in identifying label for the X Axis. For example: Lakers scored 86 and their opponents score 89, so

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

For Data Series Values, we again use self score and opponent score from row 2 to 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),

);

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

Data Series Values

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

Now, we focus on creating 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 information about the data series and the chart as a whole and also indicates whether chart shows values, percentage etc, while a legend provides a visual presentation of the data groups. And we can begin creating a chart:

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

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

The title of the chart can be created by following the given code:

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

Once the chart is created we need to adjust the position and size of the chart and then insert it into the worksheet.

Saving your worksheet

It can be done by the following:

$writer = \PHPExcel_IOFactory::createWriter($ea, ‘Excel2007’);

$writer->setIncludeCharts(true);
$writer->save(‘output.xlsx’);

Using a factory pattern a writer can save the file and a format will be specified. Be sure to include the “setIncludeCharts(true)” or else the chart won’t be saved. With Excel 2013, PHP Excel cannot produce a usable pie chart. And the data sheet will look as follows:

[Last Updated: 22nd Dec, 2016]