Introduction
In our previous topics, we have already provided many examples to demonstrate that how can you set a data source for you chart but in this topic, we are going to provide more details about the types of data that can be set for a chart.
Setting Charts Data
There are two types of data to deal with while working on charts using Aspose.Cells as follows:
Chart Data
Chart data is that data that we use as a data source to build our charts. We can add a range of the cells (containing chart data) by calling the Add method of NSeries object.
Example:
[C#]
//Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B4"
chart.NSeries.Add("A1:B4",true);
[VB.NET]
'Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B4"
chart.NSeries.Add("A1:B4",True)
[JAVA]
'Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B4"
NSeries nSeries = chart.getNSeries();
nSeries.add("A1:B4",true);
Category Data
Category data is used for the labeling of chart data and can be added to NSeries by using its CategoryData property.
Example:
[C#]
//Setting the data source for the category data of NSeries
chart.NSeries.CategoryData = "C1:C4";
[VB.NET]
'Setting the data source for the category data of NSeries
chart.NSeries.CategoryData = "C1:C4"
[JAVA]
'Setting the data source for the category data of NSeries
NSeries nSeries = chart.getNSeries();
nSeries.setCategoryData("C1:C4");
Example:
A complete example is given below to demonstrate the use of chart and category data.
[C#]
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Excel object
int sheetIndex=workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet=workbook.Worksheets[sheetIndex];
//Adding a sample value to "A1" cell
worksheet.Cells["A1"].PutValue(50);
//Adding a sample value to "A2" cell
worksheet.Cells["A2"].PutValue(100);
//Adding a sample value to "A3" cell
worksheet.Cells["A3"].PutValue(150);
//Adding a sample value to "A4" cell
worksheet.Cells["A4"].PutValue(200);
//Adding a sample value to "B1" cell
worksheet.Cells["B1"].PutValue(60);
//Adding a sample value to "B2" cell
worksheet.Cells["B2"].PutValue(32);
//Adding a sample value to "B3" cell
worksheet.Cells["B3"].PutValue(50);
//Adding a sample value to "B4" cell
worksheet.Cells["B4"].PutValue(40);
//Adding a sample value to "C1" cell as category data
worksheet.Cells["C1"].PutValue("Q1");
//Adding a sample value to "C2" cell as category data
worksheet.Cells["C2"].PutValue("Q2");
//Adding a sample value to "C3" cell as category data
worksheet.Cells["C3"].PutValue("Y1");
//Adding a sample value to "C4" cell as category data
worksheet.Cells["C4"].PutValue("Y2");
//Adding a chart to the worksheet
int chartIndex=worksheet.Charts.Add(ChartType.Column,5,0,15,5);
//Accessing the instance of the newly added chart
Chart chart=worksheet.Charts[chartIndex];
//Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B4"
chart.NSeries.Add("A1:B4",true);
//Setting the data source for the category data of NSeries
chart.NSeries.CategoryData = "C1:C4";
//Saving the Excel file
workbook.Save(saveFileDialog1.FileName,FileFormatType.Default);
[VB.NET]
'Instantiating a Workbook object
Dim workbook As Workbook= New Workbook()
'Adding a new worksheet to the Excel object
Dim sheetIndex As Integer = workbook.Worksheets.Add()
'Obtaining the reference of the newly added worksheet by passing its sheet index
Dim worksheet As Worksheet = workbook.Worksheets(sheetIndex)
'Adding a sample value to "A1" cell
worksheet.Cells("A1").PutValue(50)
'Adding a sample value to "A2" cell
worksheet.Cells("A2").PutValue(100)
'Adding a sample value to "A3" cell
worksheet.Cells("A3").PutValue(150)
'Adding a sample value to "A4" cell
worksheet.Cells("A4").PutValue(200)
'Adding a sample value to "B1" cell
worksheet.Cells("B1").PutValue(60)
'Adding a sample value to "B2" cell
worksheet.Cells("B2").PutValue(32)
'Adding a sample value to "B3" cell
worksheet.Cells("B3").PutValue(50)
'Adding a sample value to "B4" cell
worksheet.Cells("B4").PutValue(40)
'Adding a sample value to "C1" cell as category data
worksheet.Cells("C1").PutValue("Q1")
'Adding a sample value to "C2" cell as category data
worksheet.Cells("C2").PutValue("Q2")
'Adding a sample value to "C3" cell as category data
worksheet.Cells("C3").PutValue("Y1")
'Adding a sample value to "C4" cell as category data
worksheet.Cells("C4").PutValue("Y2")
'Adding a chart to the worksheet
Dim chartIndex As Integer = worksheet.Charts.Add(ChartType.Column,5,0,15,5)
'Accessing the instance of the newly added chart
Dim chart As Chart = worksheet.Charts(chartIndex)
'Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B4"
chart.NSeries.Add("A1:B4",True)
'Setting the data source for the category data of NSeries
chart.NSeries.CategoryData = "C1:C4"
'Saving the Excel file
workbook.Save(saveFileDialog1.FileName,FileFormatType.Default)
[JAVA]
//Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheets worksheets = workbook.getWorksheets();
//Obtaining the reference of the first worksheet
Worksheet worksheet= worksheets.getSheet(0);
Cells cells = worksheet.getCells();
//Adding a sample value to "A1" cell
cells.getCell("A1").setValue(50);
//Adding a sample value to "A2" cell
cells.getCell("A2").setValue(100);
//Adding a sample value to "A3" cell
cells.getCell("A3").setValue(150);
//Adding a sample value to "A4" cell
cells.getCell("A4").setValue(200);
//Adding a sample value to "B1" cell
cells.getCell("B1").setValue(60);
//Adding a sample value to "B2" cell
cells.getCell("B2").setValue(32);
//Adding a sample value to "B3" cell
cells.getCell("B3").setValue(50);
//Adding a sample value to "B4" cell
cells.getCell("B4").setValue(40);
//Adding a sample value to "C1" cell as category data
cells.getCell("C1").setValue("Q1");
//Adding a sample value to "C2" cell as category data
cells.getCell("C2").setValue("Q2");
//Adding a sample value to "C3" cell as category data
cells.getCell("C3").setValue("Y1");
//Adding a sample value to "C4" cell as category data
cells.getCell("C4").setValue("Y2");
//Adding a chart to the worksheet
Charts charts = worksheet.getCharts();
int chartIndex= charts.add(ChartType.COLUMN_CLUSTERED,5,0,15,5);
//Accessing the instance of the newly added chart
Chart chart= charts.getChart(chartIndex);
//Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B4"
NSeries nSeries = chart.getNSeries();
nSeries.add("A1:B4",true);
//Setting the data source for the category data of NSeries
nSeries.setCategoryData("C1:C4");
workbook.save("book1.xls");
After executing the above example code, a column chart will be added to the worksheet as shown below:
|
Figure: Column chart with chart & category data
|