EasyExcel新增一列数据，用于导入后标记行错误提示信息

2023-12-11 02:01| 来源: 网络整理| 查看: 265

EasyExcel新增一列数据，用于导入后标记错误提示信息模板表格内容在这里插入图片描述

模拟导入失败后，标记行错误提示信息

导入 EasyExcel Maven包

com.alibaba easyexcel 3.0.5

能日赚30的APP试玩平台推荐，亲测有效

/** * 导入后新增一列标记错误提示信息 * * @author [email protected] * [能日赚30的APP试玩平台推荐，亲测有效] * (https://mp.weixin.qq.com/s/VZRMTVWmKwfFV4-miJnOJw) */ public class ExcelTest { @Test public void test() { // 临时文件路径 String writerFilePath = "D:/temp/device_" + IdUtil.fastUUID() + ".xls"; try (FileInputStream in = new FileInputStream("D:/temp/template.xls")) { // 生成错误信息的文件 ExcelWriterBuilder writerBuilder = EasyExcel.write(writerFilePath) .withTemplate(in) .excelType(ExcelTypeEnum.XLS) .needHead(false); ExcelWriterSheetBuilder writerSheetBuilder = EasyExcel.writerSheet("Sheet1"); writerBuilder.registerWriteHandler(new CommentWriteHandler(1, "IMEI已被分配")); ExcelWriter excelWriter = writerBuilder.build(); excelWriter.write(new ArrayList(), writerSheetBuilder.build()); excelWriter.finish(); } catch (IOException e) { e.printStackTrace(); } } }

CommentWriteHandler 是自定义handler，根据业务需求调整

/** * 自定义拦截器.新增注释,第一行头加批注 * * @author [email protected] * [能日赚30的APP试玩平台推荐，亲测有效] * (https://mp.weixin.qq.com/s/VZRMTVWmKwfFV4-miJnOJw) */ @Slf4j public class CommentWriteHandler implements SheetWriteHandler { /** * 第几行 */ private final Integer cellIndex; /** * 批注内容 */ private final String annotation; /** * 表格样式 */ private CellStyle cellStyle; private CellStyle cellStyleHeader; public CommentWriteHandler(Integer cellIndex, String annotation) { this.cellIndex = cellIndex; this.annotation = annotation; } @Override public void afterSheetCreate(SheetWriteHandlerContext context) { Sheet sheet = context.getWriteSheetHolder().getSheet(); sheet.setColumnWidth(1, 18000); Row row = sheet.getRow(cellIndex); if (row == null) { row = sheet.createRow(cellIndex); } Cell cell = row.getCell(1); if (cell == null) { cell = row.createCell(1); } Workbook workbook = context.getWriteWorkbookHolder().getWorkbook(); // 表头样式 if (cellStyleHeader == null) { cellStyleHeader = workbook.createCellStyle(); cellStyleHeader.setAlignment(HorizontalAlignment.CENTER); cellStyleHeader.setVerticalAlignment(VerticalAlignment.CENTER); cellStyleHeader.setFillForegroundColor(IndexedColors.WHITE.getIndex()); Font font = workbook.createFont(); font.setFontName("微软雅黑"); font.setColor((short) 10); font.setFontHeightInPoints((short) 12); cellStyleHeader.setFont(font); } cell.setCellStyle(cellStyleHeader); cell.setCellValue(annotation); // 内容样式 if (cellStyle == null) { cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); Font font = workbook.createFont(); font.setFontName("微软雅黑"); font.setFontHeightInPoints((short) 12); cellStyle.setFont(font); } // 表头 Row row0 = sheet.getRow(0); if (row0 == null) { row0 = sheet.createRow(0); } // 第几列。我这里是1.正常业务根据需求传递 Cell cell0 = row0.getCell(1); if (cell0 == null) { cell0 = row0.createCell(1); } cell0.setCellStyle(cellStyle); cell0.setCellValue("错误信息"); } }

运行方法后，生成文件带新增的一列，标记错误行的信息在这里插入图片描述

【本文地址】

公司简介

联系我们