C# Area Chart SpreadsheetGear API Sample

Excel-Compatible Samples for .NET

Description

Creating Excel-compatible Area Charts is trivial to do with SpreadsheetGear. At its most basic level you call IShapes.AddChart(...) and then set the returned chart object to use a ChartType.Area chart type (or one of the other area-based chart types).

The below sample demonstrates this process and performs a few other tasks to properly setup the chart:

Opens an Excel workbook (ChartData.xlsx) pre-populated with chart source data located in the Defined Name "TotalByQuarter" which points to the range L3:L6. This will be used to create a single series in the chart.
Charts are positioned on the worksheet relative to the top-left edge of the worksheet (in Points); however, you might want to position the column chart based on row / column indexes. SpreadsheetGear can take care of this conversion by using the IWorksheetWindowInfo.ColumnToPoints(double columnIndex) and RowToPoints(double rowIndex) methods.
Adds the chart at the specified worksheet location by calling IShapes.AddChart(double top, double left, double width, double height) and setting the source data for the chart by calling IChart.SetSourceData(IRange source, RowCol rowCol).
Sets the chart's type (IChart.ChartType) to ChartType.Area. Other area-based chart types are available, such as ChartType.AreaStacked, ChartType.AreaStacked100 and others.
The single series in this area chart is accessed via IChart.SeriesCollection[...] to:
- Set the category axis (X-axis) labels via ISeries.XValues which are sourced from another Defined Name "Quarters" which points to the range G3:G6 and so will use the axis labels "Q1", "Q2", "Q3" and "Q4".
- Changes the fill color of the area chart to use a theme color (ColorSchemeIndex.Accent4) by setting the ISeries.Format.Fill.ForeColor.ThemeColor property.
Hides the legend (IChart.HasLegend = false) which is displayed by default.
Additional chart options are configured, including:
- Adding a chart title by setting IChart.HasTitle to true, then setting the title text and other chart title options under the IChart.ChartTitle property.
- Accessing the "value" axis (Y-axis) via the IChart.Axes[...] collection to hide the major gridlines.

Sample C# Source Code

// Open workbook with some data for the chart and get a reference to the active 
// sheet and its cells.
SpreadsheetGear.IWorkbook workbook = 
    SpreadsheetGear.Factory.GetWorkbook("ChartData.xlsx");
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;

// Get a reference to the worksheet window info and shapes collection.
SpreadsheetGear.IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
SpreadsheetGear.Shapes.IShapes shapes = worksheet.Shapes;

// Add a chart to the worksheet's shape collection.
// NOTE: Calculate the coordinates of the chart by converting row and column
//       coordinates to points.  Use fractional row and column values to get 
//       coordinates anywhere in between row and column boundaries.
double left = windowInfo.ColumnToPoints(0.15);
double top = windowInfo.RowToPoints(0.5);
double right = windowInfo.ColumnToPoints(5.85);
double bottom = windowInfo.RowToPoints(13.5);
SpreadsheetGear.Charts.IChart chart =
    shapes.AddChart(left, top, right - left, bottom - top).Chart;

// Get the source data range from an existing defined name.
SpreadsheetGear.IRange source = workbook.Names["TotalByQuarter"].RefersToRange;

// Set the chart's source data range, plotting series in columns.
chart.SetSourceData(source, SpreadsheetGear.Charts.RowCol.Columns);

// Set the chart type to an area chart.
chart.ChartType = SpreadsheetGear.Charts.ChartType.Area;

// Get a reference to the single total series.
SpreadsheetGear.Charts.ISeries seriesTotal = chart.SeriesCollection[0];

// Add category axis labels by using an existing defined name.
seriesTotal.XValues = "=Quarters";

// Change the theme color of the total series.
seriesTotal.Format.Fill.ForeColor.ThemeColor =
    SpreadsheetGear.Themes.ColorSchemeIndex.Accent4;

// Hide the legend.
chart.HasLegend = false;

// Hide the major gridlines on the value axis.
chart.Axes[SpreadsheetGear.Charts.AxisType.Value].HasMajorGridlines = false;

// Add a chart title and change the font size.
chart.HasTitle = true;
chart.ChartTitle.Text = "Total Sales by Quarter";
chart.ChartTitle.Font.Size = 12;