Web中的EasyExcel导出Excel(不创建对象且自定义合并单元格策略)
适用于多张表(只查单表数据就用创建对象那种方法)
Controller
@RequestMapping(value = "/downloadPlanList", method = RequestMethod.GET)
@ApiOperation(value = "下载")
public void exportExcel(HttpServletResponse response) throws IOException {
dyplanService.exportPlanList(response);
}
Service
@Override
public void exportPlanList(HttpServletResponse response) {
List<List<Object>> planList= getPlanList();
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=demo.xlsx");
//需要合并的列(不需要合并就忽略)
int[] mergeColumeIndex = {0,1,2,5,6,7,8,9,10};
// 从哪一行开始合并
int mergeRowIndex = 0;
// 创建excel
EasyExcel.write(response.getOutputStream())
.head(excelHead())
// 自适应列宽(不需要就忽略)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
// 单元格合并策略(不需要就忽略)
.registerWriteHandler(new LocalCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
// 时间转换
.registerConverter(new LocalDateTimeConverter())
.sheet("模板")
.doWrite(planList);
} catch (Exception e) {
logger.error("下载报表异常:{}", e.getMessage());
throw new RuntimeException("下载报表异常");
}
}
/**
* 组装生成excel需要的字段
* @param nodeId
* @return
*/
public List<List<Object>> getPlanList(String nodeId){
List<Dyplan> dyplanList = dyplanMapper.getDyPlanList(nodeId);
(中间根据具体业务组装数据)
....
return dataList;
}
/**
* 组装excel头部
* @return
*/
private List<List<String>> excelHead() {
List<List<String>> headList = new ArrayList();
headList.add(new ArrayList() {{
add("工作年度");
}});
// 下面这种写法,可以实现复杂的头
headList.add(new ArrayList() {{
add("工作内容");
add("资源类型");
}});
headList.add(new ArrayList() {{
add("工作内容");
add("数量");
}});
(具体业务具体处理)
......
return headList;
}
上面实现的头部效果:
自定义Converter(解决LocalDateTime日期转换的问题)
注:EasyExcel支持Date类型,可以直接导入导出
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
@Override
public Class supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
@Override
public CellData convertToExcelData(LocalDateTime localDateTime, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return new CellData<>(localDateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
}
}
自定义合并单元格策略
参考:EasyExcel导出自定义合并单元格策略.