easypoi 学习和遇到的问题

基本是看着官方文档做的：easypoi官方文档

先引入jar包 

        <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>
        <--fastjson非必须，为了输出对象信息-->
        <dependency>
            <groupId>com.alibaba</groupId>
		    <artifactId>fastjson</artifactId>
		    <version>1.2.54</version>
	    </dependency>

 

贴上代码，注释挺详细了，复制粘贴就能用：

Course对象： 

package com.easyPoi.entity;

import java.util.List;

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;
@ExcelTarget(value = "course")
public class Course {
	private String id;
	/**
	 * isImportField（重点字段，为空则不导入）isImportField = "true"只在读取excel时传入的那个pojoClass中生效
	 * ExcelImportUtil.importExcel(new File("f:/11111.xls"), Course.class, params);
	 */
	//课程名称
	@Excel(name = "课程名称",needMerge = true,isImportField = "true")
	private String c_name;
	@Excel(name = "课程学分",type = 10,needMerge = true)
	private Integer c_grade;
	/** 教师对象 */
    @ExcelEntity(name = "教师信息",id = "teacher")
    //@ExcelEntity(name = "教师信息",id = "teacher",show = true ) 如果设置show = true则会多出一行标题“教师信息”，这样在导入时应注意给标题行数加1
    private Teacher teacher;
    
    @ExcelCollection(name ="学生信息")
    List<Student> students;
	
	public Course() {
		// TODO Auto-generated constructor stub
	}

	public String getC_name() {
		return c_name;
	}

	public void setC_name(String c_name) {
		this.c_name = c_name;
	}

	public Integer getC_grade() {
		return c_grade;
	}

	public void setC_grade(Integer c_grade) {
		this.c_grade = c_grade;
	}

	public Teacher getTeacher() {
		return teacher;
	}

	public void setTeacher(Teacher teacher) {
		this.teacher = teacher;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public List<Student> getStudents() {
		return students;
	}

	public void setStudents(List<Student> students) {
		this.students = students;
	}

}

 Student对象：

package com.easyPoi.entity;


import java.util.Date;

import cn.afterturn.easypoi.excel.annotation.Excel;
public class Student {
	private String id;
	//needMerge -- 是否需要纵向合并单元格(用于含有list中,合并list创建的多个row)
	@Excel(name = "姓名",needMerge = true,isImportField = "true")
	private String name;
	//导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本
	@Excel(name = "年龄",type=10,needMerge = true)
	private Integer age;
	//导出时间设置,如果字段是Date类型则不需要设置 数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出
	@Excel(name = "生日", format = "yyyy-MM-dd",needMerge = true)
	private Date birthday;
	//测试合计信息
	@Excel(name = "资产",type=10,isStatistics = true,needMerge = true)
	private Integer money;

	/**
	 * type =2 该字段类型为图片,imageType=1 (默认可以不填),表示从file读取,字段类型是个字符串类型 可以用相对路径也可以用绝对路径,绝对路径优先依次获取
	 * 
	 *读取excel 默认保存的路径为(项目硬盘根目录下)excel/upload/img/类名/属性名+随机数？.* 保存名称
	 *savePath="" 修改保存的路径
	 */
	@Excel(name = "照片", type = 2 ,width = 20 ,imageType = 1,needMerge = true,savePath = "F:/excel")
	private String pic;
	
	public Student() {
		// TODO Auto-generated constructor stub
	}

	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 Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public Integer getMoney() {
		return money;
	}

	public void setMoney(Integer money) {
		this.money = money;
	}

	public String getPic() {
		return pic;
	}

	public void setPic(String pic) {
		this.pic = pic;
	}


}

 Teacher对象

package com.easyPoi.entity;

import java.util.List;

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;
@ExcelTarget(value = "course")
public class Course {
	private String id;
	/**
	 * isImportField（重点字段，为空则不导入）isImportField = "true"只在读取excel时传入的那个pojoClass中生效
	 * ExcelImportUtil.importExcel(new File("f:/11111.xls"), Course.class, params);
	 */
	//课程名称
	@Excel(name = "课程名称",needMerge = true,isImportField = "true")
	private String c_name;
	@Excel(name = "课程学分",type = 10,needMerge = true)
	private Integer c_grade;

	/**
	 * @ExcelEntity(name = "教师信息",id = "teacher",show = true ) 
	 * 如果设置show = true则会多出一行标题“教师信息”，这样在导入时应注意给标题行数加1
	 */
	/** 教师对象 */
	@ExcelEntity(name = "教师信息",id = "teacher")
	private Teacher teacher;

	@ExcelCollection(name ="学生信息")
	List<Student> students;

	public Course() {
		// TODO Auto-generated constructor stub
	}

	public String getC_name() {
		return c_name;
	}

	public void setC_name(String c_name) {
		this.c_name = c_name;
	}

	public Integer getC_grade() {
		return c_grade;
	}

	public void setC_grade(Integer c_grade) {
		this.c_grade = c_grade;
	}

	public Teacher getTeacher() {
		return teacher;
	}

	public void setTeacher(Teacher teacher) {
		this.teacher = teacher;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public List<Student> getStudents() {
		return students;
	}

	public void setStudents(List<Student> students) {
		this.students = students;
	}

}

导出方法：

package com.easyPoi.excel;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import org.apache.poi.ss.usermodel.Workbook;

import com.easyPoi.entity.Course;
import com.easyPoi.entity.Student;
import com.easyPoi.entity.Teacher;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;

public class ExportExcel {
	public static void main(String[] args) throws Exception {
		long start = new Date().getTime();
		List<Student> students = new ArrayList<Student>();
		
		List<Course> courses = new ArrayList<Course>();
		
		Teacher t1 = new Teacher();
		t1.setId(UUID.randomUUID().toString());
		t1.setT_name("张老师");
		t1.setT_gender("1");
		t1.setT_intro("教师，以教书为生的职业。这个职业是人类社会最古老的职业之一。按照法律法规和行业规范，在规定的时间节点内，根据学校设施条件和个人职称专业，安排学生入座、发放学习资料、备课授课、批改作业、引导辅导帮助学生学习、组织听课练习，组织考试、传授科学文化基本知识，开展主持学术交流、提高学生的...");
		Teacher t2 = new Teacher();
		t2.setId(UUID.randomUUID().toString());
		t2.setT_name("李老师");
		t2.setT_intro("教师，以教书为生的职业。这个职业是人类社会最古老的职业之一。按照法律法规和行业规范，在规定的时间节点内，根据学校设施条件和个人职称专业，安排学生入座、发放学习资料、备课授课、批改作业、引导辅导帮助学生学习、组织听课练习，组织考试、传授科学文化基本知识，开展主持学术交流、提高学生的...");
		t2.setT_gender("2");
		
		Course course1 = new Course();
		course1.setC_name("语文");
		course1.setC_grade(5);
		course1.setTeacher(t1);
		
		Course course2 = new Course();
		course2.setC_name("数学");
		course2.setC_grade(7);
		course2.setTeacher(t2);

		for (int i = 0; i < 5; i++) {
			Student stu = new Student();
			stu.setName("zhangsan");
			stu.setAge(10);
			stu.setBirthday(new Date());
			stu.setMoney(10);
			stu.setPic("f:/pic.jpg");
			students.add(stu);
		}
		course1.setStudents(students);
		course2.setStudents(students);
		
		courses.add(course1);
		courses.add(course2);
		
		// =========easypoi部分
		ExportParams exportParams = new ExportParams();
		exportParams.setTitle("表格名称");//增加首行
		exportParams.setSecondTitle("第二行名称");//增加第二行
		exportParams.setSheetName("页签名称");
		exportParams.setHeight((short) 50);
//		exportParams.setAddIndex(true);//开启序号，出错~
//		exportParams.setIndexName("序号");
		//exportParams.setFreezeCol(2);//锁定列（从第一列开始），传入的是真实的列数，非列序号
		//exportParams.setMaxNum(2);//最大行数，包括属性名行
		Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Course.class, courses);
		
		String path = "F:/11111.xls";
		// 测试文件保存位置
		FileOutputStream out = new FileOutputStream(path);
		workbook.write(out);
		out.flush();
		out.close();
		
		System.out.println("导出用时---------------->"+(new Date().getTime() - start));
		
	}
}

导入的方法：

package com.easyPoi.excel;

import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.alibaba.fastjson.JSON;
import com.easyPoi.entity.Course;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;

public class ImportExcel {

	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		ImportParams params = new ImportParams();
		//标题行数，默认0
        params.setTitleRows(2);
        //表头行数，默认1
        params.setHeadRows(2);
        //字段真正值和列标题之间的距离 默认0
        //params.setStartRows(2);
        //主键设置,如果这个cell没有值,就跳过--设置的值为列序号
        //params.setKeyIndex(0);
        //最后的无效行数,不读的行数--可以用户忽略合计行等信息
        params.setLastOfInvalidRow(1);
        long start = new Date().getTime();
        
        List<Course> courses = new ArrayList<Course>();
        
        courses = ExcelImportUtil.importExcel(new File("f:/11111.xls"), Course.class, params);
        
        //InputStream stream = new FileInputStream(new File("f:/11111.xls"));
        //返回ExcelImportResult则需要含有验证信息？
       // ExcelImportResult<Course> result = ExcelImportUtil.importExcelMore(stream, Course.class, params);
        
        System.out.println("读取用时---------------->"+(new Date().getTime() - start));
        System.out.println("行数-------------->"+courses.size());
        
        for (int i = 0; i < courses.size(); i++) {
			System.err.println("course------------->"+JSON.toJSONString(courses.get(i)));
		}
        
        
	}

}

待考证的问题：

1.当我student表中有course集合对象，course对象中有teacher对象时导出没问题，导入时读取不了teacher对象信息，需要进一步测试；

2. 调用下面返回ExcelImportResult的读取方法时报错（貌似读取错行）。
    ExcelImportResult<Course> result = ExcelImportUtil.importExcelMore(stream, Course.class, params);