Referencing Worksheets in different Workbooks?

Topics: User Forum
Jun 19, 2009 at 3:05 PM

Does PHPExcel's $cell->getCalculatedValue() support calculations which reference other xls files? Example: ='|someOtherExcelDoc.xls|Other Worksheet'!A1

Coordinator
Jun 19, 2009 at 4:59 PM
Edited Jun 19, 2009 at 5:00 PM

Simple answer is no. Even the new calculation engine won't do that, because PHPExcel doesn't have direct access to any external workbook files.

Jun 24, 2009 at 5:51 PM
Should it at least still process what it can of the file?  I got an error when uploading a spreadsheet:

Notice: Undefined offset: 2 in /home/william/svn/ezyield_v3/library/D2EApplication/_packages/PHPExcel/Reader/Excel5.php on line 4237

Then I got a different error while trying to recreate the first error on a sheet that I can share with you:

Notice: Undefined index: in /Users/william/Downloads/1.6.7/Classes/PHPExcel/Shared/OLERead.php on line 232

<?php error_reporting(E_ALL); require_once("PHPExcel.php"); require_once("PHPExcel/Reader/Excel5.php"); require_once("PHPExcel/Shared/Date.php"); $excelReader = new PHPExcel_Reader_Excel5(); $excelReader->setReadDataOnly(true); echo "Loading referenceerror.xls\n"; $excelDoc = $excelReader->load('referenceerror.xls'); $activeWorksheet = $excelDoc->getActiveSheet(); $value = $activeWorksheet->getCellByColumnAndRow(0,1)->getValue(); var_dump($value);

http://www.mediafire.com/?sharekey=db041a7dd4757e53d2db6fb9a8902bda is the file.  I created it in Excel for Mac by referencing another file.  Opening it with OpenOffice and saving it will 'fix' the file so it can be read.

Jun 24, 2009 at 5:52 PM

Note that you can actually change that file so it has no external links and that A1 contains just '1' and not a calculation at all and the same error is given.

Developer
Jun 29, 2009 at 1:56 AM

@wccrawford: With that file I indeed get errors? I get errors like these when I try to read the xls file:

Notice: Undefined property: PHPExcel_Shared_OLERead::$rootentry in C:\xampp\htdocs\_phpexcel\PHPExcel\Classes\PHPExcel\Shared\OLERead.php on line 173

Notice: Undefined index: in C:\xampp\htdocs\_phpexcel\PHPExcel\Classes\PHPExcel\Shared\OLERead.php on line 173

Notice: Undefined index: in C:\xampp\htdocs\_phpexcel\PHPExcel\Classes\PHPExcel\Shared\OLERead.php on line 232

I will investigate the problem.

Jul 1, 2009 at 12:06 PM

Thanks for looking into this, Koyama.

Jul 2, 2009 at 7:49 PM

I have looked into this further and determined that I'm getting this error because the file is small enough to use 'smallblock' instead of 'bigblock'.

This is completely unrelated to my original problem, and I'm uninterested in this particular error.  If I can reproduce my original error, I'll post more information.

Jul 2, 2009 at 8:09 PM

For the error:

 

Notice: Undefined offset:  2 in /Users/william/svn/ezyield_v3/library/D2EApplication/_packages/PHPExcel/Reader/Excel5.php on line 4237

Notice: Undefined offset:  2 in /Users/william/svn/ezyield_v3/library/D2EApplication/_packages/PHPExcel/Reader/Excel5.php on line 4238

 

It seems to work If I insert this code at line 4236:

 

if(!isset($this->_sheets[$this->_ref[$index]['firstSheetIndex']]['name'])) { return false; }

 

This file reproduces the error:  http://www.mediafire.com/?sharekey=db041a7dd4757e53d2db6fb9a8902bda

To create the file, I took a file that referenced another file, but did not have the second file.  Then I loaded it with Excel for Mac, clicked 'ignore links', and then saved it again.  There may be something in particular about this file that is odd beyond the missing referenced file as I did not have this trouble with creating a file from scratch.

 

Developer
Jul 2, 2009 at 8:20 PM

Thanks for the update, I now posted this in the issue tracker. There is also a report for a similar bug in the php-excel-reader on Google Code. Not strange as they are based on the same class.

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10252

I will definitely have a look at your fix. It seems we are seeing two unrelated bugs at once here. I'm a bit confused right now, but we should get these things fixed. Will return and post back later this week.

Jul 6, 2009 at 6:04 PM

I've got another issue that I think is related to the last one somehow, but I'm not sure...  That's why I'm posting here and not on the issue you posted on the tracker.

If I pull up that last file I posted in Excel, C6 is 'L0' (it's a reference to another sheet that I don't have, but the value still shows.)

If I pull it up in PHPExcel, I get "='Sant''Elena'!$U430" .

The actual value is "='@:Srv2003sbs:DATI:Documenti:Pricing:[Pricing Santelena 2009.xls]Situazione'!$U430" .

For some reason, it seems to be re-writing the formula to use the current page instead of the other file, but then returns the formula instead of the computation.  (The other file does not exist on my computer.)

Jul 6, 2009 at 6:06 PM

Sorry, on that last post, I just realized (after I hit 'post') that I'm using 'GetValue' which would return the formula, so that's right.  GetCalculatedValue() returns 'NULL', instead of 'L0' as I'd expect.

Thanks,

William

Developer
Jul 8, 2009 at 10:07 AM

@wccrawford: Fixed those notices. There was indeed a bug in Excel5 reader:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10274

When a workbook is saved in MS Office Excel, it usually stores the value from the last calculation. That is why you can still see the values when you open the file next time even when the referenced external file is not there.

To retrieve those values in PHPExcel, do like this:
$sheet->getCell('C6')->getOldCalculatedValue();

Jul 8, 2009 at 5:12 PM

Thank you!  I just tried the latest SVN code with my test file and I do get the 'L0' value from A6 now, but I still get a lot of:

Notice: Undefined offset:  2 in /Users/william/src/phpexcel/Classes/PHPExcel/Reader/Excel5.php on line 4337

Notice: Undefined offset:  2 in /Users/william/src/phpexcel/Classes/PHPExcel/Reader/Excel5.php on line 4338

Thanks,

William

Jul 8, 2009 at 5:17 PM

Sorry, meant C6.  :)

Developer
Jul 8, 2009 at 6:19 PM

>> but I still get a lot of:...

Ok, I see I checked the patch in a bit too late. This error has already been solved. It should be gone in tomorrows source code release. Alternatively, you can download the patch found at the issue I linked to.

Jul 8, 2009 at 8:03 PM

Ah, okay.  I assumed when I checked out from SVN that it was always the latest.  I used that patch (manually, couldn't get it to work using the 'patch' command, but that's probably my fault) and everything seems to work great!

Thanks again!

May 10, 2010 at 1:39 PM

Hi... Am trying to read a file whose extension is changed(Like .doc to .xls) But am getting so many exceptions like

Notice: Undefined property: PHPExcel_Shared_OLERead::$wrkbook in C:\xampp\htdocs\Classes\PHPExcel\Shared\OLERead.php  on line 176

Notice: Undefined index: in C:\xampp\htdocs\assignment16\vijay\Classes\PHPExcel\Shared\OLERead.php on line 176

Notice: Undefined property: PHPExcel_Shared_OLERead::$wrkbook in C:\xampp\htdocs\assignment16\PHPExcel\Shared\OLERead.php on line 180

Notice: Undefined index: in C:\xampp\htdocs\assignment16\vijay\Classes\PHPExcel\Shared\OLERead.php on line 180

Notice: Undefined index: in C:\xampp\htdocs\assignment16\vijay\Classes\PHPExcel\Shared\OLERead.php on line 187

Notice: Undefined index: in C:\xampp\htdocs\assignment16\vijay\Classes\PHPExcel\Shared\OLERead.php on line 187

Notice: Undefined index: in C:\xampp\htdocs\assignment16\vijay\Classes\PHPExcel\Shared\OLERead.php on line 187

Notice: Undefined index: in C:\xampp\htdocs\assignment16\vijay\Classes\PHPExcel\Shared\OLERead.php on line 187

Am really not getting what to do with this... Please help me

Coordinator
May 15, 2010 at 12:05 PM

If you're trying to read a Word document using PHPExcel, then you can expect all kinds or errors.

May 17, 2010 at 11:33 AM

Its just for validating... If someone tries to read the invalid file(eg: some .doc renamed to .xls),if we try to open the file it shows  file format is not valid... what i want to do is if its such kind of file, the reader created still tries to read the file and shows the above exceptions..... I just have to catch those exceptions and display to users something like [Invalid file]. please find me out some way. I tried with canRead(), read and also is_readable.. nothing worked......

 

May 18, 2010 at 6:55 AM
Can you please tell if there is any inbuilt class or any other method to fix the above.?? As i said above i tried with canRead() and read(). But i didnt get the result expected.
May 25, 2010 at 7:23 AM

Please suggest something... I am waiting for the reply.

Coordinator
May 26, 2010 at 8:52 AM

There is no method built into PHPExcel that will do this for you, not without a lot of additional verification of the file format. The canRead() method only tests as far as validating that the file is an OLE file (that could be from any MS Office product) using the following logic in the canRead() method of /Classes/PHPExcel/Reader/Excel5.php

  try {
   // Use ParseXL for the hard work.
   $ole = new PHPExcel_Shared_OLERead();
   // get excel data
   $res = $ole->read($pFilename);
   return true;
  } catch (Exception $e) {
   return false;
  }

Feel free to extend this validation if you want. We get very few instances of people renaming .doc files as .xls, so it's very low on our priority list at the moment