How to: Perform Calculations in a Table

• Apr 08, 2019
• 4 minutes to read

The example below demonstrates how to perform calculations on data in a table using column names. The initial table 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.

1. Access table columns by their indexes in the table column collection (TableColumnCollection), returned by the Table.Columns property.

2. Add a new column using the TableColumnCollection.Add method, and set its TableColumn.Name property to "Amount". Headers of other table columns are automatically set to the values of the corresponding cells.

   • table.Columns[0].Name = "Product"
   • table.Columns[1].Name = "Price"
   • table.Columns[2].Name = "Quantity"
   • table.Columns[3].Name = "Discount"
3. 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 names.
4. Set the Table.ShowTotals property to true to display the total row at the bottom of the table.

5. Specify the function to calculate the total amount. To do this, set the TableColumn.TotalRowFunction property of the "Amount" column to TotalRowFunction.Sum.

   Tip

   In the total row, you can use any formulas you wish, not only functions listed by the TotalRowFunction enumerator. To use a custom formula in the total row, assign it to the TableColumn.TotalRowFormula property of the required table column.

6. 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.

   Use the Table.HeaderRowRange and Table.TotalRowRange properties to access table header and total row ranges, and set the alignment.

   Change the width of table columns. To do this, access the table range via the Table.Range property, and use its Range.ColumnWidthInCharacters property.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/spreadsheet-document-server-api-part-2-t217615

TableActions.vb

Dim worksheet As Worksheet = workbook.Worksheets("TableRanges")
workbook.Worksheets.ActiveWorksheet = worksheet

' Access a table.
Dim table As Table = worksheet.Tables(0)

' Access table columns.
Dim productColumn As TableColumn = table.Columns(0)
Dim priceColumn As TableColumn = table.Columns(1)
Dim quantityColumn As TableColumn = table.Columns(2)
Dim discountColumn As TableColumn = table.Columns(3)

' Add a new column to the end of the table .
Dim amountColumn As TableColumn = table.Columns.Add()

' Set the name of the last column. 
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
worksheet.Visible = True

TableActions.cs

Worksheet worksheet = workbook.Worksheets["TableRanges"];
workbook.Worksheets.ActiveWorksheet = worksheet;

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

// Access table columns.
TableColumn productColumn = table.Columns[0];
TableColumn priceColumn = table.Columns[1];
TableColumn quantityColumn = table.Columns[2];
TableColumn discountColumn = table.Columns[3];

// Add a new column to the end of the table .
TableColumn amountColumn = table.Columns.Add();

// Set the name of the last column. 
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;
worksheet.Visible = true;

The image below illustrates the result (the workbook is opened in Microsoft® Excel®).