allbs工具类说明 - excel导入导出

依赖jar包

引入包	版本
jdk	1.8
spring boot	2.7.4
easyexcel	3.1.1
spring-boot-starter-validation	2.7.4
spring-boot-starter-web	2.7.4
allbs-common	1.1.8
spring-boot-starter-aop	2.7.4

使用

添加依赖

• maven
• Gradle
• Kotlin

<dependency>
  <groupId>cn.allbs</groupId>
  <artifactId>allbs-excel</artifactId>
  <version>1.1.8</version>
</dependency>

implementation 'cn.allbs:allbs-excel:1.1.8'

implementation("cn.allbs:allbs-excel:1.1.8")

基本导出

当前特殊类型转换的只有java8的LocalDate和LocalDateTime以及TimeStamp 类型转换，如有其他特殊要求请联系我添加转换方法

java

@ApiOperation(value = "excel导出")
@GetMapping("exportExcel")
@ExportExcel(name = "测试excel", sheets = @Sheet(sheetName = "第一个sheet"))
public List<MeterAccountEntity> exportExcel() {
    List<MeterAccountEntity> accountEntities = meterAccountService.list();
    return accountEntities;
}

前端

window.location.href = serverUrl + "/meterAccount/exportExcel";

定义字段名称、宽度、忽略字段等

注解	说明
@ColumnWidth	设定宽度
@ExcelIgnore	忽略该行
@ExcelProperty	设定列名称
@ContentStyle	样式设置各样式具体查看源码即可

@Data
@ApiModel(value = "")
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("meter_account")
public class MeterAccountEntity extends Model<MeterAccountEntity> {

    private static final long serialVersionUID = 356241669539194507L;

    @TableId(value = "id", type = IdType.AUTO)
    @ApiModelProperty(value = "")
    @ColumnWidth(10)
    private Long id;

    @ApiModelProperty(value = "用户名")
    @ExcelProperty("用户名")
    @ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
    private String userName;

    @ApiModelProperty(value = "密码")
    @ExcelProperty("密码")
    private String password;

    @ApiModelProperty(value = "id")
    private String clientId;

    @ApiModelProperty(value = "企业名称")
    private String unitName;

    @ApiModelProperty(value = "0：正常1：逻辑删除")
    @TableLogic
    @ExcelIgnore
    private Integer delFlg;

    @ApiModelProperty(value = "创建人id")
    @ExcelIgnore
    private Long createId;

    @ApiModelProperty(value = "创建时间")
    private LocalDateTime createTime;

    @ApiModelProperty(value = "更新人id")
    @ExcelIgnore
    private Long updateId;

    @ApiModelProperty(value = "更新时间")
    private LocalDateTime updateTime;

    @ApiModelProperty(value = "")
    @ExcelIgnore
    private Long unitId;
}

FillPattern模式说明

fillForegroundColor颜色表

导出多个sheet,并加上密码

@ApiOperation(value = "excel导出多sheet")
@GetMapping("exportSheets")
@ExportExcel(name = "多sheet导出", sheets = {@Sheet(sheetName = "第一个sheet"), @Sheet(sheetName = "第二个sheet")}, password = "chenqi")
public List<List<MeterAccountEntity>> exportSheets() {
    List<MeterAccountEntity> accountEntities = meterAccountService.list();
    List<List<MeterAccountEntity>> list = new ArrayList<>();
    list.add(accountEntities.stream().filter(a -> a.getId() % 2 == 0).collect(Collectors.toList()));
    list.add(accountEntities.stream().filter(a -> a.getId() % 2 != 0).collect(Collectors.toList()));
    return list;
}

读excel

注意实体类中千万不要加@Accessors(chain = true) 否则会读不到数据

导入时适用spring-boot-starter-validation字段校验，所有错误信息将会保存在BindingResult中，出现校验错误的行将不会导入

方式一 使用注解@RequestExcel

@PostMapping("importExcel")
public String importExcel(@RequestExcel(ignoreEmptyRow = true) List<MeterAccountEntity> list, BindingResult bindingResult) {
    meterAccountService.saveBatch(list);
    List<ErrorMessage> errorMessageList = Convert.toList(ErrorMessage.class, bindingResult.getTarget());
    List<String> resList = errorMessageList.stream().map(a -> "第" + a.getLineNum() + "行" + a.getErrors()).collect(Collectors.toList());
    return StrUtil.join(StringPool.SEMICOLON, resList);
}

方式二 文件流读取

@PostMapping("/importExcel")
public List<TestFirEntity> importExcel(MultipartFile file) throws IOException {
    List<TestFirEntity> list = EasyExcel.read(file.getInputStream()).head(TestFirEntity.class).sheet().doReadSync();
    return list;
}

方式三 添加监听器

监听器

@Slf4j
public class MeterAccountListener extends AnalysisEventListener<MeterAccountEntity> {
 
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<MeterAccountEntity> list = new ArrayList<MeterAccountEntity>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    @Resource
    private MeterAccountService demoDAO;
 
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(MeterAccountEntity data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
 
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }
 
    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.saveBatch(list);
        log.info("存储数据库成功！");
    }
}

controller

@PostMapping("importExcel1")
public String importExcel1(@RequestParam("file") MultipartFile file) throws IOException {
    ExcelReader excelReader = EasyExcel.read(file.getInputStream(), MeterAccountEntity.class, new MeterAccountListener()).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet);
    // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
    excelReader.finish();
    return "success";
}

Entity

@Data
@ApiModel(value = "")
@EqualsAndHashCode(callSuper = true)
@AllArgsConstructor
@NoArgsConstructor
@TableName("meter_account")
public class MeterAccountEntity extends Model<MeterAccountEntity> {
 
    private static final long serialVersionUID = 356241669539194507L;
 
    @TableId(value = "id", type = IdType.AUTO)
    @ApiModelProperty(value = "")
    @ColumnWidth(10)
    @ExcelIgnore
    private Long id;
 
    @ApiModelProperty(value = "用户名")
    @ExcelProperty(value = "用户名", index = 0)
    @ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
    private String userName;
 
    @ApiModelProperty(value = "密码")
    @ExcelProperty(value = "密码", index = 1)
    private String password;
 
    @ApiModelProperty(value = "id")
    @ExcelProperty(index = 2)
    private String clientId;
 
    @ApiModelProperty(value = "企业名称")
    @ExcelProperty(index = 3)
    private String unitName;
 
    @ApiModelProperty(value = "0：正常1：逻辑删除")
    @TableLogic
    @ExcelIgnore
    private Integer delFlg;
 
    @ApiModelProperty(value = "创建人id")
    @ExcelIgnore
    private Long createId;
 
    @ApiModelProperty(value = "创建时间")
    @ExcelProperty(index = 4)
    private Date createTime;
 
    @ApiModelProperty(value = "更新人id")
    @ExcelIgnore
    private Long updateId;
 
    @ApiModelProperty(value = "更新时间")
    @ExcelProperty(index = 5)
    private Date updateTime;
 
    @ApiModelProperty(value = "")
    @ExcelIgnore
    private Long unitId;
}