为解决大数据量的excel文档导入导出存在的内存泄露问题，可使用EasyExcel实现excel文档的解析和生成。

配置

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>2.2.6</version>
</dependency>

使用

excel导出

代码示例
- Customer.java（vo） ```java package org.polaris.easyexcel.vo;

public class Customer {

private int index;
private String company;
private String type;
private int cash;
private String from;
private String others;
public Customer(int index, String company, String type, int cash, String from, String others) {
    this.index = index;
    this.company = company;
    this.type = type;
    this.cash = cash;
    this.from = from;
    this.others = others;
}
public int getIndex() {
    return index;
}
public void setIndex(int index) {
    this.index = index;
}
public String getCompany() {
    return company;
}
public void setCompany(String company) {
    this.company = company;
}
public String getType() {
    return type;
}
public void setType(String type) {
    this.type = type;
}
public int getCash() {
    return cash;
}
public void setCash(int cash) {
    this.cash = cash;
}
public String getFrom() {
    return from;
}
public void setFrom(String from) {
    this.from = from;
}
public String getOthers() {
    return others;
}
public void setOthers(String others) {
    this.others = others;
}
@Override
public String toString() {
    return "Customer{" +
            "index=" + index +
            ", company='" + company + '\'' +
            ", type='" + type + '\'' +
            ", cash=" + cash +
            ", from='" + from + '\'' +
            ", others='" + others + '\'' +
            '}';
}

}


   - **ExcelExportTest.java（测试类）**
```java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.junit.Test;
import org.polaris.easyexcel.vo.Customer;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
public class ExcelExportTest
{
    @Test
    public void excelExport() throws IOException
    {
        String templateFileName=ExcelExportTest.class.getClassLoader().getResource("template1.xls").getPath();
        BufferedOutputStream bos=new BufferedOutputStream(new FileOutputStream("output"+ File.separator+"template1-out.xls"));
        ExcelWriter excelWriter=EasyExcel.write(bos).withTemplate(templateFileName).build();
        WriteSheet writeSheet=EasyExcel.writerSheet().build();
        List<Customer> customers=new ArrayList<>();
        customers.add(new Customer(1,"广州市越秀区东方红幼儿园","002",1000,"01","测试数据1"));
        customers.add(new Customer(2,"广州市越秀区东山幼儿园","002",2000,"01","测试数据2"));
        customers.add(new Customer(3,"广州市越秀区太阳升幼儿园","002",3000,"02","测试数据3"));
        customers.add(new Customer(4,"广州市越秀区红黑蓝幼儿园","005",4000,"01","测试数据4"));
        customers.add(new Customer(5,"广州市越秀区太阳幼儿园","007",5000,"02","测试数据5"));
        customers.add(new Customer(6,"广州市越秀区月亮幼儿园","002",6000,"01","测试数据6"));
        customers.add(new Customer(7,"广州市越秀区星星幼儿园","002",7000,"01","测试数据7"));
        customers.add(new Customer(8,"广州市越秀区星星幼儿园","008",8000,"01","测试数据8"));
        excelWriter.fill(customers,writeSheet);
        Map<String, Object> maps=new HashMap<>();
        maps.put("date",DateUtils.format(new Date(),DateUtils.DATE_FORMAT_19));
        excelWriter.fill(maps,writeSheet);
        excelWriter.finish();
        bos.flush();
        bos.close();
    }
}

附件
- 模板：template1.xls
- 导出文件：template1-out.xls
  excel解析
代码示例
- PayDetail.java（vo） ```java package org.polaris.easyexcel.vo;

/**

缴费明细
@author polaris 450733605@qq.com */ public class PayDetail {

private String id; private String name; private String address; private String area; private String paydate; private String singleAmt; private String totalAmt;

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 String getAddress() {
```
 return address;
```
}

public void setAddress(String address) {
```
 this.address = address;
```
}

public String getArea() {
```
 return area;
```
}

public void setArea(String area) {
```
 this.area = area;
```
}

public String getPaydate() {
```
 return paydate;
```
}

public void setPaydate(String paydate) {
```
 this.paydate = paydate;
```
}

public String getSingleAmt() {
```
 return singleAmt;
```
}

public void setSingleAmt(String singleAmt) {
```
 this.singleAmt = singleAmt;
```
}

public String getTotalAmt() {
```
 return totalAmt;
```
}

public void setTotalAmt(String totalAmt) {
```
 this.totalAmt = totalAmt;
```
}

@Override public String toString() {
```
 return "PayDetail{" +
         "id='" + id + '\'' +
         ", name='" + name + '\'' +
         ", address='" + address + '\'' +
         ", area='" + area + '\'' +
         ", paydate='" + paydate + '\'' +
         ", singleAmt='" + singleAmt + '\'' +
         ", totalAmt='" + totalAmt + '\'' +
         '}';
```
} } ```
- DataListener.java（vo） ```java package org.polaris.easyexcel.util;

import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import org.polaris.easyexcel.vo.PayDetail; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.ArrayList; import java.util.List;

/**

分页监听器

@author polaris 450733605@qq.com */ public class DataListener extends AnalysisEventListener {

private final static Logger log = LoggerFactory.getLogger(DataListener.class);

List list=new ArrayList<>();

@Override public void doAfterAllAnalysed(AnalysisContext analysisContext) {

 doWork(analysisContext);

}

@Override public void invoke(PayDetail payDetail, AnalysisContext analysisContext) {

 // 此处进行数据处理
 log.info(payDetail.toString());
 list.add(payDetail);
 // 分批处理从excel文档读取的数据，每次处理若干条数据，防止存储几万条数据在内存，容易OOM
 int BATCH_SIZE=32;
 if(list.size()>=BATCH_SIZE){
     // 每读取${BATCH_SIZE}条数据清空一次列表
     doWork(analysisContext);
     list.clear();
 }

}

private void doWork(AnalysisContext analysisContext){

 log.info(String.format("当前批次处理数据条数：%d，累计：%d/%d",
         list.size(),
         analysisContext.getCurrentRowNum(),
         analysisContext.getTotalCount()-1)
 );

} } ```

ExcelParseTest.java（测试类） ```java package org.polaris.easyexcel;

import com.alibaba.excel.EasyExcel; import com.alibaba.excel.support.ExcelTypeEnum; import org.junit.Test; import org.polaris.easyexcel.util.DataListener; import org.polaris.easyexcel.vo.PayDetail; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.BufferedInputStream; import java.io.IOException; import java.io.InputStream; import java.util.List;

/**

excel解析示例
@author polaris 450733605@qq.com */ public class ExcelParseTest {

private final static Logger log = LoggerFactory.getLogger(ExcelParseTest.class);

/**
- 解析excel
- @throws IOException */ @Test public void excelParseSimple() throws IOException { InputStream inputStream=new BufferedInputStream(ExcelParseTest.class.getClassLoader().getResourceAsStream(“template2.xlsx”)); List voList=EasyExcel.read(inputStream)
```
     .excelType(ExcelTypeEnum.XLSX)
     .head(PayDetail.class)
     .sheet()
     .headRowNumber(2)
     .doReadSync();
```
  inputStream.close(); for(int i=0;i<voList.size();i++){
```
 log.info(voList.get(i).toString());
```
  } }
  
  /**
- 分页读取Excel文档中的数据
- @throws IOException / @Test public void excelParseBatch() throws IOException { InputStream inputStream=new BufferedInputStream(ExcelParseTest.class.getClassLoader().getResourceAsStream(“template2.xlsx”)); // 方式1：不推荐，结果将再次封装到${voList}，若过大可能导致OOM /List voList=EasyExcel.read(inputStream,new DataListener())
```
     .excelType(ExcelTypeEnum.XLSX)
     .head(PayDetail.class)
     .sheet()
     .headRowNumber(2)
     .doReadSync();*/
```
  // 方式2：推荐，分批处理，不做收集 EasyExcel.read(inputStream,new DataListener())
```
     .excelType(ExcelTypeEnum.XLSX)
     .head(PayDetail.class)
     .sheet()
     .headRowNumber(2)
     .doRead();
```
  inputStream.close();
  
  log.info(“完成——————————————————“); }

} ```

附件
- 模板：template2.xlsx
  完整示例代码
  easyexcel_demo.zip

Knowledge learning library - 知识管理

EasyExcel使用案例

配置

使用

excel导出

excel解析

完整示例代码