.net_core6.0_mvc+layui上传Excel文件并导入到数据库

最新推荐文章于 2024-07-20 20:45:00 发布

djk8888

最新推荐文章于 2024-07-20 20:45:00 发布

阅读量712

点赞数 7

文章标签： .net mvc layui

本文链接：https://blog.csdn.net/djk8888/article/details/134921735

版权

HTML

<input type="file" id="file" style="display:none;" />
<button type="button" class="layui-btn" id="btn_Upload">导入表格</button>

 @section Scripts
        {
        <script type="text/javascript">
            layui.use(['table', 'layer', 'upload'],
                function () {
                    var table = layui.table,
                        $ = layui.$,
                        upload = layui.upload,
                        layer = layui.layer;

                    //初始化文件上传
                    upload.render({
                        elem: '#btn_Upload',//上传按钮的id
                        field: 'file',//file控件的id
                        url: '/BusinessData/Uplaod',//ajax的url
                        exts: 'xlsx',//文件类型限制
                        done: function (res) {
                            if (res.success) {
                                layer.msg(res.message, {
                                    icon: 1,
                                    time: 1000 //1秒关闭（如果不配置，默认是3秒）
                                }, function () {
                                    PageList();
                                    layer.closeAll();
                                });
                            }
                            else {
                                layer.msg(res.message);
                            }
                        },
                        error: function () {
                            layer.alert('上传失败', { icon: 2 });
                        }
                    });
                });
        </script>
    }

MVC

 /// <summary>
        /// 上传Excel
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<IActionResult> Uplaod(List<IFormFile> file)
        {
            MyResponse myResponse = new MyResponse();

            foreach (var formFile in file)
            {
                if (formFile.Length > 0)
                {
                    try
                    {
                        FileInfo fi = new FileInfo(formFile.FileName);
                        string ext = fi.Extension;
                        if (ext.ToLower() == ".xlsx")
                        {
                            #region 上传Excel             
                            var orgFileName = fi.Name;
                            var newFileName = "导入_" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ext;//202312040322028731.xlsx

                            var uploads = Path.Combine(Directory.GetCurrentDirectory(), "TEMP");//上传文件夹
                            var filePath = Path.Combine(uploads, newFileName);
                            using (var stream = new FileStream(filePath, FileMode.Create))
                            {
                                await formFile.CopyToAsync(stream);//上传文件到服务器TEMP文件夹中    
                            }
                            #endregion
                            #region 读取Excel
                            Thread.Sleep(1000);//休息1秒,给excel文件生成并保存的时间
                            bool IsExist = ExcelHelper.FileIsExist(filePath);//判断文件是否已保存
                            if (IsExist)//已保存
                            {
                                DataTable dt = ExcelHelper.GetExcelData(filePath);//读取Excel文件到DataTable
                                if (dt != null && dt.Rows.Count > 0)
                                {
                                    for (int i = 0; i < dt.Rows.Count; i++)
                                    {
                                        if (!string.IsNullOrWhiteSpace(dt.Rows[i]["工程账号"].ToString()))
                                        {
                                            BusinessData bus = new BusinessData();
                                            bus.sort = Tools.ToInt(dt.Rows[i]["序号"]);
                                            bus.kprq = Tools.ToDateTime(dt.Rows[i]["日期"], DateTime.Now).Date;                   bus.gfdz = Tools.ToString(dt.Rows[i]["地址"]);                                      bus.gfdh = Tools.ToString(dt.Rows[i]["电话"]);                                      bus.xmszqx = Tools.ToString(dt.Rows[i]["所在地"]);                                      bus.xmmc = Tools.ToString(dt.Rows[i]["名称"]);                                      bus.remark = Tools.ToString(dt.Rows[i]["备注"]);                                      bus.bz = businessDataBLL.BZ(bus.xmzh, bus.xmszqx, bus.xmmc);                      bus.BusinessData_UUID = Guid.NewGuid().ToString();                  bus.IsDeleted = false;
                                            bus.CreateBy = LOGIN_USER_NAME;
                                            bus.CreateDate = DateTime.Now;
                                            bus.UpdateBy = LOGIN_USER_NAME;
                                            bus.UpdateDate = DateTime.Now;
                                            businessDataBLL.AddSth(bus);//保存
                                            Thread.Sleep(0);//转给空闲线程
                                        }
                                    }
                                    myResponse.success = true;
                                    myResponse.message = "导入成功!";
                                }
                            }
                            else//未保存
                            {
                                myResponse.success = false;
                                myResponse.message = "上传文件出错!文件保存失败!";
                            }
                            #endregion
                        }
                        else
                        {
                            myResponse.success = false;
                            myResponse.message = "上传文件出错!请上传 *.xlsx 格式文件!";
                        }
                    }
                    catch (Exception ex)
                    {
                        myResponse.success = false;
                        myResponse.message = "上传文件出错!" + ex.Message;
                    }
                    myResponse.success = true;
                }
                else
                {
                    myResponse.success = false;
                    myResponse.message = "上传文件出错!";
                }
            }
            return Json(myResponse);
        }