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);
}