wordpress hit counter
Creating an Open XML spreadsheet in PHP - OpenXML Developer - Blog - OpenXML Developer

Creating an Open XML spreadsheet in PHP

Blog

Samples, Demos, and Reference Articles

Creating an Open XML spreadsheet in PHP

  • Comments 1

Author: Maarten Balliauw

This article covers the dynamic creation of invoices using PHPExcel (www.codeplex.com/phpexcel). It covers the example code “05featuredemo.php” which can be found in the PHPExcel package project on Codeplex. Here's what the output document will look like:

Getting started…

First of all, you will need some software: PHP, the php_zip library, and PHPExcel. In the /Tests/ folder of PHPExcel, you can find 05featurdemo.php, which is also in the ZIP file attached to this article. After you have these, you can start scripting.

Let’s cover the first few lines of code, as they are quite important to finding the PHPExcel class files, and the creation of error-free code:

/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
ini_set('include_path', ini_get('include_path').';../Classes/');

/** PHPExcel */
include 'PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';

In this piece of code, we set the error_reporting(E_ALL), so PHP treats all warnings as errors. This helps creating error-free code. This is not really within the scope of PHPExcel, but I think this is a best-practise all PHP developers should make use of.

We also set the include path, which is the path PHP uses for resolving class files. The “../Classes/” simply tells PHP to look for the PHPExcel classes in that relative folder. This can be another path on your system! You can also register PHPExcel as a system-wide library, by adding the absolute path to PHP’s include-path setting in php.ini.

Afterwards, we include the PHPExcel class, and the PHPExcel_Writer_Excel2007 class, which will perform the translation of an in-memory spreadsheet to Excel2007 file format.

Really getting started!

In the previous part, I basically covered setting up the environment and necessary includes. Now it’s time to get to some real coding!

Just create a new PHPExcel object, like this:

$objPHPExcel = new PHPExcel();

We will immediately add some document meta-data, which can be usefull to describe the document you are creating and also simplifies searching for the document in a Sharepoint environment. You can add different meta-data properties, in this article I’ll just set the two most important ones:

$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
...

Now you all know my name :-)

By default, PHPExcel creates one worksheet in your spreadsheet, but it is a good idea to explicitly tell PHPExcel to use the first worksheet:

$objPHPExcel->setActiveSheetIndex(0);

This way, you can simply use the PHPExcel “getActiveSheet()” property to make calls to the worksheet. If you want to do this on another worksheet later, you just have to change the active sheet index and not all code which explicitly uses worksheet number 2…

Adding data to the sheet is just plain easy:

$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Invoice');
$objPHPExcel->getActiveSheet()->setCellValue('E1', '#12566');

Also, all Open XML formulas are supported. Creating a formula-based cell is just as easy. Don’t forget the equals sign at the start, and PHPExcel will treat this as a formula:

$objPHPExcel->getActiveSheet()->setCellValue('E4', '=C4*D4');

Now let’s add some column header data which we will style afterwards:

$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Product Id');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Description');
$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Price');
$objPHPExcel->getActiveSheet()->setCellValue('D3', 'Amount');
$objPHPExcel->getActiveSheet()->setCellValue('E3', 'Total');

Each cell supports different styling properties. Let’s review some of them. A style always has a property named “getFont()”, which reveals all font properties like font name, size, boldness, text color …

$objPHPExcel->getActiveSheet()
->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()
->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()
->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()
->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
...

The same story goes for cell borders. You can simply set the border color, thickness, style, … using the “getBorders()” property:

$objPHPExcel->getActiveSheet()->getStyle('A3')
->getBorders()->getTop()->setBorderStyle
(PHPExcel_Style_Border::BORDER_THIN);

Another cool thing is adding images to the worksheet. This requires a little more code: you first need to instantiate a new PHPExcel_Worksheet_Drawing, set its properties, and then assign a worksheet to it:

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

Finally, we just need to save this spreadsheet to a XLSX file. This is again real easy: just create a PHPExcel_Writer_Excel2007 object, and tell it to save to a specific file name:

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(‘invoice.xlsx’);

Another quick note: this file can afterwards be read again, using the PHPExcel_Reader_Excel2007. It’s not fully supporting all properties the writer does, currently, but it should have no problems with simple spreadsheets.

Attachment: source.zip
  • Do we have the ability to create Excel charts using this package? If so can you post some sample code?


    Thanks!
Page 1 of 1 (1 items)