Range reports (Delphi)

Note

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

Overview

On the GettingStarted demo we saw how to write tags on a template, and have FlexCel replace them. Now we are going to replace whole ranges with values from a database.

Concepts

• To be able to use a database, you have to define a named range on the template, to tell FlexCel which rows or columns to use. Also, you need to add the table on the code.

• You can arrange the ranges in many different ways. Take a look at all the sheets, they have different reports from the Northwind demo recreated with FlexCel.

• There are four kinds of named ranges:

  • "__" Ranges: They are the most common, and the ones you should probably use. The table will be inserted horizontally, and the full rows will be moved down.

  • "_" Ranges: They work as the "__" ranges, but only the range and not the full row is moved down. Row properties (like row height) are not copied because not the full row is copied.

  • "II_" Ranges: Use them to insert the table by columns, not by rows. Full columns will be inserted.

  • "I_" Ranges: Tables will be inserted by columns, but only the selected range will move. Column properties (like column width) are not copied.

• Ranges can be defined inside ranges, to create Master-Detail reports. On the dataset schema on your application, you have to define the corresponding relations for this to work.

• It might happen that you need to use the same datatable, lets say __Categories__ on more than one sheet. You can't define more than one __Categories__ named range, but you can define an alias on the Config sheet, (for example Categories2=Categories) and then use __Categories2__ on the second sheet. Another alternative not shown in this example would be to use Alias Bands.

• We used Intelligent Page Breaks in the first sheet so categories are kept in their own pages when printing.

Files

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,
  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, DemoData;

{$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
    Report.AddTable(DemoTables);
    Report.SetValue('Date', Now);
    Report.Run(
      TPath.Combine(GetDataPath, 'Range Reports.template.xls'),
      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.