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 Referencing Cells and Regions 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.
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 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 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.
The Worksheet object’s methods GetCell and GetRegion allow a named reference to be specified to access a cell or region.
Random numbers in Regions A1:A10 and B1:B10 of the Worksheet.
Reference name added for cell B3 - refNameB3. This can be done by right clicking on Cell B3 and from the context menu choose ‘Define Name’, which pops up the ‘New Name’ window where the reference name can be specified.
Reference name added for region C1:C10 – refNameRegionC1C10. This can be done by selecting the range C1:C10 and from the context menu selecting the ‘Define Name’ option which pops up the ‘New Name’ window where the range reference name can be specified.
The following code loads a Workbook and creates an instance of the Worksheet. Cell B3 of the Worksheet is accessed using the GetCell 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.
In C#:
// Load a Workbook
Infragistics.Documents.Excel.Workbook workBook = Infragistics.Documents.Excel.Workbook.Load(inputStream);
Infragistics.Documents.Excel.Worksheet sampleSheet = workBook.Worksheets[0];
// Create a comment
Infragistics.Documents.Excel.WorksheetCellComment testComment = new Infragistics.Documents.Excel.WorksheetCellComment();
Infragistics.Documents.Excel.FormattedString formatted = new Infragistics.Documents.Excel.FormattedString("This cell has a reference name.");
testComment.Text = formatted;
// Access a cell by its reference name
sampleSheet.GetCell("refNameB3").Comment = testComment;
// Access a Region by its reference name
sampleSheet.GetRegion("refNameRegionC1C10").ApplyFormula("=A1*B1");
// Save the Workbook with the applied changes
workBook.Save(outputStream);The Worksheet object’s method GetRegions allows a named reference to be specified to access an array of regions.
Random numbers in Regions A3:C7, E4:G6, and I2:L5 of the Worksheet.
Reference name added for multiple regions A1:C5, E2:G4, and I7:G8 – MultipleRegions. Naming of reference is done by selecting the reference in Excel and going to Formulas -> Name Manager. Name manager window appears. Click on New… button and New Name window appears, where you can specify name of your selected range.
The following code loads a Workbook and creates an instance of the Worksheet. Region E4:G6 is accessed by using GetRegions method, which returns array of regions, and specifying second element of this array. A formula is applied on E4:G6 region.
In C#:
// Load a Workbook
Infragistics.Documents.Excel.Workbook workBook = Infragistics.Documents.Excel.Workbook.Load(inputStream);
Infragistics.Documents.Excel.Worksheet sampleSheet = workBook.Worksheets[0];
// Access a Region by its reference name by its index in a array of regions and apply a formula on the region
sampleSheet.GetRegions("MultipleRegions")[1].ApplyFormula("=$A$1*A3");
workBook.Save(outputStream);A reference to a cell or region can be obtained using the NamedReference instance that refers to a cell or region.
The following code defines a NamedReference for a cell, region and array of regions and gets access to the cell, region and array of regions from the NamedReference instance.
In C#:
// Load a Workbook
Infragistics.Documents.Excel.Workbook workBook =
Infragistics.Documents.Excel.Workbook.Load(inputStream);
Infragistics.Documents.Excel.Worksheet sampleSheet = workBook.Worksheets[0];
// Define a Named Reference for a Cell
Infragistics.Documents.Excel.NamedReference namedRefCell = workBook.NamedReferences.Add("NamedReferenceCell", "=Sheet1!$B$5", sampleSheet);
System.Diagnostics.Debug.WriteLine(namedRefCell.ReferencedCell.ToString());
// Define a Named Reference for a Region
Infragistics.Documents.Excel.NamedReference namedRefRegion = workBook.NamedReferences.Add("NamedRefRegion", "=Sheet1!$A$5:$A$10", sampleSheet);
System.Diagnostics.Debug.WriteLine(namedRefRegion.ReferencedRegion.ToString());
// Define a Named Referene for Multiple Regions
Infragistics.Documents.Excel.NamedReference namedMultiRef = workBook.NamedReferences.Add("NamedMultiRegion",
"=Sheet1!$A$1:$B$5,Sheet1!$D$5:$G$10", sampleSheet);
System.Diagnostics.Debug.WriteLine(namedMultiRef.ReferencedRegions[1].ToString());The following topics provide additional information related to this topic.
The following material (available outside the Infragistics family of content) provides additional information related to this topic.