Reading formula throws "3D Range references are not yet supported"

Topics: Developer Forum, User Forum
Jan 4, 2011 at 3:31 PM

Hi!

While reading the following formula, I get a "3D Range references are not yet supported" exception

SUMIF(Detail!$K:$K,"CP",Detail!$C:$C)

The same error occurs when setting bounds to the cell range :

SUMIF(Detail!$K1:$K1000,"CP",Detail!$C1:$C1000)

I thought that a 3D range reference was a formula calling multiple sheets (like SUM(Sheet1:Sheet2!$K1...) )

Did I misunderstood something or is this formula just not readable by PHPExcel for now?

 

Thanks for your help, and happy new year

Coordinator
Jan 5, 2011 at 11:46 AM
LNA wrote:

While reading the following formula, I get a "3D Range references are not yet supported" exception

 SUMIF(Detail!$K:$K,"CP",Detail!$C:$C)

 The same error occurs when setting bounds to the cell range :

SUMIF(Detail!$K1:$K1000,"CP",Detail!$C1:$C1000)

 I thought that a 3D range reference was a formula calling multiple sheets (like SUM(Sheet1:Sheet2!$K1...) )

Did I misunderstood something or is this formula just not readable by PHPExcel for now?

 This issue is partially resolved in the latest SVN code. Using the formula:

SUMIF(Detail!$K1:$K1000,"CP",Detail!$C1:$C1000)

specifying a cell range will return the correct result

You can access the latest SVN code via the "Source Code" tab here on PHPExcel.net, and the only file needed to resolve this is Classes/PHPExcel/Calculation.php

Row and column ranges such as

SUMIF(Detail!$K:$K,"CP",Detail!$C:$C)

or

SUMIF(Detail!$2:$2,"CP",Detail!$2:$2)

still cause some problems

Jun 22, 2011 at 5:51 AM
Edited Jun 22, 2011 at 9:27 AM

HI!

While writing the following formula, I get a "3D Range references are not yet supported" exception:

"Fatal error: Uncaught exception 'Exception' with message '�тоги!B6 -> �тоги!C6 -> 3D Range references are not yet supported' in Y:\home\localhost\172.29.9.233\reporting\PHPExcel\Cell.php:288 Stack trace: #0 Y:\home\localhost\172.29.9.233\reporting\PHPExcel\Writer\Excel2007\Worksheet.php(984): PHPExcel_Cell->getCalculatedValue() #1 Y:\home\localhost\172.29.9.233\reporting\PHPExcel\Writer\Excel2007\Worksheet.php(930): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'B6', Array, Array) #2 Y:\home\localhost\172.29.9.233\reporting\PHPExcel\Writer\Excel2007\Worksheet.php(82): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array) #3 Y:\home\localhost\172.29.9.233\reporting\PHPExcel\Writer\Excel2007.php(266): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array) #4 Y:\home\localhost\172.29.9.233\reporting\reporting.php(62): PHPExcel_Writer_Excel2007->save('k in Y:\home\localhost\172.29.9.233\reporting\PHPExcel\Cell.php on line 288".

=ЕСЛИ(ЕНД(ВПР(A7;Выдача!A5:G540;2;ЛОЖЬ));0;ВПР(A7;Выдача!A5:G540;2;ЛОЖЬ))

Thanks for your help.

Jan 23, 2012 at 11:39 AM

Hi,

Same issue here! like 3D Range references are not yet supported is it possible to get this value by other way.

 

please let me know.

 

thanks

Apr 3, 2013 at 9:05 AM
Hi all,

Any idea how to fix this exception issue. I have the same issue with this formulae:
=(SUMPRODUCT(C466:H466,'Rekenmodel huidige situatie'!$B$44:'Rekenmodel huidige situatie'!$G$44)/1000)+(SUMPRODUCT(J466:O466,'Rekenmodel huidige situatie'!$B$44:'Rekenmodel huidige situatie'!$G$44)/1000)

when this one work correctly:
=(SUMPRODUCT(C426:H426,'Rekenmodel huidige situatie'!$B$44:$G$44)/1000)+(SUMPRODUCT(J426:O426,'Rekenmodel huidige situatie'!$B$44:$G$44)/1000)

However I don't see much difference between both formulae. Same number of pages involved ...

Thanks for your help.

I really love this library and I hope I can get it working as soon as possible.

Else, in the other case, do someone can propose a good php-excel library which doesn't have this issue?

Thanks to all.
Coordinator
Apr 3, 2013 at 9:31 AM
loicf wrote:
Any idea how to fix this exception issue
The problem is that the PHPExcel calculation engine is seeing a range, the beginning and end each with a worksheet defined, and isn't checking whether they are the same worksheet. I can't recall whether the changes I've made to the calculation engine for the 1.7.9 release (rc1 is currently available in the master branch on github) fix this; but change
'Rekenmodel huidige situatie'!$B$44:'Rekenmodel huidige situatie'!$G$44
to
'Rekenmodel huidige situatie'!$B$44:$G$44
and it should work without issue
I really love this library and I hope I can get it working as soon as possible.
Support for other related issues in this thread is not going to be quick. I've started the rewrite of the calculation engine to include row and column ranges such as Detail!$K:$K or Detail!$1:$3, and this should allow ,'Rekenmodel huidige situatie'!$B$44:'Rekenmodel huidige situatie'!$G$44 but it's going to take me a large part of the next few months to complete it.
Else, in the other case, do someone can propose a good php-excel library which doesn't have this issue?
For full support of all Excels formulae, you need one of the following 3 options:
  • Ilia Alshanetsky's Excel extension now on github (requires commercial libXL component)
  • PHP's COM extension (requires a COM enabled spreadsheet program such as MS Excel or OpenOffice Calc running on the server)
  • The Open Office alternative to COM (PUNO) (requires Open Office installed on the server with Java support enabled)
No other PHP/Excel libraries that I'm aware of have a calculation engine
Apr 3, 2013 at 10:10 AM
IHi Mark,

Thanks a lot for your fast answer. Changing the formulae like you suggested works with 1.7.8 release. I will wait the 1.7.9 version to be available on composer before upgrading.

Again, thanks a lot.
Apr 11, 2013 at 7:36 AM
Hi Mark,

I found an issue which may be related to the previous problem.

The issue is when I try to get the calculated value of a cell, it outputs a result totally incorrect.

The formulae is the following:
=(C144+J144+Q144)*'Rekenmodel huidige situatie'!$B$81+(K144+R144+D144)*'Rekenmodel huidige situatie'!$B$87+(E144+L144)*'Rekenmodel huidige situatie'!$B$93+(I144+P144)*$B$178

Any idea if the issue is related or not?

Don't hesitate to ask me if you need any more information.

Thanks per advance for your help.