Problem with complex calculations

Topics: User Forum
Feb 25, 2010 at 3:04 AM

Hello, thank you for this great library. I am trying to use PHPExcel on existing file: input some data into it and then get calculated values from other cells which will be calculated according to the data entered earlier. The calculated values differ from actual excel if I enter them manually. The result cells seem to give the values that are in the excel file itself before the input - so its like no calc is being done..

The error seem to be in the cells that depend on other calculations which include vlookup. This is a bit complicated excel file so here is a link to download both excel file and php script.

http://www.dima.org.il/1/php-excel.rar

There also is the original file before convert to excel5 and 2007.


How it works:

 

Cells F to T 10 are the result which is: TRUE or FALSE.

Inputting values into cells:  B7, B8, C7, D7, D8, H to N 7.  Also F2 the input is "yes".

Now the calculation on line 10 is made on values on line 14, so the script loops the lines 15-115 putting each line in line 14, and read True/False values from line 10 each time.

To get all true on at least a few lines the input should be:

C7 - 5, D7 - 0.001, D8 - 0.0001, H7 - 1.064, F2 - yes.

 


 

The script originally is in 2003 format (I think), to make phpexcel work on it I converted it to Excel5 and calculations didn't work as I stated before, so I converted it to 2007 and got whole bunch of errors like:

Warning: strtolower() expects parameter 1 to be string, array given in F:\xampp\htdocs\xls\PHPExcel_1.7.2\Classes\PHPExcel\Calculation\Functions.php on line 11178

which are from function that takes care of "VLOOKUP".

-----------------

Sorry for the long post but I didn't know how else to explain it :)

Thanks for your help.

 

Coordinator
Feb 25, 2010 at 9:35 PM

I'll take a look at it over the weekend, in between rugby games.

Coordinator
Feb 26, 2010 at 12:32 PM

For a quick fix (for the 1.7.2 release):

insert the three lines:

$lookup_value = self::flattenSingleValue($lookup_value);
$index_number = self::flattenSingleValue($index_number);
$not_exact_match = self::flattenSingleValue($not_exact_match);


immediately after line 11152 (which should read)

public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true) {

I'll fix the SVN code in time for tomorrow's daily download

Feb 28, 2010 at 11:48 AM

Thank you so much!! This is working fine now on 2007 version, I guess the excel 5 is not something to work with on such projects..

Feb 28, 2010 at 7:44 PM
Edited Feb 28, 2010 at 7:47 PM

There is one more issue I've got with this:

I am using this command:

$objCalc = PHPExcel_Calculation::getInstance()->setCalculationCacheEnabled(false);
but it take too long to finish the script since I presume there is no cache. I put in htaccess:
php_value max_execution_time 200
And still the script does not finish.. Now using the
$objCalc = PHPExcel_Calculation::getInstance();

Produces wrong results.. Any suggestions?

 

 

Developer
Mar 19, 2010 at 1:34 AM

Did you get this fixed? Both your scripts seem to work fine?