基于注解的Excel 导入导出

     在做后台管理系统时，经常遇到Excel的导入导出，基本都是使用poi的api。虽然都封装了一些工具类，但是重复性代码还是很多。特别是导入时，基本导入一个Excel就要写一个导入方法，很麻烦。最近研究了一下注解，可以利用注解加反射很简单的进行导入导出。

1：依赖

<dependencies>

    <!--单元测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.9</version>
    </dependency>

    <!--spring 单元测试-->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-test</artifactId>
        <version>4.1.2.RELEASE</version>
    </dependency>


    <!--日志-->
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.14</version>
    </dependency>

    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.5</version>
    </dependency>

    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-nop</artifactId>
        <version>1.7.8</version>
    </dependency>

    <dependency>
        <groupId>commons-lang</groupId>
        <artifactId>commons-lang</artifactId>
        <version>2.6</version>
    </dependency>

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
    </dependencies>

2：自定义注解

package com.mpp.easypoi.anon;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @des Excel 注解
 * @author mapp 2018/2/10.
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExceVo {

    /** 对应的列名称 */
    String name() default "";

    /** 列序号 */
    int sort();

    /** 字段类型对应的格式 */
    String format() default "";

    /** 是否需要校验 */
    boolean isCheck() default false;

    /** 校验字段长度 */
    int fieldLength() default 50;

    /** 校验是否可以为空 */
    boolean isEmpty() default true;
}

3：导出工具类

/**
 * @des Excel 导出 工具类
 * @author mapp 2018/2/10.
 */
public class ExcelExportUtil<T> {

    private Class claze;

    public ExcelExportUtil(Class claze) {
        this.claze = claze;
    }

    /**
     * 基于模板导出
     * @param fileName 模板名称
     * @param objs 导出实体集合
     * @param rowIndex excel第几行开始导出
     */
    public void export(String fileName, List<T[]> objs, int rowIndex) {
        POIFSFileSystem fs = null;
        FileOutputStream os = null;
        try {
            fs = new POIFSFileSystem(Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName));
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            HSSFCellStyle style = setCellStyle(workbook);
            HSSFSheet sheet = workbook.getSheetAt(0);

            for (int i = 0; i < objs.size(); i++) {
                HSSFRow row = sheet.createRow(i +  rowIndex - 1);
                for (int j = 0; j < objs.get(i).length; j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellStyle(style);
                    cell.setCellValue(objs.get(i)[j].toString());
                }
            }
            os = new FileOutputStream(new File("D:\\8888.xls"));
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 基于注解导出
     * 不需要自己封装每列的值
     * @param fileName 模板名称
     * @param objs 导出实体集合
     * @param rowIndex excel第几行开始导出
     */
    public void export1(String fileName, List<T> objs, int rowIndex) {
        POIFSFileSystem fs = null;
        FileOutputStream os = null;
        try {
            // classpath 下的模板excel文件
            fs = new POIFSFileSystem(Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName));
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            // 带注解并排序好的字段
            List<Field> fieldList = getFieldList();

            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            HSSFCellStyle style = setCellStyle(workbook);
            HSSFSheet sheet = workbook.getSheetAt(0);

            for (int i = 0; i < objs.size(); i++) {
                HSSFRow row = sheet.createRow(i + rowIndex - 1);
                for (int j = 0; j < fieldList.size(); j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellStyle(style);
                    Field field = fieldList.get(j);

                    String fieldValue = covertAttrType(field, objs.get(i));
                    cell.setCellValue(fieldValue);
                }
            }
            os = new FileOutputStream(new File("D:\\8888.xls"));
            workbook.write(os);
        } catch (Exception e) {
            throw new ExcelException("导出失败", e);
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 设置样式
     * @param workbook
     */
    private HSSFCellStyle setCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }

    /**
     * 获取带注解的字段 并且排序
     * @return
     */
    private List<Field> getFieldList() {
        Field[] fields = this.claze.getDeclaredFields();
        // 无序
        List<Field> fieldList = new ArrayList<Field>();
        // 排序后的字段
        List<Field> fieldSortList = new LinkedList<Field>();
        int length = fields.length;
        int sort = 0;
        Field field = null;
        // 获取带注解的字段
        for (int i = 0; i < length; i++) {
            field = fields[i];
            if (field.isAnnotationPresent(ExceVo.class)) {
                fieldList.add(field);
            }
        }

        Assert.assertNotNull("未获取到需要导出的字段", fieldList);
        length = fieldList.size();

        for (int i = 1; i <= length; i++) {
            for (int j = 0; j < length; j++) {
                field = fieldList.get(j);
                ExceVo exceVo = field.getAnnotation(ExceVo.class);
                field.setAccessible(true);
                sort = exceVo.sort();
                if (sort == i) {
                    fieldSortList.add(field);
                    continue;
                }
            }
        }
        return fieldSortList;
    }

    /**
     * 类型转换 转为String
     */
    private String covertAttrType(Field field, T obj) throws IllegalAccessException {
        if (field.get(obj) == null) {
            return "";
        }
        ExceVo exceVo = field.getAnnotation(ExceVo.class);
        String type = field.getType().getSimpleName();
        String format = exceVo.format();
         if ("Date".equals(type)) {
            return DateFormatUtils.format((Date)field.get(obj), "yyyy-MM-dd");
        }else {
            return field.get(obj).toString();
        }
    }
}

4：导入工具类

/**
 * @des Excel 导入 工具类
 * @author mapp 2018/2/10.
 */
public class ExcelReadUtil<T> {

    private Class claze;

    public ExcelReadUtil(Class claze) {
        this.claze = claze;
    }

    /**
     * 基于注解读取excel
     * @param filePath 文件地址
     * @param rowIndex 从第几行开始读取数据
     * @return List<T> List 集合
     */
    public List<T> readExcel(String filePath, int rowIndex) {
        POIFSFileSystem fs = null;
        List<T> list = new ArrayList<T>();
        T entity = null;
        try {

            // 带注解并排序好的字段
            List<Field> fieldList = getFieldList();
            int size = fieldList.size();
            Field field = null;
            fs = new POIFSFileSystem(new FileInputStream(filePath));
            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            HSSFSheet sheet = workbook.getSheetAt(0);
            // 不准确
            int rowLength = sheet.getLastRowNum();

            for (int i = 0; i <= 3 - rowIndex; i++) {
                HSSFRow row = sheet.getRow(i + rowIndex - 1);
                entity = (T) claze.newInstance();
                for (int j = 0; j < size; j++) {
                    HSSFCell cell = row.getCell(j);
                    field = fieldList.get(j);
                    field.set(entity, covertAttrType(field, cell));
                }
                list.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 获取带注解的字段 并且排序
     * @return
     */
    private List<Field> getFieldList() {
        Field[] fields = this.claze.getDeclaredFields();
        // 无序
        List<Field> fieldList = new ArrayList<Field>();
        // 排序后的字段
        List<Field> fieldSortList = new LinkedList<Field>();
        int length = fields.length;
        int sort = 0;
        Field field = null;
        // 获取带注解的字段
        for (int i = 0; i < length; i++) {
            field = fields[i];
            if (field.isAnnotationPresent(ExceVo.class)) {
                fieldList.add(field);
            }
        }

        Assert.assertNotNull("未获取到需要导出的字段", fieldList);
        length = fieldList.size();

        for (int i = 1; i <= length; i++) {
            for (int j = 0; j < length; j++) {
                field = fieldList.get(j);
                ExceVo exceVo = field.getAnnotation(ExceVo.class);
                field.setAccessible(true);
                sort = exceVo.sort();
                if (sort == i) {
                    fieldSortList.add(field);
                    continue;
                }
            }
        }
        return fieldSortList;
    }

    /**
     * 类型转换 将cell 单元格格式转为 字段类型
     */
    private Object covertAttrType(Field field, HSSFCell cell) {
        int type = cell.getCellType();
        if (type == Cell.CELL_TYPE_BLANK) {
            return null;
        }
        ExceVo exceVo = field.getAnnotation(ExceVo.class);

        // 字段类型
        String fieldType = field.getType().getSimpleName();
       try {
           if ("String".equals(fieldType)) {
                return getValue(cell);
           }else if ("Date".equals(fieldType)) {
                return DateUtils.parseDate(getValue(cell), new String[]{"yyyy-MM-dd"});
           }else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
                return Integer.parseInt(getValue(cell));
           }else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
               return Double.parseDouble(getValue(cell));
           }
       }catch (Exception e) {
            if (e instanceof ParseException) {
                throw new ExcelException("时间格式转换失败", e);
            }else {
                e.printStackTrace();
                throw new ExcelException("格式转换失败");
            }
       }
        throw new ExcelException("excel 单元格 格式暂不支持");
    }


    /**
     * 格式转为String
     * @param cell
     * @return
     */
    public String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                return cell.getRichStringCellValue().getString().trim();
            case HSSFCell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date dt = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                    return DateFormatUtils.format(dt, "yyyy-MM-dd");
                } else {
                    // 防止数值变成科学计数法
                    String strCell = "";
                    Double num = cell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(num.toString());
                    if (bd != null) {
                        strCell = bd.toPlainString();
                    }
                    // 去除 浮点型 自动加的 .0
                    if (strCell.endsWith(".0")) {
                        strCell = strCell.substring(0, strCell.indexOf("."));
                    }
                    return strCell;
                }
            case HSSFCell.CELL_TYPE_BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case HSSFCell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case HSSFCell.CELL_TYPE_BLANK:
                return "";
            default:
                return "";
        }
    }
}

5：导出导入实体

/**
 * 导入实体
 * @author mapp 2018/2/10.
 */
public class Student {

    @ExceVo(sort = 1)
    private String name;
    @ExceVo(sort = 3)
    private String sex;
    @ExceVo(sort = 8, format = "yyyy-MM-dd")
    private Date birthDay;
    @ExceVo(sort = 4)
    private String className;
    @ExceVo(sort = 6)
    private String address;
    @ExceVo(sort = 5)
    private String phone;
    @ExceVo(sort = 7)
    private String love;
    @ExceVo(sort = 2)
    private int age;
}

/**
 * 导出实体
 * @author mapp 2018/2/10.
 */
public class User {

    private String id;
    @ExceVo(name = "姓名",sort = 1)
    private String name;
    @ExceVo(name = "性别",sort = 2)
    private String sex;
    @ExceVo(name = "生日",sort = 3)
    private Date birthDay;

    public Date getBirthDay() {
        return birthDay;
    }

    public User() {

    }
}

6：测试方法

/**
 * 测试
 */
public class TestExport {

    public static void main(String[] args) throws ParseException {
        User user1 = new User("1", "盖伦", "男", DateUtils.parseDate("2018-2-11", new String[]{"yyyy-MM-dd"}));
        User user2 = new User("2", "德邦", "男", DateUtils.parseDate("2018-2-11", new String[]{"yyyy-MM-dd"}));
        User user3 = new User("3", "拉克丝", "女", DateUtils.parseDate("2018-2-11", new String[]{"yyyy-MM-dd"}));
        User user4 = new User("4", "寒冰", "女", DateUtils.parseDate("2018-2-11", new String[]{"yyyy-MM-dd"}));

        List<User> users = new ArrayList<User>();
        users.add(user1);
        users.add(user2);
        users.add(user3);
        users.add(user4);

        Object[] objs = null;
        List<Object[]> list = new ArrayList<Object[]>();

        for (int i = 0; i < users.size(); i++) {
            objs = new Object[3];
            objs[0] = users.get(i).getId();
            objs[1] = users.get(i).getName();
            objs[2] = users.get(i).getSex();
            list.add(objs);
        }

        ExcelExportUtil<User> excelUtil = new ExcelExportUtil<User>(User.class);
        // 原始方式导出
//        excelUtil.export("test.xls", list, 2);
        // 基于注解导出
        excelUtil.export1("test.xls", users, 2);
    }
}


/**
 * 测试导入
 */
public class TestReadExcel {

    public static void main(String[] args) throws ParseException {
        ExcelReadUtil<Student> excelReadUtil = new ExcelReadUtil<Student>(Student.class);
        List<Student> students = excelReadUtil.readExcel("D:\\student.xls", 2);
        for (Student student : students) {
            System.out.println(student);
        }
    }
}