TExcelFile.Subtotal Method
This method works like the "Subtotal" command in Excel in the "Data" tab of the ribbon. It will take a number of cells and group them by similar values, adding a subtotal formula every new different value and a grand total at the end. It will also add outlines at the right.
Syntax
Unit: FlexCel.Core
procedure TExcelFile.Subtotal(range: TXlsCellRange; const atChangeInColumn: Integer; const aggFunction: Integer; const subtotalColumns: TArray<Int32>; const SubtotalText: TFunc<Integer, Integer, TCellValue, string, string>; const grandtotalText: string; const SubTotalRowFormat: TFunc<TFlxFormat, TCellValue, TFlxFormat>; const SubTotalCellTextFormat: TFunc<TFlxFormat, TCellValue, TFlxFormat>; const SubTotalCellFormulaFormat: TFunc<TFlxFormat, TCellValue, TFlxFormat>; const GrandTotalRowFormat: TFunc<TFlxFormat, TFlxFormat>; const GrandTotalCellTextFormat: TFunc<TFlxFormat, TFlxFormat>; const GrandTotalCellFormulaFormat: TFunc<TFlxFormat, TFlxFormat>); virtual; abstract;
Parameters
<-> | Parameter | Type | Description |
---|---|---|---|
range | TXlsCellRange | Range where to apply the subtotals. If null, the whole sheet will be used. | |
const | atChangeInColumn | Integer | Index of the column where we want to group in. Every time a value in this column changes from the previous row, a new subtotal will be added. |
const | aggFunction | Integer | Function that will be used in the =Subtotal(...) formula added. To do a sum, set this value to 9. For other values, see the Excel reference in the =Subtotal function. |
const | subtotalColumns | TArray<Int32> | We will add a subtotal formula in each one of the columns in this array. |
const | SubtotalText | TFunc<Integer, Integer, TCellValue, string, string> | In this function you need to return the text that will be written in every "Subtotal" line. The parameters to this function are the row, column, and value of the cell we are aggregating, as an object and as a string. If this function is null, "aggregatedcolumn Total" will be written. |
const | grandtotalText | string | Text for the grand total line. If null or empty, no grand total line will be added. Note: You can pass "Grand " + the result of SubtotalDefaultEnglishString here to get the standard English labels for the function ("Total", "Average", etc). |
const | SubTotalRowFormat | TFunc<TFlxFormat, TCellValue, TFlxFormat> | Row format for the subtotal rows added. You get a TFlxFormat and the cell value for the column you are aggregating, and you must return a changed one with the format you want. You might leave this function null, and no special format will be applied to the rows. |
const | SubTotalCellTextFormat | TFunc<TFlxFormat, TCellValue, TFlxFormat> | Cell format for the subtotal cells which contain text like "Food Subtotal". You get a TFlxFormat and the cell value for the column you are aggregating, and you must return a changed one with the format you want. You might leave this function null, and bold will be applied to all subtotal texts. |
const | SubTotalCellFormulaFormat | TFunc<TFlxFormat, TCellValue, TFlxFormat> | Cell format for the subtotal cells which contain formulas like =Subtotal(...). You get a TFlxFormat and the cell value for the column you are aggregating, and you must return a changed one with the format you want. You might leave this function null, and no special format will be applied to subtotal formulas. |
const | GrandTotalRowFormat | TFunc<TFlxFormat, TFlxFormat> | Row format for the grand total row added. You get a TFlxFormat, and you must return a changed one with the format you want. You might leave this function null, and no special format will be applied to the grand total row. |
const | GrandTotalCellTextFormat | TFunc<TFlxFormat, TFlxFormat> | Cell format for the grand total cells which contain text like "Food Total". You get a TFlxFormat, and you must return a changed one with the format you want. You might leave this function null, and bold will be applied to all grand total texts. |
const | GrandTotalCellFormulaFormat | TFunc<TFlxFormat, TFlxFormat> | Cell format for the grand total cells which contain formulas like =Subtotal(...). You get a TFlxFormat, and you must return a changed one with the format you want. You might leave this function null, and no special format will be applied to grand total formulas. |
Examples
The following example will calculate the Count (function 3) of column 2 at every change of column 1. It will write "Grand Count" in the big total, and the rest of parameters will be the default.
xls.Subtotal(TXlsCellRange.Null, 1, 3, Int32Array.Create(2), nil, 'Grand ' + xls.SubtotalDefaultEnglishString(3), nil, nil, nil, nil, nil, nil);
Below is a more complex example, which will calculate the Average (function 1) of columns 4 and 5 at every change of column 3. This method uses the callbacks to provide custom text for the subtotal lines and custom formats for all possible cases. This can be useful if default texts aren't enough or you want to use other language than English, but normally most callbacks will be null as the defaults should work in most cases. This example is just to show all the functionality in a single call.
xls.Subtotal(TXlsCellRange.Create(2, 1, xls.RowCount - 1, 2), 3, 1, Int32Array.Create(4, 5),
function (row: Int32; col: Int32; cellVal: TCellValue; cellValAsString: string): string
begin
exit('This is the average of ' + cellValAsString);
end, 'Super Average',
function (fmt: TFlxFormat; cellval: TCellValue): TFlxFormat
begin
fmt.Font.Name := 'Courier new';
fmt.Font.Scheme := TFontScheme.None;
fmt.FillPattern.Pattern := TFlxPatternStyle.Solid;
fmt.FillPattern.FgColor := Colors.MediumAquamarine;
exit(fmt);
end,
function (fmt: TFlxFormat; cellval: TCellValue): TFlxFormat
begin
fmt.Font.Style := [TFlxFontStyles.Bold];
fmt.FillPattern.Pattern := TFlxPatternStyle.Solid;
fmt.FillPattern.FgColor := Colors.MediumBlue;
exit(fmt);
end,
function (fmt: TFlxFormat; cellval: TCellValue): TFlxFormat
begin
fmt.Font.Style := [TFlxFontStyles.Italic];
fmt.FillPattern.Pattern := TFlxPatternStyle.Solid;
fmt.FillPattern.FgColor := Colors.MediumOrchid;
exit(fmt);
end,
function (fmt: TFlxFormat): TFlxFormat
begin
fmt.Font.Style := [TFlxFontStyles.StrikeOut];
fmt.FillPattern.Pattern := TFlxPatternStyle.Solid;
fmt.FillPattern.FgColor := Colors.MediumSeaGreen;
exit(fmt);
end,
function (fmt: TFlxFormat): TFlxFormat
begin
fmt.Font.Style := [TFlxFontStyles.StrikeOut];
fmt.FillPattern.Pattern := TFlxPatternStyle.Solid;
fmt.FillPattern.FgColor := Colors.MediumSpringGreen;
exit(fmt);
end,
function (fmt: TFlxFormat): TFlxFormat
begin
fmt.Font.Style := [TFlxFontStyles.StrikeOut];
fmt.FillPattern.Pattern := TFlxPatternStyle.Solid;
fmt.FillPattern.FgColor := Colors.MediumTurquoise;
exit(fmt);
end);