devbox
笔触狂放9
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：笔触狂放9 | 2024-03-01 02:27:40

赞

踩

easyexcel隐藏列

目录

1 Maven依赖

2 RowHeightColWidthModel

3 CustomRowHeightColWidthHandler

4 调试代码

5 调试结果

注：

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 RowHeightColWidthModel

行高列宽信息（Builder构建者模式）。

  1. package com.xudongbase.common.easyexcel.model;
  2.  
  3. import lombok.Getter;
  4.  
  5. /**
  6.  * 行高列宽信息（Builder构建者模式）
  7.  *
  8.  * @author xudongmaster
  9.  */
  10. @Getter
  11. public class RowHeightColWidthModel {
  12.     /**
  13.      * sheet名称
  14.      */
  15.     private String sheetName;
  16.     /**
  17.      * 行号
  18.      */
  19.     private Integer rowIndex;
  20.     /**
  21.      * 列号
  22.      */
  23.     private Integer colIndex;
  24.     /**
  25.      * 行高
  26.      */
  27.     private Float rowHeight;
  28.     /**
  29.      * 列宽
  30.      */
  31.     private Integer colWidth;
  32.  
  33.     private RowHeightColWidthModel(Builder builder) {
  34.         this.sheetName = builder.sheetName;
  35.         this.rowIndex = builder.rowIndex;
  36.         this.colIndex = builder.colIndex;
  37.         this.rowHeight = builder.rowHeight;
  38.         this.colWidth = builder.colWidth;
  39.     }
  40.  
  41.     public static class Builder {
  42.  
  43.         /**
  44.          * sheet名称
  45.          */
  46.         private String sheetName;
  47.         /**
  48.          * 行号
  49.          */
  50.         private Integer rowIndex;
  51.         /**
  52.          * 列号
  53.          */
  54.         private Integer colIndex;
  55.         /**
  56.          * 行高
  57.          */
  58.         private Float rowHeight;
  59.         /**
  60.          * 列宽
  61.          */
  62.         private Integer colWidth;
  63.  
  64.  
  65.         public Builder rowIndex(Integer rowIndex) {
  66.             this.rowIndex = rowIndex;
  67.             return this;
  68.         }
  69.  
  70.         public Builder colIndex(Integer colIndex) {
  71.             this.colIndex = colIndex;
  72.             return this;
  73.         }
  74.  
  75.         public Builder rowHeight(Float rowHeight) {
  76.             this.rowHeight = rowHeight;
  77.             return this;
  78.         }
  79.  
  80.         public Builder colWidth(Integer colWidth) {
  81.             this.colWidth = colWidth;
  82.             return this;
  83.         }
  84.  
  85.         public Builder(String sheetName) {
  86.             this.sheetName = sheetName;
  87.         }
  88.  
  89.         public RowHeightColWidthModel build() {
  90.             return new RowHeightColWidthModel(this);
  91.         }
  92.     }
  93.  
  94.     /**
  95.      * 创建隐藏行信息
  96.      *
  97.      * @param sheetName sheet页名称
  98.      * @param rowIndex  行号
  99.      * @return
  100.      */
  101.     public static RowHeightColWidthModel createHideRowModel(String sheetName, Integer rowIndex) {
  102.         return createRowHeightColWidthModel(sheetName, rowIndex, 0f, null, null);
  103.     }
  104.  
  105.     /**
  106.      * 创建隐藏列信息
  107.      *
  108.      * @param sheetName sheet页名称
  109.      * @param colIndex  列号
  110.      * @return
  111.      */
  112.     public static RowHeightColWidthModel createHideColModel(String sheetName, Integer colIndex) {
  113.         return createRowHeightColWidthModel(sheetName, null, null, colIndex, 0);
  114.     }
  115.  
  116.     /**
  117.      * 创建行高信息
  118.      *
  119.      * @param sheetName sheet页名称
  120.      * @param rowIndex  行号
  121.      * @param rowHeight 行高
  122.      * @return
  123.      */
  124.     public static RowHeightColWidthModel createRowHeightModel(String sheetName, Integer rowIndex, Float rowHeight) {
  125.         return createRowHeightColWidthModel(sheetName, rowIndex, rowHeight, null, null);
  126.     }
  127.  
  128.     /**
  129.      * 创建列宽信息
  130.      *
  131.      * @param sheetName sheet页名称
  132.      * @param colIndex  列号
  133.      * @param colWidth  列宽
  134.      * @return
  135.      */
  136.     public static RowHeightColWidthModel createColWidthModel(String sheetName, Integer colIndex, Integer colWidth) {
  137.         return createRowHeightColWidthModel(sheetName, null, null, colIndex, colWidth);
  138.     }
  139.  
  140.     /**
  141.      * 创建行高列宽信息
  142.      *
  143.      * @param sheetName sheet页名称
  144.      * @param rowIndex  行号
  145.      * @param rowHeight 行高
  146.      * @param colIndex  列号
  147.      * @param colWidth  列宽
  148.      * @return
  149.      */
  150.     public static RowHeightColWidthModel createRowHeightColWidthModel(String sheetName, Integer rowIndex, Float rowHeight, Integer colIndex, Integer colWidth) {
  151.         return new RowHeightColWidthModel.Builder(sheetName)
  152.                 //行号
  153.                 .rowIndex(rowIndex)
  154.                 //行高
  155.                 .rowHeight(rowHeight)
  156.                 //显示列号
  157.                 .colIndex(colIndex)
  158.                 //列宽
  159.                 .colWidth(colWidth)
  160.                 .build();
  161.     }
  162. }

3 CustomRowHeightColWidthHandler

自定义行高列宽处理器。

  1. package com.xudongbase.common.easyexcel.handler;
  2.  
  3. import cn.hutool.core.collection.CollUtil;
  4. import cn.hutool.core.collection.CollectionUtil;
  5. import cn.hutool.core.util.StrUtil;
  6. import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
  7. import com.alibaba.excel.write.metadata.holder.*;
  8. import com.xudongbase.common.easyexcel.model.RowHeightColWidthModel;
  9. import com.xudongbase.common.util.EqualsUtil;
  10. import org.apache.poi.ss.usermodel.*;
  11.  
  12. import java.util.*;
  13. import java.util.stream.Collectors;
  14.  
  15. /**
  16.  * 自定义行高列宽处理器
  17.  *
  18.  * @author xudongmaster
  19.  */
  20. public class CustomRowHeightColWidthHandler extends AbstractRowWriteHandler {
  21.  
  22.     /**
  23.      * sheet页名称列表
  24.      */
  25.     private List<String> sheetNameList;
  26.     /**
  27.      * 列宽信息
  28.      */
  29.     private List<RowHeightColWidthModel> colWidthList = new ArrayList<>();
  30.  
  31.     /**
  32.      * 行高信息
  33.      */
  34.     private List<RowHeightColWidthModel> rowHeightList = new ArrayList<>();
  35.  
  36.  
  37.     /**
  38.      * 自定义行高列宽适配器构造方法
  39.      *
  40.      * @param rowHeightColWidthList 行高列宽信息
  41.      */
  42.     public CustomRowHeightColWidthHandler(List<RowHeightColWidthModel> rowHeightColWidthList) {
  43.         if (CollUtil.isEmpty(rowHeightColWidthList)) {
  44.             return;
  45.         }
  46.         rowHeightColWidthList = rowHeightColWidthList.stream().filter(x ->
  47.                 StrUtil.isNotBlank(x.getSheetName())).collect(Collectors.toList());
  48.         //填充行高信息
  49.         this.rowHeightList = rowHeightColWidthList.stream().filter(x ->
  50.                 x.getRowIndex() != null && x.getRowIndex() >= 0 && x.getRowHeight() != null && x.getRowHeight() >= 0).collect(Collectors.toList());
  51.         //填充列宽信息
  52.         this.colWidthList = rowHeightColWidthList.stream().filter(x ->
  53.                 x.getColIndex() != null && x.getColIndex() >= 0 && x.getColWidth() != null && x.getColWidth() >= 0).collect(Collectors.toList());
  54.         //获取sheet页名称
  55.         sheetNameList = this.rowHeightList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());
  56.         sheetNameList.addAll(this.colWidthList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList()));
  57.         sheetNameList = sheetNameList.stream().distinct().collect(Collectors.toList());
  58.     }
  59.  
  60.     @Override
  61.     public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row
  62.             , Integer relativeRowIndex, Boolean isHead) {
  63.         Sheet sheet = writeSheetHolder.getSheet();
  64.         //不需要设置行高列宽，或者当前sheet页不需要设置行高列宽
  65.         if ((CollUtil.isEmpty(rowHeightList) && CollUtil.isEmpty(colWidthList))
  66.                 || sheetNameList.contains(sheet.getSheetName()) == false) {
  67.             return;
  68.         }
  69.         //获取当前sheet页当前行的行高信息
  70.         List<RowHeightColWidthModel> sheetRowHeightList = rowHeightList.stream().filter(x ->
  71.                 StrUtil.equals(x.getSheetName(), sheet.getSheetName())
  72.                         && EqualsUtil.equals(x.getRowIndex(),relativeRowIndex)).collect(Collectors.toList());
  73.         for (RowHeightColWidthModel rowHeightModel : sheetRowHeightList) {
  74.             //行高
  75.             Float rowHeight = rowHeightModel.getRowHeight();
  76.             //设置行高
  77.             if (rowHeight != null) {
  78.                 row.setHeightInPoints(rowHeight);
  79.             }
  80.         }
  81.         //获取当前sheet页的列宽信息
  82.         List<RowHeightColWidthModel> sheetColWidthList = colWidthList.stream().filter(x ->
  83.                 StrUtil.equals(x.getSheetName(), sheet.getSheetName())).collect(Collectors.toList());
  84.         for (RowHeightColWidthModel colWidthModel : sheetColWidthList) {
  85.             //列号
  86.             Integer colIndex = colWidthModel.getColIndex();
  87.             //列宽
  88.             Integer colWidth = colWidthModel.getColWidth();
  89.             //设置列宽
  90.             if (colIndex != null && colWidth != null) {
  91.                 sheet.setColumnWidth(colIndex, colWidth * 256);
  92.             }
  93.         }
  94.         //删除已添加的行高信息
  95.         rowHeightList.removeAll(sheetRowHeightList);
  96.         //删除已添加的列宽信息
  97.         colWidthList.removeAll(sheetColWidthList);
  98.         //重新获取要添加的sheet页姓名
  99.         sheetNameList = this.rowHeightList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());
  100.         sheetNameList.addAll(this.colWidthList.stream().map(x ->  x.getSheetName()).distinct().collect(Collectors.toList()));
  101.         sheetNameList = sheetNameList.stream().distinct().collect(Collectors.toList());
  102.     }
  103. }

4 调试代码

  1.     /**
  2.      * 测试行高列宽、隐藏行和列
  3.      */
  4.     @Test
  5.     public void testRowHeightColWidth() {
  6.         try {
  7.             File file = new File("D:/easyexcel/testRowHeightColWidth.xlsx");
  8.             FileUtil.createNewFile(file);
  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.             List<RowHeightColWidthModel> rowHeightColWidthList = new ArrayList<>();
  16.             String sheetName="模板";
  17.             //设置行高
  18.             rowHeightColWidthList.add(RowHeightColWidthModel.createRowHeightModel(sheetName, 0, 20f));
  19.             //隐藏行
  20.             rowHeightColWidthList.add(RowHeightColWidthModel.createHideRowModel(sheetName, 2));
  21.             //设置列宽
  22.             rowHeightColWidthList.add(RowHeightColWidthModel.createColWidthModel(sheetName, 0, 20));
  23.             //隐藏列
  24.             rowHeightColWidthList.add(RowHeightColWidthModel.createHideColModel("模板", 2));
  25.             FileOutputStream fileOutputStream = new FileOutputStream(file);
  26.             ExcelWriter excelWriter = EasyExcel.write(fileOutputStream)
  27.                     .registerWriteHandler(new CustomRowHeightColWidthHandler(rowHeightColWidthList)).build();
  28.             WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
  29.             excelWriter.write(dataList, writeSheet);
  30.             //千万别忘记finish 会帮忙关闭流
  31.             excelWriter.finish();
  32.         } catch (Exception e) {
  33.             e.printStackTrace();
  34.         }
  35.     }

5 调试结果

注：

如果需要源码请前往Gitee查看。

xudongbase: 主要是项目中可以用到的共通方法https://gitee.com/xudong_master/xudongbase

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/笔触狂放9/article/detail/170327
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号