Formula =MAX(Data!A:A) gives Invalid cell coordinate error

Topics: Developer Forum
Aug 2, 2010 at 5:04 PM

Hi

Are formulas with ranges like sheet!A:A recognised by the calculation engine ?

PHPExcel 1.7.3c

[02-Aug-2010 16:51:13] PHP Fatal error:  Uncaught exception 'Exception' with message 'Invalid cell coordinate.' in PHPExcel/Classes/PHPExcel/Calculation.php:2088
Stack trace:
#0 PHPExcel/Classes/PHPExcel/Cell.php(285): PHPExcel_Calculation->calculateCellValue(Object(PHPExcel_Cell), true)
#1 PHPExcel/Classes/PHPExcel/Writer/Excel2007/Worksheet.php(969): PHPExcel_Cell->getCalculatedValue()
#2 PHPExcel/Classes/PHPExcel/Writer/Excel2007/Worksheet.php(910): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'B4', Array, Array)
#3 PHPExcel/Classes/PHPExcel/Writer/Excel2007/Worksheet.php(82): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array)
#4 PHPExcel/Classes/PHPExcel/Writer/Excel2007.php(258): PHPExcel_Writer_E in PHPExcel/Classes/PHPExcel/Calculation.php on line 2088

Coordinator
Aug 2, 2010 at 5:15 PM

They are recognised correctly if you use the latest SVN code.

This is issue 13515, which has been fixed in the repository

Aug 3, 2010 at 8:05 AM

Neat - Cheers

Aug 3, 2010 at 9:27 AM

$setup_sheet->setCellValueExplicitByColumnAndRow(1, $row, '=MAX(Data!A:A)', PHPExcel_Cell_DataType::TYPE_FORMULA);

<br />
<b>Fatal error</b>:  Uncaught exception 'Exception' with message 'Invalid cell coordinate.' in PHPExcel/Classes/PHPExcel/Calculation.php:2088
Stack trace:
#0 PHPExcel/Classes/PHPExcel/Cell.php(292): PHPExcel_Calculation-&gt;calculateCellValue(Object(PHPExcel_Cell), true)
#1 PHPExcel/Classes/PHPExcel/Writer/Excel2007/Worksheet.php(969): PHPExcel_Cell-&gt;getCalculatedValue()
#2 PHPExcel/Classes/PHPExcel/Writer/Excel2007/Worksheet.php(910): PHPExcel_Writer_Excel2007_Worksheet-&gt;_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'B4', Array, Array)
#3 PHPExcel/Classes/PHPExcel/Writer/Excel2007/Worksheet.php(82): PHPExcel_Writer_Excel2007_Worksheet-&gt;_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array)
#4 PHPExcel/Classes/PHPExcel/Writer/Excel2007.php(260): PHPExcel_Writer_E in <b>PHPExcel/Classes/PHPExcel/Calculation.php</b> on line <b>2088</b><br />

Change Set 57274
- Bugfix:   (MBaker) Work item 13515 - "Invalid cell coordinate" error when formula access data from an other sheet




$setup_sheet->setCellValueExplicitByColumnAndRow(1, $row, '=MAX(Data!A1:A16000)', PHPExcel_Cell_DataType::TYPE_FORMULA);

WORKS



Therefore ranges of the A:A style (no row numbers) are not evaluated correctly.

Using A1:A{last_row} in the caluculation mode works

Just need to export A:A in the writing mode.

Coordinator
Aug 3, 2010 at 11:09 PM

The current version of the calculation engine doesn't support cell ranges specified by only row or by column ID (e.g. A:A, B:C, 1:1, or 2:3), only by row and column ID (e.g. A1:B2).

This will be available in the next release (Version 1.7.4), which should be available sometime over the next week. I've been testing this feature in the calculation engine - everything seems to be working there - and will be testing the readers and writers this weekend. At the very least, it should be working for Excel2007.

Note that 3-D ranges (e.g. Sheet1!A1:Sheet3!C3, or Sheet1!B:Sheet3!B) aren't supported in PHPExcel.

Aug 4, 2010 at 8:41 AM

Awesome.

 

What with cell references, named ranges and functions sometimes goung back the Lotus use of the @ to preface functions alone would make things easier.

 

Edge cases make basket cases I guess.

Coordinator
Aug 4, 2010 at 9:20 AM
JohnGriffiths2 wrote:

What with cell references, named ranges and functions sometimes goung back the Lotus use of the @ to preface functions alone would make things easier.

Yeah, it's fun! Excel's parser isn't particularly clean, and the PHPExcel parser is getting kludgy too just to handle the variations. Ranges and intersections are particularly awkward (a space is the intersection operator, just to confuse parsers). A1:C3 is the most common form for ranges, or the use of named ranges like MyRange.
Standard parser rules would dictate that 3:4 is two numeric values with the : operator (which would give a null result), but (only when numbers are used with : they're cell ranges). At least B:B can't be confused as easily, "B" would be a string, B (without the quotes) is always a column range reference, but can only be used with the : operator.
And just to confuse still further, Monkey1:Monkey2 is a valid range where Monkey1 and Monkey2 are both named ranges, and named ranges be combined with a column reference when using the : operator, so Monkey:B is also valid [I've not yet tested if my code will handle this case].
Then again, the only difference between a named range and a column ref is the length of the string, so IV or ABC are column references (Excel 2007 allows up to 16,384 columns) but ZZZ could be a valid named range... I use an arbitrary 3 character max test for column range and treat anything longer as a named range.

You'll be pleased to know that we actually support the @ prefix before a function

JohnGriffiths2 wrote:

Edge cases make basket cases I guess.

They certainly drive me crazy.
But some of the functions are worse. There's a whole level of complexity depending on what is passed to a function. SUM(A1:C3) uses different logic compared with SUM({1,2,3;4,,6;7,8,NULL}) even though A1:C3 might contain exactly the same values as the matrix... and none of these minor idiosyncracies are documented anywhere.

Coordinator
Aug 4, 2010 at 11:50 PM

Update on row ranges and column ranges:

Calculation engine appears to handle all my tests correctly.

Excel2007 Reader and Writer handle formulas containing row and column ranges correctly.

Excel5 Reader translates a row range such as 1:1 to $A1:$IV1 and a column range like A:A to A$1:A$65536, so I'll be investigating that further. Excel5 Writer goes >> bang <<, taking the web server with it. Any PHP code fault that crashes Apache is serious, so that will definitely need fixing.