officefileapi-devexpress-dot-spreadsheet-a88f4bae.md
Lists functions to calculate the totals of table columns.
Namespace : DevExpress.Spreadsheet
Assembly : DevExpress.Spreadsheet.v25.2.Core.dll
NuGet Package : DevExpress.Spreadsheet.Core
public enum TotalRowFunction
Public Enum TotalRowFunction
| 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.
|
The following properties accept/return TotalRowFunction values:
The TotalRowFunction enumeration members are used by the TableColumn.TotalRowFunction property.
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;
See Also