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

如需客户端指定excel版本,只需要判断后缀名然后在controller中的.excelType(ExcelTypeEnum.XLS)做指定输出内容格式即可
***(注意表格行高列宽统一设置是在实体类的类名注解上,如果需要对表格进行精细的宽高设置需要删除掉这两个注解,可以在拦截器使用row的方法进行设置)

1. ## 引入依赖

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

2.实体类(注解法)

package com.jpxx.admin.pilebody.service.api.dto;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 {@ExcelIgnoreprivate String id;@ExcelIgnoreprivate String cityid;@ExcelIgnoreprivate 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;@ExcelIgnoreprivate 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;@ExcelIgnoreprivate String residuenum;@ExcelIgnoreprivate String governendtime;@ExcelIgnoreprivate 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;@ExcelIgnoreprivate String qynum;@ExcelIgnore@Dict(dicCode = \"sourcestr\")private String sourcestr;@ExcelIgnoreprivate String createbyname;}

controller

@postMapping(“pilebodystatisticsmonthexport”)
public WebApiResponse<List> pilebodystatisticsmonthexport (HttpServletResponse response,String month) throws IOException {
List 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);

}

4. 拦截器

package com.jpxx.admin.pilebody.web.api;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 {@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic 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(\"联系方式\");}}

生成表格如下: