Read excel with PHPExcel dependent on another file

Question

I started working with PHPExcel to read a file.

In my first steps, I would get a portion of the file because there is another that is not of my usefulness. I saw that the rangeToArray function was useful for this.

Just as a test, I tried to obtain from the start in row A12 to the end at Y25.

$sheetData = $objPHPExcel->getActiveSheet()->rangeToArray('A12:Y25');

This returns something like this:

array (size=14)
  0 => 
    array (size=25)
      0 => float 258500
      1 => string '#REF!' (length=5)
      2 => string 'Nº equivocado' (length=14)
      3 => string 'Nº equivocado' (length=14)
      4 => boolean false
      5 => boolean false
      6 => boolean false
      7 => boolean false
      8 => boolean false
      9 => boolean false
      10 => null
      11 => boolean false
      12 => string '#DIV/0!' (length=7)
      13 => string '166,056' (length=7)
      14 => float 0
      15 => string '11,070' (length=6)
      16 => string '1,510' (length=5)
      17 => string '12,580' (length=6)
      18 => string 'Transferencia' (length=13)
      19 => string 'Cheque' (length=6)
      20 => string '1/1/2016' (length=8)
      21 => string '1/31/2016' (length=9)
      22 => null
      23 => null
      24 => string 'EL COSTO ANUAL ES 10% SUPERIOR AL MENSUAL X 12, DEBIDO AL 10% DE COMISION DE FASCIOLI' (length=85)

But the fact is that some values in the array does not match the values that have some cells.

The row is selected in image capture, it is what I'm trying to get to the rangeToArray function.

As I saw the values returned by the array, is not exactly the string that is the cell in excel, I decided to try to get that particular cell.

$sheetData = $objPHPExcel->getActiveSheet()->getCell('E12');

And I could see that the contents of the cell is stored in a variable call "calculatedValue".

object(PHPExcel_Cell)[3121]
  private 'value' => string '=IF($A12=$B12,VLOOKUP($A12,#REF!,2))' (length=36)
  private 'calculatedValue' => string 'PRINA EDIFICIO                ' (length=30)
  private 'dataType' => string 'f' (length=1)
  private 'parent' =>

Then I saw that the value of the cell with rangeToArray function returns "boolean false" with the getCell function, returns as private 'calculatedValue' => string 'PRINA EDIFICIO' (length = 30)

I could also see that these cells are filled with the values collected from another excel.

=SI($A12=$B12;CONSULTAV($A12;'C:\wamp\www\PHPExcel\Documentation\Examples\Reader\sampleData\[BASE_RETENIDOS.xls]BASE DE DATOS'!$B$5:$I$729;2))

That's why my question is if I can get the values in string format if obtained without relying on other excel.

Thanks

![EXCEL FILE, ROW TO GET ]1

ok, so you're trying to build something, but your tools are in another building. you're basicallya sking "how can I use the tools without going to get them?" and in short. "you can't". your spreadsheet has values which depends on external data, which means you have to provide that data. — Marc B
I understand what you say. But the data of this sheet are dynamic. By completing a cell, I get data from another excel file related to the field that is full. Something like an ID number. That should provide data from another file? — urreta17

Mark Baker Mark Baker · Accepted Answer · 2016-01-14T19:45:25

There is no way PHPExcel can access data from an external spreadsheet or external file that may or may not even be accessible from your webserver.

There is a "fallback" option to getOldCalculatedValue() for a cell that may or may not help, although the toArray() function doesn't check against this in any way.

getOldCalculatedValue() will return the value from a cell containing a formula from the last time a calculation was run against that cell in MS Excel itself.... of course, calculation could be disabled in MS Excel when the spreadsheet was created (in which case it may be unpopulated, or contain old values that don't reflect current data that the calculation might otherwise have used. And if it relies on data from external files, it may no longer be an accurate calculated result, making it highly unreliable.

This is the value that is stored in the calculatedValue property of the cell.

Read excel with PHPExcel dependent on another file

1 Answers