需求:导出数据的内容行,按照每三行设置背景色为 灰色、。
easyexcel 版本
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
1、导出的实体类
@Data
@Builder
public class ExportExcel {
@ExcelProperty("姓名")
private String name;
@ExcelProperty("年龄")
private Integer age;
}
2、controller 接口
@GetMapping("export")
public void export(HttpServletResponse response) {
log.info("导出数据开始");
try {
// 构建数据
List<ExportExcel> list = new ArrayList<>();
for (int i = 1; i < 500; i++) {
list.add(ExportExcel.builder().name("TIMI"+i+"").age(i).build());
}
// 导出设置
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
String fileName = URLEncoder.encode("测试数据表", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
EasyExcel.write(response.getOutputStream(), ExportExcel.class).autoCloseStream(Boolean.FALSE)
.registerWriteHandler(new CustomCellWriteHandler())
.sheet("测试数据表")
.doWrite(list);
log.info("导出数据结束");
}catch (Exception e){
throw new RuntimeException("导出数据失败:" + e.getMessage());
}
}
3、自定义样式
public class CustomCellWriteHandler extends AbstractCellWriteHandler {
/**
* 用于存储单元格样式对象
* 将 CellStyle 缓存下来,防止导出:超过创建超过6W个CellStyle对象报错
*/
private Map<String, CellStyle> contentCellStyleCache = new HashMap<String, CellStyle>();
/**
* 缓存的 单元格样式对象 的KEY
*/
public final static String CELL_STYLE_CACHE_KEY = "CELL_STYLE_CACHE_KEY";
/**
* 每 ROW_PARAM 行设置背景色
*/
public final static int ROW_PARAM = 3;
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (!isHead) {
// 除头外,每三行设置背景色
if ((relativeRowIndex + 1) % ROW_PARAM == 0) {
CellStyle cellStyle = contentCellStyleCache.get(CELL_STYLE_CACHE_KEY);
if (ObjectUtil.isNull(cellStyle)) {
Workbook workbook = writeSheetHolder.getParentWriteWorkbookHolder().getWorkbook();
cellStyle = workbook.createCellStyle();
contentCellStyleCache.put(CELL_STYLE_CACHE_KEY, cellStyle);
}
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(cellStyle);
}
}
}
}
以上的方式即可实现自定义背景色导出。
ps:之前使用的其他方式,导致导出时报错:The maximum number of cell styles was exceeded ,使用以上方式,完美的解决。