Easyexcel用非注解的方式生成复杂表头/多表头/自定义表头的excel

旧城等待， 2024-03-31 16:57 217阅读 0赞

#### 文章目录 ####

*   *  1. 引入依赖
     *  2.创建存储数据的实体类
     *  3.自定义excel的列宽和行高
     *  4.生成excel的表头以及生成excel
     *  5.测试导出结果

### 1. 引入依赖 ###

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.6</version>
            </dependency>

### 2.创建存储数据的实体类 ###

@Data
    public class Detail {
        
        //姓名
        private String name;
    
        //省
        private String address;
    
        //市
        private String phone;
    
        //备注
        private String remark;
    }

### 3.自定义excel的列宽和行高 ###

public class CustomizeColumnWidth extends AbstractColumnWidthStyleStrategy {
        
        //自定义列宽
        @Override
        protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
        
            // 测试为 COLUMN 宽度定制.
            if (isHead && cell.getRowIndex() == 2) {
        
                int columnWidth = cell.getStringCellValue().getBytes().length;
                int cellIndex = cell.getColumnIndex();
                switch (cellIndex) {
        
                    case 0:
                    case 2:
                    case 3:
                        columnWidth = 10;
                        break;
                    case 1:
                        columnWidth = 12;
                        break;
                    case 4:
                        columnWidth = 15;
                        break;
                    case 5:
                        columnWidth = 50;
                        break;
                    default:
                        break;
                }
    
                if (columnWidth > 255) {
        
                    columnWidth = 255;
                }
                writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);
            }
        }
    
        //自定义行高
        @Override
        public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        
            // 设置行高测试
            int rowIndex = row.getRowNum();
            System.out.println("当前行: " + rowIndex);
            short height = 600;
            row.setHeight(height);
        }
    }

### 4.生成excel的表头以及生成excel ###

在controller类中加入以下几个方法

public void exportData(HttpServletResponse response) throws IOException {
        
            List<Demo> list = new ArrayList<>();
            for (int i = 0; i < 10; i++) {
        
                Demo demo = new Demo();
                demo.setName("姓名" + i);
                demo.setProvince("省" + i);
                demo.setCity("市" + i);
                demo.setPhone("电话" + i);
                list.add(demo);
            }
            //设置返回数据的类型
            response.setContentType("application/msexcel");
            //设置返回数据的字符集编码
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("ParamsConfig", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), Demo.class)
            		 .head(getHeader())
            		 .registerWriteHandler(new CustomizeColumnWidth())
                     .registerWriteHandler(getStyleStrategy())
                     .sheet("sheet名称")
                     .doWrite(list);
        }
    	//获取自定义表头:1.表头总共占用几行，就要在定义的每个list中添加几条数据 2.表头总共有几列就
    	//要定义几个list
    private List<List<String>> getHeader() {
        
            String first = "报表";
            List<List<String>> list = new ArrayList<List<String>>();
            List<String> head0 = new ArrayList<String>();
            //注意这里是表头的合并上下两行
            head0.add(first);
            head0.add("姓名");
            head0.add("姓名");
            //注意这里是合并表头的左右两行
            List<String> head1 = new ArrayList<String>();
            head1.add(first);
            head1.add("地址");
            head1.add("省");
            List<String> head2 = new ArrayList<String>();
            head2.add(first);
            head2.add("地址");
            head2.add("市");
            //注意这里是表头的合并上下两行
            List<String> head3 = new ArrayList<String>();
            head3.add(first);
            head3.add("电话");
            head3.add("电话");
    
            list.add(head0);
            list.add(head1);
            list.add(head2);
            list.add(head3);
            return list;
    	}

### 5.测试导出结果 ###

![在这里插入图片描述][db9e0c3e81fb47e8899b17e599f69db6.png]

[如果只导出正常表头的excel，请点击此处链接https://blog.csdn.net/qq\_41774102/article/details/128252059][excel_https_blog.csdn.net_qq_41774102_article_details_128252059]  
**说明：本文章内容为笔者亲测有效，若有不当之处，希望各位大佬不吝赐告，笔者定当虚心接收并改正。**

[db9e0c3e81fb47e8899b17e599f69db6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a0b91a4bb1f24520a844ea05d8898f94.png
[excel_https_blog.csdn.net_qq_41774102_article_details_128252059]: https://blog.csdn.net/qq_41774102/article/details/128252059