springboot 通过注解导入excel数据

最新推荐文章于 2022-10-19 18:20:30 发布

clmm123

最新推荐文章于 2022-10-19 18:20:30 发布

阅读量358

点赞数

分类专栏： excel数据导入 spring boot 文章标签： spring boot excel 数据导入

本文链接：https://blog.csdn.net/baidu_37302589/article/details/125615167

版权

spring boot 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

excel数据导入

1 篇文章 0 订阅

订阅专栏

前端页面代码可以参考 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";
}