Jump to content


Check out our Community Blogs





- - - - -

PHPExcel Wrapper - Refactored

Posted by rhossis, 21 April 2014 · 9228 views

php phpexcel excel spreadsheet worksheet
PHPExcel Wrapper - Refactored Hello guys, I hope you all had a great Easter :) I would like to apologize for the long time in making ammends to the PHPExcelWrapper, which in the last tutorial, had some deployment issues. There were a number of issues with the original article...you had to manipulate files from a specific directory, which was hardcoded onto the classes, which is not very practical. Also, the wrapper had to be located in the same folder as PHPExcel library, again losing points for ease of deployment. We try to address these in this second tutorial.
We address the first issue by providing a parameter in the spreadsheet factory, to tell it where the spreadsheet should be created/opened. The second issue, we address it is via the SplAutoloader class, which is the standard in our office projects at the moment. The class is PSR-0 compliant, and can be downloaded here (https://gist.github.com/jwage/221634) . We had not yet gotten into PSR4 autoloading but coming soon :thumbup:

1) The Setup
The structure of the libraries is as follows:
http://picpaste.com/....1398126233.png

May look like a lot just for a little wrapper, but its all about the namespacing. Each class has one file. A brief description of the sources is:
  • Config Directory
    Contains NamedConstant.php, for listing named constants
  • Exception Directory
    For Exception classes. We have two, one for our SpreadsheetProcessorFactory class, which generates spreadsheets, and for the SpreadsheetProcessor, which actually wraps the PHPExcel workbooks
  • autoload Directory
    The SplClassLoader class comes here. This was downloaded from github as per the link above
  • SpreadsheetProcessor Directory
    Contains the SpreadsheetProcessorFactory, which does the following tasks
    - Set directories from which spreadsheets will be manipulated
    - Set cache method (APC, temp file, MemCached, etc)
    - Perform task of creating or opening spreadsheets and csv files on the fly
Also contains SpreadsheetProcessor, which encapsulates PHPExcel object with its writer and reader, as well as a host of functions to manipulate them
  • Lib Directory
- Contains PHPExcel >= 1.7.9. This can be downloaded here http://phpexcel.code...ses/view/107442. I have not included this in the zip attachment. Please not that our wrapper does not support PHPExcel 1.7.8 and below


2) Autoloading the application and also PHPExcel
Our autoloader is simple, but ensures we are able to access both our wrapper classes as well as PHPExcel itself
<?php
//fetch configs
require("Config/NamedConstant.php");

//fetch the SplClassLoader autoloader
require("autoload/SplClassLoader.php");

//the directory where myapp is stored
$myappRoot = filter_input(INPUT_SERVER,'DOCUMENT_ROOT') ;

//register myapp namespace
$myappConfig = new SplClassLoader('myapp',$myappRoot);

$myappConfig->register();

//require phpexcel autoloader
if (!defined('PHPEXCEL_ROOT')) {
    define('PHPEXCEL_ROOT', (dirname(__FILE__) . '/Lib/PHPExcel/'));
    require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
}

With myapp and PHPExcel autoloaded, we are ready to generate some spreadsheets.

3) Basic Usage
I'd imagine, that programmatic spreadsheet manipulation is not something a programmer would want to be doing daily, we'd like to design the application, and then do some templates that will be automatically processed in regular production. I think utilization of the processor has strengths with this in mind in terms of simple creation of the spreadsheets, or loading the template for data processing. Also conversion between xlsx, xls, csv, pdf and html is available.
A small example below; pardon the poor variable naming in the example below
<?php
//load the autoloader
include("myappAutoload.php");

//delcare we are using the factory namespace
use myapp\core\application\spreadsheet\SpreadsheetProcessor\SpreadsheetProcessorFactory;

//Instantiate our factory
$xlFactory = SpreadsheetProcessorFactory::getInstance();
$xlFactory::initialize();

//Create the file (default name will be tmp, and default type Excel 2007 (.xlsx)
//We will look at setting the directory, filename and type in next tutorial :)
$a = $xlFactory->createFile();

//Open the file (it looks in the default directory but can be set)
$b = $xlFactory->openFile('tmp');

//Write some data
$b->writeCell(1, 1, 'Hello World :)');

//Save our worksheeet
$b->Save();

//Get location of where our wrapper saved our file
$c = $b->fileName;
echo $c;

4) Possible Useful Utilizations?
PHPExcel is pretty powerful, and our class is really more of a toolkit to get things done more quickly. But it would have to offer more than just simplyfying spreadsheet manipulation on the fly. I have had time to think of the usage of the class. What do you think, perhaps it can be as a backend to a JS editing program? I hope to try this out with JQGrid sometime;
Or deployed in a worksheet server. We know that PHPExcel is a memory hog. Each cell I think occupy 1kb in memory. In a situation where spreadsheet processing is heavy and distrubuted, can we maybe be have it working in Sync with a FileSystem abstraction library like Gauffrette (https://github.com/KnpLabs/Gaufrette‎) this may have some use, enabling reporting applications to manipulate and send automated spreadsheet reports to filesystem, cloud and email at will.

I would like go hear your feedback on this over time :) but shall be posting in Tutorial 3, a detailed tutorial on how to do some heavy processing work with the spreadsheet processor class. The library is available for download. Remember to download PHPExcel 1.7.9 Thanks again to Mark Baker for absolutely great library.

UPDATE: 2014-05-05: Fixed an issue with SpreadsheetProcessor::openFile() method, where it would load a blank reader instead of reading the file it is supposed to open

Attached Files



  • 1



One thing my company does is create websites as customer portals. Basically, our main application is for internal processing of data for service work. That data needs controlled visibility to their customers, including reports. For many business people, a report in Excel is the most desirable report format possible. Having a way to write to Excel for reports is a huge boon.

    • 0

WingedPanther, your right. Excel is quite simple and powerful at the same time; We have used it in reporting situations, running bank a/c recons on excel for clients, and then saving to network share. Is your product related with ecommerce / crm? Those could see high throughput with needs for lots of reports.

    • 0

No, my product is related to equipment maintenance and tracking. Think "looking up service records and service due dates".

    • 0

Thats great, i think your use case is a good scenario too for such a school of thought with regards to reports.

    • 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