//---------------------------------------------------------------------------
// ExcelRemoteControl.h, Author: Holger Mackeldey
//---------------------------------------------------------------------------
#ifndef ExcelRemoteControlH
#define ExcelRemoteControlH
//---------------------------------------------------------------------------
#include <Classes.hpp>
#include <Controls.hpp>
#include <StdCtrls.hpp>
#include <Forms.hpp>
//---------------------------------------------------------------------------


class TExcelOLE : public TForm
{
  private:
    Variant m_vExcel;         // OLE Schnittstelle zu Excel
    Variant m_vWorkbooks_00;  // OLE Schnittstelle zur "Ur-Arbeitsmappe"
                              // aus der werden andere erzeugt
    Variant m_vWorksheets;    // OLE Schnittstelle zu Tabellen
    Variant m_vCharts;        // OLE Schnittstelle zu Diagrammen

  __published:  // Komponenten, die von der IDE verwaltet werden
        TButton *TestExcelOLE;
        void __fastcall TestExcelOLEClick(TObject *Sender);
  private:  // Benutzerdeklarationen

  public:    // Benutzerdeklarationen
            __fastcall TExcelOLE(TComponent* Owner);
    Variant __fastcall OpenExcelOLE(bool bVisible);
    Variant __fastcall CreateWorkbook( DWORD dwWorkbooknumber );
    Variant __fastcall CreateNewTable( Variant vWorkbook,char* cTablename );
    Variant __fastcall GetTableByName( Variant vWorkbook,char* cTablename );
    void    __fastcall WriteTableCell( Variant vWorkbook,char*
      cWorksheetName,DWORD dwRow,DWORD dwCol,Variant vValue );
    Variant __fastcall ReadTableCell( Variant vWorkbook,char*
      cWorksheetName,DWORD dwRow,DWORD dwCol );
    void    __fastcall FormulaOnRange( Variant vWorkbook,char*
      cWorksheetName,AnsiString asRange,AnsiString asFormula );
    void    __fastcall ColourOnRange( Variant vWorkbook,char*
      cWorksheetName,AnsiString asRange,Variant ColourIndex );
    void    __fastcall SetColumnWidth( Variant vWorkbook,char*
      cWorksheetName,AnsiString asRange, Variant ColumnWidth );
    void    __fastcall SaveWorkbook( Variant vWorkbook,
      AnsiString SavePath, bool bConfirm );
    void    __fastcall TerminateExcel();
    bool    __fastcall CellBorderSet( Variant vWorkbook,char*
      cWorksheetName,AnsiString &CellName, int Border_Code,int Line_Style );
    bool    __fastcall BookOpen(AnsiString &BookName);
    bool    __fastcall BookClose(AnsiString &BookName);
};

//---------------------------------------------------------------------------
extern PACKAGE TExcelOLE *ExcelOLE;
//---------------------------------------------------------------------------

#endif

//---------------------------------------------------------------------------
// ExcelRemoteControl.cpp, Author: Holger Mackeldey
//---------------------------------------------------------------------------

#include <vcl.h>
#include <stdio.h>
#include <comobj.hpp>   // für OLE
#include <utilcls.h>    // für OLE
#pragma hdrstop

#include "ExcelRemoteControl.h"

//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"

TExcelOLE *ExcelOLE;

// Borderdefines
#define BLeft    2
#define BRight   4
#define BTop     8
#define BBottom 16

#define LNone    0
#define LSingle  1
#define LDouble  9
#define LBold    7

//---------------------------------------------------------------------------
__fastcall TExcelOLE::TExcelOLE(TComponent* Owner)
        : TForm(Owner)
{
}

/***************************************************************************
 Function: Ereignisroutine der Testbuttons "TestExcelOLE"
 Author: Holger Mackeldey
****************************************************************************/
void __fastcall TExcelOLE::TestExcelOLEClick(TObject *Sender)
{
  AnsiString asExcelFile = "Test.xls";
  OpenExcelOLE( true );  // öffnet Excel sichtbar
  Variant vWorkbook  = CreateWorkbook( 1 );
  Variant vWorksheet = CreateNewTable( vWorkbook,"Test" );

  for( DWORD dwIndex = 1; dwIndex < 10; dwIndex++ )
  {
    WriteTableCell( vWorkbook,"Test",dwIndex, 1, 33333 );
    WriteTableCell( vWorkbook,"Test",dwIndex, 2, 44444 );
    DWORD dwReadValue = ReadTableCell( vWorkbook,"Test",dwIndex, 1 );
  }

  ColourOnRange( vWorkbook,"Test","A1:A1",1 );
  ColourOnRange( vWorkbook,"Test","A2:A2",2 );
  ColourOnRange( vWorkbook,"Test","A3:A3",3 );
  ColourOnRange( vWorkbook,"Test","A4:A4",4 );
  ColourOnRange( vWorkbook,"Test","A5:A5",5 );
  ColourOnRange( vWorkbook,"Test","A6:A6",6 );
  FormulaOnRange( vWorkbook,"Test","C1:C1","=A1 + 1000" );
  CellBorderSet( vWorkbook,"Test","C1", BBottom,LDouble );
  SaveWorkbook( vWorkbook, asExcelFile, false );
  BookClose( asExcelFile );
  TerminateExcel();
}

/***************************************************************************
 Function: öffnet eine OLE Schnittstelle zu MS Excel entweder sichtbar oder
           unsichtbar
 Author: Holger Mackeldey
****************************************************************************/

Variant __fastcall TExcelOLE::OpenExcelOLE( bool bVisible )
{
  try
  {
    m_vExcel = CreateOleObject("Excel.Application.8");
    if( bVisible )
    {
      m_vExcel.OlePropertySet("Visible",true);
    }
    m_vWorkbooks_00 = m_vExcel.OlePropertyGet("Workbooks");
    return( m_vExcel );
  }

  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Öffnen der OLE-Schnittstelle" );
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
    return 0;
  }
}

/***************************************************************************
 Function: erzeugt einen neue Arbeitsmappe und gibt dessen Schnittstelle
           zurück
 Author: Holger Mackeldey
****************************************************************************/
Variant __fastcall TExcelOLE::CreateWorkbook( DWORD dwWorkbooknumber )
{
  try
  {
    m_vWorkbooks_00.OleProcedure("Add");
    return( m_vWorkbooks_00.OlePropertyGet("Item",dwWorkbooknumber) );
  }
  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Öffnen der OLE-Schnittstelle" );
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
    return 0;
  }
}

/***************************************************************************
 Function: öffnet eine existierende Arbeitsmappe
 Author: Holger Mackeldey
****************************************************************************/

bool __fastcall TExcelOLE::BookOpen(AnsiString &BookName)
{
  try
  {
    m_vWorkbooks_00 = m_vExcel.OlePropertyGet("Workbooks").OlePropertyGet("Open", BookName);
    return true;
  }
  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Öffnen der Arbeitsmappe \"%s\"", BookName.c_str());
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
    return false;
  }
}

/***************************************************************************
 Function: schließt die offene Arbeitsmappe
 Author: Holger Mackeldey
****************************************************************************/

bool __fastcall TExcelOLE::BookClose(AnsiString &BookName)
{
  try
  {
    m_vWorkbooks_00.OleProcedure("Close");
    return true;
  }
  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Schließen der Arbeitsmappe \"%s\"", BookName.c_str());
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
    return false;
  }
}

/***************************************************************************
 Function: erzeugt einen neue Arbeitsmappe und gibt dessen Schnittstelle
           zurück
 Author: Holger Mackeldey
****************************************************************************/
Variant __fastcall TExcelOLE::CreateNewTable( Variant vWorkbook,char* cTablename )
{
  try
  {
    Variant vWorksheets;
    Variant vWorksheet;
    vWorksheets = vWorkbook.OlePropertyGet("Worksheets") ;
    vWorksheets.OleProcedure("Add");
    vWorksheet = m_vExcel.OlePropertyGet( "ActiveSheet" );
    vWorksheet.OlePropertySet("Name",cTablename);
    return( vWorksheet );
  }
  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Erzeugen der Tabelle \"%s\"", cTablename);
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
    return 0;
  }
}



/***************************************************************************
 Function: gibt die Tabelle mit dem übergebenen Namen zurück
 Author: Holger Mackeldey
****************************************************************************/
Variant __fastcall TExcelOLE::GetTableByName( Variant vWorkbook,char* cTablename )
{
  try
  {
    Variant vWorksheet;
    Variant vWorksheets;

    vWorksheets = vWorkbook.OlePropertyGet("Worksheets") ;
    vWorksheet  = vWorksheets.OlePropertyGet("Item",cTablename);
    return( vWorksheet );
  }
  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Ermitteln der Tabelle \"%s\"", cTablename);
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
    return 0;
  }
}

/***************************************************************************
 Function: schreibt einen Wert in die angegebene Zelle
 Author: Holger Mackeldey
****************************************************************************/
void __fastcall TExcelOLE::WriteTableCell( Variant vWorkbook,char* cWorksheetName,DWORD dwRow, DWORD dwCol,Variant vValue )
{
  try
  {
    Variant vWorksheet = GetTableByName( vWorkbook,cWorksheetName );
    vWorksheet.OlePropertyGet("Cells").OlePropertyGet("Item",dwRow,dwCol).OlePropertySet("Value",vValue);
  }
  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Schreiben einer Zelle");
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
  }
}


/***************************************************************************
 Function: liest einen Wert aus der angegebenen Zelle
 Author: Holger Mackeldey
****************************************************************************/
Variant __fastcall TExcelOLE::ReadTableCell( Variant vWorkbook,char* cWorksheetName,DWORD dwRow, DWORD dwCol )
{
  try
  {
    Variant vWorksheet = GetTableByName( vWorkbook,cWorksheetName );
    Variant vValue = vWorksheet.OlePropertyGet("Cells").OlePropertyGet("Item",dwRow,dwCol).OlePropertyGet("Value");
    return( vValue );
  }
  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Lesen einer Zelle");
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
    return 0;
  }
}

/***************************************************************************
 Function: bastelt eine Formel in den angegebenen Bereich
 Author: Holger Mackeldey
****************************************************************************/
void __fastcall TExcelOLE::FormulaOnRange( Variant vWorkbook,char* cWorksheetName,
                                           AnsiString asRange, AnsiString asFormula )
{
  try
  {
    Variant vWorksheet = GetTableByName( vWorkbook,cWorksheetName );
    Variant vRange     = vWorksheet.OlePropertyGet("Range",asRange);
    vRange.OlePropertySet("Formula",asFormula);
  }
  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Erzeugen einer Formel über einen Bereich");
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
  }
}

/***************************************************************************
 Function: Färbt die angegebenen Zellen mit der übergebene Farbe ein
 Author: Holger Mackeldey
****************************************************************************/
void __fastcall TExcelOLE::ColourOnRange( Variant vWorkbook,char* cWorksheetName,
                                          AnsiString asRange, Variant ColourIndex )
{
  try
  {
    Variant vWorksheet = GetTableByName( vWorkbook,cWorksheetName );
    Variant vRange     = vWorksheet.OlePropertyGet("Range",asRange);
    vRange.OlePropertyGet("Interior").OlePropertySet("ColorIndex",ColourIndex);
  }
  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Ändern der Farbe eines Bereiches");
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
  }
}

/***************************************************************************
 Function: setzt die Breite iner Spalte
 Author: Holger Mackeldey
****************************************************************************/
void __fastcall TExcelOLE::SetColumnWidth( Variant vWorkbook,char* cWorksheetName,
                                           AnsiString asRange, Variant ColumnWidth )
{
  try
  {
    Variant vWorksheet = GetTableByName( vWorkbook,cWorksheetName );
    vWorksheet.OlePropertyGet("Columns",asRange).OlePropertySet("ColumnWidth",ColumnWidth);
  }
  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Ändern der Breite einer Spalte");
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
  }
}

/***************************************************************************
 Function: speichert die aktuelle Mappe mit oder ohne Bestätigungs-
           aufforderung
 Author: Holger Mackeldey
****************************************************************************/
void __fastcall TExcelOLE::SaveWorkbook( Variant vWorkbook, AnsiString SavePath, bool bConfirm )
{
  try
  {
    m_vExcel.OlePropertySet("DisplayAlerts",bConfirm);
    vWorkbook.OleProcedure("SaveAs",SavePath);
  }
  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Speichern einer Arbeitsmappe");
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
  }
}

/***************************************************************************
 Function: Ändert den Rahmen einer Zelle
 Author: Holger Mackeldey
****************************************************************************/
bool __fastcall TExcelOLE::CellBorderSet( Variant vWorkbook,char* cWorksheetName,
                                          AnsiString &CellName, int Border_Code,
                                          int Line_Style )
{
  Variant var_Cell;
  Variant vWorksheet = GetTableByName( vWorkbook,cWorksheetName );
  try
  {
    switch ( Line_Style )
    {
        case LSingle:
        {
          for( int i=1; i <= 4; i++ )
          {
            if( Border_Code & (2<<(i-1)))
              vWorksheet.OlePropertyGet("Range", CellName).
              OlePropertyGet("Borders", i).
              OlePropertySet("LineStyle", LSingle);
          }
          return true;
        }
        case LBold:
        {
          for( int i=1; i <= 4; i++ )
          {
            if( Border_Code & (2<<(i-1)))
            {
              var_Cell = vWorksheet.OlePropertyGet("Range", CellName);
              var_Cell.OlePropertyGet("Borders", i).
              OlePropertySet("LineStyle", LSingle);
              var_Cell.OlePropertyGet("Borders", i).
              OlePropertySet("LineStyle", LBold);
            }
          }
          return true;
        }
        case LDouble:
        {
          for( int i=1; i <= 4; i++ )
          {
            if( Border_Code & (2<<(i-1)))
              vWorksheet.OlePropertyGet("Range", CellName).
              OlePropertyGet("Borders", i).
              OlePropertySet("LineStyle", LDouble);
          }
          return true;
        }
        default:
        {
          for( int i=1; i <= 4; i++ )
          {
            if( Border_Code & (2<<(i-1)))
              vWorksheet.OlePropertyGet("Range", CellName).
              OlePropertyGet("Borders", i).
              OlePropertySet("LineStyle", Line_Style);
          }
          return true;
        }
    }
  }
  catch(...)
  {
    MessageBox( 0, "Fehler in CellBorderSet", "Fehler", MB_OK );
    return false;
  }
}

/***************************************************************************
 Function: beendet Excel
 Author: Holger Mackeldey
****************************************************************************/
void __fastcall TExcelOLE::TerminateExcel()
{
  try
  {
    m_vExcel.OleProcedure("Quit");
  }
  catch(...)
  {
    char cBuffer[255];
    sprintf( cBuffer, "Fehler beim Beenden von Excel");
    MessageBox( 0, cBuffer, "Fehler", MB_OK );
  }
}

//--------------------------------------------------------------------------