使用EasyExcel自定义导出表格

一、介绍

作者：fyupeng
技术专栏：☞ https://github.com/fyupeng
分布式博客项目地址：☞ https://github.com/fyupeng/distributed-blog-system-api

---

留给读者

工作中难免会遇到数据导出的问题，最简单的解决方案就是引入阿里的EasyExcel来解决。

二、代码

• 实体类

  @Data
  public class Person {
  
  
      @ExcelProperty(value = "序号", index = 0)
      private String seqNumber;
  
      @ExcelProperty(value = "名称", index = 1)
      private String name;
  
      @ExcelProperty(value = "爱好", index = 2)
      private String hobby;
  
  }

• 实现代码

  private final static DateTimeFormatter DATE_TIME_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd");
  
  
     public static void export(HttpServletRequest request, HttpServletResponse response) throws IOException {
  
        Person person = new Person();
        person.setName("小明");
        person.setHobby("苹果");
        List<Person> list = new ArrayList<>();
        list.add(person);
  
        String fileName = "人.xlsx";
        
        SXSSFWorkbook sheets = exportExcel(list);
        response.setContentType("application/octet-stream;charset=UTF8");
        response.setHeader("Content-Type", "application/vnd.ms-excel");
        fileName = URLEncoder.encode(fileName, "UTF-8") + DATE_TIME_FORMAT.format(LocalDate.now());
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        response.addHeader("Cache-Control", "no-cache");
        OutputStream out = response.getOutputStream();
        sheets.write(out);
        out.flush();
        out.close();
  
     }
  
     private static SXSSFWorkbook exportExcel(List<Person> moduleList) {
        // 标题
        String[] title = {"序号", "名称", "爱好"};
        // 创建一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        // 创建一个工作表 sheet
        SXSSFSheet sheet1 = workbook.createSheet("Sheet1");
        // 创建第二行
        Row row = sheet1.createRow(1);
        // 创建单元格
        Cell cell = null;
        // 创建表头
        for (int i = 0; i < title.length; i++) {
           cell = row.createCell(i);
           // 设置样式
           CellStyle cellStype = workbook.createCellStyle();
           cellStype.setAlignment(HorizontalAlignment.CENTER); // 设置字体居中
           // 设置字体
           Font font = workbook.createFont();
           font.setFontHeightInPoints((short) 13);
           cellStype.setFont(font);
           cell.setCellStyle(cellStype);
           cell.setCellValue(title[i]);
        }
        // 从第三行开始追加数据
        for (int i = 2; i < (moduleList.size() + 2); i++) {
           // 创建第i行
           Row nextRow = sheet1.createRow(i);
           for (int j = 0; j < 3; j++) {
              Person person = moduleList.get(i - 2);
              Cell cell2 = nextRow.createCell(j);
              if (j == 0) {
                 cell2.setCellValue(String.valueOf(i - 1));
              } else if (j == 1) {
                 cell2.setCellValue(person.getName());
              } else if (j == 2) {
                 cell2.setCellValue(person.getHobby());
              }
           }
        }
        return workbook;
     }

• 展示效果
  

三、总结

生活总能给你带来惊喜，工作中总结未尝不是？