odbc 读取excel

最新推荐文章于 2023-07-20 11:45:28 发布

tianyu030963

最新推荐文章于 2023-07-20 11:45:28 发布

阅读量840

点赞数

CC 4.0 BY-SA版权

分类专栏： mfc

本文链接：https://blog.youkuaiyun.com/tianyu030963/article/details/11894279

mfc 专栏收录该内容

23 篇文章

订阅专栏

CString GetExcelDriver()
{
    wchar_t szBuf[2001];
wchar_t excl[] = L"Excel";
    WORD cbBufMax = 2000;
    WORD cbBufOut;
    wchar_t *pszBuf = szBuf;
    CString sDriver;
    // 获取已安装驱动的名称(函数在odbcinst.h里)
    if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
        return L"";
    // 检索已安装的驱动是否有Excel...
// AfxMessageBox(CString(pszBuf));
    do
    {
        if(wcsstr(pszBuf, excl) != 0)
        {
            //发现 !
            sDriver = CString(pszBuf);
            break;
        }
  wchar_t ze = {'\0'};
        pszBuf = wcschr(pszBuf, ze) + 1;
    }
    while (pszBuf[1] != '\0');

    return sDriver;
}

void Cmfc_xmlDlg::readExcel()
{
    CDatabase   database;
    CString   sSql;
    CStringW   sSn, sName, sDpt, sPip;
    CString   sDriver;
    CString   sDsn;
    CString   sFile("d:\\tab_2003.xls");//   将被读取的Excel文件名

    //   检索是否安装有Excel驱动   "Microsoft   Excel   Driver   (*.xls) "
    sDriver = GetExcelDriver();
    if(sDriver.IsEmpty())
    {
        //没有发现Excel驱动
        MessageBox( (LPCTSTR)"没有安装Excel驱动! ");
        return;
    }
    //   创建进行存取的字符串
    sDsn.Format( _T("ODBC;DRIVER={%s};DSN= ' ';DBQ=%s "),sDriver, sFile);
    TRY
    {
        //打开数据库(既Excel文件)
        database.Open(NULL, false, false, sDsn);
        CRecordset   recset(&database);
        //   设置读取的查询语句
        // 设置读取的查询语句.注意此处表名的写法，excel里的表默认为系统表，所以访问表时表明应为[表明$]
        sSql = "SELECT 工号, 姓名, 部门, 职责 FROM [demo$] ORDER BY 工号, 姓名, 部门,职责 ";

        //执行查询语句
        recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly);
        short nFields = recset.GetODBCFieldCount( );
        //   获取查询结果g_data[1]="111 222 333";
        while (!recset.IsEOF())
        {
            //读取Excel内部数值
            recset.GetFieldValue(_T("工号"), sSn);
            recset.GetFieldValue(_T("姓名"), sName);
            recset.GetFieldValue(_T("部门"), sDpt);
            recset.GetFieldValue(_T("职责"), sPip);

            wchar_t * wch= sSn.GetBuffer(sizeof(sSn));
            char ch[100] = {0};
            WideCharToMultiByte( CP_ACP, 0, wch, -1, ch, 100, NULL, NULL );
            int num = atoi(ch);

            wch= sName.GetBuffer(sizeof(sName));
            WideCharToMultiByte( CP_ACP, 0, wch, -1, ch, 100, NULL, NULL );
            string strMsg = ch;
            strMsg += " ";

            wch= sDpt.GetBuffer(sizeof(sDpt));
            WideCharToMultiByte( CP_ACP, 0, wch, -1, ch, 100, NULL, NULL );
            strMsg += ch;
            strMsg += " ";

            wch= sPip.GetBuffer(sizeof(sPip));
            WideCharToMultiByte( CP_ACP, 0, wch, -1, ch, 100, NULL, NULL );
            strMsg += ch;

            g_data[num] = strMsg;
            recset.MoveNext();
        }
        //   关闭数据库
        database.Close();
    }
    CATCH(CDBException,   e)
    {
        //   数据库操作产生异常时...
        MessageBox( _T("数据库错误:") + e->m_strError);
    }
    END_CATCH;
}