An Ali open source parsing Excel framework "Easy-Excel"

Wechat official account: Tangerine Java Technology pit Internet technology exchange & internal push group: 693961584 Article first nugget platform, follow-up synchronized update public account, reply “add group” after attention can join the Internet technology exchange & internal push wechat group, and discuss interview questions with a group of big factory executives. Reply “666” to obtain all information packs available on the First-line Internet (including development software, development specifications, classic e-PDFs, and some premium learning courses).

preface

What about bugs? When we encounter a bug online, we may need to fix the data, so what should we do? You panic, you open the offline table, you rush, you get a copy of the data to fix, you write a fix script, you are ready to release the fix, and then the problem comes, how do you parse the data, load the data into memory, and then read the fix in turn? Too slow.

Here introduces an Ali open source parsing excel framework easy- Excel.

What is the

Here is the official introduction

Java parsing, Excel generation more famous framework Apache, POI, JXL. However, they all have a serious problem with memory consumption. Poi has a SAX API that can solve some memory overflow problems to some extent, but POI still has some drawbacks, such as 2007 Version of Excel decompression and storage after decompression are completed in memory, memory consumption is still very large. Easyexcel rewrote poI on 07 version of Excel parsing, can originally a 3M Excel with POI SAX still need about 100M memory down to a few M, and again large Excel will not appear memory overflow, 03 version of the POI SAX mode. In the upper layer of model transformation encapsulation, so that users more simple and convenient.

What can do

Batch processing and parsing of data
Support for custom model class fields mapping excel table columns
Extreme reduces memory usage and is easy to use

A quick practice

So let’s write a script that gives us a little bit of a feel for the logic of the data being processed. Suppose it’s a fix script.

You have worked hard to retrieve the problem data from several offline tables that need to be fixed, assuming the following:

Ok, we have the data, let’s start writing the repair script, we first define a repair data interface`FlushDataService` 。

/** * @founder: Gold mining account "orange pine Java" * @creation time 2021/7/11 * @ description: need source code plus QQ group [693961584] for free */
public interface FlushDataService {
    /** * Brush data interface *@paramIndex Defines the brush data file index */ for extension purposes
    void flush(Integer index);
}
Copy the code

Redefine the Excel table header and fix the model needed for the table data`ItemChannelExcel`. Notice here`@ExcelProperty`The notes must be marked, notes`index`Properties are yours`excel`The index of the column of the table.

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

/** * @founder: Gold mining account "orange pine Java" * @creation time 2021/7/11 * @ description: need source code plus QQ group [693961584] for free */
@Data
public class ItemChannelExcel extends BaseRowModel {

    @ExcelProperty(index = 0)
    private Long itemNo;

    @ExcelProperty(index = 1)
    private Long itemName;
}
Copy the code

We need to write listeners here`ItemChannelExcelListener`inheritance`AnalysisEventListener`Generic class to write your business data processing logic.

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.concurrent.atomic.AtomicInteger;

/** * @founder: Gold mining account "orange pine Java" * @creation time 2021/7/11 * @ description: need source code plus QQ group [693961584] for free */
@Service
public class ItemChannelExcelListener extends AnalysisEventListener<ItemChannelExcel> {

    private AtomicInteger totalHandleCount = new AtomicInteger(0);
    private AtomicInteger sucHandleCount = new AtomicInteger(0);
    private AtomicInteger failHandleCount = new AtomicInteger(0);

    @Override
    public void invoke(ItemChannelExcel channelExcel, AnalysisContext analysisContext) {
        System.out.println("Row == in process"+channelExcel.getItemNo()+"= = ="+channelExcel.getItemName());

        // Invoke RPC Interface mock
        try{
            //Result<Xxx> listItemRes = xxxService.listItems(Long shopId);
            //if(listItemRes.isSuccess()) {
            // sucHandleCount.incrementAndGet();
            //}else{
            // failHandleCount.incrementAndGet();
            / /}
            sucHandleCount.incrementAndGet();
        }catch(Exception ex){
            failHandleCount.incrementAndGet();
        }final{ totalHandleCount.incrementAndGet(); }}@Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // The callback function that completes all processing
        System.out.println("The total number of data processed is"+ totalHandleCount+"Article");
        System.out.println("The data successfully processed this time are" + sucHandleCount + "Article");
        System.out.println("The data processed in this failure is" + failHandleCount + "Article");
    }
Copy the code

With the logic and model fixed, it’s time to write the implementation class`FlushDataServiceImpl`.Note that injection resolution is required here`Excel`The listener is bound to read`Excel`Specify the file source location, as well as the parsing model and business process listeners.

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.metadata.Sheet;

import java.io.InputStream;

/** * @founder: Gold mining account "orange pine Java" * @creation time 2021/7/11 * @ description: need source code plus QQ group [693961584] for free */
@service
public class FlushDataServiceImpl implements FlushDataService {
    
    @Resource
    private ItemChannelExcelListener itemChannelExcelListener;

    @Override
    public void flush(Integer index) {
        Class<? extends FlushDataServiceImpl> clazz = this.getClass();
        InputStream resourceAsStream = clazz.getResourceAsStream("/flush.xlsx");
        / / here to create a Sheet object mainly is the header of the February 1, because excel the first line does not need to parse, readers specified as required
        EasyExcelFactory.readBySax(resourceAsStream,new Sheet(1.1, ItemChannelExcel.class), itemChannelExcelListener); }}Copy the code

Test the

Whether the effect is ok, quickly also try it, but still pray that you usually less problems, after all, you are professional, bugs are inevitable, the key is that we have to have the ability to solve problems quickly. Well, I’ll see you next time

“I love you” takes three seconds to say, three hours to explain, and a lifetime to prove. “Bug” takes three seconds to find, three hours to find, and a lifetime to debug.

The last

Articles are original, original is not easy, thanks to the mining platform, feel fruitful, help three even ha, thank you

Wechat search public number: Orange pine Java technology nest, make a friend, enter the Internet technology exchange group

All the code, sequence diagram and architecture diagram involved in the article are shared and can be requested for free through the public number plus group

Article if there is a mistake, welcome to comment messages pointed out, also welcome to reprint, the trouble to mark the source is good

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

An Ali open source parsing Excel framework “Easy-Excel”

preface

What is the

What can do

A quick practice

Ok, we have the data, let’s start writing the repair script, we first define a repair data interface`FlushDataService` 。

Redefine the Excel table header and fix the model needed for the table data`ItemChannelExcel`. Notice here`@ExcelProperty`The notes must be marked, notes`index`Properties are yours`excel`The index of the column of the table.

We need to write listeners here`ItemChannelExcelListener`inheritance`AnalysisEventListener`Generic class to write your business data processing logic.

With the logic and model fixed, it’s time to write the implementation class`FlushDataServiceImpl`.Note that injection resolution is required here`Excel`The listener is bound to read`Excel`Specify the file source location, as well as the parsing model and business process listeners.

Test the

The last

An Ali open source parsing Excel framework “Easy-Excel”

preface

What is the

What can do

A quick practice

Ok, we have the data, let’s start writing the repair script, we first define a repair data interfaceFlushDataService 。

Redefine the Excel table header and fix the model needed for the table dataItemChannelExcel. Notice here@ExcelPropertyThe notes must be marked, notesindexProperties are yoursexcelThe index of the column of the table.

We need to write listeners hereItemChannelExcelListenerinheritanceAnalysisEventListenerGeneric class to write your business data processing logic.

With the logic and model fixed, it’s time to write the implementation classFlushDataServiceImpl.Note that injection resolution is required hereExcelThe listener is bound to readExcelSpecify the file source location, as well as the parsing model and business process listeners.

Test the

The last

Related Posts

Domain-driven Design (DDD) : Layered architecture

How to use Netty to implement RPC

Seven points to clarify the Spring Cloud microservices architecture

Ok, we have the data, let’s start writing the repair script, we first define a repair data interface`FlushDataService` 。

Redefine the Excel table header and fix the model needed for the table data`ItemChannelExcel`. Notice here`@ExcelProperty`The notes must be marked, notes`index`Properties are yours`excel`The index of the column of the table.

We need to write listeners here`ItemChannelExcelListener`inheritance`AnalysisEventListener`Generic class to write your business data processing logic.

With the logic and model fixed, it’s time to write the implementation class`FlushDataServiceImpl`.Note that injection resolution is required here`Excel`The listener is bound to read`Excel`Specify the file source location, as well as the parsing model and business process listeners.