.net core ajax使用EPPlus上傳excle導入總結

前端

<form class="layui-form" id="div_imp" style="display:none;">
            <div class="layui-row">
                <div class="layui-col-xs11">
                    <div class="layui-form-item">
                    </div>
                </div>
            </div>
            <div class="layui-row">
                <div class="layui-col-xs11">
                    <div class="layui-form-item">
                        <label for="Goodsno" class="layui-form-label">
                            <span>附件</span>
                        </label>
                        <div class="layui-input-block">
                            <input type="file" name="goodsfile" id="goodsfile" class="layui-input" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel">
                        </div>
                    </div>
                </div>
                <div class="layui-col-xs11">
                    <div class="layui-form-item">
                        <label for="Goodsno" class="layui-form-label">
                            <span></span>
                        </label>
                        <div class="layui-input-block">
                            <span class="we-red"><a href="~/import/商品導入模板.xlsx">點我下載最新模板</a></span>
                        </div>
                    </div>
                </div>
            </div>
            <div class="layui-row">
                <div class="layui-form-item">
                    <label for="L_repass" class="layui-form-label">
                    </label>
                    <button class="layui-btn" lay-filter="import" lay-submit="" onclick="import_submit()">導入</button>
                </div>
            </div>
        </form>

<javascript>
function import_submit() {
            var file;
            $('#import').attr('disabled', true);//.find('i').removeClass('layui-hide');
            var fordata = new FormData(); 
            fordata.append("file", $('#goodsfile')[0].files[0]); //傳給後臺的file的key值是可以自己定義的
            $.ajax({
                url: "/Goods/Import",
                type: "POST",
                data: fordata,
                cache: false,
                processData: false,
                contentType: false,
                success: function (res) {
                    if (res.code == 0) {
                        parent.layer.close(index);
                        parent.layer.msg('導入成功', { time: 3000 });
                        parent.table.reload("test", { where: { "key": $("#goodsname").val() } });
                    } else {
                        layer.msg("導入失敗 " + res.msg, { time: 3000 });
                        $('#add').attr('disabled', false).find('i').removeClass('layui-hide');
                    }
                },
                error: function (e) {
                    $('#add').attr('disabled', false).find('i').removeClass('layui-hide');
                    layer.msg("導入失敗 " + res.msg, { time: 3000 });
                }
            });
        }
    </script>

後端

[HttpPost]
        public IActionResult Import([FromForm(Name = "file")]IFormFile excelfile)
        {
            HandleResult<string> result = new HandleResult<string>{ Code=(int)StatusCodeEnum.ERROR_SYSTEM, success=false, Msg=StatusCodeEnum.ERROR_SYSTEM.ToDescription() };
            string sWebRootFolder = _hostingEnvironment.WebRootPath+ @"\import_tmp\";
            string sFileName = $"{DateTime.Now.ToString("yyyyMMddHHmmssfff")}-{Guid.NewGuid()}.xlsx";
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            if (file.Exists)
            {
                file.Delete();
                file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            }
            try
            {
                using (FileStream fs = new FileStream(file.ToString(), FileMode.Create))
                {
                    excelfile.CopyTo(fs);
                    fs.Flush();
                }
                using (ExcelPackage package = new ExcelPackage(file))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                    int rowCount = worksheet.Dimension.Rows;
                    int ColCount = worksheet.Dimension.Columns;
                    var goods = new List<pub_goods>();
                    if (file.Exists)
                    {
                        file.Delete();
                    }
                    for (int row =2; row <= rowCount; row++)
                    {
                        
                            pub_goods pub_Goods = new pub_goods();
                            pub_Goods.GoodsID = Guid.NewGuid().ToString();
                            pub_Goods.CompanyID = Application.WebContext.CompanyID;
                            pub_Goods.State = "1";
                            pub_Goods.Createtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                            pub_Goods.Is_del = "0";
                            pub_Goods.Goodsno = worksheet.Cells[row, 1].Value == null ? "" : worksheet.Cells[row,1].Value.ToString();
                            pub_Goods.Goodsname = worksheet.Cells[row,2].Value == null ? "" : worksheet.Cells[row, 2].Value.ToString();
                            pub_Goods.Goodstype = worksheet.Cells[row, 3].Value == null ? "" : worksheet.Cells[row, 3].Value.ToString();
                            pub_Goods.Unit = worksheet.Cells[row, 4].Value == null ? "" : worksheet.Cells[row, 4].Value.ToString();
                            pub_Goods.Goodscode = worksheet.Cells[row, 5].Value == null ? "" : worksheet.Cells[row, 5].Value.ToString();
                            pub_Goods.Approval = worksheet.Cells[row, 6].Value == null ? "" : worksheet.Cells[row, 6].Value.ToString();
                            pub_Goods.Factory = worksheet.Cells[row, 7].Value == null ? "" : worksheet.Cells[row, 7].Value.ToString();
                            pub_Goods.dosage = worksheet.Cells[row, 8].Value==null?"": worksheet.Cells[row, 8].Value.ToString();
                            goods.Add(pub_Goods);
                       
                    }
                    
                    result =_goodsService.AddListAsync(goods).Result;
                }
            }
            catch (Exception ex)
            {
                Logger.Default.ProcessError((int)StatusCodeEnum.ERROR_SYSTEM, ex.Message);
            }
            return Ok(result);
        }

技術要點:

1.上傳附件<input type="file" name="goodsfile" id="goodsfile" class="layui-input" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel">

  accept可設置預設允許的文件類型,也可選擇其他文件類型,需要後臺加判斷限制文件類型.

2.使用IFormFile一定要加上[FromForm(Name = "file")],否則IFormFile為null,file為前端自定義的文件參數名

3.附件添加 var fordata = new FormData();  fordata.append("file", $('#goodsfile')[0].files[0]);  processData和contentType一定要設為false