使用ODBC读取excel文件

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;
 ///查询
};