NativeExcel2 for .NET Developer's Reference

Defined Names

Example

This example demonstrates how to use defined names

Output file: console-names.xls

[C#]
using System;
using NativeExcel;

namespace Console_Names
{
    class Program
    {
        static void Main(string[] args)
        {
            string FileName = "console-names.xls";
            CreateWorkbook(FileName);
            OpenWorkbookWithExcel(FileName);
        }

        static void CreateWorkbook(string FileName) {
            //Create workbook
            IWorkbook book = NativeExcel.Factory.CreateWorkbook();

            //Add sheet
            IWorksheet sheet1 = book.Worksheets.Add();
            //Add sheet
            IWorksheet sheet2 = book.Worksheets.Add();

            //Add global defined name
            book.Names.Add("MyName", "=Sheet1!$C$10"); 
            //Add local defined name with the same name
            sheet2.Names.Add("MyName", "=Sheet2!$C$10"); 

            //Set the cell values
            sheet1.Cells["C10"].Value = 1;
            sheet2.Cells["C10"].Value = 2;

            //Add formula which using global and local defined names
            sheet1.Cells["C11"].Value = "= MyName + Sheet2!MyName";

            //Save workbook
            book.SaveAs(FileName);
        }

        static void OpenWorkbookWithExcel(string FileName){
            try {
                System.Diagnostics.Process.Start(FileName);
            } catch {
                Console.WriteLine(FileName + " created in application folder");
            }
        }
    }
}
[Visual Basic]
imports System
imports NativeExcel

Module Console_Names

    Sub Main()
        Dim FileName As String = "console-names.xls"
        CreateWorkbook(FileName)
        OpenWorkbookWithExcel(FileName)
    End Sub

    Sub CreateWorkbook(FileName As String)

        'Create workbook
        Dim book As IWorkbook = NativeExcel.Factory.CreateWorkbook()
        'Add sheet
        Dim sheet1 As IWorksheet = book.Worksheets.Add()

        'Add sheet
        Dim sheet2 As IWorksheet = book.Worksheets.Add()

        'Add global defined name
        book.Names.Add("MyName", "=Sheet1!$C$10") 
        'Add local defined name with the same name
        sheet2.Names.Add("MyName", "=Sheet2!$C$10")

        'Set the cell values
        sheet1.Cells("C10").Value = 1
        sheet2.Cells("C10").Value = 2

        'Add formula which using global and local defined names
        sheet1.Cells("C11").Value = "= MyName + Sheet2!MyName"

        'Save workbook
        book.SaveAs(FileName)

    End Sub

    Sub OpenWorkbookWithExcel(FileName As String)
        Try
           System.Diagnostics.Process.Start(FileName)
        Catch
           Console.WriteLine(FileName + " created in application folder")
        End Try  
    End Sub

End Module