Setting the data range of a chart based on a designer

Aspose.Cells Product Family
1

Hi,

I have a pie chart defined in a designer. It’s data range is based on a multiVariable named “Stats”. I set this data source programmaticaly in my java code.
myDesigner.setDataSource(“Stats”, stats);

The lines number of the data source could obviously change. So I need a simply way to change the data range of the chart. Something like
myChart.setDataRange(“A1:C” + linesNumber);

I can do it via Excel, but could not find a way to do it in Java.

Could someone help me ?

Thanks,
Jeff

2

Hi,

I think you may try to use ASeries.setValues() or ASeries.setXValues() method for your need, see the sample code below:

//…

//Access the series
ASeries serA = chart.getNSeries().get(index);

//Set axis values
serA.setValues("=Sheet1!A1:C" + linesNumber);

//OR
//Set X-axis values
// serA.setXValues("=Sheet1!A1:A" + linesNumber);

//…


Thank you.

3

Hi,

Thank you for your response.

I have tried the setValues/setXValues method. The data range is fine but my pie has only one color. There is also only one legend, while my XValues have 2 elements.

Jeff

4

Hi,

I have tested your scenario using our latest version/fix v2.5.1.1. It works absolutely fine. I have attached the input and output files here. Please check the input Excel file first to see the NSeries ranges for value axis and category data.

Here is the sample code to manipulate the chart series data range:

Sample code:

//Create a new Workbook.
Workbook workbook = new Workbook();

workbook.open(“d:\files\ggggpie_chart.xls”);

//Get the first worksheet.
Worksheet sheet = workbook.getWorksheets().getSheet(1);

Chart chart = sheet.getCharts().getChart(0);

int last_row = workbook.getWorksheets().getSheet(0).getCells().getMaxDataRow()+1;

//Set properties of nseries
chart.getNSeries().get(0).setValues(“Data!B2:B” + last_row);
chart.getNSeries().setCategoryData(“Data!A2:A” + last_row);

workbook.save(“d:\files\out_ggggpie_chart.xls”,FileFormatType.EXCEL2003);


If you still find the issue, kindly do provide the input file, output file and paste the code here (similar to what i have presented here). We will check it soon.



Thank you.

5

Hi,

I have tried with the latest version and it does not work anymore. I think this limit is due to the fact that my template is based on a multi variable.

In my data sheet, I have something like &=$Stats(noadd)

In my java code, I have designer.setDataSource(“Stats”, stats);

Jeff

6

Hi,

Please do create a simple console demo application, post us your template files and paste the complete sample code here, so that we can reproduce the issue you have mentioned. We will check it soon.

Thank you.