All docs
V20.2
20.2
20.1
The page you are viewing does not exist in version 20.1. This link will take you to the root page.
19.2
The page you are viewing does not exist in version 19.2. This link will take you to the root page.
19.1
The page you are viewing does not exist in version 19.1. This link will take you to the root page.
18.2
The page you are viewing does not exist in version 18.2. This link will take you to the root page.
18.1
The page you are viewing does not exist in version 18.1. This link will take you to the root page.
17.2
The page you are viewing does not exist in version 17.2. This link will take you to the root page.
.NET Framework 4.5.2+
.NET Framework 4.5.2+
.NET Standard 2.0+

How to: Create Excel 2016 Charts

  • 7 minutes to read

The Spreadsheet Document API allows you to create, load, edit, print, and export Excel 2016 charts to PDF. Call the Worksheet.Charts.Add method to add a chart to a worksheet. A ChartType enumeration member defines the chart's type.

Box and Whisker

Use the Series.LayoutOptions.BoxAndWhisker property to define options for a box and whisker data series.

Property Description
BoxAndWhiskerSeriesOptions.ShowInnerPoints Specifies whether to show inner data points that lie between the lower and upper whisker lines.
BoxAndWhiskerSeriesOptions.ShowOutlierPoints Specifies whether to show outlier data points that lie either below the lower whisker line or above the upper whisker line.
BoxAndWhiskerSeriesOptions.ShowMeanLine Specifies whether to display a line that connects the means of boxes in the series.
BoxAndWhiskerSeriesOptions.ShowMeanMarkers Specifies whether to show the mean markers.
BoxAndWhiskerSeriesOptions.QuartileCalculationMethod Defines the quartile calculation method.

// Create a box and whisker chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.BoxAndWhisker, worksheet["B2:E17"]);
chart.TopLeftCell = worksheet.Cells["G2"];
chart.BottomRightCell = worksheet.Cells["N17"];

// Set the minimum and maximum values for the value axis.
Axis axis = chart.PrimaryAxes[1];
axis.Scaling.AutoMax = false;
axis.Scaling.Max = 70;
axis.Scaling.AutoMin = false;
axis.Scaling.Min = 40;

// Specify series options.
foreach (Series series in chart.Series)
{
    var options = series.LayoutOptions.BoxAndWhisker;
    options.ShowInnerPoints = true;
    options.ShowMeanLine = false;
    options.ShowOutlierPoints = true;
    options.ShowMeanMarkers = true;
    options.QuartileCalculationMethod = QuartileCalculationMethod.ExclusiveMedian;
}

// Add the chart title.
chart.Title.Visible = true;
chart.Title.SetValue("Academic Performance Distribution");

Waterfall

Use the Series.LayoutOptions.Waterfall property to specify options for a waterfall data series.

Property Description
WaterfallSeriesOptions.ShowConnectorLines Specifies whether to display connector lines between data points.
WaterfallSeriesOptions.SubtotalDataPoints Returns a collection of total points.

// Create a waterfall chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.Waterfall, worksheet["B2:C7"]);
chart.TopLeftCell = worksheet.Cells["E2"];
chart.BottomRightCell = worksheet.Cells["L17"];

// Hide the major gridlines for the value axis.
chart.PrimaryAxes[1].MajorGridlines.Visible = false;

// Specify series options.
var options = chart.Series[0].LayoutOptions.Waterfall;
// Display connector lines.
options.ShowConnectorLines = true;
// Set the third data point as the total.
options.SubtotalDataPoints.Add(2);
// Set the last data point as the total.
options.SubtotalDataPoints.Add(5);

// Add the chart title.
chart.Title.Visible = true;
chart.Title.SetValue("Income Statement");

Histogram

Use the Series.LayoutOptions.Histogram property to specify options for a histogram data series.

Property Description
HistogramSeriesOptions.BinType Specifies how to calculate bins for a histogram.
HistogramSeriesOptions.BinCount Defines the number of histogram bins.
HistogramSeriesOptions.BinWidth Defines the bin size.
HistogramSeriesOptions.OverflowType,
HistogramSeriesOptions.OverflowValue
Allow you to create a bin for all values that are above a specific value.
HistogramSeriesOptions.UnderflowType,
HistogramSeriesOptions.UnderflowValue
Allow you to create a bin for all values that are below or equal to a specific value.
HistogramSeriesOptions.IntervalClosedSide Specifies the bin closed side.

// Create a histogram chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.Histogram, worksheet["B2:B21"]);
chart.TopLeftCell = worksheet.Cells["D2"];
chart.BottomRightCell = worksheet.Cells["K18"];

// Specify histogram series options.
var options = chart.Series[0].LayoutOptions.Histogram;
options.BinType = HistogramBinType.BinWidth;
options.BinWidth = 24;
// Specify the gap width.
chart.Series[0].GapWidth = 10;

// Add the chart title.
chart.Title.Visible = true;
chart.Title.SetValue("Exam Score Distribution");

Pareto

Use the Series.LayoutOptions.Histogram property to specify bin options for a Pareto data series.

See histogram series options.

// Create a Pareto chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.Pareto, worksheet["B2:C7"]);
chart.TopLeftCell = worksheet.Cells["E2"];
chart.BottomRightCell = worksheet.Cells["L17"];

// Specify series options.
var options = chart.Series[0].LayoutOptions.Histogram;
options.BinType = HistogramBinType.ByCategory;
// Specify the gap width.
chart.Series[0].GapWidth = 15;

// Add the chart title.
chart.Title.Visible = true;
chart.Title.SetValue("Key Causes of Late Projects");

Funnel

// Create a funnel chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.Funnel, worksheet["B2:C6"]);
chart.TopLeftCell = worksheet.Cells["E2"];
chart.BottomRightCell = worksheet.Cells["L18"];

// Add the chart title.
chart.Title.Visible = true;
chart.Title.SetValue("Website Visitor Trend");

Sunburst

// Create a sunburst chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.Sunburst, worksheet["B2:E18"]);
chart.TopLeftCell = worksheet.Cells["G2"];
chart.BottomRightCell = worksheet.Cells["N19"];

// Hide the chart title.
chart.Title.Visible = false;

Treemap

Use the Series.LayoutOptions.Treemap property to specify options for a treemap data series.

Property Description
TreemapSeriesOptions.ParentLabelLayout Defines the layout of parent labels.

// Create a treemap chart and specify its location.
Chart chart = worksheet.Charts.Add(ChartType.Treemap, worksheet["B2:E14"]);
chart.TopLeftCell = worksheet.Cells["G2"];
chart.BottomRightCell = worksheet.Cells["N17"];

// Specify series options.
var options = chart.Series[0].LayoutOptions.Treemap;
options.ParentLabelLayout = TreemapParentLabelLayout.Banner;

// Add the chart title.
chart.Title.Visible = true;
chart.Title.SetValue("Daily Food Sales");

Limitations

The following limitations exist when you print or export Excel 2016 charts to PDF.

Chart Type Limitations
All charts 1. The Spreadsheet Document API can render only a linear gradient fill with two color stops for a chart area. Other gradient types are replaced with a solid fill.
2. A pattern fill applied to a chart area or data series is replaced with a solid fill.
Box and whisker 1. Data labels are not displayed.
2. The Series.GapWidth property value is ignored.
Waterfall Charts with empty data values are displayed incorrectly.
Sunburst You cannot print or export these charts to PDF.
Treemap You cannot print or export these charts to PDF.
See Also