How to use Named Ranges with XlsIO?
8 Dec 20234 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());
}
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());
}
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