//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop
//---------------------------------------------------------headers BC++5
#include <dir.h>		//ffblk
#include <stdio.h>		//sprintf
#include <sysvari.h>	//variant et olevariant
//---------------------------------------------------------------------------
#include "Excel97.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "Excel_97_SRVR"
#pragma resource "*.dfm"
TFormExcel97 *FormExcel97;
//---------------------------------------------------------------------------
__fastcall TFormExcel97::TFormExcel97(TComponent* Owner)
    : TForm(Owner)
{
}
///////////////////////////////////////////////////////////////////////
//Gérard Colo, from LOGIC SYSTEMS in Paris
//logicsyst@aol.com
//29/12/2000
//
//A lot of litterature about automating MS Office applications using VBA
//can be found for people working under MS Visual Basic ; a little less,
//but interesting material are still available for Delphi users ;
//but very few, as far as I know, for BC++5 environment.
//Thanks to the Borland Community members who showed me the right
//direction through code examples and articles, I succeeded in creating
//an automation server for Excel 97 under Borland C++Builder 5.
//Now, it's my turn, to post the following lines to demonstrate
//how to adapt VBA methods.
//
//I)  . It could be a good thing to add to the Menu Tools of your editor
//      an entry towards "C:\Program Files\Microsoft Office\Office\Vbaxl8.hlp",
//      the MS Help for Excel97.
//II) . to understand the architecture and the syntax of properties and
//      functions related to Excel, I've made a large use of the Classes
//      explorer of BC++5 Editor : I suggest you to do so.
//III). create a form under BC++5 (here, Excel97.cpp / FormExcel97.dfm)
//    . put a TButton "Open Excel" (here, ButOpen)
//    . and a TButton "Close Excel" (here, ButClose)
//    . add a component "ExcelApplication" from the "Servers" tag
//    . develop the OnClick events of your buttons like below.
//IV) . that's all. Click "Open Excel" on your form :
//    . the file "c:\tmp\toto.xls" is created or opened,
//    . the workbook is activated, with 3 worksheets (my default)
//    . the sheets are named or renamed "toto1" "toto2" "toto3"
//    . the 2nd one is activated
//    . the cells A1:C3 are selected
//    . the cell B2 is activated
//    . and filled with a value
//V)  . My e-mail is opened for any comment or suggestion ! (logicsyst@aol.com)
//
/////////////////////////////////////////////////////////////////////////
//---------------------------------------------------------------------------
void __fastcall TFormExcel97::FormCreate(TObject *Sender)
{
    excel97=false;      //a private bool to check if Excel is already opened
}
//---------------------------------------------------------------------------
void __fastcall TFormExcel97::FormDestroy(TObject *Sender)
{
    ButClose->Click();  //don't let Excel opened when leaving
}
//---------------------------------------------------------------------------
void __fastcall TFormExcel97::ButOpenClick(TObject *Sender)
{
    if  (excel97)
        return;
//
    int i,nbSheet;
    char s[257];
    bool okWb;
//Variables for Excel
    Excel_97::WorkbooksPtr books;
    Excel_97::SheetsPtr    sheets;
    Excel_97::RangePtr     cells;
    _WorkbookPtr           wb;            //ExcelWorkbookPtr wb;
    _WorksheetPtr          ws;            //ExcelWorksheetPtr ws;
    TVariant wbName,
             wsName;
    TVariantInParam readOnly,               //bool
                    numSheet,intValue,      //int
                    Cell1,Cell2,strValue;   //string

//opening ExcelApplication
    try {
        ExcelApplication->Connect();                        //okXls
        books=ExcelApplication->Get_Workbooks();            //okXls
        ExcelApplication->Set_Visible(TDefLCID(),true);     //okXls
        excel97=true;
    }
    catch (Exception &exception)    {
            Application->MessageBox(
                "This application needs Excel 97 or more",
                "Sorry...",
                MB_ICONSTOP);
            abort;
    }

//Opening a Workbook in Excel

    strcpy(s,"c:\\tmp\\toto.xls");
    okWb=_ChkFile(s);   //returns true if the above file already exists
    wbName=s;
    try {
        if  (okWb)  {
//Open the existing Excel Workbook
    //following line to open with defaults values
            wb=books->Open(wbName);
    //following 2 lines to show how to proceed with others values, readOnly for instance
    //      readOnly=true;
    //      wb=books->Open(wbName,TNoParam(),readOnly);
        }
        else    {
//Or create a new Excel Workbook
            wb=books->Add();
            wb->SaveAs(wbName,
                TNoParam(),TNoParam(),TNoParam(),TNoParam(),TNoParam(),
                xlNoChange/*XlSaveAsAccessMode*/,
                TNoParam(),TNoParam(),TNoParam(),TNoParam(),TDefLCID());
        }
//Give a name to the Excel main windows, for instance the path of the Workbook
        ExcelApplication->Caption=wbName;
    //or explicitely
    //  ExcelApplication->Caption=TVariant("Toto");

//Recover the collection of Worksheets
        sheets=wb->get_Sheets();
    //and give each Worksheet a name
        nbSheet=sheets->Count;
        for (i=1;i<=nbSheet;i++) {       // Worksheets are indexed from 1, not 0
            sprintf(s,"Toto %d",i);
            wsName=s;
            numSheet=i;
            ws=sheets->get_Item(numSheet);
            ws->set_Name(wsName);
        }
    //to print all the sheets at the same time, try something like the following paragraph
    //        if  (sheets)    {
    //                sheets->PrintOut(1,10,
    //                           TNoParam(),
    //                           TNoParam(),
    //                           TNoParam(),
    //                           TNoParam(),
    //                           TNoParam(),
    //                           TDefLCID());
    //        }

//Activate the 2nd Worksheet
        numSheet=2;
        ws=sheets->get_Item(numSheet);
        ws->Activate(TDefLCID());
    //select range of cells A1:C3
        Cell1="A1";
        Cell2="C3";
        cells=ws->get_Range(Cell1,Cell2);
        cells->Select();                    //note : get_Range + Select to select a range
        Cell1="B2";
        cells=ws->get_Range(Cell1,TNoParam());
        cells->Activate();                  //note : get_Range + Activate to activate a cell
    //Put an int in the activated cell
        intValue=1234;
        cells->set_Value(intValue);
    //or try the following for a string
    //    strValue="Hi, Toto !";
    //    cells->set_Value(strValue);
    }
    catch (Exception &exception)    {
            Application->MessageBox(
                "Sure, there remains a problem anywhere !",
                "Sorry...",
                MB_ICONSTOP);
            abort;
        }
}
//---------------------------------------------------------------------------
void __fastcall TFormExcel97::ButCloseClick(TObject *Sender)
{
    if  (excel97)
        ExcelApplication->Disconnect();
    excel97=false;
}
//---------------------------------------------------------------------------
void __fastcall TFormExcel97::ButExitClick(TObject *Sender)
{
    ModalResult=mrOk;
}
//---------------------------------------------------------------------------
bool __fastcall TFormExcel97::_ChkFile(char *s)
{
	ffblk bloc;
	return (findfirst(s,&bloc,FA_NORMAL)==0);
}
//---------------------------------------------------------------------------