一、导入easyexcel包
二、读Excel(.xlsx)版大于1000行数据返回List
三、写Excel(.xlsx)版大于1000行数据返回List
1.创建读javaModel映射模型ExcelPropertyIndexModel类继承BaseRowModel
public class ExcelPropertyIndexModel extends BaseRowModel {
@ExcelProperty(value = "姓名" ,index = 0)
private String name;
@ExcelProperty(value = "年龄",index = 1)
private String age;
@ExcelProperty(value = "邮箱",index = 2)
private String email;
@ExcelProperty(value = "地址",index = 3)
private String address;
@ExcelProperty(value = "性别",index = 4)
private String sax;
@ExcelProperty(value = "高度",index = 5)
private String heigh;
@ExcelProperty(value = "备注",index = 6)
private String last;
@Override
public String toString() {
return "ExcelPropertyIndexModel{" +
"name='" + name + '\'' +
", age='" + age + '\'' +
", email='" + email + '\'' +
", address='" + address + '\'' +
", sax='" + sax + '\'' +
", heigh='" + heigh + '\'' +
", last='" + last + '\'' +
public String getName() {
return name;
public void setName(String name) {
this.name = name;
public String getAge() {
return age;
public void setAge(String age) {
this.age = age;
public String getEmail() {
return email;
public void setEmail(String email) {
this.email = email;
public String getAddress() {
return address;
public void setAddress(String address) {
this.address = address;
public String getSax() {
return sax;
public void setSax(String sax) {
this.sax = sax;
public String getHeigh() {
return heigh;
public void setHeigh(String heigh) {
this.heigh = heigh;
public String getLast() {
return last;
public void setLast(String last) {
this.last = last;
2.创建ExcelListener类继承 AnalysisEventListener
public class ExcelListener extends AnalysisEventListener {
Logger logger = Logger.getLogger("ExcelListener");
private List
@Override
public void invoke(Object object, AnalysisContext context) {
logger.info("当前行："+context.getCurrentRowNum());
logger.info(object.toString());//获取当前行的数据
datas.add(object);//数据存储到list，供批量处理，或后续自己业务逻辑处理。
// doSomething(object);//根据自己业务做处理
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//解析结束销毁不用的资源
public List
return datas;
public void setDatas(List
this.datas = datas;
4.创建测试类public class ReadTest {
public void test() {
InputStream inputStream = null;
List
inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("reader.xlsx");
// List
ExcelListener excelListener = new ExcelListener();
Sheet sheet= new Sheet(1, 0, ExcelPropertyIndexModel.class);
ExcelReader excelReader = EasyExcelFactory.readBySax(inputStream,sheet ,excelListener);
} catch (Exception e) {
e.printStackTrace();
}finally {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
5 测试读取数据 6、写操作创建写JavaModel 映射模型MultiLineHeadExcelModel类继承BaseRowModel
public class MultiLineHeadExcelModel extends BaseRowModel {
@ExcelProperty(value = {"测试execl头","姓名"},index = 0)
private String name;
@ExcelProperty(value = {"测试execl头","年龄"},index = 1)
private String age;
@ExcelProperty(value = {"测试execl头","邮箱"},index = 2)
private String email;
@ExcelProperty(value = {"测试execl头","地址"},index = 3)
private String address;
@ExcelProperty(value = {"测试execl头","性别"},index = 4)
private String sax;
@ExcelProperty(value = {"测试execl头","高度"},index = 5)
private String heigh;
@ExcelProperty(value = {"测试execl头","备注"},index = 6)
private String last;
public String getName() {
return name;
public void setName(String name) {
this.name = name;
public String getAge() {
return age;
public void setAge(String age) {
this.age = age;
public String getEmail() {
return email;
public void setEmail(String email) {
this.email = email;
public String getAddress() {
return address;
public void setAddress(String address) {
this.address = address;
public String getSax() {
return sax;
public void setSax(String sax) {
this.sax = sax;
public String getHeigh() {
return heigh;
public void setHeigh(String heigh) {
this.heigh = heigh;
public String getLast() {
return last;
public void setLast(String last) {
this.last = last;
public class WriteTest {
public void write() {
List
OutputStream out = null;
out = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\writeV20072.xlsx");
ExcelWriter writer = EasyExcelFactory.getWriter(out);
Sheet sheet1 = new Sheet(1, 0,MultiLineHeadExcelModel.class,"第一个sheet",null);
sheet1.setAutoWidth(Boolean.TRUE);
sheet1.setTableStyle(getTableStyle1());
writer.write(getModeldatas, sheet1);
writer.merge(2,3,0,0);
writer.finish();
out.close();
} catch (Exception e) {
e.printStackTrace();
private TableStyle getTableStyle1() {
TableStyle tableStyle =new TableStyle();
tableStyle.setTableContentBackGroundColor(IndexedColors.AQUA);
Font tableContentFont=new Font();
tableContentFont.setFontName("宋体");
tableContentFont.setFontHeightInPoints((short) 20);
tableContentFont.setBold(true);
tableStyle.setTableContentFont(tableContentFont);
tableStyle.setTableHeadFont(tableContentFont);
return tableStyle;
private int getRandomNumberInRange(int min, int max) {
Random r = new Random();
return r.ints(min, (max + 1)).findFirst().getAsInt();
public List
List
MultiLineHeadExcelModel mode=null;
for (int i=0;i<10000;i++ ) {
mode=new MultiLineHeadExcelModel();
mode.setName("test"+i);
mode.setAge(String.valueOf(getRandomNumberInRange(20,50)));
mode.setAddress("testAddress");
mode.setEmail("test"+i+"@163.com");
mode.setHeigh(String.valueOf(getRandomNumberInRange(150,186)));
mode.setSax("男");
mode.setLast(String.valueOf(i));
list.add(mode);
return list;
