EasyExcel 实现冻结行和列

矫情吗；* 2022-10-15 04:50 268阅读 0赞

### 1 Maven依赖 ###

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.8</version>
            </dependency>
            
            <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();
            }
        }

### 4 调试结果 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70]: /images/20221014/6f8ada7a678e4433a07391c0ae06871f.png