officefileapi-devexpress-dot-spreadsheet-dot-worksheet-dot-subtotal-x28-devexpress-dot-spreadsheet-dot-cellrange-system-dot-int32-system-dot-collections-dot-generic-dot-list-system-dot-int32-system-dot-int32-system-dot-string-x29.md
Creates subtotals for the specified range of cells.
Namespace : DevExpress.Spreadsheet
Assembly : DevExpress.Spreadsheet.v25.2.Core.dll
NuGet Package : DevExpress.Spreadsheet.Core
void Subtotal(
CellRange range,
int groupByColumn,
List<int> subtotalColumnList,
int functionCode,
string functionText
)
Sub Subtotal(
range As CellRange,
groupByColumn As Integer,
subtotalColumnList As List(Of Integer),
functionCode As Integer,
functionText As String
)
| Name | Type | Description |
|---|---|---|
| range | CellRange |
A CellRange object that is the range of cells for which the subtotals should be created.
| | groupByColumn | Int32 |
An integer that specifies the zero-based index of a column containing related data for grouping.
| | subtotalColumnList | List<Int32> |
A list of column indexes in a worksheet that defines columns for which the subtotals should be calculated.
| | functionCode | Int32 |
An integer that specifies the code of the function to be used in calculating subtotals.
| | functionText | String |
A string that defines the text to be displayed in the summary rows.
|
Before subtotaling your data, make sure that the range you wish to subtotal has columns headings and the column defined by the groupByColumn parameter contains repetitive values. Each time a value in this column changes, a new subtotal row is inserted, so that it is better to sort your data to ensure that the same values in the groupByColumn column will be in one group.
The table below provides a full list of functions that can be used in calculating subtotals.
|
functionCode
(includes hidden values)
|
functionCode
(ignores hidden values)
|
Function Name
|
fuctionText
(default)
| | --- | --- | --- | --- | |
1
|
101
|
AVERAGE
|
Average
| |
2
|
102
|
COUNT
|
Count
| |
3
|
103
|
COUNTA
|
Count
| |
4
|
104
|
MAX
|
Max
| |
5
|
105
|
MIN
|
Min
| |
6
|
106
|
PRODUCT
|
Product
| |
7
|
107
|
STDEV
|
StdDev
| |
8
|
108
|
STDEVP
|
StdDevp
| |
9
|
109
|
SUM
|
Total
| |
10
|
110
|
VAR
|
Var
| |
11
|
111
|
VARP
|
Varp
|
The example below demonstrates how to use the Worksheet.Subtotal method to automatically create outlines for a sorted range and summarize data in each group using the SUBTOTAL function.
Important
Before subtotaling, make sure that the range you wish to subtotal has column headings in the first row and the data in this range is sorted by a column at each change in which a subtotal row will be inserted.
To insert subtotals, do the following.
Worksheet.Subtotal method and pass the following parameters: the specified data range to be subtotaled, the index of the column by which you wish to group your data, the specified list of columns to which the subtotals should be added, the code of the function to be used in calculating subtotals and the text to be displayed in the summary rows.CellRange dataRange = worksheet["B3:E23"];
// Specify that subtotals should be calculated for the column "D".
List<int> subtotalColumnsList = new List<int>();
subtotalColumnsList.Add(3);
// Insert subtotals by each change in the column "B" and calculate the SUM fuction for the related rows in the column "D".
worksheet.Subtotal(dataRange, 1, subtotalColumnsList, 9, "Total");
Dim dataRange As CellRange = worksheet("B3:E23")
' Specify that subtotals should be calculated for the column "D".
Dim subtotalColumnsList As New List(Of Integer)()
subtotalColumnsList.Add(3)
' Insert subtotals by each change in the column "B" and calculate the SUM fuction for the related rows in the column "D".
worksheet.Subtotal(dataRange, 1, subtotalColumnsList, 9, "Total")
The following code snippets (auto-collected from DevExpress Examples) contain references to the Subtotal(CellRange, Int32, List<Int32>, Int32, String) method.
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.
// Insert subtotals by each change in the column "B" and calculate the SUM fuction for the related rows in the column "D".
worksheet.Subtotal(dataRange, 1, subtotalColumnsList, 9, "Total");
#endregion #Subtotal
// Insert subtotals by each change in the column "B" and calculate the SUM fuction for the related rows in the column "D".
worksheet.Subtotal(dataRange, 1, subtotalColumnsList, 9, "Total");
#endregion #Subtotal
// and calculate the SUM fuction for the related rows in column "D".
worksheet.Subtotal(dataRange, 1, subtotalColumnsList, 9, "Total");
#endregion #Subtotal
' Insert subtotals by each change in the column "B" and calculate the SUM fuction for the related rows in the column "D".
worksheet.Subtotal(dataRange, 1, subtotalColumnsList, 9, "Total")
' #End Region ' #Subtotal
' Insert subtotals by each change in the column "B" and calculate the SUM fuction for the related rows in the column "D".
worksheet.Subtotal(dataRange, 1, subtotalColumnsList, 9, "Total")
' #End Region ' #Subtotal
' and calculate the SUM fuction for the related rows in column "D".
worksheet.Subtotal(dataRange, 1, subtotalColumnsList, 9, "Total")
' #End Region ' #Subtotal
See Also