How to get the data range of a Excel Chart

Aspose.Cells Product Family
1

Hi, I want to get the data range of a Excel Chart and set a new value, but I don’t find a method to get it.
Is there a way to get the range?
获取图表区域.zip (64.6 KB)

2

@xuerui,

You may try to use Chart.getChartDataRange method to get the data range. Also, you may set the chart’s range using Chart.setChartDataRange method. See the following sample code to accomplish your task for your requirements:
e.g.
Sample code:

 Workbook workbook = new Workbook("f:\\files\\获取图表区域.xlsx");
        WorksheetCollection worksheetCollection = workbook.getWorksheets();
        Worksheet workSheet = worksheetCollection.get(0);
        Chart chart = workSheet.getCharts().get(0);
        System.out.println(chart.getChartDataRange());

Hope, this helps a bit.

3

Thanks, in fact the version I am using is aspose-cells-java-20.12, and the getChartDataRange method is added in the 21.1. Actually I want to copy a sheet of the existing chart to another sheet, and won’t change the chart’s data source, I use the setReferToDestinationSheet method and set it to false, but not work.

 Workbook workbook = new Workbook("f:\\files\\获取图表区域.xlsx");
 WorksheetCollection worksheetCollection = workbook.getWorksheets();
 Worksheet workSheet = worksheetCollection.get(0);
 int index = worksheetCollection.add();
 Worksheet newSheet = worksheetCollection.get(index);
 CopyOptions copyOptions = new CopyOptions();
 copyOptions.setReferToDestinationSheet(false);
 newSheet.copy(worksheet, copyOptions);
 workbook.save("获取图表区域2.xlsx");

获取图表区域2.zip (50.3 KB)

4

@xuerui,

Since the chart’s data source existed in the same sheet, so when you copy the sheet, it will also refer to the same (copied) sheet for the chart in the destination (new) sheet. If you do not want to upgrade to latest version of the APIs to use chart.getChartDataRange method and want to use your existing version, you may try the following sample code for your needs:
e.g.
Sample code:

 Workbook workbook = new Workbook("f:\\files\\获取图表区域.xlsx");
        WorksheetCollection worksheetCollection = workbook.getWorksheets();
        Worksheet workSheet = worksheetCollection.get(0);
        Chart chart = workSheet.getCharts().get(0);

        System.out.println(chart.getNSeries().get(0).getValues());
        System.out.println(chart.getNSeries().getCategoryData());

Hope, this helps a bit.

5

Thanks a lot, it do work.

6

@xuerui,

Good to know that your issue is sorted out by the suggested code segment. Feel free to write us back if you have further comments or questions.