java easyExcel 导入

package springboot_001.entity;

import org.apache.commons.beanutils.BeanUtils;
import springboot_001.annotaion.Id;
import springboot_001.annotaion.MyExcelProperty;
import springboot_001.annotaion.Table;

import java.lang.reflect.InvocationTargetException;

/**
 * @Author tc
 * @Date 2019/6/610:47
 * @Desc
 */
@Table("t_ows_license_flow_data")
public class LicenseFlowDataVo {
    @Id
    private String id;

    @MyExcelProperty(index = 0)
    private String esn;//ESN

    @MyExcelProperty(index = 1)
    private String sbhj;//设备（网元）名称
    @MyExcelProperty(index = 2)
    private String lsn;//lsn
    @MyExcelProperty(index = 3)
    private String scenario;//场景名称 暂时没有 3
    @MyExcelProperty(index = 4)
    private String depart         ;//地区部
    @MyExcelProperty(index = 5)
    private String dbchuye;// 代表处 6
    @MyExcelProperty(index = 6)
    private String danwei         ; //客户
    @MyExcelProperty(index = 7)
    private String product        ;//产品
    @MyExcelProperty(index = 8)
    private String versions       ;//版本
    @MyExcelProperty(index = 9)
    private String application    ; //申请人
    @MyExcelProperty(index = 10 )
    private String zerenren;//责任人
    @MyExcelProperty(index = 11)
    private String phoneno        ;//联系电话
    @MyExcelProperty(index = 12)
    private String emailaddr      ;//邮件地址
    @MyExcelProperty(index = 13)
    private String dirctor;//直接主管
    @MyExcelProperty(index = 14)
    private String product_area_sel;//地区部专项部长
    @MyExcelProperty(index = 15,format = "yyyy-MM-dd")
private String activit_timeStr;//激活日期 16
    private Long activit_time;
@MyExcelProperty(index = 16,format = "yyyy-MM-dd")
private String exp_dateStr;//有效截止日期 中文
    private Long exp_date;//有效截止日期
    @MyExcelProperty(index = 17)
    private String applys_fren;//申请次数 18
    @MyExcelProperty(index = 18)
    private String wheather_net;//    是否在网 19
@MyExcelProperty(index = 19)
    private String is_network     ;//是否商用

    public String getId() {
        return id;
    }

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

    public String getEsn() {
        return esn;
    }

    public void setEsn(String esn) {
        this.esn = esn;
    }

    public String getSbhj() {
        return sbhj;
    }

    public void setSbhj(String sbhj) {
        this.sbhj = sbhj;
    }

    public String getLsn() {
        return lsn;
    }

    public void setLsn(String lsn) {
        this.lsn = lsn;
    }

    public String getDepart() {
        return depart;
    }

    public void setDepart(String depart) {
        this.depart = depart;
    }

    public String getDbchuye() {
        return dbchuye;
    }

    public void setDbchuye(String dbchuye) {
        this.dbchuye = dbchuye;
    }

    public String getDanwei() {
        return danwei;
    }

    public void setDanwei(String danwei) {
        this.danwei = danwei;
    }

    public String getProduct() {
        return product;
    }

    public void setProduct(String product) {
        this.product = product;
    }

    public String getVersions() {
        return versions;
    }

    public void setVersions(String versions) {
        this.versions = versions;
    }

    public String getApplication() {
        return application;
    }

    public void setApplication(String application) {
        this.application = application;
    }

    public String getZerenren() {
        return zerenren;
    }

    public void setZerenren(String zerenren) {
        this.zerenren = zerenren;
    }

    public String getPhoneno() {
        return phoneno;
    }

    public void setPhoneno(String phoneno) {
        this.phoneno = phoneno;
    }

    public String getEmailaddr() {
        return emailaddr;
    }

    public void setEmailaddr(String emailaddr) {
        this.emailaddr = emailaddr;
    }


    public String getProduct_area_sel() {
        return product_area_sel;
    }

    public void setProduct_area_sel(String product_area_sel) {
        this.product_area_sel = product_area_sel;
    }

    public String getExp_dateStr() {
        return exp_dateStr;
    }

    public void setExp_dateStr(String exp_dateStr) {
        this.exp_dateStr = exp_dateStr;
    }

    public Long getExp_date() {
        return exp_date;
    }

    public void setExp_date(Long exp_date) {
        this.exp_date = exp_date;
    }

    public String getIs_network() {
        return is_network;
    }

    public void setIs_network(String is_network) {
        this.is_network = is_network;
    }

    public String getScenario() {
        return scenario;
    }

    public void setScenario(String scenario) {
        this.scenario = scenario;
    }

    public String getDirctor() {
        return dirctor;
    }

    public void setDirctor(String dirctor) {
        this.dirctor = dirctor;
    }

    public String getActivit_timeStr() {
        return activit_timeStr;
    }

    public void setActivit_timeStr(String activit_timeStr) {
        this.activit_timeStr = activit_timeStr;
    }

    public Long getActivit_time() {
        return activit_time;
    }

    public void setActivit_time(Long activit_time) {
        this.activit_time = activit_time;
    }

    public String getApplys_fren() {
        return applys_fren;
    }

    public void setApplys_fren(String applys_fren) {
        this.applys_fren = applys_fren;
    }

    public String getWheather_net() {
        return wheather_net;
    }

    public void setWheather_net(String wheather_net) {
        this.wheather_net = wheather_net;
    }

    public LicenseFlowData voToBean() throws InvocationTargetException, IllegalAccessException {
        LicenseFlowData licenseFlowData = new LicenseFlowData();
        BeanUtils.copyProperties(licenseFlowData,this);
        return licenseFlowData;
    }
}

 @Override
    public Result dealImportLicenseFlowExcel(InputStream inputStream, String originalFilename,User currentUser) throws Exception {
        List<LicenseFlowDataVo> licenseFlowDataVos = ExcelUtils.readPlus(inputStream, 1, LicenseFlowDataVo.class, originalFilename);

        for (LicenseFlowDataVo vo : licenseFlowDataVos) {
            String exp_dateStr = vo.getExp_dateStr();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date exp_date = simpleDateFormat.parse(exp_dateStr);
            vo.setExp_date(exp_date.getTime());
            vo.setActivit_time(simpleDateFormat.parse(vo.getActivit_timeStr()).getTime());
            LicenseFlowData licenseFlowData = vo.voToBean();
            licenseFlowData.setId(UUID.randomUUID().toString());
            Map map = (Map) JSON.toJSON(licenseFlowData);

            String flowDefId = UUID.randomUUID().toString().replace("-", "");
            Map<String, Object> params = new HashMap<>();
            params.put("flow_def_id", flowDefId);
            params.put("pending", "draft");
            Map<String,Object> instanceMap= flowInstanceService.sumbitForm(params,currentUser);
            JSONObject obj= JSONObject.parseObject(JSON.toJSONString(instanceMap));
            String status=obj.getString("status");
            if("200".equals(status)){
                JSONObject object=JSONObject.parseObject("data");
                String instanceId = object.getString("ID");
                String dataId = (String) map.get("ID");
                dao.remove(R.FLOW_DATA, dataId);
                map.put("INSTANCEID", instanceId);
                dao.saveOrUpdate(R.FLOW_DATA, map);
            }




        }
        return Result.buildResult(Result.Status.OK);
    }

package springboot_001.utils;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import springboot_001.annotaion.MyExcelProperty;

import java.beans.PropertyDescriptor;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ExcelUtils {
    public static List read(InputStream inputStream, int startRow) throws Exception {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
        List<List> returnDate = new ArrayList<>();
        for (int i = startRow; i < sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            ArrayList<String> data = new ArrayList<>();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                HSSFCell cell = row.getCell(j);
                cell.setCellType(CellType.STRING);
                String stringCellValue = cell.getStringCellValue();
                data.add(stringCellValue);
            }
            returnDate.add(data);
        }
        return returnDate;
    }

    public static <T> List<T> readPlus(InputStream inputStream, int startRow, Class<T> tClass, String filename) throws Exception {
        Workbook workbook = null;
        if (StringUtils.isBlank(filename)) {
            filename = "xls";
        }
        if (filename.endsWith("xls")) {
            workbook = new HSSFWorkbook(inputStream);
        } else {
            workbook = new XSSFWorkbook(inputStream);
        }
        Sheet sheet = workbook.getSheetAt(0);
        List<T> returnDate = new ArrayList<>();
        for (int i = startRow; i <=sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            T t = tClass.newInstance();
            Field[] declaredFields = tClass.getDeclaredFields();
            if (row == null || row.getCell(0) == null) {
                continue;
            } else {
                Cell cell = row.getCell(0);
                cell.setCellType(CellType.STRING);
                String value = cell.getStringCellValue();
                if (StringUtils.isBlank(value)) {
                    continue;
                }
            }
            for (Field file : declaredFields) {
                if (file.isAnnotationPresent(MyExcelProperty.class)) {
                    MyExcelProperty myExcelProperty = file.getAnnotation(MyExcelProperty.class);
                    int index = myExcelProperty.index();
                    String format = myExcelProperty.format();
                    Cell cell = row.getCell(index);
                    if (cell == null) {
                        cell = row.createCell(index);
                    }
                    cell.setCellType(CellType.STRING);
                    String stringCellValue = cell.getStringCellValue();
                    if (StringUtils.isNotBlank(format) &&
                            file.getGenericType().toString().equals("class java.lang.String")) {
                        String format1 = "";
                        try {
                            Date javaDate = DateUtil.getJavaDate(Double.parseDouble(stringCellValue));
                            SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
                            format1 = simpleDateFormat.format(javaDate);
                        } catch (Exception e) {
                            format1 = stringCellValue;
                        }
                        PropertyDescriptor pd = new PropertyDescriptor(file.getName(), tClass);
                        Method writeMethod = pd.getWriteMethod();
                        writeMethod.invoke(t, format1);
                    } else if (file.getGenericType().toString().equals("class java.lang.String")) {
                        PropertyDescriptor pd = new PropertyDescriptor(file.getName(), tClass);
                        Method writeMethod = pd.getWriteMethod();
                        writeMethod.invoke(t, stringCellValue);
                    }
                }
            }
            returnDate.add(t);
        }
        return returnDate;
    }

    public static <T> List<T> readPlus(InputStream inputStream, int startRow, Class<T> tClass) throws Exception {
        return readPlus(inputStream, startRow, tClass, "xls");
    }

    public static void write(InputStream inputStream, OutputStream outputStream, int startRow, List data) throws Exception {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
        for (int i = startRow; i < data.size(); i++) {
            HSSFRow row = sheet.getRow(i);
            List list = (List) data.get(i);
            for (int j = 0; j < list.size(); j++) {
                HSSFCell cell = row.getCell(j);
                HSSFCellStyle cellStyle = cell.getCellStyle();
                cell.setCellType(CellType.STRING);
                cell.setCellValue(list.get(j).toString());
                cell.setCellStyle(cellStyle);
            }
        }
        hssfWorkbook.write(outputStream);
    }

    public static <T> void writePlus(InputStream inputStream, OutputStream outputStream, int startRow, List<T> data, String fileName) throws Exception {
        if (StringUtils.isBlank(fileName)) {
            fileName = "xls";
        }
        Workbook workbook = null;
        if (fileName.endsWith("xls")) {
            workbook = new HSSFWorkbook(inputStream);

        } else if (fileName.endsWith("xlsx")) {
            workbook = new XSSFWorkbook(inputStream);

        }
        Sheet sheet = workbook.getSheetAt(0);
        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.getRow(i + startRow);
            if (row == null) {
                row = sheet.createRow(i + startRow);
            }
            T t = data.get(i);
            Field[] declaredFields = t.getClass().getDeclaredFields();
            for (Field field : declaredFields) {
                if (field.isAnnotationPresent(MyExcelProperty.class)) {
                    MyExcelProperty myExcelProperty = field.getAnnotation(MyExcelProperty.class);
                    int index = myExcelProperty.index();
                    Cell cell = row.getCell(index);
                    if (cell == null) {
                        cell = row.createCell(index);
                    }
                    CellStyle cellStyle = cell.getCellStyle();
                    CellType cellTypeEnum = cell.getCellTypeEnum();
                    cell.setCellType(CellType.STRING);
                    PropertyDescriptor pd = new PropertyDescriptor(field.getName(), t.getClass());
                    Method readMethod = pd.getReadMethod();
                    Object invoke = readMethod.invoke(t);
                    cell.setCellValue(invoke.toString());
                }
            }
        }
        workbook.write(outputStream);
    }

    public static <T> void writePlus(InputStream inputStream, OutputStream outputStream, int startRow, List<T> data) throws Exception {
        writePlus(inputStream, outputStream, startRow, data, "xls");
    }

    /**
     * Excel导出工具
     *
     * @param sheetName
     * @param values
     * @param wb
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, List<Map<String, Object>> values, HSSFWorkbook wb) {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        if (values.size() == 0) {
            return wb;
        }
        Map<String, Object> firstRow = values.get(0);
        List<String> titles = new ArrayList<String>();
        for (String title : firstRow.keySet()) {
            titles.add(title);
        }
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        //style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //声明列对象
        HSSFCell cell = null;
        //创建标题
        for (int i = 0; i < titles.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(titles.get(i));
            cell.setCellStyle(style);
        }
        for (int i = 0; i < values.size(); i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < titles.size(); j++) {
                row.createCell(j).setCellValue(values.get(i).get(titles.get(j)) == null ? "" :
                        values.get(i).get(titles.get(j)).toString());
            }
        }
        return wb;
    }
}

package springboot_001.annotaion;

import java.lang.annotation.*;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface MyExcelProperty {
    int index() default 1;

    String format() default "";
}