EasyExcel的导入导出,改变单元格样式,导出到不同的sheet

easyExcel 官方文档https://github.com/alibaba/easyexcel

1.添加依赖

<!--我用的是3.1.1版本-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>
<!--版本由父工程控制-->
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
</dependency>

2.创建excel实体类

@ExcelProperty注解 可以双向操作excel,读取excel中的数据和把java程序中的数据写入excel,括号中的值是excel中的标头

默认是匹配excel中的标头,还可以使用index 匹配行号

@Data
public class ExcelDictDTO {
    @ExcelProperty("id")
    private Long id;

    @ExcelProperty("上级id")
    private Long parentId;

    @ExcelProperty("名称")
    private String name;

    @ExcelProperty("值")
    private Integer value;

    @ExcelProperty("编码")
    private String dictCode;
}

3.创建监听器

在创建监听器时可以加入泛型<>,也就是excel实体类,相当于创建专门操作这种excel数据的监听器

@Slf4j
public class ExcelDictDTOListener extends AnalysisEventListener<ExcelDictDTO> {
    //解析excel数据时的操作
    //excel默认是一行一行的解析数据的
    @Override
    public void invoke(ExcelDictDTO data, AnalysisContext analysisContext) {
		log.info("解析到一条数据: {}",data);
        //调用方法save 保存解析到的数据
    }
    //解析excel数据 结束后的操作
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
		log.info("所有数据解析完成!");
    }
}

4.简单导入Excel

在业务类中使用EasyExcel.read()方法,在监听器中保存解析到的数据,就可以实现将excel中的数据保存到数据库

.read()方法有多个重载,可以传入File类型,也可以传入String类型的文件路径Path,也可以传入io流,这里我使用了InputStream 流的方式传入excel数据

@Service
@Slf4j
public class DictServiceImpl extends ServiceImpl<DictMapper, Dict> implements DictService {
    /**
     * 导入数据
     * @param inputStream
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void importData(InputStream inputStream) {
        //使用easyExcel解析上传的excel数据
        EasyExcel.read(inputStream, ExcelDictDTO.class, new ExcelDictDTOListener()).sheet().doRead();
        log.info("Excel导入成功");
    }
}

5.在监听器中加入保存方法

需要注意的有:

在监听器中,应该使用构造方法传入保存数据的对象(mapper或者service),不要忘了加无参构造方法

当导入的数据带id时,批量插入方法尽量不用mybatis-plus自带的方法,我测试虽然插入成功了,但是id是自增的,而不是数据中的id,

如果导入的数据不带id,就无所谓了

原因可能是实体类id属性上有 type = IdType.AUTO(未确认),这里我自己在xml中写了insert语句

批量插入时数据可能不是整数,余下的数据不够触发插入条件,需要最后保存一下

监听器

@Slf4j
@NoArgsConstructor
public class ExcelDictDTOListener extends AnalysisEventListener<ExcelDictDTO> {
    //使用mapper保存数据
    private DictMapper dictMapper;
    //数据list,用与批量插入数据
    private List<ExcelDictDTO> list = new ArrayList<>();
    //测试环境下每5条数据保存一次
    private static final int BEACH_COUNT=5;

    public ExcelDictDTOListener(DictMapper dictMapper) {
        this.dictMapper = dictMapper;
    }

    //
    //解析excel数据时的操作
    //excel默认是一行一行的解析数据的
    @Override
    public void invoke(ExcelDictDTO data, AnalysisContext analysisContext) {
        log.info("解析到一条数据: {}",data);
        //将数据保存在list中
        list.add(data);
        //当数据达到5条时插入一次
        if (list.size()>=BEACH_COUNT){
            saveData(list);
            //插入成功后清空list
            list.clear();
        }
    }

    private void saveData(List<ExcelDictDTO> list) {
        //调用方法保存到数据库
        dictMapper.insertBatch(list);
        log.info("插入 {}条数据",list.size());
    }

    //解析excel数据 结束后的操作
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("所有数据解析完成!");
        //最后不够5条数据的也保存一下
        saveData(list);
    }
}

批量插入sql

<insert id="insertBatch">
		insert into dict (
		id ,
		parent_id ,
		name ,
		value ,
		dict_code
		) values
		<foreach collection="list" item="item" index="index" separator=",">
			(
			#{item.id} ,
			#{item.parentId} ,
			#{item.name} ,
			#{item.value} ,
			#{item.dictCode}
			)
	    </foreach>
</insert>

controller

@Slf4j
@RestController
@RequestMapping("/admin/core/dict")
public class AdminDictController {
    @Autowired
    private DictService dictService;

    @PostMapping("/import")
    @ApiOperation("Excel批量导入")
    public R beachImport(@RequestParam("file") MultipartFile file) {
        try {
            InputStream inputStream = file.getInputStream();
            dictService.importData(inputStream);
            return R.ok().message("数据批量导入成功");
        } catch (Exception e) {
            log.error("文件上传失败: {}",e.getMessage());
            return R.error().message(ResponseEnum.UPLOAD_ERROR.getMessage());
        }
    }
}

serviceImpl

在这里以有参构造的方式,把baseMapper传入监听器

在方法上加事务注解,出现异常时回滚,防止在导入数据时有异常,导致插入的数据不完整,

@Service
@Slf4j
public class DictServiceImpl extends ServiceImpl<DictMapper, Dict> implements DictService {
    /**
     * 导入数据
     * @param inputStream
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void importData(InputStream inputStream) {
        //使用easyExcel解析上传的excel数据
        EasyExcel.read(inputStream, ExcelDictDTO.class, new ExcelDictDTOListener(this.baseMapper)).sheet().doRead(); //使用有参构造传入baseMapper
        log.info("Excel导入成功");
    }
}

5.简单导出Excel

controller

    @GetMapping("/export")
    public void export(HttpServletResponse response, Dict dictParam) {
        try {
            //设置响应数据类型为excel
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("UTF-8");
            //防止文件名中文乱码
            String fileName = URLEncoder.encode("数据字典", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            //要导出的数据
            List<Dict> list = dictService.list();
            List<ExcelDictDTO> dataList = list.stream().map(item -> {
                ExcelDictDTO dto = new ExcelDictDTO();
                BeanUtils.copyProperties(item, dto);
                return dto;
            }).collect(Collectors.toList());
            //ExcelDictDTO.class 可写可不写
            EasyExcel.write(response.getOutputStream(), ExcelDictDTO.class).sheet("数据字典").doWrite(dataList);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

下面提供一些需求案例,所有案例都是将数据导出成excel并下载

案例中所用的实体类

DemoDataDto

@Data
public class DemoDataDto {
    @ExcelProperty("字符串列")
    private String name;
    
    @ExcelProperty("日期列")
    @DateTimeFormat("yyyy年MM月dd日")
    private Date date;

    @ExcelProperty("数字列")
    @NumberFormat("0.00%") //使用百分比格式化数据,使用#.##% 也可以
    private Double doubleNum;

}

DemoDataExport

@Data
public class DemoDataExport {
    @ExcelProperty("字符串列")
    private WriteCellData<String> name;

    @ExcelProperty("日期列")
    @DateTimeFormat("yyyy年MM月dd日")
    private WriteCellData<Date> date;

    @ExcelProperty("数字列")
    @NumberFormat("0.00%") 
    private WriteCellData<Double> doubleNum;
}

需求案例:导出excel时,根据条件给单元格染色

主要操作就是把数据转成WriteCellData<>类型,然后通过属性WriteCellStyle来控制样式

@GetMapping("/export")
    public void export(HttpServletResponse response) {
        try {
            //设置响应
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("UTF-8");
            //防止中文乱码
            String fileName = URLEncoder.encode("测试Excel", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            //要导出的数据
            List<DemoDataDto> dataList=new ArrayList<>();
            for (int i = 0; i<5; i++){
                DemoDataDto dataDto=new DemoDataDto();
                dataDto.setName("我是字符串"+i);
                dataDto.setDoubleNum(0.33+0.01*i);
                dataDto.setDate(new Date());
                dataList.add(dataDto);
            }
            //要操作样式,需要把数据转换成WriteCellData类型
            List<DemoDataExport> exportList=new ArrayList<>();
            for (DemoDataDto data : dataList) {
                Double num = data.getDoubleNum();
                Date date = data.getDate();
                String name = data.getName();
                WriteCellData<Double> numData=new WriteCellData<>(BigDecimal.valueOf(num));
                WriteCellData<Date> dateData = new WriteCellData<>(date);
                WriteCellData<String> nameData=new WriteCellData<>(name);
                if (name.contains("2")){
                    WriteCellStyle writeCellStyle = new WriteCellStyle();
                    // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.
                    writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                    // 背景蓝色
                    writeCellStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
                    nameData.setWriteCellStyle(writeCellStyle);
                }
                if (num==0.37){
                    WriteCellStyle writeCellStyle = new WriteCellStyle();
                    // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.
                    writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                    // 背景绿色
                    writeCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
                    numData.setWriteCellStyle(writeCellStyle);
                }
                DemoDataExport export=new DemoDataExport();
                export.setDate(dateData);
                export.setName(nameData);
                export.setDoubleNum(numData);
                exportList.add(export);
            }
            EasyExcel.write(response.getOutputStream(), DemoDataExport.class).sheet("测试excel").doWrite(exportList);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

还有很多属性可以设置

 	    WriteCellStyle writeCellStyle = new WriteCellStyle();
	    // 设置字体
        WriteFont contentWriteFont = new WriteFont();
        //设置字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        //设置字体名字
        contentWriteFont.setFontName("宋体");
        //在样式用应用设置的字体;
        writeCellStyle.setWriteFont(contentWriteFont);
        //样式  设置底边框;
        writeCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        writeCellStyle.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置左边框;
        writeCellStyle.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        writeCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置右边框;
        writeCellStyle.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        writeCellStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置顶边框;
        writeCellStyle.setBorderTop(BorderStyle.THIN);
        ///设置顶边框颜色;
        writeCellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 水平居中
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行;
        writeCellStyle.setWrapped(true);

需求案例:将相同类型的数据导出到不同的sheet中

先创建写对象,再创建sheet对象,把数据写到不同的sheet中

@GetMapping("/export2")
    public void export2(HttpServletResponse response) {
        try {
            //设置响应
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("UTF-8");
            //防止中文乱码
            String fileName = URLEncoder.encode("测试Excel2", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            //要导出的数据
            List<DemoDataDto> dataList=new ArrayList<>();
            for (int i = 0; i<5; i++){
                DemoDataDto dataDto=new DemoDataDto();
                dataDto.setName("我是字符串"+i);
                dataDto.setDoubleNum(0.33+0.01*i);
                dataDto.setDate(new Date());
                dataList.add(dataDto);
            }
            //创建写对象
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), DemoDataDto.class).build();
            //sheet对象
            WriteSheet sheet1 = EasyExcel.writerSheet(0, "test").build();
            WriteSheet sheet2 = EasyExcel.writerSheet(1, "test2").build();
            //这里把上面的5条数据写到了两个sheet中
            excelWriter.write(dataList.subList(0, 2),sheet1 ).write(dataList.subList(2,5), sheet2);
            //最后需要调这个方法,否则不会写入excel中
            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

需求案例:将不同类型的数据导出到不同的sheet中

和上面差不多,需要注意的只有:

因为导出的数据类型不是同一个实体类,write()方法中就不能加xxx.class了,

在所有sheet都写完才能.finish()

@GetMapping("/export3")
    public void export3(HttpServletResponse response) {
        try {
            //设置响应
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("UTF-8");
            //防止中文乱码
            String fileName = URLEncoder.encode("测试Excel3", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            //要导出的数据1  DemoDataDto类型
            List<DemoDataDto> dataList=new ArrayList<>();
            for (int i = 0; i<5; i++){
                DemoDataDto dataDto=new DemoDataDto();
                dataDto.setName("我是字符串"+i);
                dataDto.setDoubleNum(0.33+0.01*i);
                dataDto.setDate(new Date());
                dataList.add(dataDto);
            }
            System.out.println(dataList);
            //要导出的数据2 DemoDataExport类型
            //数据转换成WriteCellData类型
            List<DemoDataExport> exportList=new ArrayList<>();
            for (DemoDataDto data : dataList) {
                Double num = data.getDoubleNum();
                Date date = data.getDate();
                String name = data.getName();
                WriteCellData<Double> numData=new WriteCellData<>(BigDecimal.valueOf(num));
                WriteCellData<Date> dateData = new WriteCellData<>(date);
                WriteCellData<String> nameData=new WriteCellData<>(name);
                nameData.setType(CellDataTypeEnum.STRING);
                if (name.contains("2")){
                    WriteCellStyle writeCellStyle = new WriteCellStyle();
                    writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                    // 背景蓝色
                    writeCellStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
                    nameData.setWriteCellStyle(writeCellStyle);
                }
                if (num==0.37){
                    WriteCellStyle writeCellStyle = new WriteCellStyle();
                    writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                    // 背景蓝色
                    writeCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
                    numData.setWriteCellStyle(writeCellStyle);
                }
                DemoDataExport export=new DemoDataExport();
                export.setDate(dateData);
                export.setName(nameData);
                export.setDoubleNum(numData);
                exportList.add(export);
            }
            ServletOutputStream outputStream = response.getOutputStream();
            //因为导出的数据类型不是同一个实体类,所以这里就不能加xxx.class了
            //ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), DemoDataDto.class).build();
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            WriteSheet sheet1 = EasyExcel.writerSheet(0, "test").build();
            excelWriter.write(dataList,sheet1 );
            WriteSheet sheet2 = EasyExcel.writerSheet(1, "test2").build();
            excelWriter.write(exportList ,sheet2 );
            //需要注意的只有在所有sheet都写完了 才能.finsh();
            excelWriter.finish();
            //response.flushBuffer();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

颜色对比