Topic Overview
This topic explains, with code examples, how to access cells and regions within a worksheet in a Microsoft® Excel® workbook by their names or by their named references. For information about how to access cells and regions by their reference strings, refer to the Accessing Cells and Regions by their Reference Strings topic.
This topic contains the following sections:
In Microsoft Excel, individual cells, as well as cell regions can have Names assigned to them, (The Names are assigned and displayed in the Name Box of the spreadsheet.) The Name of a cell or region can be used to reference that cell or region instead of their cell references. The Names of cells and regions can be defined either in Excel or programmatically in code.
Infragistics supports the referencing of cells and regions by Name through the and methods of the Worksheet object. You refer to the cell or region using the NamedReference instance that refers to that cell or region.
To be able to access an individual cell or a region of cells, they must be Named, either in MS Excel or programmatically in code.
To define a Name for a cell or region in an Excel spreadsheet, with the cell or region selected, click the Name Box , type the desired name , and then press Enter .
Cell or region Names defined in code are sometimes referred to as “named references”.
To Name a cell or a region in code, use the syntax that follows. In your actual code, replace the variables (cell_Name, range_Name, sheet_name, cell_reference, range_cell_reference, instance_for_cell_NamedReference, and instance_for_range_NamedReference) with specific names.
In Visual Basic:
' Define a Name for a cell.
Dim instance_for_cell_NamedReference As NamedReference = wrkBook.NamedReferences.Add("cell_Name", "=sheet_name!cell reference ", sheet_name)
' Define a Name for a region.
Dim instance_for_range_NamedReference As NamedReference = wrkBook.NamedReferences.Add("range_Name ", "= sheet_name!range cell reference ", sheet_name )In C#:
// Define a Name for a cell.
NamedReference instance_for_cell_NamedReference = wrkBook.NamedReferences.Add("cell_Name", "=sheet_name!cell reference", sheet_name);
// Define a Name for a region.
NamedReference instance_for_range_NamedReference = wrkBook.NamedReferences.Add("range_Name", "=sheet_name!range cell reference", sheet_name);To reference a cell or a region by Name in code, use the following syntax. In your actual code replace the variables (cell_Name, range_Name) with specific names.
In Visual Basic:
' Access a cell by its name.
sampleSheet.GetCell("cell_Name")
' Access a region by its name
sampleSheet.GetRegion("range_Name")In C#:
// Access a cell by its Name.
sampleSheet.GetCell("cell_Name");
// Access a region by its Name.
sampleSheet.GetRegion("range_Name");The following table lists the code examples included in this topic.
In the following code, Names are created in code for cell B5 and for cell region G1 ÷ G10 of Sheet1 .
In Visual Basic:
' Define a Name for the cell.
Dim nameRefCell As NamedReference = wrkBook.NamedReferences.Add("NamedRefCell", "=Sheet1!$B$5", sampleSheet)
' Define a Name for the region.
Dim namerefRegion As NamedReference = wrkBook.NamedReferences.Add("NamedRefRegion", "=Sheet1!$G$1:$G$10", sampleSheet)In C#:
// Define a Name for the cell.
NamedReference nameRefCell = wrkBook.NamedReferences.Add("NamedRefCell", "=Sheet1!$B$5", sampleSheet);
// Define a Name for the region.
NamedReference namerefRegion = wrkBook.NamedReferences.Add("NamedRefRegion", "=Sheet1!$G$1:$G$10", sampleSheet);The following code demonstrates how to access a cell with a Name refNameB3 and a cell region with a Name refNameRegionC1C10 .
In Visual Basic:
' Access a cell by its Name.
sampleSheet.GetCell("refNameB3").Comment = testComment
' Access a region by its Name.
sampleSheet.GetRegion("refNameRegionC1C10").ApplyFormula("=A1*B1")In C#:
// Access a cell by its Name.
sampleSheet.GetCell("refNameB3").Comment = testComment;
// Access a region by its Name.
sampleSheet.GetRegion("refNameRegionC1C10").ApplyFormula("=A1*B1");The following code demonstrates how to reference a Named cell and region for displaying their content as a string in a message box.
In Visual Basic:
MessageBox.Show(nameRefCell.ReferencedCell.Value.ToString())
MessageBox.Show(namerefRegion.ReferencedRegion.Worksheet.ToString())In C#:
MessageBox.Show(nameRefCell.ReferencedCell.Value.ToString());
MessageBox.Show(namerefRegion.ReferencedRegion.Worksheet.ToString());The following example demonstrates how to access by their names, the cell B3 and the region C1:C10 in the first worksheet of workbook named Book1.xls residing on C:. The cell and the region are named refNameB3 and refNameRegionC1C10, respectively earlier in Excel.
The example code loads a Workbook and creates an instance of the Worksheet. Cell B3 of the Worksheet is accessed using the method by passing in the named reference of the cell. A comment is added to cell B3 .
Similarly RegionC1:C10 is accessed using the GetRegion method by passing in the named reference of the region. A formula is applied to the region C1:C10 .
This example has the following prerequisites:
An Excel file named Book1.xls residing in C:\
Arbitrary numbers added in regions A1:A10 and B1:B10 of the Worksheet
Names added for Cell B3 and region C1:C10 as explained in Naming cells and regions in Excel
In Visual Basic:
' Load the workbook.
Dim wrkBook As Infragistics.Documents.Excel.Workbook = Infragistics.Documents.Excel.Workbook.Load("C:\\Book1.xls")
Dim sampleSheet As Infragistics.Documents.Excel.Worksheet = wrkBook.Worksheets(0)
Dim testComment As Infragistics.Documents.Excel.WorksheetCellComment = New WorksheetCellComment
Dim formatted As FormattedString = New FormattedString("This cell has a reference name.")
testComment.Text = formatted
' Access the cell by its Name.
sampleSheet.GetCell("refNameB3").Comment = testComment
' Access the region by its Name.
sampleSheet.GetRegion("refNameRegionC1C10").ApplyFormula("=A1*B1")
' Save the workbook.
wrkBook.Save("C:\\Book1.xls")In C#:
// Load the workbook.
Infragistics.Documents.Excel.Workbook wrkBook = Infragistics.Documents.Excel.Workbook.Load("C:\\Book1.xls");
Infragistics.Documents.Excel.Worksheet sampleSheet = wrkBook.Worksheets[0];
Infragistics.Documents.Excel.WorksheetCellComment testComment = new WorksheetCellComment();
FormattedString formatted = new FormattedString ("This cell has a reference name.");
testComment.Text = formatted;
// Access the cell by its Name.
sampleSheet.GetCell("refNameB3").Comment = testComment;
// Access the region by its Name.
sampleSheet.GetRegion("refNameRegionC1C10").ApplyFormula("=A1*B1");
// Save the workbook.
wrkBook.Save("C:\\Book1.xls");The following code demonstrates how to define a NamedReference for a cell and region and get access to them from the NamedReference instance.
This example has the following prerequisites:
An Excel file named Book1.xls residing in C:\
Arbitrary numbers added in Regions A1:A10 and B1:B10 of the Worksheet
In Visual Basic:
' Load the workbook.
Dim wrkBook As Infragistics.Documents.Excel.Workbook = Infragistics.Documents.Excel.Workbook.Load("C:\\Book1.xls")
Dim sampleSheet As Infragistics.Documents.Excel.Worksheet = wrkBook.Worksheets(0)
'Define a Name for the cell.
Dim nameRefCell As NamedReference = wrkBook.NamedReferences.Add("NamedRefCell", "=Sheet1!$B$5", sampleSheet)
MessageBox.Show(nameRefCell.ReferencedCell.Value.ToString())
' Define a Name for the region.
Dim namerefRegion As NamedReference = wrkBook.NamedReferences.Add("NamedRefRegion", "=Sheet1!$G$1:$G$10", sampleSheet)
MessageBox.Show(namerefRegion.ReferencedRegion.Worksheet.ToString())In C#:
// Load the workbook.
Infragistics.Documents.Excel.Workbook wrkBook = Infragistics.Documents.Excel.Workbook.Load("C:\\Book1.xls");
Infragistics.Documents.Excel.Worksheet sampleSheet = wrkBook.Worksheets[0];
// Define a Name for the cell.
NamedReference nameRefCell = wrkBook.NamedReferences.Add("NamedRefCell", "=Sheet1!$B$5", sampleSheet);
MessageBox.Show(nameRefCell.ReferencedCell.Value.ToString());
// Define a Name for the region.
NamedReference namerefRegion = wrkBook.NamedReferences.Add("NamedRefRegion", "=Sheet1!$G$1:$G$10", sampleSheet);
MessageBox.Show(namerefRegion.ReferencedRegion.Worksheet.ToString());The following topics provide additional information related to this topic.