Defined Names and Named Ranges in Excel
The defined names in Excel files are descriptive or human-readable names that reference a single cell, range of cells, constant values, or formulas. You can use these names to define cell references without using the columns and rows, thus making it easier to understand the purpose of that cell or cell range.
With GemBox.Spreadsheet you can access defined names via ExcelWorkbook.DefinedNames and ExcelWorksheet.NamedRanges properties. The difference is that NamedRanges is a collection of NamedRange objects, filtered DefinedName objects that reference cells in a global scope (ExcelWorkbook) or a local scope (ExcelWorksheet).
The following example shows how to create and retrieve defined names or named ranges in an Excel file from C# and VB.NET.
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 = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Names");
// Create a defined name for a constant value with a global scope.
workbook.DefinedNames.AddDefinedName("Tax", "0.2", -1);
// Retrieve defined name.
DefinedName taxConstant = workbook.DefinedNames["Tax"];
// Use defined name with formula.
worksheet.Cells["A1"].Value = taxConstant.Name;
worksheet.Cells["B1"].Formula = "=Tax";
worksheet.Cells["B1"].Style.NumberFormat = "0%";
// Create a named range for cell "A3" with a local scope.
worksheet.Cells["A2"].Value = "Price";
worksheet.Cells["A3"].Value = 240;
worksheet.Cells["A4"].Value = 180;
worksheet.Cells["A5"].Value = 210;
worksheet.NamedRanges.Add("Prices", worksheet.Cells.GetSubrange("A3"));
// Retrieve named range.
NamedRange priceRange = worksheet.NamedRanges["Prices"];
// Modify named range's cell reference to cells "A3:A5".
priceRange.Range = worksheet.Cells.GetSubrange("A3:A5");
// Use named range with formulas.
worksheet.Cells["B2"].Value = "Total";
worksheet.Cells["B3"].Formula = "=Prices * (Tax + 1)";
worksheet.Cells["B4"].Formula = "=Prices * (Tax + 1)";
worksheet.Cells["B5"].Formula = "=Prices * (Tax + 1)";
workbook.Save("Defined Names.%OutputFileType%");
}
}
Imports GemBox.Spreadsheet
Module Program
Sub Main()
' If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook As New ExcelFile()
Dim worksheet = workbook.Worksheets.Add("Names")
' Create a defined name for a constant value with a global scope.
workbook.DefinedNames.AddDefinedName("Tax", "0.2", -1)
' Retrieve defined name.
Dim taxConstant As DefinedName = workbook.DefinedNames("Tax")
' Use defined name with formula.
worksheet.Cells("A1").Value = taxConstant.Name
worksheet.Cells("B1").Formula = "=Tax"
worksheet.Cells("B1").Style.NumberFormat = "0%"
' Create a named range for cell "A3" with a local scope.
worksheet.Cells("A2").Value = "Price"
worksheet.Cells("A3").Value = 240
worksheet.Cells("A4").Value = 180
worksheet.Cells("A5").Value = 210
worksheet.NamedRanges.Add("Prices", worksheet.Cells.GetSubrange("A3"))
' Retrieve named range.
Dim priceRange As NamedRange = worksheet.NamedRanges("Prices")
' Modify named range's cell reference to cells "A3:A5".
priceRange.Range = worksheet.Cells.GetSubrange("A3:A5")
' Use named range with formulas.
worksheet.Cells("B2").Value = "Total"
worksheet.Cells("B3").Formula = "=Prices * (Tax + 1)"
worksheet.Cells("B4").Formula = "=Prices * (Tax + 1)"
worksheet.Cells("B5").Formula = "=Prices * (Tax + 1)"
workbook.Save("Defined Names.%OutputFileType%")
End Sub
End Module