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

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

1.添加依赖 com.alibaba easyexcel 3.1.1 org.apache.xmlbeans xmlbeans 2.创建excel实体类

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

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

@Datapublic 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数据的监听器

@Slf4jpublic class ExcelDictDTOListener extends AnalysisEventListener { //解析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@Slf4jpublic class DictServiceImpl extends ServiceImpl 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@NoArgsConstructorpublic class ExcelDictDTOListener extends AnalysisEventListener { //使用mapper保存数据 private DictMapper dictMapper; //数据list,用与批量插入数据 private List 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 list) { //调用方法保存到数据库 dictMapper.insertBatch(list); log.info("插入 {}条数据",list.size()); } //解析excel数据 结束后的操作 @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { log.info("所有数据解析完成!"); //最后不够5条数据的也保存一下 saveData(list); }}

批量插入sql

insert into dict (id ,parent_id ,name ,value ,dict_code) values(#{item.id} ,#{item.parentId} ,#{item.name} ,#{item.value} ,#{item.dictCode})

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@Slf4jpublic class DictServiceImpl extends ServiceImpl 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 list = dictService.list(); List 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

@Datapublic class DemoDataDto { @ExcelProperty("字符串列") private String name; @ExcelProperty("日期列") @DateTimeFormat("yyyy年MM月dd日") private Date date; @ExcelProperty("数字列") @NumberFormat("0.00%") //使用百分比格式化数据,使用#.##% 也可以 private Double doubleNum;}

DemoDataExport

@Datapublic class DemoDataExport { @ExcelProperty("字符串列") private WriteCellData name; @ExcelProperty("日期列") @DateTimeFormat("yyyy年MM月dd日") private WriteCellData date; @ExcelProperty("数字列") @NumberFormat("0.00%") private WriteCellData 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 dataList=new ArrayList(); for (int i = 0; i Double num = data.getDoubleNum(); Date date = data.getDate(); String name = data.getName(); WriteCellData numData=new WriteCellData(BigDecimal.valueOf(num)); WriteCellData dateData = new WriteCellData(date); WriteCellData 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 dataList=new ArrayList(); for (int i = 0; i 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 dataList=new ArrayList(); for (int i = 0; i Double num = data.getDoubleNum(); Date date = data.getDate(); String name = data.getName(); WriteCellData numData=new WriteCellData(BigDecimal.valueOf(num)); WriteCellData dateData = new WriteCellData(date); WriteCellData 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(); } } 颜色对比