PHPExcel Error on Charts Using Named Ranges

Question

Using PHPExcel 1.7.8 I have a 2007 xlsx Excel File, consisting of a Pie Chart on Sheet1 and two columns of simple data in Sheet2.

The Workbook has a named range defined for both columns in Sheet2, one for labels, the other for data. The pie chart in Sheet1 then references these named ranges meaning it will auto update as data is added/removed from Sheet2.

Using PHPExcel I can read the sheet, edit data in Sheet2 and write back to Excel 2007. However, using the setup noted above, where a chart uses named ranges, PHPExcel throws the error Invalid cell coordinate CHARTDATA when reading the Excel, having looked further it seems PHPExcel doesnt read any prexisting named ranges but loses them, though these can then be defined again via code- the error still persists.

Crucially what Im after is using PHPExcel to load a spreadhseet which has a graph in it, edit the underlying data -adding or removing rows, and then write back to Excel with the graph updating accordingly.

Many thanks for any help!

Full error below:

Fatal error: Uncaught exception 'PHPExcel_Exception' with message 'Invalid cell coordinate CHARTDATA' in /home/content/34/6122134/html/core.excel/Classes/PHPExcel/Cell.php:539
Stack trace:
#0 /home/content/34/6122134/html/core.excel/Classes/PHPExcel/Cell.php(674): PHPExcel_Cell::coordinateFromString('CHARTDATA')
#1 /home/content/34/6122134/html/core.excel/Classes/PHPExcel/Cell.php(693): PHPExcel_Cell::rangeBoundaries('ChartData')
#2 /home/content/34/6122134/html/core.excel/Classes/PHPExcel/Chart/DataSeriesValues.php(298): PHPExcel_Cell::rangeDimension('ChartData')
#3 /home/content/34/6122134/html/core.excel/Classes/PHPExcel/Chart/DataSeries.php(350): PHPExcel_Chart_DataSeriesValues->refresh(Object(PHPExcel_Worksheet), false)
#4 /home/content/34/6122134/html/core.excel/Classes/PHPExcel/Chart/PlotArea.php(121): PHPExcel_Chart_DataSeries->refresh(Object(PHPExcel_Worksheet))
#5 /home/content/34/6122134/html/core.excel/Classes/PHPExcel/Chart.php(499): PHPExcel_Chart_PlotArea->refresh(Object(PHPExcel_Worksheet))
#6 /home/conten in /home/content/34/6122134/html/core.excel/Classes/PHPExcel/Cell.php on line 539

Is it possible to use regular ranges instead of named range? I haven't worked in PHP. The error tries to convert string address to range, which I think doesn't support named range. — shahkalpesh
In Excel 2007 setting regular ranges for charts results in them showing labels for rows that dont have data (e.g. A:A) — SW4
In that case, cant the range be limited to rows having data instead of A:A? — shahkalpesh
Not sure how you can do this in Excel 2007 without using defined ranges, using OFFSET when setting up charts is no accepted — SW4

Alan Doyle Alan Doyle · Accepted Answer · 2017-03-08T22:52:35

I had this issue as well. open Excel's Name Manager and make sure that the named range is not #REF! or otherwise broken.

Also, make sure you don't reference a named cell from another sheet. that will also break PHPexcel..

hope that helps!

(make sure you are using the latest version)

PHPExcel Error on Charts Using Named Ranges

1 Answers