officefileapi-devexpress-dot-spreadsheet-dot-tablecolumn-7226e173.md
Specifies a predefined function to calculate the column total.
Namespace : DevExpress.Spreadsheet
Assembly : DevExpress.Spreadsheet.v25.2.Core.dll
NuGet Package : DevExpress.Spreadsheet.Core
TotalRowFunction TotalRowFunction { get; set; }
Property TotalRowFunction As TotalRowFunction
| Type | Description |
|---|---|
| TotalRowFunction |
The TotalRowFunction enumeration member.
|
Available values:
| Name | Description |
|---|---|
| None |
Indicates that neither a predefined function (TableColumn.TotalRowFunction) nor a custom formula (TableColumn.TotalRowFormula or TableColumn.TotalRowArrayFormula) is used to calculate the total for the table column.
| | Sum |
Returns the sum of the numbers contained in the table column.
| | Min |
Returns the smallest number contained in the table column.
| | Max |
Returns the largest number contained in the table column.
| | Average |
Returns the average (arithmetic mean) of the numbers contained in the table column.
| | Count |
Returns the number of cells with non-empty values contained in the table column.
| | CountNums |
Returns the number of cells with numeric values contained in the table column.
| | StdDev |
Calculates the standard deviation based on the sample contained in the table column.
| | Var |
Calculates the variance of the sample contained in the table column.
| | Custom |
Indicates that a custom formula (TableColumn.TotalRowFormula or TableColumn.TotalRowArrayFormula) is used to calculate the total for the table column.
|
You can specify how to calculate a total for each column in the table. To do this, use the following properties.
TableColumn.TotalRowFunction - allows you to select one of the predefined functions to calculate the column total.Use the Table.ShowTotals property to control the visibility of the table total row.
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
Format the Table
Table Columns
Table Header Row
Calculated Column
Table Total Row
Total Row Function
Table Ranges and Data Ranges
The image below shows the results.
' 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
// 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;
The following code snippets (auto-collected from DevExpress Examples) contain references to the TotalRowFunction 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.
discountColumn.TotalRowLabel = "Total:"
amountColumn.TotalRowFunction = DevExpress.Spreadsheet.TotalRowFunction.Sum
' Specify the number format for each column.
spreadsheet-document-api-import-data-from-data-sources/VB/DataImportExample/Form1.vb#L128
discountColumn.TotalRowLabel = "Total:"
amountColumn.TotalRowFunction = TotalRowFunction.Sum
See Also