1. /**
    2.  * 校验Excel模板工具类
    3.  */
    4. public class ExcelCheckUtil {
    6.     /**
    7.      * 检验导入Excel的格式是否正确
    8.      */
    9.     public static String checkExcelPattern(MultipartFile excel, Object obj) throws Exception {
    10.         if(excel == null || excel.isEmpty()){
    11.             throw new Exception("导入文件不能为空！");
    12.         }
    13.         //获取文件得拓展名
    14.         String extension = org.springframework.util.StringUtils.getFilenameExtension(excel.getOriginalFilename());
    15.         if (!Objects.equals(extension,"xlsx")){
    16.             throw new Exception("仅支持以.xlsx为拓展名的文件格式");
    17.         }
    18.         //用于拼接校验结果
    19.         StringBuilder builder = new StringBuilder();
    20.         //校验文件表头
    21.         BufferedInputStream header = new BufferedInputStream(excel.getInputStream());
    22.         //读取XLS和XLSX文件
    23.         Workbook wb = WorkbookFactory.create(header);
    24.         //获取注解当中的值
    25.         Map<String, List<String>> annotationValue = getAnnotationValue(obj);
    26.         List<String> annotationName = annotationValue.get("annotationName");
    27.         //获取到的实体注解名称顺序要与excel表头顺序保持一样
    28.         String[] columnName = annotationName.toArray(new String[]{});
    29.         Sheet sheet = wb.getSheetAt(0);
    30.         Row row = sheet.getRow(0);
    31.         if (row != null && row.getLastCellNum() >= columnName.length) {
    32.             int lastCellNum = row.getLastCellNum();
    33.             for (int idx = 0; idx < lastCellNum; idx++) {
    34.                 String value = getCellValue(row.getCell(idx)).trim();
    35.                 if (idx <  columnName.length) {
    36.                     if (StringUtils.isBlank(value) || !columnName[idx].equals(value)) {
    37.                         builder.append("第" + (idx + 1) + "列表头应为" + columnName[idx]+"!");
    38.                     }
    39.                 } else {
    40.                     if (idx ==  columnName.length) {
    41.                         builder.append("导入文件只应该有:"+ columnName.length+"!");
    42.                     }
    43.                 }
    44.             }
    45.         } else {
    46.             builder.append("上传文件首行不能为空，且应与模板文件表头保持一致；");
    47.         }
    48.         if(builder.length()>0){
    49.             builder.append("请下载模板按照模板表头顺序进行上传!");
    50.         }
    51.         return builder.toString();
    52.     }
    55.     /**
    56.      * 获取Excel单元格的值
    57.      */
    58.     private static String getCellValue(Cell cell) {
    59.         String cellValue;
    60.         // 以下是判断数据的类型
    61.         switch (cell.getCellType()) {
    62.             case Cell.CELL_TYPE_NUMERIC: // 数字
    63.                 if (DateUtil.isCellDateFormatted(cell)) {
    64.                     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    65.                     cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
    66.                 } else {
    67.                     DataFormatter dataFormatter = new DataFormatter();
    68.                     cellValue = dataFormatter.formatCellValue(cell);
    69.                 }
    70.                 break;
    71.             case Cell.CELL_TYPE_STRING: // 字符串
    72.                 cellValue = cell.getStringCellValue();
    73.                 break;
    74.             case Cell.CELL_TYPE_BOOLEAN: // Boolean
    75.                 cellValue = String.valueOf(cell.getBooleanCellValue());
    76.                 break;
    77.             case Cell.CELL_TYPE_FORMULA: // 公式
    78.                 cellValue = cell.getCellFormula();
    79.                 break;
    80.             case Cell.CELL_TYPE_BLANK: // 空值
    81.                 cellValue = "";
    82.                 break;
    83.             case Cell.CELL_TYPE_ERROR: // 故障
    84.                 cellValue = "非法字符";
    85.                 break;
    86.             default:
    87.                 cellValue = "未知类型";
    88.                 break;
    89.         }
    90.         return cellValue;
    91.     }
    93.     /**
    94.      * 获取@ExcelField注解对象的字段名称以及注解的title值
    95.      *      -annotationList：存储@ExcelField注解对象的字段名称
    96.      *      -fieldList：存储@ExcelField注解的title值
    97.      */
    98.     public static Map<String,List<String>> getAnnotationValue(Object obj) {
    99.         Map<String,List<String>>map=new HashMap<>();
    100.         List<String> fieldList=new ArrayList<>();
    101.         List<String> annotationList=new ArrayList<>();
    102.         Field[] fields = obj.getClass().getDeclaredFields();
    103.         for(int i = 0 ; i < fields.length ; i++) {
    104.             //设置是否允许访问，不是修改原来的访问权限修饰词。
    105.             fields[i].setAccessible(true);
    106.             ExcelField annotation = fields[i].getAnnotation(ExcelField.class);
    107.             if (Objects.isNull(annotation)) continue;
    108.             String value = annotation.title();
    109.             /*for (String s : value) {
    110.                 annotationList.add(s);
    111.             }*/
    112.             annotationList.add(value);
    113.             fieldList.add(fields[i].getName());
    114.         }
    115.         map.put("fieldName",fieldList);
    116.         map.put("annotationName",annotationList);
    117.         return map;
    118.     }
    120. }