EasyExcel 实现冻结行和列-蒲公英云

1 Maven依赖

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.8</version>
        </dependency>
        <!--hutool工具包-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.5.1</version>
        </dependency>

2 CustomFreezeRowColHandler(自定义冻结行和列处理器)

package com.easyexcel;
import cn.hutool.core.util.StrUtil;
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.Sheet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
 * 自定义冻结行和列处理器
 */
public class CustomFreezeRowColHandler implements SheetWriteHandler {
    /**
     * sheet名称KEY
     */
    public static final String SHEETNAME_NAME = "sheetName";
    /**
     * 显示行号key
     */
    public static final String DISPLAYROWINDEX_NAME = "displayRowIndex";
    /**
     * 显示列号key
     */
    public static final String DISPLAYCOLINDEX_NAME = "displayColIndex";
    /**
     * 冻结列数key
     */
    public static final String FREEZECOLNUM_NAME = "freezeColNum";
    /**
     * 冻结行数key
     */
    public static final String FREEZEROWNUM_NAME = "freezeRowNum";
    /**
     * sheet页名称列表
     */
    private List<String> sheetNameList;
    /**
     * 冻结行和列信息
     */
    private List<Map<String, String>> freezeList = new ArrayList<>();
    /**
     * 创建冻结行列信息
     *
     * @param sheetName    sheet页名称
     * @param freezeRowNum 冻结行数
     * @param freezeColNum 冻结列数
     * @return
     */
    public static Map<String, String> createFreezeMap(String sheetName, int freezeRowNum, int freezeColNum) {
        return createFreezeMap(sheetName, freezeRowNum, freezeColNum, 0, 0);
    }
    /**
     * 创建冻结行列信息
     *
     * @param sheetName       sheet页名称
     * @param displayRowIndex 显示行号
     * @param displayColIndex 显示列号
     * @param freezeRowNum    冻结行数
     * @param freezeColNum    冻结列数
     * @return
     */
    public static Map<String, String> createFreezeMap(String sheetName, int freezeRowNum, int freezeColNum
            , int displayRowIndex, int displayColIndex) {
        Map<String, String> map = new HashMap<>();
        //sheet页名称
        map.put(SHEETNAME_NAME, sheetName);
        //显示行号
        map.put(DISPLAYROWINDEX_NAME, displayRowIndex + "");
        //显示列号
        map.put(DISPLAYCOLINDEX_NAME, displayColIndex + "");
        //冻结行数
        map.put(FREEZEROWNUM_NAME, freezeRowNum + "");
        //冻结列数
        map.put(FREEZECOLNUM_NAME, freezeColNum + "");
        return map;
    }
    public CustomFreezeRowColHandler(List<Map<String, String>> freezeList) {
        if (freezeList == null || freezeList.size() <= 0) {
            return;
        }
        freezeList = freezeList.stream().filter(x ->
                //判断sheet名称KEY是否存在
                x.keySet().contains(SHEETNAME_NAME) == true && x.get(SHEETNAME_NAME) != null
                        && StrUtil.isNotBlank(x.get(SHEETNAME_NAME).toString())
                        //判断显示行号KEY是否存在
                        && x.keySet().contains(DISPLAYROWINDEX_NAME) == true && x.get(DISPLAYROWINDEX_NAME) != null
                        && StrUtil.isNotBlank(x.get(DISPLAYROWINDEX_NAME).toString())
                        //判断显示列号KEY是否存在
                        && x.keySet().contains(DISPLAYCOLINDEX_NAME) == true && x.get(DISPLAYCOLINDEX_NAME) != null
                        && StrUtil.isNotBlank(x.get(DISPLAYCOLINDEX_NAME).toString())
                        //判断冻结列数KEY是否存在
                        && x.keySet().contains(FREEZECOLNUM_NAME) == true && x.get(FREEZECOLNUM_NAME) != null
                        && StrUtil.isNotBlank(x.get(FREEZECOLNUM_NAME).toString())
                        //判断冻结行数KEY是否存在
                        && x.keySet().contains(FREEZEROWNUM_NAME) == true && x.get(FREEZEROWNUM_NAME) != null
                        && StrUtil.isNotBlank(x.get(FREEZEROWNUM_NAME).toString()))
                .collect(Collectors.toList());
        this.freezeList = freezeList;
        sheetNameList = this.freezeList.stream().map(x -> x.get(SHEETNAME_NAME).toString()).collect(Collectors.toList());
    }
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }
    /**
     * sheet页创建之后调用
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        //不需要冻结行列，或者当前sheet页不需要冻结行列
        if (freezeList == null || freezeList.size() <= 0 || sheetNameList.contains(sheet.getSheetName()) == false) {
            return;
        }
        //获取当前sheet的冻结行列
        List<Map<String, String>> sheetMapList = freezeList.stream().filter(x ->
                StrUtil.equals(x.get(SHEETNAME_NAME).toString(), sheet.getSheetName())
                        && StrUtil.equals(x.get(SHEETNAME_NAME).toString(), sheet.getSheetName())
        ).collect(Collectors.toList());
        //当前sheet页不需要冻结行列
        if (sheetMapList == null || sheetMapList.size() <= 0) {
            return;
        }
        for (Map<String, String> map : sheetMapList) {
            //显示行号
            int displayRowIndex = Integer.parseInt(map.get(DISPLAYROWINDEX_NAME));
            //显示列号
            int displayColIndex = Integer.parseInt(map.get(DISPLAYCOLINDEX_NAME));
            //冻结行数
            int freezeRowNum = Integer.parseInt(map.get(FREEZEROWNUM_NAME));
            //冻结列数
            int freezeColNum = Integer.parseInt(map.get(FREEZECOLNUM_NAME));
            sheet.createFreezePane(freezeColNum, freezeRowNum, displayColIndex, displayRowIndex);
        }
    }
}

3 调试代码

/**
     * 导出（冻结行和列）
     *
     * @param response
     */
    @GetMapping("/exportFreezeRowCol")
    public void exportFreezeRowCol(HttpServletResponse response) {
        try {
            //生成表格数据
            List<List<Object>> dataList = new ArrayList<>();
            dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头11", "表头2", "表头3", "表头4"})));
            dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头1", "表头2", "表头3", "表头4"})));
            dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头31", "表头2", "表头3", "表头4"})));
            List<Map<String,String>> freezeList=new ArrayList<>();
            //导出文件
            String fileName = new String("文件名称.xls".getBytes(), "UTF-8");
            freezeList.add(CustomFreezeRowColHandler.createFreezeMap("模板",1,2));
            response.addHeader("Content-Disposition", "filename=" + fileName);
            //设置类型，扩展名为.xls
            response.setContentType("application/vnd.ms-excel");
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new CustomFreezeRowColHandler(freezeList)).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            WriteSheet writeSheet2 = EasyExcel.writerSheet("模板2").build();
            excelWriter.write(dataList, writeSheet);
            excelWriter.write(dataList, writeSheet2);
            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }