java反射和注解实现excel导入导出功能

excel导入导出

今天业务实现excel的导入导出功能，便简单做下记录

1，导入导出注解

1.1导出辅助注解

加在类的属性上

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD,ElementType.METHOD})
public @interface ExcelOut {
    /**
     * @return 表头名称
     */
    String value() default "";
}

例如：

public class SbssDoorplateCheckExcel {

    @ExcelOut("行政区划")
    //组织名称
    private String orgName;
}

1.2 倒入辅助注解

加在类的属性上

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD,ElementType.METHOD})
public @interface ExcelIn {
    /**
     * @return 表头名称
     */
    String value() default "";

    /*
    实体类对应的属性在excel第几列，从0开始
     */
    int order();
}

例如：

public class TestUser {
    
    @ExcelIn(value = "姓名",order = 0)
    //order：对应excel表中列号
    private String name;
    //也可以不加注解，就不会赋值
    private Integer age;
}

2.实现工具类

代码如下;

public class ExcelUtil {

    private static String GET = "get";
    private static String CONTENT_TYPE = "application/x-download";
    private static String FILE_TYPE = ".xls";
    private static String HEAD_NAME = "Content-Disposition";

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     *
     * @param response 下载
     * @param title 文件名称
     * @param dataList 导出数据
     * @param dateFormat 日志格式
     */
    public static <T> void  export(HttpServletResponse response,String title, List<T> dataList,String dateFormat) {


        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 16); // 字体高度
        font.setFontName("黑体"); // 字体
        style.setFont(font);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);

        // 其余中文样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        HSSFFont font2 = workbook.createFont();
        font2.setFontHeightInPoints((short) 12); // 字体高度
        font2.setFontName("宋体"); // 字体
        style2.setFont(font2);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);

        HSSFSheet sheet = workbook.createSheet(title);
        sheet.setDefaultColumnWidth(30);
        HSSFRow rowHeader = sheet.createRow(0);

        //表头
        List<String> headers = new ArrayList<>();
        if (!CollectionUtils.isEmpty(dataList)) {
            T t = (T)dataList.get(0);
            Field[] headFields = t.getClass().getDeclaredFields();
            for (int i = 0; i < headFields.length; i++) {
                Field head = headFields[i];
                ExcelOut annotation = head.getAnnotation(ExcelOut.class);
                if (annotation == null) {
                    continue;
                }
                String value = annotation.value();
                headers.add(value);
            }
        }


        //设置表头行
        for (int i = 0; i < headers.size(); i++) {
            String header = headers.get(i);
            HSSFCell cellHeader = rowHeader.createCell(i);
            cellHeader.setCellValue(header);
            cellHeader.setCellStyle(style);
        }
        Iterator<T> iterator = dataList.iterator();
        int index = 0;
        HSSFRow row = null;
        HSSFCell cell = null;
        while (iterator.hasNext()) {
            index ++;
            row = sheet.createRow(index);
            T it = (T)iterator.next();
            //反射，利用getXxx() 获取属性值
            Field[] fields = it.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                cell = row.createCell(i);
                cell.setCellStyle(style2);
                Field field = fields[i];
                String fieldName = field.getName();
                String methodName = GET + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                Class<?> clazz = it.getClass();
                try {
                    Method getMethod = clazz.getMethod(methodName);
                    Object value = getMethod.invoke(it);
                    //判断数据类型
                    if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    } else if (value instanceof Double) {
                        cell.setCellValue((Double) value);
                    } else if (value instanceof String) {
                        cell.setCellValue(String.valueOf(value));
                    } else if (value instanceof Long) {
                        cell.setCellValue((Long) value);
                    } else if (value instanceof Date) {
                        String s = DateUtil.formatDate((Date) value, dateFormat);
                        cell.setCellValue(s);
                    }else {
                        cell.setCellValue(value.toString());
                    }

                } catch (Exception e) {
                    logger.error("导出出错", e);
                }
            }
        }

        OutputStream out = null;
        try {
            response.setCharacterEncoding(StandardCharsets.UTF_8.name());
            response.setContentType(CONTENT_TYPE);
            String fileName = title + DateUtil.formatTimestemp(System.currentTimeMillis(),dateFormat)+ FILE_TYPE;
            fileName = new String(fileName.getBytes(), StandardCharsets.ISO_8859_1);
            response.addHeader(HEAD_NAME, "attachment;filename=" + fileName);
            out = response.getOutputStream();
            workbook.write(out);
            out.close();
        } catch (IOException e) {
            logger.error("导出出错", e);
        }finally {
            IOUtils.closeQuietly(out);
        }

    }

    /**
     *
     * @param inputStream 输入excel
     * @param clazz 目标entity
     * @return 目标类型集合
     */
    public static <T> List<T> readExcel(InputStream inputStream, Class<T> clazz, String format) throws Exception {
        List<T> result = new ArrayList<>();

        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        //行数
        int rows = sheet.getPhysicalNumberOfRows();
        int numberOfCells = 0;
        if (rows > 0 && sheet.getRow(0) != null) {
            numberOfCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }

        for (int i = 1; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            T target = clazz.newInstance();
            for (int j = 0; j < numberOfCells; j++) {
                HSSFCell cell = row.getCell(j);
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    ExcelIn in = field.getAnnotation(ExcelIn.class);
                    if (in != null) {
                        field.setAccessible(true);
                        if (in.order() == j) {
                            field.set(target, coverToObject(field, cell, format));
                        }
                    }
                }

            }
            result.add(target);
        }
        return result;
    }

    private static Object coverToObject(Field field, HSSFCell cell, String format) {
        String typeName = field.getType().getSimpleName();
        String value = getValue(cell, format);
        if ("String".equals(typeName)) {
            return value;
        } else if ("Date".equals(typeName)) {
            return DateUtil.parseDate(value, format);

        } else if ("int".equals(typeName) || "Integer".equals(typeName)) {
            return Integer.parseInt(value);
        } else if ("long".equals(typeName) || "Long".equals(typeName)) {
            return Long.parseLong(value);
        } else if ("Double".equals(typeName) || "double".equals(typeName)) {
            return Double.parseDouble(value);
        }else
            return null;
    }

    private static String getValue(HSSFCell cell, String format) {
        int cellType = cell.getCellType();
        DecimalFormat decimalFormat = new DecimalFormat("#");
        switch (cellType) {
            case CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    return DateUtil.formatDate(date, format);
                }
                double numericCellValue = cell.getNumericCellValue();
                return decimalFormat.format(numericCellValue);
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue().trim();
            case CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case CELL_TYPE_BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
        }
        return "";

    }



}