MFC 导入EXCEL到数据库

void TaskDlg::importExcel(CString filePathName)
{
    //开始导入操作
    //定义变量
    CApplication m_appExcel;       // Excel应用程序
    CWorkbooks m_books;
    CWorkbook m_book;
    CWorksheets m_sheets; 
    CWorksheet m_sheet;
    CRange m_range;          //选择范围

    LPDISPATCH lpDisp = NULL;
    // TODO: 在此添加控件通知处理程序代码
    // 初始化Com
    if (::CoInitialize( NULL ) == E_INVALIDARG)
    {
        MessageBox(L"初始化Com失败!");
    }

    // 启动Excel
    if ( !m_appExcel.CreateDispatch(_T("Excel.Application"), NULL))
    {
        MessageBox(_T("创建Excel失败!"));
        ::CoUninitialize();
    }

    ///*判断当前Excel的版本*/
    //CString strExcelVersion = m_appExcel.get_Version();
    //int iStart = 0;
    //strExcelVersion = strExcelVersion.Tokenize(_T("."), iStart);
    //if (_T("10") == strExcelVersion)
    //{
    //    AfxMessageBox(_T("当前Excel的版本是2002。"));
    //}
    //else if (_T("11") == strExcelVersion)
    //{
    //    AfxMessageBox(_T("当前Excel的版本是2003。"));
    //}
    //else if (_T("12") == strExcelVersion)
    //{
    //    AfxMessageBox(_T("当前Excel的版本是2007。"));
    //}
    //else if (_T("14") == strExcelVersion)
    //{
    //    AfxMessageBox(_T("当前Excel的版本是2010。"));
    //}
    //else
    //{
    //    AfxMessageBox(_T("当前Excel的版本是其他版本。"));
    //}
    //m_appExcel.put_Visible(TRUE);
    //m_appExcel.put_UserControl(FALSE);

    /*得到工作簿容器*/
    m_books.AttachDispatch(m_appExcel.get_Workbooks()); 
    /*打开一个工作簿*/
    CString strBookPath = filePathName;
    try
    {
        /*打开一个工作簿*/
        lpDisp = m_books.Open(strBookPath, 
            vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,
            vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, 
            vtMissing, vtMissing, vtMissing, vtMissing);
        m_book.AttachDispatch(lpDisp);
    }
    catch(...)
    {
        AfxMessageBox(L"Excel打开失败，请重新操作！");
        return;
    }
    m_sheets.AttachDispatch(m_book.get_Sheets());

    int sheetIndex = 1;//sheetIndex = 1表示任务表，sheetIndex = 2表示条码表
    for (sheetIndex = 1; sheetIndex <= 2; sheetIndex++)
    {
        try
        {
            /*打开一个已有的Sheet*/
            lpDisp = m_sheets.get_Item(_variant_t((short)sheetIndex));
            m_sheet.AttachDispatch(lpDisp);
        }
        catch(...)
        {
            AfxMessageBox(L"Sheet%d未找到，请重新操作！", sheetIndex);
            return;
        }

        // 读取已经使用区域的信息，包括已经使用的行数、列数、起始行、起始列
        CRange usedRange;
        usedRange.AttachDispatch(m_sheet.get_UsedRange());
        m_range.AttachDispatch(usedRange.get_Rows());
        long iRowNum = m_range.get_Count();                  //已经使用的行数

        m_range.AttachDispatch(usedRange.get_Columns());
        long iColNum = m_range.get_Count();                  //已经使用的列数

        if (iRowNum <= 1)
        {
            AfxMessageBox(L"当前选择的Excel中没有要导入的数据！");
            return;
        }

        COleVariant vResult;
        int i,j=0;
        ////标题行 check the title
        //for (j=1; j<iColNum; j++)
        //{
        //    m_range.AttachDispatch(m_sheet.get_Cells());
        //    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)1 ),COleVariant((long)j)).pdispVal );
        //    vResult =m_range.get_Value2();
        //    CString  str1 = vResult.bstrVal;
        //}
        vector<_ParameterPtr> para;//参数
        try
        {
            //开启事务
            dbAccess->conn->BeginTrans();
            //任务表
            if (sheetIndex == 1)
            {
                //查询本地数据库中已经存在的任务ID
                set<CString> existTaskIds;//数据库中已存在的任务ID
                CString strExistSQL = L"SELECT C_ID FROM tab_task";
                _RecordsetPtr m_ExistRecodeSet = dbAccess->ExecuteReader((LPCTSTR)strExistSQL, para, adCmdText);
                while(!m_ExistRecodeSet->adoEOF)
                {
                    CString existTaskId;
                    _variant_t var = m_ExistRecodeSet->GetCollect(_variant_t((long)0));
                    if (var.vt != VT_NULL)
                        existTaskId = var.bstrVal;
                    existTaskIds.insert(existTaskId);
                    m_ExistRecodeSet->MoveNext();
                }

                int cstatus = 0;
                int cpriority = 0;
                int cgroupNum = 0;
                CString cid = L"";
                CString cmachine = L"";    
                CString ccustom = L"";
                CString cspecial = L"";
                CString cmodel = L"";
                CString cwriteTime = L"";
                CString cmodulus = L"";
                CString cbatteryPlate = L"";
                CString cbattery = L"";
                CString cassemble = L"";
                CString cchargingGroup = L"";

                //vector<_ParameterPtr> para;//参数
                CString strTaskSQL_tmp = L"INSERT INTO tab_task (c_id, c_status, c_machine, c_priority, c_custom, c_special, c_model, c_write_datetime, c_group_num, c_modulus, c_battery_plate, c_battery, c_assemble, c_charging_group)";
                strTaskSQL_tmp.Append(L" VALUES (%s, %d, %s, %d, %s, %s, %s, %s, %d, %s, %s, %s, %s, %s)");
                //数据行
                for ( i=2; i<= iRowNum; i++)
                {
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)1)).pdispVal );//任务ID
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                    {
                        cid = vResult.bstrVal;
                        // 判断本地数据库中是否已经存在当前任务，如果存在，无需导入
                        if (existTaskIds.find(cid) != existTaskIds.end())
                            continue;
                    }

                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)3)).pdispVal );//打标日期
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cwriteTime = vResult.bstrVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)4)).pdispVal );//打标机器
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cmachine = vResult.bstrVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)6)).pdispVal );//客户
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        ccustom = vResult.bstrVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)7)).pdispVal );//型号
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cmodel = vResult.bstrVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)8)).pdispVal );//系数
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cmodulus = vResult.bstrVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)9)).pdispVal );//组数
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cgroupNum = (int) vResult.dblVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)10)).pdispVal );//专用
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cspecial = vResult.bstrVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)12)).pdispVal );//电池类型
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cbattery = vResult.bstrVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)13)).pdispVal );//极板
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cbatteryPlate = vResult.bstrVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)14)).pdispVal );//装配类型
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cassemble = vResult.bstrVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)15)).pdispVal );//充电机组
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cchargingGroup = vResult.bstrVal;

                    //插入到本地Access
                    CString strTaskInsertSQL;//insert语句
                    strTaskInsertSQL.Format(strTaskSQL_tmp, L"'"+ cid + L"'", cstatus, L"'"+ cmachine + L"'", cpriority, L"'"+ ccustom + L"'", L"'"+ cspecial + L"'", L"'"+ cmodel + L"'",  L"'"+ cwriteTime + L"'", cgroupNum, L"'"+ cmodulus + L"'", L"'"+ cbatteryPlate + L"'", L"'"+ cbattery + L"'", L"'"+ cassemble + L"'", L"'"+ cchargingGroup + L"'");
                    dbAccess->ExecuteNonQuery((LPCTSTR)strTaskInsertSQL, para, adCmdText);    
                }
            }
            //条码表
            else if (sheetIndex == 2)
            {
                ////查询本地数据库中已经存在的任务ID
                //set<CString> existTaskIds;//数据库中已存在的任务ID
                //CString strExistSQL = L"SELECT COUNT(*) FROM tab_barcode WHERE C_BARCODE_ID ='" + barCodeTaskId + "'";
                //_RecordsetPtr m_ExistRecodeSet = dbAccess->ExecuteReader((LPCTSTR)strExistSQL, para, adCmdText);
                //variant_t vCount = m_ExistRecodeSet->GetCollect(_variant_t((long)0));
                //if (vCount.lVal == 0)
                //    return;//没有记录，返回

                CString barCodeTaskId = L"";
            
                long csxm = 0;//顺序码
                CString cid = L"";
                int cstatus = 0;
                CString ctaskId = L"";
                CString cplainCode = L"";
                CString csecretCode = L"";

                //vector<_ParameterPtr> para;//参数
                CString strBarCodeSQL_tmp = L"INSERT INTO tab_barcode (c_barcode_id, c_taskid, c_status, c_plain_code, c_secret_code, c_sxm, c_inputdate) VALUES (%s, %s, %d, %s, %s, %d, date())";
                //数据行
                for ( i=2; i<= iRowNum; i++)
                {
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)5)).pdispVal );//条码ID
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cid = vResult.bstrVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)1)).pdispVal );//任务ID
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                    {
                        ctaskId = vResult.bstrVal;
                        if (barCodeTaskId == ctaskId)
                        {
                            csxm = csxm + 1;//相同任务的任务的条码累加
                        }else
                        {
                            barCodeTaskId = ctaskId;
                            csxm = 1;//不同的任务的条码从1开始
                        }
                    }
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)2)).pdispVal );//明码
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cplainCode = vResult.bstrVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)3)).pdispVal );//暗码
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        csecretCode = vResult.bstrVal;
                    m_range.AttachDispatch(m_sheet.get_Cells());
                    m_range.AttachDispatch(m_range.get_Item (COleVariant((long)i),COleVariant((long)4)).pdispVal );//打标状态
                    vResult = m_range.get_Value2();
                    if (vResult.vt!=VT_NULL)
                        cstatus = (int) vResult.dblVal;
                

                    //插入到本地Access
                    CString strBarCodeInsertSQL;//insert语句
                    //插入到本地Access
                    strBarCodeInsertSQL.Format(strBarCodeSQL_tmp, L"'"+ cid + L"'", L"'"+ ctaskId + L"'", cstatus, L"'"+ cplainCode + L"'", L"'"+ csecretCode + L"'", csxm);
                    dbAccess->ExecuteNonQuery((LPCTSTR)strBarCodeInsertSQL, para, adCmdText);
                }

            }
            //提交事务
            dbAccess->conn->CommitTrans();
        }catch (...)
        {
            AfxMessageBox(L"操作失败，数据回滚！");
            dbAccess->conn->RollbackTrans();

        }
    }
    
    //绑定数据列表
    DataListBind();

    /*释放资源*/
    m_sheet.ReleaseDispatch();
    m_sheets.ReleaseDispatch();
    m_book.ReleaseDispatch();
    m_books.ReleaseDispatch();
    m_appExcel.Quit();
    m_appExcel.ReleaseDispatch();
    
}