Chip 2002 October/Chip_2002-10_cd1.bin/zkuste/delphi/kolekce/d56/FLEXCEL.ZIP/FlexCel/UFlexcelReport.pas

Pascal/Delphi Source File | 2002-07-03 | 30KB | 921 lines

unit UFlexcelReport;
{******************************************************************
 Component to automate reporting to Excel
 Version 2.0

// The contents of this file are subject to the Mozilla Public License
// Version 1.1 (the "License"); you may not use this file except in compliance
// with the License. You may obtain a copy of the License at http://www.mozilla.org/MPL/
//
// Software distributed under the License is distributed on an "AS IS" basis,
// WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for the
// specific language governing rights and limitations under the License.
//
// The original code is Excel.pas, released January 23, 2002.
//
// The initial developer of the original code is Adrian Gallero,
// written by Adrian Gallero ([e-mail]).
//
// Portions created by Adrian Gallero are Copyright
// (C) 2002-2002 Adrian Gallero. All Rights Reserved.

 Send any comments to [e-mail]
******************************************************************** }
interface
{$R IFlexcel.RES}
uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  contnrs, db,
  {$IFDEF ConditionalExpressions}{$if CompilerVersion >= 14} variants,{$IFEND}{$ENDIF} //Delphi 6 or above
  typinfo, UExcelAdapter, UFlxMessages, UFlxFullDataSets;

type
  TCalcRecordCount=(cr_None, cr_Count, cr_SlowCount);
  TRecordcountEvent = procedure (Sender: TObject; const DataSet: TDataSet; var RecordCount: integer) of object;
  TOnGenerateEvent = procedure (Sender: TObject; const ExcelApp: TExcelFile) of object;
  TOnGeneratePageEvent = procedure (Sender: TObject; const ExcelApp: TExcelFile) of object;
  TOnGetCellValue = procedure (Sender: TObject; const FieldName: widestring; var FieldValue: variant) of object;

  TExcelBandList= class;

  TFlxValueType=(flv_Value, flv_Field, flv_Property, flv_FullDataSet);

  TOneBandValue= record
    //It's not a variant record because it has variant and pointer values
    ColOffset: integer;
    CellValue: Widestring;

    ValueType: TFlxValueType;
    Value: TXlsCellValue;
    Field: TField;
    PropInfo: PPropInfo;
    PropIndex: string;
    VarName: string;
  end;

  TBandValue= array of TOneBandValue;  // it can have many values in one cell.
  TBandValueList=array of array of TBandValue;

  TExcelBand= class
    StartRow, EndRow: integer;
    RangePos: integer;
    SubBands: TExcelBandList;
    DataSet: TDataSet;
    constructor Create;
    destructor Destroy;override;
  end;

  TExcelBandList= class (TObjectList) //Items are TExcelBand
  {$Include inc\TExcelBandListHdr.inc}
  end;

  TExcelDbList=class(TList) //Items are TDataSet
  {$Include inc\TExcelDbListHdr.inc}
    procedure Check(const DataSet: TDataSet);
    destructor Destroy; override;
  end;

  TFlexCelReport = class(TComponent)
  private
    Workbook: TExcelFile;
    FullDataSets: TFullDataSetList;
    LazyDbList: TExcelDbList;
    ValueCache: TBandValueList;

    FTemplate: TFileName;
    FDataModule: TComponent;
    FAdapter: TExcelAdapter;

    FAutoClose: boolean;
    FFileName: TFileName;
    FCalcRecordCount: TCalcRecordCount;
    FPagesDataSet: TDataSet;
    FPagesDataField: string;

    FOnRecordCount: TRecordCountEvent;
    FOnBeforeGenerateWorkbook: TOnGenerateEvent;
    FOnAfterGenerateWorkbook: TOnGenerateEvent;
    FOnAfterGeneratePage: TOnGeneratePageEvent;
    FOnBeforeGeneratePage: TOnGeneratePageEvent;
    FOnGetFileName: TOnGetFilenameEvent;
    FOnGetCellValue: TOnGetCellValue;

    procedure SetTemplate(const Value: TFileName);
    procedure SetPagesDataSet(const Value: TDataSet);
    procedure SetPagesDataField(const Value: string);
    procedure SetOnRecordCount(const Value: TRecordCountEvent);
    procedure SetDataModule(const Value: TComponent);
    procedure SetAdapter(const Value: TExcelAdapter);

    procedure InsertSheets(const FirstPage: integer);
    function RecordCount(const DbSet: TDataSet): integer;
    function ExportFieldData: boolean;
    procedure ReadTemplate(var MainBand: TExcelBand);
    function FindBands(const StartRow, EndRow: integer): TExcelBand;
    function IsDBName(s: widestring): boolean;
    function GetDataSetFromName(s: WideString): WideString;
    function FindSubBands(const StartRow, EndRow: integer): TExcelBandList;
    procedure CopyStructure(const Band: TExcelBand; var RowOffset: integer);
    procedure FillBandData(const Band: TExcelBand; var RowOffset: integer; const MainBandRow: integer);
    function GetValue(const BandValue: TBandValue; const RowOffset, col: integer): variant;
    procedure ReplaceValues(const Band: TExcelBand;const Rof: integer; var RowOffset: integer);
    procedure ReplacePictures(var RowOffset: integer);
    procedure ReplaceComments(var RowOffset: integer);
    function GetPictureType(const s: widestring): TXlsImgTypes;
    procedure FillFullDatasets;
    function SupressCR(const s: Widestring): widestring;
    procedure GetOneDimArray(var v: variant; PropIndex: string; const VarName: string);
    procedure GetMultiDimArray(var v: variant; PropIndex: string; const VarName: string);

    procedure FillBandValueList(const RangePos: integer);
    function GetBandValue(const v: Variant;const ColOffset: integer; const XF: integer=-1): TBandValue;
    function GetOneBandValue(const v:Variant; const ColOffset: integer; const XF: integer): TOneBandValue;
    { Private declarations }

  protected
    procedure Notification(AComponent: TComponent; Operation: TOperation); override;
    { Protected declarations }

  public
    constructor Create(AOwner:TComponent);override;
    destructor Destroy;override;
    procedure Run;
    { Public declarations }

  published
    property Template: TFileName read FTemplate write SetTemplate;
    property DataModule: TComponent read FDataModule write SetDataModule;
    property FileName: TFileName read FFileName write FFileName;
    property AutoClose: boolean read FAutoClose write FAutoClose;

    property CalcRecordCount: TCalcRecordCount read FCalcRecordCount write FCalcRecordCount default cr_Count;

    property PagesDataSet: TDataSet read FPagesDataSet write SetPagesDataSet;
    property PagesDataField: string read FPagesDataField write SetPagesDataField;

    property Adapter: TExcelAdapter read FAdapter write SetAdapter;

    //Events
    property OnRecordCount: TRecordCountEvent read FOnRecordCount write SetOnRecordCount;
    property OnBeforeGenerateWorkbook: TOnGenerateEvent read FOnBeforeGenerateWorkbook write FOnBeforeGenerateWorkbook;
    property OnAfterGenerateWorkbook: TOnGenerateEvent read FOnAfterGenerateWorkbook write FOnAfterGenerateWorkbook;
    property OnBeforeGeneratePage: TOnGeneratePageEvent read FOnBeforeGeneratePage write FOnBeforeGeneratePage;
    property OnAfterGeneratePage: TOnGeneratePageEvent read FOnAfterGeneratePage write FOnAfterGeneratePage;

    property OnGetFilename: TOnGetFilenameEvent read FOnGetFileName write FOnGetFileName;

    property OnGetCellValue: TOnGetCellValue read FOnGetCellValue write FOnGetCellValue;
    { Published declarations }
  end;

procedure Register;

implementation
{$Include inc\TExcelBandListImp.inc}
{$Include inc\TExcelDbListImp.inc}


procedure Register;
begin
  RegisterComponents('FlexCel', [TFlexcelReport]);
end;


{ TFlexCelReport }

constructor TFlexCelReport.Create(AOwner: TComponent);
begin
  inherited;
  FDataModule:=AOwner;
  FCalcRecordCount:=cr_Count;
end;

destructor TFlexCelReport.Destroy;
begin
  inherited;
end;

procedure TFlexCelReport.Notification(AComponent: TComponent; Operation: TOperation);
begin
  inherited Notification(AComponent, Operation);
  if Operation = opRemove then
  begin
    if AComponent = FPagesDataSet then
        FPagesDataSet:= nil;
    if AComponent = FDataModule then
        FDataModule:= Owner;
    if AComponent = FAdapter then
        FAdapter:= nil;
  end;
end;

////////////////////////////////////////////////////////////////////////////////
function TFlexCelReport.RecordCount(const DbSet: TDataSet): integer;
begin
  LazyDbList.Check(DbSet);

  Result:=0;
  if DbSet<>nil then
  begin
    //Count the records
    DbSet.First;

    //If the event OnCountRecords is Assigned, the var FCalcRecordCount has no meaning
    if Assigned (FOnRecordCount) then FOnRecordCount(Self, DbSet, Result)
    else
      case FCalcRecordCount of
        cr_None:
          Result:= DbSet.RecordCount;
        cr_Count:
        begin
          DbSet.Last;
          DbSet.First;
          Result:= DbSet.RecordCount;
        end; //cr_Count
        cr_SlowCount:
        begin
          while not DbSet.Eof do
          begin
            inc(Result);
            DbSet.Next;
          end;
          DbSet.First;
        end; //cr_SlowCount
      end; //case
  end //DbSet <>nil
  else Result:=1;
end;


procedure TFlexCelReport.InsertSheets(const FirstPage: integer);
var
  Rc: integer;
begin
  Rc:= RecordCount(FPagesDataSet);
  if (Rc>250) then Raise Exception.Create(ErrTooManySheets);
  Workbook.InsertAndCopySheets(FirstPage, FirstPage+1, Rc-1);
end;

//Ranges containing '!' (like "'Sheet1'!Name") are special in Excel, they are local and refer to just one page
function TFlexCelReport.IsDBName(s: widestring): boolean;
begin
   if pos('!',s)>0 then delete(s,1,pos('!',s));
   Result:=(copy(s,1,length(DataSetStr))=DataSetStr);
end;

function TFlexCelReport.GetDataSetFromName(s: WideString): WideString;
begin
   if pos('!',s)>0 then delete(s,1,pos('!',s));

   s:=copy(s,length(DataSetStr)+1,Length( s));
   s:=copy(s,1,pos(DataSetStr,(s+DataSetStr))-1);
   GetDataSetFromName:=s;
end;

function TFlexCelReport.FindSubBands(const StartRow, EndRow: integer): TExcelBandList;
var
  b: TExcelBand;
begin
  Result:=TExcelBandList.Create(true);
  b:= FindBands(StartRow, EndRow);
  while b<>nil do
  begin
    Result.Add(b);
    b:=FindBands(b.EndRow+1, EndRow); //Search for all the ranges below the one we found. The ones inside will be subbands of it, not of the parent
  end;
end;

procedure TFlexCelReport.FillBandValueList(const RangePos: integer);
var
  i, k, rl, R1, R2, C1, C2: integer;
  v: TXlsCellValue;
begin
  R1:=Workbook.RangeR1[RangePos];
  R2:=Workbook.RangeR2[RangePos];
  C1:=Workbook.RangeC1[RangePos];
  C2:=Workbook.RangeC2[RangePos];
  SetLength(ValueCache, R2-R1+1);
  for i:=0 to Length(ValueCache)-1 do
  begin
    SetLength(ValueCache[i], C2-C1+1); //This is the maximum. Later we adjust it to what we use
    rl:=0;
    for k:=0 to Length(ValueCache[i])-1 do
    begin
      v:=Workbook.GetCellDataX(R1+i, C1+k-1);
      if ((v.XF>=0) or (not VarIsEmpty(v.Value))) and (not v.IsFormula) then
      begin
        ValueCache[i][rl]:= GetBandValue(v.Value, C1+k, v.XF );
        inc(rl);
      end;
    end;
    SetLength(ValueCache[i], rl); //shrink empty values
  end;

end;


function TFlexCelReport.FindBands(const StartRow, EndRow: integer): TExcelBand;
var
  i, St, En, sRow, eRow, First:integer;
  DataName: Widestring;
begin
  St:=EndRow; First:=-1;
  for i:=1 to Workbook.RangeCount do
  if IsDBName(Workbook.RangeName[i]) and
     (Workbook.RangeSheet[i]= Workbook.ActiveSheet) then
  begin
    //Search For the lowest row
    sRow:= Workbook.RangeR1[i];
    eRow:= Workbook.RangeR2[i];
    if (sRow>=StartRow)and
      (sRow<=St) and (eRow<=EndRow) and
      ((sRow<>StartRow) or (eRow<>EndRow))//do not process itself
       then begin;St:=sRow; First:=i;end;
  end;
  if First<0 then Begin; Result:=nil;exit;end;

  //Now search for the biggest range starting on St
  en:=St;First:=-1;
  for i:=1 to Workbook.RangeCount do
  if IsDBName(Workbook.RangeName[i]) and
     (Workbook.RangeSheet[i]= Workbook.ActiveSheet) then
  begin
    sRow:= Workbook.RangeR1[i];
    eRow:= Workbook.RangeR2[i];
    if (sRow=St)and
      (eRow>=En) and (eRow<=EndRow) and
      ((sRow<>StartRow) or (eRow<>EndRow))//do not process itself
       then begin; En:=eRow; First:=i;end;
  end;

  Result:=TExcelBand.Create;
  Result.StartRow:=St;
  Result.EndRow:=En;
  Result.RangePos:=First;
  DataName:=GetDataSetFromName(Workbook.RangeName[First]);
  Result.DataSet:=FDataModule.FindComponent(DataName) as TDataSet;
  Result.SubBands:=FindSubBands(St,En);
end;


procedure TFlexCelReport.ReadTemplate(var MainBand: TExcelBand);
begin
  //Find the Bands
  Assert(MainBand=nil,'MainBand must be nil');
  MainBand:=FindBands(1, 65535);
  if MainBand=nil then exit; //nothing to do in this page
  Workbook.SetBounds(MainBand.RangePos);
  if Workbook.CanOptimizeRead then FillBandValuelist(MainBand.RangePos);
end;

procedure TFlexCelReport.CopyStructure(const Band: TExcelBand; var RowOffset: integer);
var
  RealOldRangeCount: integer;
  k:integer;
  DataRecordCount: integer;
begin
    //Insert the new Cells
    DataRecordCount:=RecordCount(Band.DataSet);

    //insert/delete the corresponding rows
    RealOldRangeCount:=Band.EndRow - Band.StartRow+1;  //in RealOldRangeCount we store the row count without the subbands
    for k:=0 to Band.SubBands.Count-1 do Dec(RealOldRangeCount,(Band.SubBands[k] as TExcelBand).EndRow-(Band.SubBands[k] as TExcelBand).StartRow+1);

    if DataRecordCount - 1 > 0 then
    begin
      Workbook.InsertAndCopyRows(Band.StartRow+RowOffset, Band.EndRow+RowOffset, Band.EndRow+1+RowOffset, DataRecordCount - 1, true);
    end else
    if DataRecordCount - 1 < 0 then //We must delete the line
    begin
      Workbook.DeleteRows(Band.StartRow+RowOffset, Band.EndRow-Band.StartRow+1);
    end;

    // SubBands
    while (Band.DataSet=nil) or (not Band.DataSet.Eof) do
    begin
      for k:=0 to Band.SubBands.Count-1 do
      begin
        CopyStructure(Band.SubBands[k] as TExcelBand, RowOffset);
        dec(RowOffset, TExcelBand(Band.SubBands[k]).EndRow-TExcelBand(Band.SubBands[k]).StartRow+1); //Compensate the extra line in the ranges
      end;

      for k:=0 to Band.SubBands.Count-1 do
        inc(RowOffset, TExcelBand(Band.SubBands[k]).EndRow-TExcelBand(Band.SubBands[k]).StartRow+1); //Restore the extra line for the next record

      inc(RowOffset,RealOldRangeCount);  //not just the total range, because the Subbands have already incremented their part.
      if Band.DataSet<>nil then Band.DataSet.Next else break;
    end;
end;

function TFlexCelReport.GetPictureType(const s: widestring): TXlsImgTypes;
var
  Id: string;
begin
  Id:=UpperCase(s);
  if pos(FieldStr,Id)<> 0 then
  begin; //is a db image
    while pos(FieldStr,Id)>0 do Delete(Id, 1, pos(FieldStr,Id)+Length(FieldStr)-1 );
    if Id= xls_Wmf then Result:= xli_Wmf else
    if Id= xls_Emf then Result:= xli_Emf else
    if Id= xls_Png then Result:= xli_Png else
    if Id= xls_Jpeg then Result:= xli_Jpeg else
    raise Exception.CreateFmt(ErrInvalidDrawingType, [Id]);
  end else
  if pos(VarStr,Id)<> 0 then
  begin; //is a prop image
    while pos(VarStr,Id)>0 do Delete(Id, 1, pos(VarStr,Id)+Length(VarStr)-1 );
    if Id= xls_Wmf then Result:= xli_Wmf else
    if Id= xls_Emf then Result:= xli_Emf else
    if Id= xls_Png then Result:= xli_Png else
    if Id= xls_Jpeg then Result:= xli_Jpeg else
    raise Exception.CreateFmt(ErrInvalidDrawingType, [Id]);
  end else
  begin  //Is not an image to replace
    Result:=xli_Jpeg;
  end;
end;

function TFlexCelReport.SupressCR(const s: Widestring): widestring;
var
  i,k: integer;
begin
  SetLength(Result,Length(s));
  k:=1;
  for i:=1 to Length(s) do if s[i]<>#13 then
  begin
    Result[k]:=s[i];
    inc(k);
  end;

  SetLength(Result, k-1);
end;


procedure TFlexCelReport.GetMultiDimArray(var v: variant; PropIndex: string; const VarName: string);
var
  p:^VariantArray;
  u: variant;
  i, k, abs, LastDim: integer;
begin
  abs:=0;
  k:=1;
  LastDim:=1;
  while PropIndex<>'' do
  begin
    i:=StrToInt(Copy(PropIndex,1,Pos(VarStr,PropIndex+VarStr)-1));
    Delete(PropIndex,1,Pos(VarStr,PropIndex+VarStr)+Length(VarStr)-1);
    if k> VarArrayDimCount(v) then raise exception.CreateFmt(ErrTooManyDimensions,[VarName, VarArrayDimCount(v)]);
    if (i<VarArrayLowBound(v,k)) or (i>VarArrayHighBound(v,k)) then raise Exception.CreateFmt(ErrIndexOutBounds, [i, VarName,VarArrayLowBound(v,k),VarArrayHighBound(v,k)]);

    inc (abs, (i-VarArrayLowBound(v,k))* LastDim);
    LastDim:=LastDim* (VarArrayHighBound(v,k)-VarArrayLowBound(v,k)+1);
    inc(k);
  end;

  u:=null;
  p:=VarArrayLock(v);
  try
    u:=(p[abs]);
  finally
    VarArrayUnlock(v);
  end;

  v:=u;
end;


procedure TFlexCelReport.GetOneDimArray(var v: variant; PropIndex: string; const VarName: string);
var
  i: integer;
begin
  while PropIndex<>'' do
  begin
    i:=StrToInt(Copy(PropIndex,1,Pos(VarStr,PropIndex+VarStr)-1));
    Delete(PropIndex,1,Pos(VarStr,PropIndex+VarStr)+Length(VarStr)-1);
    if (i<VarArrayLowBound(v,1)) or (i>VarArrayHighBound(v,1)) then raise Exception.CreateFmt(ErrIndexOutBounds, [i, VarName,VarArrayLowBound(v,1),VarArrayHighBound(v,1)]);
    v:=v[i];
  end;
end;


function TFlexCelReport.GetOneBandValue(const v:Variant; const ColOffset: integer; const XF: integer): TOneBandValue;
var
  Ds: TDataSet;
  DsName, FieldName: string;
  VarName, PropIndex: string;
  s: widestring;
begin
  Result.ValueType:=flv_Value;
  Result.Value.Value:=v;
  Result.Value.XF:=XF;
  Result.CellValue:=v;
  Result.ColOffset:= ColOffset;

  s:=v;
  if copy(s,1,Length(FieldStr))=FieldStr then //It's a field from the database
  begin
    DsName:=copy(s,Length(FieldStr)+1, Length(s));
    DsName:=copy(DsName,1, pos(FieldStr,DsName)-1);
    Ds:=(FDataModule.FindComponent(DsName) as TDataSet);
    if Ds=nil then Raise Exception.CreateFmt(ErrNoDataSet,[DsName]);
    FieldName:=copy(s,1+length(DsName)+length(FieldStr)*2,length(s));
    FieldName:=copy(FieldName, 1, pos(FieldStr, FieldName+FieldStr)-1);
    if FieldName= FullDataSetStr then
    begin
      Result.ValueType:=flv_FullDataSet;
      Result.Value.Value:=unassigned;
    end else
    begin
      LazyDbList.Check(Ds);
      Result.ValueType:= flv_Field;
      Result.Field:=Ds.FieldByName(FieldName);
    end;

  end else
  if copy(s,1,Length(VarStr))=VarStr then  //It's a Property of the datamodule
  begin
    VarName:=copy(s,Length(VarStr)+1, Length(s));
    PropIndex:='';
    if Pos(VarStr, VarName)<>0 then//It's a Prop with Subindexs
    begin
      PropIndex:=copy(VarName,Pos(VarStr, VarName)+ Length(VarStr), Length(VarName));
      VarName:=copy(VarName,1,Pos(VarStr, VarName)-1);
    end;
    Result.ValueType:= flv_Property;
    Result.PropInfo:=GetPropInfo(FDataModule, VarName);
    if Result.PropInfo=nil then raise exception.CreateFmt(ErrNoPropDefined,[VarName]);
    Result.PropIndex:= PropIndex;
    Result.VarName:= VarName;
  end;
end;

function TFlexCelreport.GetBandValue(const v: Variant; const ColOffset: integer; const XF: integer=-1): TBandValue;
begin
  SetLength(Result,1);
  Result[0]:=GetOneBandValue(v, ColOffset, XF);
end;

function TFlexCelReport.GetValue(const BandValue: TBandValue; const RowOffset, col: integer): variant;
var
  Field: TField;
  i: integer;
begin
  for i:=0 to Length(BandValue)-1 do
  begin
    case BandValue[i].ValueType of
      flv_Value: Result:= BandValue[i].Value.Value;
      flv_Field:
      begin
        Field:=BandValue[i].Field;
        if Field is TDateTimeField then Result:= Field.AsFloat else
        //There are problems if we assign v:=Field.Value to a TdatetimeField, the result is a string and has problems with the locales
        //Remember to format the cell in the excel template as Date.
        if Field is TMemoField then Result:= SupressCR(Field.Value) else
        Result:=Field.Value;
        if Assigned(FOnGetCellValue) then FOnGetCellValue(Self, BandValue[i].CellValue, Result);
      end;
      flv_Property:
      begin
        try
          Result:=GetVariantProp(FDataModule, BandValue[i].PropInfo);
        except
          raise exception.CreateFmt(ErrBadProp,[BandValue[i].VarName]);
        end; //except

        if VarArrayDimCount(Result)>1 then GetMultiDimArray(Result, BandValue[i].PropIndex, BandValue[i].VarName) else
          if VarArrayDimCount(Result)=1 then GetOneDimArray(Result, BandValue[i].PropIndex, BandValue[i].VarName);

        if Assigned(FOngetCellValue) then FOnGetCellValue(Self, BandValue[i].CellValue, Result);
        if VarType(Result)=varDate then Result:=VarAsType(Result, varDouble); //Convert dates into numbers
      end;
      flv_FullDataset:
      begin
        FullDataSets.Add(TFullDataSet.Create(BandValue[i].CellValue, RowOffset, Col));
        Result:=Unassigned;
      end;
      else Result:=Unassigned;
    end; //case
  end;
end;

procedure TFlexCelReport.FillFullDatasets;
var
  Ds: TDataSet;
  DsName: string;
  i,k, fds, fds2: integer;
  Rc: integer;
  FullDs: TFullDataSet;
begin
  for fds:=0 to FullDataSets.Count-1 do
  begin
    FullDs:= FullDataSets[fds];
    DsName:=copy(FullDs.Text,Length(FieldStr)+1, Length(FullDs.Text));
    DsName:=copy(DsName,1, pos(FieldStr,DsName)-1);
    Ds:=(FDataModule.FindComponent(DsName) as TDataSet);
    if Ds=nil then Raise Exception.CreateFmt(ErrNoDataSet,[DsName]);

    Rc:=RecordCount(Ds);
    Ds.First;
    if Rc>0 then
    begin
      Workbook.InsertAndCopyRows(FullDs.RowOffset+2, FullDs.RowOffset+2, FullDs.RowOffset+3, Rc, false);
      for fds2:=fds+1 to FullDataSets.Count-1 do // insert rows into next FullDataSets
        if FullDataSets[fds2].RowOffset>FullDs.RowOffset then inc (FullDataSets[fds2].RowOffset, Rc);
    end;
    Workbook.PrepareBlockData(FullDs.RowOffset+1, FullDs.Col, FullDs.RowOffset+1+Rc+1-1, FullDs.Col+Ds.FieldCount-1);
    for i:= FullDs.Col to FullDs.Col + Ds.FieldCount-1 do
      Workbook.AssignBlockData(FullDs.RowOffset+1, i, Ds.Fields[i-FullDs.Col].DisplayName);
    for k:=1 to Rc do
    begin
      for i:= FullDs.Col to FullDs.Col + Ds.FieldCount-1 do
        if Ds.Fields[i-FullDs.Col] is TMemoField then
          Workbook.AssignBlockData(FullDs.RowOffset+1+k, i, SupressCR(Ds.Fields[i-FullDs.Col].Value)) else
          Workbook.AssignBlockData(FullDs.RowOffset+1+k, i, Ds.Fields[i-FullDs.Col].Value);
          //Here we dont convert dates into numbers, cause this is a generic template, so we cannot format the cells
      Ds.Next;
    end;
    Workbook.PasteBlockData;
  end;
end;

procedure TFlexCelReport.ReplaceValues(const Band: TExcelBand;const Rof: integer; var RowOffset: integer);
var
  j:integer;
  CV: TXlsCellValue;
  v:Variant;
begin
  if Workbook.CanOptimizeRead then
  begin
    for j:=0 to Length(ValueCache[Rof])-1 do
    begin
      CV.Value:=GetValue(ValueCache[Rof][j], RowOffset, ValueCache[Rof][j][0].ColOffset-1);
      CV.XF:=ValueCache[Rof][j][0].Value.XF;
      Workbook.AssignCellDataX(RowOffset+1, ValueCache[Rof][j][0].ColOffset-1, CV);
    end
  end else

  begin
  for j:=0 to Workbook.CellCount(RowOffset+1)-1 do
  begin
    v:=Workbook.GetCellData(RowOffset+1,j);
      if not VarIsEmpty(v) then Workbook.AssignCellData(RowOffset+1, j, GetValue(GetBandValue(v, j), RowOffset, j));
    end;
  end;
end;

procedure TFlexCelReport.ReplacePictures(var RowOffset: integer);
var
  i:integer;
  v, vw:Variant;
  PictureType: TXlsImgTypes;
begin
  for i:=0 to Workbook.PicturesCount[RowOffset+1]-1 do
  begin
    v:=Workbook.PictureName[RowOffset+1,i];
    PictureType:= GetPictureType(v);
    vw:=GetValue(GetBandValue(v,0), RowOffset, 0);
    if v<>vw then Workbook.AssignPicture(RowOffset+1, i, vw, PictureType);
  end;
end;

procedure TFlexCelReport.ReplaceComments(var RowOffset: integer);
var
  i:integer;
  v, vw:Variant;
begin
  for i:=Workbook.CommentsCount[RowOffset+1]-1 downto 0 do   //Reverse order so comments might be deleted
  begin
    v:=Workbook.CommentText[RowOffset+1,i];
    vw:= GetValue(GetBandValue(v,0), RowOffset, 0);
    if v<>vw then Workbook.AssignComment(RowOffset+1, i ,vw);
  end;
end;


procedure TFlexCelReport.FillBandData( const Band: TExcelBand; var RowOffset: integer; const MainBandRow: integer);
var
  i,k, SaveK:integer;
begin
  if Band.DataSet<>nil then
  begin
    Band.DataSet.First;
  end;

  //Fill in the data
  while (Band.DataSet=nil) or (not Band.DataSet.Eof) do
  begin
    i:=Band.StartRow;
    while i<=Band.EndRow do
    begin
      SaveK:=-1;
      for k:=0 to Band.SubBands.Count-1 do
        if (Band.SubBands[k] as TExcelBand).StartRow=i then
        begin
          SaveK:=k;
          break;
        end;
      if SaveK>=0 then
      begin
        FillBandData(Band.SubBands[SaveK] as TExcelBand, RowOffset, MainBandRow);
        i:=(Band.SubBands[SaveK] as TExcelBand).EndRow;
      end else
      begin
        ReplaceValues(Band, i-MainBandRow, RowOffset);
        ReplaceComments(RowOffset);
        ReplacePictures(RowOffset);
        inc(RowOffset);
      end;
      inc(i);
    end;
    if Band.DataSet<>nil then Band.DataSet.Next else break;
  end;
end;

function TFlexCelReport.ExportFieldData: boolean;
var
  MainBand: TExcelBand;
  RowOffset: integer;
begin
  Result:=false;
  MainBand:= nil;
  try
    ValueCache:= nil;
    try
      ReadTemplate(MainBand);
      if MainBand=nil then exit;
      Result:=true;

      RowOffset:=0;
      CopyStructure(MainBand, RowOffset);

      RowOffset:=MainBand.StartRow-1;
      Workbook.BeginSheet;
      try
        FillBandData(MainBand, RowOffset, MainBand.StartRow);
      finally
        Workbook.EndSheet(RowOffset);
      end;  //finally
    finally
      ValueCache:=nil;
    end; //finally
  finally
    FreeAndNil(MainBand);
  end; //finally
end;


////////////////////////////////////////////////////////////////////////////////
procedure TFlexCelReport.Run;
var
  OldCursor: TCursor;
  i, FirstSheet:integer;

begin
  if FAdapter=nil then raise Exception.Create(ErrNoAdapter);
  OldCursor := Screen.Cursor;
  Screen.Cursor := crHourGlass;
  try
    Workbook:=FAdapter.GetWorkbook;
    try
      Workbook.Connect;
      try
        Workbook.OpenFile(FTemplate);
        try
          LazyDbList:=TExcelDbList.Create;
          try
            LazyDbList.Check(FPagesDataSet);
            if Assigned (FOnBeforeGenerateWorkbook) then FOnBeforeGenerateWorkbook(Self, WorkBook);

            FirstSheet:=0;
            if Assigned (FPagesDataSet) then
            begin
              FirstSheet:= Workbook.ActiveSheet;
              InsertSheets(FirstSheet);
            end;

            for i:=1 to Workbook.SheetCount do
            begin
              if Assigned (FOnBeforeGeneratePage) then FOnBeforeGeneratePage(Self, Workbook);

              Workbook.ActiveSheet:=i;

              FullDataSets:= TFullDataSetList.Create;
              try
                if ExportFieldData then
                begin
                  FillFullDatasets;
                  Workbook.DeleteMarkedRows(MarkedRowStr);
                end;
                Workbook.RefreshPivotTables;
                if Assigned (FOnAfterGeneratePage) then FOnAfterGeneratePage(Self, Workbook);

                if Assigned(FPagesDataSet) and (i>=FirstSheet) then
                begin
                  if not FPagesDataSet.Eof then
                  begin
                    if (FPagesDataField<>'') then Workbook.ActiveSheetName:=FPagesDataSet.FieldByName(FPagesDataField).Value;
                    FPagesDataSet.Next;
                  end;
                end;

              finally
                FreeAndNil(FullDataSets);
              end; //finally

            end; //For each page
          finally
            FreeAndNil(LazyDbList);
          end; //finally

          Workbook.SelectSheet(1);
          if Assigned (FOnAfterGenerateWorkbook) then FOnAfterGenerateWorkbook(Self, Workbook);

          Workbook.Save(FAutoClose, FFileName, FOnGetFileName);
        except
          Workbook.CloseFile;
          raise;
        end; //Except

        if (FAutoClose) then    //If there was an exception the workbook has already been closed, so we dont have to protect this
        try
          Workbook.CloseFile;  //This wont quit excel, but when the app finishes it will quit too.
        except
          //nothing
        end; //Except

      finally
        Workbook.Disconnect;
      end; //finally
    finally
      FreeAndNil(Workbook);
    end; //finally

  finally
    Screen.Cursor := OldCursor;
  end;
end;

procedure TFlexCelReport.SetTemplate(const Value: TFileName);
begin
  FTemplate := Value;
end;

procedure TFlexCelReport.SetPagesDataSet(const Value: TDataSet);
begin
  FPagesDataSet := Value;
end;

procedure TFlexCelReport.SetPagesDataField(const Value: string);
begin
  FPagesDataField := Value;
end;

procedure TFlexCelReport.SetOnRecordCount(const Value: TRecordCountEvent);
begin
  FOnRecordCount := Value;
end;

procedure TFlexCelReport.SetDataModule(const Value: TComponent);
begin
  if Value=nil then FDataModule := Owner else FDataModule:=Value;
end;

procedure TFlexCelReport.SetAdapter(const Value: TExcelAdapter);
begin
  FAdapter := Value;
end;

{ TExcelBand }

constructor TExcelBand.Create;
begin
  inherited;
end;

destructor TExcelBand.Destroy;
begin
  FreeAndNil(SubBands);
  inherited;
end;

{ TExcelDbList }

procedure TExcelDbList.Check(const DataSet: TDataSet);
begin
  if (DataSet<>nil) and not DataSet.Active then
  begin
    DataSet.Open;
    Add(DataSet);
  end;
end;

destructor TExcelDbList.Destroy;
var
  i: integer;
begin
  for i:=0 to Count -1 do Items[i].Active:=False;
  inherited;
end;

end.