VLOOKUP choking on parameters in PHPExcel.1.7.5/PHPExcel_Writer_Excel2007

Topics: Developer Forum
Dec 17, 2010 at 3:30 AM

I posted this to the issue tracker (http://phpexcel.codeplex.com/workitem/14901) but maybe I should've tried here first:

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

Either my execution is wrong or VLOOKUP is choking on the parameters I'm sending it.
The following code reproduces the problem:

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);

$objSheet = $objPHPExcel->getActiveSheet();
$objSheet->setTitle('Worksheet1');

$objSheet->setCellValue('A1', '10');
$objSheet->setCellValue('B1', 'foo');
$objSheet->setCellValue('A2', '20');
$objSheet->setCellValue('B2', 'bar');

$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objSheet = $objPHPExcel->getActiveSheet();
$objSheet->setTitle('Worksheet2');

$objSheet->setCellValue('A1', '10');
$objSheet->setCellValue('A2', '=VLOOKUP(A1,Worksheet1!$A$1:$A$2,2)');

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('test.xlsx');

Resulting error:

Fatal error: Uncaught exception 'Exception' with message 'Worksheet2!A2 -> Formula Error: Wrong number of arguments for VLOOKUP() function: 2 given, either 3 or 4 expected' in C:\all\dev\daily\PHPExcel.1.7.5\Classes\PHPExcel\Cell.php:284
Stack trace:
#0 C:\all\dev\daily\PHPExcel.1.7.5\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(965): PHPExcel_Cell->getCalculatedValue()
#1 C:\all\dev\daily\PHPExcel.1.7.5\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(911): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'A2', Array, Array)
#2 C:\all\dev\daily\PHPExcel.1.7.5\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(82): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array)
#3 C:\all\dev\daily\PHPExcel.1.7.5\Classes\PHPExcel\Writer\Excel2007.php(264): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array)
#4 C:\all\dev\daily\test.php(23): PHPExcel_Writer_Excel2007->sav in C:\all\dev\daily\PHPExcel.1.7.5\Classes\PHPExcel\Cell.php on line 284

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

I could be mistaken but it almost seems as if the : in the second parameter is causing the problems.  I'm trying to dig through Classes/PHPExcel/Calculation.php to pinpoint the issue but no luck yet.  Anyone else experience an issue like this?

Thanks!

Coordinator
Dec 21, 2010 at 9:43 PM
Edited Dec 21, 2010 at 9:47 PM

The : (range) operator should not cause any problems, and should (if it was causing an error) give 4 parameters rather than 2 (that the error suggests). I'll try taking a look at it in debug mode this week.

If you want to try debugging yourself, I use the following to dump the parser stack and calc engine execution details.

PHPExcel_Calculation::getInstance()->writeDebugLog = true;

$formulaValue = $sheet->getCell($cell)->getValue();
echo '<b>'.$cell.' Value is </b>'.$formulaValue."<br />\n";
$expectedValue = $sheet->getCell($cell)->getOldCalculatedValue();
echo '<b>'.$cell.' Expected Value is </b>'.$expectedValue."<br />\n";

echo '<b>Parser Stack :-</b><pre>';
$tokens = PHPExcel_Calculation::getInstance()->parseFormula($formulaValue,$sheet->getCell($cell));
print_r($tokens);
echo '</pre>';

try {
   $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>';
} catch (Exception $e) {
   echo "CALCULATION ENGINE ERROR: ".$e->getMessage()."<br />\n";
   echo '<h3>Evaluation Log:</h3><pre>';
   print_r(PHPExcel_Calculation::getInstance()->debugLog);
   echo '</pre>';
}
Dec 22, 2010 at 2:21 AM

Thanks, I'll see what I can do.  By the way, there isn't any method that'll dump the arguments for a function call, is there?  I'm wondering what exactly phpexcel thinks I've passed into VLOOKUP.

Dec 22, 2010 at 2:54 AM

Interesting... it seems that in Calculation.php

        $stack->push($d['type'],++$d['value'],$d['reference']);  // increment the argument count

is only getting called once.

Dec 22, 2010 at 3:06 AM

Further, if I don't include references to any other sheets in my VLOOKUP call then it works as expected.

Dec 22, 2010 at 3:16 AM

Restoring the comments in Calculation.php I see this:

Assessing Expression <b>A1,'Worksheet1'!$A$1:$A$2,2)</b>
Initial character of expression block is A
$isOperandOrFunction is True
Element with value A1,'Worksheet1'!$A$1 is an Operand, Variable, Constant, Strin
g, Number, Cell Reference or Function
Element A1,'Worksheet1'!$A$1 is a Cell reference

So instead of just grabbing the A1, it's grabbing all the way up to the colon.  Maybe a regexp issue?

Dec 22, 2010 at 5:01 AM

Regexp is not my forte, but I believe that in

  //  Cell reference (cell or range of cells, with or without a sheet reference)
  const CALCULATION_REGEXP_CELLREF  = '((((?:\P{M}\p{M}*)+?)|(\'[^\']*\')|(\"[^\"]*\"))!)?\$?([a-z]{1,3})\$?(\d+)';
  //  Named Range of cells
  const CALCULATION_REGEXP_NAMEDRANGE  = '((((?:\P{M}\p{M}*)+?)|(\'[^\']*\')|(\"[^\"]*\"))!)?([_A-Z][_A-Z0-9]*)'

the "A1," is being gobbled up by the (?:\P{M}\p{M}*).  If I change the regexp to the following (which is sufficient for my test but clearly not correct) my code works.

  //  Cell reference (cell or range of cells, with or without a sheet reference)
  const CALCULATION_REGEXP_CELLREF  = '((((?:[a-zA-Z0-9]*)+?)|(\'[^\']*\')|(\"[^\"]*\"))!)?\$?([a-z]{1,3})\$?(\d+)';
  //  Named Range of cells
  const CALCULATION_REGEXP_NAMEDRANGE  = '((((?:[a-zA-Z0-9]*)+?)|(\'[^\']*\')|(\"[^\"]*\"))!)?([_A-Z][_A-Z0-9]*)';

Dec 22, 2010 at 7:52 PM

I'm not sure if I am missing something, but isn't the problem in the VLOOKUP itself?

The VLOOKUP is looking up a value in column 1 of Worksheet1, and you want to return the value in column 2, but the VLOOKUP area only has 1 column.
Try '=VLOOKUP(A1,Worksheet1!$A$1:$B$2,2)' and see if the error disappears?

Coordinator
Dec 22, 2010 at 8:20 PM

@gridder

The formula and data it's working on will indeed return an error for the reasons you state, but it should return a "valid" Excel #REF! error rather than throwing an exception.

@blurmy23

A quick fix is to modify line 2602 of Calculation.php, which currently reads

')/si';

to make the regexp check ungreedy with the /U switch, so the line should read

')/siU';

I've tested this with your VLOOKUP formula, and it reads the arguments correctly; but haven't yet done any extensive testing to see if it has any adverse effects for the other regular expressions handled by $regexpMatchString.

Coordinator
Dec 24, 2010 at 10:46 AM
Edited Dec 24, 2010 at 11:10 AM

Hopefully, this issue is resolved with the latest changes to Calculation.php. The code can be found in the SVN Repository

Dec 26, 2010 at 2:58 PM

@gridder - heh ... uh ... yeah, there's that too.

@MarkBaker - thanks!  working perfectly now.