odbcexcel.cpp:
#include "StdAfx.h"
#include "OdbcExcel.h"
CString GetExcelDriver()
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver;
// 获取已安装驱动的名称(涵数在odbcinst.h里)
if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
return "";
// 检索已安装的驱动是否有Excel...
do
{
if (strstr(pszBuf, "Excel") != 0)
{
//发现 !
sDriver = CString(pszBuf);
break;
}
pszBuf = strchr(pszBuf, '\0') + 1;
}
while (pszBuf[1] != '\0');
return sDriver;
}
COdbcExcel::COdbcExcel(void)
{
}
COdbcExcel::~COdbcExcel(void)
{
}
void COdbcExcel::ReadExcel()
{
CString qudong = GetExcelDriver();
}
CString COdbcExcel:: GetExcelDriver()
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver;
// 获取已安装驱动的名称(涵数在odbcinst.h里)
if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
return "";
// 检索已安装的驱动是否有Excel...
do
{
if (strstr(pszBuf, "Excel") != 0)
{
//发现 !
sDriver = CString(pszBuf);
break;
}
pszBuf = strchr(pszBuf, '\0') + 1;
}
while (pszBuf[1] != '\0');
return sDriver;
}
bool COdbcExcel::Initial_path(CString _path)
{
ExcelName = _path;
int _exit = PathFileExists(ExcelName);
if (1 == _exit)
{
return TRUE;
}
else
{
return FALSE;
}
}
void COdbcExcel::Initial_sheet_columnname(CString _sheet, CString _columnname)
{
SheetName = _sheet;
ColumnName = _columnname;
CString _temp = _columnname;
ColumnLength = _temp.Remove(',') + 1;
}
void COdbcExcel::Open_DataBase()
{
// 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)"
sDriver = GetExcelDriver();
if (sDriver.IsEmpty())
{
// 没有发现Excel驱动
AfxMessageBox("没有安装Excel驱动!");
return;
}
// 创建进行存取的字符串
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s", sDriver, ExcelName);
TRY
{
// 打开数据库(既Excel文件)
database.Open(NULL, false, false, sDsn);
recset = new CRecordset(&database);
//recset = &database;
//CRecordset recset(&database);
// 设置读取的查询语句.
sSql = "SELECT " + ColumnName + " FROM " + SheetName;//"ORDER BY Name ";
// 执行查询语句
recset->Open(CRecordset::forwardOnly/*dynamic*/, sSql, CRecordset::readOnly);
}
CATCH(CDBException, e)
{
// 数据库操作产生异常时...
AfxMessageBox("数据库错误: " + e->m_strError);
}
END_CATCH;
}
void COdbcExcel::Close_DataBae()
{
recset->Close();
// 关闭数据库
database.Close();
}
bool COdbcExcel::GetValueFromExcel( int _column, int & _value)
{
if ( _column > ColumnLength)
{
return FALSE;
}
else
{
CString result;
int value_;
CString onecolumn = GetOneColumn(_column);
recset->GetFieldValue(onecolumn, result);
char* _tempchar = result.GetBuffer();
sscanf(_tempchar, "%d", &value_);
_value = value_;
return TRUE;
}
}
bool COdbcExcel::GetValueFromExcel(int _column, double &_value)
{
if ( _column > ColumnLength)
{
return FALSE;
}
else
{
CString result;
CString onecolumn = GetOneColumn(_column);
recset->GetFieldValue(onecolumn, result);
char* _tempchar = result.GetBuffer();
sscanf(_tempchar, "%f", &_value);
return TRUE;
}
}
bool COdbcExcel::GetValueFromExcel(int _column, char *_value)
{
if ( _column > ColumnLength)
{
return FALSE;
}
else
{
CString result;
CString onecolumn = GetOneColumn(_column);
recset->GetFieldValue(onecolumn, result);
//_value = result.GetBuffer();
strcpy(_value, result.GetBuffer());
return TRUE;
}
}
void COdbcExcel::MoveNextRow()
{
recset->MoveNext();
}
CString COdbcExcel::GetOneColumn(int th)
{
CString _tempstr = ColumnName;
CString _result;
int n;
while ( 0 != th)
{
n = _tempstr.Find(',');
if ( -1 == n)
{
return _tempstr;
}
else
{
_result = _tempstr.Left(n);
_tempstr = _tempstr.Right(_tempstr.GetLength() - n - 1);
--th;
}
}
return _result;
}
***************************************************************************\
odbcexcel.h
#pragma once
#include <afxdb.h>
#include <odbcinst.h>
#include <shlwapi.h>
#include <iostream>
using namespace std;
class COdbcExcel
{
public:
COdbcExcel(void);
~COdbcExcel(void);
void ReadExcel();
CString GetExcelDriver();//OK
bool Initial_path (CString _path);//OK
void Initial_sheet_columnname(CString _sheet, CString _columnname);//布尔类型返回值没设定
void Open_DataBase();//OK
void Close_DataBae();//OK
bool GetValueFromExcel( int _column, int & _value);//OK
bool GetValueFromExcel( int _column, double & _value);//OK
bool GetValueFromExcel( int _column, char* _value);//OK
void MoveNextRow();//OK
CString GetOneColumn(int th);//OK
public:
///初始化excel文件
CString ExcelName;
CString SheetName;
CString ColumnName;
///初始化excel文件
///初始化DataBase
CDatabase database;
CRecordset *recset;
CString sDsn;
CString sDriver;
CString sSql;
///初始化DataBase
///查询
int ColumnLength;
///查询
};