EasyExcel 设置行高列宽、隐藏行和列

约定不等于承诺〃 2022-09-07 09:22 799阅读 0赞

## 1 Maven依赖 ##

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

## 2 CustomRowHeightColWidthHandler ##

自定义行高列宽处理器。

package com.easyexcel;
    
    import cn.hutool.core.collection.CollectionUtil;
    import cn.hutool.core.util.StrUtil;
    import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
    import com.alibaba.excel.write.metadata.holder.*;
    import org.apache.poi.ss.usermodel.*;
    
    import java.util.*;
    import java.util.stream.Collectors;
    
    /**
     * 自定义行高列宽处理器
     */
    public class CustomRowHeightColWidthHandler extends AbstractRowWriteHandler {
        /**
         * sheet名称KEY
         */
        public static final String KEY_SHEET_NAME = "sheetName";
        /**
         * 行号key
         */
        public static final String KEY_ROW_INDEX = "rowIndex";
        /**
         * 列号key
         */
        public static final String KEY_COL_INDEX = "colIndex";
        /**
         * 行高key
         */
        public static final String KEY_ROW_HEIGHT = "rowHeight";
        /**
         * 列宽key
         */
        public static final String KEY_COL_WIDTH = "colWidth";
        /**
         * sheet页名称列表
         */
        private List<String> sheetNameList;
        /**
         * 列宽信息
         */
        private List<Map<String, Object>> colWidthList = new ArrayList<>();
    
        /**
         * 行高信息
         */
        private List<Map<String, Object>> rowHeightList = new ArrayList<>();
    
        /**
         * 创建行高信息
         *
         * @param sheetName sheet页名称
         * @param rowIndex  行号
         * @param rowHeight 行高
         * @return
         */
        public static Map<String, Object> createRowHeightMap(String sheetName, Integer rowIndex, Float rowHeight) {
            return createRowHeightColWidthMap(sheetName, rowIndex, rowHeight, null, null);
        }
    
        /**
         * 创建列宽信息
         *
         * @param sheetName sheet页名称
         * @param colIndex  列号
         * @param colWidth  列宽
         * @return
         */
        public static Map<String, Object> createColWidthMap(String sheetName, Integer colIndex, Integer colWidth) {
            return createRowHeightColWidthMap(sheetName, null, null, colIndex, colWidth);
        }
    
        /**
         * 创建行高列宽信息
         *
         * @param sheetName sheet页名称
         * @param rowIndex  行号
         * @param rowHeight 行高
         * @param colIndex  列号
         * @param colWidth  列宽
         * @return
         */
        public static Map<String, Object> createRowHeightColWidthMap(String sheetName, Integer rowIndex, Float rowHeight, Integer colIndex, Integer colWidth) {
            Map<String, Object> map = new HashMap<>();
            //sheet页名称
            map.put(KEY_SHEET_NAME, sheetName);
            //显示行号
            map.put(KEY_ROW_INDEX, rowIndex);
            //行高
            map.put(KEY_ROW_HEIGHT, rowHeight);
            //显示列号
            map.put(KEY_COL_INDEX, colIndex);
            //列宽
            map.put(KEY_COL_WIDTH, colWidth);
            return map;
        }
    
        /**
         * 自定义行高列宽适配器构造方法
         *
         * @param rowHeightColWidthList 行高列宽信息
         */
        public CustomRowHeightColWidthHandler(List<Map<String, Object>> rowHeightColWidthList) {
            if (rowHeightColWidthList == null || rowHeightColWidthList.size() <= 0) {
                return;
            }
            rowHeightColWidthList = rowHeightColWidthList.stream().filter(x ->
                    //判断sheet名称KEY是否存在
                    x.keySet().contains(KEY_SHEET_NAME) && x.get(KEY_SHEET_NAME) != null
                            && StrUtil.isNotBlank(x.get(KEY_SHEET_NAME).toString())
                            //判断列索引KEY是否存在
                            && x.keySet().contains(KEY_COL_INDEX)
                            //判断行索引KEY是否存在
                            && x.keySet().contains(KEY_ROW_INDEX)
                            //判断行高KEY是否存在
                            && x.keySet().contains(KEY_ROW_HEIGHT)
                            //判断列宽KEY是否存在
                            && x.keySet().contains(KEY_COL_WIDTH)).collect(Collectors.toList());
            //填充行高信息
            this.rowHeightList = rowHeightColWidthList.stream().filter(x ->
                    x.get(KEY_ROW_INDEX) != null && x.get(KEY_ROW_HEIGHT) != null).collect(Collectors.toList());
            //填充列宽信息
            this.colWidthList = rowHeightColWidthList.stream().filter(x ->
                    x.get(KEY_COL_INDEX) != null && x.get(KEY_COL_WIDTH) != null).collect(Collectors.toList());
            //获取sheet页名称
            sheetNameList = this.rowHeightList.stream().map(x -> x.get(KEY_SHEET_NAME).toString()).distinct().collect(Collectors.toList());
            sheetNameList.addAll(this.colWidthList.stream().map(x -> x.get(KEY_SHEET_NAME).toString()).distinct().collect(Collectors.toList()));
            sheetNameList = sheetNameList.stream().distinct().collect(Collectors.toList());
        }
    
        @Override
        public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row
                , Integer relativeRowIndex, Boolean isHead) {
            Sheet sheet = writeSheetHolder.getSheet();
            //不需要设置行高列宽，或者当前sheet页不需要设置行高列宽
            if ((CollectionUtil.isEmpty(rowHeightList) && CollectionUtil.isEmpty(colWidthList))
                    || sheetNameList.contains(sheet.getSheetName()) == false) {
                return;
            }
            //获取当前sheet页当前行的行高信息
            List<Map<String, Object>> sheetRowHeightMapList = rowHeightList.stream().filter(x ->
                    StrUtil.equals(x.get(KEY_SHEET_NAME).toString(), sheet.getSheetName())
                            && (int) x.get(KEY_ROW_INDEX) == relativeRowIndex).collect(Collectors.toList());
            for (Map<String, Object> map : sheetRowHeightMapList) {
                //行号
                Integer rowIndex = (Integer) map.get(KEY_ROW_INDEX);
                //行高
                Float rowHeight = (Float) map.get(KEY_ROW_HEIGHT);
                //设置行高
                if (rowIndex != null && rowHeight != null) {
                    row.setHeightInPoints(rowHeight);
                }
            }
            //获取当前sheet页的列宽信息
            List<Map<String, Object>> sheetColWidthMapList = colWidthList.stream().filter(x ->
                    StrUtil.equals(x.get(KEY_SHEET_NAME).toString(), sheet.getSheetName())).collect(Collectors.toList());
            for (Map<String, Object> map : sheetColWidthMapList) {
                //列号
                Integer colIndex = (Integer) map.get(KEY_COL_INDEX);
                //列宽
                Integer colWidth = (Integer) map.get(KEY_COL_WIDTH);
                //设置列宽
                if (colIndex != null && colWidth != null) {
                    sheet.setColumnWidth(colIndex, colWidth * 256);
                }
            }
            //删除已添加的行高信息
            rowHeightList.removeAll(sheetRowHeightMapList);
            //删除已添加的列宽信息
            colWidthList.removeAll(sheetColWidthMapList);
            //重新获取要添加的sheet页姓名
            sheetNameList = this.rowHeightList.stream().map(x -> x.get(KEY_SHEET_NAME).toString()).distinct().collect(Collectors.toList());
            sheetNameList.addAll(this.colWidthList.stream().map(x -> x.get(KEY_SHEET_NAME).toString()).distinct().collect(Collectors.toList()));
            sheetNameList = sheetNameList.stream().distinct().collect(Collectors.toList());
        }
    }

## 3 调试代码 ##

/**
         * 导出（设置行高列宽、隐藏行和列）
         *
         * @param response
         */
        @GetMapping("/exportRowHeightColWidth")
        public void exportRowHeightColWidth(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[]{"表头17777777777", "表头2", "表头3", "表头4444"})));
                dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头31", "表头2", "表头3", "表头4"})));
                //导出文件
                String fileName = new String("文件名称.xlsx".getBytes(), "UTF-8");
    
                List<Map<String, Object>> rowHeightColWidthList = new ArrayList<>();
                //设置行高
                rowHeightColWidthList.add(CustomRowHeightColWidthHandler.createRowHeightMap("模板", 0, 20f));
                //隐藏行
                rowHeightColWidthList.add(CustomRowHeightColWidthHandler.createRowHeightMap("模板", 2, 0f));
                //设置列宽
                rowHeightColWidthList.add(CustomRowHeightColWidthHandler.createColWidthMap("模板", 0, 20));
                //隐藏列
                rowHeightColWidthList.add(CustomRowHeightColWidthHandler.createColWidthMap("模板", 2, 0));
                response.addHeader("Content-Disposition", "filename=" + fileName);
                //设置类型，扩展名为.xls
                response.setContentType("application/vnd.ms-excel");
                ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                        .registerWriteHandler(new CustomRowHeightColWidthHandler(rowHeightColWidthList)).build();
                WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
                excelWriter.write(dataList, writeSheet);
                //千万别忘记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/20220829/1d59aada4eee47ac9a15d4e5dbcbf2cd.png