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

慵懒的被子

已于 2023-03-02 16:59:07 修改

阅读量5.4k

点赞数 11

分类专栏： java 文章标签： java Powered by 金山文档

于 2023-02-18 11:59:51 首次发布

本文链接：https://blog.csdn.net/weixin_42215988/article/details/129097743

版权

java 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章介绍了如何使用Java和Jeecg框架来导出Excel表格，包括简单的用户信息表，以及处理一对多和多sheet的场景。在一对多示例中，展示了如何将用户信息与其成绩信息一起导出，而多sheet示例则演示了如何在同一Excel文件中创建多个工作表，分别展示不同类别的数据（如用户和老师信息）。

摘要由CSDN通过智能技术生成

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

简单表格

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");
}

一对多

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

多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);