java使用easyexcel实现Excel下载

java使用easyexcel实现Excel下载

1. pom添加依赖

   <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>easyexcel</artifactId>
       <version>2.2.10</version>
   </dependency>
   

2. 简单场景下载

   public File generateFile(Map<String, List<Data>> dataListMap, String filename) {
       ExcelWriter writer = EasyExcelFactory.write(filename).build();
       dataListMap.forEach((sheetName, record) -> {
           WriteSheet writeSheet = EasyExcelFactory.writerSheet(sheetName).head(Data.class).build();
           writer.write(record, writeSheet);
       });
       writer.finish();
       return new File(filename);
   }
   

   public class Data {
   
       @JsonProperty
       @ExcelProperty(index = 0, value = "ID")
       public int id;
   
       @JsonProperty
       @ExcelProperty(index = 1, value = "姓名")
       public String name;
   
       @JsonProperty
       @ExcelProperty(index = 2, value = "年龄")
       public Integer age;
   
       // get set
       ...
   }
   

   public static void main(String[] args) {
       String filename = "导出文件.xlsx";
       Map<String, List<Data>> dataListMap = new HashMap<>();
       dataListMap.put("一组", mockDataList());
       dataListMap.put("二组", mockDataList());
       ExcelUtil excelUtil = new ExcelUtil();
       File file = excelUtil.generateFile(dataListMap, filename);
       Resource resource = new FileSystemResource(file);
   }
   
   private static List<Data> mockDataList() {
       ...
   }
   

   

3. 多层数据结构下载

   public File generateFile(Map<String, List<Data>> dataListMap, String filename) {
       ExcelWriter writer = EasyExcelFactory.write(filename).build();
       dataListMap.forEach((sheetName, record) -> {
           WriteSheet writeSheet = EasyExcelFactory.writerSheet(sheetName).needHead(Boolean.FALSE).build();
           for (int i = 0; i < record.size(); i++) {
               Data data = record.get(i);
               WriteTable table1 = EasyExcelFactory.writerTable(i * 2)
                       .needHead(Boolean.TRUE)
                       .head(Data.class)
                       .build();
               writer.write(Collections.singletonList(data), writeSheet, table1);
   
               if (CollectionUtils.isEmpty(data.getInnerDataList())) {
                   WriteTable table2 = EasyExcelFactory.writerTable(i * 2 + 1)
                           .needHead(Boolean.FALSE)
                           .build();
                   writer.write(data.getInnerDataList(), writeSheet, table2);
               } else {
                   WriteTable table2 = EasyExcelFactory.writerTable(i * 2 + 1)
                           .needHead(Boolean.TRUE)
                           .head(Data.InnerData.class)
                           .build();
                   writer.write(data.getInnerDataList(), writeSheet, table2);
               }
           }
       });
       writer.finish();
       return new File(filename);
   }
   

   public class Data {
   
       @JsonProperty
       @ExcelProperty(index = 0, value = "ID")
       public int id;
   
       @JsonProperty
       @ExcelProperty(index = 1, value = "姓名")
       public String name;
   
       @JsonProperty
       @ExcelProperty(index = 2, value = "年龄")
       public Integer age;
   
       @ExcelIgnore
       public List<InnerData> innerDataList;
   
       // get set
       ...
   
       public static class InnerData {
   
           @JsonProperty
           @ExcelProperty(index = 1, value = "ID")
           public int id;
   
           @JsonProperty
           @ExcelProperty(index = 2, value = "日期")
           public String date;
   
           @JsonProperty
           @ExcelProperty(index = 3, value = "出单量")
           public Integer count;
   
           // get set
           ...
       }
   }
   

   public static void main(String[] args) {
       String filename = "导出文件.xlsx";
       Map<String, List<Data>> dataListMap = new HashMap<>();
       dataListMap.put("一组", mockDataList());
       dataListMap.put("二组", mockDataList());
       ExcelUtil excelUtil = new ExcelUtil();
       File file = excelUtil.generateFile(dataListMap, filename);
       Resource resource = new FileSystemResource(file);
   }
   
   private static List<Data> mockDataList() {
       ...
   }