springboot整合easypoi
官方文档
旧地址:http://easypoi.mydoc.io/
新地址:http://doc.wupaas.com/docs/easypoi/
依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
导出导入工具类
public class FileUtil {
/**
* 导出列表数据为对象类型
* @param list 列表集合
* @param title 主题
* @param sheetName 工作页名称
* @param pojoClass 列表类型
* @param fileName 导出后的文件名
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
//下载到页面
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
System.out.println("导出异常");
}
}
/**
* 导出列表数据为map类型
* @param list
* @param fileName
* @param response
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
defaultExport(list, fileName, response);
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* 数据导入
* @param file 导入文件
* @param titleRows 标题行数
* @param headerRows 列名行数
* @param pojoClass 实例类型
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
if (file == null){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
}catch (NoSuchElementException e){
/*throw new NormalException("excel文件不能为空");*/
} catch (Exception e) {
/*throw new NormalException(e.getMessage());*/
}
return list;
}
}
Excel导出简单对象列表
创建列表实例StudentEntity
@ExcelTarget("studentEntity")
public class StudentEntity implements Serializable {
@Excel(name = "学生编号",orderNum ="0" )
private int id;
@Excel(name = "学生姓名", height = 20, width = 15,orderNum ="1" )
private String name;
@Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生",orderNum ="2")
private int sex;
@Excel(name = "年龄",suffix = "岁",orderNum ="3" )
private int age;
@Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd",width = 20 ,orderNum ="4")
private Date birthday;
@Excel(name = "家庭住址", height = 20, width = 50,orderNum ="5" )
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
导出接口
/**
* excel导出StudentEntity
* @return
*/
@RequestMapping("exportStudentEntity")
public void exportStudentEntity(HttpServletResponse response){
//从数据库获取需要导出的数据
List<StudentEntity> list=new ArrayList<>();
for(int i=0;i<20;i++){
StudentEntity studentEntity = new StudentEntity();
studentEntity.setId(i+1);
studentEntity.setName("zhansan"+i);
if(i%2==0){
studentEntity.setSex(1);
}else{
studentEntity.setSex(2);
}
studentEntity.setAge(20+(i%5));
studentEntity.setBirthday(new Date());
studentEntity.setAddress("随便编辑一个地址吧");
list.add(studentEntity);
}
//导出操作
FileUtil.exportExcel(list,"学生信息详情表","学生信息",StudentEntity.class,"学生信息-"+new Date().getTime()+".xls",response);
}
Excel导出复杂对象列表
课程CourseEntity中包括老师和学生列表
创建实例CourseEntity
@ExcelTarget("courseEntity")
public class CourseEntity implements Serializable {
@Excel(name = "课程编号",orderNum ="0" )
private String id;
/** 课程名称 */
@Excel(name = "课程名称", orderNum = "1", width = 25)
private String name;
/** 老师主键 */
@ExcelEntity(id = "teacherEntity")
private TeacherEntity teacherEntity;
@ExcelCollection(name = "学生列表",orderNum = "4")
private List<StudentEntity> students;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public TeacherEntity getTeacherEntity() {
return teacherEntity;
}
public void setTeacherEntity(TeacherEntity teacherEntity) {
this.teacherEntity = teacherEntity;
}
public List<StudentEntity> getStudents() {
return students;
}
public void setStudents(List<StudentEntity> students) {
this.students = students;
}
}
创建实例TeacherEntity
@ExcelTarget("teacherEntity")
public class TeacherEntity implements Serializable {
/* @Excel(name = "老师编号",orderNum ="0" )*/
private int id;
@Excel(name = "老师姓名", height = 20, width = 15,orderNum ="2" )
private String name;
@Excel(name = "性别", replace = { "男_1", "女_2" }, suffix = "生",orderNum ="3")
private int sex;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
}
创建实例TeacherEntity
@ExcelTarget("studentEntity")
public class StudentEntity implements Serializable {
/*@Excel(name = "学生编号",orderNum ="0" )*/
private int id;
@Excel(name = "学生姓名", height = 20, width = 15,orderNum ="1" )
private String name;
@Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生",orderNum ="2")
private int sex;
/*@Excel(name = "年龄",suffix = "岁",orderNum ="3" )*/
private int age;
/*@Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd",width = 20 ,orderNum ="4")*/
private Date birthday;
/*@Excel(name = "家庭住址", height = 20, width = 50,orderNum ="5" )*/
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
导出接口
/**
* excel导出CourseEntity
* @return
*/
@RequestMapping("exportCourseEntity")
public void exportCourseEntity(HttpServletResponse response){
//从数据库获取需要导出的数据
List<StudentEntity> list1=new ArrayList<>();
for(int i=0;i<7;i++){
StudentEntity studentEntity = new StudentEntity();
studentEntity.setId(i+1);
studentEntity.setName("zhansan"+i);
if(i%2==0){
studentEntity.setSex(1);
}else{
studentEntity.setSex(2);
}
studentEntity.setAge(20+(i%5));
studentEntity.setBirthday(new Date());
studentEntity.setAddress("随便编辑一个地址吧");
list1.add(studentEntity);
}
List<StudentEntity> list2=new ArrayList<>();
for(int i=0;i<8;i++){
StudentEntity studentEntity = new StudentEntity();
studentEntity.setId(i+7);
studentEntity.setName("lisi"+i);
if(i%2==0){
studentEntity.setSex(1);
}else{
studentEntity.setSex(2);
}
studentEntity.setAge(20+(i%5));
studentEntity.setBirthday(new Date());
studentEntity.setAddress("随便编辑一个地址吧");
list2.add(studentEntity);
}
List<CourseEntity> courseEntityList=new ArrayList<>();
CourseEntity courseEntity1 = new CourseEntity();
courseEntity1.setId("1");
courseEntity1.setName("数学");
courseEntity1.setStudents(list1);
TeacherEntity teacherEntity = new TeacherEntity();
teacherEntity.setName("王老师");
teacherEntity.setSex(1);
courseEntity1.setTeacherEntity(teacherEntity);
CourseEntity courseEntity2 = new CourseEntity();
courseEntity2.setId("2");
courseEntity2.setName("数学");
courseEntity2.setStudents(list2);
TeacherEntity teacherEntity2 = new TeacherEntity();
teacherEntity2.setName("马老师");
teacherEntity2.setSex(2);
courseEntity2.setTeacherEntity(teacherEntity);
courseEntityList.add(courseEntity1);
courseEntityList.add(courseEntity2);
//导出操作
FileUtil.exportExcel(courseEntityList,"课程信息详情表","课程信息",CourseEntity.class,"课程信息-"+new Date().getTime()+".xls",response);
}
Excel创建模板导出
导出列表类studentEntity
@ExcelTarget("studentEntity")
public class StudentEntity implements Serializable {
@Excel(name = "学生编号",orderNum ="0" )
private int id;
@Excel(name = "学生姓名", height = 20, width = 15,orderNum ="1" )
private String name;
@Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生",orderNum ="2")
private int sex;
@Excel(name = "年龄",suffix = "岁",orderNum ="3" )
private int age;
@Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd",width = 20 ,orderNum ="4")
private Date birthday;
@Excel(name = "家庭住址", height = 20, width = 50,orderNum ="5" )
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
创建模板
导出接口
@GetMapping("export")
public void export( HttpServletResponse response) throws Exception {
List<StudentEntity> list=new ArrayList<>();
for(int i=0;i<20;i++){
StudentEntity studentEntity = new StudentEntity();
studentEntity.setId(i);
studentEntity.setName("zhansan"+i);
studentEntity.setSex(1);
studentEntity.setAge(20+i);
studentEntity.setBirthday(new Date());
studentEntity.setAddress("随便写个地址");
list.add(studentEntity);
}
Map<String, Object> params=new HashMap<>();
String fileNmae = "学生信息-"+new Date().getTime()+".xlsx";
TemplateExportParams param = new TemplateExportParams("/excel/student.xlsx", true);
Map<String, Object> data = new HashMap<String, Object>();
data.put("title", "学生信息");//导出一个对象
data.put("list", list);//导出list集合
try {
Workbook book = ExcelExportUtil.exportExcel(param, data);
//下载方法
FileUtil.downLoadExcel(fileNmae,response,book);
} catch (Exception e) {
System.out.println("导出模板Excel,失败:" + e);
}
}
访问接口
http://localhost:8080/export
Excel导入
导入的列表实例
@ExcelTarget("studentEntity")
public class StudentEntity implements Serializable {
@Excel(name = "学生编号",orderNum ="0" )
private int id;
@Excel(name = "学生姓名", height = 20, width = 15,orderNum ="1" )
private String name;
@Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生",orderNum ="2")
private int sex;
@Excel(name = "年龄",suffix = "岁",orderNum ="3" )
private int age;
@Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd",width = 20 ,orderNum ="4")
private Date birthday;
@Excel(name = "家庭住址", height = 20, width = 50,orderNum ="5" )
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "StudentEntity{" +
"id=" + id +
", name='" + name + '\'' +
", sex=" + sex +
", age=" + age +
", birthday=" + birthday +
", address='" + address + '\'' +
'}';
}
}
接口
//跳转到EXCEL导入数据页面
@GetMapping("/jumpFileUpload")
public String jumpFileUpload(){
return "/fileUpload";
}
/**
* 导入
*/
@PostMapping("importExcel")
@ResponseBody
public String importExcel(@RequestParam(name = "file") MultipartFile file) throws Exception {
List<StudentEntity> list= FileUtil.importExcel(file,1,1,StudentEntity.class);
for (StudentEntity studentEntity:list) {
System.out.println(studentEntity);
}
return "SUCCESS";
}
页面
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
</head>
<body>
<div style="text-align: center">
导入EXCEL文件
<form th:action="@{/importExcel}" method="post" enctype="multipart/form-data">
<input type="file" name="file"/>
<input type="submit" value="导入数据"/>
</form>
</div>
</body>
</html>
导入模板
导入结果
Word导出
导出模板
导出接口
@RequestMapping("wordExport")
public void wordExport(HttpServletResponse response) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("university", "北京大学");
map.put("studentName", "JueYue");
map.put("AdmissionTime","2021年9月1日上午8点");
map.put("writingDate","2021年8月1日");
try {
XWPFDocument doc = WordExportUtil.exportWord07(
"/excel/通知书.docx", map);
String fileName= "通知书-"+new Date().getTime()+".docx";
downLoadExcel(fileName,response,doc);
} catch (Exception e) {
e.printStackTrace();
}
}
//下载到页面
public static void downLoadExcel(String fileName, HttpServletResponse response, XWPFDocument doc) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
doc.write(response.getOutputStream());
} catch (IOException e) {
System.out.println("导出异常");
}
}
访问http://localhost:8080/importExcelwordExport