In practical application, we need to apply a large number of Excel forms. In order to reduce the input workload, we use Excel forms for input and realize the application scenario of Excel import and export. Data import: reduce the input workload, data export: Archive statistical information, data transmission: data transmission between heterogeneous systems. Here we operate through EasyExcel.
EasyExcel features: parsing and generating excel in the Java field. The well-known frameworks include Apache poi, jxl, etc. But they all have a serious problem, which is very memory consumption. If your system has a small amount of concurrency, it may be OK, but once concurrency comes up, it will be full gc of OOM or JVM frequently. EasyExcel is an open source excel processing framework of Alibaba, which is famous for its simple use and memory saving. The main reason why EasyExcel can greatly reduce the memory consumption is that it does not load all the file data into the memory at one time when parsing excel, but reads the data line by line from the disk and parses it one by one. EasyExcel adopts a row by row parsing mode, and introduces xml related dependencies into the analysis event listener pom in the mode of observer.
Implement EasyExcel to read Excel and create entity class:

@Data
public class DemoData {
@ExcelProperty(value = "Header 1",index = 0)   private String string;
@ExcelProperty(value = "Header 2",index = 0)private Date date;
@ExcelProperty(value = "Header 3",index = 0)   private Double doubleData;}

Create listener for read operation:
There is a very important point that DemoDataListener cannot be managed by spring. It needs new every time it reads excel, and then spring can be used to construct methods to pass in. Every five databases can be stored. In actual use, 3000 databases can be used, and then clean up the list to facilitate memory recycling. Assuming that this is a DAO, of course, it can also be a service with business logic. Of course, it's useless if you don't have to store this object. demo, so just new one.

public class DemoDataListener extends AnalysisEventListener<DemoData> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();
    private DemoDAO demoDAO;
    public DemoDataListener() {   demoDAO = new DemoDAO(); }
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO; }

If spring is actually used, please use the following parameterized constructor. If spring is used, please use this constructor. Every time you create a Listener, you need to pass in the classes managed by spring to achieve BATCH_COUNT, you need to store the database once to prevent tens of thousands of data from being stored in memory. It is easy to OOM. Every data analysis will be called. After the storage is completed, clean up the list, and all data analysis will be called to save the data, so as to ensure that the last remaining data is also stored in the database, plus the storage database.

 @Override
 public void invoke(DemoData data, AnalysisContext context) {
 LOGGER.info("Parse to a piece of data:{}", JSON.toJSONString(data))
list.add(data);
if (list.size() >= BATCH_COUNT) {saveData();list.clear(); }}
 @Override
  public void doAfterAllAnalysed(AnalysisContext context) {
    saveData();LOGGER.info("All data analysis completed!");}
 private void saveData() {
  LOGGER.info("{}Data, start storing database!", list.size());
  demoDAO.save(list); LOGGER.info("Database storage succeeded!");}}

Suppose this is your DAO storage. Of course, you also need this class to be managed by spring. Of course, you don't need storage or this class. If it is mybatis, try not to directly call insert multiple times. Write a mapper and add a new method batchInsert to insert all data at one time.

 public class DemoDAO {
    public void save(List<DemoData> list) { }}

The simplest way to read is to create the entity object corresponding to excel to refer to {@ link DemoData}. Since excel is read line by line by default, you need to create a callback listener for Excel line by line, which can be read directly by referring to {@ link DemoDataListener}. A very important point is that DemoDataListener cannot be managed by spring. It needs to read new every time excel is read, and then spring can be used to construct methods to pass in.

@Test
public void simpleRead() {
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();}

Write excel general data, and it will not be written repeatedly after generation:

private List<DemoData> data() {
    List<DemoData> list = new ArrayList<DemoData>();
    for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();data.setString("character string" + i);
data.setDate(new Date());data.setDoubleData(0.56);
list.add(data); }return list;}

The simplest way is to create an entity object corresponding to excel and write it directly by referring to {@ link DemoData},

@Test
public void simpleWrite() {
fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
 ExcelWriter excelWriter = null;
  try { excelWriter = EasyExcel.write(fileName, DemoData.class).build();
 WriteSheet writeSheet = EasyExcel.writerSheet("Template").build();
excelWriter.write(data(), writeSheet);
} finally {// Don't forget that finish will help close the flow if (Excel writer! = null){
 xcelWriter.finish();}}}

Simplest fill, object:

@Data
public class FillData {
private String name;    private double number;     }

Note that the template uses {} to represent the variables you want to use. If there are "{", "}" special characters, use "{", "}" instead of "{" and "}" to fill the scheme according to the object, it will be filled into the first sheet, and then the file stream will be closed automatically. Scheme 2 will be filled according to the Map.

@Test
public void simpleFill() {
    String templateFileName =
    TestFileUtil.getPath() + "demo" + File.separator + "fill" + File.separator + "simple.xlsx";
   String fileName = TestFileUtil.getPath() + "simpleFill" + System.currentTimeMillis() + ".xlsx";
    FillData fillData = new FillData(); fillData.setName("Zhang San"); fillData.setNumber(5.2);
    EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(fillData);
    fileName = TestFileUtil.getPath() + "simpleFill" + System.currentTimeMillis() + ".xlsx";
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("name", "Zhang San");map.put("number", 5.2); EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);}

Final result: