ODBC操作Excel

想要通过ODBC直接读、写Excel表格文件，首先，应确保ODBC中已安装有Excel表格文件的驱动"MICROSOFT EXCEL DRIVER (*.XLS)"。然后，可根据下面步骤进行：

1. 在StdAfx.h文件中加入：

#include <afxdb.h> 
#include <odbcinst.h>

2. 通过ODBC直接创建Excel文件并在表中插入数据(暂定文件名：c:\demo.xls)

 

void CRWExcelDlg::Onwrite() 
{
	// TODO: Add your control notification handler code here
	CDatabase database;
	CString sDriver="MICROSOFT EXCEL DRIVER (*.XLS)";
	CString sExcelFile="c:\\demo.xls";
	CString sSql;
	
	TRY
	{
		//sql
		sSql.Format("DRIVER={%s};DSN='''';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",
			sDriver,sExcelFile,sExcelFile);
		//create db
		if (database.OpenEx(sSql,CDatabase::noOdbcDialog))
		{
			//create table
			sSql="CREATE TABLE demo (Name TEXT,Age NUMBER)";
			database.ExecuteSQL(sSql);
			
			//insert value
			sSql="INSERT INTO demo (Name,Age) VALUES ('boxer',24)";
			database.ExecuteSQL(sSql);
			
			sSql="INSERT INTO demo (Name,Age) VALUES ('youngboxer',14)";
			database.ExecuteSQL(sSql);
			
			sSql="INSERT INTO demo (Name,Age) VALUES('matureboxer',30)";
			database.ExecuteSQL(sSql);
			
		}
		//close db
		database.Close();
		
	}
	CATCH_ALL (e)
	{
		TRACE1("Excel driver not installed: %s",sDriver);
	}
	END_CATCH_ALL;
}

3. 通过ODBC直接读取Excel文件(暂定文件名：c:\demo.xls)

void CRWExcelDlg::Onread() 
{
	// TODO: Add your control notification handler code here
	CDatabase database;
	CString sDriver;
	CString sItem1,sItem2;
	CString sDsn;
	CString sFile="c:\\demo.xls";//filename
	CString sSql;
	
	//check driver
	sDriver=GetExcelDriver();
	if (sDriver.IsEmpty())
	{
		//not found
		AfxMessageBox("driver not found");
		return;
	}
	
	//con string
	
	sDsn.Format("ODBC;DRIVER={%s};DSN='''';DBQ=%s",sDriver,sFile);
	
	TRY
	{
		//open db
		database.Open(NULL,false,false,sDsn);
		
		CRecordset recset(&database);
		//read sql 
		sSql="SELECT Name,Age "
			"FROM demo "
			"ORDER BY Name";
		recset.Open(CRecordset::forwardOnly,sSql,CRecordset::readOnly);
		
		//get query results
		while(!recset.IsEOF())
		{
			//read inside value
			recset.GetFieldValue("Name",sItem1);
			recset.GetFieldValue("Age",sItem2);
			
			//show 
			m_ctrlList1.AddString(sItem1+"--->"+sItem2);
			m_ctrlList1.SendMessage(WM_VSCROLL,SB_PAGEDOWN,0);
			//next
			recset.MoveNext();
		}
		
		//close db
		database.Close();
		
	}
	CATCH (CDBException,e)
	{
		//db exception occur
		AfxMessageBox("db error: "+e->m_strError);
	}
	END_CATCH;
}

 

4.获取ODBC中Excel驱动的函数

CString CRWExcelDlg::GetExcelDriver()
{
	char szBuf[2001];
	WORD cbBufMax=2000;
	WORD cbBufOut;
	char *pszBuf=szBuf;
	CString sDriver;
	
	//get driver name function(included in odbcinst.h)
	if (!SQLGetInstalledDrivers(szBuf,cbBufMax,&cbBufOut))
		return "";
	//check excel included or not
	do 
	{
		if (strstr(pszBuf,"Excel")!=0)
		{
			//found
			sDriver=CString(pszBuf);
			break;
		}
		pszBuf=strchr(pszBuf,'\0')+1;
	} while (pszBuf[1]!='\0');
	
	return sDriver;
}