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

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

1 Maven依赖

  1. <!--hutool工具包-->
  2.         <dependency>
  3.             <groupId>cn.hutool</groupId>
  4.             <artifactId>hutool-all</artifactId>
  5.             <version>5.5.1</version>
  6.         </dependency>
  7.         <!--easyexcel文档处理工具-->
  8.         <dependency>
  9.             <groupId>com.alibaba</groupId>
  10.             <artifactId>easyexcel</artifactId>
  11.             <version>2.2.8</version>
  12.         </dependency>

2 CustomRowHeightColWidthHandler

自定义行高列宽处理器。

  1. package com.easyexcel;
  3. import cn.hutool.core.collection.CollectionUtil;
  4. import cn.hutool.core.util.StrUtil;
  5. import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
  6. import com.alibaba.excel.write.metadata.holder.*;
  7. import org.apache.poi.ss.usermodel.*;
  9. import java.util.*;
  10. import java.util.stream.Collectors;
  12. /**
  13.  * 自定义行高列宽处理器
  14.  */
  15. public class CustomRowHeightColWidthHandler extends AbstractRowWriteHandler {
  16.     /**
  17.      * sheet名称KEY
  18.      */
  19.     public static final String KEY_SHEET_NAME = "sheetName";
  20.     /**
  21.      * 行号key
  22.      */
  23.     public static final String KEY_ROW_INDEX = "rowIndex";
  24.     /**
  25.      * 列号key
  26.      */
  27.     public static final String KEY_COL_INDEX = "colIndex";
  28.     /**
  29.      * 行高key
  30.      */
  31.     public static final String KEY_ROW_HEIGHT = "rowHeight";
  32.     /**
  33.      * 列宽key
  34.      */
  35.     public static final String KEY_COL_WIDTH = "colWidth";
  36.     /**
  37.      * sheet页名称列表
  38.      */
  39.     private List<String> sheetNameList;
  40.     /**
  41.      * 列宽信息
  42.      */
  43.     private List<Map<String, Object>> colWidthList = new ArrayList<>();
  45.     /**
  46.      * 行高信息
  47.      */
  48.     private List<Map<String, Object>> rowHeightList = new ArrayList<>();
  50.     /**
  51.      * 创建行高信息
  52.      *
  53.      * @param sheetName sheet页名称
  54.      * @param rowIndex  行号
  55.      * @param rowHeight 行高
  56.      * @return
  57.      */
  58.     public static Map<String, Object> createRowHeightMap(String sheetName, Integer rowIndex, Float rowHeight) {
  59.         return createRowHeightColWidthMap(sheetName, rowIndex, rowHeight, null, null);
  60.     }
  62.     /**
  63.      * 创建列宽信息
  64.      *
  65.      * @param sheetName sheet页名称
  66.      * @param colIndex  列号
  67.      * @param colWidth  列宽
  68.      * @return
  69.      */
  70.     public static Map<String, Object> createColWidthMap(String sheetName, Integer colIndex, Integer colWidth) {
  71.         return createRowHeightColWidthMap(sheetName, null, null, colIndex, colWidth);
  72.     }
  74.     /**
  75.      * 创建行高列宽信息
  76.      *
  77.      * @param sheetName sheet页名称
  78.      * @param rowIndex  行号
  79.      * @param rowHeight 行高
  80.      * @param colIndex  列号
  81.      * @param colWidth  列宽
  82.      * @return
  83.      */
  84.     public static Map<String, Object> createRowHeightColWidthMap(String sheetName, Integer rowIndex, Float rowHeight, Integer colIndex, Integer colWidth) {
  85.         Map<String, Object> map = new HashMap<>();
  86.         //sheet页名称
  87.         map.put(KEY_SHEET_NAME, sheetName);
  88.         //显示行号
  89.         map.put(KEY_ROW_INDEX, rowIndex);
  90.         //行高
  91.         map.put(KEY_ROW_HEIGHT, rowHeight);
  92.         //显示列号
  93.         map.put(KEY_COL_INDEX, colIndex);
  94.         //列宽
  95.         map.put(KEY_COL_WIDTH, colWidth);
  96.         return map;
  97.     }
  99.     /**
  100.      * 自定义行高列宽适配器构造方法
  101.      *
  102.      * @param rowHeightColWidthList 行高列宽信息
  103.      */
  104.     public CustomRowHeightColWidthHandler(List<Map<String, Object>> rowHeightColWidthList) {
  105.         if (rowHeightColWidthList == null || rowHeightColWidthList.size() <= 0) {
  106.             return;
  107.         }
  108.         rowHeightColWidthList = rowHeightColWidthList.stream().filter(x ->
  109.                 //判断sheet名称KEY是否存在
  110.                 x.keySet().contains(KEY_SHEET_NAME) && x.get(KEY_SHEET_NAME) != null
  111.                         && StrUtil.isNotBlank(x.get(KEY_SHEET_NAME).toString())
  112.                         //判断列索引KEY是否存在
  113.                         && x.keySet().contains(KEY_COL_INDEX)
  114.                         //判断行索引KEY是否存在
  115.                         && x.keySet().contains(KEY_ROW_INDEX)
  116.                         //判断行高KEY是否存在
  117.                         && x.keySet().contains(KEY_ROW_HEIGHT)
  118.                         //判断列宽KEY是否存在
  119.                         && x.keySet().contains(KEY_COL_WIDTH)).collect(Collectors.toList());
  120.         //填充行高信息
  121.         this.rowHeightList = rowHeightColWidthList.stream().filter(x ->
  122.                 x.get(KEY_ROW_INDEX) != null && x.get(KEY_ROW_HEIGHT) != null).collect(Collectors.toList());
  123.         //填充列宽信息
  124.         this.colWidthList = rowHeightColWidthList.stream().filter(x ->
  125.                 x.get(KEY_COL_INDEX) != null && x.get(KEY_COL_WIDTH) != null).collect(Collectors.toList());
  126.         //获取sheet页名称
  127.         sheetNameList = this.rowHeightList.stream().map(x -> x.get(KEY_SHEET_NAME).toString()).distinct().collect(Collectors.toList());
  128.         sheetNameList.addAll(this.colWidthList.stream().map(x -> x.get(KEY_SHEET_NAME).toString()).distinct().collect(Collectors.toList()));
  129.         sheetNameList = sheetNameList.stream().distinct().collect(Collectors.toList());
  130.     }
  132.     @Override
  133.     public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row
  134.             , Integer relativeRowIndex, Boolean isHead) {
  135.         Sheet sheet = writeSheetHolder.getSheet();
  136.         //不需要设置行高列宽，或者当前sheet页不需要设置行高列宽
  137.         if ((CollectionUtil.isEmpty(rowHeightList) && CollectionUtil.isEmpty(colWidthList))
  138.                 || sheetNameList.contains(sheet.getSheetName()) == false) {
  139.             return;
  140.         }
  141.         //获取当前sheet页当前行的行高信息
  142.         List<Map<String, Object>> sheetRowHeightMapList = rowHeightList.stream().filter(x ->
  143.                 StrUtil.equals(x.get(KEY_SHEET_NAME).toString(), sheet.getSheetName())
  144.                         && (int) x.get(KEY_ROW_INDEX) == relativeRowIndex).collect(Collectors.toList());
  145.         for (Map<String, Object> map : sheetRowHeightMapList) {
  146.             //行号
  147.             Integer rowIndex = (Integer) map.get(KEY_ROW_INDEX);
  148.             //行高
  149.             Float rowHeight = (Float) map.get(KEY_ROW_HEIGHT);
  150.             //设置行高
  151.             if (rowIndex != null && rowHeight != null) {
  152.                 row.setHeightInPoints(rowHeight);
  153.             }
  154.         }
  155.         //获取当前sheet页的列宽信息
  156.         List<Map<String, Object>> sheetColWidthMapList = colWidthList.stream().filter(x ->
  157.                 StrUtil.equals(x.get(KEY_SHEET_NAME).toString(), sheet.getSheetName())).collect(Collectors.toList());
  158.         for (Map<String, Object> map : sheetColWidthMapList) {
  159.             //列号
  160.             Integer colIndex = (Integer) map.get(KEY_COL_INDEX);
  161.             //列宽
  162.             Integer colWidth = (Integer) map.get(KEY_COL_WIDTH);
  163.             //设置列宽
  164.             if (colIndex != null && colWidth != null) {
  165.                 sheet.setColumnWidth(colIndex, colWidth * 256);
  166.             }
  167.         }
  168.         //删除已添加的行高信息
  169.         rowHeightList.removeAll(sheetRowHeightMapList);
  170.         //删除已添加的列宽信息
  171.         colWidthList.removeAll(sheetColWidthMapList);
  172.         //重新获取要添加的sheet页姓名
  173.         sheetNameList = this.rowHeightList.stream().map(x -> x.get(KEY_SHEET_NAME).toString()).distinct().collect(Collectors.toList());
  174.         sheetNameList.addAll(this.colWidthList.stream().map(x -> x.get(KEY_SHEET_NAME).toString()).distinct().collect(Collectors.toList()));
  175.         sheetNameList = sheetNameList.stream().distinct().collect(Collectors.toList());
  176.     }
  177. }

3 调试代码

  1. /**
  2.      * 导出（设置行高列宽、隐藏行和列）
  3.      *
  4.      * @param response
  5.      */
  6.     @GetMapping("/exportRowHeightColWidth")
  7.     public void exportRowHeightColWidth(HttpServletResponse response) {
  8.         try {
  9.             //生成表格数据
  10.             List<List<Object>> dataList = new ArrayList<>();
  11.             dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头11", "表头2", "表头3", "表头4"})));
  12.             dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头17777777777", "表头2", "表头3", "表头4444"})));
  13.             dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头31", "表头2", "表头3", "表头4"})));
  14.             //导出文件
  15.             String fileName = new String("文件名称.xlsx".getBytes(), "UTF-8");
  17.             List<Map<String, Object>> rowHeightColWidthList = new ArrayList<>();
  18.             //设置行高
  19.             rowHeightColWidthList.add(CustomRowHeightColWidthHandler.createRowHeightMap("模板", 0, 20f));
  20.             //隐藏行
  21.             rowHeightColWidthList.add(CustomRowHeightColWidthHandler.createRowHeightMap("模板", 2, 0f));
  22.             //设置列宽
  23.             rowHeightColWidthList.add(CustomRowHeightColWidthHandler.createColWidthMap("模板", 0, 20));
  24.             //隐藏列
  25.             rowHeightColWidthList.add(CustomRowHeightColWidthHandler.createColWidthMap("模板", 2, 0));
  26.             response.addHeader("Content-Disposition", "filename=" + fileName);
  27.             //设置类型，扩展名为.xls
  28.             response.setContentType("application/vnd.ms-excel");
  29.             ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
  30.                     .registerWriteHandler(new CustomRowHeightColWidthHandler(rowHeightColWidthList)).build();
  31.             WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
  32.             excelWriter.write(dataList, writeSheet);
  33.             //千万别忘记finish 会帮忙关闭流
  34.             excelWriter.finish();
  35.         } catch (Exception e) {
  36.             e.printStackTrace();
  37.         }
  38.     }

4 调试结果

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70

发表评论

表情：
评论列表 （有 0 条评论，929人围观）

还没有评论，来说两句吧...

相关阅读