All docs
V21.1
21.2 (EAP/Beta)
21.1
20.2
The page you are viewing does not exist in version 20.2. This link will take you to the root page.
20.1
The page you are viewing does not exist in version 20.1. This link will take you to the root page.
19.2
The page you are viewing does not exist in version 19.2. This link will take you to the root page.
19.1
The page you are viewing does not exist in version 19.1. This link will take you to the root page.
18.2
The page you are viewing does not exist in version 18.2. This link will take you to the root page.
18.1
The page you are viewing does not exist in version 18.1. This link will take you to the root page.
17.2
The page you are viewing does not exist in version 17.2. This link will take you to the root page.

Spreadsheet Tables

  • 13 minutes to read

The WinForms Spreadsheet control allows you to convert a cell range into a table. Use tables to manage worksheet data more effectively: sort and filter table data, bind a table to a data source, create calculated columns, display a total row, and use table names in formulas. You can apply a predefined or custom style to a table to change its appearance.

Tables in the User Interface

The WinForms Spreadsheet ships with the Create Table dialog that allows users to insert a new table. Click the Table button on the Insert tab to invoke this dialog. In the dialog box, select a cell range to include in the table and specify whether your table contains headers.

Invoke the Create Table dialog

When a user selects a table cell, the Table Tools contextual tab appears. Tab items allow users to specify the table name, convert the table to a regular cell range, and apply a table style.

The Table Tools contextual tab

Access Tables

Use the Worksheet.Tables property to access the collection of tables in a worksheet. You can obtain an individual table from the collection by its index.

using DevExpress.Spreadsheet;
// ...

IWorkbook workbook = spreadsheetControl1.Document;

// Access the table collection.
TableCollection tables = workbook.Worksheets[0].Tables;

// Access the first table in the collection.
Table table1 = tables[0];

You can also use the TableCollection.GetTables method to return tables that occupy or intersect a specific cell range.

The following example returns tables for the “B2:F7” cell range:

using DevExpress.Spreadsheet;
// ...

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

// Access the table collection.
TableCollection tables = worksheet.Tables;

// Return tables contained in the "B2:F7" cell range.
CellRange range = worksheet.Range["B2:F7"];
IList<Table> tablesFromRange = tables.GetTables(range);

Create a Table

Use the TableCollection.Add method to add a new table to a worksheet. Pass a cell range that you wish to format as a table and use the hasHeaders parameter to specify whether the top row of this range contains the table header.

The example below creates a simple table with sales data. The table contains an empty column that is used later to calculate the amount for each product.

Create a table

View Example: Create and Format a Spreadsheet Table

using DevExpress.Spreadsheet;
// ...

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

// Specify a cell range that contains table data.
CellRange range = worksheet["B2:F6"];

// Insert a table with the default headers.
Table table = worksheet.Tables.Add(range, false);

Specify a Header Row

To specify column headers, access each column by its index in the table’s column collection (Table.Columns) and set the column’s TableColumn.Name property. If you want to hide the header row, disable the Table.ShowHeaders property.

The following code snippet specifies column names for the table created in the previous section:

Specify table column names

using DevExpress.Spreadsheet;
// ...

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

// Access a table. 
Table table = worksheet.Tables[0];

// Obtain table columns and specify their names.
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";

Create a Calculated Column

Assign a formula to the TableColumn.Formula property to create a calculated column. This formula applies to all cells in the column. When you enter the formula, you can refer to table columns by their names.

The following code snippet specifies a formula for the “Amount” table column:

Create a calculated column

using DevExpress.Spreadsheet;
// ...

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

// Access a table. 
Table table = worksheet.Tables[0];
// Return the "Amount" table column.
TableColumn amountColumn = table.Columns[4];

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

Add a Total Row

Use the following properties to display a total row for a table and calculate totals for table columns:

Property Description
Table.ShowTotals Specifies whether the table total row is visible.
TableColumn.TotalRowFunction Specifies a predefined function to calculate the column total.
TableColumn.TotalRowFormula Specifies a formula to calculate the column total.
TableColumn.TotalRowArrayFormula Specifies an array formula to calculate the column total.
TableColumn.TotalRowLabel Specifies text displayed in the total cell of the table column.

The code snippet below displays a total row for a table and calculates the total amount for the “Amount” column.

Specify the Table Total Row

using DevExpress.Spreadsheet;
// ...

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

// Access a table. 
Table table = worksheet.Tables[0];
// Return the "Discount" column.
TableColumn discountColumn = table.Columns[3];
// Return the "Amount" column.
TableColumn amountColumn = table.Columns[4];

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

// Use the SUM function to calculate the total value for the "Amount" column.
discountColumn.TotalRowLabel = "Total";
amountColumn.TotalRowFunction = TotalRowFunction.Sum;

Access Table Ranges

You can use the following properties to obtain cell ranges that contain different parts of a table:

Property Description
Table.Range Specifies the cell range that contains the entire table.
Table.DataRange Returns the cell range that contains table data.
Table.HeaderRowRange Returns the cell range that contains the header row of the table.
Table.TotalRowRange Returns the cell range that contains the total row of the table.
TableColumn.Range Returns the cell range that contains the entire table column.
TableColumn.DataRange Returns the cell range that contains column data (without the column header and total cell).
TableColumn.Total Returns the cell that contains the column total.

The code snippet below returns specific table ranges and formats these ranges as follows:

  • Displays numbers as currency values in the “Price” and “Amount” columns
  • Formats values in the “Discount” column as percentages
  • Aligns table data horizontally
  • Specifies the width of table columns

Access table ranges

using DevExpress.Spreadsheet;
// ...

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

// Access a table. 
Table table = worksheet.Tables[0];
// Return the "Price" column.
TableColumn priceColumn = table.Columns[1];
// Return the "Discount" column.
TableColumn discountColumn = table.Columns[3];
// Return the "Amount" column.
TableColumn amountColumn = table.Columns[4];

// 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 the header and total rows.
table.HeaderRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
table.TotalRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;

// Specify horizontal alignment 
// for all columns except the first column.
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;

Manage Table Styles

You can use table styles to change the appearance of worksheet tables. A table style is a set of format settings that apply to an entire table. Use the IWorkbook.TableStyles property to access the workbook’s collection of table styles (TableStyleCollection). This collection contains built-in styles similar to Microsoft® Excel® and the None style that applies no formatting to a table. You cannot modify or delete built-in styles. However, you can create a custom style or duplicate an existing style and modify the created copy.

The following API allows you to manage the table style collection:

Member Description
TableStyleCollection.DefaultStyle Specifies the default table style.
TableStyleCollection.Item[BuiltInTableStyleId] Returns the built-in table style by its identifier.
TableStyleCollection.Item[String] Returns the table style by its name.
TableStyleCollection.Add Creates a new table style with the specified name and appends it to the style collection.
TableStyle.Duplicate Creates a copy of the table style.
TableStyleCollection.Contains Indicates whether the collection contains the table style with the specified name.
TableStyleCollection.Remove Removes the table style with the specified name from the collection.

Apply a Built-In Style to a Table

To apply a built-in style to a table, use the style’s identifier (BuiltInTableStyleId) to return the corresponding TableStyle object from the table style collection and assign this object to the Table.Style property.

Use the following properties of the Table object to specify table style options:

Property Description
Table.ShowTableStyleRowStripes Enables alternate shading for table rows.
Table.ShowTableStyleColumnStripes Enables alternate shading for table columns.
Table.ShowTableStyleFirstColumn Formats the first column in the table.
Table.ShowTableStyleLastColumn Formats the last column in the table.

The example below applies the TableStyleDark9 style to a table.

Apply a built-in table style

using DevExpress.Spreadsheet;
// ...

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

// Access a table. 
Table table = worksheet.Tables[0];

// Access the workbook's collection of table styles.
TableStyleCollection tableStyles = workbook.TableStyles;

// Access the built-in table style by its Id.
TableStyle tableStyle = tableStyles[BuiltInTableStyleId.TableStyleDark9];

// Apply the style to the table.
table.Style = tableStyle;

// Enable banded column formatting for the table.
table.ShowTableStyleRowStripes = false;
table.ShowTableStyleColumnStripes = true;

Create a Custom Table Style

Call the TableStyleCollection.Add method to add a new table style with the specified name to the IWorkbook.TableStyles collection. All table styles include a collection of table style elements (TableStyle.TableStyleElements). You can access an individual table style element (TableStyleElement) from the collection by its TableStyleElementType type.

Each table style element allows you to specify the following format settings:

Member Description
TableStyleElement.Borders Specifies borders for the table element cells.
TableStyleElement.Fill Specifies fill options for the table element cells.
TableStyleElement.Font Specifies font attributes for the table element cells.
TableStyleElement.StripeSize Specifies the number of table rows or columns displayed as odd and even stripes in the table. This property applies only to these table style elements: FirstRowStripe, SecondRowStripe, FirstColumnStripe, and SecondColumnStripe.
TableStyleElement.Clear Clears formatting for the table style element.

Modify table style elements within the TableStyle.BeginUpdate and TableStyle.EndUpdate method calls.

The code snippet below demonstrates how to create a custom table style.

Apply a custom table style

using DevExpress.Spreadsheet;
// ...

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

// Access a table. 
Table table = worksheet.Tables[0];

String styleName = "testTableStyle";

// If a style with the specified name exists in the collection,
// apply this style to the table.
if (workbook.TableStyles.Contains(styleName)) {
        table.Style = workbook.TableStyles[styleName];
}
else {
    // Add a new style under the "testTableStyle" name to the table style collection.
    TableStyle customTableStyle = workbook.TableStyles.Add("testTableStyle");

    // Modify table style formatting. 
    // Specify format characteristics for different table elements.
    customTableStyle.BeginUpdate();
    try {
        customTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Font.Color = 
            Color.FromArgb(107, 107, 107);

        // Format the header row. 
        TableStyleElement headerRowStyle = 
            customTableStyle.TableStyleElements[TableStyleElementType.HeaderRow];
        headerRowStyle.Fill.BackgroundColor = Color.FromArgb(64, 66, 166);
        headerRowStyle.Font.Color = Color.White;
        headerRowStyle.Font.Bold = true;

        // Format the total row. 
        TableStyleElement totalRowStyle = 
            customTableStyle.TableStyleElements[TableStyleElementType.TotalRow];
        totalRowStyle.Fill.BackgroundColor = Color.FromArgb(115, 193, 211);
        totalRowStyle.Font.Color = Color.White;
        totalRowStyle.Font.Bold = true;

        // Specify banded row formatting for the table.
        TableStyleElement secondRowStripeStyle = 
            customTableStyle.TableStyleElements[TableStyleElementType.SecondRowStripe];
        secondRowStripeStyle.Fill.BackgroundColor = Color.FromArgb(234, 234, 234);
        secondRowStripeStyle.StripeSize = 1;
    }
    finally {
        customTableStyle.EndUpdate();
    }
    // Apply the custom style to the table.
    table.Style = customTableStyle;
}