操作Excel数据库(从网上收集,直接拷贝过来,稍作整理)
1. 加上如下代码,得到对数据库的支持
#pragma comment( lib, "Odbccp32.lib" )
#include <afxdb.h>
#include <Odbcinst.h>
2. 得到Excel驱动字符串
CString GetExcelDriver()
{
const WORD cbBufMax = 2000;
char szBuf[ cbBufMax + 1 ];
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver;
// 获取已安装驱动的名称
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;
}
3. 打开已有数据库
// 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)"
CString sDriver = GetExcelDriver();
if (sDriver.IsEmpty())
{
// 没有发现Excel驱动
AfxMessageBox("没有安装Excel驱动!");
return;
}
// 打开数据库
CString sDsn;
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s", sDriver,filename );
CDatabase database;
database.Open(NULL, false, false, sDsn);
4. 读取表中数据
CRecordset recset(&database);
CString sSql = "SELECT * from [人员$]";
recset.Open( CRecordset::forwardOnly,sSql,CRecordset::readOnly);
while (!recset.IsEOF())
{
CString name;
recset.GetFieldValue( "name", name ); //列名,值
recset.MoveNext();
}
5. 创建数据库
CString sDriver = GetExcelDriver();
CString sSql;
sSql.Format("DRIVER={%s};DSN='''';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=/"%s/";DBQ=%s",
sDriver, filename, filename );
CDatabase database;
database.OpenEx( sSql,CDatabase::noOdbcDialog );
6. 创建表
CString tableName = "人员";
sSql = "CREATE TABLE " + tableName + " ( name TEXT )";
database.ExecuteSQL( sSql );
7. 插入新值
sSql.Format( "INSERT INTO %s ( %s ) VALUES ( '%s' )", tableName, colName, personName );
database.ExecuteSQL(sSql);
8. 异常处理
TRY
{
......
}
CATCH(CDBException, e)
{
// 数据库操作产生异常时...
AfxMessageBox("数据库错误: " + e->m_strError);
}
END_CATCH;
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/hongjiqin/archive/2006/07/29/997218.aspx