poi实现导出和导入excel

poi实现导出和导入excel

引入

需求

最近工作上遇到一个功能需求，需要将页面上的数据模板导出为一个excel，和将单个excel导入，页面进行展示。导入有不符合规则的数据时，提供一个错误导出，错误地方用红框标注。

分析

模板导出十分简单，显示的字段是固定的，模块名称和进程名称，但是导入需要进行判断，判断规则有三种，不符合其中任意一种即标红。一、模块名称或者进程名称对应的字段值没填；二、导入的进程名称的字段值之间存在重复，重复的第二个标红；三、导入的进程名称与数据库存在的进程名称存在重复，重复的标红。二三点即确保进程名称唯一。根据分析导入excel的单行数据可以存为一个List<Map<String, Object>>形式，Map的key为模块名称或进程名称，value为其对应的值，如果存在错误的数据，增加一个Map，Map的key为RedKey(自定义)，value为模块名称或进程名称，导入完成后若有错误的数据，将错误的数据存入redis，确保redis的key为唯一性，并返回key。再提供一个错误导出的接口用于取出本次导入存在错误的数据并导出为一个excel。

项目搭建

创建数据库和表

-- 创建数据库和表
CREATE DATABASE IF NOT EXISTS poi
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;

-- 创建表
DROP TABLE IF EXISTS `rules`;
CREATE TABLE `rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '规则id (pk)',
  `business_module_name` varchar(255) NOT NULL COMMENT '模块名称',
  `process_name` varchar(255) NOT NULL COMMENT '进程名称',
  `match_result` int(11) NULL DEFAULT 0 COMMENT '匹配结果',
  PRIMARY KEY (`id`) USING BTREE
) 

导入依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
        <version>2.0.4.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <version>2.0.4.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <version>2.0.4.RELEASE</version>
        <scope>test</scope>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.11</version>
    </dependency>
    
    <!--mybatis-plus-->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.0.5</version>
    </dependency>

    <!--lombok-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>

    <!-- poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.0.1</version>
    </dependency>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.58</version>
        <scope>compile</scope>
    </dependency>
    
    <!--redis-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-redis</artifactId>
    </dependency>
<dependencies>

application.yml配置

spring:
  datasource:
    url: jdbc:mysql://192.168.0.207:3306/poi?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
    username: root
    password: root
    driver-class-name: com.mysql.jdbc.Driver
server:
  port: 8888
logging:
  level:
    root: info
    com.fastech.fault: info
    com.fastech.framework.mqtt.service: info
    org.apache.hadoop.util.Shell: OFF
    org.mongodb.driver.*: OFF
    org.apache.zookeeper.ZooKeeper: OFF
  
# 配置*mapper.xml的位置
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml

实体类Rules

package com.fastech.fault.model;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import java.io.Serializable;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="Rules对象", description="")
public class Rules implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "规则id (pk)")
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @ApiModelProperty(value = "业务模块名称")
    private String businessModuleName;

    @ApiModelProperty(value = "进程名称")
    private String processName;

    @ApiModelProperty(value = "匹配结果")
    private Integer matchResult;

    @TableField(exist = false)
    private String matchResultValue;

}

返回的数据封装实体类

package com.fastech.fault.model;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class DBResult {

    private int result;

    private String info;

    private JSONArray jsonArray;

    private int count;

    private JSONObject jsonObject;

    public int getResult() {
        return result;
    }

    public void setResult(int result) {
        this.result = result;
    }

    public String getInfo() {
        return info;
    }

    public void setInfo(String info) {
        this.info = info;
    }

    public JSONArray getJsonArray() {
        return jsonArray;
    }

    public void setJsonArray(JSONArray jsonArray) {
        this.jsonArray = jsonArray;
    }

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }

    public JSONObject getJsonObject() {
        return jsonObject;
    }

    public void setJsonObject(JSONObject jsonObject) {
        this.jsonObject = jsonObject;
    }
}

package com.fastech.fault.utils;

public enum ResponseStatus {

    /**
     *  请求成功状态码
     */
    OK("200","请求成功"),

    /**
     * 请求失败
     */
    FAIL_400("400","请求失败"),

    /**
     *  未认证
     */
    FAIL_401("401","未认证"),

    /**
     * 无权限
     */
    FAIL_403("403","无权限"),
   
    /**
     * 错误
     */
    FAIL_500("500","系统内部错误"),

    /**
     * 无cookie
     **/
    FAIL_799("799","无cookie");


    private final String value;

    private final String reasonPhrase;

    ResponseStatus(String value, String reasonPhrase) {
        this.value = value;
        this.reasonPhrase = reasonPhrase;
    }

    public String getValue() {
        return value;
    }

    public String getReasonPhrase() {
        return reasonPhrase;
    }
}

package com.fastech.fault.utils;

import io.swagger.annotations.ApiModelProperty;

public class ResponseWrapper<T> {

    @ApiModelProperty("状态码")
    private String status = ResponseStatus.OK.getValue();

    @ApiModelProperty("信息")
    private String msg = "success";

    @ApiModelProperty("接口返回数据")
    private T result;

    public ResponseWrapper(ResponseStatus status, String msg) {
        this.status = status.getValue();
        this.msg = msg;
    }

    public ResponseWrapper(ResponseStatus status, T result) {
        this.status = status.getValue();
        this.result = result;
    }

    public ResponseWrapper(ResponseStatus status, String msg, T result) {
        this.status = status.getValue();
        this.msg = msg;
        this.result = result;
    }

    public ResponseWrapper(T result) {
        this.result = result;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(ResponseStatus status) {
        this.status = status.getValue();
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public T getResult() {
        return result;
    }

    public void setResult(T result) {
        this.result = result;
    }
}

常量类

package com.fastech.fault.constant;

import java.util.Arrays;
import java.util.List;

/**
 * @ClassName: RelevancyTemplateConstant
 * @Description:
 * @Author: keke
 * @Date: 2021/6/24
 */
public class FaultTemplateConstant {
    /**
     * 关联进程
     */
    public static final List<String> PROCESS_CONTEXT_LIST = Arrays.asList("模块名称","进程名称");

    /**
     * 模块名称
     **/
    public static final String PROCESS_CONTEXT_MODULE = "模块名称";

    /**
     * 进程名称
     **/
    public static final String PROCESS_CONTEXT_PROCESS = "进程名称";

    /**
     * 导入错误存入redis的key
     **/
    public static final String ERROR_DATA = "error_data";

}

持久层

package com.fastech.fault.mapper;

import com.fastech.fault.model.Rules;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface RulesMapper extends BaseMapper<Rules> {
}

业务层

package com.fastech.fault.service;

import com.alibaba.fastjson.JSONObject;
import com.fastech.fault.model.DBResult;
import com.fastech.fault.model.Rules;
import com.baomidou.mybatisplus.extension.service.IService;
import com.fastech.fault.utils.PageResult;

public interface RulesService extends IService<Rules> {
}

模板导出和错误导出

业务层

package com.fastech.fault.service;

import com.fastech.fault.model.DBResult;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * @ClassName: exportService
 * @Description:
 * @Author: keke
 * @Date: 2021/6/24
 */
public interface ExportService {

    /**
     * @Author keke
     * @Description 模板导出
     * @Date 2021/6/24
     * @Param [response]
     * @Return com.fastech.fault.model.DBResult
     **/
    DBResult exportFaultTemplate(HttpServletResponse response);

    /**
     * @Author keke
     * @Description 错误导出
     * @Date 2021/6/25
     * @Param [excelId, response]
     * @Return com.fastech.fault.model.DBResult
     **/
    DBResult getFaultErrorExcel(String excelId, HttpServletResponse response);

}

业务层实现类

package com.fastech.fault.service.impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.fastech.fault.constant.FaultTemplateConstant;
import com.fastech.fault.model.DBResult;
import com.fastech.fault.service.ExportService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;

/**
 * @ClassName: ExportServiceImpl
 * @Description:
 * @Author: keke
 * @Date: 2021/6/24
 */
@Service
public class ExportServiceImpl implements ExportService {

    private static final Logger logger = LoggerFactory.getLogger(ExportServiceImpl.class);

    @Autowired
    private RedisTemplate<String, String> redisTemplate;

    @Override
    public DBResult exportFaultTemplate(HttpServletResponse response) {
        DBResult result = new DBResult();
        result.setJsonArray(new JSONArray());
        OutputStream outputStream = null;
        try {
            XSSFWorkbook wb = getFaultTemplateWorkBook();
            outputStream = response.getOutputStream();
            response.reset(); // 非常重要
            response.setHeader("result", "1");
            response.setContentType("application/x-msdownload;charset=GBK");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=FaultTemplate.xlsx");
            wb.write(outputStream);
            outputStream.flush();
            result.setResult(1);
            result.setInfo("导出成功");
        } catch (Exception e) {
            logger.error("导出失败");
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    @Override
    public DBResult getFaultErrorExcel(String excelId, HttpServletResponse response) {
        DBResult dbResult = new DBResult();
        JSONArray errorList = JSONArray.parseArray(redisTemplate.opsForValue().get(excelId));
        if (errorList == null || errorList.size() == 0) {
            dbResult.setInfo("错误模板不存在");
            dbResult.setResult(0);
            return dbResult;
        }
        OutputStream outputStream = null;
        try {
            XSSFWorkbook wb = getFaultTemplateWorkBook();
            for (int i = 0; i < errorList.size(); i++) {
                JSONObject jsonObject = errorList.getJSONObject(i);
                XSSFRow row = wb.getSheet("FaultTemplate").createRow(1 + i);
                for (int j = 0; j < FaultTemplateConstant.PROCESS_CONTEXT_LIST.size(); j++) {
                    String name = FaultTemplateConstant.PROCESS_CONTEXT_LIST.get(j);
                    CellStyle style = wb.createCellStyle();
                    //标红操作
                    if (jsonObject.containsKey(jsonObject.getString("RedKey")) && name.equals(jsonObject.getString("RedKey"))) {
                        style.setBorderBottom(BorderStyle.THIN);
                        style.setBottomBorderColor(IndexedColors.RED.getIndex());
                        style.setBorderLeft(BorderStyle.THIN);//左边框
                        style.setLeftBorderColor(IndexedColors.RED.getIndex());
                        style.setBorderTop(BorderStyle.THIN); //上边框
                        style.setRightBorderColor(IndexedColors.RED.getIndex());
                        style.setBorderRight(BorderStyle.THIN);//右边框
                        style.setTopBorderColor(IndexedColors.RED.getIndex());
                    }
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(style);
                    cell.setCellValue(jsonObject.getString(name));
                }
            }
            outputStream = response.getOutputStream();
            response.reset(); // 非常重要
            response.setHeader("result", "1");
            response.setContentType("application/x-msdownload;charset=GBK");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=errorFaultTemplate.xlsx");
            wb.write(outputStream);
            outputStream.flush();
            dbResult.setResult(1);
            dbResult.setInfo("导出成功");
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("导出失败");
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return dbResult;
    }

    private XSSFWorkbook getFaultTemplateWorkBook(){
        XSSFWorkbook wb = new XSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        XSSFSheet sheet = wb.createSheet("FaultTemplate");
        //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        XSSFRow rowFirst = sheet.createRow(0);
        XSSFCellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setItalic(true);
        style.setFont(font);
        for(int i = 0; i< FaultTemplateConstant.PROCESS_CONTEXT_LIST.size(); i++){
            String titleCN = FaultTemplateConstant.PROCESS_CONTEXT_LIST.get(i);
            XSSFCell cellFirst = rowFirst.createCell(i);
            cellFirst.setCellValue(titleCN);
        }
        return wb;
    }

}

表现层

package com.fastech.fault.controller;


import com.alibaba.fastjson.JSONObject;
import com.fastech.fault.model.DBResult;
import com.fastech.fault.service.ExportService;
import com.fastech.fault.service.ImportService;
import com.fastech.fault.service.RulesService;
import com.fastech.fault.utils.PageResult;
import com.fastech.fault.utils.ResponseStatus;
import com.fastech.fault.utils.ResponseWrapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.Map;


/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author keke
 * @since 2021-06-24
 */
@RestController
@RequestMapping("/fault/rules")
public class RulesController {

    private final static Logger logger = LoggerFactory.getLogger(RulesController.class);

    @Autowired
    private ExportService exportService;
    @Autowired
    private ImportService importService;

    /**
     * @Author keke
     * @Description 模板导出
     * @Date 2021/6/25
     * @Param [response]
     * @Return java.lang.String
     **/
    @RequestMapping("/exportFaultTemplate")
    public ResponseWrapper<String> exportFaultTemplate(HttpServletResponse response) {
        try {
            DBResult dbResult = exportService.exportFaultTemplate(response);
            return new ResponseWrapper<>(ResponseStatus.OK, dbResult.getInfo());
        } catch (Exception e) {
            logger.error(JSONObject.toJSONString(e));
            return new ResponseWrapper<>(ResponseStatus.FAIL_400, e.getMessage());
        }
    }

    /**
     * @Author keke
     * @Description 导入
     * @Date 2021/6/25
     * @Param [enterpriseCode, serverCode, file, session]
     * @Return com.fastech.fault.utils.ResponseWrapper<java.util.Map<java.lang.String,java.lang.Object>>
     **/
    @RequestMapping("/importFaultTemplate")
    public ResponseWrapper<Map<String, Object>> importFaultTemplate(@RequestParam(value = "file", required = false) MultipartFile file) {
        Map<String, Object> resultMap = importService.importFaultTemplate(file);
        if (!resultMap.containsKey("errorMsg") && !resultMap.containsKey("excelId")) {
            resultMap.put("errorMsg", "全部导入成功,导入结束");
            return new ResponseWrapper<>(resultMap);
        }
        if (resultMap.get("excelId") == null) {
            return new ResponseWrapper<>(ResponseStatus.FAIL_500, resultMap);
        }
        return new ResponseWrapper<>(ResponseStatus.FAIL_400, resultMap);
    }

    /**
     * @Author keke
     * @Description 错误导出
     * @Date 2021/6/25
     * @Param [excelId, response]
     * @Return java.lang.String
     **/
    @GetMapping("/getFaultErrorExcel")
    public String getFaultErrorExcel(@RequestParam(required = false) String excelId,
                                     HttpServletResponse response) {
        JSONObject result = new JSONObject();
        try {
            DBResult dbResult = exportService.getFaultErrorExcel(excelId, response);
            result.put("result", dbResult.getResult());
            result.put("info", dbResult.getInfo());
        } catch (Exception e) {
            result.put("info", e.getMessage());
            logger.error(JSONObject.toJSONString(e));
        }
        return JSONObject.toJSONString(result);
    }

}

导入

业务层

package com.fastech.fault.service;

import org.springframework.web.multipart.MultipartFile;

import java.util.Map;

/**
 * @ClassName: ImportService
 * @Description:
 * @Author: keke
 * @Date: 2021/6/24
 */
public interface ImportService {

    /**
     * @Author keke
     * @Description 导入
     * @Date 2021/6/25
     * @Param [file]
     * @Return java.util.Map<java.lang.String,java.lang.Object>
     **/
    Map<String, Object> importFaultTemplate(MultipartFile file);

}

业务层实现类

package com.fastech.fault.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.fastech.fault.constant.FaultTemplateConstant;
import com.fastech.fault.mapper.RulesMapper;
import com.fastech.fault.model.Rules;
import com.fastech.fault.service.ImportService;
import com.fastech.fault.service.TimerService;
import com.fastech.fault.utils.RSACryptographyUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.TimeUnit;

/**
 * @ClassName: ImportServiceImpl
 * @Description:
 * @Author: keke
 * @Date: 2021/6/24
 */
@Service
public class ImportServiceImpl implements ImportService {

    @Autowired
    private RulesMapper rulesMapper;

    @Autowired
    private RedisTemplate<String, String> redisTemplate;

    private static final Logger logger = LoggerFactory.getLogger(ImportServiceImpl.class);

    /**
     * @Author keke
     * @Description 导入关联模板主方法
     * @Date 2021/6/25
     * @Param [file, username]
     * @Return java.util.Map<java.lang.String,java.lang.Object>
     **/
    @Override
    public Map<String, Object> importFaultTemplate(MultipartFile file) {
        Map<String, Object> resultMap = new HashMap<>();
        StringBuilder allError = new StringBuilder();
        try {
            resultMap = readFile(file);
        } catch (Exception e) {
            allError.append("导入关联模板出现异常");
            resultMap.put("errorMsg", allError);
            resultMap.put("excelId", null);
            logger.error(allError.toString());
            return resultMap;
        }
        return resultMap;
    }


    /**
     * @Author keke
     * @Description 读取关联模板
     * @Date 2021/6/25
     * @Param [file]
     * @Return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     **/
    private Map<String, Object> readFile(MultipartFile file) {
        //用于存放正确的记录
        List<Map<String, Object>> listMap = new ArrayList<>();
        Map<String, Object> map = new HashMap<>();
        //创建Excel文件
        Workbook workBook = null;
        //表的总行数
        int lastRowNum;
        //表的总列数
        int lastCellNum;

        try {
            workBook = new XSSFWorkbook(file.getInputStream());
            Sheet sheet = workBook.getSheetAt(0);
            //获取表的总行数
            lastRowNum = sheet.getLastRowNum();
            //获取表的总列数
            Row headRow = sheet.getRow(0);
            lastCellNum = headRow.getLastCellNum();

            //存储数据到List<Map<String, Object>>集合中
            for (int i = 1; i <= lastRowNum; i++) {
                Map<String, Object> trueDataMap = new LinkedHashMap<>();

                for (int j = 0; j < lastCellNum; j++) {
                    Row row = sheet.getRow(i);
                    Object value = "";
                    if (row.getCell(j) != null) {
                        if (row.getCell(j).getCellType() == CellType.NUMERIC) {
                            value = BigDecimal.valueOf(row.getCell(j).getNumericCellValue());
                        }
                        if (row.getCell(j).getCellType() == CellType.STRING || row.getCell(j).getCellType() == CellType.BLANK) {
                            value = nullToString(row.getCell(j).getStringCellValue());
                        }
                    }
                    trueDataMap.put(headRow.getCell(j).getStringCellValue(), value);
                }
                listMap.add(trueDataMap);
            }

            //查询数据库中规则表所有的记录
            List<Rules> rules = rulesMapper.selectList(null);

            //拿到不符合的数据
            //进程名为空或者进程名重复（包括与数据库已有的进程名重复）
            List<Map<String, Object>> errorListMap = filterMapError(listMap, rules);
            //将不符合的数据存入redis
            String excelId = null;
            try {
                excelId = RSACryptographyUtil.encode(FaultTemplateConstant.ERROR_DATA + System.currentTimeMillis());
            } catch (Exception e) {
                e.printStackTrace();
            }
            if (excelId != null) {
                JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(errorListMap));
                redisTemplate.opsForValue().set(excelId, String.valueOf(jsonArray), 30, TimeUnit.MINUTES);
            }
            map.put("excelId", excelId);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workBook != null) {
                try {
                    workBook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return map;
    }

    /**
     * @Author keke
     * @Description 过滤到进程名称重复、数据库存在、进程名称为空的数据
     * @Date 2021/6/25
     * @Param [listMap]
     * @Return boolean
     **/
    private List<Map<String, Object>> filterMapError(List<Map<String, Object>> listMap, List<Rules> rulesList) {
        //过滤掉重复的数据
        List<Map<String, Object>> errorListMap = new ArrayList<>();
        for (int i = 0; i < listMap.size(); i++) {
            if (checkIsEmpty(listMap.get(i)) || checkDataBaseRepeat(listMap.get(i), rulesList) || checkProcessRepeat(listMap, listMap.get(i), i)) {
                errorListMap.add(listMap.get(i));
            }
        }
        //移除不符合的数据
        listMap.removeAll(errorListMap);
        //符合的数据入库
        for (Map<String, Object> map : listMap) {
            Rules rules = new Rules();
            rules.setBusinessModuleName(map.get(FaultTemplateConstant.PROCESS_CONTEXT_MODULE).toString());
            rules.setProcessName(map.get(FaultTemplateConstant.PROCESS_CONTEXT_PROCESS).toString());
            rulesMapper.insert(rules);
        }
        return errorListMap;
    }

    /**
     * @Author keke
     * @Description 判断导入的数据是否存在重复
     * @Date 2021/6/25
     * @Param [listMap, map]
     * @Return boolean
     **/
    private boolean checkProcessRepeat(List<Map<String, Object>> listMap, Map<String, Object> map, Integer i) {
        for (int j = i + 1; j < listMap.size(); j++) {
            if (listMap.get(j).get(FaultTemplateConstant.PROCESS_CONTEXT_PROCESS).toString().equals(map.get(FaultTemplateConstant.PROCESS_CONTEXT_PROCESS).toString())) {
                map.put("RedKey", FaultTemplateConstant.PROCESS_CONTEXT_PROCESS);
                return true;
            }
        }
        return false;
    }

    /**
     * @Author keke
     * @Description 判断数据库是否存在导入的数据
     * @Date 2021/6/25
     * @Param [listMap, rulesList]
     * @Return boolean
     **/
    private boolean checkDataBaseRepeat(Map<String, Object> map, List<Rules> rulesList) {
        for (Rules rule : rulesList) {
            if (rule.getProcessName().equals(map.get(FaultTemplateConstant.PROCESS_CONTEXT_PROCESS))) {
                map.put("RedKey", FaultTemplateConstant.PROCESS_CONTEXT_PROCESS);
                return true;
            }
        }
        return false;
    }

    /**
     * @Author keke
     * @Description 判断进程名称是否为空
     * @Date 2021/6/25
     * @Param [map]
     * @Return boolean
     **/
    private boolean checkIsEmpty(Map<String, Object> map) {
        for (String key : map.keySet()) {
            if ("".equals(map.get(key))) {
                map.put("RedKey", key);
                return true;
            }
        }
        return false;
    }

    /**
     * @Author keke
     * @Description 空值""转换
     * @Date 2021/6/29
     * @Param [s]
     * @Return java.lang.String
     **/
    private String nullToString(String s) {
        if (s == null || s.isEmpty()) {
            return "";
        }
        return s;
    }

}

表现层

package com.fastech.fault.controller;


import com.alibaba.fastjson.JSONObject;
import com.fastech.fault.model.DBResult;
import com.fastech.fault.service.ExportService;
import com.fastech.fault.service.ImportService;
import com.fastech.fault.service.RulesService;
import com.fastech.fault.utils.PageResult;
import com.fastech.fault.utils.ResponseStatus;
import com.fastech.fault.utils.ResponseWrapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.Map;


/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author keke
 * @since 2021-06-24
 */
@RestController
@RequestMapping("/fault/rules")
public class RulesController {

    private final static Logger logger = LoggerFactory.getLogger(RulesController.class);

    @Autowired
    private ImportService importService;

    /**
     * @Author keke
     * @Description 导入
     * @Date 2021/6/25
     * @Param [enterpriseCode, serverCode, file, session]
     * @Return com.fastech.fault.utils.ResponseWrapper<java.util.Map<java.lang.String,java.lang.Object>>
     **/
    @RequestMapping("/importFaultTemplate")
    public ResponseWrapper<Map<String, Object>> importFaultTemplate(@RequestParam(value = "file", required = false) MultipartFile file) {
        Map<String, Object> resultMap = importService.importFaultTemplate(file);
        if (!resultMap.containsKey("errorMsg") && !resultMap.containsKey("excelId")) {
            resultMap.put("errorMsg", "全部导入成功,导入结束");
            return new ResponseWrapper<>(resultMap);
        }
        if (resultMap.get("excelId") == null) {
            return new ResponseWrapper<>(ResponseStatus.FAIL_500, resultMap);
        }
        return new ResponseWrapper<>(ResponseStatus.FAIL_400, resultMap);
    }

}

测试

模板导出测试

导入测试

数据库数据

测试数据

测试流程及结果

错误导出测试