SpringBoot基于Hutool实现Excel批量导入及错误数据高亮提醒与导出

1.功能需求

将用户信息通过Excel模板进行批量导入，且将其中不满足导入条件的单元格进行标黄，并注明失败原因

2.方案设计

请求传入一个Excel文件，到达后端后：

1. 创建一个**ExcelWriter**用于将无法导入的行数据写入到一个新的Excel文件
2. 首先通过Hutool的Excel工具**ExcelUtil**对其进行逐行提取，并对数据执行自定义校验逻辑
3. 如果数据校验不通过，则通过**ExcelWriter**写入到新的Excel文件中，并将单元格标黄，并记录失败原因
4. 传入的Excel中的数据逐行分析完毕后，统一进行数据导入，并生成一个uuid作为本次请求的**requestId**，将本次导入结果和requestId返回
5. 若存在数据未成功导入，可通过**requestId**调接口获取生成的记录了错误数据的Excel文件，方便修改后重新导入

3.代码实现

3.1 引入依赖

<!-- hutool -->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.22</version>
</dependency>

<!-- poi表格管理工具 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

3.2 代码实现

枚举类

/**
 * 用户导入失败原因枚举
 */
public enum EnumFailResult {

    PARAM_ILLEGAL(0, "参数不合法"),
    USER_DUPLICATE(1, "用户重复导入"),,
    PHONE_DUPLICATE(2, "此手机号已绑定用户"),
    EMAIL_DUPLICATE(3, "此邮箱已绑定用户");

    private Integer code;
    private String msg;

    EnumFailResult(Integer code, String msg) {
        this.code = code;
        this.msg = msg;
    }

    public Integer getCode() {
        return code;
    }

    public String getMsg() {
        return msg;
    }
}

/**
 * Excel文件列号枚举
 */
public enum EnumUserColumnName {
    
    EXTERNAL_ID(0, "外部id"),
    NAME(1, "姓名"),
    ACCOUNT(2, "账号"),
    PHONE(3, "手机号"),
    EMAIL(4, "电子邮箱"),
    REMARK(5, "备注");
    
    private Integer index;
    private String name;

    EnumUserColumnName(Integer index, String name) {
        this.index = index;
        this.name = name;
    }

    public Integer getCode() {
        return index;
    }

    public String getMsg() {
        return name;
    }
}

Service代码

此功能实现起来代码逻辑比较简单，都已经通过注释进行说明，
麻烦的是对**行数据的校验**（这部分需要结合业务自行实现）和**错误数据的写出**（这部分代码中已实现，如有其他要求微调即可）

注意：
1.行和列序号从 0 开始计
2.ExcelUtil.readBySax方法用于逐行获取行数据，获取到的行数据格式为List，rowList.get(列号)获取指定列的数据(Object格式)，注意自行转换数据格式

/**
 * 导入用户列表
 * @param file 上传的文件
 * @return 本地导入返回对象(成员：requestId、导入成功行数、出错行数) 
 */
@Transactional
public LocalImportVO importUserList(MultipartFile file) {
	try(InputStream inputStream = file.getInputStream()){
	
	    // 创建ExcelWriter
	    ExcelWriter writer = ExcelUtil.getWriter(true);
	    List<String> header = new ArrayList<>(Arrays.asList(ExcelHeaderAliasConfig.user_header.split(",")));
	    writer.writeHeadRow(header);
	
	    // 可导入用户列表
	    List<UserPO> userList = new ArrayList<>();
	
	    // 逐行分析表格数据，验证是否满足用户导入条件
	    ExcelUtil.readBySax(inputStream, 0, (sheetIndex, rowIndex, rowList) -> {
	        // 通过表头列数来判断是否是按给定的excel模板导入的
	        if(rowIndex == 0 && rowList.size() != 9 && rowList.size() != 10) {
	            log.error("Failure to fill in data according to Excel template");
	            throw new BusinessException(ErrorCodeConst.EXCEL_CONTENT_INCORRECT);
	        }
	        if (rowIndex > 0) {
	            // 验证是否满足导入条件(格式+数据库查重)
	            UserPO user = validUserData(rowList, userList, writer);
	            if(user != null){
	                // 为对象设置公共参数
	                user.setId(IdGenerator.ins().generator());
	                userList.add(user);
	            }
	        }
	    });
	
	    // 批量插入用户
	    userService.saveBatch(userList);
	
	    // 无法导入的用户数量
	    int errorRowCount = writer.getPhysicalRowCount() - 1;
	    // 成功导入的用户数量
	    int successRows = userList.size();
	
	    // 将无法导入的用户列表写入到错误文件中
	    if(errorRowCount > 0){
	        String requestId = UUID.randomUUID().toString().replace("-", "");
	        String fileName = FileReadUtil.pathConcat(tempPath, requestId.concat(XLSX));
	
	        FileOutputStream outputStream = new FileOutputStream(fileName);
	        writer.flush(outputStream, true);
	        writer.close();
	        outputStream.close();
	        return new IdpLocalImportVO(successRows, errorRowCount, requestId);
	    }
	    return null;
	
	}catch (IOException e){
	    log.error("Read excel file error", e);
	    throw new BusinessException(ErrorCodeConst.READ_WRITE_FILE_ERROR);
	}
}


/**
 * 验证用户导入行数据
 * @param rowList 行数据
 * @param writer excel写入者
 * @return 参数校验失败则返回null， 校验成功则返回 user对象
 */
private UserPO validUserData(List<Object> rowList, List<UserPO> userList, ExcelWriter writer){
    // 错误描述
    StringBuilder errorDesc = new StringBuilder();
    // 标黄单元格列表
    List<Integer> cols = new ArrayList<>();
    
    // 对行中数据进行校验
    // 如：必填项验证 前四列为必填
    for (int i = 0; i < 4; i++) {
        if(rowList.get(i) == null){
            cols.add(i);
            if(!errorDesc.toString().contains(EnumFailResult.PARAM_ILLEGAL.getMsg())){
                errorDesc.append(EnumFailResult.PARAM_ILLEGAL.getMsg());
            }
        }
    }
    
    // 如果无单元格需要标黄，则证明本行数据验证成功
    if(cols.isEmpty()){
        UserPO user = new UserPO();
        user.setExternalId(rowList.get(EnumUserColumnName.EXTERNAL_ID.getCode()).toString());
        user.setName(rowList.get(EnumUserColumnName.NAME.getCode()).toString());
        user.setAccount(rowList.get(EnumUserColumnName.ACCOUNT.getCode()).toString());
        user.setRemark(rowList.get(EnumUserColumnName.REMARK.getCode()).toString());
        return user;
    }else{
        // 将错误原因写入最后一列
        rowList.add(errorDesc.toString());
        // 写入本行数据
        writer.writeRow(rowList);
        // 创建样式并设置背景颜色为黄色
        CellStyle cellStyle = createCellStyle(writer);
        for (Integer col : cols) {
            writer.setStyle(cellStyle, col, writer.getRowCount() - 1);
        }
        return null;
    }
}


/**
 * 获取单元格格式---黄色高亮
 */
@NotNull
private CellStyle createCellStyle(ExcelWriter writer) {
    CellStyle cellStyle = writer.createCellStyle();
    cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    // 设置四周边框
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    // 设置内容居中对齐
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    return cellStyle;
}