0

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

urreta17
  • 183
  • 4
  • 20
  • 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 Jan 14 '16 at 19:00
  • 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 Jan 14 '16 at 19:23

1 Answers1

0

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.

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • Let's say I get excel with all the information already loaded. My goal was just to get the value of the fields, not knowing that depended on how these fields (depending on other excel) were loaded. So there's no way I can read the file as data having the same information is obtained from another excel? – urreta17 Jan 14 '16 at 20:05
  • Unclear what you mean! You can't guarantee that the result in `calculatedValue` will be the same as the result that would be returned by that external file.... as I said, calculation may have been disabled in MS Excel, or the data in the external file may have been changed since the last time it was accessed from your current file. Even MS Excel itself can't guarantee it if it can't access the external file to execute a recalculation – Mark Baker Jan 14 '16 at 20:16
  • Sorry if I was not clear. What I wanted to say that I do not care how cells excel file is complete, because I just want to read. But if I want to know whether the fact that these cells are completed from another excel, is a problem to read them. – urreta17 Jan 14 '16 at 20:20
  • Yes! It's a problem to read them.... They can't be read directly from the external file so the formula can't be evaluated. All that you can do is get the `calculatedValue` property, which may or may not be correct. So the problem is that you can't verify that the data returned is accurate or correct or up-to-date. – Mark Baker Jan 14 '16 at 20:22
  • I do understand. Sorry to bother you, but could get a reference to the use of calculatedValue function, use this link http://hitautodestruct.github.io/ reference information but I saw there. Thank you. – urreta17 Jan 14 '16 at 20:41
  • I've seen how to implement use. But I have seen that the result reflects, it is not exactly the string whose content that cell, but "boolean false". This is what you meant he could return as a result? – urreta17 Jan 14 '16 at 20:50
  • `toArray()` does not access the `calculatedValue` property at all, and cannot calculate to result of a formula referencing an external file. If you want to access `calculatedValue`, then you need to do it yourself on a cell by cell basis using `getOldCalculatedValue()`; `toArray()` will not do this for you – Mark Baker Jan 14 '16 at 20:55
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/100706/discussion-between-urreta17-and-mark-baker). – urreta17 Jan 14 '16 at 21:36
  • I sent a message via chat. – urreta17 Jan 14 '16 at 21:44