自定义注解文件导入导出

自己写的，就是利用反射和自定义注解实现的。直接上代码
目前只支持Integer、String、Long、Date类型，如果需要别的类型需在ExportExcelUtils类readExcel方法中，switch里新增所需类型

• 工具类

public class ExportExcelUtils {

    public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
        exportExcel(data, response.getOutputStream());
    }

    /***
     * @Description: 文件导出
     * @param list 集合
     * @param clazz 集合类型
     * @author zhaobinyang
     * @date 2020/6/25 17:59
     */
    public static void exportExcel(HttpServletResponse response, @Nullable List<?> list, @Nullable Class clazz) throws Exception {
        if (CollectionUtils.isNotEmpty(list) && Objects.nonNull(clazz)) {
            boolean annotationPresent = clazz.isAnnotationPresent(FiledTable.class);
            if (annotationPresent) {
                FiledTable filedTable = (FiledTable) clazz.getAnnotation(FiledTable.class);
                //获取表头
                List<String> titles = getTitles(clazz);
                //获取数据
                List<List<Object>> rows = getRows(list);
                if (CollectionUtils.isNotEmpty(rows)) {
                    long count = rows.stream().filter(bean -> bean.size() != titles.size()).count();
                    if (count >= 1) {
                        throw new BusinessException(ErrorCodeEnum.GL99990512);
                    }
                }
                exportExcel(response, filedTable.fileName(), ExcelData.builder()
                        .name(filedTable.tabName())
                        .titles(titles)
                        .rows(rows)
                        .build());
            }
        }

    }

    /**
     * @Description: 文件导入
     * @param inputStream
     * @param clazz
     * @param <T>
     * @return
     * @Description: 文件导入
     */
    public static <T> List<T> readExcel(InputStream inputStream, @Nullable Class<T> clazz) {
        Workbook wb = null;
        try {
            List<String> beanTitles = new ArrayList<>();
            List<String> beanTypes = new ArrayList<>();
            Map<String, String> attributeMap = new HashMap<>();
            Map<String, String> typeMap = new HashMap<>();
            Field[] declaredFields = clazz.getDeclaredFields();
            for (Field field : declaredFields) {
                if (field.isAnnotationPresent(FiledColumn.class)) {
                    FiledColumn annotation = field.getAnnotation(FiledColumn.class);
                    beanTitles.add(annotation.name());
                    beanTypes.add(field.getType().getTypeName());
                    attributeMap.put(annotation.name(), field.getName());
                    if (field.isAnnotationPresent(JsonFormat.class)) {
                        JsonFormat jsonFormat = field.getAnnotation(JsonFormat.class);
                        typeMap.put(field.getName(), jsonFormat.pattern());
                    }
                }
            }

            wb = WorkbookFactory.create(inputStream);
            Sheet sheetAt = wb.getSheetAt(0);
            int lastRowNum = sheetAt.getLastRowNum();
            Row row = sheetAt.getRow(0);
            int rowNum = row.getLastCellNum();

            List<String> columnsTitle = new ArrayList<>();
            for (int i = 0; i < rowNum; i++) {
                Cell cell = row.getCell(i);
                String stringCellValue = cell.getStringCellValue();
                columnsTitle.add(stringCellValue);
            }

            List<T> beans = new ArrayList<>();
            for (int k = 1; k <= lastRowNum; k++) {
                Row fileRow = sheetAt.getRow(k);
                int rowNum1 = fileRow.getLastCellNum();
                T instance = (T) clazz.newInstance();
                for (int j = 0; j < rowNum1; j++) {
                    Cell cell = fileRow.getCell(j);
                    if (Objects.isNull(cell))
                        continue;

                    String beanTitle = beanTitles.get(j);
                    String beanType = beanTypes.get(j);
                    String attribute = attributeMap.get(beanTitle);
                    PropertyDescriptor propertyDescriptor = new PropertyDescriptor(attribute, clazz);
                    Method writeMethod = propertyDescriptor.getWriteMethod();

                    switch (beanType) {
                        case "java.lang.Integer":
                            writeMethod.invoke(instance, Integer.valueOf(cell.getStringCellValue()));
                            break;
                        case "java.lang.String":
                            writeMethod.invoke(instance, cell.getStringCellValue());
                            break;
                        case "java.lang.Long":
                            writeMethod.invoke(instance, Long.valueOf(cell.getStringCellValue()));
                            break;
                        case "java.util.Date":
                            try {
                                writeMethod.invoke(instance, cell.getDateCellValue());
                            } catch (Exception e) {
                                String format = typeMap.get(attribute);
                                Date date = DateUtil.stringToDate(cell.getStringCellValue(), format);
                                writeMethod.invoke(instance, date);
                            }
                            break;
                    }
                }
                beans.add((T) instance);
            }
            return beans;
        } catch (Exception e) {
            e.printStackTrace();
            throw new BusinessException(ErrorCodeEnum.GL99990506);
        } finally {
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private static List<List<Object>> getRows(List<?> list) {
        return list.stream().map(bean -> {
            List<Object> row = new ArrayList<>();
            Class<?> rClass = bean.getClass();
            Field[] declaredFields = rClass.getDeclaredFields();

            for (Field field : declaredFields) {
                if (field.isAnnotationPresent(FiledColumn.class)) {
                    FiledColumn annotation = field.getAnnotation(FiledColumn.class);
                    if (annotation.export()) {
                        field.setAccessible(true);
                        try {
                            PropertyDescriptor pd = new PropertyDescriptor(field.getName(), rClass);
                            Method readMethod = pd.getReadMethod();
                            Object invoke = readMethod.invoke(bean);
//                            由于实体bean GET、SET方法会存在自定义类型，故不能使用PropertyDescriptor类实现反射赋值
//                            PropertyDescriptor pd = new PropertyDescriptor(field.getName(), rClass);
//                            Method readMethod = pd.getReadMethod();
//                            Object invoke = readMethod.invoke(bean);

                            Method method = getMethod(bean, rClass, field);
                            Object invoke = method.invoke(bean);

                            if (field.isAnnotationPresent(JsonFormat.class)) {
                                JsonFormat jsonFormat = field.getAnnotation(JsonFormat.class);
                                String formatDate = DateUtil.formatDate((Date) invoke, jsonFormat.pattern());
                                invoke = formatDate;
                            }
                            if (Objects.isNull(invoke)) {
                                invoke = "";
                            }

                            row.add(invoke);
                        } catch (Exception e) {

                            log.error(e.getMessage());
                        }
                    }
                }
            }
            return row;
        }).collect(Collectors.toList());
    }

    private static Method getMethod(Object bean, Class<?> rClass, Field field) throws Exception {
        Method[] methods = rClass.getMethods();
        for (Method method : methods) {
            if (method.getName().equals("get".concat(getMethodName(field.getName())))) {
                return method;
            }
        }
        return null;
    }

    // 把一个字符串的第一个字母大写、效率是最高的、
    private static String getMethodName(String fildeName) throws Exception {
        byte[] items = fildeName.getBytes();
        items[0] = (byte) ((char) items[0] - 'a' + 'A');
        return new String(items);
    }

    private static List<String> getTitles(Class clazz) {
        Field[] fields = clazz.getDeclaredFields();
        List<String> titles = new ArrayList<>();
        //获取表头
        for (Field filed : fields) {
            if (filed.isAnnotationPresent(FiledColumn.class)) {
                FiledColumn annotation = filed.getAnnotation(FiledColumn.class);
                if (annotation.export()) {
                    titles.add(annotation.name());
                }
            }
        }
        return titles;
    }

    public static void exportExcel(ExcelData data, OutputStream out) throws Exception {

        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            String sheetName = data.getName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            XSSFSheet sheet = wb.createSheet(sheetName);
            writeExcel(wb, sheet, data);

            wb.write(out);
        } finally {
            wb.close();
            out.close();
        }
    }

    private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
        int rowIndex = 0;
        rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
        writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
        autoSizeColumns(sheet, data.getTitles().size() + 1);

    }

    private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
        int rowIndex = 0;
        int colIndex = 0;

        Font titleFont = wb.createFont();
        titleFont.setFontName("simsun");
        titleFont.setBold(true);
        // titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
        titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        Row titleRow = sheet.createRow(rowIndex);
        // titleRow.setHeightInPoints(25);
        colIndex = 0;

        for (String field : titles) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }

        rowIndex++;
        return rowIndex;
    }

    private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
        int colIndex = 0;

        Font dataFont = wb.createFont();
        dataFont.setFontName("simsun");
        // dataFont.setFontHeightInPoints((short) 14);
        dataFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        dataStyle.setFont(dataFont);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        for (List<Object> rowData : rows) {
            Row dataRow = sheet.createRow(rowIndex);
            // dataRow.setHeightInPoints(25);
            colIndex = 0;

            for (Object cellData : rowData) {
                Cell cell = dataRow.createCell(colIndex);
                if (cellData != null) {
                    cell.setCellValue(cellData.toString());
                } else {
                    cell.setCellValue("");
                }

                cell.setCellStyle(dataStyle);
                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

    private static void autoSizeColumns(Sheet sheet, int columnNumber) {

        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int newWidth = (int) (sheet.getColumnWidth(i) + 100);
            if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(BorderSide.TOP, color);
        style.setBorderColor(BorderSide.LEFT, color);
        style.setBorderColor(BorderSide.RIGHT, color);
        style.setBorderColor(BorderSide.BOTTOM, color);
    }

    /**
     * 单纯获取一列数据
     *
     * @param inputStream
     */
    public static List<String> readExcel(InputStream inputStream) {
        List<String> list = new ArrayList<>();
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(inputStream);
            Sheet sheetAt = wb.getSheetAt(0);
            //获取最后行号
            int lastRowNum = sheetAt.getLastRowNum();
            for (int i = 0; i <= lastRowNum; i++) {
                Row row = sheetAt.getRow(i);
                Cell cell = row.getCell(0);
                String stringCellValue = cell.getStringCellValue();
                list.add(stringCellValue);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
}

• 自定义注解

/**
 * 类描述: 导出文件自定义列名注解
 *
 * @author zhaobinyang
 * @date 2020/06/25 14:56
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface FiledColumn {

    String name() default "";

    boolean export() default true;
}

/**
 * 类描述: 导出文件表名注解
 *
 * @author zhaobinyang
 * @date 2020/06/25 14:55
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface FiledTable {
    String tabName() default "";

    String fileName() default "";
}

• 实体对象配置注解

@Data
@FiledTable(tabName = "课件解锁", fileName = "课件解锁")
public class ExportUnlockWaresDTO {

    @FiledColumn(name = "课件解锁序号")
    private Integer id;

    /**
     * 营期ID
     */
    @FiledColumn(name = "营期序号")
    private Long campPeriodId;

    /**
     * 目录大纲
     */
    @FiledColumn(name = "目录大纲")
    private String directoryTitle;

    /**
     * 解锁时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    @FiledColumn(name = "解锁时间")
    private Date unlockTime;
}

• 项目中使用

	/****
     * @Description: 导入
     * @Param: [file]
     * @return: com.ycx.wrapper.Message<com.ycx.transfer.response.ExportDO>
     * @Author: zhaobinyang
     * @Date: 2020/7/1 18:53
     */
    @PostMapping("/fileUpload")
    public Message<BaseDO> fileUpload(@RequestParam("file") MultipartFile file) {
        try {
            List<ExportUnlockWaresDTO> exportUnlockWaresDTOS = ExportExcelUtils.readExcel(file.getInputStream(), ExportUnlockWaresDTO.class);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return Wrapper.error(ErrorCodeEnum.GL88880002);
	}

	/***
     * @Description: 导出
     * @Param: [campUnlockPageDTO]
     * @return: com.ycx.wrapper.Message<com.ycx.transfer.response.CampCourseUnlockPageDO>
     * @Author: zhaobinyang
     * @Date: 2020/7/1 17:55
     */
    @PostMapping(value = "/export")
    public Message<BaseDO> export(@RequestBody CampUnlockPageDTO campUnlockPageDTO, HttpServletResponse response) {
        List<CampCourseUnlockPage> campCourseUnlockPages = Arrays.asList();
        try {
            ExportExcelUtils.exportExcel(response,campCourseUnlockPages,CampCourseUnlockPage.class);
        } catch (Exception e) {
            throw new BusinessException(ErrorCodeEnum.GL88880000);
        }
        return Wrapper.success();
    }