Extend PHPExcel with custom functions?

Topics: Developer Forum
May 29, 2013 at 3:40 AM
Hi,

I wonder if it's possible to add more functions to PHPExcel using some kind of extension or plugin? If the function is standard Excel function, I guess I can just modify the source code, but I'm thinking there may be custom functions implemented in VBA which I want to run under PHPExcel by re-implement it in PHP extension, is this possible?

Thanks.
Coordinator
May 29, 2013 at 6:35 AM
It's perfectly possible to do this by editing the calculation engine code, though it isn't extensible (the rewrite currently in progress for version 2.0 will support extending the function list). Details of all Excel functions are held in a large array in Calculation.php, which includes the class/method names for an implementation of that function.
May 29, 2013 at 10:26 AM
Ok, thanks for the clarification. I assume version 2.0 is still in early phase and not an option for new project using PHPExcel?
Aug 14, 2013 at 11:25 AM
jrao wrote:
Hi,

I wonder if it's possible to add more functions to PHPExcel using some kind of extension or plugin? If the function is standard Excel function, I guess I can just modify the source code, but I'm thinking there may be custom functions implemented in VBA which I want to run under PHPExcel by re-implement it in PHP extension, is this possible?

Thanks.
actually you can do that but will eat your days :P

step1: go into calculation.php
step2: find _calculateFormulaValue function and after it get the formula trimmed you can start to implement your code with a regex for start
for example:

if(strpos($formula,'ABRACADABRA(')!==false)
{
            preg_match_all("/ABRACADABRA(\((?:[^()]++|(?1))+\))/", $formula, $matches);
            for($v=0;$v<sizeof($matches[0]);$v++)
            {
                                here comes your code
                        }
}
step3: be pacient...it takes a lot to understand how the system works...

hope that helped
Aug 14, 2013 at 3:11 PM
Ok, thanks for the explanation. I'm looking at 2.0 right now, it seems to be much easier to extend, not sure how stable it is though.
Aug 14, 2013 at 8:25 PM
It's pretty stable but it need some fixes from what I saw...

and also works pretty ok with sheets of 2Mb => the only thing I don't like is that they didn't fixed :
space+9 -> phpexcel: #VALUE! -> msexcel: 9
range functions bassed on postions(ie index) - this should go 1st to find what it will return and after that to calculate the elements -> to not get into a cyclic trap...

I posted some fixed around here but I'm to lazy to fork them...
Coordinator
Aug 14, 2013 at 10:39 PM
On 14/08/2013 16:12, jrao wrote:

From: jrao

Ok, thanks for the explanation. I'm looking at 2.0 right now, it seems to be much easier to extend, not sure how stable it is though
2.0 should not be considered stable. It's still very much ongoing work... part of that is using an object factory to instantiate objects (which will help extensibility, because the internal code will look for extended classes and use those where appropriate); and the calculation engine is also undergoing complete rewrite (currently working on simplifying the logic for calling Excel functions in a way that allows User-defined functions to be added easily).... neither of these changes are yet in github.


Timescale estimates? I'm not making any yet; but suspect that there'll probably be at least one 1.x branch release before version 2 is stable enough for anything more than evaluation

-- 
Mark Baker
Aug 15, 2013 at 1:22 AM
Ok, thanks for the heads up, Mark, this is very useful information.