//创建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

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;

}

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;

}

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;

}