vue2 数据导入excel

1、安装 npm install xlsx

  一、前端

 <el-upload
     style="display: inline-block"
     action
     accept=".xlsx, .xls"
     :auto-upload="false"
      :show-file-list="false"
      :on-change="handleUpload"
  >
      <el-button type="primary" icon="el-icon-upload2" round>导入</el-button>
 </el-upload>

  二、逻辑

 

npm install xlsx
<script>
  /* eslint-disable */
  import * as XLSX from "xlsx";
    // 导入
    handleUpload(ev){
      let yearMonth =this.selectForm.yearMonth
      // 如果有数据则给出提示
      if(this.tableCount >0){
        this.$confirm(yearMonth+'有数据，是否进行替换？','提示',{
          confirmButtonText: '确定',
          cancelButtonText: '取消',
          type:'warning'
        }).then(() =>{
          const file = ev.raw
          const fileReader = new FileReader();
          fileReader.readAsArrayBuffer(file);
          fileReader.onload = (ev) =>{
            const data = new Uint8Array(ev.target.result);
            const workbook = XLSX.read(data, { type: 'array' });
            const firstSheetName = workbook.SheetNames[0];
            const worksheet = workbook.Sheets[firstSheetName];
            // 转译范围
            const range = { s: { r: 2, c: 3 }, e: { r: 32, c: 18 } }
            this.importData =(XLSX.utils.sheet_to_json(worksheet,{ header: 1,defval:'',range: range}));
            let formData={
              list: JSON.parse(JSON.stringify(this.importData)),
              yearMonth: this.selectForm.yearMonth
            }
            importData(formData).then((res) =>{
              if(res.code == 200){
                this.getDataList()
                this.$publicmethod.showMessage("导入成功",this.$publicmethod.SuccessType)
              }else{
                this.$publicmethod.showMessage("导入失败-选择的模版不正确！",this.$publicmethod.ErrorType)
              }
            })
          }
        })
      }else{
        const file = ev.raw
        const fileReader = new FileReader();
        fileReader.readAsArrayBuffer(file);
        fileReader.onload = (ev) =>{
          const data = new Uint8Array(ev.target.result);
          const workbook = XLSX.read(data, { type: 'array' });
          const firstSheetName = workbook.SheetNames[0];
          const worksheet = workbook.Sheets[firstSheetName];
          // 转译范围
          const range = { s: { r: 2, c: 3 }, e: { r: 32, c: 18 } }
          this.importData =(XLSX.utils.sheet_to_json(worksheet,{ header: 1,defval:'',range: range}));
          let formData={
            list: JSON.parse(JSON.stringify(this.importData)),
            yearMonth: this.selectForm.yearMonth
          }
          importData(formData).then((res) =>{
            if(res.code == 200){
              this.getDataList()
              this.$publicmethod.showMessage("导入成功",this.$publicmethod.SuccessType)
            }else{
              this.$publicmethod.showMessage("导入失败-选择的模版不正确！",this.$publicmethod.ErrorType)
            }
          })
        }
      }
    },
</script>

　　三、接口

export function importData(data) {
  return request({
    url: 'api/operation_report/importData',
    method: 'post',
    data
  })  
}

　　四、后端实现

  

  1、控制器类
   @ApiOperation(value = "导入数据")
    @PostMapping(value = "/importData")
    public JsonBean importData(  @RequestBody @Validated ReportQueryDo queryDo) {
        List<String> list =queryDo.getList();
        String yearMonth = queryDo.getYearMonth();
        return operationReportService.importData(list,yearMonth);
    }
  2、实现类
      // 导入
    @Override
    @Transactional(value = "MainTransactionManager", rollbackFor = Exception.class)
    public JsonBean importData(List<String> list,String  yearMonth) {
        try{
            List<OperationReport> addList = new ArrayList<>();
            // 将 excel数据保存在数据库中,传过来的是字符串，将字符串转成对应的对象进行数据的添加
            list.stream().forEach(data ->{
                OperationReport report = new OperationReport();
                report.setId(SnowIdUtil.getId());
                report.setYearMonth(yearMonth);
                JSONArray arrayData =JSON.parseArray(data);
                // 将数据转换成list集合进行操作
                if(arrayData !=null){
                    // 公司名称
                    String orgName = arrayData.getString(0);
                    int orgId=getOrgId(orgName);
                    // -1 代表没有当前组织
                    if(orgId != -1){
                        report.setOrgId(orgId);
                    }else{
                        String msg="不存在"+orgName+"公司";
                        logger.error(msg);
                        throw new RuntimeException(msg);
                    }
                    report = changeDataEntity(report,arrayData);
                    addList.add(report);
                }
            });
            // 数据批量添加
            if(CollectionUtils.isNotEmpty(addList)){
                // 先删除数据再进行添加
                Map<String,Object> map = new HashMap<>();
                map.put("yearMonth",yearMonth);
                operationReportMapper.delete(map);
                operationReportMapper.batchInsert(addList);
            }
            return new JsonBean(ResultCode.SERVICE_OK);
        }catch (Exception e){
            logger.error(e.toString());
            return new JsonBean(ResultCode.SERVICE_ERR);
        }
    }

   // 将excel表格数据转成实体
    private OperationReport changeDataEntity(OperationReport report,JSONArray arrayData){
        // 人员总数
        String personnelTotalCount = arrayData.getString(1);
        if(StringUtils.isNotEmpty(personnelTotalCount)){
            report.setPersonnelTotalCount(Integer.parseInt(personnelTotalCount));
        }else{
            report.setPersonnelTotalCount(0);
        }
        // 机关人员总数
        String officialCount = arrayData.getString(2);
        if(StringUtils.isNotEmpty(officialCount)){
            report.setOfficialCount(Integer.parseInt(officialCount));
        }else{
            report.setOfficialCount(0);
        }
        //  一线业务人员
        String frontLineBusinessCount = arrayData.getString(3);
        if(StringUtils.isNotEmpty(frontLineBusinessCount)){
            report.setFrontLineBusinessCount(Integer.parseInt(frontLineBusinessCount));
        }else{
            report.setFrontLineBusinessCount(0);
        }
        //  车辆总数
        String vehicleCount =  arrayData.getString(4);
        if(StringUtils.isNotEmpty(vehicleCount)){
            report.setVehicleCount(Integer.parseInt(vehicleCount));
        }else{
            report.setVehicleCount(0);
        }
        .....
        return report;
    }
  3、根据公司名称获取对应的公司编号
  public class ConstantEnum {
    // 创建HashMap来存储城市公司名称和编号的映射
    private static HashMap<String,Integer> orgMap = new HashMap<>();
    // 静态代码块，初始化公司编号数据
    static{
        orgMap.put("A",00001);
        orgMap.put("B",00002);

    }
    // 根据公司名称获取公司编号的方法
    public static int getOrgId(String orgName){
        return orgMap.containsKey(orgName)?orgMap.get(orgName):-1;
    }
 }
4、批量添加xml
    <!--批量添加 -->
    <insert id="batchInsert"  parameterType="java.util.List">
        insert into operation_report (id,org_id,yearMonth,personnel_total_count) values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id},#{item.orgId},#{item.yearMonth},#{item.personnelTotalCount})
        </foreach>
    </insert>
 

　　五、导入的文件模版（行、列数据，只是转译内容，前面的数据不需要）