How to get Category data from chart SeriesCollection?

Aspose.Cells Product Family
1

I was trying to set the category data by using below code.

ChartCollection charts = sheet.getCharts();

int chartIndex = charts.add(ChartType.COLUMN,5,5,10,10);

Chart chart = charts.get(chartIndex);

SeriesCollection seriess = chart.getNSeries();
seriess.setCategoryData(“C1:D2”);
int index = seriess.add(“A1:A2”,true);
seriess.get(index).setName(“A3”);
index = seriess.add(“B1:B2”,true);
seriess.get(index).setName(“B3”);

when I try to read this info back everything else comes back as expected except category data.

Here is retrieval code:


ChartCollection charts = sheet.getCharts();

Chart chart = charts.get(0);

SeriesCollection series = chart.getNSeries();

Iterator it = series.iterator();
System.out.println(series.getCategoryData());//this return null

while(it.hasNext()){
Series s = it.next();
System.out.println(s.getName());
System.out.println(s.getValues());
}

Sample Output :
null
A3
=Sheet1!$A$1:$A$2
B3
=Sheet1!$B$1:$B$2

Is it a right way to get the category data? if not how to get that? Please do suggest

Regards,
Azhar

2

Hi,


I think you are doing Ok. Could you provide us your template Excel file so we could evaluate your issue properly.

Thank you.
3


Here is code I am trying. I am generating file and trying to read. attaching the generated xlsx as well.

import java.util.Iterator;

import org.w3c.dom.ranges.RangeException;

import com.aspose.cells.Area;
import com.aspose.cells.Cell;
import com.aspose.cells.Chart;
import com.aspose.cells.ChartArea;
import com.aspose.cells.ChartCollection;
import com.aspose.cells.ChartDataTable;
import com.aspose.cells.ChartType;
import com.aspose.cells.FileFormatType;
import com.aspose.cells.License;
import com.aspose.cells.Range;
import com.aspose.cells.Series;
import com.aspose.cells.SeriesCollection;
import com.aspose.cells.Style;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.aspose.cells.WorksheetCollection;

public class ChartsDemo {

public static void writeChart(){
Workbook workbook = new Workbook();
WorksheetCollection sheets = workbook.getWorksheets();

Worksheet sheet = sheets.get(0);

sheet.getCells().get(“A1”).putValue(10);

sheet.getCells().get(“A2”).putValue(20);
sheet.getCells().get(“A3”).putValue(30);
sheet.getCells().get(“A4”).putValue(40);

sheet.getCells().get(“B1”).putValue(90);

sheet.getCells().get(“B2”).putValue(50);
sheet.getCells().get(“B3”).putValue(40);
sheet.getCells().get(“B4”).putValue(37);



ChartCollection charts = sheet.getCharts();

int chartIndex = charts.add(ChartType.COLUMN,5,5,10,10);

Chart chart = charts.get(chartIndex);

SeriesCollection seriess = chart.getNSeries();
seriess.setCategoryData(“C1:D2”);
int index = seriess.add(“A1:A2”,true);
seriess.get(index).setName(“A3”);
index = seriess.add(“B1:B2”,true);
seriess.get(index).setName(“B3”);


try {
workbook.save(“charts2.xlsx”,FileFormatType.XLSX);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}


public static void readChart(){
try {
Workbook workbook = new Workbook(“charts2.xlsx”);
WorksheetCollection sheets = workbook.getWorksheets();

Worksheet sheet = sheets.get(0);

ChartCollection charts = sheet.getCharts();
for(int i=0;i<charts.getCount();i++){
Chart chart = charts.get(i);
System.out.println(chart.getTitle().getText());
System.out.println(chart.getName());
System.out.println(chart.getValueAxis().getTitle().getText());
System.out.println(chart.getCategoryAxis().getTitle().getText());
System.out.println(chart.getSecondValueAxis().getTitle().getText());
System.out.println(chart.getChartObject().getX());
System.out.println(chart.getChartObject().getY());
System.out.println(chart.getChartObject().getLowerDeltaX());
System.out.println(chart.getChartObject().getLowerDeltaY());
SeriesCollection series = chart.getNSeries();

Iterator it = series.iterator();
System.out.println(series.getCategoryData());

while(it.hasNext()){
Series s = it.next();
System.out.println(s.getName());
System.out.println(s.getValues());
}
}



} catch (Exception e) {
e.printStackTrace();
}

}



public static void main(String arg[]){

License licence = new License();
licence.setLicense(“Aspose.Total.Java.lic”);


writeChart();
readChart();
System.out.println(“done”);


}


}

4

Hi Azhar,

Thanks for your sample code and using Aspose.Cells for Java.

Please set the category data after setting the series, so please change your following code

ChartCollection charts = sheet.getCharts();

int chartIndex = charts.add(ChartType.COLUMN,5,5,10,10);

Chart chart = charts.get(chartIndex);

SeriesCollection seriess = chart.getNSeries();
seriess.setCategoryData(“C1:D2”);
int index = seriess.add(“A1:A2”,true);
seriess.get(index).setName(“A3”);
index = seriess.add(“B1:B2”,true);
seriess.get(index).setName(“B3”);

into this

ChartCollection charts = sheet.getCharts();

int chartIndex = charts.add(ChartType.COLUMN,5,5,10,10);

Chart chart = charts.get(chartIndex);

SeriesCollection seriess = chart.getNSeries();

int index = seriess.add(“A1:A2”,true);
seriess.get(index).setName(“A3”);
index = seriess.add(“B1:B2”,true);
seriess.get(index).setName(“B3”);

seriess.setCategoryData(“C1:D2”);

And it will fix your issue.

5

Strange but worked. Thanks.

Regards,
Azhar

6

Hi Azhar,

Thanks for your feedback and using Aspose.Cells.

Yes, it is little strange, please set it like this for a time being, we will look into it, why category data cannot be set before setting the series.

It is good to know your issue is resolved with the above code change now. Let us know if you encounter any other issue, we will be glad to look into it and help you further.