net core 上传并使用EPPlus导入Excel文件

1.  cshtml页面 form

<form id="form" method="post" action="/SaveValueBatch"
      enctype="multipart/form-data">
<input type="file" name="uploadExcel" style="width:200px;" />
</form>

2. controller

        [HttpPost]
        public ActionResult SaveValueBatch(IFormCollection form)
        {
            try
            {
                var files =Request.Form.Files.Where(x => x.Name.Equals("uploadExcel"));

                //非空限制
                if (files == null || files.Count() <= 0) { return Json(new { isSuccess = false, message = "请选择要上传的Excel文件" }, "text/html"); }

                //格式限制
                var allowType = new string[] { "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"};
                if (files.Any(b => !allowType.Contains(b.ContentType)))
                {
                    return Json(new { isSuccess = false, message = "只能上传Excel 2007 格式文件" }, "text/html");
                }

                //大小限制
                if (files.Sum(b => b.Length) >= 1024 * 1024 * 4)
                {
                    return Json(new { isSuccess = false, message = "上传文件的总大小只能在4M以下" }, "text/html");
                }

                //写入服务器磁盘
                foreach (var file in files)
                {

                    var fileName = file.FileName;
                    var path = Path.Combine(_host.ContentRootPath+ "/Upload", fileName);
                    using (var stream = System.IO.File.Create(path))
                    {
                        file.CopyTo(stream);
                    }
                }
                return Json(new { isSuccess = true, message = "保存成功" }, "text/html");
            }
            catch (Exception e)
            {
               
                return Json(new { isSuccess = false, message = "保存失败:" + e.InnerException.Message }, "text/html");
            }
        }

        private IHostingEnvironment _host;

        public ExcelController(IHostingEnvironment host)
        {
            _host = host;
        }

 3. 读取excel内容,用context.AddRange 插入数据(这个是用Merge SQL语句操作的.), 之前用SQLServer 2005,不支持Merge语句, 只有升级到到新版本. 我选择升级到SQL2014了. (SQL 2016/2017开始支持Linux,暂时我不需要)

                        using (ExcelPackage ep = new ExcelPackage(new FileInfo(path)))
                        {
                            ExcelWorksheet ws = ep.Workbook.Worksheets[0]; //第1张Sheet

                            int colStart = ws.Dimension.Start.Column;  //工作区开始列,start=1
                            int colEnd = ws.Dimension.End.Column;       //工作区结束列
                            int rowStart = ws.Dimension.Start.Row;       //工作区开始行号,start=1
                            int rowEnd = ws.Dimension.End.Row;       //工作区结束行号

                            Guid[] columnsGuid = new Guid[colEnd+1]; //skip zero index
                            for (int k = colStart; k <= colEnd; k++)
                            {
                                string columnName = ws.Cells[1, k].Text;//列名
                             
                            }
                            for (int i = rowStart + 1; i <= rowEnd; i++) //第1行是列名,跳过
                            {
                                Guid rowGuid = Guid.NewGuid();
                                for (int j = colStart; j <= colEnd; j++)
                                {
                                    var info = new BaseInfo();
                                    info.Id = 0;
                                    info.RowGuid = rowGuid;                                    
                                    info.Guid = columnsGuid[j];
                                    info.FieldValue = ws.Cells[i, j].Text;
                                    infos.Add(info);                                   
                                   
                                }

                            }
                            _context.ChangeTracker.AutoDetectChangesEnabled = false;
                            _context.AddRange(infos);
                            _context.SaveChanges();
                            _context.ChangeTracker.AutoDetectChangesEnabled = true;


                        }

 有一个注意点: 如果你是用excel上传数据,同时做新增和修改的话, DB已有的数据,不要放在Infos 这个数组  _context.AddRange(infos);

因为如果你把DB已有的数据再AddRange,它会尝试插入一条新的记录,SQL语句里包括主键,而你的主键是自动生成的话.就会出现这个错误

System.Data.SqlClient.SqlException (0x80131904): 当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'XXXXXX' 中的标识列插入显式值。

 

转载于:https://www.cnblogs.com/zitjubiz/p/net_core_epplus_excel_upload.html