SpringBoot实现简单的Excel导入操作

本文简单的介绍SpringBoot+MongoDB下实现简单的Excel导入操作功能，关系型数据库是同样的道理，就不多做介绍了

@Persistent
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExcelName {
    String name() default "";
}

@Data
@ApiModel("商铺信息excel实体")
public class StoreInfoExportDto {

    @ApiModelProperty("店铺名称")
    @ExcelName(name = "店铺名称")
    @NotBlank(message = "店铺名称不能为空")
    private String storeName;

    @ApiModelProperty("店铺地址")
    @ExcelName(name = "店铺地址")
    @NotBlank(message = "店铺地址不能为空")
    private String storeAddress;

    @ApiModelProperty("店铺类型 1:餐饮 2:住宿")
    @ExcelName(name = "店铺类型")
    @NotNull(message = "店铺类型不能为空 1:餐饮 2:住宿")
    private String storeTypeName;

    @ApiModelProperty("经度")
    @ExcelName(name = "经度")
    @NotBlank(message = "经度不能为空")
    private String longitude;

    @ApiModelProperty("纬度")
    @ExcelName(name = "纬度")
    @NotBlank(message = "纬度不能为空")
    private String latitude;

    @ApiModelProperty("状态 1开启2关闭")
    @ExcelName(name = "状态")
    @NotNull(message = "状态不能为空 1开启2关闭")
    private String statusName;

    @ApiModelProperty("备注")
    @ExcelName(name = "备注")
    @NotBlank(message = "备注不能为空")
    private String note;
}

@Data
@ApiModel("店铺")
@NoArgsConstructor
@document(collection = "store")
public class StoreEntity  {
    private static final long serialVersionUID = 6702105957344005879L;
    @Id
    @NotBlank(message = "id不能为空", groups = {Update.class})
    private String id;
    @ApiModelProperty("店铺名称")
    @NotBlank(message = "店铺名称不能为空")
    private String storeName;
    @ApiModelProperty("店铺地址")
    @NotBlank(message = "店铺地址不能为空")
    private String storeAddress;
    @ApiModelProperty("店铺类型 1:餐饮 2:住宿")
    @NotNull(message = "店铺类型不能为空 1:餐饮 2:住宿")
    private Integer storeType;
    @ApiModelProperty("店铺类型 1:餐饮 2:住宿")
    private String storeTypeName;
    @ApiModelProperty("经度")
    @NotBlank(message = "经度不能为空")
    private String longitude;
    @ApiModelProperty("纬度")
    @NotBlank(message = "纬度不能为空")
    private String latitude;
    @ApiModelProperty("状态 1开启2关闭")
    @NotNull(message = "状态不能为空 1开启2关闭")
    private Integer status;
    @ApiModelProperty("状态 1开启2关闭")
    private String statusName;
    @ApiModelProperty("备注")
    private String note;
    
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern ="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    private Date createTime;

}

public class ExcelPrepareUtil {

    private ExcelPrepareUtil(){}
	//本方法用于判断文件格式与文件大小
    public static void checkFile(MultipartFile file, int limitSize, String... limitType) {
        Assert.isTrue(limitSize > 0,"文件限制大小必须大于0");
        if (file.getSize() > limitSize) {
            throw new Exception("最大文件尺寸为:" + (limitSize / 1024 / 1024) + "M");
        }
        if (limitType != null) {
            String fileName = file.getOriginalFilename();
            String suffix = fileName.substring(fileName.lastIndexOf('.') + 1, fileName.length());
            boolean contains = Arrays.asList(limitType).contains(suffix.toLowerCase());
            if (!contains) {
                throw new Exception("只允许上传以下类型文件:" + Arrays.toString(limitType) + "");
            }
        }
    }
	//本方法用于将表头名与字段名相对应
    public static Map getTitleAlias(Class entityClass) {
        Map title = new linkedHashMap<>();
        Field[] declaredFields = entityClass.getDeclaredFields();
        Arrays.stream(declaredFields)
        	  .forEachOrdered(field ->title.put(field.getName(), Optional.ofNullable(field.getAnnotation(ExcelName.class))
        	  .flatMap(excelName -> Optional.of(excelName.name()))
        	  .filter(s -> !StrUtil.isBlank(s)).orElseGet(field::getName)));
        return title;
    }
}

@Slf4j
@Service
public class importExcelDataServiceImpl implements importExcelDataService {

    @Override
    public void importStore(MultipartFile multipartFile, String type) {
        ExcelPrepareUtil.checkFile(multipartFile, 5 * 1024 * 1024, "xlsx", "xls");
        try {
        	//获取到数据流
            ExcelReader reader = ExcelUtil.getReader(multipartFile.getInputStream());
            Map titleAlias = new HashMap<>(10);
            //将EXCEL数据转换为被注解标注过字段的数据
            ExcelPrepareUtil.getTitleAlias(StoreInfoExportDto.class).forEach((key, value) -> titleAlias.put(value, key));
            reader.setHeaderAlias(titleAlias);
            //将转化后的数据对象存储到list中
            List storeInfo = reader.readAll(StoreEntity.class);
            //获取到list之后就该干嘛干嘛了
            if (storeInfo != null & storeInfo.size() > 0) {
                for (Store store : storeInfo) {
                    //判断参数是否符合
                    //插入到数据库
                }
            }
        } catch (Exception e) {
            log.error("上传导入失败!",e);
            throw BusinessException.defaultException("上传导入失败!");
        }
    }
}