{********************************************}
{ TeeChart Excel Series data importing       }
{ Copyright (c) 2000-2020 by Steema Software }
{ All Rights Reserved.                       }
{********************************************}
unit TeeExcelSource;
{$I TeeDefs.inc}

interface

uses
  {$IFNDEF LINUX}
  Windows, Messages,
  {$ENDIF}
  SysUtils, Classes,

  {$IFDEF D16}
  System.UITypes,
  {$ENDIF}

  {$IFDEF D17}
  System.Types,
  {$ENDIF}

  Graphics, Controls, Forms, Dialogs, StdCtrls, Buttons, ExtCtrls,
  TeeURL, TeEngine, TeeSourceEdit, TeCanvas, TeeProcs, TeeStore,
  Chart;

type
  TTeeExcelSource=class(TTeeSeriesSourceFile)
  private
    FExcel     : Variant;
    FLabelsFrom: String;
    FLabelsTo  : String;
    FRangeFrom : String;
    FRangeTo   : String;
    FWorkSheet : String;

    FRunning   : Boolean;
    WorkBook   : OleVariant;
    Procedure OpenWorkBook;
    procedure SetLabelsFrom(const Value: String);
    procedure SetLabelsTo(const Value: String);
    procedure SetRangeFrom(const Value: String);
    procedure SetRangeTo(const Value: String);
    procedure SetWorkSheet(const Value: String);
  public
    Procedure Close; override;

    class Function Description:String; override;
    class Function Editor:TComponentClass; override;

    Procedure Load; override;
  published
    property Active;
    property Chart;
    property FileName;
    property LabelsFrom:String read FLabelsFrom write SetLabelsFrom;
    property LabelsTo:String read FLabelsTo write SetLabelsTo;
    property RangeFrom:String read FRangeFrom write SetRangeFrom;
    property RangeTo:String read FRangeTo write SetRangeTo;
    property Series;
    property WorkSheet:String read FWorkSheet write SetWorkSheet;
  end;

  TSeriesDataXLSObject=class(TSeriesData)
  public
    Procedure SaveToFile(Const FileName:String); override;
  end;

  TTeeExcelExport = class (TSeriesData)
  private
    FExcel     : Variant;
    FWorkSheet : OleVariant;

    FFileName : String;

    FRunning   : Boolean;
    WorkBook   : OleVariant;

    procedure SetFileName(const Value:String);
  protected
    Procedure OpenWorkBook;
  public
    procedure SaveChart(const Format:TeeFormatFlag);
    property FileName:String read FFileName write SetFileName;
  end;

  TExcelSourceForm = class(TBaseSourceEditor)
    LabelFile: TLabel;
    EFileName: TEdit;
    LabelSheet: TLabel;
    CBWorkSheet: TComboFlat;
    GroupBox1: TGroupBox;
    Label3: TLabel;
    EValFrom: TEdit;
    Label4: TLabel;
    EValTo: TEdit;
    GroupBox2: TGroupBox;
    Label5: TLabel;
    Label6: TLabel;
    ELabFrom: TEdit;
    ELabTo: TEdit;
    OpenDialog1: TOpenDialog;
    Button1: TButton;
    procedure CBWorkSheetChange(Sender: TObject);
    procedure EFileNameChange(Sender: TObject);
    procedure EValFromChange(Sender: TObject);
    procedure BApplyClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
    Procedure CheckControls;
    Procedure FillWorkSheets;
  public
    { Public declarations }
  end;

implementation

{$IFNDEF LCL}
{$R *.DFM}
{$ELSE}
{$R *.lfm}
{$ENDIF}

uses {$IFNDEF LINUX}
     ActiveX,
     {$ENDIF}

     {$IFDEF D6}
     Variants,
     {$ENDIF}

     TeeConst, TeeEdiSeri, ComObj, TeExport;

Procedure TExcelSourceForm.FillWorkSheets;
var t : Integer;
begin
  CBWorkSheet.Clear;

  Screen.Cursor:=crHourGlass;

  With TTeeExcelSource.Create(nil) do
  try
    FileName:=EFileName.Text;
    OpenWorkBook;

    for t:=1 to WorkBook.Worksheets.Count do
        CBWorkSheet.Items.Add(WorkBook.Worksheets.Item[t].Name);
  finally
    Screen.Cursor:=crDefault;
    
    Free;

    CBWorkSheet.Enabled:=CBWorkSheet.Items.Count>0;
  end;
end;

Procedure TExcelSourceForm.CheckControls;
begin
  CBWorkSheet.Enabled:=(EFileName.Text<>'') and FileExists(EFileName.Text);
end;

procedure TExcelSourceForm.CBWorkSheetChange(Sender: TObject);
begin
  EnableControls(True,[ELabFrom,ELabTo,EValFrom,EValTo]);
  BApply.Enabled:=True;
end;

procedure TExcelSourceForm.EFileNameChange(Sender: TObject);
begin
  BApply.Enabled:=True;
end;

procedure TExcelSourceForm.EValFromChange(Sender: TObject);
begin
  BApply.Enabled:=True;
end;

procedure TExcelSourceForm.BApplyClick(Sender: TObject);
var tmpSheet : String;
begin
  tmpSheet:=CBWorkSheet.CurrentItem;

  if EFileName.Text='' then
  begin
    EFileName.SetFocus;
    Raise Exception.Create(TeeMsg_ExcelEmptyFile);
  end;

  if tmpSheet='' then
     Raise Exception.Create(TeeMsg_ExcelSheetEmpty);

  if (EValFrom.Text='') or (EValTo.Text='') then
     Raise Exception.Create(TeeMsg_ExcelRangeMissing);

  Screen.Cursor:=crHourGlass;

  with TTeeExcelSource(IDataSource) do
  try
    Close;

    FileName:=EFileName.Text;
    WorkSheet:=tmpSheet;
    RangeFrom:=EValFrom.Text;
    RangeTo:=EValTo.Text;
    LabelsFrom:=ELabFrom.Text;
    LabelsTo:=ELabTo.Text;

    CheckReplaceSource(IDataSource);

    Open;

    BApply.Enabled:=False;
  finally
    Screen.Cursor:=crDefault;
  end;
end;

procedure TExcelSourceForm.FormShow(Sender: TObject);
begin
  inherited;

  CheckDataSource(TTeeExcelSource);

  With TTeeExcelSource(IDataSource) do
  begin
    EFileName.Text:=FileName;
    EValFrom.Text:=RangeFrom;
    EValTo.Text:=RangeTo;
    ELabFrom.Text:=LabelsFrom;
    ELabTo.Text:=LabelsTo;

    if FileName<>'' then
    begin
      FillWorkSheets;

      if WorkSheet<>'' then
      begin
        CBWorkSheet.ItemIndex:=CBWorkSheet.Items.IndexOf(WorkSheet);
        CBWorkSheetChange(Self);
      end;
    end;
  end;

  BApply.Enabled:=Assigned(TheSeries) and (TheSeries.DataSource<>IDataSource);
end;

{ TTeeExcelSource }
Procedure TTeeExcelSource.OpenWorkBook;
Const
  ExcelClass='Excel.Application'; // Do not localize

  Function IsExcelRunning:Boolean;
  var ClassID: TCLSID;
      Unknown: IUnknown;
  begin
    ClassID := ProgIDToClassID(ExcelClass);
    result:=Succeeded(GetActiveObject(ClassID, nil, Unknown));
  end;

begin
  FRunning:=IsExcelRunning;

  if FRunning then FExcel:=GetActiveOleObject(ExcelClass)
              else FExcel:=CreateOleObject(ExcelClass);

  WorkBook:=FExcel.WorkBooks.Open(FileName  {,ReadOnly:=True});
end;

Procedure TTeeExcelSource.Load;

  Procedure GetRangeFromList(List:TChartValueList; Var FromPart,ToPart:String);
  var s : String;
      i : Integer;
  begin
    FromPart:='';
    ToPart:='';

    if List.ValueSource<>'' then
    begin
      s:=List.ValueSource;
      i:=Pos(':',s); // Do not localize

      if i>0 then
      begin
        FromPart:=Copy(s,1,i-1);
        ToPart:=Copy(s,i+1,Length(s));
      end;
    end;
  end;

var WS       : OleVariant;
    tmp      : Variant;
    List,
    t,
    n,
    tmpLow,
    tmpHigh  : Integer;
    tmpRangeFrom,
    tmpRangeTo : String;
begin
  if FileName='' then
     Raise Exception.Create(TeeMsg_ExcelEmptyFile);

  OpenWorkBook;

  WS:=WorkBook.Worksheets.Item[WorkSheet];
  WS.Activate;

  if RangeFrom='' then
     GetRangeFromList(Series.MandatoryValueList,FRangeFrom,FRangeTo);

  tmp:=WS.Range[RangeFrom, RangeTo].Value;
  if VarIsArray(tmp) then
  begin
    Series.Clear;

    n:=VarArrayDimCount(tmp);
    tmpLow:=VarArrayLowBound(tmp,n);
    tmpHigh:=VarArrayHighBound(tmp,n);
    Dec(n);

    if (tmpHigh-tmpLow)=0 then
    begin
      tmpLow:=VarArrayLowBound(tmp,n);
      tmpHigh:=VarArrayHighBound(tmp,n);
      if tmpHigh>=tmpLow then
      for t:=tmpLow to tmpHigh do
          Series.Add(tmp[t,n]);
    end
    else
      for t:=tmpLow to tmpHigh do
          Series.Add(tmp[n,t]);

    tmp:=UnAssigned;

    // Import additional Value Lists (ie: XYZ in Surface)
    for List:=0 to Series.ValuesList.Count-1 do
    if Series.ValuesList[List]<>Series.MandatoryValueList then
    begin
      GetRangeFromList(Series.ValuesList[List],tmpRangeFrom,tmpRangeTo);

      if (tmpRangeFrom<>'') and (tmpRangeTo<>'') then
      begin
        tmp:=WS.Range[tmpRangeFrom, tmpRangeTo].Value;

        if VarIsArray(tmp) then
        begin
          n:=VarArrayDimCount(tmp);
          tmpLow:=VarArrayLowBound(tmp,n);
          tmpHigh:=VarArrayHighBound(tmp,n);
          Dec(n);

          if (tmpHigh-tmpLow)=0 then
          begin
            tmpLow:=VarArrayLowBound(tmp,n);
            tmpHigh:=VarArrayHighBound(tmp,n);
            if tmpHigh>=tmpLow then
            for t:=tmpLow to tmpHigh do
                Series.ValuesList[List].Value[t-tmpLow]:=tmp[t,n];
          end
          else
            for t:=tmpLow to tmpHigh do
                Series.ValuesList[List].Value[t-tmpLow]:=tmp[n,t];
        end;
      end;
    end;

    { load label text }
    if (LabelsFrom<>'') and (LabelsTo<>'') then
    begin
      tmp:=WS.Range[LabelsFrom, LabelsTo].Value;

      if VarIsArray(tmp) then
      begin
        tmpLow:=VarArrayLowBound(tmp,1);
        tmpHigh:=VarArrayHighBound(tmp,1);

        if tmpHigh>=tmpLow then
        for t:=tmpLow to tmpHigh do
            Series.Labels[t-1]:=tmp[t,1];
      end;
    end;
  end;
end;

Procedure TTeeExcelSource.Close;
begin
  if not VarIsEmpty(FExcel) then
  begin
    if not FRunning then FExcel.Quit;
    FExcel:=UnAssigned;
    FRunning:=False;
  end;

  inherited;
end;

procedure TTeeExcelSource.SetRangeFrom(const Value: String);
begin
  if FRangeFrom<>Value then
  begin
    FRangeFrom:=Value;
    Close;
  end;
end;

procedure TTeeExcelSource.SetRangeTo(const Value: String);
begin
  if FRangeTo<>Value then
  begin
    FRangeTo:=Value;
    Close;
  end;
end;

procedure TTeeExcelSource.SetWorkSheet(const Value: String);
begin
  if FWorkSheet<>Value then
  begin
    FWorkSheet:=Value;
    Close;
  end;
end;

procedure TTeeExcelSource.SetLabelsFrom(const Value: String);
begin
  if FLabelsFrom<>Value then
  begin
    FLabelsFrom:=Value;
    Close;
  end;
end;

procedure TTeeExcelSource.SetLabelsTo(const Value: String);
begin
  if FLabelsTo<>Value then
  begin
    FLabelsTo:=Value;
    Close;
  end;
end;

class function TTeeExcelSource.Description: String;
begin
  result:=TeeMsg_ExcelFile;
end;

class function TTeeExcelSource.Editor: TComponentClass;
begin
  result:=TExcelSourceForm;
end;

procedure TExcelSourceForm.FormCreate(Sender: TObject);
begin
  inherited;
  LLabel.Visible:=False;
  CBSources.Visible:=False;
end;

procedure TExcelSourceForm.Button1Click(Sender: TObject);
begin
  if OpenDialog1.Execute then
  begin
    EFileName.Text:=OpenDialog1.FileName;
    FillWorkSheets;
    CheckControls;
  end;
end;

{ TTeeExcelExport }
procedure TTeeExcelExport.OpenWorkBook;
Const
  ExcelClass='Excel.Application'; // Do not localize

  Function IsExcelRunning:Boolean;
  var ClassID: TCLSID;
      Unknown: IUnknown;
  begin
    ClassID := ProgIDToClassID(ExcelClass);
    result:=Succeeded(GetActiveObject(ClassID, nil, Unknown));
  end;

begin
  FRunning:=IsExcelRunning;

  if FRunning then FExcel:=GetActiveOleObject(ExcelClass)
              else FExcel:=CreateOleObject(ExcelClass);

  WorkBook:=FExcel.WorkBooks.Add;
end;


Function ColorToHex(const Color:TColor):String;
var tmp : Byte;
begin
  tmp:=TTeeCanvas.ColorAlpha(Color);

  with RGBValue(Color) do
  if tmp<>0 then
     result:=Format('#%.2x%.2x%.2x%.2x',[tmp,Red,Green,Blue])
  else
     result:=Format('#%.2x%.2x%.2x',[Red,Green,Blue]);
end;

procedure TTeeExcelExport.SaveChart(const Format : TeeFormatFlag);

  function ConvertToLetter(iCol : Integer) : String;
  var Value  : Integer;
  Begin
    result:='';

    Value:=iCol - 1;

    while Value > 25 do
    begin
      result:=Chr(65 + (Value Mod 26)) + result;
      Value := Trunc(Value / 26) - 1;
    end;

    result:=Chr(65 + Value) + result;
  end;

  procedure WriteNull;
  begin
    //WriteParams(1,0);
  end;

  procedure WriteCell(Col, Row : Integer; Value: OleVariant);
  Begin
    FWorkSheet.Range[ConvertToLetter(Col)+IntToStr(Row)].Value := Value;
  end;

var
  Row,
  Col,
  tt,
  currRow,
  currCol : Integer;

  Procedure WriteSeries(const ASeries:TChartSeries);

    procedure WriteValue(const AList:TChartValueList);
    var tmp : String;
    begin
      if UseSeriesFormat then
         tmp:=ASeries.ValueFormat
      else
         tmp:=ValueFormat;

      if tmp='' then
         WriteCell(Col,Row,AList.Value[currRow])
      else
         WriteCell(Col,Row,StrToFloat(ValueToString(ASeries,AList.Value[currRow])))
    end;

  var tt : Integer;
  begin
    if (ASeries.Count-1)<currRow then
    begin
      if tfLabel in IFormat then
      begin
        WriteCell(Col,Row,'');
        Inc(Col);
      end;

      if tfColor in IFormat then
      begin
        WriteCell(Col,Row,'');
        Inc(Col);
      end;

      if tfNoMandatory in IFormat then
      begin
        WriteCell(Col,Row,'');
        Inc(Col);
      end;

      for tt:=1 to ASeries.ValuesList.Count-1 do
      begin
        WriteCell(Col,Row,'');
        Inc(Col);
      end;
    end
    else
    begin
      if tfLabel in IFormat then
      begin
        WriteCell(Col,Row,ASeries.Labels[currRow]);
        Inc(Col);
      end;

      if tfColor in IFormat then
      begin
        WriteCell(Col,Row,ColorToHex(ASeries.ValueColor[currRow]));
        Inc(Col);
      end;

      if tfNoMandatory in IFormat then
      begin
        WriteValue(ASeries.NotMandatoryValueList);
        Inc(Col);
      end;

      if ASeries.IsNull(Row) then
      for tt:=1 to ASeries.ValuesList.Count-1 do
      begin
        WriteNull;
        Inc(Col);
      end
      else
      begin
        WriteValue(ASeries.MandatoryValueList);

        if (ASeries.ValuesList.Count > 2) then
          Inc(Col);

        for tt:=2 to ASeries.ValuesList.Count-1 do
        begin
          WriteValue(ASeries.ValuesList[tt]);
          if (tt <> ASeries.ValuesList.Count-1) then
            Inc(Col);
        end;
      end;
    end;
  end;

  Procedure WriteHeaderSeries(const ASeries:TChartSeries);
  var tt : Integer;
  begin
    if tfLabel in IFormat then
    begin
      WriteCell(Col,Row,TeeMsg_Text);
      Inc(Col);
    end;

    if tfColor in IFormat then
    begin
      WriteCell(Col,Row,TeeMsg_Colors);
      Inc(Col);
    end;

    { the "X" point value }
    if tfNoMandatory in IFormat then
    begin
      WriteCell(Col,Row,ASeries.NotMandatoryValueList.Name);
      Inc(Col);
    end;

    { the "Y" point value }
    WriteCell(Col,Row,SeriesTitleOrName(ASeries));
    Inc(Col);

    for tt:=2 to ASeries.ValuesList.Count-1 do
    begin
      WriteCell(Col,Row,ASeries.ValuesList[tt].Name);
      Inc(Col);
    end;
  end;

begin
  Row := 1;
  Col := 1;
  iFormat := format;
  Prepare(Series);
  try
    OpenWorkBook;

    FWorkSheet := WorkBook.Worksheets.Item[1]; // go to first worksheet
    FWorkSheet.Activate;
    FWorkSheet.Select;

    if IncludeHeader then
    begin
     if IncludeIndex then
      begin
       FWorkSheet.Range[ConvertToLetter(Col)+IntToStr(Row)].Value := TeeMsg_Index;
       inc(Col);
      end;

     if Assigned(Series) then
        WriteHeaderSeries(Series)
     else
     for tt:=0 to Chart.SeriesCount-1 do
         if ValidSeries(Chart[tt]) then
            WriteHeaderSeries(Chart[tt]);
    end
    else
      Row := 0;

    for currRow:=0 to MaxSeriesCount-1 do
    begin
      currCol:= 0;
      col := 1;
      Inc(Row);

      if IncludeIndex then
      begin
        WriteCell(currCol+Col,Row,currRow);
        Inc(currCol);
      end;

      if Assigned(Series) then
         WriteSeries(Series)
      else
      for tt:=0 to Chart.SeriesCount-1 do
          if ValidSeries(Chart[tt]) then
          Begin
             Col := currCol + 1;
             WriteSeries(Chart[tt]);
             currCol := Col;
          end;
    end;

    WorkBook.SaveAs(FileName);
  finally
    WorkBook.Close(True);
    FWorkSheet := Unassigned;
    WorkBook := Unassigned;
    FExcel := Unassigned;
  end;
end;

procedure TTeeExcelExport.SetFileName(const Value: String);
begin
  if FFileName<>Value then
  begin
    //Close;
    FFileName:=Value;
  end;
end;

{ TSeriesDataXLSObject }
procedure TSeriesDataXLSObject.SaveToFile(const FileName: String);
var objExport : TTeeExcelExport;
begin
  objExport := TTeeExcelExport.Create(Chart,Series);
  try
    objExport.IncludeIndex:=IncludeIndex;
    objExport.IncludeHeader:=IncludeHeader;
    objExport.IncludeColors:=IncludeColors;
    objExport.IncludeLabels:=IncludeLabels;

    objExport.FileName:=FileName;

    objExport.SaveChart(iFormat);
  finally
    objExport.Free;
  end;
end;

initialization
  TeeExcelDataObject:=TSeriesDataXLSObject; // Excel as COM Object

  TeeActivateGroup;
  RegisterClass(TTeeExcelSource);
  TeeSources.Add(TTeeExcelSource);
finalization
  TeeExcelDataObject:=nil;
  
  TeeSources.Remove(TTeeExcelSource);
end.

