Use hutool to parse the excel content and convert it into an entity, and the entity attribute is empty

1. Blog premise

I encountered a demand during work. The customer provided an excel table with the original data that needs to be inserted into the database. I went to parse the table and insert the data into the database. At that time, I immediately thought of using the tool class of hutool to directly convert the content of the excel table into the entity object I needed. There are also many similar blogs on the Internet, but I am doing it with reference to the content of the online blog, using hutool to parse the excel content and convert it into an entity, but encounter the problem of the entity attribute being empty, this blog will be recorded as a work note .

Second, the code implementation

1. Introduce pom

<dependency>     <groupId>cn.hutool</groupId>     <artifactId>hutool-all</artifactId>     <version>5.5.8</version></dependency><dependency>     <groupId>org.apache.poi</groupId>     <artifactId>poi-ooxml</artifactId>      <version>3.1.7</version></dependency>

2. Define the entity after excel conversion

@Datapublic class GoodFavorOfExcel {     /**     * 宝贝id     */    private Long id;     /**     * 总收藏量     */    private Integer totalFavor;     /**     * 时间     */    private String date; }

3. Parse the code

    @PostMapping(value = "getData")    public Resp indexConvert(@RequestParam("file") MultipartFile file) throws IOException {         ExcelReader reader = ExcelUtil.getReader(file.getInputStream());        reader.addHeaderAlias("日期", "date");        reader.addHeaderAlias("宝贝ID", "id");        reader.addHeaderAlias("收藏数", "totalFavor");        List<GoodFavorOfExcel> list =  reader.readAll(GoodFavorOfExcel.class);         return RespUtil.success();    }

4. Excel table format

5. Results display

The content shown here has been converted into the entity object list I need in excel

Three, the problem is solved when it reappear

2,

Let’s show you the problems I encountered when writing code with reference to blogs on the Internet.

By comparing with the above picture, we can find that when the data is converted, although the data size is the same as the previous 5126, but the entity content inside is indeed null, the reason is because

        ExcelReader reader = ExcelUtil.getReader(file.getInputStream());        List<GoodFavorOfExcel> list =  reader.readAll(GoodFavorOfExcel.class);

The title alias is not added to the ExcelReader object, so that when the hutool tool class is parsed at the bottom level, it is impossible to know which attribute of the corresponding entity the content of that column is. This is also something that most blogs on the Internet do not talk about. The hutoll api does not mention this: https://www.hutool.cn/docs/#/poi/Excel%E8%AF%BB%E5%8F%96-ExcelReader .

2. Solution

        ExcelReader reader = ExcelUtil.getReader(file.getInputStream());        reader.addHeaderAlias("日期", "date");        reader.addHeaderAlias("宝贝ID", "id");        reader.addHeaderAlias("收藏数", "totalFavor");

The solution is to set the correspondence between the title and the alias

Four, summary

This blog is just a very simple usage note. Readers who want to learn more about why the title is set, and the alias correspondence relationship can display the data, can check the source code to understand the principle.