http://poi.apache.org/
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
eaypoi 是什么?,
Easypoi的目标不是替代poi,而是让一个不懂导入导出的快速使用poi完成Excel和word的各种操作,而不是看很多api才可以完成这样工作
https://easypoi.mydoc.io/#text_202979
需要导入的依赖
<!--poi表格处理依赖-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
2.2 注解
###注解介绍
easypoi起因就是Excel的导入导出,最初的模板是实体和Excel的对应,model–row,filed–col 这样利用注解我们可以和容易做到excel到导入导出
经过一段时间发展,现在注解有5个类分别是
@Excel 作用到filed上面,是对Excel一列的一个描述
@ExcelCollection 表示一个集合,主要针对一对多的导出,比如一个老师对应多个科目,科目就可以用集合表示
@ExcelEntity 表示一个继续深入导出的实体,但他没有太多的实际意义,只是告诉系统这个对象里面同样有导出的字段
@ExcelIgnore 和名字一样表示这个字段被忽略跳过这个导导出
@ExcelTarget 这个是作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理
2.3 使用注解导出
2.3.1对象的定义
StudentEntity .java
package test.testEasyPoi;
import cn.afterturn.easypoi.excel.annotation.Excel;
import java.util.Date;
/**
* @author *cruder
* @version 1.0
* @since 2020/11/15 16:23
*/
public class StudentEntity implements java.io.Serializable {
/**
* id
*/
private String id;
/**
* 学生姓名
*/
@Excel(name = "学生姓名", height = 20, width = 30, isImportField = "true_st")
private String name;
/**
* 学生性别
*/
@Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生", isImportField = "true_st")
private int sex;
@Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true_st", width = 20)
private Date birthday;
@Excel(name = "进校日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd")
private Date registrationDate;
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 int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Date getRegistrationDate() {
return registrationDate;
}
public void setRegistrationDate(Date registrationDate) {
this.registrationDate = registrationDate;
}
public StudentEntity(){}
public StudentEntity(String id,String name,int sex,Date birthday,Date registrationDate){
this.id = id;
this.name = name;
this.sex = sex;
this.birthday =birthday;
this.registrationDate =registrationDate;
}
}
package test.testEasyPoi;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author *cruder
* @version 1.0
* @since 2020/11/15 16:18
*/
public class Test01 {
public static void main(String[] args) throws IOException {
List<StudentEntity> studentEntities = new ArrayList<>();
for (int i = 0; i <3 ; i++) {
studentEntities.add(new StudentEntity(String.valueOf(i),"李得劲"+i,1,new Date(),new Date()));
}
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("计算机一班学生","学生"),
StudentEntity .class, studentEntities);
FileOutputStream fos = new FileOutputStream("D:/ExcelExportHasImgTest.exportCompanyImg.xls");
workbook.write(fos);
fos.close();
}
}
2.3.2 集合定义
给出一个某个班级选择选择某些课的学生以及对应的老师
一个课程对应一个老师
一个课程对应N个学生
TeacherEntity .java
package test.testEasyPoi;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
/**
* @author *cruder
* @version 1.0
* @since 2020/11/15 17:04
*/
@ExcelTarget("teacherEntity")
public class TeacherEntity implements java.io.Serializable {
private String id;
/**
* name
*/
@Excel(name = "主讲老师_major,代课老师_absent", orderNum = "1", needMerge = true,isImportField = "true_major,true_absent")
private String name;
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(String id, String name) {
this.id = id;
this.name = name;
}
}
CourseEntity .java
package test.testEasyPoi;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import java.util.List;
/**
* @author *cruder
* @version 1.0
* @since 2020/11/15 17:03
*/
@ExcelTarget("courseEntity")
public class CourseEntity implements java.io.Serializable {
/** 主键 */
private String id;
/** 课程名称 */
@Excel(name = "课程名称", orderNum = "1", width = 25,needMerge = true)
private String name;
/** 老师主键 */
@ExcelEntity(id = "absent")
private TeacherEntity mathTeacher;
@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 getMathTeacher() {
return mathTeacher;
}
public void setMathTeacher(TeacherEntity mathTeacher) {
this.mathTeacher = mathTeacher;
}
public List<StudentEntity> getStudents() {
return students;
}
public void setStudents(List<StudentEntity> students) {
this.students = students;
}
public CourseEntity(String id, String name, TeacherEntity mathTeacher, List<StudentEntity> students) {
this.id = id;
this.name = name;
this.mathTeacher = mathTeacher;
this.students = students;
}
}
package test.testEasyPoi;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author *cruder
* @version 1.0
* @since 2020/11/15 16:18
*/
public class Test01 {
public static void main(String[] args) throws IOException {
List<StudentEntity> studentEntities = new ArrayList<>();
for (int i = 0; i <3 ; i++) {
studentEntities.add(new StudentEntity(String.valueOf(i),"张同学"+i,1,new Date(),new Date()));
}
List<TeacherEntity> teacherEntities = new ArrayList<>();
for (int i = 0; i <3 ; i++) {
teacherEntities.add(new TeacherEntity(String.valueOf(i),"李老师"+i));
}
List<CourseEntity> courseEntities = new ArrayList<>();
courseEntities.add(new CourseEntity("SCI01","固体物理",teacherEntities.get(1),studentEntities));
courseEntities.add(new CourseEntity("SCI02","模拟电子线路基础",teacherEntities.get(2),studentEntities));
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("计算机一班学生课程表","课程信息"),
CourseEntity .class, courseEntities);
FileOutputStream fos = new FileOutputStream("D:/ExcelExportHasImgTest.exportCompanyImg.xls");
workbook.write(fos);
fos.close();
}
}
注解变种 更自由的导出(列动态变化情况)
/**
* 测试动态列
*/
public static void testDynamicColumn() {
try {
List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
//构造对象等同于@Excel
ExcelExportEntity excelentity = new ExcelExportEntity("姓名", "name");
//是否需要纵向合并单元格(用于含有list中,单个的单元格,合并list创建的多个row)
excelentity.setNeedMerge(true);
entity.add(excelentity);
entity.add(new ExcelExportEntity("性别", "sex"));
excelentity = new ExcelExportEntity(null, "students");
List<ExcelExportEntity> temp = new ArrayList<ExcelExportEntity>();
temp.add(new ExcelExportEntity("姓名", "name"));
temp.add(new ExcelExportEntity("性别", "sex"));
//构造List等同于@ExcelCollection
excelentity.setList(temp);
entity.add(excelentity);
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
//把我们构造好的bean对象放到params就可以了
Map<String, Object> map = new HashMap<>();
map.put("name","小乔");
map.put("sex","女");
//students又是集合
List<Map<String,Object>> studentListMap = new ArrayList<>();
for (int i = 0; i <3 ; i++) {
Map<String,Object> studentMap = new HashMap<>();
studentMap.put("name","孙策");
studentMap.put("sex","男");
studentListMap.add(studentMap);
}
map.put("students",studentListMap);
list.add(map);
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("测试", "测试"), entity,
list);
FileOutputStream fos = new FileOutputStream("D:/000T.XLSX");
workbook.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
测试结果
2.3.3 图片的导出
CompanyHasImgModel .java
package test.testEasyPoi;
import cn.afterturn.easypoi.excel.annotation.Excel;
/**
* @author *cruder
* @version 1.0
* @since 2020/11/15 17:33
*/
public class CompanyHasImgModel {
@Excel(name = "公司名称")
private String companyName;
@Excel(name = "公司LOGO", type = 2 ,width = 20 , height = 15,imageType = 1)
private String companyLogo;
@Excel(name = "公司地址")
private String companyAddress;
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public String getCompanyLogo() {
return companyLogo;
}
public void setCompanyLogo(String companyLogo) {
this.companyLogo = companyLogo;
}
public String getCompanyAddress() {
return companyAddress;
}
public void setCompanyAddress(String companyAddress) {
this.companyAddress = companyAddress;
}
public CompanyHasImgModel(String companyName, String companyLogo, String companyAddress) {
this.companyName = companyName;
this.companyLogo = companyLogo;
this.companyAddress = companyAddress;
}
}
static void test03()throws IOException{
List<CompanyHasImgModel> list = new ArrayList<CompanyHasImgModel>();
list.add(new CompanyHasImgModel("百度", "https://ss0.bdstatic.com/70cFuHSh_Q1YnxGkpoWK1HF6hhy/it/u=1076238794,2051819414&fm=26&gp=0.jpg", "北京市海淀区西北旺东路10号院百度科技园1号楼"));
list.add(new CompanyHasImgModel("阿里巴巴", "https://ss0.bdstatic.com/70cFuHSh_Q1YnxGkpoWK1HF6hhy/it/u=1076238794,2051819414&fm=26&gp=0.jpg", "北京市海淀区西北旺东路10号院百度科技园1号楼"));
list.add(new CompanyHasImgModel("Lemur", "https://ss0.bdstatic.com/70cFuHSh_Q1YnxGkpoWK1HF6hhy/it/u=1076238794,2051819414&fm=26&gp=0.jpg", "亚马逊热带雨林"));
list.add(new CompanyHasImgModel("腾讯", "https://ss0.bdstatic.com/70cFuHSh_Q1YnxGkpoWK1HF6hhy/it/u=1076238794,2051819414&fm=26&gp=0.jpg", "山东济宁俺家"));
File savefile = new File("D:/excel/");
if (!savefile.exists()) {
savefile.mkdirs();
}
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), CompanyHasImgModel.class, list);
FileOutputStream fos = new FileOutputStream("D:/excel/ExcelExportHasImgTest.exportCompanyImg.xls");
workbook.write(fos);
fos.close();
}
2.3.4 Excel导入介绍
public static void test04(){
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
long start = new Date().getTime();
List<CompanyHasImgModel> list = ExcelImportUtil.importExcel(
new File("D:/excel/ExcelExportHasImgTest.exportCompanyImg.xls"),
CompanyHasImgModel.class, params);
System.out.println(new Date().getTime() - start);
System.out.println(list.size());
System.out.println(ReflectionToStringBuilder.toString(list.get(0)));
}
参考http://doc.wupaas.com/docs/easypoi