#REF! error in Chart data source reference when source worksheet has exclamation mark

Aspose.Cells Product Family
1

Hi,

In Excel, I have created a chart whose category and series values come from cells in a worksheet whose name contains an exclamation mark (!). When I use Aspose.Cells to read the category and series references, I receive a =#REF! error instead of the proper reference (e.g. =‘Table!’!$A$1:$B:$10).

I have attached an example Workbook, and the following code will reproduce the problem.

static void Main(string[] args)
{
Workbook wkbk = new Workbook(“spreadsheet.xlsx”);

foreach (Worksheet wks in wkbk.Worksheets)
{
foreach (Chart chart in wks.Charts)
{
SeriesCollection dataSource = chart.NSeries;

Console.WriteLine("Category: " + dataSource.CategoryData);
foreach (Series series in dataSource)
{
Console.WriteLine("Series Name: " + series.Name);
Console.WriteLine("Series Values: " + series.Values);
}
}
}
}

The output should be:

Category: =#REF!
Series Name: Amount
Series Values: =#REF!

Can you please have a look at the problem? Thanks.

2
Hi,

Thanks for providing us template file and sample code.

I observed the issue as you mentioned by using your sample code with your template file. I got "#REF!" error while obtaining Chart data source reference when source worksheet has exclamation mark "!" in its name.
e.g
Sample code:

static void Main(string[] args)
{
Workbook wkbk = new Workbook("spreadsheet.xlsx");

foreach (Worksheet wks in wkbk.Worksheets)
{
foreach (Chart chart in wks.Charts)
{
SeriesCollection dataSource = chart.NSeries;

Console.WriteLine("Category: " + dataSource.CategoryData);
foreach (Series series in dataSource)
{
Console.WriteLine("Series Name: " + series.Name);
Console.WriteLine("Series Values: " + series.Values);
}
}
}
}

I have logged a ticket with an id "CELLSNET-42856" for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.


3

Hi,

Please try our latest version/fix: Aspose.Cells for .NET v8.1.2.2


We have fixed the issue now.

Let us know your feedback.

Thank you.

4

Just confirming the issue mentioned is fixed now.

However, there is still a minor problem when the chart’s series value references a name range (Excel seems to allow chart series to reference worksheet-scoped name range but not workbook-scoped name range). I get

Calculations{!}!ClientSeries

instead of

’Calculations{!}’!ClientSeries

this is minor and I’m too lazy to come up with an example so it is up to you if you want to fix it or not…

5

Hi Lawrence,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your original issue has resolved with the latest fix.

For your next issue, it will be helpful if you could provide us some sample Excel and source code. You can provide it at the time of you convenience. It will help us look into your issue precisely and we will be able to fix it soon.

6

The issues you have found earlier (filed as CELLSNET-42856) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.