excel文档上传批量读取入库

结合上节下载模板实现数据批量导入功能(上节内容 https://blog.csdn.net/fcfs_sstf/article/details/136297469)
结合swagger注解 @ApiModelProperty 识别excel表头

构建实体类

import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@AllArgsConstructor
@NoArgsConstructor
@Data
@Builder
public class UserImport {
    @ApiModelProperty(value="用户名")
    private String username;

    @ApiModelProperty(value="姓名")
    private String realName;

    @ApiModelProperty(value="电话")
    private String phone;

    @ApiModelProperty(value="状态(1:启用 0：禁用)")
    private String status;
}

业务代码

 public Result importUserList(MultipartFile multipartFile) {
        List<UserImport> userImports = ExcelUtils.readExcel(multipartFile, UserImport.class);
        List<SysUser> list = new ArrayList<>();
        if (CollUtil.isNotEmpty(userImports)) {
            //业务代码....
        }
        return ResultUtil.success();
    }

主要实现方法

注意编码格式按实际情况配置

public static <T> List<T> readExcel(MultipartFile multipartFile, Class<T> clazz) {
        List<T> list = new LinkedList<>();
        try {
            BufferedReader reader = new BufferedReader(new InputStreamReader(multipartFile.getInputStream(), "GBK"));
            String line;
            int index = 1;
            String[] titles = null;
            while ((line = reader.readLine()) != null) {
                if (index == 1) {
                    titles = line.split(",");
                    index++;
                    continue;
                }
                String[] objs = line.split(",");
                List<Field> fields = Arrays.stream(clazz.getDeclaredFields()).collect(Collectors.toList());
                T object = clazz.newInstance();
                Map<String, List<Field>> map = fields.stream().collect(Collectors.groupingBy(item -> item.getAnnotation(ApiModelProperty.class) != null ? item.getAnnotation(ApiModelProperty.class).value().toLowerCase() : item.getAnnotation(ApiModelProperty.class).value().toLowerCase()));
                //添加值到对应字段
                if (titles != null && titles.length > 0) {
                    for (int i = 0; i < titles.length; i++) {
                        //判断当前字段名是否为空字符串
                        if (StringUtils.isBlank(titles[i])) {
                            continue;
                        }
                        Field field = map.get(titles[i].replace("\ufeff","").toLowerCase()) != null ? map.get(titles[i].replace("\ufeff","").toLowerCase()).get(0) : null;
                        if (field != null) {
                            //打开私有链接
                            field.setAccessible(true);
                            field.set(object, getValueByFieldType(objs[i], field.getType()));
                        }
                    }
                }
                list.add(object);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

public static Object getValueByFieldType(Object value, Class<?> fieldType) {
        //判断值是不是为空
        if (value == null || StringUtils.isBlank(value.toString())) {
            return null;
        }
        //判断该字段类型并赋值
        String typeStr = fieldType.getSimpleName();
        String v = String.valueOf(value).trim();
        if ("String".equalsIgnoreCase(typeStr)) {
            return v;
        } else if ("double".equalsIgnoreCase(typeStr)) {
            return Double.valueOf(v);
        } else if ("integer".equalsIgnoreCase(typeStr) || "int".equalsIgnoreCase(typeStr)) {
            if (value.toString().indexOf(".") != -1) {
                v = v.split("\\.")[0];
            }
            return Integer.valueOf(v);
        } else if ("float".equalsIgnoreCase(typeStr)) {
            return Float.valueOf(v);
        } else if ("long".equalsIgnoreCase(typeStr)) {
            return Long.valueOf(v);
        } else if ("BigDecimal".equalsIgnoreCase(typeStr)) {
            return new BigDecimal(v);
        } else if ("Short".equalsIgnoreCase(typeStr)) {
            return Short.valueOf(v);
        }
//        else if ("Date".equalsIgnoreCase(typeStr)) {
//            return DateUtils.stringToDate(value.toString(), DateUtils.getDateFormat(value.toString()));
//        }
        else if ("Byte".equalsIgnoreCase(typeStr)) {
            return Byte.parseByte(v);
        } else if ("Boolean".equalsIgnoreCase(typeStr)) {
            return Boolean.parseBoolean(v);
        }
        return null;
    }