使用easypoi导出excel设置表头样式

使用easypoi导出excel设置表头属性

之前使用easypoi导出excel的时候，没太关注这个表头的样式设置，直到前几天看到个需求，需要表头设置蓝底白字，懵了。嘿嘿，不过百度了一下，懂了一点点。

导入easypoi

			<easypoi.version>4.0.0</easypoi.version>
			<!--easyPOI工具类 -->
			<dependency>
				<groupId>cn.afterturn</groupId>
				<artifactId>easypoi-base</artifactId>
				<version>${easypoi.version}</version>
			</dependency>
			<dependency>
				<groupId>cn.afterturn</groupId>
				<artifactId>easypoi-web</artifactId>
				<version>${easypoi.version}</version>
			</dependency>
			<dependency>
				<groupId>cn.afterturn</groupId>
				<artifactId>easypoi-annotation</artifactId>
				<version>${easypoi.version}</version>
			</dependency>

ExcelUtil

package com.aqara.project.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.nio.charset.StandardCharsets;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Title: excel导出工具类
 * @Author: jackson
 * @Date: 2018-09-24 17:44
 */
@Slf4j
public class ExcelUtil {
    static ServletOutputStream out = null;

    private ExcelUtil() {
    }

    /**
     * 获取总页数
     */
    public static Long getTotalPage(Long totalCount, Long pageSize) {
        if (totalCount % pageSize == 0) {
            return totalCount / pageSize;
        } else {
            return (totalCount / pageSize) + 1;
        }
    }

    /**
     * 获取导出表格参数
     */
    public static ExportParams getExportParams(String title, String sheetName) {
        return new ExportParams(title, sheetName, ExcelType.XSSF);
    }

    /**
     * 适配集合+实体类方式导出
     */
    public static void exportExcel(String fileName, String title, String sheetName, List<?> list,
                                   Class<?> pojoClass,
                                   HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setType(ExcelType.XSSF); //此处格式对应下文文件名后缀xlsx
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        defaultExport(workbook, fileName, response);
    }

    public static void exportExcel(String fileName, String title, String sheetName, List<?> list,
                                   Class<?> pojoClass,
                                   HttpServletResponse response, boolean flag) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setType(ExcelType.XSSF); //此处格式对应下文文件名后缀xlsx
        if (flag) {
            exportParams.setStyle(ExcelExportTitleStyle.class);
        }
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        defaultExport(workbook, fileName, response);
    }

    /**
     * 适配集合+实体类方式导出 专用于返回列表下还有统计总数、等
     */
    public static Workbook exportExcel(String fileName, String title, String sheetName, List<?> list,
                                       Class<?> pojoClass) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        //此处格式对应下文文件名后缀xlsx
        exportParams.setType(ExcelType.XSSF);
        return ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
    }


    /**
     * 适配动态列的导出方式
     *
     * @param colList 动态列
     */
    public static void exportExcel(String fileName, String title, String sheetName,
                                   List<ExcelExportEntity> colList, List<Map<String, Object>> dataList,
                                   HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setType(ExcelType.XSSF); // 此处格式对应下文文件名后缀xlsx
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, colList, dataList);
        defaultExport(workbook, fileName, response);
    }

    public static void defaultExport(Workbook workbook, String fileName, HttpServletResponse response) {
        defaultExport(workbook, fileName, response, "application/msexcel; charset=UTF-8");
    }

    public static void defaultBigDataExport(Workbook workbook, String fileName, HttpServletResponse response) {
        defaultExport(workbook, fileName, response, "application/vnd.openxmlformats-officedocument.drawing+xml; charset=UTF-8");
    }

    private static void defaultExport(Workbook workbook, String fileName, HttpServletResponse response
            , String contentType) {
        if (workbook == null) {
            log.warn("导出workbook对象(文件名:{})为空!请检查", fileName);
            return;
        }
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" +
                    new String(fileName.getBytes("gbk"), "iso8859-1") + ".xlsx");
            response.setContentType(contentType);
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }


    /**
     * 导入excel,此种方式支持校验,但是无校验的信息
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Integer sheetIndex,
                                          Class<T> pojoClass) throws Exception {
        ImportParams params = getParams(file, titleRows, headerRows);
        if (params == null) {
            return Collections.emptyList();
        }
        //要读取的 sheet数目
        params.setSheetNum(1);
        params.setStartSheetIndex(sheetIndex);
        return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
    }

    /**
     * 导入excel,此种方式支持校验,也可获取校验的信息
     */
    public static <T> ExcelImportResult<T> importExcelMore(MultipartFile file, Integer titleRows
            , Integer headerRows, Class<T> pojoClass) throws Exception {
        ImportParams params = getParams(file, titleRows, headerRows);
        if (params == null) {
            return null;
        }
        return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
    }


    private static ImportParams getParams(MultipartFile file, Integer titleRows, Integer headerRows) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedVerify(true);
        return params;
    }


    /**
     * 收集excel校验失败的信息
     */
    public static Boolean collectExcelVerifyFailMsg(StringBuilder rowMsg,
                                                    ExcelImportResult<? extends BaseImportExcelDto> importResult) {
        List<? extends BaseImportExcelDto> failList = importResult.getFailList();
        //无错误信息返回 true
        if (failList == null || failList.isEmpty()) {
            return true;
        }
        for (BaseImportExcelDto baseImportExcelDto : failList) {
            rowMsg.append("[excel第" + (baseImportExcelDto.getRowNum() + 1) + "行" +
                    baseImportExcelDto.getErrorMsg() + "]；");
        }
        return false;
    }

    /**
     * 导出模板
     *
     * @param fileName 　文件名
     * @param filePath 　模板位置
     */
    public static void exportTemplateExcel(String fileName, String filePath,
                                           HttpServletResponse response) {
        try (InputStream inputStream = ExcelUtil.class.getResourceAsStream(filePath);
             XSSFWorkbook excelTemplate = new XSSFWorkbook(inputStream);
             OutputStream outputStream = response.getOutputStream()) {
            response.reset();
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1")
                            + ".xlsx");
            response.setContentType("application/msexcel; charset=UTF-8");

            // 解决跨域问题
            response.addHeader("Access-Control-Allow-Origin", "*");

            excelTemplate.write(outputStream);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            exportWarnMsg(response, "导出模板失败!,路径：" + filePath);
        }
    }

    /**
     * 通过response输出响应信息
     */
    public static void exportWarnMsg(HttpServletResponse response, String msg) {
        try {
            response.setContentType("application/json;charset-utf-8");
            ServletOutputStream outputStream = response.getOutputStream();
            outputStream.write(msg.getBytes(StandardCharsets.UTF_8));
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }


    public static void exportDesign(String fileName, String title, String sheetName, List<?> list, Class<?> pojoClass,
                                    HttpServletResponse response) {

        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setType(ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        Sheet sheet = workbook.getSheet(sheetName);

        // 隐藏最后一列
        sheet.setColumnHidden(sheet.getRow(0).getPhysicalNumberOfCells() - 1, true);

        defaultExport(workbook, fileName, response);
    }

    /**
     * 导入excel,此种方式支持校验,也可获取校验的信息
     */
    public static <T> ExcelImportResult<T> importExcelWithVerifyMessages(
            MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)
            throws Exception {
        ImportParams params = getParams(file, titleRows, headerRows);
        if (params == null) {
            return null;
        }
        return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
    }

    public static class BaseImportExcelDto implements IExcelModel, IExcelDataModel, Serializable {
        /**
         * 实现IExcelDataModel接口,返回行号
         */
        private int rowNum;

        /**
         * 实现IExcelModel接口,返回不符合的信息
         */
        private String errorMsg;

        @Override
        public int getRowNum() {
            return rowNum;
        }

        @Override
        public void setRowNum(int rowNum) {
            this.rowNum = rowNum;
        }

        @Override
        public String getErrorMsg() {
            return errorMsg;
        }

        @Override
        public void setErrorMsg(String errorMsg) {
            this.errorMsg = errorMsg;
        }
    }


    /**
     * @Description:创建一个sheet页的数据
     * @Author: weiwu
     * @Date: 2021-02-03
     **/
    public static Map<String, Object> createOneSheet(String sheetName, String title, Class<?> clazz, List<?> data) {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        Map<String, Object> map = new HashMap<>();
        map.put("title", exportParams);//new ExportParams("title"+i, "sheetName"+i, ExcelType.XSSF)
        map.put("entity", clazz);
        map.put("data", data);
        return map;
    }

    /**
     * @Description:生成多个sheet页的Workbook
     * @Author: weiwu
     * @Date: 2021-02-03
     **/
    public static void exportExcelMultipleSheet(String fileName, List<Map<String, Object>> mapListList, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(mapListList, ExcelType.XSSF);
        defaultExport(workbook, fileName, response);
    }

    public static final boolean checkExtensions(String extension) {
        return Lists.newArrayList("xls", "xlsx", "XLS", "XLSX").contains(extension);
    }


    public static void init(String fileName, HttpServletResponse response) throws IOException {
        out = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), StandardCharsets.ISO_8859_1) + ".xlsx");
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
    }
}

一切准备就绪

我们把一个list准备导出（注意实体类的字段加上@Excel的注解哦），一切准备好了发现可以导出来了。但是我们现在要修改样式问题。
ExportParams这个是easypoi，我们点进去发现，他的style属性使用了默认的ExcelExportStylerDefaultImpl这个类的属性，茅塞顿开，我决定自定义一个class，然后set他的style属性，这样不就可以使用我们自定义的属性了？

    /**
     * Excel 导出style
     */
    private Class<?> style = ExcelExportStylerDefaultImpl.class;

开始 (很重要)

我们新增一个类:ExcelExportTitleStyle,通过修改他的getTitleStyle方法进行我们的自定义颜色。

package com.aqara.agreement.utils;

import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * 导出自定义title的工具类
 *
 * @author changjiang.liu
 * @date 2022/5/23 16:44
 */
public class ExcelExportTitleStyle extends AbstractExcelExportStyler
        implements IExcelExportStyler {
    public ExcelExportTitleStyle(Workbook workbook) {
        super.createStyles(workbook);
    }

    @Override
    public CellStyle getTitleStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        // 自定义字体
        Font font = workbook.createFont();
        font.setColor(IndexedColors.WHITE1.getIndex());
        font.setBold(true);
        font.setFontName("宋体");
        titleStyle.setFont(font);

		// 自定义背景色
        titleStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);

        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setWrapText(true);
        return titleStyle;
    }

    @Override
    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

    @Override
    public CellStyle getHeaderStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return titleStyle;
    }

    @Override
    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

}

##　效果