ado excel到access

做了好久，出了很多错误，不过最终还是做好了^_^

原理是从EXCEL表中读一条记录出来，然后插入到ACCESS中。

int CManage::RecordFromExcelToAccess(CString excelPathName)   //将ExcelFileName中的数据导入到Access数据库中
{
 _ConnectionPtr excelConPtr,accessConPtr;
 accessConPtr.CreateInstance(__uuidof(Connection));//初始化
 excelConPtr.CreateInstance(__uuidof(Connection));//初始化
 CString excelConString=L"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+excelPathName+L";Extended Properties=Excel 8.0;";
 CString accessConString=L"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Information.mdb";
 //打开数据库和Excel表格文件
 try
 {
  accessConPtr->Open(accessConString.AllocSysString(),"","",adModeUnknown);
  excelConPtr->Open(excelConString.AllocSysString(),"","",adModeUnknown);
 }
 catch (_com_error* e)
 {
  MessageBox(e->Description());
  return 0;
 }

 CString excelSqlString=L"select * from [sheet$]";
 _RecordsetPtr   pExcelRecordset;
 pExcelRecordset.CreateInstance(__uuidof(Recordset));
 //打开记录集
 try
 {  
  pExcelRecordset->Open(excelSqlString.AllocSysString(),
   excelConPtr.GetInterfacePtr(),
   adOpenDynamic,
   adLockOptimistic,
   adCmdText);
 }
 catch(_com_error e)
 {
  excelConPtr->Close();
  MessageBox(e.Description());
  return 0;
 }
 //开始读取数据
 int count=0;
 _variant_t var;
 _RecordsetPtr p;
 _variant_t v;
 CString csSql;
 CString csClass,csNumber,csName,csUnit,csTradePrice,csRetailPrice;
 while(!pExcelRecordset->adoEOF)
 {  
  try
  {
   var=pExcelRecordset->GetCollect(_variant_t("车型")); 
   csClass=_com_util::ConvertBSTRToString(_bstr_t(var));
   var=pExcelRecordset->GetCollect(_variant_t("配件编码"));
   csNumber=_com_util::ConvertBSTRToString(_bstr_t(var));
   var = pExcelRecordset->GetCollect(_variant_t("配件名称"));
   csName=_com_util::ConvertBSTRToString(_bstr_t(var));
   var=pExcelRecordset->GetCollect(_variant_t("单位"));
   csUnit=_com_util::ConvertBSTRToString(_bstr_t(var));
   var=pExcelRecordset->GetCollect(_variant_t("单价"));
   csTradePrice=_com_util::ConvertBSTRToString(_bstr_t(var));
   var=pExcelRecordset->GetCollect(_variant_t("建议零售价"));
   csRetailPrice=_com_util::ConvertBSTRToString(_bstr_t(var));
  }
  catch (_com_error e)
  {
   if(pExcelRecordset->State==1)
    pExcelRecordset->Close();
   if(excelConPtr->State==1)
    excelConPtr->Close();
   if(accessConPtr->State==1)
    accessConPtr->Close();
   MessageBox(e.Description());
   return count;
  }
  csSql=L"select * from data where class='"+csClass+L"' and number='"+csNumber+L"'";
  try
  {
   p=accessConPtr->Execute(csSql.AllocSysString(),&v,adCmdText);
   if(p->adoEOF)
   {
    csSql=L"insert into data values('"+csClass+L"','"+csNumber+L"','"+csName+L"','"+csUnit+L"','"+csTradePrice+L"','"+csRetailPrice+L"','0')";
    accessConPtr->Execute(csSql.AllocSysString(),&v,adCmdText);
    count++;
   }
  }
  catch(_com_error e)
  {
   if(pExcelRecordset->State==1)
    pExcelRecordset->Close();
   if(excelConPtr->State==1)
    excelConPtr->Close();
   if(accessConPtr->State==1)
    accessConPtr->Close();
   MessageBox(e.Description());
   return count;
  }

  pExcelRecordset->MoveNext();
 }
 if(pExcelRecordset->State==1)
  pExcelRecordset->Close();
 if(excelConPtr->State==1)
  excelConPtr->Close();
 if(accessConPtr->State==1)
  accessConPtr->Close();
 return count;
}