Named Range

Define named range by programming

To define a name range, use the DefineNamedRange method:

var sheet = grid.CurrentWorksheet;

// define named range start at C3, end at F3, it contains 4 columns
sheet.DefineNamedRange("header", new RangePosition("C3:F3"));

Set data into worksheet by specifying the name:

// set header text inside range named 'header'
sheet["header"] = new object[] { "Name", "Age", "Birthday", "Email" };

The text displayed inside 4 columns as below:

Named Range

It’s possible to set data via instance of named range.

// define a named range 
NamedRange namedRange = sheet.DefineNamedRange("items", "B2:E2"); 

// set values into named range 
namedRange.Data = new object[] { 1, 3, 5, 7 };

Use name in formula

To reference a range in formula by name, use the name directly:

var range = sheet.DefineNamedRange("r1", "A1:C2");

range.Data = new object[,] { 
  {1, 2, 3},
  {4, 5, 6}
};

sheet["E2"] = "=SUM(r1)";

Output:

139

Learn more about formula, see Formula.

Remove a named range definition

To remove a named range definition from spreadsheet, use the UndefineNamedRange method.

sheet.UndefineNamedRange("myname");

Customize name parsing provider

It’s possible to use a customize name in formula even not define it in spreadsheet:

sheet["F1"] = "=myName1 + myName2";

The myName1 and myName2 is not defined as named range, to provide theirs value by programming, use the customize name parsing provider, see Customize function.

Range changing after inserting/removing rows and columns

Inserting/removing rows and columns will affect the named range:

  • If insert rows or columns before a named range, the named range will be moved automatically.
  • If any rows or columns inserted inside a named range, the rows or columns in the named range will be increased automatically.
  • If any rows or columns removed inside a named range, the rows or columns in the named range will be also removed automatically. If all the rows or columns in the named range have been removed, the range will also be removed automatically.

The behaviors above currently is not able to change.

Events

To get an event when any named range defining, use the NamedRangeDefined event; to get an event when any named range definition removing, use the NamedRangeUndefined event.

Define named range in ReoGridEditor

To define a named range in ReoGridEditor, select the target range, choose menu Formula->Define named range.

179

Press ‘OK’, the name will be displayed in the drop-down of address bar:

180

Select the name ‘header’, the range will be selected.

93