关于EXCEL导入数据库的解决方法

从EXCEL导入数据库网上的讨论有很多,本人在实践中使用和参考了各种方法,总结如下:

   一、在导入SQL SERVER时，可以采用DTS包方式，没有实际做过，对于定期导入的情况可能比较好，用于实时导入的情况可能不是很好，因为一般都是建好一个包后，调用DBMS的计划定于某个时间运行的。

   二、完全的存储过程解决，将导入文件上传到服务器后，写个存储过程通过 OPENDATAROWSOURCE方法读出EXCEL内容到临时表，在通过临时表处理，导入物理表。采用这种方式会有一个问题：如果数据库登陆用户没有system Administrator角色，则会提示：“已拒绝对Microsoft OleDb"的访问，必须建立一个链接数据库。由于 system Administrator角色一般不会分配给应用系统的登陆角色，所以比较难解决。这种方法的好处是能用临时表处理，一次导入一个临时表，完全在一个存储过程处理，结构比较分明。

   一个小例子：

--生成要导入的临时表 
 set @per =  '''Data Source="'+@FileName+'";User ID=Admin;Password=;Extended properties=Excel 5.0'''
 exec ('select 序号,资产条码,cast(项目名称 as varchar(100)) 项目名称,资产名,制造商,序列号,型号, 其他特征,使用时间,资产价值,cast ( 应用领域 as varchar(100))  应用领域, cast(所属部门 as varchar(100)) 所属部门,cast(资产地点 as varchar(100)) 资产地点, 详细地点 ,使用人,责任人,合同编号,新增批次,备注,网元编号,机架信息,资产来源 ,'''+@BillID+''' as 单据编号 into ##temp2
  FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'','+@per+')...[资产导入模板$] ')
 if @@Error>0
 begin
  select '失败' as Flg,'' as 序号,'' as 资产条码,'资产导入不成功' as ErrorInfo
  return
 end

   三、以前用ASP的时候还用过执行 XP_CMDSHEET的方式导入，这中导入发现对于一串长数字的单元格内容它智能的采用了科学计数发。

  四、现在我觉得比较的方式应该是在应用程序里完成EXCEL的打开读取操作的，因为这样可以避免数据库权限不够引起的读写EXCEL错误，在应用程序里读取也是跟读取数据库一样的，只是打开的数据库驱动不一样罢了，下面是我用C sharp读取的实现方法：

//读取EXCEL内容返回一个DATASET

public DataSet getExcelDataSet(string strxls,string strSheetName)
  {
   
//注意连接的驱动是  Microsoft.Jet.OLEDB.4.0，附件属性还有：Extended Properties=Excel 8.0//表示是97-2000版本，HDR=YES/NO//第一行是否为标题， IMEX=1//表示以文本读取。

 OleDbConnection myOleConn = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source="+strxls+"; Extended Properties=Excel 8.0;");
   OleDbDataAdapter MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from ["+strSheetName+"$]", myOleConn);
   DataSet DS = new System.Data.DataSet();
   MyCommand.Fill(DS);
   
   myOleConn.Close();
   return DS;
  }

//将读取的dataset内容写入表中  

public bool ImportExcelToSqlTable(string strxls,string strSheetName,string TableName,string BillID)
  {
    try
   {
   
    DataSet XlsDs = getExcelDataSet(strxls,strSheetName);
    string strInsertComm;
    for(int i = 0 ; i<XlsDs.Tables[0].Rows.Count;i++)
    {
     strInsertComm="";
     strInsertComm = "Insert T_AddTemp(序号,资产条码,项目名称,资产名,类项目节,制造商,序列号,型号,其他特征,使用时间,资产价值,应用领域,资产来源,所属部门,资产地点,详细地点,使用人,责任人,合同编号,新增批次,备注,网元编号,机架信息,新增单据编号,FileName)";
     strInsertComm +=" values(";
     for(int j=0;j<XlsDs.Tables[0].Columns.Count;j++)
     {
      if (j>0)
      {
       strInsertComm +=",'"+XlsDs.Tables[0].Rows[i][j].ToString()+"'";
      }
      else
      {
       strInsertComm +="'"+XlsDs.Tables[0].Rows[i][j].ToString()+"'";
      }
     }
     strInsertComm += ",'"+BillID+"','"+strxls.Replace("'","''")+"'";
     strInsertComm +=")";
     ExecSql(strInsertComm);
    }
    ExecSql("AstImport '"+BillID+"','"+strxls+"'");
   }
   catch
   {
    return false;
   }
   return true;
  }