学步园

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;

    }