用 ODBC 我程序中的一段读EXCEL数据的代码,你自己看看void ImportTeachersData(char * file) { if (file == NULL || strlen(file) == 0) return; CString sDsn, sDriver, sSql, sItem1, sItem2; char number[20], name[50]; CDatabase database; int iCount; sDriver = theApp.GetExcelDriver(); sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,file); TRY { // Open the database using the former created pseudo DSN database.Open(NULL,false,false,sDsn);
// Allocate the recordset CRecordset recset( &database ); // Build the SQL string // Remember to name a section of data in the Excel sheet using "Insert->Names" to be // able to work with the data like you would with a table in a "real" database. There // may be more than one table contained in a worksheet. sSql = "SELECT * from [Sheet1$]";
// Execute that query (implicitly by opening the recordset) recset.Open(CRecordset::forwardOnly,sSql,CRecordset::readOnly); // Browse the result iCount = recset.GetRecordCount(); short nFields = recset.GetODBCFieldCount( ); while( !recset.IsEOF() ) { // Read the result line for( short index = 0; index < nFields; index++ ) { // do something with varValu switch (index) { case 0: recset.GetFieldValue(index, sItem1); memset(number, 0, 20); sprintf(number, "%s", sItem1); break; case 1: recset.GetFieldValue(index, sItem2); memset(name, 0, 50); sprintf(name, "%s", sItem2); break; default: break; } } theApp.m_db.InsertTeachersInfo(number, name); // Skip to the next resultline recset.MoveNext(); } // Close the database database.Close(); } CATCH(CDBException, e) { // A database exception occured. Pop out the details... AfxMessageBox("Database error: "+e->m_strError); } END_CATCH; }CString GetExcelDriver() { char szBuf[2001]; WORD cbBufMax = 2000; WORD cbBufOut; char *pszBuf = szBuf; CString sDriver; // Get the names of the installed drivers ("odbcinst.h" has to be included ) if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut)) return "";
我程序中的一段读EXCEL数据的代码,你自己看看void ImportTeachersData(char * file)
{
if (file == NULL ||
strlen(file) == 0)
return; CString sDsn, sDriver, sSql, sItem1, sItem2;
char number[20], name[50];
CDatabase database;
int iCount; sDriver = theApp.GetExcelDriver();
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,file); TRY
{
// Open the database using the former created pseudo DSN
database.Open(NULL,false,false,sDsn);
// Allocate the recordset
CRecordset recset( &database ); // Build the SQL string
// Remember to name a section of data in the Excel sheet using "Insert->Names" to be
// able to work with the data like you would with a table in a "real" database. There
// may be more than one table contained in a worksheet.
sSql = "SELECT * from [Sheet1$]";
// Execute that query (implicitly by opening the recordset)
recset.Open(CRecordset::forwardOnly,sSql,CRecordset::readOnly); // Browse the result
iCount = recset.GetRecordCount();
short nFields = recset.GetODBCFieldCount( ); while( !recset.IsEOF() )
{
// Read the result line
for( short index = 0; index < nFields; index++ )
{
// do something with varValu
switch (index)
{
case 0:
recset.GetFieldValue(index, sItem1);
memset(number, 0, 20);
sprintf(number, "%s", sItem1);
break;
case 1:
recset.GetFieldValue(index, sItem2);
memset(name, 0, 50);
sprintf(name, "%s", sItem2);
break;
default:
break;
}
}
theApp.m_db.InsertTeachersInfo(number, name);
// Skip to the next resultline
recset.MoveNext();
} // Close the database
database.Close(); }
CATCH(CDBException, e)
{
// A database exception occured. Pop out the details...
AfxMessageBox("Database error: "+e->m_strError);
}
END_CATCH;
}CString GetExcelDriver()
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver; // Get the names of the installed drivers ("odbcinst.h" has to be included )
if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))
return "";
// Search for the driver...
do
{
if( strstr( pszBuf, "Excel" ) != 0 )
{
// Found !
sDriver = CString( pszBuf );
break;
}
pszBuf = strchr( pszBuf, '\0' ) + 1;
}
while( pszBuf[1] != '\0' ); return sDriver;
}
二 可以通过OLE AUTOMATION方式
三 直接对文件二进制读取以下我给你一段ODBC方法代码:想要通过ODBC直接读、写Excel表格文件,首先,应确保ODBC中已安装有Excel表格文件的驱动"MICROSOFT EXCEL DRIVER (*.XLS)"。然后,可根据下面步骤进行: 1. 在StdAfx.h文件中加入: #include <afxdb.h>
#include <odbcinst.h> 2. 通过ODBC直接创建Excel文件并在表中插入数据(暂定文件名:Demo.xls) //创建并写入Excel文件
void CRWExcel::WriteToExcel()
{
CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // Excel安装驱动
CString sExcelFile = "c:\\demo.xls"; // 要建立的Excel文件
CString sSql;
TRY
{
// 创建进行存取的字符串
sSql.Format("DRIVER={%s};DSN='''';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",
sDriver, sExcelFile, sExcelFile); // 创建数据库 (既Excel表格文件)
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
// 创建表结构(姓名、年龄)
sSql = "CREATE TABLE demo (Name TEXT,Age NUMBER)";
database.ExecuteSQL(sSql); // 插入数值
sSql = "INSERT INTO demo (Name,Age) VALUES (''徐景周'',26)";
database.ExecuteSQL(sSql); sSql = "INSERT INTO demo (Name,Age) VALUES (''徐志慧'',22)";
database.ExecuteSQL(sSql); sSql = "INSERT INTO demo (Name,Age) VALUES (''郭徽'',27)";
database.ExecuteSQL(sSql);
} // 关闭数据库
database.Close();
}
CATCH_ALL(e)
{
TRACE1("Excel驱动没有安装: %s",sDriver);
}
END_CATCH_ALL;
} 3. 通过ODBC直接读取Excel文件(暂定文件名:Demo.xls) // 读取Excel文件
void CRWExcel::ReadFromExcel()
{
CDatabase database;
CString sSql;
CString sItem1, sItem2;
CString sDriver;
CString sDsn;
CString sFile = "Demo.xls"; // 将被读取的Excel文件名
// 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)"
sDriver = GetExcelDriver();
if (sDriver.IsEmpty())
{
// 没有发现Excel驱动
AfxMessageBox("没有安装Excel驱动!");
return;
}
// 创建进行存取的字符串
sDsn.Format("ODBC;DRIVER={%s};DSN='''';DBQ=%s", sDriver, sFile); TRY
{
// 打开数据库(既Excel文件)
database.Open(NULL, false, false, sDsn);
CRecordset recset(&database); // 设置读取的查询语句.
sSql = "SELECT Name, Age "
"FROM demo "
"ORDER BY Name ";
// 执行查询语句
recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly); // 获取查询结果
while (!recset.IsEOF())
{
//读取Excel内部数值
recset.GetFieldValue("Name ", sItem1);
recset.GetFieldValue("Age", sItem2); // 移到下一行
recset.MoveNext();
} // 关闭数据库
database.Close();
}
CATCH(CDBException, e)
{
// 数据库操作产生异常时...
AfxMessageBox("数据库错误: " + e->m_strError);
}
END_CATCH;
} 4. 获取ODBC中Excel驱动的函数 CString CRWExcel::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;
}
http://community.csdn.net/Expert/topic/4602/4602265.xml?temp=.2278101