EXCEL转为数据库

   #region excel 转为数据库记录         /// <summary>         /// excel 转为数据库记录         /// </summary>         /// <param name="tbname">表名</param>         /// <param name="filePath">文件路径</param>         public static int ExcelToTable(string tbname, string filePath, string reDepartmentCode, string creatdata,string month)         {             System.Data.DataTable dt = new DataTable();             //连接excel             string strExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;";             OleDbConnection conn = new OleDbConnection(strExcel);

            if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)             {                 conn.Open();             }

            System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);             //execel转为dataset

            //获取Excel的第一个Sheet名称             string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();             //查询sheet中的数据             string strSql = "select * from [" + sheetName + "$]";             OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);             da.Fill(dt);             conn.Close();             //利用dataset 向数据库中写入数据             string sqls = "";

            int i = 0;//影响的行数             using (SqlConnection connection = SqlHelper.GetConn())             {

                using (SqlCommand cmd = new SqlCommand())                 {                     connection.Open();                     SqlTransaction myTrans = connection.BeginTransaction();//定义连接的事务                     cmd.Connection = connection;//定义cmd的链接属性                     cmd.Transaction = myTrans;//定义cmd的事务属性                     i = dt.Rows.Count;                     try                     {                         foreach (DataRow dr in dt.Rows)                         {                             sqls = string.Format("Insert into  " + tbname + "  (Sfqs,mc ,jlbh ,ccbh ,ggxh ,clfw ,sccj ,azwz ,gllb ,jddw ,sycj ,bz ,bgr ,xcjdrq ,createDate,monthplan,departmentCode,dqzt, Sfjd  )  values(" +                                 " '{0}', '{1}', '{2}', '{3}', '{4}','{5}','{6}', '{7}', '{8}', '{9}' , '{10}' , '{11}' , '{12}' , '{13}','{14}','{15}','{16}','{17}','{18}' ) ", "1", dr["名称"].ToString().Trim(), dr["计量编号"].ToString().Trim(),                                 dr["出厂编号"].ToString().Trim(), dr["规格型号"].ToString().Trim(), dr["测量范围"].ToString().Trim(), dr["生产厂家"].ToString().Trim(), dr["安装位置"].ToString().Trim(),                             dr["类别"].ToString().Trim(), dr["检定单位"].ToString().Trim(), dr["使用车间"].ToString().Trim(), dr["班组"].ToString().Trim(), dr["保管人"].ToString().Trim(), dr["计划检定日期"].ToString().Trim(), creatdata, month, reDepartmentCode,"在用合格",'0');                             cmd.CommandText = sqls;                             i = cmd.ExecuteNonQuery();                         }                         myTrans.Commit();                         return i;                     }                     catch (System.Data.SqlClient.SqlException e)                     {                         myTrans.Rollback();                         conn.Close();                         connection.Dispose();                         System.Diagnostics.Debug.WriteLine(e.Message);                         throw e;                     }                 }             }         }         #endregion