 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Brian
Guest
|
 Posted: Fri Sep 24, 2004 7:03 pm Post subject: How to read an Excel file |
 |
|
I am trying to read an Excel file I do not know where to begin I have already checked C++ Builder Developers Guide for TExcelApplication TExcelWorkBook TExcelWorksheet,there is no referance to them. Can you suggest a book that has example of how to read Excel file using builder 6.
Thanks
|
|
| Back to top |
|
 |
Paolo Borlandi
Guest
|
| Posted: Sun Sep 26, 2004 9:46 am Post subject: Re: How to read an Excel file |
|
|
"Brian" <MMI (AT) dpstats (DOT) com> ha scritto nel messaggio
news:41546f72$1 (AT) newsgroups (DOT) borland.com...
| Quote: |
I am trying to read an Excel file I do not know where to begin I have
already checked C++ Builder Developers Guide for TExcelApplication
TExcelWorkBook TExcelWorksheet,there is no referance to them. Can you
suggest a book that has example of how to read Excel file using builder 6.
Thanks
|
Some years ago I wrote a component to access an Excel file. I put here the
code hoping it can be useful. If you do not need to write a component you
can see the code on how to read and write data to excel.
Paolo
////////////////////////////////// .HPP
//---------------------------------------------------------------------------
#ifndef UOleExclH
#define UOleExclH
//---------------------------------------------------------------------------
#include <SysUtils.hpp>
#include <Controls.hpp>
#include <Classes.hpp>
#include <Forms.hpp>
#include <stdlib.h>
#define _S c_str()
//---------------------------------------------------------------------------
class PACKAGE TOleExcel : public TComponent
{
private:
bool FEXCELOpened;
bool FDocOpened;
String FDocName;
String FSheetName;
String FCellCoords;
Variant FCellValue;
struct RowCol
{
int row;
int col;
};
Variant V;
Variant X;
Variant WB;
Variant wb;
Variant SH;
Variant sh;
__fastcall RowCol GetRowCol(String Coords);
protected:
public:
__fastcall TOleExcel(TComponent* Owner);
// Methods
__fastcall bool OpenEXCEL(bool Visible);
__fastcall bool CloseEXCEL(void);
__fastcall bool OpenDocument(String Path);
__fastcall bool CloseDocument(void);
__fastcall bool SaveDocument(void);
__fastcall int GetSheetNum(void);
__fastcall bool OpenSheet(String WKSheet);
__fastcall bool OpenSheet(int WKSheetNum);
__fastcall Variant GetCellValue(String Coords);
__fastcall void SetCellValue(String Coords, String NewVal);
__fastcall void FSetDocName(String NewDocName);
__fastcall void FSetSheetName(String NewSheetName);
__fastcall void FSetCellCoords(String NewCellCoords);
__fastcall void FSetCellValue(Variant NewCellValue);
__fastcall void FSetEXCELOpened(bool NewValue);
__fastcall void FSetDocOpened(bool NewValue);
__published:
// Properties
__property String DocName = { read=FDocName, write=FSetDocName};
__property String SheetName = { read=FSheetName, write=FSetSheetName};
__property String CellCoords = { read=FCellCoords, write=FSetCellCoords};
__property Variant CellValue = { read=FCellValue, write=FSetCellValue};
__property bool EXCELOpened = { read=FEXCELOpened ,
write=FSetEXCELOpened};
__property bool DocOpened = { read=FDocOpened , write=FSetDocOpened};
};
//---------------------------------------------------------------------------
#endif
///////////////////////////////// .CPP
//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop
#include "UOleExcl.h"
#pragma package(smart_init)
//---------------------------------------------------------------------------
// ValidCtrCheck is used to assure that the components created do not have
// any pure virtual functions.
static inline void ValidCtrCheck(TOleExcel *)
{
new TOleExcel(NULL);
}
//---------------------------------------------------------------------------
__fastcall TOleExcel::TOleExcel(TComponent* Owner)
: TComponent(Owner)
{
FEXCELOpened = false;
FDocOpened = false;
}
//---------------------------------------------------------------------------
namespace Uoleexcl
{
void __fastcall PACKAGE Register()
{
TComponentClass classes[1] = {__classid(TOleExcel)};
RegisterComponents("Components", classes, 0);
}
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::OpenEXCEL(bool Visible)
{
try
{
X=Variant::GetActiveObject("Excel.Application");
}
catch(...){;}
if (X.IsEmpty())
{
try
{
X=Variant::CreateObject("Excel.Application");
FEXCELOpened = true;
}
catch(...)
{
return false;
}
}
X.OlePropertySet("Visible",Visible);
return true;
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::CloseEXCEL(void)
{
if ( X.IsEmpty() )
{
return false;
}
X.OleProcedure("Quit");
FEXCELOpened = false;
return true;
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::OpenDocument(String Path)
{
try
{
WB = X.OlePropertyGet("WorkBooks");
WB.OleProcedure("Open",Path);
wb = WB.OlePropertyGet("Item",1);
FDocName = Path;
FDocOpened = true;
}
catch(...)
{
return false;
}
return true;
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::CloseDocument(void)
{
try
{
wb.OleProcedure("Close");
FDocOpened = false;
}
catch(...)
{
return false;
}
return true;
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::SaveDocument(void)
{
try
{
wb.OleProcedure("Save");
}
catch(...)
{
return false;
}
return true;
}
////////////////////////////////////////////////////////////////////////////////
int __fastcall TOleExcel::GetSheetNum()
{
Variant ST,nn;
ST = wb.OlePropertyGet("Sheets");
nn = ST.OlePropertyGet("Count");
return ((int)nn);
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::OpenSheet(String WKSheet)
{
try
{
SH = wb.OlePropertyGet("WorkSheets");
sh = SH.OlePropertyGet("Item",WKSheet);
FSheetName = WKSheet;
}
catch(...)
{
return false;
}
return true;
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::OpenSheet(int WKSheetNum)
{
try
{
SH = wb.OlePropertyGet("WorkSheets");
sh = SH.OlePropertyGet("Item",WKSheetNum);
}
catch(...)
{
return false;
}
return true;
}
////////////////////////////////////////////////////////////////////////////////
Variant __fastcall TOleExcel::GetCellValue(String Coords)
{
RowCol cCoord;
cCoord = GetRowCol(Coords);
FCellValue.Clear();
if ( cCoord.row <=0 ) return FCellValue;
FCellValue =
sh.OlePropertyGet("Cells",cCoord.row,cCoord.col).OlePropertyGet("Value");
FCellCoords = Coords;
return FCellValue;
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::SetCellValue(String Coords, String NewVal)
{
RowCol cCoord;
Variant cell;
cCoord = GetRowCol(Coords);
if ( cCoord.row <=0 ) return ;
cell = sh.OlePropertyGet("Cells",cCoord.row,cCoord.col);
cell.OlePropertySet("Value",NewVal);
}
////////////////////////////////////////////////////////////////////////////////
TOleExcel::RowCol __fastcall TOleExcel::GetRowCol(String Coords)
{
RowCol c;
int init,
c1,
c2;
char sCoords[10],
sCol[3],
sRow[10];
if ( strlen(Coords._S) <= 0 )
{
c.row = -1;
c.col = -1;
return(c);
}
strcpy(sCoords,Coords._S);
if ( !isalpha((int)Coords[2]) )
init = 1; // Formato Cn
else
init = 2; // Formato CCn
strncpy(sCol,Coords._S,init);
strncpy(sRow,&Coords._S[init],strlen(Coords._S)-init);
if (init == 1)
{
c1 = sCol[0] - 'A' ;
c2 = 1;
}
else
{
c1 = 26 * ( sCol[0] - 'A' + 1 );
c2 = sCol[1] - 'A' + 1 ;
}
c.row = atoi(sRow);
c.col = c1 + c2;
return(c);
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::FSetDocName(String NewDocName)
{
if ( NewDocName != FDocName )
{
FDocName = NewDocName;
}
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::FSetSheetName(String NewSheetName)
{
if ( NewSheetName != FSheetName )
{
FSheetName = NewSheetName;
}
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::FSetCellCoords(String NewCellCoords)
{
if ( NewCellCoords != FCellCoords )
{
FCellCoords = NewCellCoords;
}
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::FSetCellValue(Variant NewCellValue)
{
if ( NewCellValue != FCellValue )
{
FCellValue = NewCellValue;
}
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::FSetEXCELOpened(bool NewValue)
{
ShowMessage("Property 'EXCELOpened' read only!");
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::FSetDocOpened(bool NewValue)
{
ShowMessage("Property 'DocOpened' read only!");
}
|
|
| Back to top |
|
|
Brian
Guest
|
| Posted: Tue Sep 28, 2004 2:02 am Post subject: Re: How to read an Excel file |
|
|
Paolo:
Thank you for code. This code requiers Excel to be installed on the computer. I would like to be able to read Excel file without having to run Excel. Is there any book you could suggest about this subject.
Brian
"Paolo Borlandi" <pborlandi (AT) virgilio (DOT) it> wrote:
| Quote: | "Brian" <MMI (AT) dpstats (DOT) com> ha scritto nel messaggio
news:41546f72$1 (AT) newsgroups (DOT) borland.com...
I am trying to read an Excel file I do not know where to begin I have
already checked C++ Builder Developers Guide for TExcelApplication
TExcelWorkBook TExcelWorksheet,there is no referance to them. Can you
suggest a book that has example of how to read Excel file using builder 6.
Thanks
Some years ago I wrote a component to access an Excel file. I put here the
code hoping it can be useful. If you do not need to write a component you
can see the code on how to read and write data to excel.
Paolo
////////////////////////////////// .HPP
//---------------------------------------------------------------------------
#ifndef UOleExclH
#define UOleExclH
//---------------------------------------------------------------------------
#include
#include
#include
#include
#include
#define _S c_str()
//---------------------------------------------------------------------------
class PACKAGE TOleExcel : public TComponent
{
private:
bool FEXCELOpened;
bool FDocOpened;
String FDocName;
String FSheetName;
String FCellCoords;
Variant FCellValue;
struct RowCol
{
int row;
int col;
};
Variant V;
Variant X;
Variant WB;
Variant wb;
Variant SH;
Variant sh;
__fastcall RowCol GetRowCol(String Coords);
protected:
public:
__fastcall TOleExcel(TComponent* Owner);
// Methods
__fastcall bool OpenEXCEL(bool Visible);
__fastcall bool CloseEXCEL(void);
__fastcall bool OpenDocument(String Path);
__fastcall bool CloseDocument(void);
__fastcall bool SaveDocument(void);
__fastcall int GetSheetNum(void);
__fastcall bool OpenSheet(String WKSheet);
__fastcall bool OpenSheet(int WKSheetNum);
__fastcall Variant GetCellValue(String Coords);
__fastcall void SetCellValue(String Coords, String NewVal);
__fastcall void FSetDocName(String NewDocName);
__fastcall void FSetSheetName(String NewSheetName);
__fastcall void FSetCellCoords(String NewCellCoords);
__fastcall void FSetCellValue(Variant NewCellValue);
__fastcall void FSetEXCELOpened(bool NewValue);
__fastcall void FSetDocOpened(bool NewValue);
__published:
// Properties
__property String DocName = { read=FDocName, write=FSetDocName};
__property String SheetName = { read=FSheetName, write=FSetSheetName};
__property String CellCoords = { read=FCellCoords, write=FSetCellCoords};
__property Variant CellValue = { read=FCellValue, write=FSetCellValue};
__property bool EXCELOpened = { read=FEXCELOpened ,
write=FSetEXCELOpened};
__property bool DocOpened = { read=FDocOpened , write=FSetDocOpened};
};
//---------------------------------------------------------------------------
#endif
///////////////////////////////// .CPP
//---------------------------------------------------------------------------
#include
#pragma hdrstop
#include "UOleExcl.h"
#pragma package(smart_init)
//---------------------------------------------------------------------------
// ValidCtrCheck is used to assure that the components created do not have
// any pure virtual functions.
static inline void ValidCtrCheck(TOleExcel *)
{
new TOleExcel(NULL);
}
//---------------------------------------------------------------------------
__fastcall TOleExcel::TOleExcel(TComponent* Owner)
: TComponent(Owner)
{
FEXCELOpened = false;
FDocOpened = false;
}
//---------------------------------------------------------------------------
namespace Uoleexcl
{
void __fastcall PACKAGE Register()
{
TComponentClass classes[1] = {__classid(TOleExcel)};
RegisterComponents("Components", classes, 0);
}
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::OpenEXCEL(bool Visible)
{
try
{
X=Variant::GetActiveObject("Excel.Application");
}
catch(...){;}
if (X.IsEmpty())
{
try
{
X=Variant::CreateObject("Excel.Application");
FEXCELOpened = true;
}
catch(...)
{
return false;
}
}
X.OlePropertySet("Visible",Visible);
return true;
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::CloseEXCEL(void)
{
if ( X.IsEmpty() )
{
return false;
}
X.OleProcedure("Quit");
FEXCELOpened = false;
return true;
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::OpenDocument(String Path)
{
try
{
WB = X.OlePropertyGet("WorkBooks");
WB.OleProcedure("Open",Path);
wb = WB.OlePropertyGet("Item",1);
FDocName = Path;
FDocOpened = true;
}
catch(...)
{
return false;
}
return true;
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::CloseDocument(void)
{
try
{
wb.OleProcedure("Close");
FDocOpened = false;
}
catch(...)
{
return false;
}
return true;
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::SaveDocument(void)
{
try
{
wb.OleProcedure("Save");
}
catch(...)
{
return false;
}
return true;
}
////////////////////////////////////////////////////////////////////////////////
int __fastcall TOleExcel::GetSheetNum()
{
Variant ST,nn;
ST = wb.OlePropertyGet("Sheets");
nn = ST.OlePropertyGet("Count");
return ((int)nn);
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::OpenSheet(String WKSheet)
{
try
{
SH = wb.OlePropertyGet("WorkSheets");
sh = SH.OlePropertyGet("Item",WKSheet);
FSheetName = WKSheet;
}
catch(...)
{
return false;
}
return true;
}
////////////////////////////////////////////////////////////////////////////////
bool __fastcall TOleExcel::OpenSheet(int WKSheetNum)
{
try
{
SH = wb.OlePropertyGet("WorkSheets");
sh = SH.OlePropertyGet("Item",WKSheetNum);
}
catch(...)
{
return false;
}
return true;
}
////////////////////////////////////////////////////////////////////////////////
Variant __fastcall TOleExcel::GetCellValue(String Coords)
{
RowCol cCoord;
cCoord = GetRowCol(Coords);
FCellValue.Clear();
if ( cCoord.row <=0 ) return FCellValue;
FCellValue =
sh.OlePropertyGet("Cells",cCoord.row,cCoord.col).OlePropertyGet("Value");
FCellCoords = Coords;
return FCellValue;
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::SetCellValue(String Coords, String NewVal)
{
RowCol cCoord;
Variant cell;
cCoord = GetRowCol(Coords);
if ( cCoord.row <=0 ) return ;
cell = sh.OlePropertyGet("Cells",cCoord.row,cCoord.col);
cell.OlePropertySet("Value",NewVal);
}
////////////////////////////////////////////////////////////////////////////////
TOleExcel::RowCol __fastcall TOleExcel::GetRowCol(String Coords)
{
RowCol c;
int init,
c1,
c2;
char sCoords[10],
sCol[3],
sRow[10];
if ( strlen(Coords._S) <= 0 )
{
c.row = -1;
c.col = -1;
return(c);
}
strcpy(sCoords,Coords._S);
if ( !isalpha((int)Coords[2]) )
init = 1; // Formato Cn
else
init = 2; // Formato CCn
strncpy(sCol,Coords._S,init);
strncpy(sRow,&Coords._S[init],strlen(Coords._S)-init);
if (init == 1)
{
c1 = sCol[0] - 'A' ;
c2 = 1;
}
else
{
c1 = 26 * ( sCol[0] - 'A' + 1 );
c2 = sCol[1] - 'A' + 1 ;
}
c.row = atoi(sRow);
c.col = c1 + c2;
return(c);
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::FSetDocName(String NewDocName)
{
if ( NewDocName != FDocName )
{
FDocName = NewDocName;
}
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::FSetSheetName(String NewSheetName)
{
if ( NewSheetName != FSheetName )
{
FSheetName = NewSheetName;
}
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::FSetCellCoords(String NewCellCoords)
{
if ( NewCellCoords != FCellCoords )
{
FCellCoords = NewCellCoords;
}
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::FSetCellValue(Variant NewCellValue)
{
if ( NewCellValue != FCellValue )
{
FCellValue = NewCellValue;
}
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::FSetEXCELOpened(bool NewValue)
{
ShowMessage("Property 'EXCELOpened' read only!");
}
////////////////////////////////////////////////////////////////////////////////
void __fastcall TOleExcel::FSetDocOpened(bool NewValue)
{
ShowMessage("Property 'DocOpened' read only!");
}
|
|
|
| Back to top |
|
|
Yahia El-Qasem
Guest
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
