Excel Chart Components

Chart contains many components, such as legend, title, axes, axes titles, etc.

Following sample shows how to create arbitrary chart with specific grouping and set some properties of chart components (title, axis titles, axis properties, etc.).

Sample also demonstrates how to save just single chart (instead of entire workbook) to supported output formats (PDF, XPS and image formats).

For more information about chart components and export functionality, see chart components section from help documentation.

Screenshot

Chart Components Screenshot

See the full code below, use RUN EXAMPLE to execute.


1using System;
2using GemBox.Spreadsheet;
3using GemBox.Spreadsheet.Charts;
4
5class Sample
6{
7    [STAThread]
8    static void Main(string[] args)
9    {
10        // If using Professional version, put your serial key below.
11        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
12
13        int numberOfEmployees = 4;
14        int numberOfYears = 4;
15
16        var ef = new ExcelFile();
17        var ws = ef.Worksheets.Add("Chart");
18
19        // Create chart and select data for it.
20        var chart = (ColumnChart)ws.Charts.Add(ChartType.Column, "B7", "O27");
21        chart.SelectData(ws.Cells.GetSubrangeAbsolute(0, 0, numberOfEmployees, numberOfYears));
22
23        // Set chart title.
24        chart.Title.Text = "Clustered Column Chart";
25
26        // Set axis titles.
27        chart.Axes.Horizontal.Title.Text = "Years";
28        chart.Axes.Vertical.Title.Text = "Salaries";
29
30        // For all charts (except Pie and Bar) value axis is vertical.
31        var valueAxis = chart.Axes.VerticalValue;
32
33        // Set value axis scaling, units, gridlines and tick marks.
34        valueAxis.Minimum = 0;
35        valueAxis.Maximum = 6000;
36        valueAxis.MajorUnit = 1000;
37        valueAxis.MinorUnit = 500;
38        valueAxis.MajorGridlines.IsVisible = true;
39        valueAxis.MinorGridlines.IsVisible = true;
40        valueAxis.MajorTickMarkType = TickMarkType.Outside;
41        valueAxis.MinorTickMarkType = TickMarkType.Cross;
42
43        // Add data which is used by the chart.
44        var names = new string[] { "John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat" };
45        var random = new Random();
46        for (int i = 0; i < numberOfEmployees; ++i)
47        {
48            ws.Cells[i + 1, 0].Value = names[i % names.Length] + (i < names.Length ? string.Empty : ' ' + (i / names.Length + 1).ToString());
49
50            for (int j = 0; j < numberOfYears; ++j)
51                ws.Cells[i + 1, j + 1].SetValue(random.Next(1000, 5000));
52        }
53
54        // Set header row and formatting.
55        ws.Cells[0, 0].Value = "Name";
56        ws.Cells[0, 0].Style.Font.Weight = ExcelFont.BoldWeight;
57        ws.Columns[0].Width = (int)LengthUnitConverter.Convert(3, LengthUnit.Centimeter, LengthUnit.ZeroCharacterWidth256thPart);
58        for (int i = 0, startYear = DateTime.Now.Year - numberOfYears; i < numberOfYears; ++i)
59        {
60            ws.Cells[0, i + 1].SetValue(startYear + i);
61            ws.Cells[0, i + 1].Style.Font.Weight = ExcelFont.BoldWeight;
62            ws.Cells[0, i + 1].Style.NumberFormat = "General";
63            ws.Columns[i + 1].Style.NumberFormat = "\"$\"#,##0";
64        }
65
66        // Make entire sheet print horizontally centered on a single page with headings and gridlines.
67        var printOptions = ws.PrintOptions;
68        printOptions.HorizontalCentered = printOptions.PrintHeadings = printOptions.PrintGridlines = true;
69        printOptions.FitWorksheetWidthToPages = printOptions.FitWorksheetHeightToPages = 1;
70
71        ef.Save("Chart Components.xlsx");
72    }
73}
1Imports System
2Imports GemBox.Spreadsheet
3Imports GemBox.Spreadsheet.Charts
4
5Module Samples
6
7    Sub Main()
8
9        ' If using Professional version, put your serial key below.
10        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
11
12        Dim numberOfEmployees = 4
13        Dim numberOfYears = 4
14
15        Dim ef = new ExcelFile()
16        Dim ws = ef.Worksheets.Add("Chart")
17
18        ' Create chart and select data for it.
19        Dim chart = DirectCast(ws.Charts.Add(ChartType.Column, "B7", "O27"), ColumnChart)
20        chart.SelectData(ws.Cells.GetSubrangeAbsolute(0, 0, numberOfEmployees, numberOfYears))
21
22        ' Set chart title.
23        chart.Title.Text = "Clustered Column Chart"
24
25        ' Set axis titles.
26        chart.Axes.Horizontal.Title.Text = "Years"
27        chart.Axes.Vertical.Title.Text = "Salaries"
28
29        ' For all charts (except Pie and Bar) value axis is vertical.
30        Dim valueAxis = chart.Axes.VerticalValue
31
32        ' Set value axis scaling, units, gridlines and tick marks.
33        valueAxis.Minimum = 0
34        valueAxis.Maximum = 6000
35        valueAxis.MajorUnit = 1000
36        valueAxis.MinorUnit = 500
37        valueAxis.MajorGridlines.IsVisible = true
38        valueAxis.MinorGridlines.IsVisible = true
39        valueAxis.MajorTickMarkType = TickMarkType.Outside
40        valueAxis.MinorTickMarkType = TickMarkType.Cross
41
42        ' Add data which is used by the chart.
43        Dim names = New String() {"John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat"}
44        Dim random = New Random()
45        For i As Integer = 0 To numberOfEmployees - 1
46            ws.Cells(i + 1, 0).Value = names(i Mod names.Length) & (If(i < names.Length, String.Empty, " "c & (i \ names.Length + 1).ToString()))
47
48            For j As Integer = 0 To numberOfYears - 1
49                ws.Cells(i + 1, j + 1).SetValue(random.Next(1000, 5000))
50            Next
51        Next
52
53        ' Set header row and formatting.
54        ws.Cells(0, 0).Value = "Name"
55        ws.Cells(0, 0).Style.Font.Weight = ExcelFont.BoldWeight
56        ws.Columns(0).Width = CInt(LengthUnitConverter.Convert(3, LengthUnit.Centimeter, LengthUnit.ZeroCharacterWidth256thPart))
57        Dim startYear As Integer = DateTime.Now.Year - numberOfYears
58        For i As Integer = 0 To numberOfYears - 1
59            ws.Cells(0, i + 1).SetValue(startYear + i)
60            ws.Cells(0, i + 1).Style.Font.Weight = ExcelFont.BoldWeight
61            ws.Cells(0, i + 1).Style.NumberFormat = "General"
62            ws.Columns(i + 1).Style.NumberFormat = """$""#,##0"
63        Next
64
65        ' Make entire sheet print horizontally centered on a single page with headings and gridlines.
66        Dim printOptions = ws.PrintOptions
67        printOptions.HorizontalCentered = True
68        printOptions.PrintHeadings = True
69        printOptions.PrintGridlines = True
70        printOptions.FitWorksheetWidthToPages = 1
71        printOptions.FitWorksheetHeightToPages = 1
72
73        ef.Save("Chart Components.xlsx")
74
75    End Sub
76
77End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.