【EasyExcel】导出excel冻结表头和冻结指定列并支持筛选器

发布时间：2024年01月08日

需求背景：

? ? ? ? 导出excel的同时冻结表头和前两列基础信息，方便导出后用户查看信息。

一、技术选型：

? ? ? ? easyExcel的自定义写策略处理：SheetWriteHandler

二、方案设计：（基于实现 SheetWriteHandler 接口）

? ? ? ? 1、重写afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder)方法

? ? ? ? 2、通过?writeSheetHolder.getSheet() 获取 sheet，通过 sheet.createFreezePane() 方法设置导出excel指定冻结行和列，通过?sheet.setAutoFilter() 给指定导出的excel单元格设置自动筛选器.

三、代码实现：

3.1:pom.xml

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

3.2: API

 @ApiOperation(value = "导出")
 @GetMapping(value = "/export")
 public void exportExcel(HttpServletResponse response,@RequestBody TestParam param) {
        service.export(response, param);
 }

3.3:工具类 IEasyExcelServicey

import com.alibaba.excel.write.handler.WriteHandler;

import javax.servlet.http.HttpServletResponse;
import java.util.List;

/**
 * @author c
 */
public interface IEasyExcelService {
    /**
     * 导出excel方法
     *
     * @param exportData 需要导出的数据
     * @param response   response
     * @param tClass     导出excel的字段实体类
     * @param fileName   文件名字
     * @param sheetName  sheet名字
     */
    <T> void exportExcel(List<T> exportData, HttpServletResponse response, Class<T> tClass, String fileName, String sheetName);
    /**
     * 导出excel方法 (携带自定义策略)
     * @param exportData 需要导出的数据
     * @param response HttpServletResponse
     * @param tClass 导出excel的字段实体类
     * @param fileName 文件名字
     * @param sheetName sheet名字
     * @param writeHandler 自定义策略（可扩展多个）
     * @param <T> T
     */
    <T> void exportExcelWithHandler(List<T> exportData, HttpServletResponse response, Class<T> tClass, String fileName, String sheetName, WriteHandler writeHandler);
}

?工具类：EasyExcelServiceImpl

@Slf4j
public class EasyExcelServiceImpl implements IEasyExcelService {

    /**
     * 本地转：response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileNameEncoder + ".xlsx");
     * @param exportData 需要导出的数据
     * @param response   response
     * @param tClass     导出excel的字段实体类
     * @param fileName   文件名字
     * @param sheetName  sheet名字
     * @param <T> T
     */
    @Override
    public <T> void exportExcel(List<T> exportData, HttpServletResponse response, Class<T> tClass, String fileName, String sheetName){
        try{
            // 使用swagger 会导致各种问题，直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // fileName encoder
            String fileNameEncoder = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileNameEncoder + ".xlsx");
            response.setHeader("Access-Control-Expose-Headers", "Content-disposition");
            // write to excel
            EasyExcelFactory.write(response.getOutputStream(), tClass)
                    .autoCloseStream(Boolean.FALSE)
                    .sheet(sheetName)
                    .doWrite(exportData);
        }catch (Exception e){
            log.error("EasyExcelServiceImpl->exportExcel error, message is :{}", e.getMessage());
        }
    }

    @Override
    public <T> void exportExcelWithHandler(List<T> exportData, HttpServletResponse response, Class<T> tClass, String fileName, String sheetName, WriteHandler writeHandler){
        try{
            // 使用swagger 会导致各种问题，直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // fileName encoder
            String fileNameEncoder = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileNameEncoder + ".xlsx");
            response.setHeader("Access-Control-Expose-Headers", "Content-disposition");
            // write to excel
            EasyExcelFactory.write(response.getOutputStream(), tClass)
                    .autoCloseStream(Boolean.FALSE)
                    // 自定义策略(支持扩展多个)
                    .registerWriteHandler(writeHandler)
                    .sheet(sheetName)
                    .doWrite(exportData);
        }catch (Exception e){
            log.error("EasyExcelServiceImpl->exportExcel error, message is :{}", e.getMessage());
        }
    }


}

3.4: 自定义Handle:（自定义设置导出excel设置冻结列和列以及是否自动加筛选器）

/**
 * easyExcel：export handle
 * freeze row and col with set auto filter range
 * @author c
 * @date: 2024-1-5 13:44:26
 */
public class FreezeRowColHandler implements SheetWriteHandler {

    private final FreezeRowColOptions options;

    public FreezeRowColHandler(FreezeRowColOptions options) {
        this.options = options;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        sheet.createFreezePane(options.getColSplit(), options.getRowSplit(), options.getLeftmostColumn(), options.getTopRow());
        if (null != options.getAutoFilterRange()) {
            sheet.setAutoFilter(CellRangeAddress.valueOf(options.getAutoFilterRange()));
        }
    }

}

参数：FreezeRowColOptions

@Data
@AllArgsConstructor
@NoArgsConstructor
public class FreezeRowColOptions {
    /**
     * Horizontal position of split
     */
    private Integer colSplit;
    /**
     * Vertical position of split
     */
    private Integer rowSplit;
    /**
     * Left column visible in right pane
     */
    private Integer leftmostColumn;
    /**
     * Top row visible in bottom pane
     */
    private Integer topRow;
    /**
     * auto filter range
     */
    private String autoFilterRange;
}

参数解释和示例：

四个参数分别代表:
colSplit:表示要冻结的列数；
rowSplit:表示要冻结的行数；
leftmostColumn:表示被固定列右边第一列的列号；
topRow:表示被固定行下边第一列的行号;

举例：
CreateFreezePane(0,1,0,1):冻结第一行,冻结行下侧第一行的左边框显示“2”
CreateFreezePane(1,0,1,0):冻结第一列，冻结列右侧的第一列为B列
CreateFreezePane(2,0,5,0):冻结左侧两列，冻结列右侧的第一列为F列

可以自定义 FreezeRowColConstant 方便维护，如下

/**
 * handle constant：export FreezeRowColConstant
 * freeze row and col with set auto filter range constant
 * @author c
 * @date: 2024-1-5 14:22:21
 */
public class FreezeRowColConstant {
    /**
     * TEST_ONE  export
     */
    public static final FreezeRowColOptions TEST_ONE = new FreezeRowColOptions(2, 2, 0, 0, "A2:BH2");
    /**
     * TEST_TWO  export
     */
    public static final FreezeRowColOptions TEST_TWO = new FreezeRowColOptions(2, 2, 0, 0, "A2:AC2");
    /**
     * TEST_THREE  export
     */
    public static final FreezeRowColOptions TEST_THREE = new FreezeRowColOptions(2, 2, 0, 0, "A2:T2");
}

ITestExportService：(这里可以根据自己的业务进行自定义，本文定义这个是因为在同一个业务里面有几个类似的导出可以共用这个导出接口)

public interface ITestExportService {
    /**
     * export data
     * @param response HttpServletResponse
     * @param param export common param
     * @throws BaseException ex
     */
    default void exportData(HttpServletResponse response, TestParam param) {}
}

下面给出其中一个导出的实现方法：

@Service
@Slf4j
public class TestOneServiceImpl implements ITestExportService {

    private static final String FILE_NAME_TEST = "测试";
    private final IEasyExcelService easyExcelService;
  
    public TestOneServiceImpl (IEasyExcelService easyExcelService) {
        this.easyExcelService = easyExcelService; 
    }

    @Override
    public void exportData(HttpServletResponse response, TestParam param) {
        // 这里是根据查询参数param获取需要导出的数据
        // get data
        List<DemoExcelData> data = this.ExportData(param);
        // export：调用 exportExcelWithHandler 导出
        easyExcelService.exportExcelWithHandler(data, response, DemoExcelData.class, FILE_NAME_TEST, FILE_NAME_TEST,
                new FreezeRowColHandler(FreezeRowColConstant.TEST_ONE));
    }
}

实现效果：(都加上了自动筛选器?)

下面是示例，在TestOneServiceImpl 方法中调用exportExcelWithHandler方法的时候实际用的是FreezeRowColConstant.TEST_ONE，其中?FreezeRowColConstant.TEST_ONE 设置的是?new FreezeRowColOptions(2, 2, 0, 0, "A2:BH2")，其含义是：

? ? ? ? 第一个参数：表示冻结前两行

? ? ? ? 第二个参数：表示冻结前两列?

????????"A2:BH2"：表示单元格"A2:BH2"区间设置添加自动筛选器

表头固定效果：

?前两列固定效果：