ado excel到access

最新推荐文章于 2025-08-19 12:01:15 发布

heyangya2009

最新推荐文章于 2025-08-19 12:01:15 发布

阅读量757

点赞数

CC 4.0 BY-SA版权

文章标签： excel access properties 数据库 insert class

本文链接：https://blog.youkuaiyun.com/heyangya2009/article/details/4733993

本文介绍了一种使用OLE DB从Excel文件读取数据并将其导入Access数据库的方法。通过实例展示了如何利用COM对象模型进行跨应用数据操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

原理是从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;
}