Summary of using easyExcel

Big guy, my programmer Tian. What we bring today is a summary of the simple use of esayExcel, an efficient Excel processing framework

I received a request from the leader to do an Excel import function, so I gave full play to my specialty - Programming for Baidu.

I searched Baidu for a circle of POi import methods, and found a reliable demo. In my opinion, this method is quite cumbersome. Judge one by one after reading the Excel content. After tossing all morning, the code is like an old man - standing still and ashamed of my food.

Under the guidance of the company's experienced elderly people, it is convenient to use Alibaba's open source easyExcel import method. More importantly, the development of the whole import process was completed in ten minutes.

The book returns to the true story and officially enters the use process of easyExcel.

1, Import dependency

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

2, Write entity class

public class MedicineBasicImport implements Serializable {

    /**
     * Drug code
     */
    @ExcelProperty("Drug code")
    private String code;
    /**
     * Drug type
     */
    @ExcelProperty( "Drug type")
    private String type;
     /**
     * Drug name
     */
    @ExcelProperty("Drug name")
    private String title;

}

In particular, the @ ExcelProperty annotation needs to be added to the entity property to correspond to the header of Excel.

3, Write operation

 @Override
    public int importBasicMedicine(MultipartFile file, Long updateSupport) {
        try {
            BufferedInputStream bufferedInputStream=new BufferedInputStream(file.getInputStream());
            EasyExcel.read(bufferedInputStream, MedicineBasicImport.class,new ImportMedicineService(updateSupport,medicineBasicMapper))
                .sheet()
                .doRead();;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return 0;
    }

Call easyexcel The read () method needs to pass three parameters: 1. File stream 2. Entity class 3. Business class processed by new import medicine service (updatesupport, medicine basic mapper)

4, Business realization

Importmedicaneservice requires us to inherit the AnalysisEventListener class and implement its two methods, invoke() and doAfterAllAnalysed().

public class ImportMedicineService extends AnalysisEventListener<MedicineBasicImport> {
    private Long topDeptId;
    private TMedicineBasicMapper medicineBasicMapper;

    List<TMedicineBasic> list =  new ArrayList<>();

    public ImportMedicineService(Long updateSupport, TMedicineBasicMapper medicineBasicMapper) {
        topDeptId=updateSupport;
        this.medicineBasicMapper=medicineBasicMapper;
    }

    // This method is called every time a row is read
    @Override
    public void invoke(MedicineBasicImport data, AnalysisContext context) {

        TMedicineBasic tMedicineBasic=new TMedicineBasic();
        tMedicineBasic.setId(IdUtils.simpleUUID());
        tMedicineBasic.setTopDeptId(topDeptId);
        BeanUtils.copyProperties(data,tMedicineBasic);
        list.add(tMedicineBasic);
    }

    // This method is called when all reads are completed
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        medicineBasicMapper.insertList(list);
    }

When we call the import interface, we will call the invoke() method of the implementation class of AnalysisEventListener, but the invoke() method will be executed every time we read a row. Most business scenarios will store the read content into the database. If we read a row, we will save it to the database, which is a disaster for the database.

A better way is to perform database operations in the doAfterAllAnalysed() method and convert multiple database IO into one io.

Is this method simpler than PIO?

If there is no accident, this should be the last technical blog in 2021 of the lunar calendar. Well, see you next year!

I wish you all a happy new year.