A table in a worksheet.
To manage data easier and more effective, you can convert an ordinary cell range to a table. To do this, call the TableCollection.Add method with the passed range of cells. After a table is created, you can utilize the following functionalities.
You can access and manage table columns specified by the TableColumn objects that are stored in the Table.Columns collection. The TableColumn.Formula and TableColumn.ArrayFormula properties allow you to create calculated columns.
You can automatically calculate data totals by columns (TableColumn.TotalRowFunction, TableColumn.TotalRowFormula, TableColumn.TotalRowArrayFormula, TableColumn.TotalRowLabel) and display them at the end of the table (Table.ShowTotals).
Quickly Format Tables
You can format tables using built-in or custom table styles. To apply a table style, access the desired TableStyle object from the IWorkbook.TableStyles collection and assign it to the Table.Style property.
A table style allows you to set specific formatting for the table’s header and total rows, odd and even row and column stripes, and first and last table columns. In turn, the Table object provides a set of properties to optionally specify which of these table elements should be specially formatted (Table.ShowHeaders and Table.ShowTotals, Table.ShowTableStyleRowStripes and Table.ShowTableStyleColumnStripes, Table.ShowTableStyleFirstColumn and Table.ShowTableStyleLastColumn).
When creating formulas to work with table data, you can refer to table data ranges by a table name (Table.Name) and table column names (TableColumn.Name, Table.ShowHeaders) instead of using A1 and R1C1 cell references.
For example, the =SUM(Products[Price]) formula calculates the sum of values contained in the Price column of the Products table.
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:
Create a Table
- 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.
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.
Access table columns by their indexes from the column collection that the Table.Columns property returns.
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.Name = “Product”
- table.Columns.Name = “Price”
- table.Columns.Name = “Quantity”
- table.Columns.Name = “Discount”
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.
Table Total Row
Set the Table.ShowTotals property to true, to display the total row at the bottom of the table.
Total Row Function
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.
The image below shows the results.
A complete sample project is available at https://github.com/DevExpress-Examples/winforms-spreadsheetcontrol-api-part-2-e4832
' 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