Web中的EasyExcel导出Excel(不创建对象且自定义合并单元格策略)
适用于多张表(只查单表数据就用创建对象那种方法)
Controller
@RequestMapping(value = "/downloadPlanList", method = RequestMethod.GET)@ApiOperation(value = "下载")public void exportExcel(HttpServletResponse response) throws IOException {dyplanService.exportPlanList(response);}
Service
@Overridepublic 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;// 创建excelEasyExcel.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> {@Overridepublic Class supportJavaTypeKey() {return LocalDateTime.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));}@Overridepublic CellData convertToExcelData(LocalDateTime localDateTime, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {return new CellData<>(localDateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));}}
自定义合并单元格策略
参考:EasyExcel导出自定义合并单元格策略.