Using on-the-fly import

Use this approach for on-the-fly import. See code comments for more details.

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

Not all properties of the document can be loaded during the on-the-fly import. You won't be able to get merged cells, named cells, etc. But formatting will be imported.
Use on-the-fly import only if you need to get raw data from the document.

PAS Code:
unit UnitOTFI;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids,
  OExport, OExport_Vcl, OExport_VclForms, Vcl.ComCtrls, Vcl.ExtCtrls;

type
  TFormOTFImport = class(TForm)
    Pnl1: TPanel;
    BtnImport: TButton;
    PCImport: TPageControl;
    procedure BtnImportClick(Sender: TObject);
  private
    procedure RunOnTheFlyImport;//CALL THIS PROCEDURE TO EXECUTE THE IMPORT

    procedure OTFImport_AddRow(aExport: TOExport;
      aWorkSheet: TExportWorkSheet; aRow: TExportRow;
      var outCancelLoading: Boolean);
    procedure OTFImport_AddWorkSheet(aExport: TOExport;
      aWorkSheet: TExportWorkSheet; var aParseWorksheet: Boolean);
  end;

var
  FormOTFImport: TFormOTFImport;

implementation

uses
  Math;

{$R *.dfm}

{ TFormOTFImport }

procedure TFormOTFImport.BtnImportClick(Sender: TObject);
begin
  RunOnTheFlyImport;
end;

procedure TFormOTFImport.OTFImport_AddRow(aExport: TOExport;
  aWorkSheet: TExportWorkSheet; aRow: TExportRow; var outCancelLoading: Boolean);
var
  J: Integer;
  xGrid: TStringGrid;
begin
  //GET THE GRID OBJECT
  xGrid := TStringGrid(aWorkSheet.OnTheFlyParam);

  //UPDATE ROW AND COLUMN COUNTS
  xGrid.RowCount := Max(xGrid.RowCount, aRow.IndexInList+1);
  xGrid.ColCount := Max(xGrid.ColCount, aRow.Cells.Count);

  //FETCH CELLS, SAVE THEM INTO THE GRID
  for J := 0 to aRow.Cells.Count-1 do begin
    if aRow.Cells[J] is TExportCellEmpty then
      xGrid.Cells[J, aRow.IndexInList] := '(null)'
    else
      xGrid.Cells[J, aRow.IndexInList] := aRow.Cells[J].SqlText;
  end;
end;

procedure TFormOTFImport.OTFImport_AddWorkSheet(aExport: TOExport;
  aWorkSheet: TExportWorkSheet; var aParseWorksheet: Boolean);
var
  xPage: TTabSheet;
  xGrid: TStringGrid;
begin
  //A NEW WORKSHEET IS BEING LOADED -> CREATE A TAB AND A GRID TO STORE THE DATA
  //Set aParseWorksheet to false if you don't want to parse this sheet
  xPage := TTabSheet.Create(PCImport);
  xPage.PageControl := PCImport;
  xPage.Caption := aWorkSheet.Title;
  xGrid := TStringGrid.Create(PCImport);
  xGrid.Parent := xPage;
  xGrid.Align := alClient;
  xGrid.Options := xGrid.Options + [goColSizing];
  xGrid.RowCount := 1;
  xGrid.ColCount := 1;
  aWorkSheet.OnTheFlyParam := xGrid;
end;

procedure TFormOTFImport.RunOnTheFlyImport;
var
  xExport: TOExport;
  I: Integer;
  xFN: String;
begin
  xExport := TOExport.Create;
  try
    //WE WANT TO USE ON-THE-FLY
    xExport.LoadOnTheFly := True;
    //DEFINE THE METHOD HANDLERS
    xExport.LoadOnTheFlyOnAddWorkSheet := OTFImport_AddWorkSheet;
    xExport.LoadOnTheFlyOnAddRow := OTFImport_AddRow;

    //CLEAR PAGE CONTROL
    for I := PCImport.PageCount-1 downto 0 do
      PCImport.Pages[I].Free;

    //IMPORT THE DOCUMENT!
    if xExport.LoadFromFileWithDialog2(xFN) then
    begin
      //UPDATE CAPTION
      Self.Caption := xFN;
    end;

  finally
    xExport.Free;
  end;
end;

end.
DFM Code:
object FormOTFImport: TFormOTFImport
  Left = 0
  Top = 0
  Caption = 'FormOTFImport'
  ClientHeight = 488
  ClientWidth = 691
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Pnl1: TPanel
    Left = 0
    Top = 0
    Width = 691
    Height = 41
    Align = alTop
    TabOrder = 0
    ExplicitLeft = 152
    ExplicitTop = 80
    ExplicitWidth = 185
    object BtnImport: TButton
      Left = 16
      Top = 8
      Width = 121
      Height = 25
      Caption = 'On-The-Fly Import'
      TabOrder = 0
      OnClick = BtnImportClick
    end
  end
  object PCImport: TPageControl
    Left = 0
    Top = 41
    Width = 691
    Height = 447
    Align = alClient
    TabOrder = 1
    ExplicitLeft = -161
    ExplicitTop = 65
    ExplicitWidth = 852
    ExplicitHeight = 406
  end
end
Categories