Advanced Reports from Lists (Delphi)

Note

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

This demo shows some features not shown on the Reports From Lists example.

Concepts

• How to do a master-detail report when the details are nested many levels inside the master. In this case, the class Country has a People class, and the People class has a list of Language objects. If People was a TList<> inside Country and you wanted to use that list, you would just define a __People__ band (this is shown in the Reports From Lists example). But as the TList<> is inside People which in turn is inside Country, you need to define a __People.Language__ band.

• How to reference a table with dots using [square brackets]. If you write in a cell <#tablename.section.field> FlexCel will interpret this as table "tablename", field "section.field". The text up to the first dot is always the table, and the rest is the field. But sometimes you might want this to being interpreted as table "tablename.section", field "field". To do so, you need to write <#[tablename.section].field>. In this particular case, we have a table "People.Language" which we defined in the previous point. If we wrote in cell B1: "<#people.language.speakers.percent> FlexCel would interpret this is the table "people", not "people.language" which is what we need. To make FlexCel understand that we want a table "people.language" we use <#[people.language].speakers.percent>

Files

DataModel.pas

unit DataModel;

interface
uses Generics.Collections;

type

  TArea = class
  strict private
    FWater: Int32;
    FLand: Int32;
    function Get_Total: Int32;

  public
    constructor Create(const aWater: Int32; const aLand: Int32);
    property Total: Int32 read Get_Total;
    property Water: Int32 read FWater;
    property Land: Int32 read FLand;
  end;


  TGeography = class
  strict private
    FArea: TArea;
  public
    constructor Create(const aArea: TArea);
    destructor Destroy; override;
    property Area: TArea read FArea;
  end;

  TLanguageSpeakers = class
  strict private
    FAbsoluteNumber: Int32;
    FPercent: double;

  public
    constructor Create(const aAbsoluteNumber: Int32; const aPercent: double);
    property AbsoluteNumber: Int32 read FAbsoluteNumber;
    property Percent: double read FPercent;
  end;



  TLanguageName = class
  strict private
    FShortName: string;
    FLongName: string;
  public
    constructor Create(const aShortName: string; const aLongName: string);
    property ShortName: string read FShortName;
    property LongName: string read FLongName;
  end;

  TLanguage = class
  strict private
    FName: TLanguageName;
    FSpeakers: TLanguageSpeakers;

  public
    constructor Create(const aName: TLanguageName; const aSpeakers: TLanguageSpeakers);
    destructor Destroy; override;
    property Name: TLanguageName read FName;
    property Speakers: TLanguageSpeakers read FSpeakers;
  end;

  TPeople = class
  strict private
    FPopulation: Int32;
    FLanguage: TObjectList<TLanguage>;

  public
    constructor Create(const aPopulation: Int32);
    destructor Destroy; override;
    property Population: Int32 read FPopulation;
    property Language: TObjectList<TLanguage> read FLanguage;
  end;

  TCountry = class
  strict private
    FName: string;
    FPeople: TPeople;
    FGeography: TGeography;

  public
    constructor Create(const aName: string; const aPeople: TPeople; const aGeography: TGeography);
    destructor Destroy; override;
    property Name: string read FName;
    property People: TPeople read FPeople;
    property Geography: TGeography read FGeography;
  end;



implementation

{ TArea }

constructor TArea.Create(const aWater, aLand: Int32);
begin
  inherited Create;
  FWater := aWater;
  FLand := aLand;

end;

function TArea.Get_Total: Int32;
begin
  Result := Water + Land;
end;

{ TGeography }

constructor TGeography.Create(const aArea: TArea);
begin
  inherited Create;
  FArea := aArea;
end;

destructor TGeography.Destroy;
begin
  FArea.Free;
  inherited;
end;

{ TLanguageSpeakers }

constructor TLanguageSpeakers.Create(const aAbsoluteNumber: Int32;
  const aPercent: double);
begin
  inherited Create;
  FAbsoluteNumber := aabsoluteNumber;
  FPercent := aPercent / 100.0;
end;

{ TLanguageName }

constructor TLanguageName.Create(const aShortName, aLongName: string);
begin
  inherited Create;
  FShortName := aShortName;
  FLongName := aLongName;
end;

{ TLanguage }

constructor TLanguage.Create(const aName: TLanguageName;
  const aSpeakers: TLanguageSpeakers);
begin
  inherited Create;
  FName := aName;
  FSpeakers := aSpeakers;
end;

destructor TLanguage.Destroy;
begin
  FName.Free;
  FSpeakers.Free;

  inherited;
end;

{ TPeople }

constructor TPeople.Create(const aPopulation: Int32);
begin
  inherited Create;
  FPopulation := aPopulation;
  FLanguage := TObjectList<TLanguage>.Create;

end;

destructor TPeople.Destroy;
begin
  FLanguage.Free;
  inherited;
end;

{ TCountry }

constructor TCountry.Create(const aName: string; const aPeople: TPeople;
  const aGeography: TGeography);
begin
  FName := aName;
  FPeople := aPeople;
  FGeography := aGeography;
end;

destructor TCountry.Destroy;
begin
  FPeople.Free;
  FGeography.Free;

  inherited;
end;

end.

UDataReader.pas

unit UDataReader;

interface
uses FlexCel.Report, Generics.Collections, DataModel;

procedure LoadTables(const Report: TFlexCelReport);

implementation
procedure LoadTables(const Report: TFlexCelReport);
var
  Countries: TObjectList<TCountry>;
  country: TCountry;
begin
  Countries := TObjectList<TCountry>.Create;

  Countries.Add(TCountry.Create('China', TPeople.Create($5288AD5A), TGeography.Create(TArea.Create($420D6, $8E4F4A))));
  country := Countries[Countries.Count - 1];
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Md', 'Mandarin'), TLanguageSpeakers.Create(0, 66.2)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Yue', 'Yue'), TLanguageSpeakers.Create(0, 4.9)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Wu', 'Wu'), TLanguageSpeakers.Create(0, 6.1)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Mb', 'Minbei'), TLanguageSpeakers.Create(0, 6.2)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Mn', 'Minnan'), TLanguageSpeakers.Create(0, 5.2)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Xi', 'Xiang'), TLanguageSpeakers.Create(0, 3)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Gan', 'Gan'), TLanguageSpeakers.Create(0, 4)));

  Countries.Add(TCountry.Create('India', TPeople.Create($4D4C1DFA), TGeography.Create(TArea.Create($4CAD6, $2D5E09))));
  country := Countries[Countries.Count - 1];
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Hi', 'Hindi'), TLanguageSpeakers.Create(0, 43.6)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Bg', 'Bengali'), TLanguageSpeakers.Create(0, 8)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Ma', 'Marath'), TLanguageSpeakers.Create(0, 6.9)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Te', 'Telugu'), TLanguageSpeakers.Create(0, 6.7)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Ta', 'Tamil'), TLanguageSpeakers.Create(0, 5.7)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Gu', 'Gujarati'), TLanguageSpeakers.Create(0, 4.6)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Ur', 'Urdu'), TLanguageSpeakers.Create(0, 4.2)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Ka', 'Kannada'), TLanguageSpeakers.Create(0, 3.6)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Od', 'Odia'), TLanguageSpeakers.Create(0, 3.1)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Ma', 'Malayalam'), TLanguageSpeakers.Create(0, 2.9)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Pu', 'Punjabi'), TLanguageSpeakers.Create(0, 2.7)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('As', 'Assamese'), TLanguageSpeakers.Create(0, 1.3)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Mi', 'Maithili'), TLanguageSpeakers.Create(0, 1.1)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('O', 'Other'), TLanguageSpeakers.Create(0, 5.6)));

  Countries.Add(TCountry.Create('United States', TPeople.Create($13A00E11), TGeography.Create(TArea.Create($A7764, $8B94C9))));
  country := Countries[Countries.Count - 1];
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('En', 'English'), TLanguageSpeakers.Create(0, 78.2)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Sp', 'Spanish'), TLanguageSpeakers.Create(0, 13.4)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('Ch', 'Chinese'), TLanguageSpeakers.Create(0, 1.1)));
  country.People.Language.Add(TLanguage.Create(TLanguageName.Create('O', 'Other'), TLanguageSpeakers.Create(0, 7.3)));
  report.AddTable<TCountry>('country', Countries, TDisposeMode.DisposeAfterRun);


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;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation
uses IOUtils, UDataReader;

{$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;
begin
  if not SaveDialog.Execute then exit;

  Report := TFlexCelReport.Create(true);
  try
    LoadTables(Report);

    Report.Run(
      TPath.Combine(GetDataPath, 'Advanced Reports From Lists.template.xlsx'),
      SaveDialog.FileName);
  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;



end.