当前位置：首页 > news >正文

SpringBoot中EasyExcel使用实践总结

news 2024/10/26 18:33:30

【1】导出数据返回ResponseEntity

版本是3.1

@RequestMapping(value = "/export",method =  {RequestMethod.POST, RequestMethod.GET})
public ResponseEntity<byte[]> export(HttpServletRequest request) throws UnsupportedEncodingException {List<FmInfo> allDataList = dmInfoService.list();ByteArrayOutputStream outputStream=new ByteArrayOutputStream();EasyExcel.write(outputStream, FmInfo.class).registerWriteHandler(new CustomCellWriteWidthConfig()).sheet("告警数据").doWrite(allDataList);//添加响应头HttpHeaders headers = new HttpHeaders();headers.add("Content-Disposition", "attachment;filename="+ FileUtils.setFileDownloadHeader(request, "告警数据.xlsx"));headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);HttpStatus statusCode = HttpStatus.OK;return new ResponseEntity<byte[]>(outputStream.toByteArray(), headers, statusCode);
}

【2】导出数据并设置表头字体颜色

版本是3.1

@Override
public void downLoad(HttpServletResponse response) {try {List<FmInfo> allDataList = dmInfoService.list();String fileName = "告警数据.xlsx";response.setCharacterEncoding("UTF-8");response.setHeader(HttpHeaders.CONTENT_TYPE, "application/vnd.ms-excel");response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));//设置导出的头样式HorizontalCellStyleStrategy horizontalCellStyleStrategy = getHorizontalCellStyleStrategy();// 导出数据EasyExcel.write(response.getOutputStream(), FmInfo.class).registerWriteHandler(horizontalCellStyleStrategy).sheet("Sheet1").doWrite(allDataList);} catch (Exception e) {log.error("下载校验结果失败！",e);}
}private static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 背景设置为黄色headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short)15);headWriteCellStyle.setWriteFont(headWriteFont);HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy();horizontalCellStyleStrategy.setHeadWriteCellStyle(headWriteCellStyle);return horizontalCellStyleStrategy;
}

【3】导出时将数据写入两个sheet

版本3.1

@Override
public void downLoad(HttpServletResponse response) {try {List<FmInfo> allDataList1 = dmInfoService.list();List<FmInfo> allDataList2 = dmInfoService.list();String fileName = "告警数据.xlsx";response.setCharacterEncoding("UTF-8");response.setHeader(HttpHeaders.CONTENT_TYPE, "application/vnd.ms-excel");response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));//设置导出的头样式HorizontalCellStyleStrategy horizontalCellStyleStrategy = getHorizontalCellStyleStrategy();ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), FmInfo.class).build();{WriteSheet writeSheet = EasyExcel.writerSheet(0,"sheet1").registerWriteHandler(horizontalCellStyleStrategy).build();excelWriter.write(allDataList1 , writeSheet);WriteSheet writeSheetTwo = EasyExcel.writerSheet(1,"sheet2").registerWriteHandler(horizontalCellStyleStrategy).build();excelWriter.write(allDataList2 , writeSheetTwo);}excelWriter.finish();} catch (Exception e) {log.error("下载校验结果失败！",e);}
}

【4】导出数据时自定义表头列

版本3.1

@Override
public void downLoad( HttpServletResponse response) {try {List<FmInfo> allDataList1 = dmInfoService.list();List<FmInfo> allDataList2 = dmInfoService.list();String fileName = "告警数据.xlsx";response.setCharacterEncoding("UTF-8");response.setHeader(HttpHeaders.CONTENT_TYPE, "application/vnd.ms-excel");response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));//设置导出的头样式HorizontalCellStyleStrategy horizontalCellStyleStrategy = getHorizontalCellStyleStrategy();ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();{// 表头内容WriteSheet writeSheet = EasyExcel.writerSheet(0,"sheet1").registerWriteHandler(horizontalCellStyleStrategy).head(getHeaders("header3")).build();excelWriter.write(allDataList1 , writeSheet);WriteSheet writeSheetTwo = EasyExcel.writerSheet(1,"sheet2").registerWriteHandler(horizontalCellStyleStrategy).head(getHeaders("header4")).build();excelWriter.write(allDataList2 , writeSheetTwo);}excelWriter.finish();} catch (Exception e) {log.error(e.getMessage(), e);throw new CommonException("导出数据异常！");}
}private List<List<String>> getHeaders(String cellType) {List<List<String>> headers = new ArrayList<>();headers.add(Collections.singletonList("header1"));headers.add(Collections.singletonList("header2"));headers.add(Collections.singletonList(cellType));return headers;
}

【5】读取模板写入数据并导出，导出时不采用映射类的title

读取classpath下的模板，写入数据并且导出。导出时采用模板的header，不用映射类的title。

@RequestMapping(value = "/export",method =  {RequestMethod.POST, RequestMethod.GET})
public ResponseEntity<byte[]> export(HttpServletRequest request,@RequestParam(value = "idList",required = false) List<Long> idList) throws Exception {ClassPathResource classPathResource = new ClassPathResource("订单导入模板.xlsx");InputStream inputStream = classPathResource.getInputStream();List<SysOrder> allDataList = orderService.list(new QueryWrapper<SysOrder>().in(idList!=null&&!idList.isEmpty(),"id",idList));//存储导出的数据--这里自己构造List<OrderExportVO> wholeList = new ArrayList<>();ByteArrayOutputStream outputStream=new ByteArrayOutputStream();EasyExcel.write(outputStream,OrderExportVO.class).withTemplate(inputStream).needHead(false)//导出时不写入头部，使用模板的头部.sheet().doWrite(wholeList);//添加响应头HttpHeaders headers = new HttpHeaders();headers.add("Content-Disposition", "attachment;filename="+ FileUtils.setFileDownloadHeader(request, "订单导入模板.xlsx"));headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);//设置响应参数
//        response.setContentType("application/vnd.ms-excel;charset=utf-8");HttpStatus statusCode = HttpStatus.OK;return new ResponseEntity<byte[]>(outputStream.toByteArray(), headers, statusCode);}