Click or drag to resize

Adding Chart Shapes and Chart Worksheets with ExpertXls

A chart can be placed in a worksheet, as a chart shape, or can fill a worksheet of its own, a chart worksheet. The charts of a worksheet are in its Charts collection and the chart worksheets of a workbook are in its ChartWorksheets collection.

The series of a chart are generated from a data source range or added one by one. With a data source range, the first row and the first column of the range hold the category and series names, and the seriesByRows parameter tells if each row or each column of the range is a series. ExcelChartType lists the 2D and 3D chart types: column, bar, line, area, pie, doughnut, radar, scatter, stock, cone, cylinder and pyramid charts.

Gradient, texture and preset fills of the chart area and plot area are Excel 2007 or later features; for Excel 97-2003 workbooks use solid fills.

A Chart with Series Generated from a Range

The data source range B7:G12 has the product names in row 7 (C7:G7), the quarter names in column B (B8:B12) and the values in C8:G12. The chart is placed over the cells of columns 2 to 8, rows 16 to 33.

ExcelChart chart = worksheet.Charts.AddChart(ExcelChartType.ColumnClustered, worksheet["B7:G12"], true, 2, 16, 8, 33);
chart.Title.Text = "Product Units Sold per Quarter";
chart.Title.Font.Size = 12;
chart.Title.Font.Color = Color.DarkBlue;

// the axes
chart.ValueAxis.Title.Text = "Units sold";
chart.ValueAxis.ShowVerticalTitleText();
chart.CategoryAxis.Title.Text = "Products";
chart.CategoryAxis.Font.Size = 8;

// the chart area and plot area
if (workbook.Format == ExcelWorkbookFormat.Xlsx_2007)
{
    chart.ChartArea.Interior.FillType = ExcelShapeFillType.GradientFill;
    chart.ChartArea.Interior.GradientFillOptions.GradientColorsMode = ExcelGradientColors.Preset;
    chart.ChartArea.Interior.GradientFillOptions.PresetGradientType = ExcelShapeFillPresetGradientType.Wheat;
    chart.PlotArea.Interior.FillType = ExcelShapeFillType.TextureFill;
    chart.PlotArea.Interior.TextureFillOptions.TextureType = ExcelShapeFillTextureType.Stationery;
}
else
{
    chart.ChartArea.Interior.FillType = ExcelShapeFillType.SolidFill;
    chart.ChartArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);
}

// the legend and the data labels of one series
chart.Legend.Position = ExcelChartLegendPosition.Bottom;
chart.Series["Yearly Total"].DataPoints.All.Label.ContainsValue = true;
A Chart with Series Added One by One
ExcelChart customChart = worksheet.Charts.AddChart(ExcelChartType.LineMarkers, 2, 36, 8, 54);
customChart.Title.Text = "Units Sold of the First Three Products";

ExcelChartSeries firstQuarter = customChart.Series.AddSeries("First Quarter");
firstQuarter.CategoryNamesRange = worksheet["C7:E7"];
firstQuarter.ValuesRange = worksheet["C8:E8"];

ExcelChartSeries fourthQuarter = customChart.Series.AddSeries("Fourth Quarter");
fourthQuarter.CategoryNamesRange = worksheet["C7:E7"];
fourthQuarter.ValuesRange = worksheet["C11:E11"];
fourthQuarter.DataPoints.All.Label.ContainsValue = true;

// a data table with the values below the chart
customChart.ShowDataTable = true;
customChart.DataTable.ShowLegendKey = true;
A Chart Worksheet

A chart worksheet is added after the worksheets of the workbook:

ExcelChartWorksheet chartSheet = workbook.ChartWorksheets.AddChartWorksheet(ExcelChartType.Pie3D,
    worksheet["B7:G8"], true, "Sales Chart");
chartSheet.Title.Text = "First Quarter Sales";
chartSheet.Rotation3D.XRotation = 30;
chartSheet.Legend.Position = ExcelChartLegendPosition.Right;
See Also