Live SpreadsheetGear API Samples

Worksheet-scoped named ranges are managed from the IWorksheet.Names[...] collection.

// Create a new workbook with 2 sheets and set some cell values on each sheet.
SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
workbookSet.SheetsInNewWorkbook = 2;
SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Add();
SpreadsheetGear.IWorksheet sheet1 = workbook.Worksheets["Sheet1"];
SpreadsheetGear.IWorksheet sheet2 = workbook.Worksheets["Sheet2"];
            
// Create a named range called "MyName" on Sheet1!A1 and use it in a formula in A2
sheet1.Cells["A1"].Value = "Hello";
sheet1.Names.Add("MyName", "=$A$1");
sheet1.Cells["A2"].Formula = "=MyName";

// Create an identical named range setup, but on Sheet2.
sheet2.Cells["A1"].Value = "World";
sheet2.Names.Add("MyName", "=$A$1");
sheet2.Cells["A2"].Formula = "=MyName";

// Check value for each defined name on each sheet.
System.Console.WriteLine(sheet1.Cells["MyName"].Value);    // OUTPUT: Hello
System.Console.WriteLine(sheet2.Cells["MyName"].Value);    // OUTPUT: World

// Other named range details
{
    // Access names from IWorksheet.Names[...] by either name or index (names are 
    // stored in ascending alphabetical order).
    SpreadsheetGear.IName name = sheet1.Names["MyName"];

    // Rename a named range.
    name.Name = "MyNewName";

    // Add a comment.
    name.Comment = "My comment about this named range.";

    // Set to false to omit this name from the list of displayed names in Excel's 
    // "Name Manager" dialog and SpreadsheetGear's WorkbookExplorer dialog.
    name.Visible = false;

    // Returns an IRange of the range for which this named range refers to.  For 
    // names that don't refer to a range, null is returned.
    SpreadsheetGear.IRange range = name.RefersToRange;
    System.Console.WriteLine(range.Address);   // OUTPUT: $A$1

    // Returns a string representation of what the named range refers to, in A1 
    // notation.
    System.Console.WriteLine(name.RefersTo);   // OUTPUT: =Sheet1!$A$1

    // Returns a string representation of what the named range refers to, in R1C1
    // notation.
    System.Console.WriteLine(name.RefersToR1C1);   // OUTPUT: =Sheet1!R1C1
}