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 and setting 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.

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);