本文简单的介绍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);
ExcelPrepareUtil.getTitleAlias(StoreInfoExportDto.class).forEach((key, value) -> titleAlias.put(value, key));
reader.setHeaderAlias(titleAlias);
List<StoreEntity> storeInfo = reader.readAll(StoreEntity.class);
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);
}