unit ExcelReport;
{$DEFINE Excel97}
{******************************************************************
 Component to automate reporting to Excel
 Version 1.2

// 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 (agallero@netscape.net).
//
// Portions created by Adrian Gallero are Copyright
// (C) 2002-2002 Adrian Gallero. All Rights Reserved.

 Send any comments to agallero@netscape.net
********************************************************************
 New in version 1.2
    Bug fixed: AV if you delete a Table that is assigned to PagesDataSet. Many thanks to Andrey Polubinsky
    Substituted many variants by Native types.

 New in Version 1.1
    Delphi 6 Support
    Events to allow manual changes to the report before it is saved.
    Ability to save the report in different formats, including HTML  (HTML is not available in Excel97).
    Support for Excel2000 and Excel97 Delphi Component Sets.

//******************************************************************}


interface
{$R EXCELREPORT.RES}
uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  OleServer, contnrs, db,
  {$IFDEF Excel97}
    Excel97,
  {$ELSE}
    Excel2000,
  {$ENDIF}
  {$IFDEF VER140} //Delphi 6
    variants,
  {$ENDIF}
  typinfo;

type
  TOleEnum = type Integer; // Copied from ActiveX unit
  xlsNames = Names;
  xlsName = Name;

  TExcelBandList= class(TObjectList)  //Contains TExcelBands
  end;

  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: TExcelApplication; const ExcelWorkbook: TExcelWorkbook; const LCID: integer) of object;
  TOnGeneratePageEvent = procedure (Sender: TObject; const ExcelApp: TExcelApplication; const ExcelWorkbook: TExcelWorkbook; const ExcelWorkSheet: TExcelWorksheet; const LCID: integer) of object;
  TOnGetFileNameEvent = procedure (Sender: TObject; const  FileFormat: TOleEnum; var Filename: TFileName) of object;

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

  //We need 2 sets, It's too big for one
  TExcelSaveFormatBasic= (
    saCSV, saCSVMSDOS, saCSVWindows, saCSVMac,
    saDBF4, saDIF,
    saCurrentPlatformText, saTextMSDOS, saTextWindows, saTextMac,
    saExcel5, saExcel7, saExcel9795,
    saWorkbookNormal, saSYLK, saTemplate
    {$IFNDEF Excel97}
      , saUnicodeText, saHtml
    {$ENDIF}
    );

  TExcelSaveFormatExtended= (
    saExcel2, saExcel2FarEast, saExcel3, saExcel4,
    saDBF2, saDBF3,
    saExcel4Workbook, saIntlAddIn, saIntlMacro, saTextPrinter,
    saWJ2WD1, saWK1, saWK1ALL, saWK1FMT, saWK3, saWK4, saWK3FM3, saWKS, saWorks2FarEast,
    saWQ1, saWJ3, saWJ3FJ3);

  TSetOfExcelSaveFormatBasic = Set Of TExcelSaveFormatBasic;
  TSetOfExcelSaveFormatExtended = Set Of TExcelSaveFormatExtended;

  TExcelReport = class(TComponent)
  private
    FTemplate: TFileName;
    FDataModule: TComponent;

    FExcelApplication : TExcelApplication;
    FExcelWorkbook : TExcelWorkbook;
    FExcelWorksheet : TExcelWorksheet;

    OldCellData, NewCellData: Variant;
    WorkRange: Range;
    CellRangeRow: integer;
    FirstColumn, LastColumn:integer;
    LCID : Integer;
    Band: TExcelBand;
    CurrentPage: Integer;

    RowOffset: integer;  //Keep track of how many rows we have inserted
    NewDataOffset, OldDataOffset: integer;
    FBlockSize: integer;
    FAutoClose: boolean;
    FFileName: TFileName;
    FDisplayAlerts: boolean;
    FCalcRecordCount: TCalcRecordCount;
    FPagesDataSet: TDataSet;
    FPagesDataField: string;

    FOnRecordCount: TRecordCountEvent;
    FOnAfterGeneratePage: TOnGeneratePageEvent;
    FOnAfterGenerateWorkbook: TOnGenerateEvent;
    FOnBeforeGeneratePage: TOnGeneratePageEvent;
    FOnBeforeGenerateWorkbook: TOnGenerateEvent;
    FSaveFormatBasic: TSetOfExcelSaveFormatBasic;
    FSaveFormatExtended: TSetOfExcelSaveFormatExtended;
    FOnGetFileName: TOnGetFilenameEvent;

    procedure SetTemplate(const Value: TFileName);
    procedure ExportFieldData;
    procedure FillBandData(const Band: TExcelBand);
    procedure ReadTemplate;
    procedure ReplaceValues;
    procedure AssignCellData(const Column: integer;
      const Value: Variant);
    procedure FlushFinalData;
    procedure CopyStructure(const Band: TExcelBand);
    function GetCellData(const Row, Column: integer): variant;

    function FindBands(const StartRow, EndRow: integer): TExcelBand;
    function FindSubBands(const StartRow, EndRow: integer): TExcelBandList;
    procedure SetPagesDataSet(const Value: TDataSet);
    procedure InsertPages;
    procedure SetPagesDataField(const Value: string);
    function GetNames(const i: integer): xlsName;
    function GetNamesCount: integer;
    function GetDataSetFromName(const i: integer): string;
    function IsSpecialName(const i: integer): boolean;
    function GetPage(const i: integer): integer;
    procedure SetOnRecordCount(const Value: TRecordCountEvent);
    procedure DeleteMarkedRows;
    procedure RefreshPivotTables;
    procedure SetDataModule(const Value: TComponent);
    { 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 BlockSize: integer read FBlockSize write FBlockSize default 100;
    property FileName: TFileName read FFileName write FFileName;
    property AutoClose: boolean read FAutoClose write FAutoClose;
    property DisplayAlerts: boolean read FDisplayAlerts write FDisplayAlerts default true;

    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 SaveFormatBasic: TSetOfExcelSaveFormatBasic read FSaveFormatBasic write FSaveFormatBasic default [saExcel9795];
    property SaveFormatExtended: TSetOfExcelSaveFormatExtended read FSaveFormatExtended write FSaveFormatExtended;

    //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;
    { Published declarations }
  end;

procedure Register;

implementation
uses ComObj;

resourcestring
  FieldStr='##';
  DataSetStr='__';
  VarStr='#.';
  MarkedRowStr='...delete row...';

{$IFDEF SPANISH}
  ErrNoDataSet='No se puede encontrar el dataset %s';
  ErrNoPropDefined='No se ha definido la propiedad "%s" o no es published';
  ErrBadProp='Error al recuperar la propiedad "%s". Verifique que sea del tipo variant';
  ErrIndexOutBounds='El indice "%d" de la propiedad "%s" no est?en el rango %d-%d';
{$ELSE}
  ErrNoDataSet='Canīt find the dataset %s';
  ErrNoPropDefined='Property "%s" has not been defined or is not published';
  ErrBadProp='Error trying to retrieve property "%s". Verify it is a variant';
  ErrIndexOutBounds='The index "%d" of the property "%s" is not in the range %d-%d';
{$ENDIF}

procedure Register;
begin
  RegisterComponents('Additional', [TExcelReport]);
end;

Const
  SaveFormatBasicConvert: Array[TExcelSaveFormatBasic] of TOleEnum= (
    xlCSV, xlCSVMSDOS, xlCSVWindows, xlCSVMac,
    xlDBF4, xlDIF,
    integer(xlCurrentPlatformText), xlTextMSDOS, xlTextWindows, xlTextMac,
    xlExcel5, xlExcel7, xlExcel9795,
    integer(xlWorkbookNormal), xlSYLK, xlTemplate
    {$IFNDEF Excel97}
      , xlUnicodeText, xlHtml
    {$ENDIF}
    );

  SaveFormatExtendedConvert: Array[TExcelSaveFormatExtended] of TOleEnum= (
    xlExcel2, xlExcel2FarEast, xlExcel3, xlExcel4,
    xlDBF2, xlDBF3,
    xlExcel4Workbook, xlIntlAddIn, xlIntlMacro, xlTextPrinter,
    xlWJ2WD1, xlWK1, xlWK1ALL, xlWK1FMT, xlWK3, xlWK4, xlWK3FM3, xlWKS, xlWorks2FarEast,
    xlWQ1, xlWJ3, xlWJ3FJ3);

{ TExcelReport }

constructor TExcelReport.Create(AOwner: TComponent);
begin
  inherited;
  FExcelApplication := TExcelApplication.Create(Self);
  FExcelWorkbook := TExcelWorkbook.Create(Self);
  FExcelWorksheet := TExcelWorksheet.Create(Self);
  FDataModule:=AOwner;
  FDisplayAlerts:=true;
  FCalcRecordCount:=cr_Count;
  FSaveFormatBasic:=[saExcel9795];
  FSaveFormatExtended:=[];

  Band:=nil;
  FBlockSize:=100;
end;

destructor TExcelReport.Destroy;
begin
  inherited;
end;

procedure TExcelReport.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;
  end;
end;


function TExcelReport.GetNames(const i: integer):xlsName;
var
  aNames: xlsNames;
begin
  aNames:= FExcelWorkbook.Names;
  Result:= aNames.Item(i,EmptyParam, EmptyParam);
end;

function TExcelReport.GetPage(const i:integer):integer;
var
  w:OleVariant; //Cant get to use _Worksheet
begin
  try
    w:=GetNames(i).RefersToRange.Worksheet;
    result:=w.Index;
  except //Err in range
    result:=-1;
  end; //Except
end;

//Ranges containing '!' (like "'Sheet1'!Name") are special in Excel, they are local and refer to just one page
function TExcelReport.IsSpecialName(const i:integer): boolean;
var
  s:string;
begin
   s:=GetNames(i).Name_;
   if pos('!',s)>0 then delete(s,1,pos('!',s));
   IsSpecialName:=(copy(s,1,length(DataSetStr))=DataSetStr);
end;

function TExcelReport.GetDataSetFromName(const i:integer): string;
var
  s:string;
begin
   s:=GetNames(i).Name_;
   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 TExcelReport.GetNamesCount: integer;
begin
  Result:= FExcelWorkbook.Names.Count;
end;

function TExcelReport.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;

function TExcelReport.FindBands(const StartRow, EndRow: integer): TExcelBand;
var
  i, St, En, sRow, eRow, First:integer;
  r: Range;
  DataName: string;
begin
  St:=EndRow; First:=-1;
  for i:=1 to GetNamesCount do
  if IsSpecialName(i) and
     (GetPage(i)= CurrentPage) then
  begin
    //Search For the lowest row
    r:=GetNames(i).RefersToRange;
    sRow:= r.Row;
    eRow:= r.Row+r.Rows.Count-1;
    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 GetNamesCount do
  if IsSpecialName(i) and
     (GetPage(i)= CurrentPage) then
  begin
    r:=GetNames(i).RefersToRange;
    sRow:= r.Row;
    eRow:= r.Row+r.Rows.Count-1;
    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(First);
  Result.DataSet:=FDataModule.FindComponent(DataName) as TDataSet;
  Result.SubBands:=FindSubBands(St,En);

end;

procedure TExcelReport.ReadTemplate;
var
  CellRange: Range;
begin
  //Find the Bands
  CellRangeRow:=1;

  Assert(Band=nil,'Band must be nil');
  Band:=FindBands(CellRangeRow, 65535);
  if Band=nil then exit; //nothing to do in this page
  CellRange:=GetNames(Band.RangePos).RefersToRange;

  FirstColumn:=CellRange.Column;
  LastColumn:=CellRange.Column+CellRange.Columns.Count-1;
end;

procedure TExcelReport.CopyStructure(const Band: TExcelBand);
var
  OldRange, NewRange: Range;
  OldRangeCount, NewRangeCount, RealOldRangeCount: integer;
  k:integer;
  DataRecordCount: integer;
  CharFirstColumn,CharLastColumn: char;
begin
    CharFirstColumn:=chr(FirstColumn+ord('A')-1);
    CharLastColumn:=chr(LastColumn+ord('A')-1);

    //Insert the new Cells
    DataRecordCount:=0;
    if Band.DataSet<>nil then
    begin
      //Count the records
      Band.DataSet.First;

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

    //Now we have the recordcount in DataRecordCount. Then, we have to
    //insert/delete the corresponding rows

    OldRange := FExcelWorksheet.Range[CharFirstColumn+IntToStr(Band.StartRow+RowOffset), CharLastColumn+IntToStr(Band.EndRow+RowOffset)].EntireRow; //We need the entire row to copy row height!
    OldRangeCount := Band.EndRow - Band.StartRow+1;
    RealOldRangeCount:=OldRangeCount;  //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 Band.DataSet=nil then NewRangeCount:=0 else NewRangeCount := (DataRecordCount - 1) * OldRangeCount;
    if NewRangeCount>0 then
    begin
      NewRange:=FExcelWorksheet.Range[CharFirstColumn+IntToStr(Band.EndRow+1+RowOffset), CharLastColumn+IntToStr(Band.EndRow+NewRangeCount+RowOffset)].EntireRow;
      NewRange.Insert( integer(xlDown));
      NewRange := NewRange.Offset[-NewRangeCount,0];
      OldRange.Copy (NewRange);
    end else
    if NewRangeCount<0 then //We must delete the line
    begin
      NewRange:=FExcelWorksheet.Range[CharFirstColumn+IntToStr(Band.StartRow+RowOffset), CharLastColumn+IntToStr(Band.EndRow+RowOffset)].EntireRow;
      NewRange.Delete(Integer(xlShiftUp));
    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);
        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 OldRangeCount, because the Subbands have already incremented their part.
      if Band.DataSet<>nil then Band.DataSet.Next else break;
    end;
end;

procedure TExcelReport.ExportFieldData;
begin
  Assert(Band=nil,'Band must be nil!');
  try
    try
      ReadTemplate;
      if Band=nil then exit;

      RowOffset:=0;
      CopyStructure(Band);

      RowOffset:=0;
      NewCellData:=VarArrayCreate([1,FBlockSize,FirstColumn,LastColumn],varVariant);
      WorkRange:=FExcelWorksheet.Range[
        FExcelWorksheet.Cells.Item[CellRangeRow, FirstColumn],
        FExcelWorksheet.Cells.Item[CellRangeRow+FBlockSize-1, LastColumn]
        ];
      OldCellData:=Unassigned;
      NewDataOffset:=0;OldDataOffset:=-1;
      FillBandData(Band);
      FlushFinalData;
    finally
      WorkRange:=nil; //This is needed so we don't keep any reference to Excel.
    end; //finally
  finally
    FreeAndNil(Band);
  end; //finally
end;

procedure TExcelReport.FlushFinalData;
var
  Size:integer;
begin
  Size:=RowOffset-1-NewDataOffset;
  if Size<0 then exit;
  WorkRange:=FExcelWorksheet.Range[
    FExcelWorksheet.Cells.Item[CellRangeRow, FirstColumn],
    FExcelWorksheet.Cells.Item[CellRangeRow+Size, LastColumn]
    ];
  WorkRange.Offset[NewDataOffset,0].FormulaLocal:=NewCellData;
end;

procedure TExcelReport.AssignCellData(const Column: integer; const Value: Variant);
var
  i,j:integer;
  Row:integer;
begin
  Row:=RowOffset+1;
  if Row-NewDataOffset > FBlockSize then
  begin
    //Flush Data
    WorkRange.Offset[NewDataOffset,0].FormulaLocal:=NewCellData;
    NewDataOffset:=Row-1;
    for i:=VarArrayLowBound(NewCellData,1) to VarArrayHighBound(NewCellData,1) do
      for j:=VarArrayLowBound(NewCellData,2) to VarArrayHighBound(NewCellData,2) do
        NewCellData[i,j]:=unassigned;
  end;
  NewCellData[Row-NewDataOffset,Column]:=Value;
end;

function TExcelReport.GetCellData(const Row,Column: integer): variant;
begin
  if (OldDataOffset<0)or (Row-OldDataOffset>FBlockSize) then
  begin
    OldDataOffset:=Row-1;
    OldCellData:= WorkRange.Offset[OldDataOffset,0].FormulaLocal;
  end;
  GetCellData:=OldCellData[Row-OldDataOffset,Column-FirstColumn+1];
end;

procedure TExcelReport.ReplaceValues;
var
  i,j:integer;
  Ds: TDataSet;
  v, v1:Variant;
  DsName, FieldName: string;
  VarName, PropIndex: string;
  PInfo: PPropInfo;
  Field: TField;
begin
    for j:=FirstColumn to LastColumn do
    begin
      v:=GetCellData(RowOffset+1,j);
      if copy(v,1,Length(FieldStr))=FieldStr then //It's a field from the database
      begin
        DsName:=copy(v,Length(FieldStr)+1, Length(v));
        DsName:=copy(DsName,1, pos(FieldStr,DsName)-1);
        FieldName:=copy(v,1+length(DsName)+length(FieldStr)*2,length(v));
        Ds:=(FDataModule.FindComponent(DsName) as TDataSet);
        if Ds=nil then Raise Exception.CreateFmt(ErrNoDataSet,[DsName]);
        Field:=Ds.FieldByName(FieldName);
        if Field is TDateTimeField then v:= Field.AsFloat else v:=Field.Value;
        //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.
      end else
      if copy(v,1,Length(VarStr))=VarStr then  //It's a Property of the datamodule
      begin
        VarName:=copy(v,Length(VarStr)+1, Length(v));
        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;
        PInfo:=GetPropInfo(FDataModule, VarName);
        if PInfo=nil then raise exception.CreateFmt(ErrNoPropDefined,[VarName]);
        try
          v:=GetVariantProp(FDataModule, PInfo);
        except
          raise exception.CreateFmt(ErrBadProp,[VarName]);
        end; //except
        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)]);
          v1:=v[i];
          v:=v1;
        end;
      end;
      AssignCellData(j,v);
    end;
end;

procedure TExcelReport.FillBandData( const Band: TExcelBand);
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);
          i:=(Band.SubBands[SaveK] as TExcelBand).EndRow;
        end else
        begin
          ReplaceValues;
          inc(RowOffset);
        end;
      inc(i);
      end;
      if Band.DataSet<>nil then Band.DataSet.Next else break;
    end;
end;

procedure TExcelReport.InsertPages;
var
  Ws: _Worksheet;
begin
  FPagesDataSet.First;
  Ws:=(FExcelWorkbook.ActiveSheet as _Worksheet);
  while not FPagesDataSet.Eof do
  begin
    FPagesDataSet.Next;
    if not FPagesDataSet.Eof then Ws.Copy(EmptyParam,Ws,lcid);
  end;
  FPagesDataSet.First;
end;

procedure TExcelReport.DeleteMarkedRows;
var
  r: OleVariant;
  Empty: boolean;
begin
  repeat
    r := FExcelWorksheet.UsedRange[LCID].Resize[EmptyParam,1];
   // We dont use early binding in 'Find' method because incompatibilities between d5 & d6 & excel97 & excel2000...
    r :=r.Find(MarkedRowStr, EmptyParam, integer(xlValues), xlWhole, EmptyParam, xlNext, true, EmptyParam);
    // This is VarIsEmpty... If it just worked in D6...
    Empty:= ((TVarData(r).VType = varDispatch) or (TVarData(r).VType = varUnknown)) and (TVarData(r).VDispatch = nil);
    if not Empty then r.EntireRow.Delete(Integer(xlShiftUp));
  until Empty;
end;

procedure TExcelReport.RefreshPivotTables;
var
  k, l, m: integer;
  Pvts, Pvt, PvtField, PvtItem: OleVariant;
begin
  Pvts:=FExcelWorksheet.PivotTables;
  for k:=1 to Pvts.Count do
  begin
    Pvt:= FExcelWorksheet.PivotTables(k, LCID);
    FExcelApplication.DisplayAlerts[LCID]:=False; //Here there is a warning we dont want...
    try
      Pvt.PivotCache.Refresh;
    except
      //Nothing, probably there are no rows
    end; //Except
    FExcelApplication.DisplayAlerts[LCID]:=DisplayAlerts;

    for l:=1 to Pvt.PivotFields.Count do
      begin
        PvtField:= Pvt.PivotFields(l);
        for m:=PvtField.PivotItems.Count downto 1 do
        begin
          try
            PvtItem:=PvtField.PivotItems(m);
            if (PvtItem.RecordCount = 0) and not (PvtItem.IsCalculated) then PvtItem.Delete;
          except
            //Nothing
          end; //except
        end;
      end;
  end;
end;

procedure TExcelReport.Run;
var
  OldCursor: TCursor;
  i, FirstPage:integer;
  SaveFB: TExcelSaveFormatBasic;
  SaveFE: TExcelSaveFormatExtended;
  aFileName: TFileName;
begin
  OldCursor := Screen.Cursor;
  Screen.Cursor := crHourGlass;
  try
    LCID :=   LOCALE_USER_DEFAULT;

    // Try to connect to Excel and create new Worksheet
    FExcelApplication.ConnectKind := ckRunningOrNew;
    FExcelApplication.Connect;

    FExcelApplication.DisplayAlerts[LCID] := DisplayAlerts;
    try
      FExcelWorkbook.ConnectTo(FExcelApplication.Workbooks.Add(FTemplate, LCID));
      try
        FExcelApplication.Visible[LCID]:=false;
        FExcelApplication.ScreenUpdating[LCID] := false;

        if Assigned (FOnBeforeGenerateWorkbook) then FOnBeforeGenerateWorkbook(Self, FExcelApplication, FExcelWorkbook, LCID);

        FirstPage:=(FExcelWorkbook.ActiveSheet as _Worksheet).Index[LCID];
        if Assigned(FPagesDataSet) then InsertPages;

        for i:=1 to FExcelWorkbook.Worksheets.Count do
        begin
          FExcelWorksheet.ConnectTo(FExcelWorkbook.Worksheets[i] as _Worksheet);

          CurrentPage:=i;
          if Assigned (FOnBeforeGeneratePage) then FOnBeforeGeneratePage(Self, FExcelApplication, FExcelWorkbook, FExcelWorksheet, LCID);
          ExportFieldData;
          if Assigned(FPagesDataSet) and (i>=FirstPage) then
          begin
            if not FPagesDataSet.Eof then
            begin
              if (FPagesDataField<>'') then FExcelWorksheet.Name:=FPagesDataSet.FieldByName(FPagesDataField).Value;
              FPagesDataSet.Next;
            end;
          end;

          DeleteMarkedRows;
          RefreshPivotTables;
          if Assigned (FOnAfterGeneratePage) then FOnAfterGeneratePage(Self, FExcelApplication, FExcelWorkbook, FExcelWorksheet, LCID);

        end; //For each page

        (FExcelWorkbook.Worksheets.Item [1] as _Worksheet).Activate(LCID);

        if Assigned (FOnAfterGenerateWorkbook) then FOnAfterGenerateWorkbook(Self, FExcelApplication, FExcelWorkbook, LCID);

        if (FAutoClose) then
        begin
          for SaveFB:= Low(TExcelSaveFormatBasic) to High(TExcelSaveFormatBasic) do
            if SaveFB in FSaveFormatBasic then
            begin
              aFileName:=FFilename;
              if Assigned (FOnGetFileName) then FOnGetFileName(Self,SaveFormatBasicConvert[SaveFB],aFilename);
              FExcelWorkbook.SaveAs(aFileName,SaveFormatBasicConvert[SaveFB],EmptyParam,EmptyParam,EmptyParam,EmptyParam,xlExclusive,EmptyParam,EmptyParam,EmptyParam,EmptyParam,LCID);
            end;
          for SaveFE:= Low(TExcelSaveFormatExtended) to High(TExcelSaveFormatExtended) do
            if SaveFE in FSaveFormatExtended then
            begin
              aFileName:=FFilename;
              if Assigned (FOnGetFileName) then FOnGetFileName(Self,SaveFormatExtendedConvert[SaveFE],aFilename);
              FExcelWorkbook.SaveAs(aFileName,SaveFormatExtendedConvert[SaveFE],EmptyParam,EmptyParam,EmptyParam,EmptyParam,xlExclusive,EmptyParam,EmptyParam,EmptyParam,EmptyParam,LCID);
            end;
        end
      except
        FExcelWorkbook.Close(False);
        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
           FExcelWorkbook.Close(False);  //This wont quit excel, but when the app finishes it will quit too.
        except
          //nothing
        end; //Except

    finally
      FExcelApplication.ScreenUpdating[LCID] := true;
      FExcelApplication.Visible[LCID]:=true;

      FExcelWorksheet.Disconnect;
      FExcelWorkbook.Disconnect;
      FExcelApplication.Disconnect;
    end; //finally
  finally
    Screen.Cursor := OldCursor;
  end;
end;

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

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

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

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

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


{ TExcelBand }

constructor TExcelBand.Create;
begin
  inherited;
end;

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

end.