Different results with Excel5/Excel2007 using getCalculatedValue

Topics: Developer Forum
Nov 4, 2009 at 4:05 PM

When making a call using getCalculatedValue(), I am experiencing different results when accessing the same spreadsheet saved in Excel 5 and 2007 formats. Excel5 format returns the calculated value but 2007 returns #VALUE!

The spreadsheet is from a third party and the formulas are quite extensive so without wanting to post an example just yet I am wondering if there is any way to debug the ouput of how the calculation is made in the php libraries so I can pinpoint the problem?

Many thanks in advance

Nov 4, 2009 at 4:45 PM

No need to reply. Found some useful code elsewhere in another discussion.

Great product BTW!

/******** START DEBUG ********/

$sheet = $objPHPExcel->getActiveSheet();

$cell = 'D18';
$cellValue = $sheet->getCell($cell)->getValue();
echo '<b>'.$cell.' Value is </b>'.$cellValue."<br />\n";

echo '<b>Parser Stack :-</b><pre>';
$tokens = PHPExcel_Calculation::getInstance()->parseFormula($cellValue);
print_r($tokens);
echo '</pre>';
$cellValue = $sheet->getCell($cell)->getCalculatedValue();
echo '<b>'.$cell.' Calculated Value is </b>'.$cellValue."<br />\n";

echo '<h3>Evaluation Log:</h3><pre>';
print_r(PHPExcel_Calculation::getInstance()->debugLog);
echo '</pre>';

/******** END DEBUG ********/

Developer
Nov 4, 2009 at 10:40 PM
calsoftware wrote:

When making a call using getCalculatedValue(), I am experiencing different results when accessing the same spreadsheet saved in Excel 5 and 2007 formats. Excel5 format returns the calculated value but 2007 returns #VALUE!

It sounds like a bug. If you can provide some example that shows the problem please let us know.

Nov 6, 2009 at 8:13 AM

I don't know if the following words are related to this topic or not, but, please, try to check it.

Script is following:

<?php
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/Cell.php';
require_once 'Classes/PHPExcel/Writer/Excel5.php';

  $xls = new PHPExcel();
  $sheet = $xls->getActiveSheet();
  $sheet->getCell('A1')->setValue(2);
  $sheet->getCell('A2')->setValue(3);
  $sheet->getCell('B1')->setValue(4);
  $sheet->getCell('B2')->setValue(5);
  $sheet->getCell('C1')->setValue('=SUMPRODUCT(A1:A2;B1:B2)');
  
  $xlsWriter = new PHPExcel_Writer_Excel5($xls);
  $fn = tempnam( '', 'test' );
  if( $fn != false )
    $xlsWriter->save( $fn );
?>

Execute this script and then try to open XLS file, created by script, with different versions of Excel (or similar soft).

My results:

1) when I open this file with OpenOffice 3.0 for Linux - all is OK (cell C1 contains 23 - = 2 * 4 + 3 * 5 - as expected)

2) when I open file with OpenOffice 2.4 for Winidows - all is OK

3) when I open file with MS Excel 2007 - cell C1 contains error message '#VALUE!'. But formula in that cell is correct. And when I click on "error" sign near that cell, then select "correct formula" menu item, then press "Enter" without any changes - after that cell C1 contains 23.

Coordinator
Nov 6, 2009 at 9:05 AM

Couple of points to note.

Please use the comma (,) rather than semi-colon (;) for separating function arguments. At present, the internal calculation engine only recognises commas

Secondly, SUMPRODUCT() isn't yet implemented in the calculation engine, so the result of this formula cannot be calculated internally by PHPExcel. When writing an Excel2007 file, both formula and the result of that calculation are written to the xlsx file, calculated using the internal calculation engine.
Because SUMPRODUCT() isn't yet implemented, it will return a #Not Yet Implemented PHPExcel error, and it is that result which will be written to the xlsx file. This value is not recognised as an error by Excel, so it will treat it as #Value!, hence what you see in the workbook when you open it in Excel. Excel can, of course, recalculate because SUMPRODUCT() is a recognised function there, hence your observations when using "correct formula". Open Office automatically recalculates all formulae when you open a document there, so you don't see the #Value! result, but the actual correct result for the formula.,

Nov 6, 2009 at 9:28 AM
MarkBaker wrote:

Couple of points to note.

Please use the comma (,) rather than semi-colon (;) for separating function arguments. At present, the internal calculation engine only recognises commas

That is not a way - function SUMPRODUCT requires semi-colon as argument separator. Change it to comma - and you will get an error message immediately. (Tested in OpenOffice 3 for Linux).

MarkBaker wrote:

Secondly, SUMPRODUCT() isn't yet implemented in the calculation engine, so the result of this formula cannot be calculated internally by PHPExcel. When writing an Excel2007 file, both formula and the result of that calculation are written to the xlsx file, calculated using the internal calculation engine.

That is bad news for me. But look to my code: I save file as Excel5 ".xls", not as Excel2007 ".xlsx". What about it?

MarkBaker wrote:
Because SUMPRODUCT() isn't yet implemented, it will return a #Not Yet Implemented PHPExcel error, and it is that result which will be written to the xlsx file. This value is not recognised as an error by Excel, so it will treat it as #Value!, hence what you see in the workbook when you open it in Excel. Excel can, of course, recalculate because SUMPRODUCT() is a recognised function there, hence your observations when using "correct formula". Open Office automatically recalculates all formulae when you open a document there, so you don't see the #Value! result, but the actual correct result for the formula.,

:-( So when can I expect implementation of SUMPRODUCT in PHPExcel?

Coordinator
Nov 6, 2009 at 11:07 AM
Edited Nov 6, 2009 at 11:13 AM
GRIG wrote:
MarkBaker wrote:

Couple of points to note.

Please use the comma (,) rather than semi-colon (;) for separating function arguments. At present, the internal calculation engine only recognises commas

That is not a way - function SUMPRODUCT requires semi-colon as argument separator. Change it to comma - and you will get an error message immediately. (Tested in OpenOffice 3 for Linux).

Open Office uses a sem-colon separator for function arguments. Excel uses a comma (by default in the US/English versions, I can't comment on other language versions). PHPExcel has always used the comma. This doesn't only apply to SUMPRODUCT() but to all functions that accept more than one argument.

When opening an Excel (xls or xlsx) workbook in Open Office, OO Calc should handle any conversion between , and ;

GRIG wrote:
MarkBaker wrote:

Secondly, SUMPRODUCT() isn't yet implemented in the calculation engine, so the result of this formula cannot be calculated internally by PHPExcel. When writing an Excel2007 file, both formula and the result of that calculation are written to the xlsx file, calculated using the internal calculation engine.

That is bad news for me. But look to my code: I save file as Excel5 ".xls", not as Excel2007 ".xlsx". What about it?

No comment at the moment. Without doing some additional testing using both Excel 95/2003 and Excel 2007, I can't answer that. Possibly it's a problem in the writer code.

GRIG wrote:
MarkBaker wrote:
Because SUMPRODUCT() isn't yet implemented, it will return a #Not Yet Implemented PHPExcel error, and it is that result which will be written to the xlsx file. This value is not recognised as an error by Excel, so it will treat it as #Value!, hence what you see in the workbook when you open it in Excel. Excel can, of course, recalculate because SUMPRODUCT() is a recognised function there, hence your observations when using "correct formula". Open Office automatically recalculates all formulae when you open a document there, so you don't see the #Value! result, but the actual correct result for the formula.,

:-( So when can I expect implementation of SUMPRODUCT in PHPExcel?

Already added in SVN, should be available in the latest downloads tomorrow

Nov 6, 2009 at 12:02 PM
MarkBaker wrote:
GRIG wrote:
MarkBaker wrote:

Couple of points to note.

Please use the comma (,) rather than semi-colon (;) for separating function arguments. At present, the internal calculation engine only recognises commas

That is not a way - function SUMPRODUCT requires semi-colon as argument separator. Change it to comma - and you will get an error message immediately. (Tested in OpenOffice 3 for Linux).

Open Office uses a sem-colon separator for function arguments. Excel uses a comma (by default in the US/English versions, I can't comment on other language versions). PHPExcel has always used the comma. This doesn't only apply to SUMPRODUCT() but to all functions that accept more than one argument.

MS Excel 2007 (Russian version) requires semi-colon. Comma is error. (Just tested)

But may be there is some handling by MSExcel here. Function names are "translated" into russian as listed in documentation - so argument separator also can be translated.

MarkBaker wrote:
GRIG wrote:
MarkBaker wrote:
Because SUMPRODUCT() isn't yet implemented, it will return a #Not Yet Implemented PHPExcel error, and it is that result which will be written to the xlsx file. This value is not recognised as an error by Excel, so it will treat it as #Value!, hence what you see in the workbook when you open it in Excel. Excel can, of course, recalculate because SUMPRODUCT() is a recognised function there, hence your observations when using "correct formula". Open Office automatically recalculates all formulae when you open a document there, so you don't see the #Value! result, but the actual correct result for the formula.,

:-( So when can I expect implementation of SUMPRODUCT in PHPExcel?

Already added in SVN, should be available in the latest downloads tomorrow

Well, I shall wait a little :-)

Coordinator
Nov 6, 2009 at 12:21 PM
Edited Nov 6, 2009 at 12:21 PM
GRIG wrote:

MS Excel 2007 (Russian version) requires semi-colon. Comma is error. (Just tested)

But may be there is some handling by MSExcel here. Function names are "translated" into russian as listed in documentation - so argument separator also can be translated.

That's quite possible.... I know some languages use characters other than the comma; but Excel should handle any conversion itself, so if you were to send me a workbook with Russian function names and ; separator, I'd expect my English version of Excel to translate it for me.

At present, I can only guarantee that PHPExcel works correctly with English function names and the comma separator for arguments. I do have a list of Excel function names in the following languages:

  • Czech
  • Danish
  • Dutch
  • Finnish
  • French
  • German
  • Hungarian
  • Italian
  • Norwegian
  • Polish
  • Portuguese
  • Spanish
  • Swedish

and (at some point in the future) I will try to implement that list so formulae can be written using the appropriate language... quite how I'm going to do this, I don't yet know, and it's near the bottom of my "to do" list.

Developer
Nov 6, 2009 at 9:42 PM

Also note that even if you are using some non-English version MS Office Excel, when you save the workbook the formula is always written in English inside the file.

Example using Danish version of MS Office Excel 2007:

Unzip the xlsx file and you will notice that the formula is written in English.

 

 

Nov 9, 2009 at 8:29 AM
MarkBaker wrote:

At present, I can only guarantee that PHPExcel works correctly with English function names and the comma separator for arguments. I do have a list of Excel function names in the following languages:

.......

and (at some point in the future) I will try to implement that list so formulae can be written using the appropriate language... quite how I'm going to do this, I don't yet know, and it's near the bottom of my "to do" list.

This seems to be simple in implementation. In file PHPExcel/Calculation.php you have a list of all excel functions in array $_PHPExcelFunctions. And function name is used as key in that array. I think you need just 1 another array (with name some like $_PHPExcelNLSFunctions). Keys of this array are function names, used in localised Excel version, values are correspondent english names. For example (Russian version):

$_PHPExcelNLSFunctions = array (
  'СУММ' => 'SUM',
  'СУММПРОИЗВ' => 'SUMPRODUCT',
  ....
);

Then when you do formula parsing you will search function name not in $_PHPExcelFunctions, but in $_PHPExcelNLSFunctions, "translate" it into english using this array, then use english function name to search in $_PHPExcelFunctions and do all the rest as before.

Correct $_PHPExcelNLSFunctions array can be loaded from file by user command (or function call).

Default $_PHPExcelNLSFunctions array content is for english function names. Using this will make parsing process independent of selected language.

This idea can be expanded to multi-NLS support in simple way - just do $_PHPExcelNLSFunctions as array of arrays. And then use syntax like

$englishFunctionName = $_PHPExcelMultiNLSFunctions[$usedLanguage][$NLSFunctionName];

Coordinator
Nov 9, 2009 at 10:30 AM
GRIG wrote:

This seems to be simple in implementation. In file PHPExcel/Calculation.php you have a list of all excel functions in array $_PHPExcelFunctions. And function name is used as key in that array. I think you need just 1 another array (with name some like $_PHPExcelNLSFunctions). Keys of this array are function names, used in localised Excel version, values are correspondent english names. For example (Russian version):

Simple enough in principle, and not too different from what I was thinking of using... autoloading a cross-reference array of function names for the appropriate language; but if we can automatically determine the language used when reading the file rather than forcing the coder to identify the language, it would be better. And I'm still not sure of the best method to use when writing a file.

Nov 9, 2009 at 10:51 AM
koyama wrote:
calsoftware wrote:

When making a call using getCalculatedValue(), I am experiencing different results when accessing the same spreadsheet saved in Excel 5 and 2007 formats. Excel5 format returns the calculated value but 2007 returns #VALUE!

It sounds like a bug. If you can provide some example that shows the problem please let us know.

Unfortunately, due to NDA I'm unable to send the actual spreadsheet that is causing the issue and because of the complicated nature of the calculations and the fact that there are so many going on, for the time being I am happy to workaround the issue.

FYI, I found that the problem was summing multiple cells using the + operator instead of using the SUM() function e.g. "=AB19+AB32+AB45+AB48+AB47+AB49+AB50+AB51+AB52+AB56". When one of the values in the cells was empty, #VALUE! was returned.

I was able to workaround this by manually formatting the cell in question to be numeric with a value of 0 (Zero).

Thanks for the fast response and keep up the good work on a great product.

Developer
Nov 9, 2009 at 11:19 AM

>> When one of the values in the cells was empty, #VALUE! was returned.

What version of PHPExcel are you using? I think this may be fixed in PHPExcel 1.7.1. See this bug:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10799

Nov 9, 2009 at 11:36 AM
MarkBaker wrote:

Simple enough in principle, and not too different from what I was thinking of using... autoloading a cross-reference array of function names for the appropriate language; but if we can automatically determine the language used when reading the file rather than forcing the coder to identify the language, it would be better. And I'm still not sure of the best method to use when writing a file.

This seems to be impossible. Look some up at 2 pictures made by Koyama: excel application uses Danish language, but function name is stored in English in file. That means you cannot use function names to detect language. So the only data you can use to detect language are string constants in cells. That seems to be very unreliable basement of detection. It is possible to make XLS file without any string constant, but with functions.

Then, this seems to be not necessary. Just because of the same reason - function names are stored in English. So using of localized function names is just for PHP coder, not for XLS user.

Nov 9, 2009 at 11:42 AM
calsoftware wrote:

FYI, I found that the problem was summing multiple cells using the + operator instead of using the SUM() function e.g. "=AB19+AB32+AB45+AB48+AB47+AB49+AB50+AB51+AB52+AB56". When one of the values in the cells was empty, #VALUE! was returned.

AFAIK, when excel functions like SUM() work on cell ranges, they ignore empty cells and work only on non-empty cells. Your formula contains direct references to each cell - so empty cells cannot be ignored. Try to use formula "=SUM(AB19,AB32,AB45,AB48,AB47,AB49,AB50,AB51,AB52,AB56)" instead - may be this is a solution.