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

使用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("联系方式");
 
    }
}

生成表格如下:
在这里插入图片描述
倘若要进行多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();
        }
    }

在这里插入图片描述

  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
对于复杂表头导出EasyExcel是一个非常方便的Java库。你可以按照以下步骤进行操作: 1. 导入EasyExcel依赖:在你的项目中,添加EasyExcel的Maven或Gradle依赖。 2. 创建表头样式:使用EasyExcel提供的样式类,如`com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy`,可根据列宽自动调整。你还可以自定义样式,如字体、颜色等。 3. 定义数据模型:创建一个Java类,用于表示导出数据的模型。每个字段对应一列数据。 4. 准备数据:从数据库或其他数据源获取要导出数据,并将其封装到定义好的数据模型中。 5. 创建导出任务:使用EasyExcel提供的`com.alibaba.excel.EasyExcel.write()`方法创建一个导出任务。传入要导出数据集合、要导出数据模型类以及导出文件的路径。 6. 设置表头:使用EasyExcel提供的`com.alibaba.excel.write.metadata.style.WriteCellStyle`类,可以设置表头的样式。 7. 执行导出:调用导出任务的`sheet()`方法,设置表格名称和表头行数。然后调用`doWrite()`方法执行导出导出的过程是逐行写入,并自动处理分页和大数据导出。 下面是一个示例代码片段,演示了如何使用EasyExcel导出复杂表头: ``` // 导出数据 List<YourDataModel> dataList = getDataFromDataSource(); // 创建导出任务 String exportFilePath = "path/to/export/file.xlsx"; ExcelWriter excelWriter = EasyExcel.write(exportFilePath, YourDataModel.class).build(); // 设置表头样式 WriteCellStyle headStyle = new WriteCellStyle(); // 设置样式属性,如字体、颜色等 // 执行导出 excelWriter.sheet().head(headStyle).doWrite(dataList); // 关闭资源 excelWriter.finish(); ``` 这样,你就可以使用EasyExcel实现复杂表头导出了。希望对你有帮助!如有更多问题,请继续提问。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

princeAladdin

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值