easyexcel完成复杂表头及标题的导出功能

桃扇骨 2024-02-05 17:32 225阅读 0赞

使用easyexcel完成复杂表头及标题的导出功能(自定义样式及多sheet导出)

如需客户端指定excel版本,只需要判断后缀名然后在controller中的.excelType(ExcelTypeEnum.XLS)做指定输出内容格式即可

\*\*\*(注意表格行高列宽统一设置是在实体类的类名注解上,如果需要对表格进行精细的宽高设置需要删除掉这两个注解,可以在拦截器使用row的方法进行设置)  
1.引入依赖

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

2.实体类(注解法)

import com.alibaba.excel.annotation.ExcelIgnore;
    import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.annotation.write.style.ColumnWidth;
    import com.alibaba.excel.annotation.write.style.ContentRowHeight;
    import com.alibaba.excel.annotation.write.style.HeadRowHeight;
    import com.alibaba.excel.util.StringUtils;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import lombok.experimental.Accessors;
     
     
    @Data
    @NoArgsConstructor
    @Accessors(chain = true)
    @ContentRowHeight(45)
    @HeadRowHeight(50)
    public class PilebodycheckMonthDto {
        
     
        @ExcelIgnore
        private String id;
     
        @ExcelIgnore
        private String cityid;
     
        @ExcelIgnore
        private String districtid;
     
        @ExcelProperty(value = {
        "序号","序号"},index = 0)
        @ColumnWidth(10)
        private String orderNum;
     
        @ExcelProperty(value = {
        "堆体名称","堆体名称"},index = 1)
        @ColumnWidth(15)
        private String name;
     
        @ExcelProperty(value = {
        "具体位置","具体位置"},index = 3)
        @ColumnWidth(30)
        private String address;
     
        @ExcelProperty(value = {
        "占地面积（平方）","占地面积（平方）"},index = 4)
        @ColumnWidth(15)
        private String areastr;
     
        @ExcelProperty(value = {
        "堆体高度（米）","堆体高度（米）"},index = 5)
        @ColumnWidth(10)
        private String heightstr;
     
        @ExcelProperty(value = {
        "建筑垃圾堆存量（万方）","建筑垃圾堆存量（万方）"},index = 6)
        @ColumnWidth(15)
        private String stocknum;
     
        @ExcelIgnore
        @Dict(dicCode = "governway")
        private String governway;
     
        @ExcelProperty(value = {
        "治理方式","治理方式"},index = 7)
        @ColumnWidth(20)
        private String governwayname;
     
        @ExcelProperty(value = {
        "如需外运，计划外运时间","如需外运，计划外运时间"},index = 8)
        @ColumnWidth(15)
        private String outwardtransporttime;
     
        @ExcelProperty(value = {
        "截止目前累计治理量（万方）","截止目前累计治理量（万方）"},index = 13)
        @ColumnWidth(15)
        private String governnum;
     
        @ExcelProperty(value = {
        "治理主体","治理主体"},index = 14)
        @ColumnWidth(15)
        private String governbody;
     
        @ExcelIgnore
        @Dict(dicCode = "typestr")
        private String typestr;
     
        @ExcelProperty(value = {
        "堆体类型","堆体类型"},index = 2)
        @ColumnWidth(15)
        private String typestrname;
     
        @ExcelIgnore
        @Dict(dicCode = "statestr")
        private String statestr;
     
        @ExcelIgnore
        private String districtname;
     
        @ExcelProperty(value = {
        "监管单位","监管单位"},index = 15)
        @ColumnWidth(15)
        private String supervisedepartname;
     
        @ExcelProperty(value = {
        "监管责任人","监管责任人"},index = 16)
        @ColumnWidth(10)
        private String supervisepeoname;
     
        @ExcelProperty(value = {
        "职务","职务"},index = 17)
        @ColumnWidth(10)
        private String supervisepeoposition;
     
        @ExcelProperty(value = {
        "联系方式","联系方式"},index = 18)
        @ColumnWidth(20)
        private String supervisepeophone;
     
        @ExcelIgnore
        private String residuenum;
     
        @ExcelIgnore
        private String governendtime;
     
        @ExcelIgnore
        private String governendyearmonth;
     
        @ExcelProperty(value = {
        "本月治理量（万方）","外运量"},index = 9)
        @ColumnWidth(15)
        private String outwardtransportnum;
     
        @ExcelProperty(value = {
        "本月治理量（万方）","整理地形绿化量"},index = 10)
        @ColumnWidth(15)
        private String afforestnum;
     
        @ExcelProperty(value = {
        "本月治理量（万方）","临时覆盖或绿化量"},index = 11)
        @ColumnWidth(15)
        private String temporarilynum ;
     
        @ExcelProperty(value = {
        "本月治理量（万方）","合计"},index = 12)
        private String goverytotal;
     
        @ExcelIgnore
        private String qynum;
     
        @ExcelIgnore
        @Dict(dicCode = "sourcestr")
        private String sourcestr;
     
        @ExcelIgnore
        private String createbyname;
     
    }

1.  controller

@postMapping("pilebodystatisticsmonthexport")
        public WebApiResponse<List<PilebodycheckMonthDto>> pilebodystatisticsmonthexport (HttpServletResponse response,String month) throws IOException {
         
            List<PilebodycheckMonthDto> pilebodysList = pilebodycheckService.pilebodystatisticsmonth(sysDepartDto, month);
           //设置序号
            for (int i = 1;i <= pilebodysList.size();i++){
        
                pilebodysList.get(i-1).setOrderNum(i+"");
            }
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("存量建筑垃圾堆体治理进度月报表", "UTF-8");
            response.setHeader("Content-disposition",  "attachment;filename=" + fileName + ".xls");
            //内容样式策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //垂直居中,水平居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
            contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
            contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
            contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
            //设置 自动换行
            contentWriteCellStyle.setWrapped(true);
            // 字体策略
            WriteFont contentWriteFont = new WriteFont();
            // 字体大小
            contentWriteFont.setFontHeightInPoints((short) 12);
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            //头策略使用默认
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
     
            //excel如需下载到本地,只需要将response.getOutputStream()换成File即可(注释掉以上response代码)
            EasyExcel.write(response.getOutputStream(), PilebodycheckMonthDto.class)
                    //设置输出excel版本,不设置默认为xlsx
                    .excelType(ExcelTypeEnum.XLS).head(PilebodycheckMonthDto.class)
                    //设置拦截器或自定义样式
                    .registerWriteHandler(new MonthSheetWriteHandler())
                    .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle))
                    .sheet("存量建筑垃圾堆体治理进度月报表")
                    //设置默认样式及写入头信息开始的行数
                    .useDefaultStyle(true).relativeHeadRowIndex(3)
                    //这里的addsumColomn方法是个添加合计的方法,可删除
                    .doWrite(pilebodycheckService.addSumColomn(pilebodysList));
            return new WebApiResponse(200, "生成excel文件成功", null);
     
        }

1.  拦截器

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.*;
    import org.apache.poi.ss.util.CellRangeAddress;
     
    public class MonthSheetWriteHandler implements SheetWriteHandler {
        
        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        
     
        }
     
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        
            Workbook workbook = writeWorkbookHolder.getWorkbook();
            Sheet sheet = workbook.getSheetAt(0);
            Row row1 = sheet.createRow(0);
            row1.setHeight((short) 500);
            Cell cell = row1.createCell(0);
            //设置单元格内容
            cell.setCellValue("附件2");
            //设置标题
            Row row2 = sheet.createRow(1);
            row2.setHeight((short) 800);
            Cell cell1 = row2.createCell(0);
            cell1.setCellValue("存量建筑垃圾堆体治理进度月报表");
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            Font font = workbook.createFont();
            font.setBold(true);
            font.setFontHeight((short) 400);
            cellStyle.setFont(font);
            cell1.setCellStyle(cellStyle);
            sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 17));
            //设置填表日期,填报人,联系方式
            Row row3 = sheet.createRow(2);
            row3.setHeight((short) 500);
            row3.createCell(1).setCellValue("填表日期");
            row3.createCell(11).setCellValue("填表人");
            row3.createCell(15).setCellValue("联系方式");
     
        }
    }

生成表格如下:  
![在这里插入图片描述][ed80bde22def48368fec7fc8ccdc20b7.png]  
倘若要进行多sheet导出,可参考以下导出方式(仅为示例,使用时按现实要求修改参数即可)

response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("测试输出", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
            //内容样式策略
            WriteCellStyle contentWriteCellStyle = ExcelContentStyle.getWriteCellStyle();
            //头策略使用默认
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            try {
        
                ExcelWriter build = EasyExcel.write(response.getOutputStream()).build();
                WriteSheet sheet0 = EasyExcel.writerSheet(0, "一类")
                        .head(LivelihoodStatisticsYiDto.class)
                        .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                        .useDefaultStyle(true)
                        .registerWriteHandler(new LiveSheetWriteHandler())
                        .relativeHeadRowIndex(1)
                        .build();
                build.write(lxones, sheet0);
     
     
                WriteSheet sheet1 = EasyExcel.writerSheet(1, "二类")
                        .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                        .head(LivelihoodStatisticsDto.class)
                        .useDefaultStyle(true)
                        .registerWriteHandler(new LiveTowSheetWriteHandler())
                        .relativeHeadRowIndex(1).build();
                build.write(lxtwo, sheet1);
     
                build.finish();
     
            } catch (IOException e) {
        
                e.printStackTrace();
            }
        }

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

[ed80bde22def48368fec7fc8ccdc20b7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b6a6fa566b164feb9b305fc70c1250f2.png
[41059063b1ed4ce9b8bbdc84ce101dfc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/e31483b12bc04feb9fbbdd8bf19e5567.png