How to use Named Ranges with XlsIO?

26 May 20229 minutes to read

A named range can be added to worksheet or workbook based on the required scope, the following code snippet illustrate this. For more information, see Named Range

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;

    IWorkbook workbook = application.Workbooks.Create(1);

    IWorksheet worksheet = workbook.Worksheets[0];

    //Adding named range to the workbook            

    IName workBookName = workbook.Names.Add("WorkBookName");

    workBookName.RefersToRange = worksheet.Range["I8"];

    //Looping through the Named Ranges in a workbook.

    foreach (IName workbookName in workbook.Names)
    {
    MessageBox.Show(workbookName.Name.ToString());
    }

    //Adding named range to the worksheet

    IName worksheetName = worksheet.Names.Add("WorkSheetName");

    worksheetName.RefersToRange = worksheet.Range["J8"];


    //Looping through the Named Ranges in a worksheet.

    foreach (IName name in worksheet.Names)
    {
    MessageBox.Show(name.Name.ToString());
    }

    workbook.SaveAs("NamedRange.Xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Excel2013

    Dim workbook As IWorkbook = application.Workbooks.Create(1)

    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Adding named range to the workbook

    Dim workBookName__1 As IName = workbook.Names.Add("WorkBookName")

    workBookName__1.RefersToRange = worksheet.Range("I8")

    'Looping through the Named Ranges in a workbook.

    For Each workbookName__2 As IName In workbook.Names

        MessageBox.Show(workbookName__2.Name.ToString())

    Next

    'Adding named range to the worksheet

    Dim worksheetName__3 As IName = worksheet.Names.Add("WorkSheetName")

    worksheetName__3.RefersToRange = worksheet.Range("J8")

    'Looping through the Named Ranges in a worksheet.

    For Each worksheetName__4 As IName In worksheet.Names

        MessageBox.Show(worksheetName__4.Name.ToString())

    Next

    workbook.SaveAs("NamedRange.Xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;
    IWorkbook workbook = application.Workbooks.Create(1);

    IWorksheet worksheet = workbook.Worksheets[0];

    //Adding named range to the workbook
    IName workBookName = workbook.Names.Add("WorkBookName");
    workBookName.RefersToRange = worksheet.Range["I8"];

    //Looping through the Named Ranges in a workbook
    foreach (IName workbookName in workbook.Names)
    {
        MessageDialog showDialog = new MessageDialog(workbookName.Name.ToString());
    }

    //Adding named range to the worksheet
    IName worksheetName = worksheet.Names.Add("WorkSheetName");
    worksheetName.RefersToRange = worksheet.Range["J8"];

    //Looping through the Named Ranges in a worksheet
    foreach (IName name in worksheet.Names)
    {
        MessageDialog showDialog = new MessageDialog(name.Name.ToString());
    }

    //Initializes FileSavePicker
    FileSavePicker savePicker = new FileSavePicker();
    savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
    savePicker.SuggestedFileName = "NamedRange";
    savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

    //Creates a storage file from FileSavePicker
    StorageFile storageFile = await savePicker.PickSaveFileAsync();

    //Saves changes to the specified storage file
    await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;
    IWorkbook workbook = application.Workbooks.Create(1);

    IWorksheet worksheet = workbook.Worksheets[0];

    //Adding named range to the workbook
    IName workBookName = workbook.Names.Add("WorkBookName");
    workBookName.RefersToRange = worksheet.Range["I8"];

    //Looping through the Named Ranges in a workbook
    foreach (IName workbookName in workbook.Names)
    {
        MessageBox.Show(workbookName.Name.ToString());
    }

    //Adding named range to the worksheet
    IName worksheetName = worksheet.Names.Add("WorkSheetName");
    worksheetName.RefersToRange = worksheet.Range["J8"];

    //Looping through the Named Ranges in a worksheet
    foreach (IName name in worksheet.Names)
    {
        MessageBox.Show(name.Name.ToString());
    }

    FileStream stream = new FileStream("NamedRange.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);
    workbook.SaveAs(stream);

    workbook.Close();
    excelEngine.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;
    IWorkbook workbook = application.Workbooks.Create(1);

    IWorksheet worksheet = workbook.Worksheets[0];

    //Adding named range to the workbook
    IName workBookName = workbook.Names.Add("WorkBookName");
    workBookName.RefersToRange = worksheet.Range["I8"];

    //Looping through the Named Ranges in a workbook
    foreach (IName workbookName in workbook.Names)
    {
        MessageBox.Show(workbookName.Name.ToString());
    }

    //Adding named range to the worksheet
    IName worksheetName = worksheet.Names.Add("WorkSheetName");
    worksheetName.RefersToRange = worksheet.Range["J8"];

    //Looping through the Named Ranges in a worksheet
    foreach (IName name in worksheet.Names)
    {
        MessageBox.Show(name.Name.ToString());
    }

    MemoryStream stream = new MemoryStream();
    workbook.SaveAs(stream);

    stream.Position = 0;

    //Save the stream as a file in the device and invoke it for viewing
    Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("NamedRange.xlsx", "application/msexcel", stream);
}

See Also