Java 通过Jeecg导出excel表格，实现一对多带list 多sheet

Java 通过Jeecg导出excel表格，实现一对多带list 多sheet

1. 简单表格

Controller.java

import org.jeecgframework.poi.excel.view.JeecgEntityExcelView;

  @RequestMapping(value = "/export")
public ModelAndView export(HttpServletRequest request) {
          //用户信息
          List<User> userList =  new ArrayList<>();
          userList.add(new User("张三",11,new Date(),0));
          userList.add(new User("李四",22,new Date(),1));
         // 导出Excel
         ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
         mv.addObject(NormalExcelConstants.FILE_NAME, "用户信息表.xlsx");  //文件名称，但是前端会重新命名
         mv.addObject(NormalExcelConstants.CLASS, User.class); //实体类型
         mv.addObject(NormalExcelConstants.PARAMS, new ExportParams("用户信息表", "第一页")); //标题，sheet名称
         mv.addObject(NormalExcelConstants.DATA_LIST, userList); //list数据

         return mv;
     }

实体User.java

import org.jeecgframework.poi.excel.annotation.Excel;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User  implements Serializable {
    @Excel(name = "用户名", width = 15) //width列宽
    private String userName ;

    @Excel(name = "年龄", width = 15)
    private int age ;

    @Excel(name = "创建时间", width = 20, format = "yyyy-MM-dd HH:mm:ss") //格式化日期
    private Date createTime;

    @Excel(name = "是否未成年", width = 15,dicCode = "yn") //获取字典名称
    private int isUnderAge;
}

如果用workbook也可以

import org.jeecgframework.poi.excel.ExcelExportUtil;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.apache.poi.ss.usermodel.Workbook;


public void getFile{
      ExportParams exportParams = new ExportParams("用户信息表", "第一页");
      Workbook workbook  = ExcelExportUtil.exportExcel(exportParams,User.class,userList);

     FileOutputStream fileOutputStream =  new FileOutputStream("D://TMP/用户信息.xlsx");
     workbook.write(fileOutputStream);
     File excelTmpfile = new File("D://TMP/用户信息.xlsx");
}

---

2. 一对多

Controller.java

     @RequestMapping(value = "/exporttXls")
public ModelAndView exporttXls(HttpServletRequest request) {
          List<User> userList =  new ArrayList<>();
          List<SubjectScore> scoreList1  = new  ArrayList<>();
          scoreList1.add(new SubjectScore("英语",100));
          scoreList1.add(new SubjectScore("数学",90));
          userList.add(new User("张三",11,new Date(),0,scoreList1));
          
         List<SubjectScore> scoreList2 = new  ArrayList<>();
         scoreList2.add(new SubjectScore("英语",20));
         scoreList2.add(new SubjectScore("数学",5));
          userList.add(new User("李四",22,new Date(),1,scoreList2));

         // 导出Excel
         ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
         mv.addObject(NormalExcelConstants.FILE_NAME, "用户信息表.xlsx");
         mv.addObject(NormalExcelConstants.CLASS, User.class);
         mv.addObject(NormalExcelConstants.PARAMS, new ExportParams("用户信息表", "第一页"));
         mv.addObject(NormalExcelConstants.DATA_LIST, userList);

         return mv;
     }

User.java 使用ExcelCollection。但是只能一层，即SubjectScore不能再用ExcelCollection

import org.jeecgframework.poi.excel.annotation.Excel;
import org.jeecgframework.poi.excel.annotation.ExcelCollection;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User  implements Serializable {
    @Excel(name = "用户名", width = 15)
    private String userName ;

    @Excel(name = "年龄", width = 15)
    private int age ;

    @Excel(name = "创建时间", width = 20, format = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;

    @Excel(name = "是否未成年", width = 15,dicCode = "yn")
    private int isUnderAge;

    @ExcelCollection(name = "成绩信息")
    private List<SubjectScore> list;
}

SubjectScore.java

@Data
@NoArgsConstructor
@AllArgsConstructor
public class SubjectScore {
    @Excel(name = "科目", width = 15)
    private String subjectName ;
    @Excel(name = "分数", width = 15)
    private int score ;
}

---

3. 多sheet

Controller.java

     @RequestMapping(value = "/exportXls")
     public ModelAndView exporttXls(HttpServletRequest request) {
          List<User> userList =  new ArrayList<>();
          userList.add(new User("张三",11,new Date(),0));
          userList.add(new User("李四",22,new Date(),1));

         List<Map<String, Object>> list = new ArrayList<>();
            //学生页
         Map<String, Object> map1 = new HashMap<>();
         map1.put(NormalExcelConstants.CLASS, User.class);
         map1.put(NormalExcelConstants.PARAMS, new ExportParams("用户信息表", "第一页"));
         map1.put(NormalExcelConstants.DATA_LIST, userList);
         list.add(map1);
            //老师页
         Map<String, Object> map2 = new HashMap<>();
         map2.put(NormalExcelConstants.CLASS, Teacher.class);
         map2.put(NormalExcelConstants.PARAMS, new ExportParams("老师信息表", "第二页"));
         map2.put(NormalExcelConstants.DATA_LIST, teacherList);
         list.add(map2);

         // 导出Excel
         ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
         mv.addObject(NormalExcelConstants.FILE_NAME, "信息表.xlsx");
         mv.addObject(NormalExcelConstants.MAP_LIST, list);

         return mv;
     }

如果使用workbook

 import org.jeecgframework.poi.excel.ExcelExportUtil;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.apache.poi.ss.usermodel.Workbook;


        List<Map<String, Object>> list = new ArrayList<>();
            //学生页
         Map<String, Object> map1 = new HashMap<>();
         map1.put("title", new ExportParams("用户信息表", "第一页"));
         map1.put(NormalExcelConstants.CLASS, User.class);
         map1.put(NormalExcelConstants.DATA_LIST, userList);
         list.add(map1);
            //老师页
         Map<String, Object> map2 = new HashMap<>();
         map2.put("title", new ExportParams("老师信息表", "第二页"));
         map2.put(NormalExcelConstants.CLASS, Teacher.class);
         map2.put(NormalExcelConstants.DATA_LIST, teacherList);
         list.add(map2);

   Workbook workbook  = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);