Table of Contents

Using List<T> as datasource (Delphi)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\20.Reports\22.Reports From Lists and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​VCL-​demos/​tree/​master/​Delphi/​Modules/​20.​Reports/​22.​Reports From Lists

Overview

Most of the demos here use datasets as datasources. This is just for convenience, so we share the same data layer in all demos, and also because the focus is in the Excel templates, not so much in the data layer. But you can use any TList<T> and TArray<T> as a datasource in a FlexCel report, and this is what we will show here.

Concepts

  • How to run a report from a TList<> of objects.

  • When using TList<T> and TArray<T> as datasource, you can use any public property of T in the report, any public field and any public function which has no parameters. So if type T has a public property "LastName", you can access it with <#dt.LastName>.

  • Master detail with implicit relationships. When a public property of a collection of objects is other collection of objects, the property is considered as a detail of the main collection. In this example "Elements" is a property of "Categories", and so there is an implicit relationship between them.

  • Master detail with explicit relationships. While when using TLists you will normally use implicit relationships, you can also relate any two collections of objects with a relationship, as you could with datasets. In this example, "ElementName" is explicitly related to "Elements" by calling TFlexCelReport.AddRelationship.

Files

DataModel.pas

unit DataModel;

interface
uses Generics.Collections;

type
  //We can use classes or records as data sources
  TElements = class
  private
    FElementId: integer;
    FName: string;
  public
    constructor Create(const aElementId: integer; const aName: string);

    //We will relate this property with the table of colors by adding a relationship.
    property ElementId: integer read FElementId;
    property Name: string read FName;

  end;

  TCategories = class
  private
    FName: string;
    FElements: TObjectList<TElements>;
  public
    constructor Create(const aName: string);
    destructor Destroy; override;

    //In classes we can use public properties, fields and functions without parameters.
    //In records we can't use public properties. In records we can use fields and functions only in XE2 or newer.
    property Name: string read FName;

    //Elements is in master-detail relationship with this element, even when we don't explicitly add a relationship.
    //Relationship is inferred because Elements is a property of this object
    property Elements: TObjectList<TElements> read FElements;
  end;

  //Here we will show a record instead of a class. A record can be used easier in Arrays
  //since it doesn't need free. But due to limitations in Delphi RTTI, we can only access the
  //fields of the method or functions (functions in Delphi XE2 or newer). Not properties.
  TElementName = record
  public
    ElementId: integer;
    Name: string;
    constructor Create(const aElementId: integer; const aName: string);

  end;



implementation

{ TCategories }

constructor TCategories.Create(const aName: string);
begin
  FName := aName;
  FElements := TObjectList<TElements>.Create;
end;

destructor TCategories.Destroy;
begin
  Elements.Free;
  inherited;
end;

{ TElementName }

constructor TElementName.Create(const aElementId: integer; const aName: string);
begin
  ElementId := aElementId;
  Name := aName;
end;

{ TElements }

constructor TElements.Create(const aElementId: integer; const aName: string);
begin
  FElementId := aElementId;
  FName := aName;
end;

end.

UMainForm.pas

unit UMainForm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics,
  FlexCel.VCLSupport, FlexCel.Core, FlexCel.XlsAdapter, FlexCel.Report, FlexCel.Render,
  {$if CompilerVersion >= 23.0} System.UITypes, {$IFEND}
  ShellApi, Generics.Collections, DataModel,
  Controls, Forms, Dialogs, StdCtrls, ExtCtrls;

type
  TMainForm = class(TForm)
    btnCancel: TButton;
    btnGo: TButton;
    SaveDialog: TSaveDialog;
    Label1: TLabel;
    procedure btnCancelClick(Sender: TObject);
    procedure btnGoClick(Sender: TObject);
  private
    procedure RunReport;
    function GetDataPath: string;
    procedure LoadCategories(const Categories: TObjectList<TCategories>);
    function GetElementNames: TArray<TElementName>;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation
uses IOUtils;

{$R *.dfm}

procedure TMainForm.btnCancelClick(Sender: TObject);
begin
  Close;
end;

procedure TMainForm.btnGoClick(Sender: TObject);
begin
  RunReport;

end;

function TMainForm.GetDataPath: string;
begin
  Result := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), '..\..');
end;


procedure TMainForm.RunReport;
var
  Report: TFlexCelReport;
  Categories: TObjectList<TCategories>;
begin
  if not SaveDialog.Execute then exit;

  Report := TFlexCelReport.Create(true);
  try
    Categories := TObjectList<TCategories>.Create;
    try
      LoadCategories(Categories);
      Report.AddTable<TCategories>('Categories', Categories, TDisposeMode.DoNotDispose);
      //We don't need to call AddTable for elements since it is already added when we add Categories.

      Report.AddTable<TElementName>('ElementName', GetElementNames);
      //ElementName doesn't have an intrinsic relationship with categories, so we will have to manually add a relationship.
      //Non intrinsic relationships should be rare, but we do it here to show how it can be done.
      Report.AddRelationship('Elements', 'ElementName', 'ElementID', 'ElementID');

      Report.Run(
        TPath.Combine(GetDataPath, 'Reports From Lists.template.xls'),
        SaveDialog.FileName);
    finally
      Categories.Free;
    end;
  finally
    Report.Free;
  end;

  if MessageDlg('Do you want to open the generated file?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
  begin
    ShellExecute(0, 'open', PCHAR(SaveDialog.FileName), nil, nil, SW_SHOWNORMAL);
  end;


end;

procedure TMainForm.LoadCategories(const Categories: TObjectList<TCategories>);
begin
  Categories.Add(TCategories.Create('Animals'));
  Categories[Categories.Count - 1].Elements.Add(TElements.Create(1, 'Penguin'));
  Categories[Categories.Count - 1].Elements.Add(TElements.Create(2, 'Cat'));
  Categories[Categories.Count - 1].Elements.Add(TElements.Create(3, 'Unicorn'));

  Categories.Add(TCategories.Create('Flowers'));
  Categories[Categories.Count - 1].Elements.Add(TElements.Create(4, 'Daisy'));
  Categories[Categories.Count - 1].Elements.Add(TElements.Create(5, 'Rose'));
  Categories[Categories.Count - 1].Elements.Add(TElements.Create(6, 'Orchid'));
end;

function TMainForm.GetElementNames: TArray<TElementName>;
begin
  SetLength(Result, 7);
  Result[0] := TElementName.Create(1, 'Linus');
  Result[1] := TElementName.Create(1, 'Gerard');
  Result[2] := TElementName.Create(2, 'Rover');
  Result[3] := TElementName.Create(3, 'Mike');
  Result[4] := TElementName.Create(5, 'Rosalyn');
  Result[5] := TElementName.Create(5, 'Monica');
  Result[6] := TElementName.Create(6, 'Lisa');
end;


end.