SpringBoot导出Excel

SpringBoot Excel导出功能

1. 导入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>

2. 自定义Excel导出注解

   @Target({ElementType.FIELD})
   @Retention(RetentionPolicy.RUNTIME)
   @Documented
   public @interface ExcelColumn {
       /**
       * 导出的字段名称 
       */
       String value() default "";
   
       /**
       * 导出到第几列
       */
       int col() default 0;
       
       /**
       * 导出的表格宽度
       */
       int width() default 0;
   }

3. 新建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 ;
       }

4. 在实体类上添加注解

   @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;
   }

5. 编写接口测试

   @GetMapping("/export")
       public void export(ComplaintInfoDTO complaintInfoDTO, HttpServletResponse response){
           //查询信息列表
           List<ComplaintInfoVO> list = complaintInfoService.selectComplaintInfoList(complaintInfoDTO);
           //调用导出方法设置文件名，要导出的信息
           ExcelUtil.uploadExcelAboutUser(response, "问题信息.xlsx", list, ComplaintInfoVO.class);
       }

   在浏览器上直接访问接口，就可以导出下载