phpExcel cannot retrieve Excel cells containing index - match function

0
votes

I need to import data from spreadsheets into a database. Initially, I used phpExcel to export each worksheet as a csv file, but wherever there was a cell containing an index - match function, phpExcel could not retrieve that cell value.

=INDEX(Sheet1!$E$2:$E$100,MATCH(TRUE,EXACT(B16,Sheet1!$A$2:$A$100),0))

I then decided to export the data directly from the spreadsheet into the database using phpExcel's toArray(null,true,true,true) to load the worksheet into an array. The second parameter ($calculateFormulas) is true, but phpExcel still cannot retrieve the cell value for any cell containing an index - match function.

I read in one post to use $objReader->setReadDataOnly(true) with caution - I gave it a go, but still no luck.

One solution is to open the spreadsheet, copy each worksheet and paste special as values, but I thought that the $calculateFormulas in toArray() would do that. The solution is not very appealing as I have about 50 spreadsheets each containing 5 worksheets, and they will be coming in on a regular basis :(

Any suggestions gratefully received.

1
excelphpexcel

1 Answers

0
votes

I had the same issue (a few moments ago), but couldn't solve it directly with PHPExcel. Instead I edited the cells in the Excel file, dividing the original formula in different cells.

Originally I had this formula in each cell (in this example C13):

  • =IF(INDEX(DatosIntel!$N$1:$N$77,MATCH(B13,DatosIntel!$H$1:$H$77,0))=0,$y$1,1)

Now, I divided in two (Q13 and C13):

  • =MATCH(B13,DatosIntel!$H$1:$H$77,0)
  • =IF(INDEX(DatosIntel!$N$1:$N$77,Q13,0)=0,$Y$1,1)

I even wrote the new content for C13 using PHPExcel:

  • $objTpl->getActiveSheet()->setCellValue('C13', '=IF(INDEX(DatosIntel!$N$1:$N$77,Q13,0)=0,$Y$1,1)');

It seems that a complex formula contained in one cell cannot be correctly parsed for reading using PHPExcel.

I hope this helps.