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);

See also