springboot 通过注解导入excel数据

前端页面代码可以参考 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 保持一致即可

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Spring Boot可以使用EasyExcel来实现Excel导入数据。EasyExcel是阿里基于poi开源的一个项目,它可以帮助我们更方便地实现Excel导入与导出功能。在Spring Boot中使用EasyExcel,我们可以综合应用各种Spring知识,代码量并不大。首先,在Controller层,我们可以提供一个访问接口,通过POST请求方式传入Excel文件。在请求中,我们需要将文件一同传入,并获取文件名用于后续判断是否为Excel文件。然后,我们可以调用Service层的batchImport方法进行业务逻辑处理。在Service层,我们可以使用EasyExcel提供的API来读取Excel文件中的数据,并进行相应的处理。最后,我们可以返回处理结果给前端。这样,就可以实现Spring Boot中的Excel导入数据功能。\[1\]\[2\]\[3\] #### 引用[.reference_title] - *1* *3* [SpringBoot 注解 + 反射优雅的实现Excel导入导出,通用加强版!](https://blog.csdn.net/afreon/article/details/126756870)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^koosearch_v1,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* [SpringBoot提供接口实现Excel导入数据并存储到数据库中](https://blog.csdn.net/m0_51197424/article/details/124454553)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^koosearch_v1,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值