CellRange.GetReferenceR1C1(ReferenceElement, Cell) Method

Gets the cell or cell range reference in the R1C1 reference style.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v24.1.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

string GetReferenceR1C1(
    ReferenceElement options,
    Cell baseCell
)

VB.NET

Function GetReferenceR1C1(
    options As ReferenceElement,
    baseCell As Cell
) As String

Parameters

Name	Type	Description
options	ReferenceElement	A ReferenceElement enumeration values that specify whether to return the relative, absolute or mixed cell reference, and whether to precede it with the worksheet name.
baseCell	Cell	A Cell object that specifies the base cell relative to which the relative elements of the cell reference (row or column, or both) should be obtained, if the cell reference is relative or mixed. If the cell reference is absolute, pass null (Nothing in Visual Basic).

Returns

Type	Description
String	A String value that specifies the R1C1-style reference to a cell or range of cells.

Remarks

Use the CellRange.GetReferenceA1 method to get cell references in the A1 reference style.

Example

This example demonstrates how to get R1C1-style references (relative, absolute and mixed) to a range of cells.

C#

using DevExpress.Spreadsheet;
// ...

IWorkbook workbook = spreadsheetControl1.Document;
Worksheet worksheet = workbook.Worksheets["Sheet1"];

CellRange range = worksheet.Range["B3:D5"];

// Returns the range reference relative to cell E7 - "R[-4]C[-3]:R[-2]C[-1]".
string refRel = range.GetReferenceR1C1(worksheet.Cells["E7"]);

// Returns the range reference relative to cell A4, including the worksheet name - 
// "Sheet1!R[-1]C[1]:R[1]C[3]".
string refSheet = range.GetReferenceR1C1(ReferenceElement.IncludeSheetName, worksheet.Cells["A4"]);

// Returns the mixed range reference (with absolute row reference and column reference relative to cell B2),
// including the worksheet name - "Sheet1!R3C[-1]:R5C[1]".
string refMixed = range.GetReferenceR1C1(ReferenceElement.RowAbsolute | ReferenceElement.IncludeSheetName,
                                        worksheet.Cells["B2"]);

// Returns the absolute range reference - "R3C2:R5C4".
string refAbs = range.GetReferenceR1C1(ReferenceElement.RowAbsolute | ReferenceElement.ColumnAbsolute, null);

VB.NET

Imports DevExpress.Spreadsheet
' ...

Dim workbook As IWorkbook = spreadsheetControl1.Document
Dim worksheet As Worksheet = workbook.Worksheets("Sheet1")

Dim range As CellRange = worksheet.Range("B3:D5")

' Returns the range reference relative to cell E7 - "R[-4]C[-3]:R[-2]C[-1]".
Dim refRel As String = range.GetReferenceR1C1(worksheet.Cells("E7"))

' Returns the range reference relative to cell A4, including the worksheet name - 
' "Sheet1!R[-1]C[1]:R[1]C[3]".
Dim refSheet As String = range.GetReferenceR1C1(ReferenceElement.IncludeSheetName, worksheet.Cells("A4"))

' Returns the mixed range reference (with absolute row reference and column reference relative to cell B2),
' including the worksheet name - "Sheet1!R3C[-1]:R5C[1]".
Dim refMixed As String = range.GetReferenceR1C1(ReferenceElement.RowAbsolute Or ReferenceElement.IncludeSheetName, _
                                                worksheet.Cells("B2"))

' Returns the absolute range reference - "R3C2:R5C4".
Dim refAbs As String = range.GetReferenceR1C1(ReferenceElement.RowAbsolute Or ReferenceElement.ColumnAbsolute, Nothing)

Related GitHub Examples

The following code snippet (auto-collected from DevExpress Examples) contains a reference to the GetReferenceR1C1(ReferenceElement, Cell) method.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

winforms-spreadsheet-custom-draw-example/CS/CustomDrawExample/Form1.cs#L96

Font headingFont = new Font("Times New Roman", e.Font.Size);
string cellRef = e.Cell.GetReferenceR1C1(ReferenceElement.RowAbsolute | ReferenceElement.ColumnAbsolute, null);
string formula = String.Format("=RANK.AVG({0},R{1}C{2}:R{3}C{4})", cellRef, e.Cell.RowIndex + 1, 2, e.Cell.RowIndex + 1, 10);

VB.NET

winforms-spreadsheet-custom-draw-example/VB/CustomDrawExample/Form1.vb#L84

Dim headingFont As Font = New Font("Times New Roman", e.Font.Size)
Dim cellRef As String = e.Cell.GetReferenceR1C1(ReferenceElement.RowAbsolute Or ReferenceElement.ColumnAbsolute, Nothing)
Dim formula As String = String.Format("=RANK.AVG({0},R{1}C{2}:R{3}C{4})", cellRef, e.Cell.RowIndex + 1, 2, e.Cell.RowIndex + 1, 10)