跟大家一起学习，一起进步 2005 坚强 努力 整洁

自己写的excel导入sqlserver函数

public static bool import_excel(string excelPath)
    {
     bool result = false;
     //本地连接】
     string myConnectString = "Server=localhost;Database=database;uid=sa;pwd=×××××;";
     SqlConnection objConnection = new SqlConnection(myConnectString);

    //创建一个excel应用程序

     Excel.Application objApplication = new Excel.Application();
     //创建一个工作簿
     Excel._Workbook objWorkbook = objApplication.Workbooks.Add(excelPath);
     Excel._Worksheet sh  = new WorksheetClass();
     try
     {
      string strSql = string.Empty;
      StringBuilder objBuilder = new StringBuilder();
      string mubiaotable = string.Empty;
      
      //如果只想用程序控制该excel而不想让用户操作时候，可以设置为false
      objApplication.Visible = false;
      objApplication.UserControl = true;
      // sheetName为Excel文件中Sheet的名子
     string sheetName  = string.Empty;
  
  
      //然后通过它里面Excel.Application,Excel.Workbook,Excel.Worksheet,Excel.Range对像获得一些你想要的信息
      for (int i = 1; i<= objWorkbook.Sheets.Count;i++)
      {
       取得excel 的工作表
       sh = ( Excel._Worksheet)objWorkbook.Sheets[i];
       objConnection.Open();
       objBuilder.Append(sh.Name);
       objBuilder.Append("$");
       //excel工作表名
       sheetName = objBuilder.ToString();
      //sqlserver的表名
       mubiaotable = objBuilder.ToString();
  
       //把excel的工作表读出写入sqlserver表（sql语句经常出错）
       strSql =" select * into "+mubiaotable+" from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE="+excelPath+"',"+sheetName+")";
       SqlCommand  objCMD = new SqlCommand(strSql,objConnection);
       objCMD.ExecuteNonQuery();
       objConnection.Close();
      //
       objBuilder.Remove(0,objBuilder.Length);
      }
      result = true;
     退出应用程序
      objApplication.Quit();

     }
     catch(CSystem.CException ex)
     {
      throw new CSystem.CException(ex.ToString());
     }
     //释放进程
     finally
     {
      System.Runtime.InteropServices.Marshal.ReleaseComObject(objApplication);
      System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkbook);
      System.Runtime.InteropServices.Marshal.ReleaseComObject(sh);
      objApplication = null;
      objWorkbook    = null;
      sh             =  null;
      GC.Collect();
      objConnection.Close();
      objConnection = null;  
     }
     return result;
    }  

posted on 2005-04-28 13:45 icfarmer 阅读(2378) 评论(5)  编辑 收藏