MFC---ODBC创建/读取Excel

头文件

#include "afxdb.h"

创建Excel

//创建Excel
void CExcelDlg::OnOK() 
{
    // TODO: Add extra validation here

    //创建Excel文件
    CDatabase DB;

    //Excel安装驱动
    CString StrDriver = "MICROSOFT EXCEL DRIVER (*.XLS)";

    //要建立的Execel文件
    CString StrExcelFile = "f:\\Teachers.xls";
    CString StrSQL;
    StrSQL.Format("DRIVER={%s};DSN='';FIRSTROWHASNameS=1;READONLY=FALSE;CREATE_DB=%s;DBQ=%s",StrDriver,StrExcelFile,StrExcelFile);
    TRY
    {
        //创建Excel表格文件
        DB.OpenEx(StrSQL, CDatabase::noOdbcDialog);

        //创建表结构，字段名不能是Index
        StrSQL = "CREATE TABLE Teachers(职工号 TEXT, 姓名 TEXT)";
        DB.ExecuteSQL(StrSQL);

        //插入数值
        StrSQL.Format("INSERT INTO Teachers (职工号, 姓名) VALUES ('%s', '%s')", "aa", "bb");
        DB.ExecuteSQL(StrSQL);

        //关闭数据库
        DB.Close();
    }
    CATCH(CDBException,  e)
    {
        AfxMessageBox("创建错误：" + e->m_strError);
    }
    END_CATCH;
    MessageBox("创建成功！");

}

效果：

读取Excel

//读取Excel
void CExcelDlg::OnBUTTONin() 
{
    // TODO: Add your control notification handler code here

    CDatabase DB;
    CString StrSQL;
    CString StrDsn;

    //创建ODBC数据源连接字符串
    StrDsn.Format("ODBC;DRIVER={MICROSOFT EXCEL DRIVER (*.XLS)};DSN='';DBQ=f:\\teachers.xls");
    TRY
    {
        //打开Excel文件
        DB.Open(NULL, false, false, StrDsn);  
        CRecordset DBSet(&DB);

        //设置读取的查询语句
        StrSQL = "SELECT * FROM Teachers";   

        //执行查询语句
        DBSet.Open(CRecordset::forwardOnly, StrSQL, CRecordset::readOnly);

        //获取查询结果
        CString StrInfo = "职工号, 姓名\n";
        while(!DBSet.IsEOF())
        {
            //读取Excel内部数值
            for(int i=0; i<DBSet.GetODBCFieldCount(); i++)
            {
                CString Str;
                DBSet.GetFieldValue(i, Str);
                StrInfo += Str + " ";
            }
            StrInfo += "\n";
            DBSet.MoveNext();
        }
        MessageBox(StrInfo);  //在信息框中显示
        DB.Close();
    }
    CATCH(CDBException, e) 
    {
        AfxMessageBox("数据库错误：" + e->m_strError);
    }
    END_CATCH;
}

效果：

将Excel中的内容导入到数据库

void CDataInputExput::OnButtonIn() 
{
    // TODO: Add your control notification handler code here

    CString FilePathName;
    CString FileName;
    CFileDialog dlg(TRUE, //TRUE为OPEN对话框，FALSE为SAVE AS对话框
        NULL, 
        NULL,
        OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,
        (LPCTSTR)_TEXT("Excel Files (*.xls)|*.xls|*.xls|All Files (*.*)|*.*||"),
        NULL);
    if(dlg.DoModal()==IDOK)
    {
        FilePathName=dlg.GetPathName(); 
        FileName = dlg.GetFileName();
    }
    else
    {
         return;
    }

////////////////////////////////////////////////////////////////////////////////////
//读取Excel

    CDatabase DB;
    CString StrSQL;
    CString StrDsn;
    int Which = FileName.ReverseFind('.');
    CString table = FileName.Left(Which);

    CDataBase DB1;  //自己定义的类
    DB1.Connect();
    CString cellid, traff, thtraff, rate, congsnum, callnum, callcongs, nTCH, DATE, TIME;

    //创建ODBC数据源连接字符串
    int n = FilePathName.Replace("\\","\\\\");;
    StrDsn.Format("ODBC;DRIVER={MICROSOFT EXCEL DRIVER (*.xls)};DSN='';DBQ=%s", FilePathName);
    AfxMessageBox(StrDsn);

    TRY
    {
        //打开Excel文件
        DB.Open(NULL, false, false, StrDsn);  
        CRecordset DBSet(&DB);
        //设置读取的查询语句
        StrSQL.Format("SELECT * FROM %s", table); 
        AfxMessageBox(StrSQL);

        //执行查询语句
        DBSet.Open(CRecordset::forwardOnly, StrSQL, CRecordset::readOnly);
        //获取查询结果 ;
        while(!DBSet.IsEOF())
        {
            //读取Excel内部数值
            DBSet.GetFieldValue((short)0, cellid); AfxMessageBox(cellid);
            DBSet.GetFieldValue(1, traff);
            DBSet.GetFieldValue(2, thtraff);
            DBSet.GetFieldValue(3, rate);
            DBSet.GetFieldValue(4, congsnum);
            DBSet.GetFieldValue(5, callnum);
            DBSet.GetFieldValue(6, callcongs);
            DBSet.GetFieldValue(7, nTCH);
            DBSet.GetFieldValue(8, DATE);
            DBSet.GetFieldValue(9, TIME);

            //导入数据库
            CString sql;
            sql.Format("insert into data values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",cellid, traff, thtraff, rate, congsnum, callnum, callcongs, nTCH, DATE, TIME);
            AfxMessageBox(sql);
            DB1.ExecuteSQL((_bstr_t)sql);

            DBSet.MoveNext();
        }
        AfxMessageBox("导入成功！");
        DB.Close();
        DB1.ExitConnect();
    }
    CATCH(CDBException, e) 
    {
        AfxMessageBox("读取失败：" + e->m_strError);
    }
    END_CATCH;

}

将数据库中的内容导出到Excel

void CDataInputExput::OnButtonOut() 
{
    // TODO: Add your control notification handler code here

    //////////////////////////////////////////////////////////////////////////////

    //创建Excel文件
    CDatabase DB;

    //Excel安装驱动
    CString StrDriver = "MICROSOFT EXCEL DRIVER (*.XLS)";

    //要建立的Execel文件
    CString StrExcelFile = "f:\\dataOutput.xls";
    CString StrSQL;
    StrSQL.Format("DRIVER={%s};DSN='';FIRSTROWHASNameS=1;READONLY=FALSE;CREATE_DB=%s;DBQ=%s",StrDriver,StrExcelFile,StrExcelFile);
    TRY
    {
        //创建Excel表格文件
        DB.OpenEx(StrSQL, CDatabase::noOdbcDialog);
        //创建表结构，字段名不能是Index
        StrSQL = "CREATE TABLE dataOutput(CELLID TEXT, traff TEXT, thtraff TEXT, rate TEXT, congsnum TEXT, callnum TEXT, callcongs TEXT, nTCH TEXT, DATEs TEXT, TIMEs TEXT)";
        DB.ExecuteSQL(StrSQL);

        CDataBase DB1;  //自己定义的类
        DB1.Connect();
        CString sql = "select * from data where cellid = 3";
        DB1.m_Recordset = DB1.GetRecordSet((_bstr_t)sql);
        CString cellid, traff, thtraff, rate, congsnum, callnum, callcongs, nTCH, DATE, TIME;

        while(!DB1.m_Recordset->adoEOF)
        {
            //获取记录集中的数据
            cellid = (char *)(_bstr_t)DB1.m_Recordset->GetCollect("cellid");  
            traff = (char *)(_bstr_t)DB1.m_Recordset->GetCollect("traff");
            thtraff = (char *)(_bstr_t)DB1.m_Recordset->GetCollect("thtraff");
            rate = (char *)(_bstr_t)DB1.m_Recordset->GetCollect("rate");
            congsnum = (char *)(_bstr_t)DB1.m_Recordset->GetCollect("congsnum");
            callnum = (char *)(_bstr_t)DB1.m_Recordset->GetCollect("callnum");
            callcongs = (char *)(_bstr_t)DB1.m_Recordset->GetCollect("callcongs");
            nTCH = (char *)(_bstr_t)DB1.m_Recordset->GetCollect("nTCH");
            DATE = (char *)(_bstr_t)DB1.m_Recordset->GetCollect("DATE");
            TIME = (char *)(_bstr_t)DB1.m_Recordset->GetCollect("TIME");

            //向Excel插入数值
            StrSQL.Format("INSERT INTO dataOutput values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')", cellid, traff, thtraff, rate, congsnum, callnum, callcongs, nTCH, DATE, TIME);
            DB.ExecuteSQL(StrSQL);

            DB1.m_Recordset->MoveNext();
        }

        MessageBox("导出成功！数据已导出到f:\\dataOutput.xls中");
        //关闭数据库
        DB.Close();
        DB1.ExitConnect();
    }
    CATCH(CDBException,  e)
    {
        AfxMessageBox("导出错误：" + e->m_strError);
    }
    END_CATCH;
}

CDataBase的定义在之前的博客中：
MFC通过ADO连接 SQL 2005
http://blog.csdn.net/u012319493/article/details/50548387