Click or drag to resize

Adding Chart Shapes and Chart Worksheets with ExpertXls

The ExpertXls Excel Library for .NET offers support both for adding chart shapes to a worksheet and for adding chart worksheets to a worbook. The collection of charts in a worksheet is represented by the Charts property and the collection of chart worksheets in a workbook is represented by the ChartWorksheets property.

The library is able to generate the chart series from the data source range or the chart series can be manually defined.

Code Samples

The code sample below shows how to add a chart with automatically defined series:

Copy
ExcelRange dataSourceRange = worksheet["B7:G12"];
bool seriesDataByRows = rbSeriesByRows.Checked;

ExcelChart chart = worksheet.Charts.AddChart(chartType, dataSourceRange, seriesDataByRows, 2, 16, 8, 33);

// set chart title
chart.Title.Text = "Product Units Sold per Quarter - Auto Generated Series";
chart.Title.Font.Size = 12;
chart.Title.Font.Color = Color.DarkBlue;

// set chart area style
if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
{
    chart.ChartArea.Interior.FillType = ExcelShapeFillType.SolidFill;
    chart.ChartArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);

    chart.PlotArea.Interior.FillType = ExcelShapeFillType.SolidFill;
    chart.PlotArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
}
else
{
    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;
}

// set value axis title
chart.ValueAxis.Title.Text = "Units sold";
chart.ValueAxis.Title.Font.Size = 10;
chart.ValueAxis.Title.Font.Bold = true;

// set value axis text style
chart.ValueAxis.Font.Size = 8;
chart.ValueAxis.Font.Bold = false;
chart.ValueAxis.Font.Italic = true;
chart.ValueAxis.ShowVerticalTitleText();

// set category axis title
chart.CategoryAxis.Title.Text = "Analyzed products";
chart.CategoryAxis.Title.Font.Size = 10;
chart.CategoryAxis.Title.Font.Bold = true;

// set category axis text style
chart.CategoryAxis.Font.Size = 8;
chart.CategoryAxis.Font.Bold = false;
chart.CategoryAxis.Font.Italic = true;

// set chart legend style
chart.Legend.Interior.FillType = ExcelShapeFillType.SolidFill;
chart.Legend.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);

chart.Legend.Font.Size = 8;
chart.Legend.Font.Bold = true;

The code sample below shows how to add a chart with manually defined series:

Copy
ExcelChart customSeriesChart = worksheet.Charts.AddChart(chartType, 2, 36, 8, 59);

// set chart data table
customSeriesChart.ShowDataTable = true;
customSeriesChart.DataTable.ShowLegendKey = true;

// set chart title
customSeriesChart.Title.Text = "Product Units Sold per Quarter - Custom Series";
customSeriesChart.Title.Font.Size = 12;
customSeriesChart.Title.Font.Color = Color.DarkBlue;

// create the category names range
ExcelRange categoryNamesRange = worksheet["C7:E7"];

// Add chart series

// add first series for the first quarter sales
ExcelChartSeries firstQuarterSeries = customSeriesChart.Series.AddSeries("First Quarter Sales");
firstQuarterSeries.ChartType = chartType;
firstQuarterSeries.CategoryNamesRange = worksheet["C7:E7"];
firstQuarterSeries.ValuesRange = worksheet["C8:E8"];

firstQuarterSeries.DataPoints.All.Label.ContainsValue = true;
firstQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8;
firstQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = true;


//add second series for second quarter sales
ExcelChartSeries secondQuarterSeries = customSeriesChart.Series.AddSeries("Second Quarter Sales");
secondQuarterSeries.ChartType = chartType;
secondQuarterSeries.CategoryNamesRange = categoryNamesRange;
secondQuarterSeries.ValuesRange = worksheet["C9:E9"];

secondQuarterSeries.DataPoints.All.Label.ContainsValue = true;
secondQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8;
secondQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = true;

// add third series for fourth quarter sales
ExcelChartSeries fourthQuarterSeries = customSeriesChart.Series.AddSeries("Fourth Quarter Sales");
fourthQuarterSeries.ChartType = chartType;
fourthQuarterSeries.CategoryNamesRange = categoryNamesRange;
fourthQuarterSeries.ValuesRange = worksheet["C11:E11"];

fourthQuarterSeries.DataPoints.All.Label.ContainsValue = true;
fourthQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8;
fourthQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = true;

// set chart area style
if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
{
    customSeriesChart.ChartArea.Interior.FillType = ExcelShapeFillType.SolidFill;
    customSeriesChart.ChartArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);

    customSeriesChart.PlotArea.Interior.FillType = ExcelShapeFillType.SolidFill;
    customSeriesChart.PlotArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
}
else
{
    customSeriesChart.ChartArea.Interior.FillType = ExcelShapeFillType.GradientFill;
    customSeriesChart.ChartArea.Interior.GradientFillOptions.GradientColorsMode = ExcelGradientColors.Preset;
    customSeriesChart.ChartArea.Interior.GradientFillOptions.PresetGradientType = ExcelShapeFillPresetGradientType.Wheat;

    customSeriesChart.PlotArea.Interior.FillType = ExcelShapeFillType.TextureFill;
    customSeriesChart.PlotArea.Interior.TextureFillOptions.TextureType = ExcelShapeFillTextureType.Stationery;
}

// set value axis title
customSeriesChart.ValueAxis.Title.Text = "Units sold";
customSeriesChart.ValueAxis.Title.Font.Size = 10;
customSeriesChart.ValueAxis.Title.Font.Bold = true;

// set value axis text style
customSeriesChart.ValueAxis.Font.Size = 8;
customSeriesChart.ValueAxis.Font.Bold = false;
customSeriesChart.ValueAxis.Font.Italic = true;
customSeriesChart.ValueAxis.ShowVerticalTitleText();

// set category axis text style
customSeriesChart.CategoryAxis.Font.Size = 8;
customSeriesChart.CategoryAxis.Font.Bold = false;
customSeriesChart.CategoryAxis.Font.Italic = true;

// set chart legend style
customSeriesChart.Legend.Interior.FillType = ExcelShapeFillType.SolidFill;
customSeriesChart.Legend.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);

customSeriesChart.Legend.Font.Size = 8;
customSeriesChart.Legend.Font.Bold = true;