前端页面代码可以参考 ant vue文件上传
action层
@RestController
@RequestMapping("common")
@Api(tags = "共通Action")
public class CommonAction{
@Autowired
private ITemplateService templateService;
@PostMapping(value = "/exportTemplate.xxxx")
@ApiOperation(value = "POST 导出模板",notes = "根据传入类型下载对应的模板", consumes = "application/json", produces = "application/json")
public RetType exportTemplate(@RequestBody TemplateDto dto, HttpServletResponse response){
return templateService.exportTemplate(dto,response);
}
@PostMapping(value = "/importTemplate.xxxx")
@ApiOperation(value = "POST 导入模板",notes = "根据传入类型解析对应模板数据", consumes = "application/json", produces = "application/json")
public RetType importTemplate(@RequestParam(value = "file") MultipartFile file,TemplateDto dto){
return templateService.importTemplate(file,dto);
}
}
service层
/**
* 根据传入类型下载对应的模板
*
* @Auther: czw
* @Description:
* @Date: 2022/6/8 17:11
* @Version: 1.0
*/
@Override
public RetType exportTemplate(TemplateDto dto, HttpServletResponse response) {
RetType retType = new RetType();
if (StrUtil.isEmpty(dto.getType())) {
retType.doError("模板类型不能为空!");
return retType;
}
String path = "excel/" + dto.getType() + ".xlsx";
try {
InputStream in = FileUtil.getInputStream(path);
OutputStream out = response.getOutputStream();
IOUtils.copy(in, out);
out.flush();
out.close();
in.close();
retType.doSuccess();
} catch (Exception e) {
e.printStackTrace();
retType.doError(e.getMessage());
}
return retType;
}
/**
* 解析模板上传的数据
*
* @Auther: czw
* @Description:
* @Date: 2022/6/8 17:53
* @Version: 1.0
*/
@Override
public RetType importTemplate(MultipartFile file, TemplateDto dto) {
RetType retType = new RetType();
if (ObjectUtil.isEmpty(file)) {
retType.doError("导入的模板不能为空");
return retType;
}
//导入店铺
if (dto.getType().equals("store")) {
retType = HutoolExcelUtil.importExcel(file, StoreImportDto.class);
if (retType.isSuccess()) {
List<StoreImportDto> storeList = JSON.parseArray(JSON.toJSONString(retType.getData()), StoreImportDto.class);
retType = importStoreInfo(storeList, dto);
}
} else if (dto.getType().equals("storeManage")) { //导入店铺负责人
retType = HutoolExcelUtil.importExcel(file, StoreManageImportDto.class);
if (retType.isSuccess()) {
List<StoreManageImportDto> storeManageList = JSON.parseArray(JSON.toJSONString(retType.getData()), StoreManageImportDto.class);
retType = importStoreManageInfo(storeManageList, dto);
}
} else if (dto.getType().equals("supplier")) { //导入厂商
retType = HutoolExcelUtil.importExcel(file, SupplierImportDto.class);
if (retType.isSuccess()) {
List<SupplierImportDto> supplierList = JSON.parseArray(JSON.toJSONString(retType.getData()), SupplierImportDto.class);
retType = importSupplierInfo(supplierList, dto);
}
}
return retType;
}
dto实体
/**
* @author czw
* @version 1.0
* @describe:
* @date 2022/06/08 17:13:36
*/
@Data
public class TemplateDto implements Serializable {
@ApiModelProperty(value = "模板类型")
private String type;
@ApiModelProperty(value = "企业id")
private String companyId;
@ApiModelProperty(value = "导入用户id")
private String userId;
}
HutoolExcelUtil工具类
/**
* Excel工具类
*/
public class HutoolExcelUtil {
public static void main(String[] args) {
HutoolExcelUtil.importExcel(null, null);
}
public static RetType importExcel(MultipartFile multipartFile, Class clazz) {
RetType ret = new RetType();
StringBuilder ins = new StringBuilder("导入数据出错:");
List list = new ArrayList();
boolean flag = false;
ExcelReader reader = null;
try {
reader = ExcelUtil.getReader(multipartFile.getInputStream(), 0);
} catch (IOException e) {
e.printStackTrace();
}
List<Map<String, Object>> readAll = reader.readAll();
//获取该实体所有定义的属性 返回Field数组
Field[] fields = clazz.getDeclaredFields();
int num = 1;
for (Map<String, Object> map : readAll) {
String tempIns = "第" + num + "行导入失败,失败原因是:";
boolean numErr = false;
//可以根据该类名生成Java对象
Object obj = null;
try {
obj = clazz.newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
//除自增编号外,实体字段匹配sheet列
for (Field field : fields) {
//设置些属性是可以访问的
field.setAccessible(true);
//获取属性的类型
boolean isAnn = field.isAnnotationPresent(ExcelAnnotation.class);
if (!isAnn) {
continue;
}
ExcelAnnotation ann = null;
try {
ann = field.getAnnotation(ExcelAnnotation.class);
} catch (Exception e) {
continue;
}
try {
ann = field.getAnnotation(ExcelAnnotation.class);
Object o = map.get(ann.rowName());
if (ann.isMust() && (o == null || o.equals(""))) {
tempIns += ann.rowName() + "列为空、";
// ins += num+"行"+ann.rowName()+"列为空\\t";
numErr = true;
flag = true;
}
String type = ann.type();
Object tempObj = map.get(ann.rowName());
switch (type) {
case "double":
if (tempObj instanceof Long) {
String t = tempObj.toString();
if (!StringUtil.isEmpty(t)) {
field.set(obj, Double.parseDouble(t));
} else {
field.set(obj, 0);
}
} else if (tempObj instanceof Double) {
field.set(obj, tempObj);
} else if (tempObj instanceof String) {
if (!StringUtil.isEmpty(tempObj.toString())) {
field.set(obj, Double.parseDouble(tempObj.toString()));
} else {
field.set(obj, 0);
}
}
break;
case "Double":
if (tempObj instanceof Long) {
String t = tempObj.toString();
if (!StringUtil.isEmpty(t)) {
field.set(obj, Double.parseDouble(t));
} else {
field.set(obj, 0);
}
} else if (tempObj instanceof Double) {
field.set(obj, tempObj);
} else if (tempObj instanceof String) {
if (!StringUtil.isEmpty(tempObj.toString())) {
field.set(obj, Double.parseDouble(tempObj.toString()));
} else {
field.set(obj, 0);
}
}
break;
case "long":
field.set(obj, (long) map.get(ann.rowName()));
break;
case "Long":
field.set(obj, (Long) map.get(ann.rowName()));
break;
case "int":
field.set(obj, (int) map.get(ann.rowName()));
break;
case "string":
field.set(obj, map.get(ann.rowName()).toString());
break;
case "String":
field.set(obj, map.get(ann.rowName()).toString());
break;
default:
field.set(obj, map.get(ann.rowName()));
break;
}
// field.set(obj, map.get(ann.rowName()));
} catch (Exception e) {
continue;
}
}
list.add(obj);
num++;
if (numErr) {
ins.append(tempIns.substring(0, tempIns.length() - 1) + "");
}
}
if (flag) {
ret.doError(ins.toString());
} else {
ret.doSuccess(list);
}
return ret;
}
}
maven依赖
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
解析实体
/**
* 门店导入实体
*
* @author czw
* @version 1.0
* @describe:
* @date 2022/06/09 11:51:32
*/
@Data
public class StoreImportDto implements Serializable {
@ExcelAnnotation(rowName = "店号", isMust = true)
private String storeNo;
@ExcelAnnotation(rowName = "店铺名称", isMust = true)
private String storeName;
@ExcelAnnotation(rowName = "店铺电话")
private String tel;
}
ExcelAnnotation注解
import java.lang.annotation.*;
@Target({ElementType.FIELD, ElementType.METHOD})
@Inherited
@Documented
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelAnnotation {
/**
* 用于指定excel列名
* @return
*/
String rowName() default "";
/**
* 是否为必填,默认为false
* @return
*/
boolean isMust() default false;
/**
* 非空时,替换的字段,为空或null时不替换
* @return
*/
String notEmpty() default "";
/**
* 占几列
* @return
*/
int col() default 1;
/**
* 占几行
* @return
*/
int row() default 1;
/**
* 列宽4000=30.53
* @return
*/
int width() default 4000;
/**
* 自动序列
* @return
*/
boolean autoSeq() default false;
/**
* 字段类型
* @return
*/
String type() default "String";
}
模板文件
使用说明
解析实体的 rowName 和 模板的 name 保持一致即可