Splitting datasets (Delphi)

Note

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

Overview

It is a normal request when doing a report for printing, to be able to manipulate the page breaks so there are no "hanging" lines at the end or beginning of a page. Since Excel does not provide this functionality, it is impossible for us to do it. But, you can use the <#SPLIT> tag on the config sheet to for example fit 40 records on each page.

Concepts

• How to use the SPLIT tag to create a master-detail relationship of a dataset with itself, where each detail has a fixed number of records.

• The SPLIT tag is a little special in that it does not create a real dataset, but it filters the data on the fly instead. This has some restrictions, the most important being that the master dataset and the clhild must be in direct relationship one with the other. If MasterOrders and Orders are both master and detail, you can not have MasterOrders/Other/Orders ranges each one inside the other. You must have Other/MasterOrders/Orders or Orders/MasterOrders/Other. This restriction normally is harmless.

• It is not shown on this demo, but note that you could use a parameter on the "record count" parameter of the split tag. For example: <#Split(table;<#splitcount>)> and dinamically change the split range on the application before running the report.

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, DB, ADODB;

type
  TMainForm = class(TForm)
    btnCancel: TButton;
    btnGo: TButton;
    SaveDialog: TSaveDialog;
    Label1: TLabel;
    ADOConnection: TADOConnection;
    Customers: TADODataSet;
    Employees: TADODataSet;
    DsEmployees: TDataSource;
    Orders: TADODataSet;
    procedure btnCancelClick(Sender: TObject);
    procedure btnGoClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    procedure RunReport;
    function GetDataPath: string;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation
uses IOUtils;

{$R *.dfm}

function DBFile: string;
begin
  Result := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), '..\..\..\SharedData\Northwind.mdb');
end;

procedure TMainForm.FormCreate(Sender: TObject);
begin
  ADOConnection.ConnectionString := StringReplace(ADOConnection.ConnectionString, 'Northwind.mdb', DbFile, []);
end;

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('Employees', Employees);
    Report.AddTable('Orders', Orders);
    Report.AddTable('Customers', Customers);
    Report.Run(
      TPath.Combine(GetDataPath, 'Split Datasets.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.