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

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

• 模板表格内容
  
  模拟导入失败后，标记行错误提示信息

导入 EasyExcel Maven包

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

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

/**
 * 导入后新增一列标记错误提示信息
 *
 * @author 1014483974@qq.com
 * [能日赚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 1014483974@qq.com
 * [能日赚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("错误信息");
    }

}

运行方法后，生成文件带新增的一列，标记错误行的信息