Click here to Skip to main content
15,908,264 members
Home / Discussions / C / C++ / MFC
   

C / C++ / MFC

 
GeneralInterthread messaging Pin
30-Jan-02 9:20
suss30-Jan-02 9:20 
GeneralRe: Interthread messaging Pin
Joaquín M López Muñoz30-Jan-02 9:45
Joaquín M López Muñoz30-Jan-02 9:45 
GeneralRe: Interthread messaging Pin
30-Jan-02 10:09
suss30-Jan-02 10:09 
GeneralRe: Interthread messaging Pin
Joaquín M López Muñoz30-Jan-02 10:17
Joaquín M López Muñoz30-Jan-02 10:17 
Generaltrying to clear History from IE Pin
TigerNinja_30-Jan-02 8:58
TigerNinja_30-Jan-02 8:58 
GeneralAccessing Excel Cells from Visual C++ Pin
Havoc30-Jan-02 7:13
Havoc30-Jan-02 7:13 
GeneralRe: Accessing Excel Cells from Visual C++ Pin
Richard Ellis30-Jan-02 12:14
Richard Ellis30-Jan-02 12:14 
GeneralRe: Accessing Excel Cells from Visual C++ Pin
wangyiming30-Jan-02 14:33
wangyiming30-Jan-02 14:33 
#ifndef _EXCELSPACE_H
#define _EXCELSPACE_H
#include "excel9.h"

class CExcelSpace : public CObject
{
public:
CExcelSpace();
~CExcelSpace();

BOOL OpenExcel(LPCTSTR lpszFileName,LPCTSTR lpszSheetName,
BOOL m_bReadOnly = TRUE, BOOL bNewFile = FALSE, BOOL bNewSheet = FALSE );
void Close();

void GetRowColCount(int &row, int &col);
COleVariant GetCellValue(int row, int col);
BOOL SetCellValue(int row, int col,COleVariant var);
BOOL DeleteRow(int row);

CString GetFileName() { return m_szFileName; };
BOOL IsReadOnly() { return m_bReadOnly; };


void PrepareFetchData();
void EndFetchData();

BOOL ActiveSheet(LPCTSTR lpszSheetName);
BOOL IsSheetExist(LPCTSTR lpszSheetName);
BOOL DeleteSheet(LPCTSTR lpszSheetName);
BOOL NewSheet(LPCTSTR lpszSheetName);

_Application GetApplication()
{
return excelApp;
}
protected:
CString m_szFileName;
CString m_szSheetName;
BOOL m_bNewFile;
BOOL m_bReadOnly;
BOOL m_bValidate;
BOOL m_bServiceStarted;

BOOL bPreparedFectch;

CStringArray fldNames;
_Application excelApp;
int rowCount, colCount;
int curRow;

COleSafeArray sa;

BOOL StartExcelService();
CString InnerGetColumnName(int col);
CString GetCellName(int row, int col);
int NameToIndex(LPCTSTR lpszFldName);

// Dataset function
public:
int GetRecordCount() { return rowCount; };
int GetColCount() { return colCount; };
BOOL AddColumn(LPCTSTR lpszColName, int nColType);
CString GetColumnName(int col);
int GetColumnType(int col);
BOOL Delete();
BOOL InsertRow(BOOL bAppend);
BOOL Update();

COleVariant GetFieldValue(int col);
COleVariant GetFieldValue(LPCTSTR lpszFldName);

void SetFieldValue(int col,COleVariant var);
void SetFieldValue(LPCTSTR lpszFldname,COleVariant var);

BOOL MoveFirst();
BOOL MoveNext();
BOOL MoveBefore();

BOOL IsEOF();
BOOL IsBOF();

public:
static BOOL NewExcelSheet(LPCTSTR lpszFilename, LPCTSTR lpszSheetName);
static BOOL IsSheetExist(LPCTSTR lpszFilename, LPCTSTR lpszSheetName);
static BOOL DeleteSheet(LPCTSTR lpszFilename, LPCTSTR lpszSheetName);
static CString OleVariantToStr( const COleVariant & Var);
};

#endif

-------------------------------------
CExcelSpace::CExcelSpace()
{
m_szFileName = "";
m_bReadOnly = TRUE;
m_bValidate = FALSE;
bPreparedFectch = FALSE;
curRow = -1;
rowCount = colCount = 0;
m_bServiceStarted = FALSE;
}

CExcelSpace::~CExcelSpace()
{
fldNames.RemoveAll();
Close();
}

void CExcelSpace::Close()
{
if( m_bServiceStarted )
{
TRY
COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

_Workbook workbook = excelApp.GetActiveWorkbook();
workbook.Close(COleVariant((short)FALSE), VOptional, VOptional);
excelApp.Quit();
CATCH_ALL(e)
e->Delete();
END_CATCH_ALL

m_bServiceStarted = FALSE;
}

if( m_bValidate )
{
EndFetchData();
m_bValidate = FALSE;
}
}

CString CExcelSpace::GetCellName(int row, int col)
{
col++;

int count = 0;
char szNum16[32],szTmp[32];
while( col > 0 )
{
int m = (col-1)%26;
col = (col-1)/26;
szTmp[count++] = (char)('A'+m);
}
for(int i=0; i<count; i++)
="" sznum16[i]="szTmp[count-1-i];
" sznum16[count]="0;
"
="" cstring="" s;="" s.format("%s%d",sznum16,row+2);
="" return="" s;
}

int="" cexcelspace::nametoindex(lpctstr="" lpszfldname)
{
="" for(int="" i="0;" i<fldnames.getsize();="" {
="" if(="" fldnames.getat(i).comparenocase(lpszfldname)="=" 0="" )
="" i;
="" }
="" -1;
}

bool="" cexcelspace::addcolumn(lpctstr="" lpszcolname,="" int="" ncoltype)
{
="" colevariant="" var(lpszcolname);
="" setcellvalue(-1,="" colcount-1,="" var)="" szcolname="lpszColName;
" fldnames.add(="" );
="" colcount++;
="" true;
="" else
="" false;
};

cstring="" cexcelspace::innergetcolumnname(int="" col)
{
="" var="GetCellValue(-1,col);
" olevarianttostr(var);
}

cstring="" cexcelspace::getcolumnname(int="" s(_t(""));
="" col="">=0 && col < fldNames.GetSize() )
s = fldNames.GetAt(col);

return s;
}

int CExcelSpace::GetColumnType(int col)
{
return 0;
}

void CExcelSpace::GetRowColCount(int &row, int &col)
{
row = col =0;
if( !m_bValidate )
return;

Range range = excelApp.GetActiveCell();
COleVariant Value((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
range = range.SpecialCells(11,Value);
Value = range.Select();

row = range.GetRow()-1;
col = range.GetColumn();
}

void CExcelSpace::PrepareFetchData()
{
if( !bPreparedFectch )
{
COleVariant cell1( GetCellName(-1,0) );
COleVariant cell2( GetCellName(rowCount,colCount) );

Range range = excelApp.GetRange( cell1,cell2);
VARIANT ret = range.GetValue();
sa.Attach(ret);
bPreparedFectch = TRUE;
}
}

void CExcelSpace::EndFetchData()
{
if( bPreparedFectch )
{
sa.Detach();
bPreparedFectch = FALSE;
}
}

COleVariant CExcelSpace::GetCellValue(int row, int col)
{
COleVariant var;
var.Clear();

if( m_bValidate && row >= -1 && col >= 0 && row < rowCount && col < colCount )
{
TRY
if( bPreparedFectch )
{
long index[2];
index[0] = row+2;
index[1] = col+1;
sa.GetElement(index, &var);
}
else
{
CString cellName = GetCellName(row,col);
COleVariant cell1( cellName );
//COleVariant cell2( cellName );
Range range = excelApp.GetRange( cell1,cell1);
var = range.GetValue();
}
CATCH_ALL(e)
e->ReportError();
e->Delete();
END_CATCH_ALL
}

return var;
}
QuestionBad Coding? Pin
James R. Twine30-Jan-02 6:42
James R. Twine30-Jan-02 6:42 
AnswerRe: Bad Coding? Pin
Roger Allen30-Jan-02 6:56
Roger Allen30-Jan-02 6:56 
GeneralRe: Bad Coding? Pin
James R. Twine30-Jan-02 7:04
James R. Twine30-Jan-02 7:04 
AnswerRe: Bad Coding? Pin
Igor Proskuriakov30-Jan-02 7:15
Igor Proskuriakov30-Jan-02 7:15 
GeneralRe: Bad Coding? Pin
James R. Twine30-Jan-02 8:48
James R. Twine30-Jan-02 8:48 
AnswerRe: Bad Coding? Pin
Chris Losinger30-Jan-02 9:02
professionalChris Losinger30-Jan-02 9:02 
AnswerRe: Bad Coding? Pin
Joaquín M López Muñoz30-Jan-02 9:08
Joaquín M López Muñoz30-Jan-02 9:08 
GeneralChange the date of file! Pin
Mazdak30-Jan-02 6:14
Mazdak30-Jan-02 6:14 
GeneralRe: Change the date of file! Pin
Ravi Bhavnani30-Jan-02 6:24
professionalRavi Bhavnani30-Jan-02 6:24 
Generalstring usage problem Pin
30-Jan-02 6:00
suss30-Jan-02 6:00 
GeneralRe: string usage problem Pin
Joaquín M López Muñoz30-Jan-02 7:51
Joaquín M López Muñoz30-Jan-02 7:51 
GeneralC++ Macros in Visual C++ Pin
30-Jan-02 5:18
suss30-Jan-02 5:18 
GeneralRe: C++ Macros in Visual C++ Pin
Bill Wilson30-Jan-02 10:14
Bill Wilson30-Jan-02 10:14 
GeneralForaging for Suggestions Pin
Roger Wright30-Jan-02 5:05
professionalRoger Wright30-Jan-02 5:05 
Generala question about exit() method Pin
Gérald Mercet30-Jan-02 4:59
Gérald Mercet30-Jan-02 4:59 
GeneralRe: a question about exit() method Pin
Ravi Bhavnani30-Jan-02 5:05
professionalRavi Bhavnani30-Jan-02 5:05 
GeneralRe: a question about exit() method Pin
Gérald Mercet30-Jan-02 5:17
Gérald Mercet30-Jan-02 5:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.