Worksheet.GetDataRange() Method

Gets the worksheet range that encompasses all cells containing data and formulas.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v23.2.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

CellRange GetDataRange()

VB.NET

Function GetDataRange As CellRange

Returns

Type	Description
CellRange	A CellRange which contains all nonempty cells in a worksheet.

Remarks

The GetDataRange method returns a worksheet range that meets the following requirements.

• The data range is a continuous range of nonempty cells. The top-left cell of the data range is a cell located at the intersection of the topmost row and the leftmost column containing non-empty cells. The bottom-right cell of the data range is a cell located at the intersection of the lowest row and the rightmost column containing nonempty cells. If a nonempty cell is located in the hidden row or column, it is still included in the data range.

  Note

  The cell is not empty if it contains a value (CellRange.Value) or a formula (CellRange.Formula).

  

• Empty cells containing visual formatting or conditional formatting are not included in the data range.

  

• The data range is extended to include merged cells containing data. If merged cells are empty, they are ignored.

  

• Tables are included in the data range. If a table contains empty rows below all non-empty cells, these rows are excluded from the data range.

  

• Pictures, charts and comments are not included in the data range.

  

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the GetDataRange() 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-load-an-ms-access-database-file-into-the-document/CS/WindowsFormsApplication1/MSAccessImportHelper.cs#L161

}
Table table = workSheet.Tables.Add(workSheet.GetDataRange(), true);
table.Style = wb.TableStyles[BuiltInTableStyleId.TableStyleLight1];

winforms-spreadsheet-custom-draw-example/CS/WindowsFormsApp1/Form1.cs#L120

// Specify the background color for cells that contain data and belong to odd rows.
var dataRange = e.Cell.Worksheet.GetDataRange();
if (e.Cell.IsIntersecting(dataRange) && e.Cell.RowIndex % 2 != 0)

VB.NET

winforms-spreadsheet-load-an-ms-access-database-file-into-the-document/VB/WindowsFormsApplication1/MSAccessImportHelper.vb#L181

Next i
Dim table As Table = workSheet.Tables.Add(workSheet.GetDataRange(), True)
table.Style = wb.TableStyles(BuiltInTableStyleId.TableStyleLight1)

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

' Specify the background color for cells that contain data and belong to odd rows.
Dim dataRange = e.Cell.Worksheet.GetDataRange()
If e.Cell.IsIntersecting(dataRange) AndAlso e.Cell.RowIndex Mod 2 <> 0 Then