Excel Chart formatting in C# and VB.NET

The following example shows how you can format an Excel chart in C# and VB.NET using the GemBox.Spreadsheet component.

Excel charts are supported in XLSX, HTML, PDF, XPS, and image formats. In HTML, charts are converted to images.

For more information about charts, see the Charts help page.

Excel chart formatted with GemBox.Spreadsheet
Screenshot of Excel chart formatted with GemBox.Spreadsheet
using System;
using GemBox.Spreadsheet;
using GemBox.Spreadsheet.Charts;
using GemBox.Spreadsheet.Drawing;

class Program
{
    static void Main()
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Chart");

        // Add data which will be used by the Excel chart.
        worksheet.Cells["A1"].Value = "Month";
        worksheet.Cells["A2"].Value = "January";
        worksheet.Cells["A3"].Value = "February";
        worksheet.Cells["A4"].Value = "March";
        worksheet.Cells["A5"].Value = "April";
        worksheet.Cells["A6"].Value = "May";
        worksheet.Cells["A7"].Value = "June";
        worksheet.Cells["A8"].Value = "July";
        worksheet.Cells["A9"].Value = "August";
        worksheet.Cells["A10"].Value = "September";
        worksheet.Cells["A11"].Value = "October";
        worksheet.Cells["A12"].Value = "November";
        worksheet.Cells["A13"].Value = "December";

        // Fill the values
        worksheet.Cells["B1"].Value = "Sales";
        var random = new Random();

        for (int i = 1; i <= 12; i++)
            worksheet.Cells[i, 1].SetValue(random.Next(2000, 5000));

        // Set header row and formatting.
        worksheet.Rows[0].Style.Font.Weight = ExcelFont.BoldWeight;
        worksheet.Columns[0].Width = (int)LengthUnitConverter.Convert(3, LengthUnit.Centimeter, LengthUnit.ZeroCharacterWidth256thPart);
        worksheet.Columns[1].Style.NumberFormat = "\"$\"#,##0";

        // Make entire sheet print on a single page.
        worksheet.PrintOptions.FitWorksheetWidthToPages = 1;
        worksheet.PrintOptions.FitWorksheetHeightToPages = 1;

        // Create Excel chart and select data for it.
        var chart = worksheet.Charts.Add<LineChart>("D2", "P25");
        chart.SelectData(worksheet.Cells.GetSubrangeAbsolute(0, 0, 12, 1), true);

        // Define colors
        var backgroundColor = DrawingColor.FromName(%BackgroundColor%);
        var seriesColor = DrawingColor.FromName(%SeriesColor%);
        var textColor = DrawingColor.FromName(%TextColor%);
        var borderColor = DrawingColor.FromName(DrawingColorName.Black);

        // Format chart
        chart.Fill.SetSolid(backgroundColor);

        var outline = chart.Outline;
        outline.Width = Length.From(2, LengthUnit.Point);
        outline.Fill.SetSolid(borderColor);

        // Format plot area
        chart.PlotArea.Fill.SetSolid(DrawingColor.FromName(DrawingColorName.White));

        outline = chart.PlotArea.Outline;
        outline.Width = Length.From(1.5, LengthUnit.Point);
        outline.Fill.SetSolid(borderColor);

        // Format chart title 
        var textFormat = chart.Title.TextFormat;
        textFormat.Size = Length.From(20, LengthUnit.Point);
        textFormat.Font = "Arial";
        textFormat.Fill.SetSolid(textColor);

        // Format vertical axis
        textFormat = chart.Axes.Vertical.TextFormat;
        textFormat.Fill.SetSolid(textColor);
        textFormat.Italic = true;

        // Format horizontal axis
        textFormat = chart.Axes.Horizontal.TextFormat;
        textFormat.Fill.SetSolid(textColor);
        textFormat.Size = Length.From(12, LengthUnit.Point);
        textFormat.Bold = true;

        // Format vertical major gridlines
        chart.Axes.Vertical.MajorGridlines.Outline.Width = Length.From(0.5, LengthUnit.Point);

        // Format series
        var series = chart.Series[0];
        outline = series.Outline;
        outline.Width = Length.From(3, LengthUnit.Point);
        outline.Fill.SetSolid(seriesColor);

        // Format series markers
        series.Marker.MarkerType = MarkerType.Circle;
        series.Marker.Size = 10;
        series.Marker.Fill.SetSolid(textColor);
        series.Marker.Outline.Fill.SetSolid(seriesColor);

        workbook.Save("Formatting.%OutputFileType%");
    }
}
Imports System
Imports GemBox.Spreadsheet
Imports GemBox.Spreadsheet.Charts
Imports GemBox.Spreadsheet.Drawing

Module Program

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook = New ExcelFile
        Dim worksheet = workbook.Worksheets.Add("Chart")

        ' Add data which is used by the Excel chart.
        worksheet.Cells("A1").Value = "Month"
        worksheet.Cells("A2").Value = "January"
        worksheet.Cells("A3").Value = "February"
        worksheet.Cells("A4").Value = "March"
        worksheet.Cells("A5").Value = "April"
        worksheet.Cells("A6").Value = "May"
        worksheet.Cells("A7").Value = "June"
        worksheet.Cells("A8").Value = "July"
        worksheet.Cells("A9").Value = "August"
        worksheet.Cells("A10").Value = "September"
        worksheet.Cells("A11").Value = "October"
        worksheet.Cells("A12").Value = "November"
        worksheet.Cells("A13").Value = "December"

        ' Fill the values
        worksheet.Cells("B1").Value = "Sales"
        Dim random = New Random()

        For i As Integer = 1 To 12
            worksheet.Cells(i, 1).SetValue(random.Next(2000, 5000))
        Next

        ' Set header row and formatting.
        worksheet.Rows(0).Style.Font.Weight = ExcelFont.BoldWeight
        worksheet.Columns(0).Width = CInt(LengthUnitConverter.Convert(3, LengthUnit.Centimeter, LengthUnit.ZeroCharacterWidth256thPart))
        worksheet.Columns(1).Style.NumberFormat = """$""#,##0"

        ' Make entire sheet print on a single page.
        worksheet.PrintOptions.FitWorksheetWidthToPages = 1
        worksheet.PrintOptions.FitWorksheetHeightToPages = 1

        ' Create Excel chart and select data for it.
        Dim chart = worksheet.Charts.Add(Of LineChart)("D2", "P25")
        chart.SelectData(worksheet.Cells.GetSubrangeAbsolute(0, 0, 12, 1), True)

        ' Define colors
        Dim backgroundColor = DrawingColor.FromName(%BackgroundColor%)
        Dim seriesColor = DrawingColor.FromName(%SeriesColor%)
        Dim textColor = DrawingColor.FromName(%TextColor%)
        Dim borderColor = DrawingColor.FromName(DrawingColorName.Black)

        ' Format chart
        chart.Fill.SetSolid(backgroundColor)

        Dim outline = chart.Outline
        outline.Width = Length.From(2, LengthUnit.Point)
        outline.Fill.SetSolid(borderColor)

        ' Format plot area
        chart.PlotArea.Fill.SetSolid(DrawingColor.FromName(DrawingColorName.White))

        outline = chart.PlotArea.Outline
        outline.Width = Length.From(1.5, LengthUnit.Point)
        outline.Fill.SetSolid(borderColor)

        ' Format chart title 
        Dim textFormat = chart.Title.TextFormat
        textFormat.Size = Length.From(20, LengthUnit.Point)
        textFormat.Font = "Arial"
        textFormat.Fill.SetSolid(textColor)

        ' Format vertical axis
        textFormat = chart.Axes.Vertical.TextFormat
        textFormat.Fill.SetSolid(textColor)
        textFormat.Italic = True

        ' Format horizontal axis
        textFormat = chart.Axes.Horizontal.TextFormat
        textFormat.Fill.SetSolid(textColor)
        textFormat.Size = Length.From(12, LengthUnit.Point)
        textFormat.Bold = True

        ' Format vertical major gridlines
        chart.Axes.Vertical.MajorGridlines.Outline.Width = Length.From(0.5, LengthUnit.Point)

        ' Format series
        Dim series = chart.Series(0)
        outline = series.Outline
        outline.Width = Length.From(3, LengthUnit.Point)
        outline.Fill.SetSolid(seriesColor)

        ' Format series markers
        series.Marker.MarkerType = MarkerType.Circle
        series.Marker.Size = 10
        series.Marker.Fill.SetSolid(textColor)
        series.Marker.Outline.Fill.SetSolid(seriesColor)

        workbook.Save("Formatting.%OutputFileType%")
    End Sub
End Module

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.


Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.