Jump to content


Check out our Community Blogs





- - - - -

A PHPExcel Wrapper

Posted by rhossis, 03 August 2013 · 5135 views

php excel phpexcel
Excel is widely used application in businesses. It is popular because it offers a wide array of functions, data analysis tools like pivot tables, charts. Excel is pretty powerful and is used in diverse scenarios, from calculating daily sales in your mom and pop store to crunching complex formulas in large institutions like investment banks.

Regardless of your use for it, sometimes you at times need a little leverage to assist in excel applications, especially with jobs like loading data onto spreadsheets, formatting data, calculating summaries, inserting charts, validating data, report generation. This is where visual basic for applications (VBA) comes in. It is pretty cool if you are only on MS stack. What if you're using any other language, and are writing code that is not for MS platform?

Personally, I use PHP for web development. VBA would not be an option for loading data to spreadsheets, and creating new spreadsheets on the fly. Here is where libraries like PHPExcel come in. PHPExcel is a powerful open source PHP library for creating and manipulating excel files, as well as CSV. It can generate output in Excel2007, Excel5, HTML, PDF and CSV. We still used to ship some VBA code at our client sites for some jobs, but all that stopped this year after we were able to leverage enough off of PHPExcel to enable us automate our spreadsheets natively in PHP.

I'd like to share something I have been working on, extending a wrapper originally proposed on codeplex. The PHPExcelWrapper class is built to speed up the process of creating workbooks using PHPExcel, through providing a less verbose syntax to the commonly used speadsheet manipulation techniques. It really does not add or take anything to the core PHPExcel: it is a plain wrapper, merely translating some of the libraries existing interface. I hope to improve on it, as well as provide more functionality than just wrappers to speed up the development of workbooks automatically such as templates. All in all, thanks to Mark Baker for this gem of a library, and zeriph on codeplex for the PHPExcelWrapper class, which we have extended. This wrapper is not the final piece, still improving on it :)

The main motivation behind this is to
1) Add additional functionality by writing methods that can be called in one call to perform
tasks such as adding images, opening files, converting files between various formats.
2) Reduce verbosity in method calls. As a VBA programmer as well, it is very similar to me
working with PHPExcel, whereby to work with a particular object, you traverse the object hierarchy when making calls. Nothing wrong there. This is standard enough, just gets verbose at times (because unlike VBA we don't have the luxury of the With keyword in PHP).
e.g. for VBA
ThisWorkbook.ActiveSheet.Cells(1, 1).Value = 10
wheres in PHPExcel (assuming your workbook object is called $wkbkObj)
$wkbkObj->getActiveSheet()->setCellValueByColumnAndRow(1, 1, "10");
This is pretty straight foward, we merely add some methods that produce a more straight foward one method call operation where we feel it will assist in getting a job done faster.

To test the library, just download it and save all contents in your root PHPExcel directory. It is currently working on sandbox, so the directory named "Files" is the only directory from which it will read and write to and from.

Below is some example code on how to do some common tasks.

We compare the two syntax for the wrapper and in native PHPExcel calls.

Some overhead with our wrapper is you need to initialize the factory class that
generates workbooks on the fly
$xlFactory = CYMAPGT_PHPExcelWrapperFactory::getInstance();
$xlFactory::initialize();
1) Setting Cache Storage Method
PHPExcel allows you to select from a number of storage methods to leverage on caching when processing large files. The cache options are to
- cache objects in PHP memory (default)
- in memory as serialized array
- in memory compressed with gzip
- cache in disk
- cache in temp file
- cache in apc
- cache with memcached
- cache with wincache

For more info on the free library you can download it here: http://phpexcel.code...ses/view/107442
$xlFactory->setCacheMethod("MemoryGZip");
In PHPExcel, you would do
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
2) Open a file for editing
We use our factory to load for us a file
$wkbkObj = $xlFactory->openFile("File1","Excel2007");
In PHPExcel , you would do
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("File1.xlsx");
3) Writing data to cells
We use the WriteCell() method writing to Excel cells.
$wkbkObj->WriteCell(0,1,"Hello there");
In PHPExcel
$objPHPExcel->getActiveSheet()
            ->setCellValueByColumnAndRow(0, 1, "Hello there");
4) Adding an image to the worksheet on the first cell
Here, we do
$wkbkObj->addDrawingObject(0,true,"files/spreadsheet/logo.png");
In PHPExcel
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName("img1")
           ->setPath("files/spreadsheet/logo.png");
$objDrawing->setWorksheet($wSheet);
5) Set a cells style from a style array
First, we create our style array, which is the recommended way to apply large formatting
jobs on PHPExcel, less expensive memorywise
$styleArray = array("font"=>array("bold"=>true,"italic"=>true));
Here, we do
$wkbkObj->setStyle(0,true,"A1:","A1",$styleArray,true);
In PHPExcel
$objPHPExcel->getActiveSheet()
            ->getStyle('A3')
            ->applyFromArray($styleArray);
6) Converting a file from Excel5 to Exce2007
Lets say we want to convert an Excel5 to Excel 2007
Here, we do
$xlObjConvert = $xlFactory->convertFile("report","Excel5","report","Excel2007");
and if you wanted to return an instance of the workbook for editing, you would just need to add one
more parameter to the call. Regularly, you would have to create a reader for the new file and open it as described in the first example.
$xlObjConvert = $xlFactory->convertFile("report","Excel5","report","Excel2007",true);
In PHPExcel
$xlObjConvert = PHPExcel_IOFactory::load($fileToConvert);
$Writer = PHPExcel_IOFactory::createWriter($xlObjConvert 1);
$Writer->setPreCalculateFormulas(false);
$Writer->save("report.xlsx");
UPDATE:
I have realized some issues with this wrapper handling the latest PHPExcel version (1.7.9) Shall fix this over the weekend and re-upload another package.

  • 2



Great job Rhossis, it's very useful, excel is one of the most required formats to either read or save to. No doubts this will be helpful for many :D

    • 0

Thanks, i shall be uploading the fixed up release this weekend :)

    • 0

Recent Entries

Recent Comments

My Picture

0 user(s) viewing

0 members, 0 guests, 0 anonymous users

Categories

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download