ASP.NET MVC 导入Excel文件

1.View部分内容示例如下:
<form method="post" enctype="multipart/form-data" action="/Position/ImportExcel" class="form-group">
     <input name="file" type="file" id="file" />
     <button id="btn_import" type="submit" class="btn btn-info">
            <span class="glyphicon glyphicon-pencil"></span>导入
     </button>
</form>
2.注意表单部分必须加 enctype = "multipart/form-data" ,否则后台代码获取File.ContentLength=0;
3.Controller部分
public ActionResult ImportExcel()
         {
              HttpPostedFileBase File = Request.Files["file"];
              string message = "";
               if (File.ContentLength > 0)
                {
                       var Isxls = System.IO.Path.GetExtension(File.FileName).ToString().ToLower();
                       if (Isxls != ".xls" && Isxls != ".xlsx")
                       {
                           message = "<script>alert('请上传Excel文件'),window.location.href='/Position/Index'</script>";
                        }
                var FileName = File.FileName;//获取文件夹名称
                var path = Server.MapPath("~/FileExcel/" + FileName);
                File.SaveAs(path);//将文件保存到服务器
                PositionBLL bll = new PositionBLL();
                var list = bll.FileUpLoad(path);
                if (list.Count > 0)
                {
                    int num = bll.LoadFile(list);
                    if (num > 0)
                    {
                        message = "<script>alert('数据导入成功'),window.location.href='/Position/Index'</script>";
                    }
                }
                else
                {
                    message = "<script>alert('导入的数据不能为空'),window.location.href='/Position/Index'</script>";
                }
            }
            else
            {
                message = "<script>alert('请选择上传的文件'),window.location.href='/Position/Index'</script>";
            }
            return Content(message);
        	}
    }
4. 连接Excel的字符串 private const string ConnString2003 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended  Properties='Excel 8.0;HDR=Yes'";
lic List<PositionModel> FileUpLoad(string filePath)
  {
            DataSet ds = new DataSet();
            List<PositionModel> list = new List<PositionModel>();
            string strSQL = string.Format(ConnString2003, filePath);
            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)
                {
                    PositionModel model = new PositionModel();
                    model.PositionName = dr["职位名称"].ToString();
                    model.Qualification = dr["任职资格"].ToString();
                    model.Remark = dr["职位描述"].ToString();
                    list.Add(model);
                }
            }
           catch (Exception ex)
            {
                throw;
            }
            return list;
        }
     5.将数据循环遍历到数据库中
      public int LoadFile(List<PositionModel> list)
        {
            var num = 0;
           foreach (var item in list)
             {
                 PositionModel model = new PositionModel();
                 model.PositionName = item.PositionName;
                 model.Qualification = item.Qualification;
                 model.Remark = item.Remark;
                 num = dal.Add(model);
             }
            return num;
        }

  

 

posted @ 2018-08-07 17:15  磬音  阅读(3186)  评论(3编辑  收藏  举报