CExcel_DEMO

 

#pragma once#include <afxdb.h>#include <odbcinst.h>class CExcel_DEMO{public:CExcel_DEMO(void);~CExcel_DEMO(void);//创建并写入Excel文件void WriteToExcel(){CDatabase database;CString sDriver =GetExcelDriver();// _T("MICROSOFT EXCEL DRIVER (*.XLS)"); // Excel安装驱动CString sExcelFile = _T("c:\\demo.xls");                // 要建立的Excel文件CString sSql;TRY{// 创建进行存取的字符串sSql.Format(_T("DRIVER={%s};DSN='''';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s"),sDriver, sExcelFile, sExcelFile);// 创建数据库 (既Excel表格文件)if( database.OpenEx(sSql,CDatabase::noOdbcDialog) ){// 创建表结构(姓名、年龄)sSql =_T("CREATE TABLE demo (Name TEXT,Age NUMBER)");database.ExecuteSQL(sSql);// 插入数值sSql = _T("INSERT INTO demo (Name,Age) VALUES ('徐景周',26)");database.ExecuteSQL(sSql);sSql = _T("INSERT INTO demo (Name,Age) VALUES ('徐志慧',22)");database.ExecuteSQL(sSql);sSql = _T("INSERT INTO demo (Name,Age) VALUES ('郭徽',27)");database.ExecuteSQL(sSql);}// 关闭数据库database.Close();}CATCH_ALL(e){TRACE1(_T("Excel驱动没有安装: %s"),sDriver);}END_CATCH_ALL;}CString GetExcelDriver(){TCHAR szBuf[2001];WORD cbBufMax = 2000;WORD cbBufOut;TCHAR*pszBuf = szBuf;CString sDriver;// 获取已安装驱动的名称(涵数在odbcinst.h里)if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))return _T("");// 检索已安装的驱动是否有Excel...do{if (tstrstr(pszBuf, _T("Excel")) != 0){//发现 !sDriver = CString(pszBuf);break;}pszBuf = tstrchr(pszBuf, _T('\0')) + 1;}while (pszBuf[1] != _T('\0'));return sDriver;}// 读取Excel文件void ReadFromExcel(){CDatabase database;CString sSql;CString sItem1, sItem2;CString sDriver;CString sDsn;CString sFile = _T("Demo.xls"); // 将被读取的Excel文件名// 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)"sDriver = GetExcelDriver();if (sDriver.IsEmpty()){// 没有发现Excel驱动AfxMessageBox(_T("没有安装Excel驱动!"));return;}// 创建进行存取的字符串sDsn.Format(_T("ODBC;DRIVER={%s};DSN='''';DBQ=%s"), sDriver, sFile);TRY{// 打开数据库(既Excel文件)database.Open(NULL, false, false, sDsn);CRecordset recset(&database);// 设置读取的查询语句.sSql = _T("SELECT Name, Age FROM demo ORDER BY Name ");// 执行查询语句recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly);// 获取查询结果while (!recset.IsEOF()){//读取Excel内部数值recset.GetFieldValue(_T("Name"), sItem1);recset.GetFieldValue(_T("Age"), sItem2);// 移到下一行recset.MoveNext();}// 关闭数据库database.Close();}CATCH(CDBException, e){// 数据库操作产生异常时...AfxMessageBox(_T("数据库错误: ") + e->m_strError);}END_CATCH;}};