java poi3.15操作对象导出为excel与excel导入为对象和导出带下拉框级联的模板

因为程序版本的限制，最多只能用到poi3.15,所以用不了easyexcel。小类初衷。草率的写，也没有测试什么很大量数据的情况，有问题可以指出来哟。
由于程序的中心思想是解决导出excel，所以数据是单纯的对象属性不包括list等等。分为两个步骤，一个是注释，还有一个就是工具类操作。
我的程序是测试导成本地的，如果你要用来发给前端什么的也可以另外写输出。
看了别人文章发现我好像确实草率的写了，因为就支持String类型，参考了一下补一个基础类型均可的转换函数。
参考的文章是：也是个poi操作的文章

由于业务需要，需要搞带下拉框和级联下拉的模板，所以又补充了一些功能。

由于特别需要，又加了数据导出还能带下拉级联，还有合并数据也能导出和导入的情况

注释定义：

/**
 * @Auther: ms.y
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@Inherited
public @interface PoiExcel {
    String value();

    int index();

    boolean isString() default false;

    int ref() default 0;
    
    String format() default "";

    boolean mergable() default false;
    
	int cellWidth() default 2;
}

注释类的属性分别表示
value 表示标题
index 表示在excel中的列位置,从0开始
isString处理数字字符串被读取成数字导致的科学计数法和末尾加.0问题
ref表示级联下拉引用的上一级列数据
format如果是数值的话格式化数值
mergable该列是否合并，用于写入excel，导入本身已写判断
cellWidth列宽，设置为默认列宽的两倍，填倍数

工具类的使用：
1.调用工具类导出
HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(null, a, TestEntity.class, 1);
2.excel导成对象
FileInputStream fis = new FileInputStream(new File(“F://test.xls”));
List testList = ExcelUtil.readExcelData(fis, TestEntity.class);
3.导出带下拉框和级联的的模板
HSSFWorkbook wb = ExcelUtil.createModel(“test”,t);

1.对象导出成数据，如果传入字典值参数，根据传入的字典值，部分列数据带下拉或级联框的数据
ExcelUtil.getHSSFWorkbook
2.对象导出成数据,根据列上注释是否可合并，判断列值并合并，如果传入字典值参数，根据传入的字典值，部分列数据带下拉或级联框的数据
ExcelUtil.getHSSFWorkbookMergable
3.读取excel数据，如果存在合并数据，也会对应读取到每条数据中
ExcelUtil.readExcelData
4.创建模板
ExcelUtil.createModel

PS:只支持Map<String,List>数据的级联下拉，并且map的key必须是ref列中的List的值，而且list值不能是单纯字母+数字，
容易导致excel无法识别是数据还是列值，因此导致数据下拉失效，也不能数字开头和特殊字符

思路详解：
1.导出，导出是将对象导出excel，以list中每条数据对应excel每行
a.每个sheet页的创建表头
b.反射获取对象中每个属性对应excel中的列值
c.反射读取数据，写入excel
2.导入，导入以excel读取，转换成实体对象队列
a.循环读取sheet页行数据
b.反射实例化对象，并获取对象上列配置
c.根据列配置读取对应列数据存入对应对象属性
3.导出模板，带下拉和级联，普通属性以创建表头为主，带参数的list和map才是处理的重点
a.获取对象属性注释，普通属性直接创建对应列表头
b.如果是list数据，表示下拉，反射获取对应属性对象list，获取属性对象size和方法get,循环获取list中的值，把获取的值存在新建的隐藏sheet注释在对象上的行值中，建立命名管理，并在对应列中建立数据有效性索引
c.如果是map数据，也是先获取属性对象map，获取entrySet方法，获取entrySet，再循环entrySet，把数据依旧写入注释对应行中，因为一个map存对应的list数据，所以每个list都要建立命名管理，并以key为命名，之后再在对应列创建数据有效性索引。

注：
为什么在模板中的隐藏数据要存成行不是列，是每次新建行数据，原先写入的行数据被清空，所以数据存以行为基准，注释中的index即是模板和导入的列，也是隐藏数据存储中的行值。第二个注意的点是ref指向的是级联的前一级，前一级可以是数据也可以是list或者级联数据，但是这个数据的值，必然是后一级的命名中的key值，这个必须注意，不清楚可以去百度excel级联怎么搞，看一下就理解了

工具类定义：

/**
 * @Auther: ms.y
 */
public class ExcelUtil<T> {


    /**
     * 根据传入的对象导出excel
     *
     * @param wb
     * @param dataList
     * @param <T>
     * @param pageSize 一页多少条
     * @return
     */
    public static <T> HSSFWorkbook getHSSFWorkbook(HSSFWorkbook wb, List<T> dataList, Class<T> clazz, int pageSize) {
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        if (dataList == null || dataList.size() == 0) {
            createHeader(wb, String.valueOf(0), clazz);
            return wb;
        }
        int j = 0;
        int maxRow = pageSize;//sheet.getLastRowNum();65535
        if (maxRow > 65535) {
            maxRow = 65535;
        }
        int dataSize = dataList.size();

        Sheet sheet = null;
        for (int i = 0; i < dataSize; i++) {
            if (i % maxRow == 0) {
                sheet = createHeader(wb, String.valueOf(j++), clazz);
            }
            writeDatas(sheet, dataList.get(i), clazz, (i % maxRow + 1));
        }
        try {
            if (wb != null) {
                wb.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
        return wb;
    }

    /**
     * 根据传入的对象导出excel，数据列可合并
     *
     * @param wb
     * @param dataList
     * @param <T>
     * @param pageSize 一页多少条
     * @return
     */
    public static <T> HSSFWorkbook getHSSFWorkbookMergable(HSSFWorkbook wb, List<T> dataList, Class<T> clazz, int pageSize) {
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        if (dataList == null || dataList.size() == 0) {
            createHeader(wb, String.valueOf(0), clazz);
            return wb;
        }
        int j = 0;
        int maxRow = pageSize;//sheet.getLastRowNum();65535
        if (maxRow > 65535) {
            maxRow = 65535;
        }
        int dataSize = dataList.size();

        Sheet sheet = null;
        for (int i = 0; i < dataSize; i++) {
            if (i % maxRow == 0) {
                sheet = createHeader(wb, String.valueOf(j++), clazz);
            }
            writeDatas(sheet, dataList.get(i), clazz, (i % maxRow + 1));
        }

        int page = (int) Math.ceil(Double.valueOf(dataSize) / Double.valueOf(maxRow));
        for (int i = 0; i < page; i++) {
            sheet = wb.getSheetAt(i);
            int end = (i + 1) * maxRow;
            end = end > dataSize ? dataSize : end;
            List<int[]> mergeRegions = getDataMergeRange(dataList.subList(i * maxRow, end), clazz);
            if (mergeRegions.size() == 0) {
                continue;
            }
            createMergeRegion(sheet, mergeRegions);
        }

        try {
            if (wb != null) {
                wb.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
        return wb;
    }


    /**
     * 导出带下拉或级联框的数据
     *
     * @param wb
     * @param dataList
     * @param clazz
     * @param pageSize
     * @param dictionary
     * @param <T>
     * @param <K>
     * @return
     */
    public static <T, K> HSSFWorkbook getHSSFWorkbook(HSSFWorkbook wb, List<T> dataList, Class<T> clazz, int pageSize, K dictionary) {
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        if (dataList == null || dataList.size() == 0) {
            createHeader(wb, String.valueOf(0), clazz);
            return wb;
        }
        int j = 0;
        int maxRow = pageSize;//sheet.getLastRowNum();65535
        if (maxRow > 65535) {
            maxRow = 65535;
        }
        int dataSize = dataList.size();

        Sheet sheet = null;
        for (int i = 0; i < dataSize; i++) {
            if (i % maxRow == 0) {
                sheet = createHeader(wb, String.valueOf(j++), clazz);
            }
            writeDatas(sheet, dataList.get(i), clazz, (i % maxRow + 1));
            createDropDownData(wb, sheet, dictionary);
        }
        try {
            if (wb != null) {
                wb.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
        return wb;
    }

    /**
     * 导出带下拉或级联框的数据
     *
     * @param wb
     * @param dataList
     * @param clazz
     * @param pageSize
     * @param dictionary
     * @param <T>
     * @param <K>
     * @return
     */
    public static <T, K> HSSFWorkbook getHSSFWorkbookMergable(HSSFWorkbook wb, List<T> dataList, Class<T> clazz, int pageSize, K dictionary) {
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        if (dataList == null || dataList.size() == 0) {
            createHeader(wb, String.valueOf(0), clazz);
            return wb;
        }
        int j = 0;
        int maxRow = pageSize;//sheet.getLastRowNum();65535
        if (maxRow > 65535) {
            maxRow = 65535;
        }
        int dataSize = dataList.size();

        Sheet sheet = null;
        for (int i = 0; i < dataSize; i++) {
            if (i % maxRow == 0) {
                sheet = createHeader(wb, String.valueOf(j++), clazz);
            }
            writeDatas(sheet, dataList.get(i), clazz, (i % maxRow + 1));
            createDropDownData(wb, sheet, dictionary);
        }

        int page = (int) Math.ceil(Double.valueOf(dataSize) / Double.valueOf(maxRow));
        for (int i = 0; i < page; i++) {
            sheet = wb.getSheetAt(i);
            int end = (i + 1) * maxRow;
            end = end > dataSize ? dataSize : end;
            List<int[]> mergeRegions = getDataMergeRange(dataList.subList(i * maxRow, end), clazz);
            if (mergeRegions.size() == 0) {
                continue;
            }
            createMergeRegion(sheet, mergeRegions);
        }

        try {
            if (wb != null) {
                wb.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
        return wb;
    }

    /**
     * 创建合并区域
     */
    private static void createMergeRegion(Sheet sheet, List<int[]> mergeRange) {
        //range[0], range[1], range[2], range[3] 表示 起始行，终止行，起始列，终止列
        mergeRange.stream().forEach(range -> sheet.addMergedRegion(new CellRangeAddress(range[0], range[1], range[2], range[3])));
    }

    /**
     * 获取数据合并区域
     * @param data
     * @param clazz
     * @param <T>
     * @return
     */
    private static <T> List<int[]> getDataMergeRange(List<T> data, Class<T> clazz) {
        List<int[]> ranges = new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();
        List<Field> fieldList = Arrays.asList(fields);
        if (data.size() <= 1) {
            return ranges;
        }
        fieldList.stream().forEach(field -> {
            field.setAccessible(true);
            PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
            if (poiExcel != null && poiExcel.mergable()) {
                Object firstRowData = null;
                Object lastRowData = null;
                Object firstValue = null;
                Object lastValue = null;
                int firstRow = 0;
                int lastRow = 1;
                int dataSize = data.size();
                int lastRowNum = dataSize - 1;

                try {
                    while (lastRow < dataSize) {
                        if (lastRow == 1) {
                            firstRowData = data.get(firstRow);
                            lastRowData = data.get(lastRow);
                            firstValue = field.get(firstRowData);
                            lastValue = field.get(lastRowData);
                        }
                        if (firstRowData == null || lastRowData == null || firstValue == null || lastValue == null || !String.valueOf(firstValue).equals(String.valueOf(lastValue))) {
                            if ((lastRow != firstRow + 1)) {
                                int[] range = new int[]{firstRow + 1, lastRow, poiExcel.index(), poiExcel.index()};
                                ranges.add(range);
                            }
                            if (lastRow != lastRowNum) {
                                firstRow = lastRow;
                                firstRowData = data.get(firstRow);
                                firstValue = field.get(firstRowData);
                            }
                        } else if (lastRow == lastRowNum && String.valueOf(firstValue).equals(String.valueOf(lastValue))) {
                            int[] range = new int[]{firstRow + 1, lastRow + 1, poiExcel.index(), poiExcel.index()};
                            ranges.add(range);
                        }
                        lastRow++;
                        if (lastRow != dataSize) {
                            lastRowData = data.get(lastRow);
                            lastValue = field.get(lastRowData);
                        }
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        });
        return ranges;
    }


    /**
     * 创建下拉数据
     *
     * @param wb
     * @param data
     */
    private static <T> void createDropDownData(Workbook wb, Sheet sheet, T data) {
        Class clazz = data.getClass();
        Field[] fields = clazz.getDeclaredFields();
        List<Field> fieldList = Arrays.asList(fields);
        boolean tag = wb.getSheet("hidden") == null;
        Sheet hiddenSheet = wb.getSheet("hidden") == null ? wb.createSheet("hidden") : wb.getSheet("hidden");
        wb.setSheetHidden(1, true);

        fieldList.stream().forEach(field -> {
            field.setAccessible(true);
            PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
            if (poiExcel != null) {
                if (List.class.isAssignableFrom(field.getType())) {
                    String[] dropDownDatas;
                    Type type = field.getGenericType();
                    if (type instanceof ParameterizedType) {
                        try {
                            Object fieldData = field.get(data);
                            if (fieldData != null) {
                                if (tag) {
                                    Class StringClazz = fieldData.getClass();//获取到属性的值的Class对象
                                    Method m = StringClazz.getDeclaredMethod("size");
                                    int size = (Integer) m.invoke(fieldData);//调用list的size方法，得到list的长度
                                    dropDownDatas = new String[size];
                                    for (int i = 0; i < size; i++) {//遍历list，调用get方法，获取list中的对象实例
                                        Method getM = StringClazz.getDeclaredMethod("get", int.class);
                                        getM.setAccessible(true);
                                        dropDownDatas[i] = getM.invoke(fieldData, i).toString();
                                    }

                                    Row row = hiddenSheet.createRow(poiExcel.index());
                                    for (int i = 0; i < dropDownDatas.length; i++) {
                                        Cell cell = row.createCell(i);
                                        cell.setCellValue(dropDownDatas[i]);
                                    }
                                    createDataName(wb, "list" + poiExcel.index(), "hidden!$A$" + (poiExcel.index() + 1) + ":$" + getPos(dropDownDatas.length)
                                            + "$" + (poiExcel.index() + 1));
                                }

                                DataValidation data_validation = createDataValidation("list" + poiExcel.index(), 1, 65533,
                                        poiExcel.index(), poiExcel.index());
                                sheet.addValidationData(data_validation);
                            }
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        } catch (NoSuchMethodException e) {
                            e.printStackTrace();
                        } catch (InvocationTargetException e) {
                            e.printStackTrace();
                        }
                    }
                }

                if (Map.class.isAssignableFrom(field.getType())) {
                    Type type = field.getGenericType();
                    if (type instanceof ParameterizedType) {
                        try {
                            Object fieldData = field.get(data);
                            if (fieldData != null) {
                                if (tag) {
                                    Class mapClazz = fieldData.getClass();//获取到属性的值的Class对象
                                    Row row = hiddenSheet.createRow(poiExcel.index());
                                    int from = 0;//标识在行数据中级联的起始列和终止列
                                    int end = 0;
                                    Method entrySetM = mapClazz.getDeclaredMethod("entrySet");
                                    Set<Map.Entry> entrySet = (Set<Map.Entry>) entrySetM.invoke(fieldData);
                                    Cell cell;
                                    for (Map.Entry entry : entrySet) {
                                        List<String> listValue = (List<String>) entry.getValue();
                                        for (String s : listValue) {
                                            cell = row.createCell(end++);
                                            cell.setCellValue(s);
                                        }
                                        //循环创建子序列名
                                        createDataName(wb, entry.getKey().toString(), "hidden!$" + getPos(from + 1)
                                                + "$" + (poiExcel.index() + 1) + ":$" + getPos(end) + "$" + (poiExcel.index() + 1));
                                        from = end;
                                    }
                                }
                                for (int j = 1; j < 102; j++) {
                                    DataValidation data_validation = createDataValidation("INDIRECT($" + getPos(poiExcel.ref() + 1) + "$" + (j + 1) + ")",
                                            j, j, poiExcel.index(), poiExcel.index());
                                    sheet.addValidationData(data_validation);
                                }
                            }
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        } catch (NoSuchMethodException e) {
                            e.printStackTrace();
                        } catch (InvocationTargetException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        });
    }

    /**
     * 获取cell数据，转换为String类型
     *
     * @param cell
     * @return
     */
    private static String getCellStringValue(Cell cell, Boolean isString, String format) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue;
        CellType cellType = cell.getCellTypeEnum();

        // 以下是判断数据的类型
        switch (cellType) {
            case NUMERIC: // 数字
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
                    Date date = cell.getDateCellValue();
                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                    cellValue = formater.format(date);
                } else {
                    if (isString) {
                        DecimalFormat df = new DecimalFormat("0");
                        cellValue = df.format(cell.getNumericCellValue());
                    } else {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                        cellValue = cellValue.endsWith(".0") ? cellValue.substring(0, cellValue.indexOf(".")) : cellValue;
                        if (!"".equals(format)) {
                            DecimalFormat df = new DecimalFormat(format);
                            cellValue = df.format(cell.getNumericCellValue());
                        }
                    }
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "";
                break;
        }
        return cellValue;
    }

    /**
     * 创建表格头
     *
     * @param wb
     * @param sheetName
     * @param clazz
     * @param <T>
     * @return
     */
    private static <T> Sheet createHeader(Workbook wb, String sheetName, Class<T> clazz) {
        Sheet sheet = wb.createSheet(sheetName);
        Row row = sheet.createRow(0);
        Field[] fields = clazz.getDeclaredFields();
        Cell cell;
        CellStyle style = getStringCenterStyle(wb);
        for (Field field : fields) {
            PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
            if (poiExcel == null) {
                continue;
            }
            cell = row.createCell(poiExcel.index());
            cell.setCellStyle(style);//设置单元格格式为"文本"
            cell.setCellType(CellType.STRING);
            cell.setCellValue(poiExcel.value());
            
            if (poiExcel.cellWidth() > 0) {
                sheet.setColumnWidth(poiExcel.index(), sheet.getColumnWidth(poiExcel.index()) * poiExcel.cellWidth());
            } else {
                sheet.setColumnWidth(poiExcel.index(), sheet.getColumnWidth(poiExcel.index()) * 2);
            }
        }
        return sheet;
    }

    /**
     * 行数据填写
     *
     * @param sheet
     * @param data
     * @param clazz
     * @param rowNum
     * @param <T>
     */
    private static <T> void writeDatas(Sheet sheet, T data, Class<T> clazz, int rowNum) {
        Field[] fields = clazz.getDeclaredFields();
        List<Field> fieldList = Arrays.asList(fields);
        Row row = sheet.createRow(rowNum);
        fieldList.stream().forEach(field -> {
            field.setAccessible(true);
            PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
            if (poiExcel != null) {
                try {
                    Object val = field.get(data);
                    if (val != null) {
                        row.createCell(poiExcel.index()).setCellValue(String.valueOf(val));
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        });
    }

    /**
     * sheet数据读取
     *
     * @param sheet
     * @param clazz
     * @param <T>
     * @return
     */
    private static <T> List<T> ReadDatas(Sheet sheet, Class<T> clazz) {
        List<T> datas = new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();
        List<Field> fieldList = Arrays.asList(fields);
        Iterator<Row> iterator = sheet.iterator();

        int mergerdRegions = sheet.getNumMergedRegions();
        boolean hasMergerdRegions = mergerdRegions > 0;
        List<int[]> regions = new ArrayList<>();
        Map<String, Cell> mergerdValues = new HashMap<>();

        if (hasMergerdRegions) {
            for (int i = 0; i < mergerdRegions; i++) {
                CellRangeAddress cellAddresses = sheet.getMergedRegion(i);
                int fr = cellAddresses.getFirstRow();
                int lr = cellAddresses.getLastRow();
                int fc = cellAddresses.getFirstColumn();
                int lc = cellAddresses.getLastColumn();
                regions.add(new int[]{fr, lr, fc, lc});
                Row row = sheet.getRow(fr);
                mergerdValues.put("" + fr + lr + fc + lc, row.getCell(fc));
            }
        }
        while (iterator.hasNext()) {
            try {
                Row row = iterator.next();
                if (row == null || row.getRowNum() == 0) {
                    continue;
                }
                int rowIndex = row.getRowNum();
                T data = clazz.newInstance();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell == null) {
                        continue;
                    }
                    int cellIndex = cell.getColumnIndex();
                    fieldList.stream().forEach(field -> {
                        PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
                        field.setAccessible(true);
                        if (poiExcel != null) {
                            if (poiExcel.index() == cellIndex) {//列和对象列一致
                                try {
                                    if (hasMergerdRegions) {
                                        List<int[]> finds = regions.stream().filter(region -> {
                                            if (rowIndex >= region[0] && rowIndex <= region[1] && cellIndex >= region[2] && cellIndex <= region[3]) {
                                                return true;
                                            }
                                            return false;
                                        }).collect(Collectors.toList());
                                        if (CollectionUtils.isEmpty(finds)) {
                                            field.set(data, typeParser(field.getType(), getCellStringValue(cell, poiExcel.isString(), poiExcel.format())));
                                        } else {
                                            int[] find = finds.get(0);
                                            field.set(data, typeParser(field.getType(), getCellStringValue(mergerdValues.get("" + find[0] + find[1] + find[2] + find[3]), poiExcel.isString(), poiExcel.format())));
                                        }
                                    } else {
                                        field.set(data, typeParser(field.getType(), getCellStringValue(cell, poiExcel.isString(), poiExcel.format())));
                                    }
                                } catch (IllegalAccessException e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                    });
                }
                if (!isObjEmpty(data)) {
                    datas.add(data);
                }
            } catch (InstantiationException e) {
                e.printStackTrace();
                return null;
            } catch (IllegalAccessException e) {
                e.printStackTrace();
                return null;
            }
        }
        return datas;
    }


    /**
     * String值转换成对应类型
     *
     * @param type
     * @param value
     * @param <T>
     * @return
     */
    private static <T> Object typeParser(Class<T> type, String value) {
        if (type == null) {
            return null;
        }
        if (value == null || value.length() == 0) {
            if (type.equals(int.class) || type.equals(double.class) || type.equals(float.class)
                    || type.equals(byte.class) || type.equals(long.class) || type.equals(short.class)) {
                return 0;
            }
            if (type.equals(boolean.class)) {
                return false;
            }
            if (type.equals(char.class)) {
                return '\u0000';
            }
            return null;
        }
        if (type.equals(char.class) || type.equals(String.class) || type.equals(Character.class)) {
            return value;
        } else if (type.equals(int.class) || type.equals(Integer.class)) {
            if (value.contains(".")) {
                value = value.substring(0, value.indexOf("."));
            }
            return Integer.parseInt(value);
        } else if (type.equals(double.class) || type.equals(Double.class)) {
            return Double.parseDouble(value);
        } else if (type.equals(float.class) || type.equals(Float.class)) {
            return Float.parseFloat(value);
        } else if (type.equals(boolean.class) || type.equals(Boolean.class)) {
            return Boolean.parseBoolean(value);
        } else if (type.equals(byte.class) || type.equals(Byte.class)) {
            return Byte.parseByte(value);
        } else if (type.equals(long.class) || type.equals(Long.class)) {
            return Long.parseLong(value);
        } else if (type.equals(short.class) || type.equals(Short.class)) {
            return Short.parseShort(value);
        } else {
            return null;
        }
    }

    /**
     * 样式
     *
     * @param wb
     * @return
     */
    private static CellStyle getStringCenterStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        return style;
    }

    /**
     * 读取excel数据
     *
     * @param is
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcelData(InputStream is, Class<T> clazz) {
        List<T> dataList = null;
        try {
            dataList = new ArrayList<>();
            Workbook workbook = WorkbookFactory.create(is);
            Iterator<Sheet> sheets = workbook.sheetIterator();
            while (sheets.hasNext()) {
                Sheet sheet = sheets.next();
                if (sheet == null) {
                    continue;
                }
                List<T> sheetData = ReadDatas(sheet, clazz);
                if (sheetData != null) {
                    dataList.addAll(sheetData);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataList;
    }

    /**
     * 读取excel数据，指定sheet页
     *
     * @param is
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcelData(InputStream is, Class<T> clazz, int sheetIndex) {
        List<T> dataList = null;
        try {
            dataList = new ArrayList<>();
            Workbook workbook = WorkbookFactory.create(is);
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            if (sheet == null) {
                return null;
            }
            List<T> sheetData = ReadDatas(sheet, clazz);
            if (sheetData != null) {
                dataList.addAll(sheetData);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataList;
    }

    /**
     * 导出表格模板
     *
     * @param sheetName
     * @param <T>
     * @return
     */
    public static <T> Workbook createModel(String sheetName, T data) {
        Workbook wb = new HSSFWorkbook();
        Class clazz = data.getClass();
        Field[] fields = clazz.getDeclaredFields();
        List<Field> fieldList = Arrays.asList(fields);
        Sheet sheet = createHeader(wb, sheetName, clazz);
        Sheet hiddenSheet = wb.createSheet("hidden");
        wb.setSheetHidden(1, true);
        fieldList.stream().forEach(field -> {
            field.setAccessible(true);
            PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
            if (poiExcel != null) {
                if (List.class.isAssignableFrom(field.getType())) {
                    String[] dropDownDatas;
                    Type type = field.getGenericType();
                    if (type instanceof ParameterizedType) {
                        try {
                            Object fieldData = field.get(data);
                            if (fieldData != null) {
                                Class StringClazz = fieldData.getClass();//获取到属性的值的Class对象
                                Method m = StringClazz.getDeclaredMethod("size");
                                int size = (Integer) m.invoke(fieldData);//调用list的size方法，得到list的长度
                                dropDownDatas = new String[size];
                                for (int i = 0; i < size; i++) {//遍历list，调用get方法，获取list中的对象实例
                                    Method getM = StringClazz.getDeclaredMethod("get", int.class);
                                    getM.setAccessible(true);
                                    dropDownDatas[i] = getM.invoke(fieldData, i).toString();
                                }

                                Row row = hiddenSheet.createRow(poiExcel.index());
                                for (int i = 0; i < dropDownDatas.length; i++) {
                                    Cell cell = row.createCell(i);
                                    cell.setCellValue(dropDownDatas[i]);
                                }

                                createDataName(wb, "list" + poiExcel.index(), "hidden!$A$" + (poiExcel.index() + 1) + ":$" + getPos(dropDownDatas.length)
                                        + "$" + (poiExcel.index() + 1));

                                DataValidation data_validation = createDataValidation("list" + poiExcel.index(), 1, 65533,
                                        poiExcel.index(), poiExcel.index());
                                sheet.addValidationData(data_validation);
                            }
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        } catch (NoSuchMethodException e) {
                            e.printStackTrace();
                        } catch (InvocationTargetException e) {
                            e.printStackTrace();
                        }
                    }
                }

                if (Map.class.isAssignableFrom(field.getType())) {
                    Type type = field.getGenericType();
                    if (type instanceof ParameterizedType) {
                        try {
                            Object fieldData = field.get(data);
                            if (fieldData != null) {
                                Class mapClazz = fieldData.getClass();//获取到属性的值的Class对象
                                Row row = hiddenSheet.createRow(poiExcel.index());
                                int from = 0;//标识在行数据中级联的起始列和终止列
                                int end = 0;
                                Method entrySetM = mapClazz.getDeclaredMethod("entrySet");
                                Set<Map.Entry> entrySet = (Set<Map.Entry>) entrySetM.invoke(fieldData);
                                Cell cell;
                                for (Map.Entry entry : entrySet) {
                                    List<String> listValue = (List<String>) entry.getValue();
                                    for (String s : listValue) {
                                        cell = row.createCell(end++);
                                        cell.setCellValue(s);
                                    }
                                    //循环创建子序列名
                                    createDataName(wb, entry.getKey().toString(), "hidden!$" + getPos(from + 1)
                                            + "$" + (poiExcel.index() + 1) + ":$" + getPos(end) + "$" + (poiExcel.index() + 1));
                                    from = end;
                                }

                                for (int j = 1; j < 102; j++) {
                                    DataValidation data_validation = createDataValidation("INDIRECT($" + getPos(poiExcel.ref() + 1) + "$" + (j + 1) + ")",
                                            j, j, poiExcel.index(), poiExcel.index());
                                    sheet.addValidationData(data_validation);
                                }
                            }
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        } catch (NoSuchMethodException e) {
                            e.printStackTrace();
                        } catch (InvocationTargetException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        });
        return wb;
    }

    /**
     * 创建数据约束
     *
     * @param name
     * @param fromRow
     * @param endRow
     * @param fromColumn
     * @param endColumn
     * @return
     */
    private static DataValidation createDataValidation(String name, int fromRow, int endRow, int fromColumn, int endColumn) {
        //起始行，终止行，起始列，终止列
        CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, fromColumn, endColumn);
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(name);
        return new HSSFDataValidation(regions, constraint);
    }

    /**
     * 创建命名数据
     */
    private static void createDataName(Workbook wb, String name, String region) {
        Name namedCell = wb.createName();
        namedCell.setNameName(name);
        namedCell.setRefersToFormula(region);
    }

    /**
     * 根据输入的数字返回excel列数字符
     *
     * @param size
     * @return
     */
    private static String getPos(int size) {
        if (size <= 0) {
            return "A";
        }
        String[] args = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S",
                "T", "U", "V", "W", "X", "Y", "Z"};
        boolean tag = true;//用来结束循环
        StringBuffer reversepos = new StringBuffer();
        while (tag) {
            int pos = size % 26;
            if (pos == 0) {
                pos = 25;//整除说明是26结尾，故为Z
                size--;//之所以size会减1是因为列并没有数字中所谓的10这回事，Z后面就是AA
            } else {
                pos -= 1;
            }
            int result = size / 26;
            if (result == 0) {
                reversepos.append(args[pos]);
                tag = false;
            } else {
                reversepos.append(args[pos]);
                size /= 26;
            }
        }
        return reversepos.reverse().toString();//因为是从类似个位开始算起来，所以要倒转
    }

    /**
     * 判断对象值是否全为null
     *
     * @param object
     * @param <T>
     */
    private static <T> boolean isObjEmpty(T object) {
        Class clazz = object.getClass();
        Field[] fields = clazz.getDeclaredFields();
        List<Field> fieldList = Arrays.asList(fields);
        boolean tag = true;
        for (Field field : fieldList) {
            field.setAccessible(true);
            try {
                Object fieldData = field.get(object);
                if (fieldData != null) {
                    tag = false;
                    break;
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
                return false;
            }
        }
        return tag;
    }
}

测试模板导出和对象导入导出的实体类：

public class TestList {

    @PoiExcel(index = 0, value = "t1")
    private String t1;

    @PoiExcel(index = 1, value = "下拉框")
    private List<String> list;

    @PoiExcel(index = 2, value = "t2")
    private String t2;

    @PoiExcel(index = 3, value = "下拉框2")
    private List<String> list2;

    @PoiExcel(index = 4, value = "级联下拉",ref = 3)
    private Map<String,List<String>> mapDatas;


    public String getT1() {
        return t1;
    }

    public void setT1(String t1) {
        this.t1 = t1;
    }

    public List<String> getList() {
        return list;
    }

    public void setList(List<String> list) {
        this.list = list;
    }

    public String getT2() {
        return t2;
    }

    public void setT2(String t2) {
        this.t2 = t2;
    }

    public List<String> getList2() {
        return list2;
    }

    public void setList2(List<String> list2) {
        this.list2 = list2;
    }

    public Map<String, List<String>> getMapDatas() {
        return mapDatas;
    }

    public void setMapDatas(Map<String, List<String>> mapDatas) {
        this.mapDatas = mapDatas;
    }

    public static void main(String[] args) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
		//导出带下拉级联的数据
        List<TestEntity> a = new ArrayList<>();
        TestEntity a1 = new TestEntity();
        a1.setT1("1");
        a1.setT2("2");
        a1.setT4(1);
        a1.setT5(false);
        TestEntity a2 = new TestEntity();
        a2.setT1("3");
        a2.setT3("t3");
        a2.setT4(2);
        a2.setT5(true);
        TestEntity a3 = new TestEntity();
        a3.setT1("5");
        a3.setT2("6");
        a3.setT3("t3");
        a3.setT5(true);
        a.add(a1);
        a.add(a2);
        a.add(a3);

        TestList t = new TestList();
        List<String> b = new ArrayList<>();
        b.add("1");
        b.add("2");
        b.add("3");
        t.setList(b);
        List<String> aa = new ArrayList<>();
        aa.add("测试1");
        aa.add("测试2");
        t.setList2(aa);
        Map<String, List<String>> map = new HashMap<>();
        String[] aa1 = {"1", "2", "3", "4"};
        String[] aa2 = {"1", "2", "3", "4", "5", "6", "7"};
        map.put("测试1", Arrays.asList(aa1));
        map.put("测试2", Arrays.asList(aa2));
        t.setMapDatas(map);

        HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(null, a, TestEntity.class, 1, t);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(new File("D://test.xls"));
            wb.write(fos);
            fos.flush();
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
}

@Component
public class TestEntity {

    @PoiExcel(value = "测试1",index = 1)
    private String t1;

    @PoiExcel(value = "测试2",index = 0)
    private String t2;

    private String t3;

    @PoiExcel(value = "测试4",index = 2)
    private Integer t4;

    @PoiExcel(value = "测试5",index = 3)
    private boolean t5;

    public String getT1() {
        return t1;
    }

    public void setT1(String t1) {
        this.t1 = t1;
    }

    public String getT2() {
        return t2;
    }

    public void setT2(String t2) {
        this.t2 = t2;
    }

    public String getT3() {
        return t3;
    }

    public void setT3(String t3) {
        this.t3 = t3;
    }

    public int getT4() {
        return t4;
    }

    public void setT4(int t4) {
        this.t4 = t4;
    }

    public boolean isT5() {
        return t5;
    }

    public void setT5(boolean t5) {
        this.t5 = t5;
    }

    @Override
    public String toString() {
        return "TestEntity{" +
                "t1='" + t1 + '\'' +
                ", t2='" + t2 + '\'' +
                ", t3='" + t3 + '\'' +
                ", t4=" + t4 +
                ", t5=" + t5 +
                '}';
    }
}