Spreadsheet Tables
- 13 minutes to read
The Spreadsheet Document API 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.
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;
// ...
using (Workbook workbook = new Workbook())
{
// 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;
// ...
using (Workbook workbook = new Workbook())
{
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.
using DevExpress.Spreadsheet;
// ...
using (Workbook workbook = new Workbook())
{
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:
using DevExpress.Spreadsheet;
// ...
using (Workbook workbook = new Workbook())
{
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:
using DevExpress.Spreadsheet;
// ...
using (Workbook workbook = new Workbook())
{
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.
using DevExpress.Spreadsheet;
// ...
using (Workbook workbook = new Workbook())
{
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
using DevExpress.Spreadsheet;
// ...
using (Workbook workbook = new Workbook())
{
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 Workbook.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.
using DevExpress.Spreadsheet;
// ...
using (Workbook workbook = new Workbook())
{
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 Workbook.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.
using DevExpress.Spreadsheet;
// ...
using (Workbook workbook = new Workbook())
{
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;
}
}