.Net 上传Excel导入数据功能

public ActionResult UpLoad_Excel()
        {
            //获取上传的Excel文件
            HttpPostedFileBase File = Request.Files["file"];
            string message = "";
          
            if (File.ContentLength > 0)
            {
                //GetExtension:返回指定路径的文件的扩展名
                var Isxls = System.IO.Path.GetExtension(File.FileName).ToString().ToLower();
                if (Isxls != ".xls" && Isxls != ".xlsx")
                {
                    message = "<script>alert('请上传Excel文件')</script>";
                }
                var FileName = File.FileName;//获取文件夹名称
                var path = Server.MapPath("~/Content/FileExcel/" + FileName);
                var DeletePath = Server.MapPath("~/Content/FileExcel/");
                File.SaveAs(path);//将文件保存到服务器
                //return View();
                PositionBLL bll = new PositionBLL();
                //FileUpLoad
                var list = bll.FileUpLoad(path);
                if (list.Count > 0)
                {
                    //添加数据到数据库
                    int num = bll.LoadFile(list);
                    if (num > 0)
                    {
                        message = "<script>alert('数据导入成功')</script>";

                        //object objmissing = System.Reflection.Missing.Value;
 
                        


                        DeleteFile(DeletePath);
                    }
                }
                else
                {
                    message = "<script>alert('导入的数据不能为空')</script>";
                }
            }
            else
            {
                message = "<script>alert('请上传Excel文件'),window.location.href='/MaterialTracking/Materrial_Production_lot'</script>";
            }
            return Content(message);
        }

删除服务器上传的文件

public static void DeleteFile(string src){

            try
            {
                DirectoryInfo dir = new DirectoryInfo(src);

                System.IO.FileSystemInfo[] fileinfo = dir.GetFileSystemInfos();//返回文件中所有的文件和子目录

                foreach (System.IO.FileSystemInfo i in fileinfo)
                {
                    System.IO.File.Delete(i.FullName);
                }

            }
            catch {

                throw;
            }

        
        }

上传Excel操作类：

public class PositionBLL
    {
        //private const string ConnString2003 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0';HDR='Yes'";
        //链接打开excel的字符串
        private const string ConnString2003 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0';";
        public List<UpLoad_Info> FileUpLoad(string filePath)
        {
            DataSet ds = new DataSet();
            List<UpLoad_Info> list = new List<UpLoad_Info>();
            string strSQL = string.Format(ConnString2003, filePath);
            //OleDbConnection:表示与数据源的开放链接
            OleDbConnection conn = new OleDbConnection(strSQL);
            try
            {
                //判断连接的状态
                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                DataTable tableName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //获取Excel的第一个Sheet名称
                var sheetName = tableName.Rows[0]["TABLE_NAME"].ToString().Trim();
                string SQL = "select * from [" + sheetName + "]";
                OleDbDataAdapter sa = new OleDbDataAdapter(SQL, conn);
                sa.Fill(ds);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    UpLoad_Info model = new UpLoad_Info();
                    model.proc_id = dr["工序编号"].ToString();
                    model.mat_pn = dr["材料P/N"].ToString();
                    model.mat_proc = dalUtility.ISO8859_GB2312(dr["Process"].ToString());// (dr["Process"]).ToString();
                    model.mc_id = dr["机器ID"].ToString();
                    model.user_name =  dalUtility.ISO8859_GB2312(dr["姓名"].ToString());
                    model.user_no = dr["工号"].ToString();
                    list.Add(model);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("错误信息：PositionBLL+FileUpLoad方法" + ex);
            }
            conn.Close();  //用完一定要释放资源
            return list;
        }
        //将数据循环遍历到数据库中
       // MTS_WEB.DLL.Query.Materrial_Production_lot.PositionDAL dal = new MTS_WEB.DLL.Query.Materrial_Production_lot.PositionDAL();
        public int LoadFile(List<UpLoad_Info> list)
        {
            var num = 0;
            foreach (var item in list)
            {
                UpLoad_Info model = new UpLoad_Info();
                model.proc_id = item.proc_id;
                model.mat_pn = item.mat_pn;
                model.mat_proc = item.mat_proc;
                model.mc_id = item.mc_id;
                model.user_no = item.user_no;
                model.user_name = item.user_name;

                string sql = string.Format("insert into mat_proc_user (proc_id,mat_pn,mat_proc,mc_id,user_no,user_name)values('{0}','{1}','{2}','{3}','{4}','{5}')",
               model.proc_id, model.mat_pn, model.mat_proc, model.mc_id, model.user_no, model.user_name);

                

                num = SqlServerHelper.OracleExecuteNonQuery(sql);
            }
            return num;
        }



      

        public int Add(UpLoad_Info ui)
        {

            string sql = string.Format("insert into mat_proc_user (proc_id,mat_pn,mat_proc,mc_id,user_no,user_name)values('{0}','{1}','{2}','{3}','{4}','{5}')",
                ui.proc_id, ui.mat_pn, ui.mat_proc, ui.mc_id, ui.user_no, ui.user_name);

            SqlServerHelper.OracleExecuteNonQuery(sql);

            return ExceDml(sql);
        }

        public static int ExceDml(string sql)
        {
            try
            {

               
                int res = 0;
                res = SqlServerHelper.OracleExecuteNonQuery(sql);
                 return res;
                //连接数据库
                //using (OracleConnection conn = new OracleConnection())
                //{
                //    //打开连接数据库
                //    conn.Open();
                //    //执行sql
                //    OracleCommand comm = new OracleCommand(sql, conn);
                //    res = comm.ExecuteNonQuery();


                   
                //}
            }
            catch (Exception ex)
            {
                Console.WriteLine("错误信息：ExceDml方法" + ex);
                throw ex;
            }

        }

    }