通过自定义注解+反射的形式，使用POI实现excel的导入导出

谁践踏了优雅 2023-06-24 13:28 14阅读 0赞

## ##

### 自定义注解： ###

package com.example.demo.annotation;
    
    import java.lang.annotation.ElementType;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
    
    /**
     * Created by linjiaming
     */
    @Target(ElementType.FIELD)
    @Retention(RetentionPolicy.RUNTIME)
    public @interface ExcelAnnotation {
        String headName();
        int order();
        String datePattern() default "yyyyMMdd HH:mm:ss";
        enum DataType {
            String,Number,Date,
        }
        /**
         * 数据类型，可以是String，Number(数字型)，Date等类型
         * @return
         */
        DataType type() default DataType.String;
    
    }

### 实体类： ###

package com.example.demo.entity;
    
    import com.example.demo.annotation.ExcelAnnotation;
    import com.example.demo.annotation.ExcelAnnotation.DataType;
    import java.util.Date;
    import lombok.Data;
    
    @Data
    public class Student {
    
        private Long id;
        @ExcelAnnotation(headName = "学号", order =0)
        private String stuNo;
        @ExcelAnnotation(headName = "姓名", order =1)
        private String name;
        @ExcelAnnotation(headName = "学院", order =3)
        private String academy;
        @ExcelAnnotation(headName = "专业",order = 4)
        private String major;
        @ExcelAnnotation(headName = "年级", order = 5)
        private String grade;
        @ExcelAnnotation(headName = "班级", order = 6)
        private String classes;
        @ExcelAnnotation(headName = "年龄", order = 2)
        private String age;
        @ExcelAnnotation(headName = "入学日期", order = 7, datePattern ="yyyy/MM/dd HH:mm:ss",type = DataType.Date)
        private Date entryDate;
    
    
    }

### excel导入导出工具类： ###

/**
     * Created by linjiaming
     */
    public class ExcelUtils {
    
        private static ExcelUtils instance;
    
        private ExcelUtils(){}
    
        /**
         * 单例模式
         * @return
         */
        public static ExcelUtils getInstance() {
            if (instance == null) {
                instance = new ExcelUtils();
            }
            return instance;
        }
    
        /**
         * excel的导出
         * @param out
         * @param infos
         */
        public void exportExcel (OutputStream out, List<?> infos) {
            try {
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
                XSSFSheet sheet = xssfWorkbook.createSheet();
                sheet.createRow(0);
    
                Map<Field,Integer> map = new LinkedHashMap<>();
                for (Object o : infos ) {
                    Field[] fields = o.getClass().getDeclaredFields();
                    for (Field field : fields) {
                        if (field.isAnnotationPresent(ExcelAnnotation.class)) {
                            ExcelAnnotation annotation = field.getAnnotation(ExcelAnnotation.class);
                            map.put(field, annotation.order());
                        }
                    }
                }
                List<Entry<Field,Integer>> list = new ArrayList<Entry<Field,Integer>>(map.entrySet());
                Collections.sort(list, (o1, o2) -> o1.getValue().compareTo(o2.getValue()));
    
                List<Field> excelFields = new ArrayList<>();
                for(Entry<Field,Integer> map1 : list){
                    excelFields.add(map1.getKey());
                }
    
                List<ExcelAnnotation> annotations = new ArrayList<>();
                for (Field excelField : excelFields) {
                    annotations.add(excelField.getAnnotation(ExcelAnnotation.class));
                }
                addDataToExcel(xssfWorkbook, infos, excelFields, annotations, sheet);
                xssfWorkbook.write(out);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        private <T> void addDataToExcel(XSSFWorkbook wb, List<T> dataset,List<Field> excelFields, List<ExcelAnnotation> attributes,
                Sheet sheet)
                throws IllegalAccessException, NoSuchMethodException, InvocationTargetException, ParseException {
            XSSFCellStyle style = wb.createCellStyle();
            // 居中
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
    
            // excel放入第一行列的名称
            Row row = sheet.createRow(0);
            for (int j = 0; j < excelFields.size(); j++) {
                Cell cell = row.createCell(j);
                ExcelAnnotation oneAttribute = attributes.get(j);
                cell.setCellValue(oneAttribute.headName());
                cell.setCellStyle(style);
            }
            // 添加数据到excel
            for(int i=0;i<dataset.size();i++) {
                // 数据行号从1开始,因为第0行放的是列的名称
                row = sheet.createRow(i+1);
                for(int j=0;j<attributes.size();j++) {
                    Cell cell = row.createCell(j);
                    ExcelAnnotation annotation = attributes.get(j);
                    style = wb.createCellStyle();
                    // 居中
                    style.setAlignment(HorizontalAlignment.CENTER);
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 四个边框
                    style.setBorderBottom(BorderStyle.THIN);
                    style.setBorderLeft(BorderStyle.THIN);
                    style.setBorderRight(BorderStyle.THIN);
                    style.setBorderTop(BorderStyle.THIN);
                    cell.setCellStyle(style);
                    // 根据属性名获取属性值
                    String cellValue = BeanUtils.getProperty( dataset.get(i), excelFields.get(j).getName());
                    if (DataType.Date.equals(annotation.type())){
                       String date = DateTimeUtil
                                .getFormatDateFromGLWZString(cellValue, annotation.datePattern());
                       cell.setCellValue(date);
                    }else {
                        cell.setCellValue(cellValue);
                    }
                }
            }
    
        }
    
        /**
         * excel的导入
         * @param inputStream
         * @param clazz
         * @return
         * @throws IOException
         * @throws InstantiationException
         * @throws IllegalAccessException
         * @throws InvocationTargetException
         * @throws NoSuchMethodException
         */
        public  List<?> importExcel(InputStream inputStream, Class<?> clazz)
                throws IOException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            Row titleCell = sheet.getRow(0);
            List<Object> dataList = new ArrayList<>(sheet.getLastRowNum());
            Object datum;
            Map<String, Field> fieldMap = getFieldMap(clazz);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                datum = clazz.newInstance();
                int minCell = row.getFirstCellNum();
                int maxCell = row.getLastCellNum();
                for (int cellNum = minCell; cellNum <= maxCell; cellNum++) {
                    Cell title = titleCell.getCell(cellNum);
                    if (title == null) {
                        continue;
                    }
                    String tag = title.getStringCellValue();
                    Field field = fieldMap.get(tag);
                    if (field == null) {
                        continue;
                    }
                    Class<?> type = field.getType();
                    Object value = null;
                    Cell cell = row.getCell(cellNum);
                    if (cell == null) {
                        continue;
                    }
                    if (type.equals(Date.class)){
                        value = cell.getDateCellValue();
                    } else {
                        value = cell.getStringCellValue();
                    }
                    PropertyUtils.setProperty(datum, field.getName(), value);
                }
                dataList.add(datum);
            }
            return dataList;
        }
    
        /**
         * key :headName  val:该名称对应的字段
         * @param clazz
         * @param <T>
         * @return
         */
        private static <T> Map<String, Field> getFieldMap(Class<T> clazz) {
            Field[] fields = clazz.getDeclaredFields();
            Map<String, Field> fieldMap = new HashMap<>();
            for (Field field : fields) {
                if (field.isAnnotationPresent(ExcelAnnotation.class)) {
                    ExcelAnnotation annotation = field.getAnnotation(ExcelAnnotation.class);
                    fieldMap.put(annotation.headName(), field);
                }
            }
            return fieldMap;
        }

@Slf4j
    public class DateTimeUtil {
        private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
    
        /**
         * 获得所需要的日期格式
         *
         * @param date
         * @param format yyyy-MM-dd HH:mm:ss
         * @return
         */
        public static Date getFormatDateFromString(String date, String format) {
            SimpleDateFormat formatter = new SimpleDateFormat(format);
            Date formatDate = null;
            try {
                formatDate = formatter.parse(date);
            } catch (ParseException e) {
                e.printStackTrace();
            }
            return formatDate;
        }
        
        public static String getFormatDateFromGLWZString(String strdate, String format)
                throws ParseException {
    
            DateFormat df = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);
    
            Date date =df.parse(strdate);
    
    //     System.out.println(date.toString());
    
            SimpleDateFormat sdf=new SimpleDateFormat(format);
    
            return sdf.format(date);
    
        }
    }

### 简单测试： ###

public static void main(String[] args)
                throws IOException, InvocationTargetException, NoSuchMethodException, InstantiationException, IllegalAccessException {
            ExcelUtils excelUtils = getInstance();
            String filepath = "D:\\学生信息表.xlsx";
            File file = new File(filepath);
    
            List<?> objects =  excelUtils.importExcel(new FileInputStream(file), Student.class);
            System.out.println(objects);
        }