# Demo of the Subtotal function ?

Does anyone have a working demo on how to use the FlexCel SubTotal function ??

procedure Subtotal(const range: TXlsCellRange; const atChangeInColumn: Int32; const aggFunction: Int32; const subtotalColumns: Int32Array; const subtotalText: UTF16String; const grandtotalText: UTF16String); virtual; abstract;

I am using Delphi XE, Excel 2010.

Thanks!

-stein
Hi,
Subtotal is an internal function, it shouldn't be exposed. I will see to change it to non public. If you want to do something similar to the "subtotal" menu item in Excel, you can do it manually easily by adding "=Subtotal" formulas, bolding the text, and adding ranges.

I have an example of how to do this in FlexCel.NET and C#, which I will paste below. If this is what you want to do, I can convert it to Delphi code for you, but it isn't difficult since both FlexCels are very similar. Code is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FlexCel.Core;

namespace ConsoleApplication13
{
class Program
{

static void AddSubTotals(XlsFile xls, int StartRow, int AtChangeInColumn, int AggFunction, int[] SubTotalColumns)
{
object LastVal = xls.GetCellValue(StartRow, AtChangeInColumn);
int LastRow = StartRow;
int row = StartRow + 1;
while (true)
{
object val = xls.GetCellValue(row, AtChangeInColumn);
if (Object.Equals(val, LastVal))
{
row++;
continue;
}
xls.InsertAndCopyRange(new TXlsCellRange(row, 1, row, 1), row, 1, 1, TFlxInsertMode.ShiftRowDown, TRangeCopyMode.None);

for (int stCol = 0; stCol < SubTotalColumns.Length; stCol++)
{
SetCellValueStrong(xls, row, SubTotalColumns[stCol], new TFormula("=Subtotal(" + AggFunction.ToString() + ", " + StartCell.CellRef + ":" + EndCell.CellRef + ")"));
}
SetCellValueStrong(xls, row, AtChangeInColumn, xls.GetStringFromCell(row - 1, AtChangeInColumn) + " Total");
xls.SetRowOutlineLevel(LastRow, row - 1, 2);

row++;
LastRow = row;
LastVal = val;
if (val == null) break;
}

if (row > StartRow)
{
SetCellValueStrong(xls, row, AtChangeInColumn, "Grand Total");
for (int stCol = 0; stCol < SubTotalColumns.Length; stCol++)
{
SetCellValueStrong(xls, row, SubTotalColumns[stCol], new TFormula("=Subtotal(" + AggFunction.ToString() + ", " + StartCell.CellRef + ":" + EndCell.CellRef + ")"));
}

for (int r = StartRow; r < row; r++)
{
if (xls.GetRowOutlineLevel(r) < 1) xls.SetRowOutlineLevel(r, 1);
}
}
}

private static void SetCellValueStrong(XlsFile xls, int row, int col, object value)
{
TFlxFormat fmt = xls.GetFormat(xls.GetCellFormat(row, col));
fmt.Font.Style |= TFlxFontStyles.Bold;
}

static void Main(string[] args)
{
XlsFile xls = new XlsFile(@"r:\st.xlsx", true);
AddSubTotals(xls, 1, 1, 9, new int[] {2});
xls.Save(@"r:\st1.xlsx");
}
}
}

I guess I will be able to translate it to Delphi, but at the same time I would give big thumbs up for a Delphi version from you.

The SubTotal method will be included in future versions ?

-stein

No problem translating it to Delphi, I just asked because I wasn't sure this is what you were asking about. I will paste the delphi version (completely autogenerated :)  at the end of this post.

About including this natively in FlexCel, I am not 100% sure. It was the idea originally, and what the method would do would be basically the same as I am pasting here, but the method itself is kind of too limited. If you want exactly "Excel like subtotal", then it is ok, but if you want to allow customizations, for example maybe make the subtotal line italic not bold, or change the text "Subtotal" by something else, etc, the method grows in complexity. And it hasn't big priority, since after all it is doing all stuff that you can do directly. While in Excel it makes more sense because it is interactive, in FlexCel you are always programming, so you can program the subtotal as you want it, instead of needing to use a fixed and not flexible implementation. The only real use I see for a function like this is if you are migrating existing VBA code.

But well, yes, it will most likely added in the future, but not with high priority.

The converted code is below, hope that it helps. Just create a new console app and paste it:

program subtotal;

{\$APPTYPE CONSOLE}

{\$R *.res}

uses

procedure SetCellValueStrong(const xls: TXlsFile; const row: Int32; const col: Int32; const value: TCellValue);
var
fmt: TFlxFormat;
begin
fmt := xls.GetFormat(xls.GetCellFormat(row, col));
fmt.Font.Style:= fmt.Font.Style + [TFlxFontStyles.Bold];
end;

procedure AddSubTotals(const xls: TXlsFile; const StartRow: Int32; const AtChangeInColumn: Int32; const AggFunction: Int32; const SubTotalColumns: Int32Array);
var
LastVal: TCellValue;
LastRow: Int32;
row: Int32;
val: TCellValue;
stCol: Int32;
r: Int32;
begin
LastVal := xls.GetCellValue(StartRow, AtChangeInColumn);
LastRow := StartRow;
row := StartRow + 1;
while true do
begin
val := xls.GetCellValue(row, AtChangeInColumn);
if val = LastVal then
begin
Inc(row);
continue;
end;

xls.InsertAndCopyRange(TXlsCellRange.Create(row, 1, row, 1), row, 1, 1, TFlxInsertMode.ShiftRowDown, TRangeCopyMode.None);
for stCol := 0 to Length(SubTotalColumns) - 1 do
begin
EndCell := TCellAddress.Create(row - 1, SubTotalColumns[stCol]);
SetCellValueStrong(xls, row, SubTotalColumns[stCol], TFormula.Create(((((('=Subtotal(' + IntToStr(AggFunction)) + ', ') + StartCell.CellRef) + ':') + EndCell.CellRef) + ')'));
end;

SetCellValueStrong(xls, row, AtChangeInColumn, xls.GetStringFromCell(row - 1, AtChangeInColumn) + ' Total');
xls.SetRowOutlineLevel(LastRow, row - 1, 2);
Inc(row);
LastRow := row;
LastVal := val;
if val = TCellValue.Empty then
break;

end;
if row > StartRow then
begin
SetCellValueStrong(xls, row, AtChangeInColumn, 'Grand Total');
for stCol := 0 to Length(SubTotalColumns) - 1 do
begin
EndCell := TCellAddress.Create(row - 1, SubTotalColumns[stCol]);
SetCellValueStrong(xls, row, SubTotalColumns[stCol], TFormula.Create(((((('=Subtotal(' + IntToStr(AggFunction)) + ', ') + StartCell.CellRef) + ':') + EndCell.CellRef) + ')'));
end;

for r := StartRow to row - 1 do
begin
if xls.GetRowOutlineLevel(r) < 1 then
xls.SetRowOutlineLevel(r, 1);

end;

end;

end;

var
xls: TXlsFile;
begin
xls := TXlsFile.Create('r:\st.xlsx', true);
try