Table.Range Property

Gets or sets the range of cells that contains the table.

Namespace: DevExpress.Spreadsheet

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

Declaration

C#

Range Range { get; set; }

VB.NET

Property Range As Range

Property Value

Type	Description
Range	A Range object specifying the cell range that contains the entire table.

Remarks

The cell range that the Range property returns for a sample table is highlighted in the image below.

To get the cell range that contains table data only (excluding the header and total rows), use the Table.DataRange property. The Table.HeaderRowRange and Table.TotalRowRange properties return cell ranges containing a table’s header row and total row respectively.

To obtain a cell range of an individual table column, use TableColumn.Range or TableColumn.DataRange.

Example

This example demonstrates how to create a table from a range of cells. The sample range includes a list of products and invoice information on each product: price, quantity and discount.

The resulting table will provide an additional column to calculate the amount per product, and an additional row to show the total amount. Follow the steps below:

1. Create a Table

   Add a new Table object to the worksheet’s collection of tables (Worksheet.Tables) via the TableCollection.Add method. Pass the following parameters:

   • The range of cells that you wish to format as a table. Use the “B2:F5” range reference instead of “B2:E5” to include an additional column to the right that calculates and displays product amounts.
   • A Boolean value indicating that the top row of the specified range will be the table header.

2. Format the Table

   Format the table by applying one of the built-in table styles. To do this, set the Table.Style property to the table style object from the IWorkbook.TableStyles collection. Access the desired style by its BuiltInTableStyleId identifier.

3. Table Columns

   Access table columns by their indexes from the column collection that the Table.Columns property returns.

4. Table Header Row

   To specify the last column header, set its TableColumn.Name property to “Amount”. Headers of other table columns are automatically set to the values of the corresponding cells:

   • table.Columns[0].Name = “Product”
   • table.Columns[1].Name = “Price”
   • table.Columns[2].Name = “Quantity”
   • table.Columns[3].Name = “Discount”

5. Calculated Column

   Specify the formula to calculate the product amount, and assign it to the Amount column using the TableColumn.Formula property. In the formula, refer to table columns by their headers.

6. Table Total Row

   Set the Table.ShowTotals property to true, to display the total row at the bottom of the table.

7. Total Row Function

   Specify the function to calculate the total amount. To do this, set the TableColumn.TotalRowFunction property of the Amount column to TotalRowFunction.Sum.

   Note

   In the total row, you can use any formulas you wish, not only functions listed by the TotalRowFunction enumerator. To use a custom formula in the total row, assign it to the TableColumn.TotalRowFormula property of the required table column.

8. Table Ranges and Data Ranges

   Specify number formats to display numbers as currency values in the Price and Amount columns, and as percentage values in the Discount column. To access the data range of a table column, use the TableColumn.DataRange property.

   Use the Table.HeaderRowRange and Table.TotalRowRange properties to access table header and total row ranges, and set the alignment.

   Change the width of table columns. To do this, access the table range via the Table.Range property, and use its Range.ColumnWidthInCharacters property.

9. The image below shows the results.

   

Note

A complete sample project is available at https://github.com/DevExpress-Examples/winforms-spreadsheetcontrol-api-part-2-e4832

TableActions.vb

' Insert a table in the worksheet.
Dim table As Table = worksheet.Tables.Add(worksheet("B2:F5"), True)

' Format the table by applying a built-in table style.
table.Style = workbook.TableStyles(BuiltInTableStyleId.TableStyleMedium27)

' Access table columns and name them.
Dim productColumn As TableColumn = table.Columns(0)
productColumn.Name = "Product"
Dim priceColumn As TableColumn = table.Columns(1)
priceColumn.Name = "Price"
Dim quantityColumn As TableColumn = table.Columns(2)
quantityColumn.Name = "Quantity"
Dim discountColumn As TableColumn = table.Columns(3)
discountColumn.Name = "Discount"
Dim amountColumn As TableColumn = table.Columns(4)
amountColumn.Name = "Amount"

' Set the formula to calculate the amount per product 
' and display results in the "Amount" column.
amountColumn.Formula = "=[Price]*[Quantity]*(1-[Discount])"

' Display the total row in the table.
table.ShowTotals = True

' Set the label and function to display the sum of the "Amount" column.
discountColumn.TotalRowLabel = "Total:"
amountColumn.TotalRowFunction = TotalRowFunction.Sum

' Specify the number format for each column.
priceColumn.DataRange.NumberFormat = "$#,##0.00"
discountColumn.DataRange.NumberFormat = "0.0%"
amountColumn.Range.NumberFormat = "$#,##0.00;$#,##0.00;"""";@"

' Specify horizontal alignment for header and total rows of the table.
table.HeaderRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
table.TotalRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center

' Specify horizontal alignment to display data in all columns except the first one.
For i As Integer = 1 To table.Columns.Count - 1
    table.Columns(i).DataRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
Next i

' Set the width of table columns.
table.Range.ColumnWidthInCharacters = 10

TableActions.cs

// Insert a table in the worksheet.
Table table = worksheet.Tables.Add(worksheet["B2:F5"], true);

// Format the table by applying a built-in table style.
table.Style = workbook.TableStyles[BuiltInTableStyleId.TableStyleMedium27];

// Access table columns and name them.
TableColumn productColumn = table.Columns[0];
productColumn.Name = "Product";
TableColumn priceColumn = table.Columns[1];
priceColumn.Name = "Price";
TableColumn quantityColumn = table.Columns[2];
quantityColumn.Name = "Quantity";
TableColumn discountColumn = table.Columns[3];
discountColumn.Name = "Discount";
TableColumn amountColumn = table.Columns[4]; 
amountColumn.Name = "Amount";

// Set the formula to calculate the amount per product 
// and display results in the "Amount" column.
amountColumn.Formula = "=[Price]*[Quantity]*(1-[Discount])";

// Display the total row in the table.
table.ShowTotals = true;

// Set the label and function to display the sum of the "Amount" column.
discountColumn.TotalRowLabel = "Total:";
amountColumn.TotalRowFunction = TotalRowFunction.Sum;

// Specify the number format for each column.
priceColumn.DataRange.NumberFormat = "$#,##0.00";
discountColumn.DataRange.NumberFormat = "0.0%";
amountColumn.Range.NumberFormat = "$#,##0.00;$#,##0.00;\"\";@";

// Specify horizontal alignment for header and total rows of the table.
table.HeaderRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
table.TotalRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;

// Specify horizontal alignment to display data in all columns except the first one.
for (int i = 1; i < table.Columns.Count; i++)
{
    table.Columns[i].DataRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
}

// Set the width of table columns.
table.Range.ColumnWidthInCharacters = 10;

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the Range property.

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#

how-to-export-cell-range-to-a-datatable-e4997/CS/ExportToDataTableExample/Form1.cs#L95

Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
Range range = worksheet.Tables[0].Range;

an-example-of-using-a-cell-range-as-a-data-source-t483776/CS/RangeDataSourceForSnap/Form1.cs#L32

Range dataRange = sheet.Selection;
if (dataRange.Equals(sheet.Tables[0].Range))
    options.UseFirstRowAsHeader = true;

VB.NET

how-to-export-cell-range-to-a-datatable-e4997/VB/ExportToDataTableExample/Form1.vb#L89

Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
Dim range As Range = worksheet.Tables(0).Range

an-example-of-using-a-cell-range-as-a-data-source-t483776/VB/RangeDataSourceForSnap/Form1.vb#L35

Dim dataRange As Range = sheet.Selection
If dataRange.Equals(sheet.Tables(0).Range) Then
    options.UseFirstRowAsHeader = True