We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

How to set data range for chart in internal worksheet?

I am adding a chart and want to set it’s data without the need to work with external worksheets. It seems that the addChart method creates some sample data. If the number of categories/series of my data does not match that of the sample data, how do I change the dimensions of the data? The only method I found is the IChartData.setRange method, but that requires a sheet name. I just guessed “Sheet1” for the sheet name from the sample code at https://github.com/Muhammad-Adnan-Ahmad/Aspose.Slides-for-Java, using a range of "Sheet1!$A$1:$D$4". But this is not documented anywhere, nor do I find a method to read the sheets from the internal worksheet or e. g. an IChartData.getRange method.

Can I be sure the name of the sheet for all charts I create using Aspose.Slides, even if I create many charts in a presentation, and on different operating systems with different operating system languages, is always “Sheet1”?

Here is the sample code I used for testing:
IChart chart = slide.getShapes().addChart(ChartType.StackedColumn, 50, 100, 500, 380);
IChartDataWorkbook wb = chart.getChartData().getChartDataWorkbook();
wb.getCell(0, 0, 1).setValue(“A”);
wb.getCell(0, 0, 2).setValue(“B”);
wb.getCell(0, 0, 3).setValue(“C”);
wb.getCell(0, 1, 0).setValue(“2018”);
wb.getCell(0, 1, 1).setValue(10);
wb.getCell(0, 1, 2).setValue(13);
wb.getCell(0, 1, 3).setValue(16);
wb.getCell(0, 2, 0).setValue(“2019”);
wb.getCell(0, 2, 1).setValue(3);
wb.getCell(0, 2, 2).setValue(20);
wb.getCell(0, 2, 3).setValue(7);
wb.getCell(0, 3, 0).setValue(“2020”);
wb.getCell(0, 3, 1).setValue(15);
wb.getCell(0, 3, 2).setValue(6);
wb.getCell(0, 3, 3).setValue(12);
chart.getChartData().setRange(“Sheet1!$A$1:$D$4”); // “Sheet1” is just a guess
setSeriesColor(chart,0, new Color(200, 0, 0));
setSeriesColor(chart,1, new Color(0, 200, 0));
setSeriesColor(chart,2, new Color(0, 0, 200));

The addChart() method adds a default chart of type and size you give in parameters. But API give you complete control of clearing the default chart series and categories and add them as per you requirements. There are multiple options for adding chart data:

  1. Using API to add the chart data from scratch

  2. Setting chart data using external workbook

The purpose of setRange method is to set the range of cells for chart data whether you are using internal chart worksheet created using PowerPoint and Aspose.Slides or any other external workbook. In case of internal chart data workbook the worksheet name will always be Sheet1. In case of any other external workbook that you are trying to set, you need to know the worksheet name prior and set that accordingly in your code.

For your requirement related to getting range of chart data, I have added a ticket with ID SLIDESJAVA-38148 as new feature request to further investigate the possibility of implementing the requested support.

Hi Mudassir,
Thank you for your answer. This means we can hard code “Sheet1” as the sheet name for internal workbooks.
Thank you as well for the feature rquest.

Yes, in case of internal chart data workbook this would be the name always as it is as standard using MS Excel too.