nSeries.add(..) throws exception if worksheet's name contains whitespace

Aspose.Cells Product Family
1

Hello,

to create charts whose data is on a different sheet, we used to write lines like:

worksheet.setName(“MeineDaten”);
// …
nSeries.add(“MeineDaten!A4:B9”, false);

Now if the sheet name is “Meine Daten” instead of “MeineDaten”, Aspose Cells 2.3 throws an Exception which it did not in the previous version:

Exception in thread “main” java.lang.IllegalArgumentException: Invalid data area[Meine Daten!A4:B9].
at com.aspose.cells.NSeries.a(Unknown Source)
at com.aspose.cells.NSeries.a(Unknown Source)
at com.aspose.cells.NSeries.add(Unknown Source)
at XlsTemplateToPdfTest.main(XlsTemplateToPdfTest.java:62)


We do have a lot of sheet names with spaces in them, so this is a severe problem for us. Please, can you provide a quick fix for this issue? I have attached the ready-to-run source code.

Thank you very much in advance
Lennart

2

Hi Lennart,

The issue was found based on your sample project and logged into our Issue Tracking System as 18124.

We will notify you about the updates regarding this issue as this thread has been linked with this issue.

Best Regards

3
Hi,

After further investigation, we come to know it is not an issue. For setting formulas, when sheet name contains special characters such as space, the sheet name should be quoted by "'". That is same as MS Excel. So you should change your lines of code as following:
nSeries.add("'" +SHEET_NAME + "'!A4:B9", false);
nSeries.setCategoryData("'" +SHEET_NAME + "'!A4:A9");


Thank you.
4

Hello!
I have the same problem but with “!” char in sheetname. Could u please help me to solve the problem?

string chartItemPath = string.Format("’{0}’!{1}:{2}", CurrentSheet.Name, leftCell.Name, rightCell.Name);
int index = chart.NSeries.Add(chartItemPath, false);

If CurrentSheet.Name contains “!” for example “ACHTUNG!” i have an exception:
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

5

Hi,

Kindly give us sample code and template file(s) (if you have) to show the issue, we will check it soon.

Also, kindly try our latest version/fix v2.5.3.x

Thank you.

6

Hi there!
I had installed Aspose.net 5.3.3 but it dont solve my problem, code see below:
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add(“Achtung_!Alarm"); // an exception(NullReferenceException)
//Worksheet worksheet = workbook.Worksheets.Add("Achtung:_Alarm”); // no exception, but no one Nseries at chart

worksheet.Cells[“A1”].PutValue(50);
worksheet.Cells[“A2”].PutValue(100);
worksheet.Cells[“A3”].PutValue(150);
worksheet.Cells[“B1”].PutValue(4);
worksheet.Cells[“B2”].PutValue(20);
worksheet.Cells[“B3”].PutValue(50);

//Adding a chart to the worksheet
int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Pyramid, 5, 0, 15, 5);
//Accessing the instance of the newly added chart
Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];
//Adding SeriesCollection (chart data source) to the chart ranging from “A1” cell to “B3”
string path = string.Format(“‘{0}’!{1}:{2}”, worksheet.Name, “A1”, “B3”);
chart.NSeries.Add(path, true);

//Saving the Excel file
workbook.Save(“C:\book1.xls”, SaveFormat.Xlsx);

7

Hi,


Thank you for the source code.
I have tried your scenario with Aspose.Cells for .NET v5.3.3.1, it will work for v5.3.3 as well. Please find below your source code with minor correct [see Bold].

C# Sample Code

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets.Add(“Achtung_!_Alarm”);


worksheet.Cells[“A1”].PutValue(50);

worksheet.Cells[“A2”].PutValue(100);

worksheet.Cells[“A3”].PutValue(150);

worksheet.Cells[“B1”].PutValue(4);

worksheet.Cells[“B2”].PutValue(20);

worksheet.Cells[“B3”].PutValue(50);


//Adding a chart to the worksheet

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Pyramid, 5, 0, 15, 5);


//Accessing the instance of the newly added chart

Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];


//Adding SeriesCollection (chart data source) to the chart ranging from “A1” cell to “B3”

string path = string.Format("{0}:{1}", “A1”, “B3”);


chart.NSeries.Add(path, true); //Add NSeries Range without mentioning the Sheet name


//Saving the Excel file

workbook.Save(“C:\temp\book1.xlsx”, SaveFormat.Xlsx);

8

Hi.
Thanks for the answer, but it will not work for my project because a have 2 worksheets, the first one contains data for the chart and the second chart. that is why i have to use sheet name in nserie path. Code example is just an example not a working project.

9

Hi,


Please find below the modified code. I changed the name of first sheet name “Achtung_!_Alarm” to “Data”. Because chart.NSeries.Add method accepts the data range as a string that has only one “!” sign [delimiter for sheet name]. Also I inserted a new worksheet and added the Chart to it. Please also see the attached output for your reference.

C# Code:

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

worksheet.Name = “Data”;


worksheet.Cells[“A1”].PutValue(50);

worksheet.Cells[“A2”].PutValue(100);

worksheet.Cells[“A3”].PutValue(150);

worksheet.Cells[“B1”].PutValue(4);

worksheet.Cells[“B2”].PutValue(20);

worksheet.Cells[“B3”].PutValue(50);


int sheetindex = workbook.Worksheets.Add();

Worksheet chartSheet = workbook.Worksheets[sheetindex];

chartSheet.Name = “Chart”;


//Adding a chart to the newly added worksheet

int chartIndex = chartSheet.Charts.Add(Aspose.Cells.Charts.ChartType.Pyramid, 5, 0, 15, 5);


//Accessing the instance of the newly added chart

Aspose.Cells.Charts.Chart chart = chartSheet.Charts[chartIndex];


string path = string.Format("{0}!{1}:{2}", “=Data”, “A1”, “B3”);

chart.NSeries.Add(path, false);


//Saving the Excel file

workbook.Save(“C:\temp\book1.xlsx”, SaveFormat.Xlsx);

10

Hi there.
Thanks for the answer, but it can not solve the problem too, unfortunately. In real i have a work book with a couple of worksheets it could be 1, 2, 3, … N. the name of each sheet defined by user, i cant just make every sheet like Data#1, Chart#1, Data#2 and so on, it will be unexpected for the user and anti-usability. That is why i really need save the original name of sheets, and use sheet name in nseriesPath. It might be other workaround?
thanks.

11

Hi,


You may use the actual sheet names instead of Data/Chart. But you can not use characters like “!” and “=” in those sheet names. Because Aspose.Cells API use these characters to parse the data range from a string.
12

it is so sad(((. anyway thanks for the answer. seems it should be fixed in next build.