Using on-the-fly export (2)

Use this approach for on-the-fly export if you do not know the row count at the beginning of the export. You also may continue exporting in a new sheet if you reached the maximum row count of the file format (e.g. XLS).
See code comments for more details.

Download the FREE package with a full delphi project with this code: go to download page.

PAS Code:
unit UnitOTFE;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls,
  OExport, OExport_Vcl, OExport_VclForms;

type
  TFormOTFExport = class(TForm)
    BtnOnTheFlyExport: TButton;
    LblInfo: TLabel;
    procedure BtnOnTheFlyExportClick(Sender: TObject);
  public
    procedure RunOnTheFlyExport;//CALL THIS PROCEDURE TO EXECUTE THE EXPORT
    procedure OnTheFlyExport_AddRow2(aExport: TOExport;
      aExporter: TOCustomExporter;
      aWorkSheet: TExportWorkSheet; aRowIndex: Integer);
  end;

  //JUST AN EXAMPLE -> YOU WILL PROBABLY USE A DATABASE QUERY HERE
  TMyCustomDBQuery = class(TObject)
  protected
    fRowCount: Integer;
    fColCount: Integer;
    fCurrentRow: Integer;
  public
    function GetCell(const aColumnIndex: Integer): String; virtual; abstract;
    function GetColHeader(const aColumnIndex: Integer): String;
    function FetchNextRow: Boolean;
    //property RowCount: Integer read fRowCount; //the row count is unknown !!!
    property ColCount: Integer read fColCount;
    property CurrentRow: Integer read fCurrentRow;
  public
    constructor Create;
  end;

  TDBQueryMultiplication = class(TMyCustomDBQuery)
  public
    function GetCell(const aColumnIndex: Integer): String; override;
  end;

  TDBQueryAddition = class(TMyCustomDBQuery)
  public
    function GetCell(const aColumnIndex: Integer): String; override;
  end;

var
  FormOTFExport: TFormOTFExport;

implementation

uses FormatSQL;

{$R *.dfm}

procedure TFormOTFExport.BtnOnTheFlyExportClick(Sender: TObject);
begin
  RunOnTheFlyExport;
end;

procedure TFormOTFExport.OnTheFlyExport_AddRow2(aExport: TOExport;
  aExporter: TOCustomExporter;
  aWorkSheet: TExportWorkSheet; aRowIndex: Integer);
var
  xQuery: TMyCustomDBQuery;
  I: Integer;
  xRow: TExportRow;
  xNewWorksheetTitle: String;
  xNewWorksheetTitleNumber: Integer;
begin
  //DID WE REACH THE END OF SHEET?
  if aRowIndex >= aExporter.MaxRowCount then begin
    //CONTINUE EXPORTING ON THE NEXT SHEET
    xNewWorksheetTitle := aWorkSheet.Title;
    xNewWorksheetTitleNumber := StrToIntDef(xNewWorksheetTitle[Length(xNewWorksheetTitle)], 1) + 1;
    if xNewWorksheetTitleNumber > 2 then
      Delete(xNewWorksheetTitle, Length(xNewWorksheetTitle)-1, 2);
    //ADD A NEW WORKSHEET JUST AFTER THE CURRENT WORKSHEET!
    with aExport.AddWorkSheet(xNewWorksheetTitle+' '+IntToStr(xNewWorksheetTitleNumber), aWorkSheet.IndexInList+1) do begin
      //DEFINE THE SAME ROW METHOD AND PARAM FOR THE NEW WORKSHEET
      SaveOnTheFlyOnAddRow2 := aWorkSheet.SaveOnTheFlyOnAddRow2;
      OnTheFlyParam := aWorkSheet.OnTheFlyParam;
    end;

    Exit;//EXIT !!!
  end;

  xQuery := TMyCustomDBQuery(aWorkSheet.OnTheFlyParam);
  if aRowIndex = 0 then begin
    //HEADER ROW

    //create a new row at the row index
    xRow := aWorkSheet.FindCreateRow(aRowIndex);

    //fill row with cells
    xRow.AddCellEmpty.SetBGColor(clNavy).SetFontColor(clWhite);
    for I := 0 to xQuery.ColCount-1 do
      xRow.AddCellString(xQuery.GetColHeader(I)).SetBGColor(clNavy).
        SetFontColor(clWhite);
  end else begin
    //GO TO NEXT DB ROW

    if not xQuery.FetchNextRow then
      Exit;//last row -> exit and finish the export

    //create a new row at the row index
    xRow := aWorkSheet.FindCreateRow(aRowIndex);

    //COL HEADER
    xRow.AddCellString(IntToStr(xQuery.CurrentRow+1)).SetBGColor(clNavy).
      SetFontColor(clWhite);
    //DUMP CELLS
    for I := 0 to xQuery.ColCount-1 do
      xRow.AddCellNumber(SqlStrToInt(xQuery.GetCell(I)), 0);
  end;
end;

procedure TFormOTFExport.RunOnTheFlyExport;
var
  xExport: TOExport;
  xQueryMultiplication: TDBQueryMultiplication;
  xQueryAddition: TDBQueryAddition;
begin
  xExport := TOExport.Create;
  //JUST AND EXAMPLE -> YOU WILL PROBABLY USE A DATABASE QUERY HERE
  xQueryMultiplication := TDBQueryMultiplication.Create;
  xQueryAddition := TDBQueryAddition.Create;
  try
    //WE WANT TO USE SAVE-ON-THE-FLY
    xExport.SaveOnTheFly := True;

    //NOW DEFINE WORKSHEETS THAT WILL BE EXPORTED
    with xExport.AddWorkSheet('Multiplication') do begin
      //DEFINE ROW METHOD FOR THIS WORKSHEET
      SaveOnTheFlyOnAddRow2 := OnTheFlyExport_AddRow2;
      OnTheFlyParam := xQueryMultiplication;
      //YOU MUST NOT DEFINE ROW COUNT !!!
    end;
    with xExport.AddWorkSheet('Addition') do begin
      //DEFINE ROW METHOD FOR THIS WORKSHEET
      SaveOnTheFlyOnAddRow2 := OnTheFlyExport_AddRow2;
      OnTheFlyParam := xQueryAddition;
      //YOU MUST NOT DEFINE ROW COUNT !!!
    end;

    //NOW GENERATE THE DOCUMENT
    xExport.SaveToFileWithDialog('on-the-fly-export', '', True);
  finally
    xExport.Free;
    xQueryMultiplication.Free;
    xQueryAddition.Free;
  end;
end;

{ TMyCustomDBQuery }

constructor TMyCustomDBQuery.Create;
begin
  fRowCount := 80*1000;//above the XLS limit!
  fColCount := 50;
  fCurrentRow := -1;
end;

function TMyCustomDBQuery.FetchNextRow: Boolean;
begin
  Inc(fCurrentRow);
  Result := fCurrentRow < fRowCount;
end;

function TMyCustomDBQuery.GetColHeader(const aColumnIndex: Integer): String;
begin
  Result := IntToStr(aColumnIndex+1)
end;

{ TDBQueryMultiplication }

function TDBQueryMultiplication.GetCell(const aColumnIndex: Integer): String;
begin
  Result := IntToStr((aColumnIndex+1)*(fCurrentRow+1));
end;

{ TDBQueryAddition }

function TDBQueryAddition.GetCell(const aColumnIndex: Integer): String;
begin
  Result := IntToStr((aColumnIndex+1)+(fCurrentRow+1));
end;

end.
DFM Code:
object FormOTFExport: TFormOTFExport
  Left = 0
  Top = 0
  Caption = 'FormOTFExport'
  ClientHeight = 348
  ClientWidth = 643
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object LblInfo: TLabel
    Left = 56
    Top = 32
    Width = 557
    Height = 26
    Caption =
      'Use this method if you don'#39't know the row count.'#13#10'You also may c' +
      'ontinue exporting in a new sheet if you reached the maximum row ' +
      'count of the file format (e.g. XLS).'
  end
  object BtnOnTheFlyExport: TButton
    Left = 56
    Top = 72
    Width = 145
    Height = 25
    Caption = 'On-The-Fly Export'
    TabOrder = 0
    OnClick = BtnOnTheFlyExportClick
  end
end
Categories