How to use Named Ranges with XlsIO?

22 Dec 20228 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