最近需要在项目里集成一个excel检错的功能，对excel里的数据校验错误的单元格数据标记背景色，去年年初做大数据量excel操作的时候选用了easyexcel, 研究了一下里面对单元格的样式定义支持不太友好，但是这个工具用起来个人感觉挺爽，所以又去研究了下easyexcel的新功能，看了下支持的功能已经很丰富，已经可以操作自定义样式了，应该是从10月份更新的2.1.0版本开始支持，附上easyexcel的git地址：/alibaba/easyexcel
我这里是选用的版本是2.1.6，附上语雀参考说明：/easyexcel/doc/write#591ee418：
里面功能挺丰富的，自定义格式转换、自定义样式、图片、插入批注、注解操作等，这里只把这次用的自定义样式做为记录：
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.6</version></dependency>
import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.*;import java.util.List;/*** @Desc 自定义拦截器操作单元格样式* @Author wadu* @Date /3/27* @Version 1.0**/@Slf4jpublic class CustomCellWriteHandle implements CellWriteHandler {int[][] checks = {};public CustomCellWriteHandle() {}public CustomCellWriteHandle(int[][] checks ) {this.checks = checks;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {// log.info("第{}行每{}列！" , cell.getRowIndex(), cell.getColumnIndex());Workbook workbook = writeSheetHolder.getSheet().getWorkbook();CellStyle cellStyle = CellStyleUtil.cellStyle(workbook);//根据校验结果设置单元格文字颜色if(checks[cell.getRowIndex()][cell.getColumnIndex()] == 1){//设置单元格背景色// cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());Font font = workbook.createFont();font.setColor(IndexedColors.RED.getIndex());//设置背景色cellStyle.setFont(font);}cell.setCellStyle(cellStyle);}}
public class CellStyleUtil {/*** 通用样式* @param workbook* @return*/public static CellStyle cellStyle(Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();//居中cellStyle.setAlignment(HorizontalAlignment.LEFT);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());//设置边框cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);return cellStyle;}}
@Testpublic void test2(){String fileName= "D:\\tmp\\"+ System.currentTimeMillis()+".xlsx";int[][] ints = new int[11][11];ints[2][3] = 1;ints[1][2] = 1;ints[3][7] = 1;EasyExcel.write(fileName, CheckDictDTO.class).registerWriteHandler(new CustomCellWriteHandle(ints)).sheet("check1").doWrite(data());}private List data() {List<CheckDictDTO> responses = new ArrayList<>();for (int i = 1; i <= 5; i++) {// CheckDictDTO response = CheckDictDTO.builder().word("abc" + i)//.ipa("test1")//.kk("test2").build();// responses.add(response);}return responses;}
