java springboot poi 从controller 接收不同类型excel 文件处理

import com.guard.biz.common.util.excel.excelin;
import org.apache.commons.beanutils.beanutilsbean;
import org.apache.commons.lang3.stringutils;
import org.apache.commons.lang3.reflect.fieldutils;
import org.apache.poi.hssf.usermodel.hssfdateutil;
import org.apache.poi.ss.usermodel.cell;
import org.apache.poi.ss.usermodel.row;
import org.apache.poi.ss.usermodel.sheet;
import org.apache.poi.ss.usermodel.workbook;
import org.apache.poi.ss.usermodel.workbookfactory;
import org.slf4j.logger;
import org.slf4j.loggerfactory;

import java.io.filenotfoundexception;
import java.io.ioexception;
import java.io.inputstream;
import java.lang.reflect.field;
import java.lang.reflect.invocationtargetexception;
import java.math.bigdecimal;
import java.util.arraylist;
import java.util.hashmap;
import java.util.list;
import java.util.map;

/**
 * @author wei
 * @time 2019/10/29
 * @description excel 导入工具类
 */
public class excelimportutil<t> {

    private static final logger log = loggerfactory.getlogger(excelimportutil.class);

    private static beanutilsbean beanutilsbean = new beanutilsbean();

    static {
        beanutilsbean.getconvertutils().register(new org.apache.commons.beanutils.converters.dateconverter(null), java.util.date.class);
    }

    /**
     * 表头名字和对应所在第几列的下标，用于根据title取到对应的值
     */
    private final map<string, integer> title_to_index = new hashmap<>();
    /**
     * 所有带有excelin注解的字段
     */
    private final list<field> fields = new arraylist<>();

    /**
     * 统计表格的行和列数量用来遍历表格
     */
    private int firstcellnum = 0;
    private int lastcellnum = 0;
    private int firstrownum = 0;
    private int lastrownum = 0;

    private string sheetname;

    private sheet sheet;

    public list<t> read(inputstream in, class clazz) throws exception {
        gatherannotationfields(clazz);
        configsheet(in);
        configheader();
        list rlist = null;
        try {
            rlist = readcontent(clazz);
        } catch (illegalaccessexception e) {
            throw new exception(e);
        } catch (instantiationexception e) {
            throw new exception(e);
        } catch (invocationtargetexception e) {
            throw new exception(e);
        }
        return rlist;
    }

    private list readcontent(class clazz) throws illegalaccessexception, instantiationexception, invocationtargetexception {
        object o = null;
        row row = null;
        list<object> rslist = new arraylist<>();
        object value = null;
        for (int i = (firstrownum + 1); i <= lastrownum; i++) {
            o = clazz.newinstance();
            row = sheet.getrow(i);
            cell cell = null;
            for (field field : fields) {
                //根据注解中的title，取到表格中该列所对应的的值
                integer column = title_to_index.get(field.getannotation(excelin.class).title());
                if (column == null) {
                    continue;
                }
                cell = row.getcell(column);
                value = getcellvalue(cell);
                if (null != value && stringutils.isnotblank(value.tostring())) {
                    beanutilsbean.setproperty(o, field.getname(), value);
                }
            }
            rslist.add(o);
        }
        return rslist;
    }

    private void configsheet(inputstream in) throws exception {
        // 根据文件类型来分别创建合适的workbook对象
        try (workbook wb = workbookfactory.create(in)) {
            getsheetbyname(wb);
        } catch (filenotfoundexception e) {
            throw new exception(e);
        } catch (ioexception e) {
            throw new exception(e);
        }
    }


    /**
     * 根据sheet获取对应的行列值，和表头对应的列值映射
     */
    private void configheader() {
        this.firstrownum = sheet.getfirstrownum();
        this.lastrownum = sheet.getlastrownum();
        //第一行为表头，拿到表头对应的列值
        row row = sheet.getrow(firstrownum);
        this.firstcellnum = row.getfirstcellnum();
        this.lastcellnum = row.getlastcellnum();
        for (int i = firstcellnum; i < lastcellnum; i++) {
            title_to_index.put(row.getcell(i).getstringcellvalue(), i);
        }
    }

    /**
     * 根据sheet名称获取sheet
     *
     * @param workbook
     * @return
     * @throws exception
     */
    private void getsheetbyname(workbook workbook) throws exception { 
        int sheetnumber = workbook.getnumberofsheets();
        for (int i = 0; i < sheetnumber; i++) {
            string name = workbook.getsheetname(i);
            if (stringutils.equals(this.sheetname, name)) {
                this.sheet = workbook.getsheetat(i);
                return;
            }
        }
        throw new exception("excel中未找到名称为" + this.sheetname + "的sheet");
    }

    /**
     * 根据自定义注解，获取所要导入表格的sheet名称和需要导入的字段名称
     *
     * @param clazz
     * @throws exception
     */
    private void gatherannotationfields(class clazz) throws exception {
        if (!clazz.isannotationpresent(excelin.class)) {
            throw new exception(clazz.getname() + "类上没有excelin注解");
        }
        excelin excelin = (excelin) clazz.getannotation(excelin.class);
        this.sheetname = excelin.sheetname();
        // 得到所有定义字段
        field[] allfields = fieldutils.getallfields(clazz);
        // 得到所有field并存放到一个list中
        for (field field : allfields) {
            if (field.isannotationpresent(excelin.class)) {
                fields.add(field);
            }
        }
        if (fields.isempty()) {
            throw new exception(clazz.getname() + "中没有excelin注解字段");
        }
    }

    private object getcellvalue(cell cell) {
        if (cell == null) {
            return "";
        }
        object obj = null;
        switch (cell.getcelltypeenum()) {
            case boolean:
                obj = cell.getbooleancellvalue();
                break;
            case error:
                obj = cell.geterrorcellvalue();
                break;
            case formula:
                try {
                    obj = string.valueof(cell.getstringcellvalue());
                } catch (illegalstateexception e) {
                    obj = numerictobigdecimal(cell);
                }
                break;
            case numeric:
                obj = getnumericvalue(cell);
                break;
            case string:
                string value = string.valueof(cell.getstringcellvalue());
                value = value.replace(" ", "");
                value = value.replace("\n", "");
                value = value.replace("\t", "");
                obj = value;
                break;
            default:
                break;
        }
        return obj;
    }

    private object getnumericvalue(cell cell) {
        // 处理日期格式、时间格式
        if (hssfdateutil.iscelldateformatted(cell)) {
            return cell.getdatecellvalue();
        } else if (cell.getcellstyle().getdataformat() == 58) {
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
            double value = cell.getnumericcellvalue();
            return org.apache.poi.ss.usermodel.dateutil.getjavadate(value);
        } else {
            return numerictobigdecimal(cell);
        }
    }

    private object numerictobigdecimal(cell cell) {
        string valueof = string.valueof(cell.getnumericcellvalue());
        bigdecimal bd = new bigdecimal(valueof);
        return bd;
    }
}

import java.lang.annotation.elementtype;
import java.lang.annotation.retention;
import java.lang.annotation.retentionpolicy;
import java.lang.annotation.target;

/**
 * @author lei
 * @time 2019/10/29
 * @description
 */
@retention(value = retentionpolicy.runtime)
@target(value = {elementtype.type, elementtype.field})
public @interface excelin {

    /**
     * 导入sheet名称
     *
     * @return
     */
    string sheetname() default "";

    /**
     * 字段对应的表头名称
     *
     * @return
     */
    string title() default "";
}

import lombok.data;
import lombok.tostring;

import java.util.date;

/**
 * @author lei
 * @time 2019/10/29
 * @description
 */
@tostring
@data
@excelin(sheetname = "用户")
public class user {
    private string id;

    @excelin(title = "姓名")
    private string name;

    @excelin(title = "年龄")
    private integer age;

    @excelin(title = "出生日期")
    private date birthdate;

}

 @postmapping("/batch/excel")
    @apioperation(value = "根据excel文件批量导入")
    public responsevo batchadddevicebyexcelimport(multipartfile multipartfile) {
        return new responsevo(deviceservice.adddevicebyexcelimport(multipartfile));
    }

 public boolean adddevicebyexcelimport(multipartfile multipartfile) {
        file file = null;
        try {
            file = file.createtempfile("temp", null);
        } catch (ioexception e) {
            e.printstacktrace();
        }
        try {
            multipartfile.transferto(file);
        } catch (ioexception e) {
            e.printstacktrace();
        }
        file.deleteonexit();
        inputstream inputstream = null;
        try {
            inputstream = new fileinputstream(file);
        } catch (filenotfoundexception e) {
            e.printstacktrace();
        }

        excelimportutil<user> reader = new excelimportutil<>();
        list<user> userlist = null;
        try {
            userlist = reader.read(inputstream, user.class);
        } catch (exception e) {
            log.error(e.getmessage());
            throw new codeexception("51302", e.getmessage());
        }
        
            userlist.stream().foreach(e -> log.info(e.tostring()));
        
        return true;
    }