EasyExcel学习

一、读入，写出基础入门

@Data
//内容的行高
@ContentRowHeight(num)
//表头的行高
@HeadRowHeight(num)
public class Student {
	//id
	//@ExcelProperty定义sheet的列的头,value是列头的名字，index是第几列，从左向右0开始，
	@ExcelProperty(value = "ID", index = 0)
	private String id;
	//姓名
	@ExcelProperty(value = "姓名", index = 1)
	//设置列宽
	@ColumnWidth(num)
	private String name;
  //性别
	@ExcelProperty(value = "性别", index = 2)
	private String gender;
  //出生日期
	@ExcelProperty(value = "出生日期", index = 3)
	private String birthday;

}

public class ExcelTest {
	//每个Excel文件是一个工作簿
	//每个Sheet是一个工作表
	//基本读功能
  	@Test
  	public void test01() {
    	//获得一个工作簿对象
    	/**
			* 构建一个读的工作簿对象
    	* 要读取的文件路径
    	*	@param pathName 
    	* 文件中每一行数据都要存储到的实体的类型的class
    	*	@param head
    	* 读监听器，读一行内容，都会调用一次该对象的invoke，在invoke可以操作使用要读取到的数据
			* 监听器类得自己定义，继承AnalysisEventListener
    	*	@param readListener
    	ExcelReaderBuilder r = EasyExcel.read("xxx.xlsx", Student.class, new StudentListener())
                          //获得一个工作表对象
													.sheet()
                          //读取工作表中的内容
                        	.doRead()
    }

  	//基本写功能
  	/**
			* 构建一个写的工作簿对象
    	* 要写入的文件路径
    	*	@param pathName 
    	* 封装要写入的数据的实体类型
    	*	@param head
    	* 返回写的工作簿对象
    	*	@return
  	ExcelWriterBuilder w = EasyExcel.write("xxx.xlsx", Student.class)
  	ExcelWriterSheetBuilder sheet = w.sheet();
  	List<Students> students = initData();
  	sheet.doWrite(students);


  	//构造集合对象的方法
  	private static List<Student> initData() {
        List<Student> list = new ArrayList<>();
				Student data = new Studnet();
      	for (int i = 0; i < 10; i++) {
        	data.setName("Zoro" + i);
        	data.setBirthday(new Date());
        	data.setGender("男");
        	list.add(data);
        }
      	return lists;
    }
}

//监听器都得继承AnalysisEventListener<Excel表内的列作为属性的类>
public class StudentListener extends AnalysisEventListener<Student> {
	//读一行内容，都会调用一次invoke，在invoke可以操作使用要读取到的数据
	public void invoke(Student student, AnalysisContext context) {
  	System.out.println("student = " + student);
  }
	//读取完整个文档之后调用的方法
	public void doAfterAllAnalysed(AnalysisContext context) {

  }
}

二、读入，写出进阶小需求

2.1读取xlsx文件，写入服务器

public class StudentListener extends AnalysisEventListener<Student> {
	@Autowired
	StudentService studentService;
	List<Student> list = new ArrayList<>();
	public void invoke(Student student, AnalysisContxt context) {
  	//对读取的内容做一些异常判断
  	if (student == null) {
    	throw new StudentNullException();
		}
  	//没问题进行加入列表
    list.add(student);
  	//每五个一组写入数据库
    if (list.size() % 5 == 0) {
      studentService.read(list);
    	//写入后清空列表
      list.clear();
    }
  }
}

@Controller
@RequestMapping("/student")
public class StudentController {
	@Autowired	
	StudentListener studentListener;
  @RequestMappint("/read")
	public String readExcel(MultipartFile uploadExcel) {
  	try {
    	//每读一行调用listener里的invoke()方法
    	ExcelReaderBuilder readWorkBook = EasyExcel.read(uploadExcel.getInputStream(), Student.class, studentListener);
    	readWorkBook.sheet().doRead();
    	return "success";
    } catch (IOException e) {
    	e.printStackTrace();
    	return "fail";
    }
  }
}

public interface StudentService {
	void read(List<Student> list);
}

@Service
public class StudentServiceImpl implements StudentService {
	public void read(List<Student> list) {
  	for (Student student : list) {
    	System.out.println("student = " + student);
    }
  }
}

2.2导出数据库文件为xlsx文件

public class Test {
	@RequestMapping("write")
	@ResponseBody
	public void writeExcel (HttpServletResponse response) throws IOException {
  	response.setContentType("application/vnd.ms-excel");
  	response.setCharacterEncoding("utf-8");
  	String fileName = URLEncoder.encode("测试", "UTF-8");
  	response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
  	ServletOutputStream outputStram = response.getOutputStream();
  	ExcelWriterBuilder writeWorkBook = EasyExcel.write(outputStream, Student.class);
  	ExcelWriterSheetBuilder sheet = writeWorkBook.sheet();
  	List<Student> students = initData();
  	sheet.doWrite(student);
  }
}

三、填充已有的Excel模板

3.1单组数据填充Excel模板

@Data
public class FillData {
	private String name;
	private int age;
}

public class Test {
	@Test
	public void test() {
  	//准备excel的模板
		String template = "fill_data_template.xlsx";
  	//创建一个工作簿对象
  	ExcelWriterBuilder excelWriterBuilder = EasyExcel.write("Excel填充-单组数据", FillData.class).withTemplate(template);
  	//创建一个工作表对象
		ExcelWriterSheetBuilder sheet = excelWriterBuilder.sheet();
  	//准备数据
  	/* 创建对象方式
		FillData fillData = new FillData();
  	fillData.setName("Zoro");
  	fillData.setAge(22);
  	*/
  	创建Map方式
  	FillData<String, String> fillData = new HashMap<>();
  	fillData.put("name", "Zoro");
  	fillData.put("age", "22");
  	sheet.doFill(fillData);
  }
}

3.2多组数据填充Excel模板

public class Test {
	@Test
	public void test() {
  	//准备excel的模板
		String template = "fill_data_template.xlsx";
  	//创建一个工作簿对象
  	ExcelWriterBuilder excelWriterBuilder = EasyExcel.write("Excel填充-单组数据", FillData.class).withTemplate(template);
  	//创建一个工作表对象
		ExcelWriterSheetBuilder sheet = excelWriterBuilder.sheet();
  	//准备多组数据
  	List<FillData> list = new ArrayList<>();
  	list = initData();
  	//doxxx时会在写或读最后自动关闭流
  	sheet.doFill(list);
  }
}