Demo of the Subtotal function ?

FLX TMS FlexCel for VCL & FMX
1

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
2
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.XlsAdapter;
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++)
                {
                    TCellAddress StartCell = new TCellAddress(LastRow, SubTotalColumns[stCol]);
                    TCellAddress EndCell = new TCellAddress(row - 1, SubTotalColumns[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++)
                {
                    TCellAddress StartCell = new TCellAddress(StartRow, SubTotalColumns[stCol]);
                    TCellAddress EndCell = new TCellAddress(row - 1, SubTotalColumns[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;
            xls.SetCellValue(row, col, value, xls.AddFormat(fmt));
        }

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

3

Thanks for fast reply!


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
4

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
  System.SysUtils, VCL.FlexCel.Core, FlexCel.XlsAdapter;


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];
  xls.SetCellValue(row, col, value, xls.AddFormat(fmt));
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;
  StartCell: TCellAddress;
  EndCell: TCellAddress;
  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
      StartCell := TCellAddress.Create(LastRow, SubTotalColumns[stCol]);
      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
      StartCell := TCellAddress.Create(StartRow, SubTotalColumns[stCol]);
      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
    AddSubTotals(xls, 1, 1, 9, Int32Array.Create(2));
    xls.Save('r:\st1.xlsx');
  finally
    xls.Free;
  end;
end.