SpringBoot实现简单的Excel导入操作

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

首先定义一个注解用于实现Excel中的表头与字段名相对应

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

使用注解定义DTO

@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;

}

再定义一个类用于判断和转换Excel

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<String, String> getTitleAlias(Class entityClass) {
        Map<String, String> 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;
    }
    /** 导出数据模板 */
    public static <T> void export(String type, T data, Class<T> clazz){
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = requestAttributes.getResponse();
        ExcelWriter writer = ExcelUtil.getWriter();
        Map<String, String> title = ExcelPrepareUtil.getTitleAlias(clazz);
        writer.setHeaderAlias(title);
        if (data != null) {
            writer.write(Collections.singletonList(data));
        }
        try {
            ServletOutputStream outputStream = response.getOutputStream();
            response.setHeader("Content-Disposition", "attachment;filename=" + type + ".xls");
            writer.flush(outputStream);
            writer.close();
            IoUtil.close(outputStream);
        } catch (IOException e) {
            throw BusinessException.defaultException("导出模版失败");
        }
    }
}

service层实现

@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<String, String> titleAlias = new HashMap<>(10);
            //将EXCEL数据转换为被注解标注过字段的数据
            ExcelPrepareUtil.getTitleAlias(StoreInfoExportDto.class).forEach((key, value) -> titleAlias.put(value, key));
            reader.setHeaderAlias(titleAlias);
            //将转化后的数据对象存储到list中
            List<StoreEntity> 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("上传导入失败!");
        }
    }
}

导出模板

  	@Override
    public void exportStore() {
        StoreEntity infoExportDto = new StoreEntity();
        infoExportDto.setStoreName("小王店铺");
        infoExportDto.setStoreTypeName("餐饮");
        infoExportDto.setLongitude("120.354");
        infoExportDto.setLatitude("29.144");
        infoExportDto.setNote("小王店铺备注");
        export("Store",infoExportDto,StoreEntity.class);
    }