EasyExcel导出设置表头字体样式和批注

EasyExcel版本说明

<!-- easy excel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

自定义的注解方式实现, 这篇是我之前的写过的博客功能基础上做的补充添加.

@ExcelRequired: 作用字段, 设置Excel列头字体颜色, 默认为红色, 标记此列为必填项, 可传参IndexedColors枚举指定颜色;

@ExcelNotation: 作用字段, 设置Excel列批注说明, 默认为空, 可以传参批注想要的备注信息内容;

@ExcelSelector: 作用字段, 导出Excel单元格的下拉选默认数据, 如不需要, 可以忽略, 如有需要可以找我博客, 有具体说明使用, 使用说明链接地址: https://blog.csdn.net/weixin_41481806/article/details/126978596

展示效果

类ExcelRequired

@Target(FIELD)
@Retention(RUNTIME)
public @interface ExcelRequired {

    /**
     * 字体颜色
     */
    IndexedColors frontColor() default IndexedColors.RED;
}

类ExcelNotation

@Target(FIELD)
@Retention(RUNTIME)
public @interface ExcelNotation {

    /**
     * 文本内容
     */
    String value() default "";
}

Excel工具类

@Slf4j
public class ExcelUtil {

    /**
     * 默认的sheet名称
     */
    private static final String DEFAULT_SHEET_NAME = "Sheet1";

    /**
     * 写Excel数据
     *
     * @param response response
     * @param fileName 文件名称
     * @param data     数据
     * @param clazz    类class
     * @author SunLingDa
     * @date 2022/9/6 13:59
     */
    public static <T> void writeExcel(HttpServletResponse response, String fileName, List<T> data, Class<?> clazz) {
        writeExcel(response, fileName, DEFAULT_SHEET_NAME, data, clazz);
    }

    /**
     * 写Excel数据
     *
     * @param response  response
     * @param fileName  文件名称
     * @param sheetName sheet名称
     * @param data      数据
     * @param clazz     类class
     * @author SunLingDa
     * @date 2022/9/6 13:58
     */
    public static <T> void writeExcel(HttpServletResponse response, String fileName, String sheetName, List<T> data, Class<?> clazz) {
        OutputStream outputStream = null;
        Map<Integer, Short> requiredMap = getRequiredMap(clazz);
        Map<Integer, String> notationMap = getNotationMap(clazz);
        Map<Integer, ExcelSelectorResolve> selectedMap = getSelectedMap(clazz);
        ExcelSelectorDataWriteHandler writeHandler = new ExcelSelectorDataWriteHandler(notationMap, requiredMap, selectedMap);
        try {
            outputStream = getOutputStream(response, fileName, ExcelTypeEnum.XLSX);
            ExcelWriterSheetBuilder builder = EasyExcel.write(outputStream, clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName);
            builder.registerWriteHandler(writeHandler).doWrite(data);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    log.error(e.getMessage(), e);
                }
            }
        }
    }

    /**
     * 读取不包含头信息的Excel
     *
     * @param file  文件
     * @param clazz 类class
     * @author SunLingDa
     * @date 2022/9/6 13:20
     */
    public static <T> List<T> readExcelNotContainHeader(MultipartFile file, Class<T> clazz) throws IOException {
        return readExcel(1, file, clazz);
    }

    /**
     * 读取包含头信息的Excel
     *
     * @param file  文件
     * @param clazz 类class
     * @author SunLingDa
     * @date 2022/9/6 13:20
     */
    public static <T> List<T> readExcelContainHeader(MultipartFile file, Class<T> clazz) throws IOException {
        return readExcel(0, file, clazz);
    }

    /**
     * 读取Excel
     *
     * @param rowNum 行数
     * @param file   文件
     * @param clazz  类class
     * @author SunLingDa
     * @date 2022/9/6 13:20
     */
    public static <T> List<T> readExcel(int rowNum, MultipartFile file, Class<T> clazz) throws IOException {
        String fileName = file.getOriginalFilename();
        InputStream inputStream = file.getInputStream();
        return readExcel(rowNum, fileName, inputStream, clazz);
    }

    /**
     * 读取不包含头信息的Excel
     *
     * @param fileName    文件名称
     * @param inputStream 流
     * @param clazz       类
     * @author SunLingDa
     * @date 2022/9/6 13:16
     */
    public static <T> List<T> readExcelNotContainHeader(String fileName, InputStream inputStream, Class<T> clazz) {
        return readExcel(1, fileName, inputStream, clazz);
    }

    /**
     * 读取包含头信息的Excel
     *
     * @param fileName    文件名称
     * @param inputStream 流
     * @param clazz       类
     * @param listener    监听
     * @author SunLingDa
     * @date 2022/9/6 13:16
     */
    public static <T> List<T> readExcelContainHeader(String fileName, InputStream inputStream, Class<T> clazz, ExcelCellDataListener<T> listener) {
        return readExcel(0, fileName, inputStream, clazz);
    }

    /**
     * 读取Excel
     *
     * @param rowNum      行数
     * @param fileName    文件名称
     * @param inputStream 流
     * @param clazz       类
     * @author SunLingDa
     * @date 2022/9/6 13:16
     */
    public static <T> List<T> readExcel(int rowNum, String fileName, InputStream inputStream, Class<T> clazz) {
        ExcelCellDataListener<T> dataListener = new ExcelCellDataListener<>();
        try {
            ExcelReader excelReader = getExcelReader(rowNum, fileName, inputStream, clazz, dataListener);
            if (excelReader == null) {
                return null;
            }
            List<ReadSheet> sheetList = excelReader.excelExecutor().sheetList();
            for (ReadSheet sheet : sheetList) {
                excelReader.read(sheet);
            }
            excelReader.finish();
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
        return dataListener.getData();
    }

    /**
     * 获取OutputStream
     *
     * @param response response
     * @param fileName 文件名称
     * @return java.io.OutputStream
     * @author SunLingDa
     * @date 2022/9/6 13:44
     */
    private static OutputStream getOutputStream(HttpServletResponse response, String fileName, ExcelTypeEnum typeEnum) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setStatus(200);
        response.setCharacterEncoding("UTF-8");
        if (ExcelTypeEnum.CSV.equals(typeEnum)) {
            response.setContentType("application/csv");
        } else {
            response.setContentType("application/vnd.ms-excel");
        }
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + typeEnum.getValue());
        return response.getOutputStream();
    }

    /**
     * 获取ExcelReader
     *
     * @param rowNum      行数
     * @param fileName    文件名称
     * @param inputStream 流
     * @param clazz       类class
     * @param listener    监听
     * @return com.alibaba.excel.ExcelReader
     * @author SunLingDa
     * @date 2022/9/6 13:19
     */
    private static ExcelReader getExcelReader(int rowNum, String fileName, InputStream inputStream, Class<?> clazz, ReadListener listener) {
        if (StrUtil.isBlank(fileName)) {
            return null;
        }
        String fileExtName = getFileExtName(fileName);
        EasyExcelTypeEnum typeEnum = EasyExcelTypeEnum.parseType(fileExtName);
        if (typeEnum == null) {
            log.info("表格类型错误");
        }

        return EasyExcel.read(inputStream, clazz, listener).headRowNumber(rowNum).build();
    }

    /**
     * 获取文件后缀名称 .xxx
     *
     * @param fileName 文件名称
     * @return java.lang.String
     * @author SunLingDa
     * @date 2022/9/6 11:23
     */
    private static String getFileExtName(String fileName) {
        if (StrUtil.isBlank(fileName)) {
            return null;
        }
        int lastIndex = fileName.lastIndexOf(StrUtil.DOT);
        if (lastIndex != -1) {
            return fileName.substring(lastIndex);
        }
        return null;
    }

    /**
     * 获取样式
     *
     * @return com.alibaba.excel.write.style.HorizontalCellStyleStrategy
     * @author SunLingDa
     * @date 2022/9/6 16:20
     */
    private static HorizontalCellStyleStrategy getStyleStrategy() {
        // 表头样式
        WriteCellStyle headStyle = new WriteCellStyle();
        // 设置表头居中对齐
        headStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 内容样式
        WriteCellStyle contentStyle = new WriteCellStyle();
        // 设置内容靠左对齐
        contentStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);

        return new HorizontalCellStyleStrategy(headStyle, contentStyle);
    }

    /**
     * 获取下拉的map
     *
     * @param clazz 类class
     * @return java.util.Map<java.lang.Integer, cn.com.zxelec.common.resolve.ExcelSelectorResolve>
     * @author SunLingDa
     * @date 2022/9/20 17:45
     */
    private static Map<Integer, ExcelSelectorResolve> getSelectedMap(Class<?> clazz) {
        Map<Integer, ExcelSelectorResolve> selectedMap = CollUtil.newHashMap();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (!field.isAnnotationPresent(ExcelSelector.class) || !field.isAnnotationPresent(ExcelProperty.class)) {
                continue;
            }
            ExcelSelector excelSelector = field.getAnnotation(ExcelSelector.class);
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            ExcelSelectorResolve resolve = new ExcelSelectorResolve();
            String[] data = resolve.resolveExcelSelector(excelSelector);
            if (ArrayUtil.isNotEmpty(data)) {
                resolve.setSelectorData(data);
                selectedMap.put(excelProperty.index(), resolve);
            }
        }
        return selectedMap;
    }

    /**
     * 获取必填列Map
     *
     * @param clazz 类class
     * @return java.util.Map<java.lang.Integer, java.lang.Short>
     * @author SunLingDa
     * @date 2022/11/3 13:23
     */
    private static Map<Integer, Short> getRequiredMap(Class<?> clazz) {
        Map<Integer, Short> requiredMap = CollUtil.newHashMap();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (!field.isAnnotationPresent(ExcelRequired.class) || !field.isAnnotationPresent(ExcelRequired.class)) {
                continue;
            }
            ExcelRequired excelRequired = field.getAnnotation(ExcelRequired.class);
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            requiredMap.put(excelProperty.index(), excelRequired.frontColor().getIndex());
        }
        return requiredMap;
    }

    /**
     * 获取批注Map
     *
     * @param clazz 类class
     * @return java.util.Map<java.lang.Integer, java.lang.String>
     * @author SunLingDa
     * @date 2022/11/3 13:24
     */
    private static Map<Integer, String> getNotationMap(Class<?> clazz) {
        Map<Integer, String> notationMap = CollUtil.newHashMap();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (!field.isAnnotationPresent(ExcelNotation.class) || !field.isAnnotationPresent(ExcelRequired.class)) {
                continue;
            }
            ExcelNotation excelNotation = field.getAnnotation(ExcelNotation.class);
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            notationMap.put(excelProperty.index(), excelNotation.value());
        }
        return notationMap;
    }
}

主要的内容, 如下图, 下拉选不需要可以忽略, 然后主要的就是对应的处理器了

Excel处理器的代码, 主要的三个Map, 可根据自己的需求选择

@Data
public class ExcelSelectorDataWriteHandler implements SheetWriteHandler, CellWriteHandler {

    /**
     * 批注
     */
    private final Map<Integer, String> notationMap;

    /**
     * 头列字体颜色
     */
    private final Map<Integer, Short> headColumnMap;

    /**
     * 下拉选数据
     */
    private final Map<Integer, ExcelSelectorResolve> selectedMap;

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        if (CollUtil.isEmpty(selectedMap)) {
            return;
        }
        selectedMap.forEach((k, v) -> {
            // 下拉 首行 末行 首列 末列
            CellRangeAddressList list = new CellRangeAddressList(v.getStartRow(), v.getEndRow(), k, k);
            // 下拉值
            DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSelectorData());
            DataValidation validation = helper.createValidation(constraint, list);
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            validation.createErrorBox("提示", "请输入下拉选项中的内容");
            sheet.addValidationData(validation);
        });
    }

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();

        DataFormatData dataFormatData = new DataFormatData();
        // 单元格设置为文本格式
        dataFormatData.setIndex((short) 49);
        writeCellStyle.setDataFormatData(dataFormatData);

        if (context.getHead()) {
            Cell cell = context.getCell();
            WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();
            Sheet sheet = writeSheetHolder.getSheet();
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            Drawing<?> drawing = sheet.createDrawingPatriarch();
            // 设置标题字体样式
            WriteFont headWriteFont = new WriteFont();
            // 加粗
            headWriteFont.setBold(true);
            if (CollUtil.isNotEmpty(headColumnMap) && headColumnMap.containsKey(cell.getColumnIndex())) {
                // 设置字体颜色
                headWriteFont.setColor(headColumnMap.get(cell.getColumnIndex()));
            }
            writeCellStyle.setWriteFont(headWriteFont);
            CellStyle cellStyle = StyleUtil.buildCellStyle(workbook, null, writeCellStyle);
            cell.setCellStyle(cellStyle);

            if (CollUtil.isNotEmpty(notationMap) && notationMap.containsKey(cell.getColumnIndex())) {
                // 批注内容
                String notationContext = notationMap.get(cell.getColumnIndex());
                // 创建绘图对象
                Comment comment = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), 0, (short) 5, 5));
                comment.setString(new XSSFRichTextString(notationContext));
                cell.setCellComment(comment);
            }
        }
    }
}

主要在方法afterCellDispose的if (context.getHead()) 部分, 如下图