Defined Names

In EPPlus you can add defined names to workbooks and worksheets that contain a range, formula or a value. You can then use the name as a reference for that range, formula or value. In EPPlus 8.1 some new functions to make working with names more clearer was introduced.

Example how to add a defined named range to a worksheet

var myDefinedRange = worksheet.Names.Add("MyDefinedRange", worksheet.Cells["A4:A10"]);

Workbook example

var myDefinedFormula = worksheet.Names.Add("MyDefinedFormula", "SUM(10;7)");

A defined name in a workbook can not contain a range.

New methods in EPPlus 8.1:

AddRange(string Name, ExcelRangeBase range)

New overloads for Add.

Add(string Name, string formula)

Add(string Name, object value)

A new static property: ExcelNamedRange.ValidateCellAddressInFormulas.

It's for validating formulas that contain references to cells. If a cell reference does not contain a worksheet, setting this to true will validate the and then change the cell so it reference the worksheet. Example SUM(C2) will be converted into SUM('Sheet 1'!$C$2).

EPPlus 8.1 introduced some new methods for copying, moving, setting and getting the value of a defined name. Using SetValue(object value), SetFormula(string formula) or SetRange(ExcelRangeBase range) methods will clear the previous value that was contained. For example, if you have a formula stored in your name and change it to range, the formula will be deleted.

To get values from a name there are several ways depending on what is stored in the defined name. If the defined name is a range you can treat the defined name object as a range and use the same methods to get data as you would any other range. If the defined name contains a formula you can use the GetFormula() method, Formula propery. If the define name contains anything that is not a formula or a range you can use GetValue() method, Value property or Text property.

Copy will copy the defined name to the specified workbook or worksheet.

Copy Example

var copyiedName = myNamedRange.Copy("CopiedRange", worksheet2);

Move will move the defined name to the specified workbook or worksheet.

Move Example

var MovedName = myNamedRange.Copy(worksheet2);