netcore3.1 + vue (前后端分离)Excel导入

 

1.前端(vue)代码

2.公共类ExcelHelper

3.后端(netcore)代码

 

思路：导入类似于上传，将excel上传后将流转换为数据

1.前端(Vue)代码

这里使用的是ElementUI的el-upload上传控件

需要注意的是http-request方法，每次上传文件，该方法都会被调用一次，所以后台处理的时候一次只处理一个文件

<el-col :span="1">
                  <el-upload
                    class="upload-demo"
                    action="#"
                    accept=".xls,.xlsx"
                    multiple
                    :http-request="importVeteranDiretoryExcel"
                    :file-list="fileListIntroduce">
                    <el-button size="small" type="primary">导入</el-button>
                  </el-upload>
</el-col>

data(){
 
    return {
  
         fileListIntroduce: [], //导入列表
   
    }
 
}

methods:{

     //目录导入
      importVeteranDiretoryExcel(params){
        // console.log(params.file);
        let dType = null;
        //判断是第几部分
        if(this.tabVal === 'fristTab') {
          dType = 0;
        } else if(this.tabVal === 'secondTab') {
          dType = 1;
        } else if (this.tabVal === 'thirdlyTab') {
          dType = 2;
        }
        let _this = this;
        let formData = new FormData();
        formData.append("file",params.file);
        formData.append("vId",this.id);
        formData.append("dType",dType);
        importVeteranDiretory(formData).then((res)=>{
          if (res.data.success == true) {
            this.$message({
              type:"success",
              message: res.data.msg
            });
          } else if(res.data.success == false) {
            this.$message({
              type:"warning",
              message: res.data.msg
            });
          }
          this.fileListIntroduce=[];
          if(this.tabVal === 'fristTab') {
            this.onFirstTab();
          } else if(this.tabVal === 'secondTab') {
            this.onSecondTab();
          } else if (this.tabVal === 'thirdlyTab') {
            this.onThirdlyTab();
          }
        });
      }

}

 

2.公共类ExcelHelper

目前只用到了导入，如果后期用到导出会再更新

 

public class ExcelHelper
{
     /// <summary>读取excel      
        /// 默认第一行为表头，导入第一个工作表   
        /// </summary>      
        /// <param name="strFileName">excel文档路径</param>      
        /// <returns></returns>      
        public static DataTable Import(string strFileName)
        {
            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 2); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

}

 

3.后端(netcore)代码

 接口其实是可以接受多文件的

        /// <summary>
        ///  excel导入
        /// </summary>
        /// <param name="formCollection">表单集合值</param>
        /// <returns>服务器存储的文件信息</returns>

        [HttpPost]
        public MessageModel<string> ImportVeteranDiretory(IFormCollection formCollection)
        {
            List<TBase_VeteranDirectory> directorylist = new List<TBase_VeteranDirectory>();
            var vId = Request.Form["vId"];
            var dType = Request.Form["dType"];
            int cnt = 0;
            try
            {
                //FormCollection转化为FormFileCollection
                var files = (FormFileCollection)formCollection.Files;
                //List<TBase_VeteranDirectory> directorylist = new List<TBase_VeteranDirectory>();              
                if (files.Any())
                {
                    PdfLeadingHelper.DelectDir(PathHelper.VeteranDiretoryExcel);
                    foreach (var file in files)
                    {

                        string filePath = string.Empty;
                        //文件保存
                        using (var fs = System.IO.File.Create($"{PathHelper.VeteranDiretoryExcel}\\{file.FileName}"))
                        {
                            file.CopyTo(fs);
                            fs.Flush();                       
                        }

                        DataTable dt = ExcelHelper.Import($"{PathHelper.VeteranDiretoryExcel}\\{file.FileName}");
                        foreach (DataRow dr in dt.Rows)
                        {
                            TBase_VeteranDirectory directory = new TBase_VeteranDirectory();
                            directory.VId = int.Parse(vId);
                            directory.DType = int.Parse(dType);
                            directory.MaterialName = dr[0].ToString();

                            if (dr[1].ToString() == "")
                                directory.MaterialYear = null;
                            else
                                directory.MaterialYear = int.Parse(dr[1].ToString());

                            if (dr[2].ToString() == "")
                                directory.MaterialMonth = null;
                            else
                                directory.MaterialMonth = int.Parse(dr[2].ToString());

                            if (dr[3].ToString() == "")
                                directory.MaterialDay = null;
                            else
                                directory.MaterialDay = int.Parse(dr[3].ToString());

                            //directory.MaterialYear = dr[1].ToString() == "" ? (null) : int.Parse(dr[1].ToString());
                            //directory.MaterialMonth = dr[2].ToString() == "" ? 0 : int.Parse(dr[2].ToString());
                            //directory.MaterialDay = dr[3].ToString() == "" ? 0 : int.Parse(dr[3].ToString());
                            directory.Page = dr[4].ToString() == "" ? 0 : int.Parse(dr[4].ToString());
                            directory.Collator = dr[5].ToString() ?? "";
                            directory.CreateBy = _user.Name;
                            directory.CreateId = _user.ID;
                            if (!string.IsNullOrEmpty(directory.MaterialName))
                            {
                                directorylist.Add(directory);
                            }                      
                        }
                        cnt =  _tVeteranDiretoryService.Add(directorylist).Result;
                    }
                }
                else
                {
                    return new MessageModel<string>()
                    {
                        msg = "导入失败，未检测上传的文件信息",
                        success = cnt > 0,
                        response = cnt.ObjToString()
                    };
                }
            }
            catch (Exception ex)
            {
                log.Error($"{ex.ToString()}");
                return new MessageModel<string>()
                {
                    msg = "导入失败，请检查文件内容" + ex.Message,
                    success = cnt > 0,
                    response = cnt.ObjToString()
                };
            }

            if (directorylist.Any())
            {
                //_tImgDetailInfoService.Add(insertPicData);
                return new MessageModel<string>()
                {
                    msg = "导入成功，成功导入" + cnt + "条信息",
                    success = cnt > 0,
                    response = cnt.ObjToString()
                };
            }
            else
            {              
                return new MessageModel<string>()
                {
                    msg = "网络打瞌睡了，导入失败",
                    success = cnt > 0,
                    response = cnt.ObjToString()
                };
            }
        }