Excel Chart Preservation in C# and VB.NET

Charts that are not supported by GemBox.Spreadsheet (for example, 3D charts) are preserved so they will still be contained in the output XLSX file.

Preserved charts can reflect changes made to data when opened in the MS Excel application.

One way to accomplish this is to use named ranges as a data source for charts. As the following example shows, the graph will reflect changes made to named ranges when opened in the MS Excel application.

This technique is also known as dynamic chart data. For it to work, template files containing charts must also have defined named ranges used by the chart and changed in the code.

For more information about chart preservation, see the Charts Preservation section help page.

Screenshot of Excel chart preserved with GemBox.Spreadsheet

C#
VB.NET

View on GitHub

using System;
using GemBox.Spreadsheet;

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

        var workbook = ExcelFile.Load("%#ChartTemplate.xlsx%");

        int numberOfEmployees = %EmployeesCount%;

        var worksheet = workbook.Worksheets[0];

        // Update named ranges 'Names' and 'Salaries' which are used by preserved chart.
        worksheet.NamedRanges["Names"].Range = worksheet.Cells.GetSubrangeAbsolute(1, 0, numberOfEmployees, 0);
        worksheet.NamedRanges["Salaries"].Range = worksheet.Cells.GetSubrangeAbsolute(1, 1, numberOfEmployees, 1);

        // Add data which is used by preserved chart through named ranges 'Names' and 'Salaries'.
        var random = new Random();
        for (int i = 0; i < numberOfEmployees; i++)
        {
            worksheet.Cells[i + 1, 0].Value = "Employee " + (i + 1).ToString();
            worksheet.Cells[i + 1, 1].SetValue(random.Next(1000, 5000));
        }

        workbook.Save("Preservation.xlsx");
    }
}

Imports System
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        Dim workbook = ExcelFile.Load("%#ChartTemplate.xlsx%")

        Dim numberOfEmployees As Integer = %EmployeesCount%

        Dim worksheet = workbook.Worksheets(0)

        ' Update named ranges 'Names' and 'Salaries' which are used by preserved chart.
        worksheet.NamedRanges("Names").Range = worksheet.Cells.GetSubrangeAbsolute(1, 0, numberOfEmployees, 0)
        worksheet.NamedRanges("Salaries").Range = worksheet.Cells.GetSubrangeAbsolute(1, 1, numberOfEmployees, 1)

        ' Add data which is used by preserved chart through named ranges 'Names' and 'Salaries'.
        Dim random = New Random()
        For i As Integer = 0 To numberOfEmployees - 1

            worksheet.Cells(i + 1, 0).Value = "Employee " & (i + 1).ToString()
            worksheet.Cells(i + 1, 1).SetValue(random.Next(1000, 5000))
        Next

        workbook.Save("Preservation.xlsx")
    End Sub
End Module

Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy

Published: December 13, 2018 | Modified: May 15, 2023 | Author: Damir Stipinovic

Excel Chart Preservation in C# and VB.NET

See also

Next steps