CRM项目后端完成解析Excel文件实现上传线索列表到数据库的效果实现------CRM项目

旧约Alatus

已于 2024-02-28 12:22:51 修改

阅读量851

点赞数 22

分类专栏： CRM项目 # Spring-Boot框架 # Mybatis框架文章标签： mybatis 后端 java spring boot spring excel spring cloud

于 2024-02-28 10:42:10 首次发布

本文链接：https://blog.csdn.net/2201_75960169/article/details/136339537

版权

Spring-Boot框架同时被 3 个专栏收录

182 篇文章 0 订阅

订阅专栏

CRM项目

142 篇文章 0 订阅

订阅专栏

Mybatis框架

132 篇文章 0 订阅

订阅专栏

package com.alatus.config.listener;

import com.alatus.mapper.TClueMapper;
import com.alatus.model.TClue;
import com.alatus.model.TUser;
import com.alatus.util.JSONUtils;
import com.alatus.util.JWTUtils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import lombok.extern.slf4j.Slf4j;

import java.util.Date;
import java.util.List;

/**
 * 每读一行Excel的数据，就会触发该监听器中的invoke()方法，Excel读完之后会触发该监听器中的doAfterAllAnalysed()方法
 */
@Slf4j
public class UploadDataListener implements ReadListener<TClue> {

    /**
     * 每隔100条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;

    //缓存List
    private List<TClue> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private TClueMapper tClueMapper;

    private String token;

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param tClueMapper
     */
    public UploadDataListener(TClueMapper tClueMapper, String token) {
        this.tClueMapper = tClueMapper;
        this.token = token;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param tClue    one row value. It is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(TClue tClue, AnalysisContext context) {
        log.info("读取到的每一条数据:{}", JSONUtils.toJSON(tClue));
        //给读到的clue对象设置创建时间(导入时间)和创建人（导入人）
        tClue.setCreateTime(new Date());
        TUser tUser = JWTUtils.parseUserFromJWT(token);
        tClue.setCreateBy(tUser.getId());
        //每读取一行，就把该数据放入到一个缓存List中
        cachedDataList.add(tClue);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            //把缓存list中的数据写入到数据库
            saveData();

            //存储完成清空list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        tClueMapper.saveClue(cachedDataList);
        log.info("存储数据库成功！");
    }
}

package com.alatus.config.listener;

import com.alatus.mapper.TClueMapper;
import com.alatus.model.TClue;
import com.alatus.model.TUser;
import com.alatus.util.JSONUtils;
import com.alatus.util.JWTUtils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import lombok.extern.slf4j.Slf4j;

import java.util.Date;
import java.util.List;

/**
 * 每读一行Excel的数据，就会触发该监听器中的invoke()方法，Excel读完之后会触发该监听器中的doAfterAllAnalysed()方法
 */
@Slf4j
public class UploadDataListener implements ReadListener<TClue> {

    /**
     * 每隔100条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;

    //缓存List
    private List<TClue> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private TClueMapper tClueMapper;

    private String token;

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param tClueMapper
     */
    public UploadDataListener(TClueMapper tClueMapper, String token) {
        this.tClueMapper = tClueMapper;
        this.token = token;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param tClue    one row value. It is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(TClue tClue, AnalysisContext context) {
        log.info("读取到的每一条数据:{}", JSONUtils.toJSON(tClue));
        //给读到的clue对象设置创建时间(导入时间)和创建人（导入人）
        tClue.setCreateTime(new Date());
        TUser tUser = JWTUtils.parseUserFromJWT(token);
        tClue.setCreateBy(tUser.getId());
        //每读取一行，就把该数据放入到一个缓存List中
        cachedDataList.add(tClue);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            //把缓存list中的数据写入到数据库
            saveData();

            //存储完成清空list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        tClueMapper.saveClue(cachedDataList);
        log.info("存储数据库成功！");
    }
}

package com.alatus.service.impl;

import com.alatus.config.listener.UploadDataListener;
import com.alatus.constant.Constants;
import com.alatus.mapper.TClueMapper;
import com.alatus.model.TClue;
import com.alatus.query.BaseQuery;
import com.alatus.service.ClueService;
import com.alibaba.excel.EasyExcel;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Service;

import java.io.InputStream;
import java.util.List;

@Service
public class ClueServiceImpl implements ClueService {
    @Resource
    private TClueMapper tClueMapper;
    @Override
    public PageInfo<TClue> getClueByPage(Integer current) {
        // 1.设置PageHelper
        PageHelper.startPage(current, Constants.PAGE_SIZE);
        // 2.查询
        List<TClue> list = tClueMapper.selectClueByPage(BaseQuery.builder().build());
        // 3.封装分页数据到PageInfo
        PageInfo<TClue> info = new PageInfo<>(list);
        return info;
    }

    @Override
    public void importExcel(InputStream inputStream,String token) {
//        第一个参数是文件,第二个参数是excel模板类,第三个是监听器
        EasyExcel.read(inputStream,TClue.class,new UploadDataListener(tClueMapper,token)).sheet().doRead();
    }
}

package com.alatus.service.impl;

import com.alatus.config.listener.UploadDataListener;
import com.alatus.constant.Constants;
import com.alatus.mapper.TClueMapper;
import com.alatus.model.TClue;
import com.alatus.query.BaseQuery;
import com.alatus.service.ClueService;
import com.alibaba.excel.EasyExcel;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Service;

import java.io.InputStream;
import java.util.List;

@Service
public class ClueServiceImpl implements ClueService {
    @Resource
    private TClueMapper tClueMapper;
    @Override
    public PageInfo<TClue> getClueByPage(Integer current) {
        // 1.设置PageHelper
        PageHelper.startPage(current, Constants.PAGE_SIZE);
        // 2.查询
        List<TClue> list = tClueMapper.selectClueByPage(BaseQuery.builder().build());
        // 3.封装分页数据到PageInfo
        PageInfo<TClue> info = new PageInfo<>(list);
        return info;
    }

    @Override
    public void importExcel(InputStream inputStream,String token) {
//        第一个参数是文件,第二个参数是excel模板类,第三个是监听器
        EasyExcel.read(inputStream,TClue.class,new UploadDataListener(tClueMapper,token)).sheet().doRead();
    }
}

package com.alatus.service;

import com.alatus.model.TClue;
import com.github.pagehelper.PageInfo;

import java.io.InputStream;

public interface ClueService {
    PageInfo<TClue> getClueByPage(Integer current);

    void importExcel(InputStream inputStream,String token);
}

package com.alatus.service;

import com.alatus.model.TClue;
import com.github.pagehelper.PageInfo;

import java.io.InputStream;

public interface ClueService {
    PageInfo<TClue> getClueByPage(Integer current);

    void importExcel(InputStream inputStream,String token);
}

package com.alatus.web;

import com.alatus.constant.Constants;
import com.alatus.model.TClue;
import com.alatus.result.Result;
import com.alatus.service.ClueService;
import com.github.pagehelper.PageInfo;
import jakarta.annotation.Resource;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;

@RestController
public class ClueController {
    @Resource
    private ClueService clueService;
    @GetMapping(value = "/api/clues")
    public Result cluePage(@RequestParam(value = Constants.CURRENT,required = false)Integer current){
        if(current == null){
            current = 1;
        }
        PageInfo<TClue> pageInfo = clueService.getClueByPage(current);
        return Result.OK(pageInfo);
    }
    @PostMapping(value = "/api/club/importExcel")
    public Result importExcel(MultipartFile file, @RequestHeader(value = Constants.TOKEN_NAME)String token) throws IOException {
//        接收到的文件名字需要和前端提交的名字相同,否则无法接收
        clueService.importExcel(file.getInputStream(),token);
        return Result.OK();
    }
}

package com.alatus.web;

import com.alatus.constant.Constants;
import com.alatus.model.TClue;
import com.alatus.result.Result;
import com.alatus.service.ClueService;
import com.github.pagehelper.PageInfo;
import jakarta.annotation.Resource;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;

@RestController
public class ClueController {
    @Resource
    private ClueService clueService;
    @GetMapping(value = "/api/clues")
    public Result cluePage(@RequestParam(value = Constants.CURRENT,required = false)Integer current){
        if(current == null){
            current = 1;
        }
        PageInfo<TClue> pageInfo = clueService.getClueByPage(current);
        return Result.OK(pageInfo);
    }
    @PostMapping(value = "/api/club/importExcel")
    public Result importExcel(MultipartFile file, @RequestHeader(value = Constants.TOKEN_NAME)String token) throws IOException {
//        接收到的文件名字需要和前端提交的名字相同,否则无法接收
        clueService.importExcel(file.getInputStream(),token);
        return Result.OK();
    }
}

---
spring:
  data:
    redis:
      lettuce:
        pool:
          max-idle: 8
          min-idle: 0
          max-wait: -1ms
          max-active: 8
        cluster:
          refresh:
            adaptive: true
            period: 2000
      cluster:
        max-redirects: 3
        nodes: 192.168.127.128:6381,192.168.127.128:6382,192.168.127.130:6383,192.168.127.130:6384,192.168.127.129:6385,192.168.127.129:6386
      password: abc123
      timeout: 5000
---
server:
  port: 8089
  servlet:
    context-path: /
    session:
      persistent: false
---
spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/crm_system?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: abc123
    hikari:
      maximum-pool-size: 30
      minimum-idle: 30
      connection-timeout: 5000
      idle-timeout: 0
      max-lifetime: 18000000
#  设置jackson返回json数据时的时区格式
  jackson:
    time-zone: GMT+8
    date-format: yyyy-MM-dd HH:mm:ss
---
mybatis:
  mapper-locations: classpath:mapper/*.xml
#  configuration:
#    控制台打印SQL语句
#    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
---
#定义任务调度时间
project:
  task:
    cron: '* */60 * * * *'
    delay: 3600000

---
spring:
  data:
    redis:
      lettuce:
        pool:
          max-idle: 8
          min-idle: 0
          max-wait: -1ms
          max-active: 8
        cluster:
          refresh:
            adaptive: true
            period: 2000
      cluster:
        max-redirects: 3
        nodes: 192.168.127.128:6381,192.168.127.128:6382,192.168.127.130:6383,192.168.127.130:6384,192.168.127.129:6385,192.168.127.129:6386
      password: abc123
      timeout: 5000
---
server:
  port: 8089
  servlet:
    context-path: /
    session:
      persistent: false
---
spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/crm_system?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: abc123
    hikari:
      maximum-pool-size: 30
      minimum-idle: 30
      connection-timeout: 5000
      idle-timeout: 0
      max-lifetime: 18000000
#  设置jackson返回json数据时的时区格式
  jackson:
    time-zone: GMT+8
    date-format: yyyy-MM-dd HH:mm:ss
---
mybatis:
  mapper-locations: classpath:mapper/*.xml
#  configuration:
#    控制台打印SQL语句
#    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
---
#定义任务调度时间
project:
  task:
    cron: '* */60 * * * *'
    delay: 3600000

package com.alatus;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.util.HashMap;
import java.util.Map;

@SpringBootApplication
@MapperScan(basePackages = {"com.alatus.mapper"})
public class NetCrmServerApplication {
    public static final Map<String,Object> cacheMap = new HashMap<>();

    public static void main(String[] args) {
        SpringApplication.run(NetCrmServerApplication.class, args);
    }
}

package com.alatus;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.util.HashMap;
import java.util.Map;

@SpringBootApplication
@MapperScan(basePackages = {"com.alatus.mapper"})
public class NetCrmServerApplication {
    public static final Map<String,Object> cacheMap = new HashMap<>();

    public static void main(String[] args) {
        SpringApplication.run(NetCrmServerApplication.class, args);
    }
}