SpringBoot导出Excel
SpringBoot Excel导出功能
-
导入poi包
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> -
自定义Excel导出注解
@Target({ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface ExcelColumn { /** * 导出的字段名称 */ String value() default ""; /** * 导出到第几列 */ int col() default 0; /** * 导出的表格宽度 */ int width() default 0; } -
新建Excel导出工具类
public class ExcelUtil { /** * 用户信息导出类 * @param response 响应 * @param fileName 文件名 * @param dataList 导出的数据 */ public static <T> void uploadExcelAboutUser(HttpServletResponse response,String fileName,List<T> dataList, Class<T> cls){ //声明输出流 OutputStream os = null; //设置响应头 setResponseHeader(response,fileName); try { Field[] fields = cls.getDeclaredFields(); List<Field> fieldList = Arrays.stream(fields) .filter(field -> { ExcelColumn annotation = field.getAnnotation(ExcelColumn.class); if (annotation != null && annotation.col() > 0) { field.setAccessible(true); return true; } return false; }).sorted(Comparator.comparing(field -> { int col = 0; ExcelColumn annotation = field.getAnnotation(ExcelColumn.class); if (annotation != null) { col = annotation.col(); } return col; })).collect(Collectors.toList()); //获取输出流 os = response.getOutputStream(); //内存中保留1000条数据，以免内存溢出，其余写入硬盘 //SXSSFWorkbook wb = new SXSSFWorkbook(1000); HSSFWorkbook wb = new HSSFWorkbook(); //获取该工作区的第一个sheet Sheet sheet1 = wb.createSheet("sheet1"); CellStyle cellStyle = wb.createCellStyle(); //设置水平居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); //设置垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); Font font = wb.createFont(); cellStyle.setFont(font); cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex()); for (int i = 0; i < dataList.size(); i++) { AtomicInteger ai = new AtomicInteger(); { Row row = sheet1.createRow(ai.getAndIncrement()); AtomicInteger aj = new AtomicInteger(); //写入头部 fieldList.forEach(field -> { ExcelColumn annotation = field.getAnnotation(ExcelColumn.class); String columnName = ""; if (annotation != null) { columnName = annotation.value(); } Cell cell = row.createCell(aj.getAndIncrement()); //CellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); //Font font = wb.createFont(); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(columnName); }); } //设置内容行 if (!CollectionUtils.isEmpty(dataList)) { dataList.forEach(t -> { Row row1 = sheet1.createRow(ai.getAndIncrement()); AtomicInteger aj = new AtomicInteger(); fieldList.forEach(field -> { //Class<?> type = field.getType(); Object value = ""; try { //value = field.get(t); value = t.getClass().getMethod("get" + initStr(field.getName())).invoke(t); } catch (Exception e) { e.printStackTrace(); } Cell cell = row1.createCell(aj.getAndIncrement()); cell.setCellStyle(cellStyle); if (value != null) { cell.setCellValue(value.toString()); } }); }); } for (int x = 0; x < fieldList.size(); x++) { sheet1.autoSizeColumn(x); sheet1.setColumnWidth(x,sheet1.getColumnWidth(x)*17/10); } } //将整理好的excel数据写入流中 wb.write(os); } catch (IOException e) { e.printStackTrace(); } finally { try { // 关闭输出流 if (os != null) { os.close(); } } catch (IOException e) { e.printStackTrace(); } } } /* 设置浏览器下载响应头 */ private static void setResponseHeader(HttpServletResponse response, String fileName) { try { try { fileName = new String(fileName.getBytes(),"ISO8859-1"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } response.setContentType("application/octet-stream;charset=UTF-8"); response.setHeader("Content-Disposition", "attachment;filename="+ fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } private static String initStr(String old){ // 将单词的首字母大写 String str = old.substring(0,1).toUpperCase() + old.substring(1) ; return str ; } -
在实体类上添加注解
@Data public class ComplaintInfo { /** id */ private String id; /** 案件类型 */ private String casetypeId; /** 投诉标题 */ @ExcelColumn(value = "投诉标题",col = 1) private String complaintTitle; /** 状态0未处理 1已处理 */ private String complaintStatus; /** 投诉内容 */ @ExcelColumn(value = "投诉内容",col = 2) private String complaintContent; /** 反馈结果 */ @ExcelColumn(value = "反馈结果",col = 3) private String complaintResult; /** 地址 */ @ExcelColumn(value = "地址",col = 4) private String address; /** 手机号 */ @ExcelColumn(value = "手机号",col = 5) private String phoneNum; /** * 上报时间 */ @ExcelColumn(value = "上报时间",col = 6) private String createTime; /** 处理时间 */ @ExcelColumn(value = "处理时间",col = 7) private String endTime; } -
编写接口测试
@GetMapping("/export") public void export(ComplaintInfoDTO complaintInfoDTO, HttpServletResponse response){ //查询信息列表 List<ComplaintInfoVO> list = complaintInfoService.selectComplaintInfoList(complaintInfoDTO); //调用导出方法设置文件名，要导出的信息 ExcelUtil.uploadExcelAboutUser(response, "问题信息.xlsx", list, ComplaintInfoVO.class); }在浏览器上直接访问接口，就可以导出下载
