需求背景
项目框架导出Excel功能使用easyExcel，需要导出的Excel样式较为复杂，只使用注解样式实现不了，因此考虑到了easyExcel提供的“拦截器”。
需求：
导出Excel，多个sheet页，每个sheet页数据格式相同，内容不同，表头上方增加一行特殊信息，每个sheet页工点断面信息相同，测点不同。
解决思路：
1、 使用注解方式定义样式，表头和内容
2、 多个sheet导出（不明白看这一篇 easyExcel导出单个文件多个sheet页-注解方式 - 我想养只狗 - 博客园）
3、设置写入内容从第二行开始写入
4、添加拦截器，在表写入内容后，获取到第一行，合并单元格，添加内容并设置样式。
自定义样式或内容，直接上代码
pom.xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
导出Excel实体类
@Data
@HeadRowHeight(42)
//@ContentRowHeight(14)
@HeadStyle(fillBackgroundColor= 55, horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@HeadFontStyle(fontName = "宋体", fontHeightInPoints = 9, bold = BooleanEnum.TRUE)
@ContentFontStyle(fontName = "宋体", fontHeightInPoints = 8, bold = BooleanEnum.FALSE)
public class RiskSubResultExcel {
@ExcelProperty("期数")
@ColumnWidth(4)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private Integer qici;
@ExcelProperty("有效期数")
@ColumnWidth(4)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String yxq; //有效期次
@ExcelProperty("填土高度(m)")
@ColumnWidth(6)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private Double PRELOADH;
@ExcelProperty("观测阶段")
@ColumnWidth(26)
@ContentStyle(borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String workinfoname;
@ExcelProperty("观测时间")
@ColumnWidth(15)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String MTIME_W;
@ExcelProperty("成果值(m)")
@ColumnWidth(8)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String MAVALUE;
@ExcelProperty("修正量")
@ColumnWidth(7)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String CVALUE;
@ExcelProperty("测点状态")
@ColumnWidth(8)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String PSTATE ;
@ExcelProperty("相隔天数")
@ColumnWidth(4)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private Integer day;
@ExcelProperty("两次观测时间间隔(天)")
@ColumnWidth(8)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private Integer day_jg ;
@ExcelProperty("本次沉降量(mm)")
@ColumnWidth(8)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String mavalue_bc;
@ExcelProperty("累计沉降量(mm)")
@ColumnWidth(8)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String mavalue_lj;
@ExcelProperty("沉降速率")
@ColumnWidth(4)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String mavalueSl;
@ExcelProperty("上传时间")
@ColumnWidth(15)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String createdate;
@ExcelProperty("司镜人员")
@ColumnWidth(6)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String sname;
@ExcelProperty("删除状态")
@ColumnWidth(4)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String useflag;
@ExcelProperty("本次上传备注")
@ColumnWidth(11)
@ContentStyle(borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
private String REMARK;
}
EasyExcel默认样式不美观，需要自定义样式。EasyExcel提供一个类HorizontalCellStyleStrategy，可用来配置一些表头和内容样式。
public static WriteHandler headStyle() {
// 头部样式策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 12);
headWriteFont.setBold(false);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容样式策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setWrapped(true);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置内容边框样式
contentWriteCellStyle.setBorderLeft(THIN);
contentWriteCellStyle.setBorderTop(THIN);
contentWriteCellStyle.setBorderRight(THIN);
contentWriteCellStyle.setBorderBottom(THIN);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
更细粒度控制可以通过实现com.alibaba.excel.write.handler.WriteHandler的四个子接口来自定义样式或者内容。注意：多个Handler如果作用在同一个单元格，后面会覆盖前面的样式。
| 接口 | 方法 | 描述 |
|---|---|---|
| CellWriteHandler单元格处理器 | beforeCellCreate | |
| afterCellCreate | ||
| afterCellDataConverted | 一个单元格数据转换后 | |
| afterCellDispose | 一个单元格所有操作完成后调用这个方法 | |
| RowWriteHandler 行处理器 | beforeRowCreate | |
| afterRowCreate | ||
| afterRowDispose | 一行所有操作完成后调用这个方法 | |
| SheetWriteHandlerSheet处理器 | beforeSheetCreateSheet | |
| afterSheetCreateSheet | ||
| WorkbookWriteHandler 工作簿处理器 | beforeWorkbookCreate | |
| afterWorkbookCreate | ||
| afterWorkbookDispose | 工作簿所有操作完成后调用这个方法 |
自定义拦截器，处理第一行数据
public class CustomWriteHandler implements SheetWriteHandler {
private String site;
private String section;
public CustomWriteHandler(String site,String section) {
this.site = site;
this.section = section;
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
// Sheet sheet = workbook.getSheetAt(0);
Sheet sheet = workbook.getSheet(writeSheetHolder.getSheetName());
Row row1 = sheet.getRow(0);
if(row1 == null) {
row1 = sheet.createRow(0);
}
row1.setHeight((short) 500);//25*20 实际行高*20
Cell cell1 = row1.getCell(0);
if(cell1 == null) {
cell1 = row1.createCell(0);
}
cell1.setCellValue(" 工点："+site+" 断面："+site+" 测点："+sheet.getSheetName());
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setBorderTop(BorderStyle.NONE);
cellStyle.setBorderBottom(BorderStyle.NONE);
cellStyle.setBorderLeft(BorderStyle.NONE);
cellStyle.setBorderRight(BorderStyle.NONE);
Font font = workbook.createFont();
font.setBold(false);
font.setFontHeight((short) 220);//11*20 实际字高*20
cellStyle.setFont(font);
font.setFontName("宋体");
cell1.setCellStyle(cellStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 15));
}
}
导出操作
/**
* 断面数据导出Excel
*
* @param response response
* @param fileName 文件名
* @param sheetNames sheetName列表
* @param sourceMap 原数据Map,key为sheetName,value为list
* @param targetClass 目标对象Class
*/
public static void exportExcel(HttpServletResponse response, String fileName, List<String> sheetNames, Map<String, Object> sourceMap,
Class<?> targetClass, String siteName, String sectionName) throws Exception {
if(StringUtils.isBlank(fileName)){
//当前日期
fileName = DateUtils.format(new Date());
}
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Access-Control-Expose-Headers","Content-Disposition");//不设置该参数前端（vue）接收不到文件名
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), targetClass)
.registerWriteHandler(new CustomWriteHandler(siteName, sectionName))
.relativeHeadRowIndex(1)
.build();
for (int i = 0; i < sheetNames.size(); i++) {
List list = (List) sourceMap.get(sheetNames.get(i));
List targetList = new ArrayList<>(list.size());
for(Object source : list){
Object target = targetClass.newInstance();
BeanUtils.copyProperties(source, target);
targetList.add(target);
}
WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetNames.get(i)).build();
writeSheet.setRelativeHeadRowIndex(1);
excelWriter.write(targetList, writeSheet);
}
excelWriter.finish();
}
完美导出！
补充小知识：
java处理excel表格常用的框架库有: jxl，POI, EasyExcel，EasyPoi, hutool。
- jxl , 韩国人开发的一套解析excel Java库，不支持xlsx。09年后一直没有更新，功能比较少。
- POI，apache提供的一套java解析office工具，包含excel。我之前有一篇博文介绍了一些POI中关于Excel API使用
- EasyExcel，阿里开源的JAVA解析Excel工具，对于POI解析耗内存进行了优化
- EasyPoi，对POI进行封装的一套工具库，简化POI代码编写
- hutool，国人开发的工具库，其中包含excel的读写
最主要还是POI, 后三个都依赖于它。
参考
2、使用easyexcel完成复杂表头及标题的导出功能(自定义样式及多sheet导出)_若依导出复杂表头_深蓝格调_的博客-CSDN博客
浙公网安备 33010602011771号