wordpress hit counter
Use PHP to create Open XML Spreadsheet reports - OpenXML Developer - Blog - OpenXML Developer
Goodbye and Hello

OpenXmlDeveloper.org is Shutting Down

There is a time for all good things to come to an end, and the time has come to shut down OpenXmlDeveloper.org.

Screen-casts and blog posts: Content on OpenXmlDeveloper.org will be moving to EricWhite.com.

Forums: We are moving the forums to EricWhite.com and StackOverflow.com. Please do not post in the forums on OpenXmlDeveloper.org. Instead, please post in the forums at EricWhite.com or at StackOverflow.com.

Please see this blog post for more information about my plans moving forward.  Cheers, Eric

Use PHP to create Open XML Spreadsheet reports

Use PHP to create Open XML Spreadsheet reports

  • Comments 25

by Darcy Thomas

 

Dabbling with PHP and Open XML, I built a tool that automatically produces an Open XML spreadsheet file, complete with formulas and formatting from a CSV file. I used this to turn CSV files produced by my bank into something more workable. Hopefully, banks in future will eventually provide an export option to produce a complete spreadsheet file directly. Some new accounting applications are already offering exports to Excel  complete with formatting and formulas already applied. Accounting types love this because they don’t have to add it all themselves every time they download the information.

 

Back to the code; I have not done any PHP development since university, but some of the resources that I found useful getting started were:

 

XAMPP http://www.apachefriends.org/en/xampp.html as a solution stack to run the PHP scripts.

 

W3schools PHP http://www.w3schools.com/php/php_intro.asp, a simple guide to PHP

 

This is an exercise which centers on learning about PHP and primarily working with Open XML, so I haven’t put much emphasis into the security, error handling or input validation.

The CodePlex project PHPExcel at http://www.codeplex.com/PHPExcel makes this task very straightforward. The documentation that comes with PHPExcel is quite useful, and makes it easy to get started with the classes. There are many example recipes, which can easily be adapted to your own needs. These examples are worth looking at prior to implementing a piece of functionality because they will give you ideas on how to use the library.

 

In the initial stages, I was able to transform the following CSV file:

 

Into a formatted document, complete with a formula for calculating the balance, auto filtering, and some formatting:

 

 

When I initially started I thought that I would need to string up a bunch of RegEx to get the data out of my CSV file. I was pleasantly surprised that this can be handled with the PHPExcel classes. In fact, it takes just over 10 lines of code to open a CSV file and return it as a XSLX file.

<?php
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

// set headers to redirect output to client browser as a file download
header
('Content-Type: application/vnd.openXMLformats-officedocument.spreadsheetml.sheet');
header
('Content-Disposition: attachment;filename="myfile.xlsx"');
header
('Cache-Control: max-age=0');

//-----Create a reader, set some parameters and read in the file-----
$objReader = PHPExcel_IOFactory::createReader('CSV');
$objReader->setDelimiter(’,’);
$objReader->setEnclosure('');
$objReader->setLineEnding("\r\n");
$objReader->setSheetIndex(0);
$objPHPExcel = $objReader->load('C:/SampleData.csv’);

//-----Create a Writer and output the file to the browser-----
$objWriter2007 = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter2007->save('php://output');  //push out to the client browser
?>

Here is the result of the fairly vanilla piece of code:

 

Opening the CSV file and returning it as a XSLX file produced a result that hinted that some fancy formatting was in order. To create a title, I added a couple of rows above the first row, set the value and added some formatting to the text.


$objPHPExcel->getActiveSheet()->insertNewRowBefore(1, 2);//Some empty rows for space
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Transactions for the Month');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);

 

After adding a title:

Hint: A requirement was to put this code after the rest of the code, that I added later, and just before the CreateWriter functions. Adding the title inserts a few rows at the top of the spread sheet enabled the other functions to work by applying styles from top to bottom of the imported data. Keeping track of where that data would be displaced would add too much complexity to the breadth of this application.

 

Setting the metadata and print properties of the spreadsheet was another straightforward process:

 

//Set Print properties
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('Summary of Transactions for the Month'); //Set print header
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); //set printing orientation

//Set Metadata

$objPHPExcel->getProperties()->setCreator("Professor X");
$objPHPExcel->getProperties()->setLastModifiedBy("Professor X");
$objPHPExcel->getProperties()->setTitle("Monthly Account Transactions");
$objPHPExcel->getProperties()->setSubject("Monthly Account Transactions");
$objPHPExcel->getProperties()->setDescription("Summary of account activity of the previous month.");
$objPHPExcel->getProperties()->setKeywords("money account spending");
$objPHPExcel->getProperties()->setCategory("Finance");

One of the most useful functions I found is getHighestRow()

$highestRow = $objPHPExcel->getActiveSheet()->getHighestRow();

 

This function gives the number of the last row which has been added to the data set object we are working with. This is useful when performing an action to all the rows or cells in the spread sheet, or when you need to add rows at certain places above or below the bottom of the spread sheet.

 

Likewise you can use the function getHighestColumn() to perform similar actions with regards to columns.

An AutoFilter will allow a user to sort and filter the columns once they have downloaded the file. To do this you just need to pass setAutoFilter() the range of cells (including the headers) you want to apply this to:

 //Put an auto filter on the data
$objPHPExcel->getActiveSheet()->setAutoFilter('A1:' . $objPHPExcel->getActiveSheet()->getHighestColumn() . $objPHPExcel->getActiveSheet()->getHighestRow() );
 

Setting the width of a column can be done in two different ways. The width can be set explicitly by number of characters or it can be set to auto width. In order to have a title in column ‘A’, where the number of characters used would be wider than the rest of the data in that column; I set that column explicitly to 18 characters. For the remaining columns, I set the column width to auto size:

 

//set the width of the columns

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);//We will put a title in this column, so setting the width explicitly

$highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn(); //e.g., 'G'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //e.g., 6

for($column =1; $column < $highestColumnIndex; $column++) //start from 1 as columns are 0 indexed, but we don’t want to change the first row which we have already set explicitly
{
$objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($column))->setAutoSize(true);
}

After setting auto filter and auto width, you get the following:

To ensure easy reading, I set the fill and border of the cells which contain values retrieved from the CSV file. Setting individual cell formatting properties is relatively simple. However, a great piece of functionality is the ability to duplicate one cell's formatting to a range of cells:

 

//-----Put in some formatting to the table data to make it easier to read-----

$highestRow = $objPHPExcel->getActiveSheet()->getHighestRow();
$highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
$objPHPExcel->getActiveSheet()->insertNewRowBefore($highestRow + 1, 1);//Add one more row as a footer to the table

//set heading row to bold and put a border on the top and bottom rows
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($highestRow + 1) )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

//Loop through all of the rows and put in fill and borders on the edges
for($row =1; $row<$highestRow + 2; $row++)
{
//Set the colors, mid blue/grey for the top and bottom rows, with alternating white and light blue/grey
if ($row == 1 || $row ==$highestRow + 1) $color = 'FFCFDAE7';
else if ($row%2==0) $color = 'FFFFFFFF';
else $color = 'FFE7EDF5';

 
// set the fill type and apply the color
$objPHPExcel->getActiveSheet()->getStyle('A' . $row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A' . $row)->getFill()->getStartColor()->setARGB($color);

//duplicate the first cells style (fill plus the top and bottom borders) across the whole row
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('A' . $row), 'B' . $row . ':'. $highestColumn . $row); //copy style set in first column to the rest of the row

//Put some borders on the far left and right cells of the row
$objPHPExcel->getActiveSheet()->getStyle('A' . $row )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G' . $row )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
}

Through applying some fancy formatting of the data results in the following:

At present, I have mainly customized the properties of various cells. Not surprisingly, it is easy to modify the content of a cell: I did this before, when I set the text for the title in cell ‘A1’ using the setCellValue() function. It is also possible to set a cells value using an index (rather than the cells name e.g. ‘A1’). This can also be achieved with setCellValueByColumnAndRow(). A great pointer with this is that columns are 0-based indexed but rows are 1-based. So ‘A1’ and an index of (0,1) would refer to the same cell.

You can use this to not only insert data but also to insert formulas. Here I have put in a formula to work out the total number of the transactions for the month, as well as some text.

Note the variable: $amountColumn is at the top of the file in the zipped example:

 


$amountColumn = 'B'; // The is just the arbitrary column which contains the transaction amounts in the CSV file
//-----Put in a formula, which calculates the Balance of the transactions for the month and set conditional color-----
$highestRow = $objPHPExcel->getActiveSheet()->getHighestRow();

$rowToInsert = ($highestRow + 3);
$columnToInsert = PHPExcel_Cell::columnIndexFromString($amountColumn) -1;
$formula = '=SUM(' . $amountColumn . '2:' . $amountColumn . ($highestRow - 1) .')';
 
// i.e., if $amountColumn was 'B' and $highestRow was 31 last $formula would be =SUM(B2:B30) which would give the sum of all of the transaction values (in column 'B')

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($columnToInsert, $rowToInsert, $formula);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($columnToInsert, $rowToInsert)->getFont()->setBold(true);

$columnToInsert= $columnToInsert -1 ; //Show what the formula works out, to the left hence: $columnToInsert -1
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $rowToInsert, 'Balance: ');
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($columnToInsert, $rowToInsert)->getFont()->setBold(true);


A useful thing to know is that you can get a value from a cell using getValue(), for example:

$objPHPExcel->getActiveSheet()->getCell('B8')->getValue();
or
$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(1, 8)->getValue();



To insert conditional formatting, such as coloring the balance to red when less than zero:

 

//Set color of the calculated value to red, if values are a negative number

//Setup conditionals
$objConditional1 = new PHPExcel_Style_Conditional();
$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);
$objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN);
$objConditional1->addCondition('0');
$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$objConditional1->getStyle()->getFont()->setBold(true);

$conditionalStyles = $objPHPExcel->getActiveSheet()->getStyle($amountColumn . $rowToInsert)->getConditionalStyles();
array_push
($conditionalStyles, $objConditional1);
$objPHPExcel->getActiveSheet()->getStyle($amountColumn . $rowToInsert)->setConditionalStyles($conditionalStyles);

Final result:

In summary, my experience of creating an Open XML spreadsheet using PHP convinced me that this is easy to achieve in a wide variety of situations. Any of the suggested examples and ideas could be applied to other common day or business practices. 

Attachment: PHPExample.zip
Page 1 of 2 (25 items) 12