Problem with non-contiguous cells as Chart Series

Aspose.Cells Product Family
1

Hi,

again I have a smaller problem/question concerning charts and non-contiguous range of cells.

When I try to add an NSeries using the following area specification:

D6;F6 (note the semicolon)

Aspose.Excel throws an exception, even though this is perfectly valid to tell the chart to pick values from the two cells D6 and F6. I have also tried surrounding the list with brackets ( and ) but to no luck.

Does the NSeries Add() method check whether an area has been created just using colon characters?

Any help on this issue would be highly appreciated…

Regards

Kai Iske

2

Dear Kai,

Thanks for your consideration.

NSeries.Add can only add contiguous cells. If you want to add non-contiguous cells, you have to use seperate lines of code.

For example, in your case you can use:

nseries.Add(“D6”, false);
nseries.Add(“F6”, false);

3

Laurence,

now this is an easy one. Should have better read the documentation.

Thanks for this

Kai

4

Laurence,

I have just checked it and this is the result:

I want to set up a single series with value references like (e.g.)

Sereies 1
A1;C1;E1

Series 2
A2;C2;E2

When I use NSeries.Add() it will create a new series for each cell reference I add:

Series 1
A1

Series 2
C1

Series 3
E1

Series 4
A2

Series 5
C2

Series 6
E2

There is no way to specify a non-contiguous range of values for a single cell? I have checked this and it works find when I do this manually within Excel.

Any help would be appreciated.

Regards

Kai

5

Dear Kai,
This feature is added in hot fix 1.6.3.

Please use comma to seperate them, that’s same as MS Excel. For example, “A1,C1,E1”.
Previously I did not find this feature, thanks for you feedback.

And in 1.6.3, the AddCopy problem you found is also solved.

6

Laurence,

thanks for this NSeries works fine and also AddCopy does it is supposed to do.

However if Chart.NSeries.CategoryData would/could also support non-contiguous areas that would be great.

Thanks

Kai

7

Dear Kai,

Thanks for your suggestion.

I have told other users to release a new fix in this week to meet their needs. So time is not enough.

Do you need this feature urgently?

8

Laurence,

I could live with sometime within this week (November 3rd). We are about to go live within the next 2-3 weeks.

Regards

Kai

9

Dear Kai,

I will release a quick fix for you tomorrow(11/5). Will that be ok for you?

10

Dear Kai,
Please download the newest fix. This feature is available.

11

Laurence,

thanks for this I have just checked it and it works fine.

Regards

Kai

12

I seem to be having a problem getting the CategoryData to work properly.

I am using data from a different sheet.

The chart is on a sheet called “graph” and the data is on a sheet called “5 YR SUMMARY”. So to set the CategoryData, I use the string “5 YR SUMMARY!C6:H6” which contain the values.


Initial 1998 1999 2000 2001 2002

But when I open the resulting Excel document I get an error that says “Lost data link from one or more chart series”.

Then when I look at the graph the Category (X) axis label is set to “={0,0,0,0,0,0}”.

Any help is appreciated, as always.

I have discovered a solution…the problem is that you DO NOT need to reference the sheet since it is deduced from the Aseries of the Nseries.

So I should have used the string “C6:H6”.

13

Hi, thanks for your consideration.

If the chart locates in the same sheet with the data source, you can set series data and category data without sheet name. If not in the same sheet, you should add the sheet name, such as “5 YR SUMMARY!C6:H6”.

I don’t know what caused this problem and I have not found your problem in my machine.
I will publish a new release within two days. Could you wait for that new release?

Thanks for your patience.

14

Laurence,

maybe you have the time to also include control over an NSeries’ line properties like style, color and weight.

Thanks

Kai

15

Dear Kai,

Thanks for your suggestion.

Today I will release a new version to meet other customer’s need.

In the next step I will focus on the chart api enhancement, all the features you requested will be placed on my work list.

16

Hi,

I guess this is a nice thread to add this question to :slight_smile:

Would it be possible to add support for manipulating an axis’ scale?
Set minimum, maximum, stepping etc? We currently outpubt based numbers (100) only sometimes move below 100. If I can’t set the minimum number, I get lots of waisted space below the 100 line. If I was able to manipulate the axis’ scale, I would be happy.

Regards

Kai

17

Dear Kai,

Thanks for your suggestion.

Sure it’s possible to add this feature.

Now I am working on the enhance chart api. These days I start to do axies setting issues. Your request will be placed on my work list.

18

Hi,

I have a problem using non contiguous cells in my chart. If I understand the previous messages correct, then adding series with non-contiguous cells is only possible if these aren’t value pairs. Is that correct?

That’s what I want:
In cell A is my category and in cell E the values. So I only want to select the single cells, as it it possible in excel, and use that data for the chart. The result is that it’s always not vertical aligned and the series gets the wrong data from the series.

Do I just make a mistake or is that generally not possible?

Thanks in advance
Michael Mann

19

Dear Michael,

This feature is available now. Please try the following code:

Excel excel = new Excel();
excel.Worksheets[0].Cells["A1"].PutValue(1);
excel.Worksheets[0].Cells["b2"].PutValue(2);
excel.Worksheets[0].Cells["c3"].PutValue(3);

int chartIndex = excel.Worksheets[0].Charts.Add(ChartType.Column, 5, 5, 15, 10);
Chart chart = excel.Worksheets[0].Charts[chartIndex];
chart.NSeries.Add("A1,B2,C3", true);

excel.Save("abc.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response);

Could this serve your need?

20

Hi,

thanks for the information. But what I meant whas something like that.
Column A has the categories and Column 8 has the values. I only want to generate
a chart for these two value pairs and not the data in between. It is possible in excel so I thought maybe it’s possible with Aspose.Excel, too. But then I can reorder my data appearance.

By the way, is there a way to set the chart options like in excel? e.g. the value of each column on top of it?

Michael