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(); }
本文转自农夫山泉别墅博客园博客，原文链接：http://www.cnblogs.com/yaowen/archive/2013/01/21/2869781.html，如需转载请自行联系原作者
