preface

background

start

In fact, I rejected this requirement at the beginning, because when crawling data some time ago, I always followed the logic of interface > > postman > > code

Now an Excel suddenly appears, and the logic is flawed perfectly. Of course, as a qualified progrmmer porgram progarm

Ah, come on, as a qualified rookie programmer, can we give up? So I began to analyze the page interface crazily, analyze the relevant JS of the page, find the footprints of the predecessors in the forum, and gradually find the clues of the data. Finally, I gave up after a morning's struggle

Finally, I decided to analyze Excel honestly

Trodden pit

At the beginning, it was prepared to use POI for parsing, but it was written that a very serious problem was found. The Excel header downloaded each time may be different. For example, the Excel header downloaded this time is a,b,d, and the next header may be c,d, but they all come from a,b,c,d, the total header field list

Besides, there are more fields in Excel, about 30. If POI is used, one needs to judge (maybe it's because I'm too busy. I didn't think of any good way). The guy next to me looks at my screen, and he is shocked by more than 30 case s behind my switch, and he has no end of his body. He immediately shows a look at a fool

Big guy asked me why I didn't think about EasyExcel. I couldn't help asking, "what Excel?", big guy: "EasyExcel", I: "easy what?" , big guy: "EasyExcel", me: "EasyExcel?" "Easy what?"

Climbing pit

The first step is to import the corresponding Pom dependencies. It is recommended that you install a Maven Helper plug-in first, so that you can easily view all the introduced dependencies in the project and the possible conflicts

This is it. By the way, attach the version for your own use. POI recommends using the version above 3.1.7. So far, no problems have been found

EasyExcel is recommended to use version 2.2.3. Up to now, no problems have been found. It is recommended not to use version 1.x. it should be noted that the version correspondence between EasyExcel and POI is quite metaphysical. At present, no rules have been found. There is no problem with the current version

After introducing dependency, we will start to launch

The first is the setting of entity class. There are only two core settings in entity class fields: @ ExcelIgnore and @ ExcelProperty("xxx"). The former indicates that the field of entity class is not parsed by a field in Excel, while the latter indicates that the field is parsed by xxx in Excel
Data under the header is parsed

/**
 * @description:Sample code
 * @author: King
 * @date: 2020/6/25 16:20
 */
@Data
public class DemoEntity {
    
    @ExcelIgnore
    private int id;

    @ExcelProperty("full name")
    private String name;

    @ExcelProperty("Age")
    private int age;
}

Next is the core part of EasyExcel, listener class, which is understood by the limited knowledge reserve. The listener has encapsulated the complete Excel parsing process. Users only need to create a new class to inherit the AnalysisEventListener, rewrite the invoke() method to automatically parse. Here is a post EasyExcel Official user manual of

/**
 * @description:EasyExcel EasyExcel monitor
 * @author: King
 * @date: 2020/6/24 10:05
 */
public class ExcelDataListener extends AnalysisEventListener {
    /**
     * Store the final parsed data for the final warehousing operation
     */
    List<T> list = new ArrayList<>();
    /**
     * The core method of analyzing Excel
     * @param o
     * @param analysisContext
     */
    @Override
    public void invoke(Object obj, AnalysisContext analysisContext) {
    /**
     *Excel parsing has been completed here. OBJ can be understood as each entity class object. The attribute value in the object has been mapped by the field in Excel, which can be verified by printing obj
     */
     // System.out.println ((entity class) obj);
     list.add((Entity class)obj)
    }
	/**
     * After analyzing the subsequent operations of Excel, it is generally used for the final warehousing operation
     * @param o
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    // User defined save method in dao
	 save(List);
    }

In this way, the analysis of Excel has been completed. Now, the last step is left

/**
 * Define the InputStream stream object, which can be read by the local Excel file or converted from the stream data returned by the download interface
 * 1.If it is a local file, it can be converted with new FileInputStream (file path)
 * 2.Available if the stream data returned by the download interface response.getEntity().getContent() method conversion
 **/
InputStream in = null;
// If the flow data returned by the interface
in = response.getEntity().getContent();
// If local
in = new FileInputStream(File path);

ExcelReader excelReader = null;
try {
    excelReader = EasyExcel.read(in, Entity class.class, new ExcelDataListener()).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet);
} finally {
    if (excelReader != null) {
        excelReader.finish();
    }

So far, a complete Excel parsing process seems to be completed

So if some attribute values of entity classes do not come from Excel parsing and need to be set in the business layer, what should be done

Just do a little processing on the parser

/**
 * @description:EasyExcel EasyExcel monitor
 * @author: King
 * @date: 2020/6/24 10:05
 */
public class ExcelDataListener extends AnalysisEventListener {
    /**
     * Store the final parsed data for the final warehousing operation
     */
    List<T> list = new ArrayList<>();
    
	// Suppose the gender field is set in the business layer
	public static String gender;
	// Add a method to set parameters, because static methods execute before constructors, so they can be used to get data in the business layer before creating objects
	public static void setParam(Map<String, String> map) {
        gender = map.get("gender");
    }
	
    /**
     * The core method of analyzing Excel
     * @param o
     * @param analysisContext
     */
    @Override
    public void invoke(Object obj, AnalysisContext analysisContext) {
    /**
     *Write the acquired data to entity class object here
     */
     XxxEntity entity = new XxxEntity;
     entity.setGender(gender);
     // System.out.println ((entity class) obj);
     list.add((Entity class)obj)
    }
	/**
     * After analyzing the subsequent operations of Excel, it is generally used for the final warehousing operation
     * @param o
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    // User defined save method in dao
	 save(List);
    }

The corresponding business layer should also be modified

InputStream in = null;
// If the flow data returned by the interface
in = response.getEntity().getContent();
// If local
in = new FileInputStream(File path);

ExcelReader excelReader = null;
try {
    Map<String,String> param = new HashMap<>();
    param.put("gender","18");
   
    ExcelDataListener excelDataListener = new ExcelDataListener();
    setParam(param);

    excelReader = EasyExcel.read(dataFinal, SycmFullProductRankingEntity.class, excelDataListener).build();
} finally {
    if (excelReader != null) {
        excelReader.finish();
    }

In this way, a complete Excel analysis is completed

Particular attention

summary

Compared with POI, EasyExcel is less difficult to start with, less code, and friendly to novices. Its function is far beyond the simple operation of Excel reading. I will continue to learn from EasyExcel in the future.

Subject to my level, if you find any mistakes or improvements in your blog, I hope you can leave a message in time. Thank you very much!