自定义注解：
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;@Datapublic 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);}// 添加数据到excelfor(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;}
@Slf4jpublic 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);}
