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.

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
See also
Next steps
Published: December 13, 2018 | Modified: May 15, 2023 | Author: Damir Stipinovic