I'm working on a project where I need to export a spreadsheet, populate it manually and then import it again back in to the system. I'm using PHPExcel to do all the hard work.
The spreadsheet is basically a set of questions which need answering. (You can download an example of the spreadsheet here). The answers to each question are in a Data Validation List which come from another sheet (Answers) using named ranges. Both questions and answers come with unique IDs so that I can read the results back into the database with ease.
The questionID is stored in column B and is inserted when I create the spreadsheet. The answerID however is done via a lookup (in column C) because the answerID will change depending on the answer that is chosen. Each set of answers have two named ranges 1 for the answers in the list and one for the lookups. These named ranges are labelled answers_questionID and answers_lookup_questionID. The formula used in column C for getting the lookup values is:
=INDEX(answers_lookup_1, MATCH(E6, answers_1, 0),2)
I'm using INDEX and MATCH instead of VLOOKUP because I don't want to sort the data alphabetically.
This all works fine in the spreadsheet, it's when I try to read it back in that I have the problem. When I try reading the values back in, I either get 0 (they should all be positive integers) or #REF!
Here's what I'm doing when I read the data back in:
$objPHPExcel = new PHPExcel();
// Set a read filter so that we can restrict what we're loading in
class MyReadFilter implements PHPExcel_Reader_IReadFilter {
private $_iStartRow = 0;
private $_iEndRow = 0;
private $_aColumns = array();
public function __construct($iStartRow, $iEndRow, $aColumns) {
$this->_iStartRow = $iStartRow;
$this->_iEndRow = $iEndRow;
$this->_aColumns = $aColumns;
}
public function readCell($iCol, $iRow, $sWorksheetName = '') {
if ($iRow >= $this->_iStartRow && $iRow <= $this->_iEndRow) {
if (in_array($iCol, $this->_aColumns)) {
return true;
}
}
return false;
}
}
$objFilterSubset = new MyReadFilter(5, 500, range('B', 'L'));
// Which type of reader do we need.
if (!extension_loaded('zip')) dl('php_zip.dll');
$objReader = new PHPExcel_Reader_Excel2007();
// Set the read filter so we don't get EVERYTHING
$objReader->setReadFilter($objFilterSubset);
// Load the actual data
$objPHPExcel = $objReader->load($sFilename);
// Store the data in an array
$aSheetNames = $objPHPExcel->getSheetNames();
foreach ($aSheetNames as $iSheetNum => $sSheetName) {
$objPHPExcel->setActiveSheetIndex($iSheetNum);
$aSheetData[$sSheetName] = $objPHPExcel->getActiveSheet()->toArray(null,true,true,false);
}
So I get a nice big array with all my data in, only column C is wrong.
- There doesn't seem to be anything wrong with my formula as it works in excel.
- I've tried simpler versions of formulas and they work i.e. summing multiple cells together.
- Both sheets are present at the time of reading.
- All named ranges are set fine at the time of reading.
- I'm using v 1.7.4 but I've tried all versions up to 1.7.9 (1.8.0 doesn't even read in the spreadsheet).
Mark Baker asked me to run a formula test which can be found here which gives the following debug if anyone can make sense of it.
Formula Value is =INDEX(answers_lookup_1, MATCH(E6, answers_1, 0),2)
Expected Value is 7 Parser Stack :-
Array (
[0] => Array
(
[type] => Value
[value] => answers_lookup_1
[reference] =>
)
[1] => Array
(
[type] => Cell Reference
[value] => E6
[reference] => E6
)
[2] => Array
(
[type] => Value
[value] => answers_1
[reference] =>
)
[3] => Array
(
[type] => Value
[value] => 0
[reference] =>
)
[4] => Array
(
[type] => Operand Count for Function MATCH()
[value] => 3
[reference] =>
)
[5] => Array
(
[type] => Function
[value] => MATCH(
[reference] =>
)
[6] => Array
(
[type] => Value
[value] => 2
[reference] =>
)
[7] => Array
(
[type] => Operand Count for Function INDEX()
[value] => 3
[reference] =>
)
[8] => Array
(
[type] => Function
[value] => INDEX(
[reference] =>
)
)
Calculated Value is 0
Evaluation Log:
Array
(
[0] => Questions!C6 -> Evaluating Named Range answers_lookup_1
[1] => Questions!C6 -> Evaluation Result for named range A4:B6 is a matrix with a value of { , ; , 7; , 8 }
[2] => Questions!C6 -> Evaluating Cell E6 in current worksheet
[3] => Questions!C6 -> Evaluation Result for cell Questions!E6 is a string with a value of Yes
[4] => Questions!C6 -> Evaluating Named Range answers_1
[5] => Questions!C6 -> Evaluation Result for named range A4:A6 is a matrix with a value of { ; ; }
[6] => Questions!C6 -> Evaluating Function MATCH() with 3 arguments
[7] => Questions!C6 -> Evaluating MATCH( Yes, { ; ; }, 0 )
[8] => Questions!C6 -> Evaluation Result for MATCH() function call is a #N/A error
[9] => Questions!C6 -> Evaluating Function INDEX() with 3 arguments
[10] => Questions!C6 -> Evaluating INDEX( { , ; , 7; , 8 }, #N/A, 2 )
[11] => Questions!C6 -> Evaluation Result for INDEX() function call is a null value
)
I've tried multiple variations of formulas and ways of reading the data in. I've looped over the data and used getCalculatedValue() instead of toArray() and still got the same results.
I can only think that this may be a bug within the INDEX function in Functions.php and that the formula is not being interpreted correctly when being read in using named ranges. If I print out the passed array to INDEX() the result is:
INDEX: Array
(
[12] => Array
(
[A] =>
[B] => 6
)
[13] => Array
(
[A] =>
[B] => 7
)
[14] => Array
(
[A] =>
[B] => 8
)
)
which are the right values (although I'm not sure if the A column should be populated as it is in my spreadsheet) i.e. Yes, No and N/A...
If you've got this far, thanks and hopefully you can help or point me in the right direction.
Much appreciated,
Adam.
