C# MVC 实现导入导出

导入导出引用NPOI

 视图

  <input type="button" name="name" class="btn btn-success btn-sm" onclick="GetExcel()" value="导出"  />
            <form action="/Default/Import" method="post" enctype="multipart/form-data">
                <input type="file" name="file" id="file" />
                <input type="submit" name="name" class="btn btn-success btn-sm" value="导入" />
            </form>
<script>
    //导出
      function GetExcel() {
    //window.location.href刷新当前页面,当前页面打开URL页面,同步提交
    window.location.href = "@Url.Action("ExportByNPOI")";
        }
</script>

控制器

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using System.Web;
using System.Web.Mvc;
using 导入导出.Models;

namespace 导入导出.Controllers
{
    public class DefaultController : Controller
    {
        /// <summary>
        /// 数据存储
        /// </summary>
        //List<StudentViewModel> stu = new List<StudentViewModel>()
        //{
        //     new StudentViewModel{Id=1,Name="小明",Sex="男",DateTime="2019-11-07",Static=1},
        //     new StudentViewModel{Id=2,Name="小红",Sex="女",DateTime="2019-11-08",Static=0},
        //     new StudentViewModel{Id=3,Name="小兰",Sex="女",DateTime="2019-11-09",Static=0},
        //     new StudentViewModel{Id=4,Name="小天",Sex="男",DateTime="2019-11-11",Static=1},
        //     new StudentViewModel{Id=5,Name="阿亮",Sex="男",DateTime="2019-11-12",Static=0},
        //     new StudentViewModel{Id=6,Name="王大力",Sex="男",DateTime="2019-11-13",Static=1}
        //};
        string sql = "select * from student";
       
        // GET: Default
        public ActionResult Index()
        {
            List<StudentViewModel> stu = MySqlDBHelper.GetList<StudentViewModel>(sql);
            return View(stu);
        }
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <returns></returns>
        public ActionResult ExportByNPOI()
        {
            List<StudentViewModel> stu = MySqlDBHelper.GetList<StudentViewModel>(sql);
            //1、获取数据源
            var result = stu;
            var list = result.Select(x => new { x.Id, x.Name, x.Sex, x.DateTime, x.Static }).ToList();
            //2、创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook excel = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //3、添加一个sheet
            NPOI.SS.UserModel.ISheet sheet = excel.CreateSheet("Sheet1");
            //给sheet1添加标题行
            NPOI.SS.UserModel.IRow head = sheet.CreateRow(0);
            head.CreateCell(0).SetCellValue("编号");
            head.CreateCell(1).SetCellValue("姓名");
            head.CreateCell(2).SetCellValue("性别");
            head.CreateCell(3).SetCellValue("入学时间");
            head.CreateCell(4).SetCellValue("状态");
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < list.Count; i++)
            {
                NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);
                row.CreateCell(0).SetCellValue(list[i].Id);
                row.CreateCell(1).SetCellValue(list[i].Name);
                row.CreateCell(2).SetCellValue(list[i].Sex);
                row.CreateCell(3).SetCellValue(list[i].DateTime);
                row.CreateCell(4).SetCellValue(list[i].Static);
            }
            //写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            excel.Write(ms);
            ms.Seek(0, System.IO.SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", "顾客信息表.xls");
        }
        /// Excel导入
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public ActionResult Import(HttpPostedFileBase file)
        {
            
            string msg="";
            if (file == null)
            {
                msg = "导入失败";
            }
            else
            {
                //1、先保存上传的excel文件（这一步与上传图片流程一致）
                string extName = file.FileName;
                string path = Server.MapPath("~/Content/Files");
                string filename = Path.Combine(path, extName);
                file.SaveAs(filename);
                //2、读取excel文件（通过oledb将excel数据填充到datatable）
                //HDR=Yes，这代表第一行是标题，不做为数据使用,IMEX的含义（0：写入，1：读取，2：读取与写入）
                string filePath = filename;//必须是物理路径
                string conStr = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info=False;Data Source=" + filePath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";

                OleDbDataAdapter adp = new OleDbDataAdapter("select * From[Sheet1$]", conStr);
                //默认读取的Sheet1,你也可以把它封装变量,动态读取你的Sheet工作表
                DataTable dt = new DataTable();
                adp.Fill(dt);
                //3、将table转化成list
                List<StudentViewModel> list = new List<StudentViewModel>();

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow item in dt.Rows)
                    {
                        list.Add(new StudentViewModel()
                        {
                            //有哪个写哪个
                            Id = int.Parse(item["编号"].ToString()),
                            Name = item["姓名"].ToString(),
                            Sex = item["性别"].ToString(),
                            DateTime = item["入学时间"].ToString(),
                            Static = int.Parse(item["状态"].ToString())
                        });
                    }
                }
                //4、跨action传值用tempdata
                //TempData["list"] = list;
                //return RedirectToAction("List");

                //如果不直接导入数据库这里不用写
                StudentViewModel model = new StudentViewModel();
                for (int i = 0; i < list.Count; i++)
                {
                    model.Id = list[i].Id;
                    model.Name = list[i].Name;
                    model.Sex = list[i].Sex;
                    model.DateTime = list[i].DateTime;
                    model.Static = list[i].Static;
                    //调用添加方法
                    //var result = await baseRepository.Add(model);
                    //if (result > 0)
                    //{
                    //  msg = "导入成功";
                    //}
                    DAL dal = new DAL();
                    int result = dal.Create(model);
                    if (result > 0)
                    {

                        msg = "导入成功！";
                    }
                }
            }
           
            return Json(msg);
        }
        public class DAL {
            public int Create(StudentViewModel model)
            {
                string sql = string.Format("insert into Student(Id,Name,Sex,DateTime,Static) values('{0}','{1}','{2}','{3}','{4}')", model.Id, model.Name, model.Sex, model.DateTime, model.Static);
                int result = MySqlDBHelper.ExecuteNonQuery(sql);
                return result;
            }
        }
    }
}