后端导出excel自定义样式(easyExcel)

话不多说直接上代码。。。。。

依赖，最新的poi放前面（最短路径优先原则），这样可以同时使用poi和easyexcel两种方式导入导出

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

        <!--easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.8</version>
        </dependency>

vue页面

    /** 导出按钮操作 */
    exportClick() {
      this.$confirm('是否确认导出所有离寝情况?', "警告", {
        confirmButtonText: "确定",
        cancelButtonText: "取消",
        type: "warning"
      }).then(() => {
        this.exportLoading = true;
        axios.request({
          method: 'post',
          url: process.env.VUE_APP_BASE_API +`/modules/room/exportOnlineLeave`,
          data: this.dataForm,
          headers: {
            Authorization: "Bearer " + getToken(),
          },
          responseType: 'blob'
        }).then(res => {
          const blob = new Blob([res.data], {type: 'application/vnd.ms-excel'})
          let filename = '实时离寝情况报表.xlsx'
          // 创建一个超链接，将文件流赋进去，然后实现这个超链接的单击事件
          const elink = document.createElement('a')
          elink.download = filename
          elink.style.display = 'none'
          elink.href = URL.createObjectURL(blob)
          document.body.appendChild(elink)
          elink.click()
          URL.revokeObjectURL(elink.href) // 释放URL 对象
          document.body.removeChild(elink)
          this.exportLoading = false;
        }).catch(() => {
        });
      })
    },

后端controller

    /**
     * 导出学生实时在寝情况报表
     */
    @PreAuthorize("@ss.hasPermi('modules:room:export')")
    @PostMapping("/exportOnlineReturn")
    public void exportDetailReturn(@RequestBody Map<String, Object> params, HttpServletResponse response) {
        params.remove("pageSize");
        List<BedExcelTemplateVo> list = faceBedService.selectBedOnlineBy(params);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;");
        // 标题所占列数,数字取决于表头数量
        String[] header = new String[5];
        String fileName = "实时在寝情况报表";
        try {
            EasyExcel.write(response.getOutputStream(), BedExcelTemplateVo.class)
                    // 表头、内容样式设置
                    .registerWriteHandler(EasyExcelUtil.STYLE_STRATEGY)
                    // 行高,列宽
                    .registerWriteHandler(new CustomCellWriteHandler())
                    // 第一行标题样式设置
                    .registerWriteHandler(new FirstCellWriteHandler(fileName, header))
                    //设置写入表头信息开始的行数,0行为标题所占行数,因此从1行开始
                    .relativeHeadRowIndex(1)
                    .sheet(fileName)
                    .doWrite(list);
        }catch (Exception e) {
            throw new RuntimeException("下载报表异常");
        }
    }

三个拦截器: CustomCellWriteHandler,EasyExcelUtil,FirstCellWriteHandler

第一个设置表头、内容的行高、列宽样式,CustomCellWriteHandler

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;

import java.util.List;

public class CustomCellWriteHandler extends AbstractCellWriteHandler {
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        if (row.getRowNum() == 1) {
            row.setHeight((short) 500);
        }else {
            short height = 400;
            row.setHeight(height);
        }
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        int cellIndex = cell.getColumnIndex();
            int columnWidth = 25;
            writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);
    }
}

第二个设置标题样式,FirstCellWriteHandler

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;


public class FirstCellWriteHandler implements SheetWriteHandler {

    private final String title;
    private final String[] header;

    public FirstCellWriteHandler(String title, String[] header) {
        this.title = title;
        this.header = header;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        //设置标题
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) 600);
        Cell cell = row1.createCell(0);
        //设置单元格内容
        cell.setCellValue(title);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short) 300);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
        // 第一行大标题占位设置
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, header.length-1));
    }
}

第三个设置表头、内容的样式,EasyExcelUtil

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;

import java.util.LinkedList;
import java.util.List;

public class EasyExcelUtil {

	// 样式策略封装
    public final static HorizontalCellStyleStrategy STYLE_STRATEGY;

    static {
        STYLE_STRATEGY = new HorizontalCellStyleStrategy(getHeadStyle(),getContentStyle());
    }

    /**动态头传入,设置标题,难以自定义标题样式与表头样式不同*/
    public static List<List<String>> head(String[] header, String bigTitle) {
        List<String> head0 = null;
        List<List<String>> list = new LinkedList<List<String>>();
        for (String h : header) {
            head0 = new LinkedList<>();
            head0.add(bigTitle);
            head0.add(h);
            list.add(head0);
        }
        return list;
    }

    /**动态头传入,不设置标题, 标题另外拦截处理*/
    public static List<List<String>> head2(String[] header) {
        List<String> head0 = null;
        List<List<String>> list = new LinkedList<List<String>>();
        for (String h : header) {
            head0 = new LinkedList<>();
            head0.add(h);
            list.add(head0);
        }
        return list;
    }

    /**
     * 简单样式，合并定义
     */
    public static HorizontalCellStyleStrategy styleWrite() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //背景设置为白色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)20);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short)20);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 分开定义，表头样式
     */
    public static WriteCellStyle getHeadStyle(){
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 字体
        WriteFont headWriteFont = new WriteFont();
        //设置字体名字
        headWriteFont.setFontName("黑体");
        //设置字体大小
        headWriteFont.setFontHeightInPoints((short)12);
        //字体加粗
        headWriteFont.setBold(true);
        //在样式用应用设置的字体;
        headWriteCellStyle.setWriteFont(headWriteFont);
        //样式  设置底边框;
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        headWriteCellStyle.setBottomBorderColor((short) 0);
        //设置左边框;
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        headWriteCellStyle.setLeftBorderColor((short) 0);
        //设置右边框;
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        headWriteCellStyle.setRightBorderColor((short) 0);
        //设置顶边框;
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        headWriteCellStyle.setTopBorderColor((short) 0);
        //设置自动换行;
        headWriteCellStyle.setWrapped(true);
        //设置水平对齐的样式为居中对齐;
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //        headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适
        return headWriteCellStyle;
    }

    /**
     * 分开定义，内容样式
     */
    public static WriteCellStyle getContentStyle(){
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 背景绿色
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        // 设置字体
        WriteFont contentWriteFont = new WriteFont();
        //设置字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        //设置字体名字
        contentWriteFont.setFontName("宋体");
        //在样式用应用设置的字体;
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //样式  设置底边框;
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        contentWriteCellStyle.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置左边框;
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        contentWriteCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置右边框;
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        contentWriteCellStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置顶边框;
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        ///设置顶边框颜色;
        contentWriteCellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行;
        contentWriteCellStyle.setWrapped(true);
        //设置单元格格式是：文本格式，方式长数字文本科学计数法
        contentWriteCellStyle.setDataFormat((short)49);
        // contentWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适
        return contentWriteCellStyle;
    }
}

ps:除了自定义拦截器CustomCellWriteHandler(适用于不封装实体类，动态表头)更改行高列宽外，行高也可以通过@HeadRowHeight(xx)和@ContentRowHeight(xx)注解实现，列宽可以通过@ColumnWidth(xx)实现，前提都要封装实体类。这里最好自定义一个vo类（一种实体类类型）——视图对象，用于展示层，它的作用是把某个指定页面（或组件）的所有数据封装起来。作为导出模板，BedExcelTemplateVo就不贴出来了，就是简单的实体类。不搞个实体类封装真的很头疼，搞动态头之类的东西浪费时间（除非真的需要动态字段）

最后上个效果图