基于自定义的Excel解析框架的使用范例

最新推荐文章于 2022-08-10 14:02:39 发布

低调的JVM

最新推荐文章于 2022-08-10 14:02:39 发布

阅读量696

点赞数

分类专栏：造轮子文章标签： excel

本文链接：https://blog.csdn.net/qq_27529917/article/details/78447906

版权

造轮子专栏收录该内容

21 篇文章 1 订阅

订阅专栏

首先可能会有一个Model，与数据库的一张表对应:

package com.bob.config.mvc.model;

import java.io.Serializable;

/**
 * Excel映射的Model
 *
 * @author dell-7359
 * @create 2017-10-19 19:46
 */
public class ExcelModel implements Serializable {

    private static final long serialVersionUID = -422292418932719315L;

    private Integer id;

    private String userName;

    private String password;

    private Integer age;

    private String telephone;

    private String adress;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public String getAdress() {
        return adress;
    }

    public void setAdress(String adress) {
        this.adress = adress;
    }
}

但是我们的映射Model可能不是表对应的model，可能是属性字段不足，也不应该在表对应的model上修改东西，所以我们自定义了一个扩展类，继承自ExcelModel，有一些额外的属性:

package com.bob.config.mvc.model;

import java.util.Date;

import com.bob.config.mvc.excelmapping.ExcelColumn;
import com.bob.config.mvc.excelmapping.ExcelColumn.Column;
import com.bob.config.mvc.excelmapping.ExcelMapping;
import com.bob.config.mvc.excelmapping.PropertyInitializer;

/**
 * Excel映射的Model扩展类
 *
 * @author dell-7359
 * @create 2017-10-19 19:55
 */
@ExcelMapping(titleRow = 2, dataRow = 3, sheetAt = 0)
public class ExcelModelExtends extends ExcelModel implements PropertyInitializer<ExcelModelExtends> {

    private static final long serialVersionUID = 445175433808433505L;

    @ExcelColumn(value = Column.G,last = true)
    private String description;

    private Date parseTime;

    @ExcelColumn(value = Column.A,key = true)
    public Integer getId() {
        return super.getId();
    }

    @ExcelColumn(value = Column.B)
    public String getUserName() {
        return super.getUserName();
    }

    @ExcelColumn(value = Column.C)
    public String getPassword() {
        return super.getPassword();
    }

    @ExcelColumn(value = Column.D)
    public Integer getAge() {
        return super.getAge();
    }

    @ExcelColumn(value = Column.E)
    public String getTelephone() {
        return super.getTelephone();
    }

    @ExcelColumn(value = Column.F)
    public String getAdress() {
        return super.getAdress();
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Date getParseTime() {
        return parseTime;
    }

    public void setParseTime(Date parseTime) {
        this.parseTime = parseTime;
    }

    /**初始化Model对象
     * @return
     */
    @Override
    public ExcelModelExtends initProperties() {
        ExcelModelExtends modelExtends = new ExcelModelExtends();
        modelExtends.setParseTime(new Date());
        return modelExtends;
    }
}

我们在ExcelModelExtends 类上添加了一个注解ExcelMapping，指明Excel内标题行，数据起始行，以及数据页码。ExcelModelExtends 实现了一个属性初始化接口，每解析一行数据时通过此接口方法得到ExcelModelExtends 实例，同时做一些初始化工作。我们再ExcelModelExtends 重写自ExcelModel的getter方法上添加了ExcelColumn注解，表名这个字段对应Excel第几列，同时是否是数据行与行之间唯一性的标识，可以有多个key字段，当每个key字段均相等时行数据重复，重复校验失败。

假设Excel数据如下:
这里写图片描述

解析测试代码如下:

package com.bob.test.concrete.excelmapping;

import java.io.File;
import java.io.IOException;
import java.util.Collection;

import com.bob.config.mvc.excelmapping.Excel;
import com.bob.config.mvc.excelmapping.ExcelInstance;
import com.bob.config.mvc.excelmapping.ExcelMappingProcessor;
import com.bob.config.mvc.model.ExcelModelExtends;
import org.junit.Test;

/**
 * Excel解析工具类测试
 *
 * @author 
 * @create 2017-09-26 16:56
 */
public class ExcelMappingTest {

    private ExcelMappingProcessor<ExcelModelExtends> processor;

    @Test
    public void testParsing() throws Exception {
        Excel excel = new Excel("C:\\Users\\dell-7359\\Desktop\\Excel原始数据.xlsx");
        processor = new ExcelMappingProcessor<ExcelModelExtends>(excel,ExcelModelExtends.class);
        boolean success = processor.process();
        Collection<ExcelInstance<ExcelModelExtends>> results = processor.getCorrectResult();
        System.out.println(results.size());
        if(!success){
            File errorFile = new File("C:\\Users\\dell-7359\\Desktop\\Excel原始数据_错误回写.xlsx");
            excel.write(errorFile);
        }
    }
}