How to Create Various Types of Charts in Excel Spreadsheet Using C# & VB.NET

[sheraz]

This technical tip shows how developers can create a variety of charts inside their spreadsheets using Aspose.Cells. Aspose.Cells provides many charting objects that can be used to create charts in a flexible manner. The primary aim of this topic is to make developers familiar with the charting objects of Aspose.Cells so that they may create any kind of chart easily. To create a chart using Aspose.Cells, please follow the steps below:

• Add some data to worksheet cells by using the PutValue method of a Cell object. This data will be used as a data source for the chart.
• Add a chart to the worksheet by calling the Add method of Charts collection, which is encapsulated in the Worksheet object. You will also specify the type of chart to add in the worksheet using the ChartType enumeration. For example, in the example given below, we have selected ChartType.Pyramid value as a chart type.
• Access the newly added Chart object from the Charts collection by passing the chart index.
• Use any of the Charting Objects (explained in the above section) encapsulated in the Chart object to manage your chart. For example, in the example given below, we have used SeriesCollection charting object to specify the data source for the chart.

Using the above general steps, developers can create any type of chart in their spreadsheets. Developers can use different charting objects to create charts according to their custom requirements.

Example1: Code snippet for Adding pyramid chart into the worksheet

[C# Code]

//Instantiating a Workbook objectWorkbook workbook = new Workbook();//Adding a new worksheet to the Excel objectint sheetIndex = workbook.Worksheets.Add();//Obtaining the reference of the newly added worksheet by passing its sheet indexWorksheet worksheet = workbook.Worksheets[sheetIndex];//Adding a sample value to "A1" cellworksheet.Cells["A1"].PutValue(50);//Adding a sample value to "A2" cellworksheet.Cells["A2"].PutValue(100);//Adding a sample value to "A3" cellworksheet.Cells["A3"].PutValue(150);//Adding a sample value to "B1" cellworksheet.Cells["B1"].PutValue(4);//Adding a sample value to "B2" cellworksheet.Cells["B2"].PutValue(20);//Adding a sample value to "B3" cellworksheet.Cells["B3"].PutValue(50);//Adding a chart to the worksheetint chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Pyramid, 5, 0, 15, 5);//Accessing the instance of the newly added chartAspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];//Adding SeriesCollection (chart data source) to the chart ranging from "A1" cell to "B3"chart.NSeries.Add("A1:B3", true);//Saving the Excel fileworkbook.Save("C:\\book1.xls");

[VB.NET Code]

'Instantiating a Workbook objectDim workbook As Workbook = New Workbook()'Adding a new worksheet to the Excel objectDim sheetIndex As Integer = workbook.Worksheets.Add()'Obtaining the reference of the newly added worksheet by passing its sheet indexDim worksheet As Worksheet = workbook.Worksheets(sheetIndex)'Adding a sample value to "A1" cellworksheet.Cells("A1").PutValue(50)'Adding a sample value to "A2" cellworksheet.Cells("A2").PutValue(100)'Adding a sample value to "A3" cellworksheet.Cells("A3").PutValue(150)'Adding a sample value to "B1" cellworksheet.Cells("B1").PutValue(4)'Adding a sample value to "B2" cellworksheet.Cells("B2").PutValue(20)'Adding a sample value to "B3" cellworksheet.Cells("B3").PutValue(50)'Adding a chart to the worksheetDim chartIndex As Integer = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Pyramid, 5, 0, 15, 5)'Accessing the instance of the newly added chartDim chart As Aspose.Cells.Charts.Chart = worksheet.Charts(chartIndex)'Adding SeriesCollection (chart data source) to the chart ranging from "A1" cell to "B3"chart.NSeries.Add("A1:B3", True)'Saving the Excel fileworkbook.Save("C:\book1.xls")

Example2: Code snippet for Adding line chart into the worksheet

[C# Code]

//Instantiating a Workbook objectWorkbook workbook = new Workbook();//Adding a new worksheet to the Excel objectint sheetIndex = workbook.Worksheets.Add();//Obtaining the reference of the newly added worksheet by passing its sheet indexWorksheet worksheet = workbook.Worksheets[sheetIndex];//Adding a sample value to "A1" cellworksheet.Cells["A1"].PutValue(50);//Adding a sample value to "A2" cellworksheet.Cells["A2"].PutValue(100);//Adding a sample value to "A3" cellworksheet.Cells["A3"].PutValue(150);//Adding a sample value to "B1" cellworksheet.Cells["B1"].PutValue(4);//Adding a sample value to "B2" cellworksheet.Cells["B2"].PutValue(20);//Adding a sample value to "B3" cellworksheet.Cells["B3"].PutValue(50);//Adding a chart to the worksheetint chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 5, 0, 15, 5);//Accessing the instance of the newly added chartAspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];//Adding SeriesCollection (chart data source) to the chart ranging from "A1" cell to "B3"chart.NSeries.Add("A1:B3", true);//Saving the Excel fileworkbook.Save("C:\\book1.xls");

[VB.NET Code]

'Instantiating a Workbook objectDim workbook As Workbook = New Workbook()'Adding a new worksheet to the Excel objectDim sheetIndex As Integer = workbook.Worksheets.Add()'Obtaining the reference of the newly added worksheet by passing its sheet indexDim worksheet As Worksheet = workbook.Worksheets(sheetIndex)'Adding a sample value to "A1" cellworksheet.Cells("A1").PutValue(50)'Adding a sample value to "A2" cellworksheet.Cells("A2").PutValue(100)'Adding a sample value to "A3" cellworksheet.Cells("A3").PutValue(150)'Adding a sample value to "B1" cellworksheet.Cells("B1").PutValue(4)'Adding a sample value to "B2" cellworksheet.Cells("B2").PutValue(20)'Adding a sample value to "B3" cellworksheet.Cells("B3").PutValue(50)'Adding a chart to the worksheetDim chartIndex As Integer = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 5, 0, 15, 5)'Accessing the instance of the newly added chartDim chart As Aspose.Cells.Charts.Chart = worksheet.Charts(chartIndex)'Adding SeriesCollection (chart data source) to the chart ranging from "A1" cell to "B3"chart.NSeries.Add("A1:B3", True)'Saving the Excel fileworkbook.Save("C:\book1.xls")

More about Aspose.Cells for .NET

- Homepage of Aspose.Cells for .NET

- Download Aspose.Cells for .NET