Inserting Formula Error (Uncaught Exception)

Topics: Developer Forum, User Forum
Apr 30, 2012 at 4:46 PM

Hello.

I am having difficulty writing a particular formula to a spreadsheet with PHPExcel. The formula is part of a Bloomberg add-on tool for Excel, for example the Excel formula to return the last trading price of Apple stock is given by:

=BDP("AAPL US Equity","px last")

This formula works fine and well in my Excel, but trying to insert this formula via PHPExcel throws the following error:

( ! ) Fatal error: Uncaught exception 'Exception' with message 'Worksheet!B1 -> Formula Error: Unexpected ,' in C:\wamp\www\handbook\Classes\PHPExcel\Cell.php on line 288
( ! ) Exception: Worksheet!B1 -> Formula Error: Unexpected , in C:\wamp\www\handbook\Classes\PHPExcel\Cell.php on line 288
Call Stack
# Time Memory Function Location
1 0.0006 721352 {main}( ) ..\buy.php:0
2 0.1517 10394960 PHPExcel_Writer_Excel2007->save( ) ..\buy.php:89
3 0.1929 10737456 PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet( ) ..\Excel2007.php:266
4 0.1934 10737968 PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData( ) ..\Worksheet.php:82
5 0.1982 10823048 PHPExcel_Writer_Excel2007_Worksheet->_writeCell( ) ..\Worksheet.php:930
6 0.1983 10823176 PHPExcel_Cell->getCalculatedValue( ) ..\Worksheet.php:984

 

Here is the relevant section of my code:

// $new_ref is a reference number
$cell_ticker = sprintf('A'.$new_ref);
$cell_value = sprintf('B'.$new_ref);

$formula = sprintf('=BDP('.$cell_ticker.',"px last")');

$objSheet->getCell($cell_ticker)->setValue($ticker);
$objSheet->getCell($cell_value)->setValue($formula);
        
// write the file
$objWriter->save('valuations.xlsx');

I know that the formula DOES work in my Excel (since I have the Bloomberg add-on installed), so I just need PHPExcel to stop throwing this error, and write the formula as it is! Why doesn't it like the comma?

 

I am using version PHPExcel Version 1.7.6 and PHP version 5.3.8 (local WAMP server).

 

Any help would be really appreciated. Thanks,

Ian

Coordinator
May 2, 2012 at 8:55 AM

Add-ins function packages are not supported, so PHPExcel doesn't recognise the function as it's a non-standard function.

By default, when writing a workbook, PHPExcel pre-calculates all formulae in the workbook, so it's trying to calculate the BDP function and failing because it isn't a valid (standard) function. Try diabling pre-calculation in your writer before saving.

$objWriter->setPreCalculateFormulas(FALSE);

However, because PHPExcel discards any features that it doesn't understand, it's probable that you'll lose the Bloomberg add-on tools.

May 3, 2012 at 12:36 PM

Thanks for your reply Mark.

Just as you said, this did allow PHPExel to write the formula in, though when I open the spreadsheet it is there as text, rather than automatically calculates the value of the formula. Plus, it has the rather unfortunate side-effect of deleting all of the other BDP formulas already in the spreadsheet!! So I am guessing there is probably no solution to my problem. Thanks anyway for your help.

Mar 16, 2015 at 11:00 PM
Hello!

Has anyone found the solution for this?
Coordinator
Mar 16, 2015 at 11:38 PM
There is no solution in PHPExcel, unless either I or somebody else writes it, and I'be never seen any sign of anybody else trying to make any changes to support external formulae.