How to: Create and Modify a Chart
- 9 minutes to read
This topic demonstrates how to create a basic chart and change its settings.
To improve the control’s performance by avoiding unnecessary render operations after each modification, enclose the chart modifications in the Workbook.BeginUpdate() - Workbook.EndUpdate() method pair.
Select the action you wish to perform.
- Add and Position a Chart
- Add or Remove Data Series
- Change Chart Data References
- Change the Order of Data Series
- Change the Chart Type
- Change the Chart Type of a Series (Combination Chart)
Add and Position a Chart
All charts embedded in a worksheet are stored in the chart collection accessible using the Worksheet.Charts property. To create a chart, add it to the chart collection by utilizing the ChartCollection.Add method and pass the following parameters: the required chart type (ChartType) and the CellRange object containing data for the chart (this parameter is optional and can be omitted, so you can manually add data series to your chart). Refer to the Create and Modify a Chart Programmatically document for information on how to organize data in the source range to create a chart.
To position the created chart in a worksheet, use one of the following approaches.
- Anchor the chart to cells by specifying the chart’s FloatingObject.TopLeftCell and FloatingObject.BottomRightCell properties. The chart will move and resize with the underlying cells (Placement.MoveAndSize).
- Set the FloatingObject.Top and FloatingObject.Left offsets in the worksheet for the chart’s top left corner. The chart will be a free floating shape (Placement.FreeFloating).
Worksheet worksheet = workbook.Worksheets["chartTask1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a pie chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.Pie3D, worksheet["B2:C7"]);
chart.TopLeftCell = worksheet.Cells["E2"];
chart.BottomRightCell = worksheet.Cells["K15"];
// Set the chart style.
chart.Style = ChartStyle.ColorGradient;
You can also place a chart on a separate worksheet by creating a chart sheet (see How to: Create a Chart Sheet for details).
Add or Remove Data Series
If you did not specify the range containing chart data in the ChartCollection.Add method, you can define it later by using one of the following approaches.
- Pass the required data range to the ChartObject.SelectData method. The method’s direction parameter allows you to specify the data direction: whether the series values are arranged in columns (ChartDataDirection.Column) or rows (ChartDataDirection.Row).
- Utilize the SeriesCollection.Add method to plot a data series on your chart. Using this method, you can fully control the number of series on your chart and explicitly specify data that should be plotted along the category (X) and value (Y) axes. Moreover, this method is extremely useful if you wish to plot data contained in noncontiguous ranges. For example, you can plot a data series with arguments from Column B and values from the Column D.
Worksheet worksheet = workbook.Worksheets["chartTask3"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.ColumnClustered);
chart.TopLeftCell = worksheet.Cells["H2"];
chart.BottomRightCell = worksheet.Cells["N14"];
// Add chart series using worksheet ranges as the data sources.
chart.Series.Add(worksheet["D2"], worksheet["B3:B6"], worksheet["D3:D6"]);
chart.Series.Add(worksheet["F2"], worksheet["B3:B6"], worksheet["F3:F6"]);
To remove an individual series from the chart, use the SeriesCollection.Remove or SeriesCollection.RemoveAt method.
Worksheet worksheet = workbook.Worksheets["chartTask3"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.ColumnClustered, worksheet["B2:E6"]);
chart.TopLeftCell = worksheet.Cells["H2"];
chart.BottomRightCell = worksheet.Cells["N14"];
// Remove the second series from the chart.
chart.Series.RemoveAt(1);
Change Chart Data References
After you create a chart, you can change the cell range from which data for the series arguments and values is retrieved. To do this, perform the steps below.
- Use the ChartData.FromRange method to create the ChartData object containing new data for the series.
- Assign the created object to the Series.Arguments or Series.Values property.
Worksheet worksheet = workbook.Worksheets["chartTask3"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.ColumnClustered);
chart.TopLeftCell = worksheet.Cells["H2"];
chart.BottomRightCell = worksheet.Cells["N14"];
// Add series using a worksheet range as the data source.
chart.Series.Add(worksheet["D2"], worksheet["B3:B6"], worksheet["D3:D6"]);
chart.Series.Add(worksheet["F2"], worksheet["B3:B6"], worksheet["F3:F6"]);
// Change the data range for the series values.
chart.Series[1].Values = ChartData.FromRange(worksheet["E3:E6"]);
// Specify the cell that is the source for the series name.
chart.Series[1].SeriesName.SetReference(worksheet["E2"]);
Change the Order of Data Series
By default, if your chart has multiple data series, they are plotted on a chart in the order they appear on a worksheet (from left to right if data is arranged in columns, or from top to bottom if data is arranged in rows). However, you can change this default order. Utilize the Series.BringForward or Series.SendBackward method to move the specified series up or down in the plotting order, one position at a time. To place the series before or behind all other series on a chart, use the Series.BringToFront or Series.SendToBack method.
Worksheet worksheet = workbook.Worksheets["chartTask3"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.ColumnClustered, worksheet["B2:D6"]);
chart.TopLeftCell = worksheet.Cells["H2"];
chart.BottomRightCell = worksheet.Cells["N14"];
// Change the series order.
chart.Series[1].BringForward();
Change the Chart Type
After you created a chart, you may find that the chart you selected is not suitable and another chart type may better represent your data. In this case, there is no need to recreate the chart. Change the type of the entire chart using the ChartObject.ChangeType method. This method substitutes the existing chart with the chart you specified. If the operation cannot be completed, an exception is fired.
The following example demonstrates how to create a chart of the ChartType.PieExploded type. After that, the code attempts to change the chart type to ChartType.LineMarker. If you try to change the chart type to ChartType.StockHighLowClose (this line is commented), an exception will be thrown, because the data range is insufficient for this chart type, and the chart will be changed to ChartType.ColumnClustered.
Worksheet worksheet = workbook.Worksheets["chartTask1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// If a new chart type cannot be created with existing data, an exception is thrown.
//ChartType type1 = ChartType.StockHighLowClose;
ChartType type1 = ChartType.LineMarker;
ChartType type2 = ChartType.ColumnClustered;
// Create a chart and specify its location
Chart chart = worksheet.Charts.Add(ChartType.PieExploded, worksheet["B2:C7"]);
chart.TopLeftCell = worksheet.Cells["E2"];
chart.BottomRightCell = worksheet.Cells["K15"];
// Hide the legend.
chart.Legend.Visible = false;
// Change the chart type.
try
{
chart.ChangeType(type1);
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Incompatible chart type");
chart.ChangeType(type2);
}
Change the Chart Type of a Series (Combination Chart)
Besides changing a type of the entire chart you can also select a different chart type for an individual data series. This will automatically turn the chart into a combination chart. A combination chart is a complex chart that consists of two or more ChartView objects that consolidate series of the same chart type. To change the type of a single data series, call the Series.ChangeType method. If the series type you specified differs from the types of the existing ChartView objects (ChartView.ViewType), a new ChartView containing your series will be created, otherwise, the series will be added to the existing ChartView of the same type.
Important
Not all chart types can be combined (for example, it is impossible to combine the 2-D and 3-D chart types). If you select a type that does not logically connect with the existing chart type, the resulting chart will contain only one ChartView of the most recently added type.
Compatible chart types are listed below.
- ChartType.Area
- ChartType.AreaFullStacked
- ChartType.AreaStacked
- ChartType.BarClustered
- ChartType.BarFullStacked
- ChartType.BarStacked
- ChartType.ColumnClustered
- ChartType.ColumnFullStacked
- ChartType.ColumnStacked
- ChartType.Line
- ChartType.LineFullStacked
- ChartType.LineFullStackedMarker
- ChartType.LineMarker
- ChartType.LineStacked
- ChartType.LineStackedMarker
Worksheet worksheet = workbook.Worksheets["chartTask5"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.LineMarker, worksheet["B2:D8"]);
chart.TopLeftCell = worksheet.Cells["F2"];
chart.BottomRightCell = worksheet.Cells["L15"];
// Change the type of the second series.
chart.Series[1].ChangeType(ChartType.ColumnClustered);
// Use the secondary axis.
chart.Series[1].AxisGroup = AxisGroup.Secondary;
// Specify the position of the legend.
chart.Legend.Position = LegendPosition.Top;