CRM项目后端使用EasyExcel实现将客户信息封装成xlxs文件提交到前端------CRM项目

于 2024-03-02 18:47:28 发布
阅读量894 收藏 21
点赞数 28
分类专栏： CRM项目 # Spring-Boot框架 # Mybatis框架 文章标签： java 后端 spring spring boot mybatis mysql excel
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2201_75960169/article/details/136415323
版权 
package com.alatus.web;

import com.alatus.constant.Constants;
import com.alatus.model.TCustomer;
import com.alatus.query.CustomerQuery;
import com.alatus.result.CustomerExcel;
import com.alatus.result.Result;
import com.alatus.service.CustomerService;
import com.alibaba.excel.EasyExcel;
import com.github.pagehelper.PageInfo;
import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.web.bind.annotation.*;

import java.io.IOException;
import java.util.List;

@RestController
public class CustomerController {
    @Resource
    private CustomerService customerService;

    @PostMapping(value = "/api/clue/customer")
    public Result transferCustomer(@RequestBody CustomerQuery customerQuery, @RequestHeader(value = Constants.TOKEN_NAME)String token){
        customerQuery.setToken(token);
        Boolean convert = customerService.convertCustomer(customerQuery);
        return convert ? Result.OK() : Result.FAIL();
    }
    @GetMapping(value = "/api/customers")
    public Result CustomersByPages(@RequestParam(value = Constants.CURRENT,required = false)Integer current){
        if(current == null){
            current = 1;
        }
        PageInfo<TCustomer> pageInfo = customerService.getCustomerByPage(current);
        return Result.OK(pageInfo);
    }
    @GetMapping(value = "/api/exportExcel")
    public void exportExcel(HttpServletResponse response) throws IOException {
        //要想让浏览器弹出下载框，后端要设置一下响应头信息
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        response.setHeader("X-Frame-Options", "allow-from uri");// 解决IFrame拒绝的问题
        response.setHeader("Content-disposition", "attachment;filename=123.xlxs");
        List<CustomerExcel> dataList = customerService.getCustomerByExcel();
        EasyExcel.write(response.getOutputStream(), CustomerExcel.class)
                .sheet()
                .doWrite(dataList);
    }
}

package com.alatus.web;

import com.alatus.constant.Constants;
import com.alatus.model.TCustomer;
import com.alatus.query.CustomerQuery;
import com.alatus.result.CustomerExcel;
import com.alatus.result.Result;
import com.alatus.service.CustomerService;
import com.alibaba.excel.EasyExcel;
import com.github.pagehelper.PageInfo;
import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.web.bind.annotation.*;

import java.io.IOException;
import java.util.List;

@RestController
public class CustomerController {
    @Resource
    private CustomerService customerService;

    @PostMapping(value = "/api/clue/customer")
    public Result transferCustomer(@RequestBody CustomerQuery customerQuery, @RequestHeader(value = Constants.TOKEN_NAME)String token){
        customerQuery.setToken(token);
        Boolean convert = customerService.convertCustomer(customerQuery);
        return convert ? Result.OK() : Result.FAIL();
    }
    @GetMapping(value = "/api/customers")
    public Result CustomersByPages(@RequestParam(value = Constants.CURRENT,required = false)Integer current){
        if(current == null){
            current = 1;
        }
        PageInfo<TCustomer> pageInfo = customerService.getCustomerByPage(current);
        return Result.OK(pageInfo);
    }
    @GetMapping(value = "/api/exportExcel")
    public void exportExcel(HttpServletResponse response) throws IOException {
        //要想让浏览器弹出下载框，后端要设置一下响应头信息
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        response.setHeader("X-Frame-Options", "allow-from uri");// 解决IFrame拒绝的问题
        response.setHeader("Content-disposition", "attachment;filename=123.xlxs");
        List<CustomerExcel> dataList = customerService.getCustomerByExcel();
        EasyExcel.write(response.getOutputStream(), CustomerExcel.class)
                .sheet()
                .doWrite(dataList);
    }
}

package com.alatus.constant;

import java.util.ResourceBundle;

public class Constants {
//    开始页地址
    public static final String LOGIN_URI = "/api/login";
//    redis的key命名规范,项目名:模块名:功能名:唯一业务参数(比如用户ID)
    public static final String REDIS_JWT_KEY = "crmSystem:user:login:";
//    七天时间
    public static final Long EXPIRE_TIME = 7 * 24 * 60 * 60L;
//    三十分钟
    public static final Long DEFAULT_EXPIRE_TIME = 30 * 60L;
//    分页时每页10条记录
    public static final int PAGE_SIZE = 10;
//    Token的名字
    public static final String TOKEN_NAME = "Authorization";
//    负责人的Key
    public static final String OWNER_KEY = "crm:user:owner";
//    五分钟
    public static final Long DEFAULT_KEY_EXPIRE_TIME = 5 * 60L;
//    管理员名字
    public static final String ADMIN = "admin";
//    记住我按钮
    public static final String REMEMBER_ME = "rememberMe";
//    ID
    public static final String ID = "id";

    public static final String IDS = "ids";

    public static final String CURRENT = "current";

    public static final String TYPECODE = "typeCode";
    public static final String PHONE = "phone";
    public static final String EMPTY = "";
    public static final String EXPORT_EXCEL_URI = "/api/exportExcel";
}
package com.alatus.constant;

import java.util.ResourceBundle;

public class Constants {
//    开始页地址
    public static final String LOGIN_URI = "/api/login";
//    redis的key命名规范,项目名:模块名:功能名:唯一业务参数(比如用户ID)
    public static final String REDIS_JWT_KEY = "crmSystem:user:login:";
//    七天时间
    public static final Long EXPIRE_TIME = 7 * 24 * 60 * 60L;
//    三十分钟
    public static final Long DEFAULT_EXPIRE_TIME = 30 * 60L;
//    分页时每页10条记录
    public static final int PAGE_SIZE = 10;
//    Token的名字
    public static final String TOKEN_NAME = "Authorization";
//    负责人的Key
    public static final String OWNER_KEY = "crm:user:owner";
//    五分钟
    public static final Long DEFAULT_KEY_EXPIRE_TIME = 5 * 60L;
//    管理员名字
    public static final String ADMIN = "admin";
//    记住我按钮
    public static final String REMEMBER_ME = "rememberMe";
//    ID
    public static final String ID = "id";

    public static final String IDS = "ids";

    public static final String CURRENT = "current";

    public static final String TYPECODE = "typeCode";
    public static final String PHONE = "phone";
    public static final String EMPTY = "";
    public static final String EXPORT_EXCEL_URI = "/api/exportExcel";
}
package com.alatus.config.filter;

import com.alatus.constant.Constants;
import com.alatus.model.TUser;
import com.alatus.result.Result;
import com.alatus.service.RedisService;
import com.alatus.util.JSONUtils;
import com.alatus.util.JWTUtils;
import com.alatus.util.ResponseUtils;
import com.alatus.result.CodeEnum;
import jakarta.annotation.Resource;
import jakarta.servlet.FilterChain;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import org.springframework.web.filter.OncePerRequestFilter;

import java.io.IOException;
import java.util.concurrent.TimeUnit;

@Component
public class TokenVerifyFilter extends OncePerRequestFilter {

    @Resource
    private RedisService redisService;
//    @Resource
//    //    springboot框架提供的线程池,ioc容器内已经存在
//    private ThreadPoolTaskExecutor threadPoolTaskExecutor;
    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain filterChain) throws ServletException, IOException {
        if (request.getRequestURI().equals(Constants.LOGIN_URI)) { //如果是登录请求，此时还没有生成jwt，那不需要对登录请求进行jwt验证
            //验证jwt通过了 ，让Filter链继续执行，也就是继续执行下一个Filter
            filterChain.doFilter(request, response);
        } else {
            String token;
            if(request.getRequestURI().equals(Constants.EXPORT_EXCEL_URI)){
                token = request.getParameter(Constants.TOKEN_NAME);
            }
            else{
                token = request.getHeader(Constants.TOKEN_NAME);
            }
            if(!StringUtils.hasText(token)){
//                没拿到token,将失败这个枚举传回去,解析并取出常量拼接
                Result result = Result.FAIL(CodeEnum.TOKEN_IS_EMPTY);
//                封装
                String resultJSON = JSONUtils.toJSON(result);
//                返回
                ResponseUtils.write(response,resultJSON);
                return;
            }
//            验证token有没有被篡改过,也是验证token合法性
            if (!(JWTUtils.verifyJWT(token))){
//                token不合法
                Result result = Result.FAIL(CodeEnum.TOKEN_IS_NONE_MATCH);
//                封装
                String resultJSON = JSONUtils.toJSON(result);
//                返回
                ResponseUtils.write(response,resultJSON);
                return;
            }
            TUser tUser = JWTUtils.parseUserFromJWT(token);
            String redisToken = (String) redisService.getValue(Constants.REDIS_JWT_KEY + tUser.getId());
            if(!StringUtils.hasText(redisToken)){
//                没有获取到内容说明token过期了
                Result fail = Result.FAIL(CodeEnum.TOKEN_IS_EXPIRED);
                String json = JSONUtils.toJSON(fail);
                ResponseUtils.write(response,json);
                return;
            }
            if (!redisToken.equals(token)) {
//                登陆失败token错误
                Result result = Result.FAIL(CodeEnum.TOKEN_IS_ERROR);
//                把R对象转为JSON
                String json = JSONUtils.toJSON(result);
                ResponseUtils.write(response,json);
                return;
            }
//            jwt验证通过了
            UsernamePasswordAuthenticationToken authenticationToken = new UsernamePasswordAuthenticationToken(tUser,tUser.getLoginPwd(),tUser.getAuthorities());
            SecurityContextHolder.getContext().setAuthentication(authenticationToken);
//            刷新一下token
//            做异步执行
//            new Thread(new Runnable() {
//                @Override
//                public void run() {
                    这里刷新token即可
                    从请求头中获取
//                    String rememberMe = request.getHeader("rememberMe");
//                    if (!Boolean.parseBoolean(rememberMe)) {
//                        redisService.expire(Constants.REDIS_JWT_KEY + tUser.getId(), Constants.DEFAULT_EXPIRE_TIME, TimeUnit.SECONDS);
//                    }
//                }
//            }).start();
//            最好使用线程池的方式去执行
//            threadPoolTaskExecutor.execute(() -> {
//                    这里刷新token即可
//                    从请求头中获取
//                    String rememberMe = null;
//                    if(StringUtils.hasText(request.getHeader(Constants.REMEMBER_ME))){
//                        rememberMe = request.getHeader(Constants.REMEMBER_ME);
//                    }
//                    if (!Boolean.parseBoolean(rememberMe)) {
//                        redisService.expire(Constants.REDIS_JWT_KEY + tUser.getId(), Constants.DEFAULT_EXPIRE_TIME, TimeUnit.SECONDS);
//                    }
//            });
//            验证jwt通过了,让filter链继续执行


//            经过实验,线程池有概率导致请求和我们的service走的线程不是同一个导致请求头里面获取这个rememberMe报错
//            还是不用线程为好,慢点就慢点吧
            String rememberMe = null;
            if(StringUtils.hasText(request.getHeader(Constants.REMEMBER_ME))){
                rememberMe = request.getHeader(Constants.REMEMBER_ME);
            }
            if (!Boolean.parseBoolean(rememberMe)) {
                redisService.expire(Constants.REDIS_JWT_KEY + tUser.getId(), Constants.DEFAULT_EXPIRE_TIME, TimeUnit.SECONDS);
            }
            filterChain.doFilter(request,response);
        }
    }
}

package com.alatus.config.filter;

import com.alatus.constant.Constants;
import com.alatus.model.TUser;
import com.alatus.result.Result;
import com.alatus.service.RedisService;
import com.alatus.util.JSONUtils;
import com.alatus.util.JWTUtils;
import com.alatus.util.ResponseUtils;
import com.alatus.result.CodeEnum;
import jakarta.annotation.Resource;
import jakarta.servlet.FilterChain;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import org.springframework.web.filter.OncePerRequestFilter;

import java.io.IOException;
import java.util.concurrent.TimeUnit;

@Component
public class TokenVerifyFilter extends OncePerRequestFilter {

    @Resource
    private RedisService redisService;
//    @Resource
//    //    springboot框架提供的线程池,ioc容器内已经存在
//    private ThreadPoolTaskExecutor threadPoolTaskExecutor;
    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain filterChain) throws ServletException, IOException {
        if (request.getRequestURI().equals(Constants.LOGIN_URI)) { //如果是登录请求，此时还没有生成jwt，那不需要对登录请求进行jwt验证
            //验证jwt通过了 ，让Filter链继续执行，也就是继续执行下一个Filter
            filterChain.doFilter(request, response);
        } else {
            String token;
            if(request.getRequestURI().equals(Constants.EXPORT_EXCEL_URI)){
                token = request.getParameter(Constants.TOKEN_NAME);
            }
            else{
                token = request.getHeader(Constants.TOKEN_NAME);
            }
            if(!StringUtils.hasText(token)){
//                没拿到token,将失败这个枚举传回去,解析并取出常量拼接
                Result result = Result.FAIL(CodeEnum.TOKEN_IS_EMPTY);
//                封装
                String resultJSON = JSONUtils.toJSON(result);
//                返回
                ResponseUtils.write(response,resultJSON);
                return;
            }
//            验证token有没有被篡改过,也是验证token合法性
            if (!(JWTUtils.verifyJWT(token))){
//                token不合法
                Result result = Result.FAIL(CodeEnum.TOKEN_IS_NONE_MATCH);
//                封装
                String resultJSON = JSONUtils.toJSON(result);
//                返回
                ResponseUtils.write(response,resultJSON);
                return;
            }
            TUser tUser = JWTUtils.parseUserFromJWT(token);
            String redisToken = (String) redisService.getValue(Constants.REDIS_JWT_KEY + tUser.getId());
            if(!StringUtils.hasText(redisToken)){
//                没有获取到内容说明token过期了
                Result fail = Result.FAIL(CodeEnum.TOKEN_IS_EXPIRED);
                String json = JSONUtils.toJSON(fail);
                ResponseUtils.write(response,json);
                return;
            }
            if (!redisToken.equals(token)) {
//                登陆失败token错误
                Result result = Result.FAIL(CodeEnum.TOKEN_IS_ERROR);
//                把R对象转为JSON
                String json = JSONUtils.toJSON(result);
                ResponseUtils.write(response,json);
                return;
            }
//            jwt验证通过了
            UsernamePasswordAuthenticationToken authenticationToken = new UsernamePasswordAuthenticationToken(tUser,tUser.getLoginPwd(),tUser.getAuthorities());
            SecurityContextHolder.getContext().setAuthentication(authenticationToken);
//            刷新一下token
//            做异步执行
//            new Thread(new Runnable() {
//                @Override
//                public void run() {
                    这里刷新token即可
                    从请求头中获取
//                    String rememberMe = request.getHeader("rememberMe");
//                    if (!Boolean.parseBoolean(rememberMe)) {
//                        redisService.expire(Constants.REDIS_JWT_KEY + tUser.getId(), Constants.DEFAULT_EXPIRE_TIME, TimeUnit.SECONDS);
//                    }
//                }
//            }).start();
//            最好使用线程池的方式去执行
//            threadPoolTaskExecutor.execute(() -> {
//                    这里刷新token即可
//                    从请求头中获取
//                    String rememberMe = null;
//                    if(StringUtils.hasText(request.getHeader(Constants.REMEMBER_ME))){
//                        rememberMe = request.getHeader(Constants.REMEMBER_ME);
//                    }
//                    if (!Boolean.parseBoolean(rememberMe)) {
//                        redisService.expire(Constants.REDIS_JWT_KEY + tUser.getId(), Constants.DEFAULT_EXPIRE_TIME, TimeUnit.SECONDS);
//                    }
//            });
//            验证jwt通过了,让filter链继续执行


//            经过实验,线程池有概率导致请求和我们的service走的线程不是同一个导致请求头里面获取这个rememberMe报错
//            还是不用线程为好,慢点就慢点吧
            String rememberMe = null;
            if(StringUtils.hasText(request.getHeader(Constants.REMEMBER_ME))){
                rememberMe = request.getHeader(Constants.REMEMBER_ME);
            }
            if (!Boolean.parseBoolean(rememberMe)) {
                redisService.expire(Constants.REDIS_JWT_KEY + tUser.getId(), Constants.DEFAULT_EXPIRE_TIME, TimeUnit.SECONDS);
            }
            filterChain.doFilter(request,response);
        }
    }
}

package com.alatus.service;

import com.alatus.model.TCustomer;
import com.alatus.query.CustomerQuery;
import com.alatus.result.CustomerExcel;
import com.github.pagehelper.PageInfo;

import java.util.List;

public interface CustomerService {
    Boolean convertCustomer(CustomerQuery customerQuery);

    PageInfo<TCustomer> getCustomerByPage(Integer current);

    List<CustomerExcel> getCustomerByExcel();
}

package com.alatus.service;

import com.alatus.model.TCustomer;
import com.alatus.query.CustomerQuery;
import com.alatus.result.CustomerExcel;
import com.github.pagehelper.PageInfo;

import java.util.List;

public interface CustomerService {
    Boolean convertCustomer(CustomerQuery customerQuery);

    PageInfo<TCustomer> getCustomerByPage(Integer current);

    List<CustomerExcel> getCustomerByExcel();
}

package com.alatus.service.impl;

import com.alatus.constant.Constants;
import com.alatus.manager.CustomerManager;
import com.alatus.mapper.TCustomerMapper;
import com.alatus.model.TCustomer;
import com.alatus.query.CustomerQuery;
import com.alatus.result.CustomerExcel;
import com.alatus.service.CustomerService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

@Service
public class CustomerServiceImpl implements CustomerService {
    @Resource
    private TCustomerMapper tCustomerMapper;
    @Resource
    private CustomerManager customerManager;

    @Override
    public Boolean convertCustomer(CustomerQuery customerQuery) {
        return customerManager.transferCustomer(customerQuery);
    }

    @Override
    public PageInfo<TCustomer> getCustomerByPage(Integer current) {
//        设置PageHelper和分页情况
        PageHelper.startPage(current, Constants.PAGE_SIZE);
//        查询
        ArrayList<TCustomer> list = tCustomerMapper.selectCustomerByPage();
//        封装分页到PageInfo中
        PageInfo<TCustomer> info = new PageInfo<>(list);
        return info;
    }

    @Override
    public List<CustomerExcel> getCustomerByExcel() {
        List<TCustomer> customers = tCustomerMapper.selectCustomerByExcel();
//        把数据转为CustomerExcel
        List<CustomerExcel> customerExcelList = new ArrayList<>();
        customers.forEach(tCustomer -> {
            CustomerExcel customerExcel = new CustomerExcel();
            //需要一个一个设置，没有办法，因为没法使用BeanUtils复制
            customerExcel.setOwnerName(ObjectUtils.isEmpty(tCustomer.getOwnerDO()) ? Constants.EMPTY : tCustomer.getOwnerDO().getName());
            customerExcel.setActivityName(ObjectUtils.isEmpty(tCustomer.getActivityDO()) ? Constants.EMPTY : tCustomer.getActivityDO().getName());
            customerExcel.setFullName(tCustomer.getClueDO().getFullName());
            customerExcel.setAppellationName(ObjectUtils.isEmpty(tCustomer.getAppellationDO()) ? Constants.EMPTY : tCustomer.getAppellationDO().getTypeValue());
            customerExcel.setPhone(ObjectUtils.isEmpty(tCustomer.getClueDO().getPhone()) ? Constants.EMPTY :tCustomer.getClueDO().getPhone());
            customerExcel.setWeixin(ObjectUtils.isEmpty(tCustomer.getClueDO().getWeixin()) ? Constants.EMPTY :tCustomer.getClueDO().getWeixin());
            customerExcel.setQq(ObjectUtils.isEmpty(tCustomer.getClueDO().getQq()) ? Constants.EMPTY :tCustomer.getClueDO().getQq());
            customerExcel.setEmail(ObjectUtils.isEmpty(tCustomer.getClueDO().getEmail()) ? Constants.EMPTY :tCustomer.getClueDO().getEmail());
            customerExcel.setAge(ObjectUtils.isEmpty(tCustomer.getClueDO().getAge()) ? 0 :tCustomer.getClueDO().getAge());
            customerExcel.setJob(ObjectUtils.isEmpty(tCustomer.getClueDO().getJob()) ? Constants.EMPTY :tCustomer.getClueDO().getJob());
            customerExcel.setYearIncome(ObjectUtils.isEmpty(tCustomer.getClueDO().getYearIncome()) ? new BigDecimal(0) : tCustomer.getClueDO().getYearIncome());
            customerExcel.setAddress(ObjectUtils.isEmpty(tCustomer.getClueDO().getAddress()) ? Constants.EMPTY : tCustomer.getClueDO().getAddress());
            customerExcel.setNeedLoadName(ObjectUtils.isEmpty(tCustomer.getNeedLoanDO().getTypeValue()) ? Constants.EMPTY : tCustomer.getNeedLoanDO().getTypeValue());
            customerExcel.setProductName(ObjectUtils.isEmpty(tCustomer.getIntentionProductDO().getName()) ? Constants.EMPTY : tCustomer.getIntentionProductDO().getName());
            customerExcel.setSourceName(ObjectUtils.isEmpty(tCustomer.getSourceDO().getTypeValue()) ? Constants.EMPTY : tCustomer.getSourceDO().getTypeValue());
            customerExcel.setDescription(tCustomer.getDescription());
            customerExcel.setNextContactTime(tCustomer.getNextContactTime());
            customerExcelList.add(customerExcel);
        });
        return customerExcelList;
    }
}


package com.alatus.service.impl;

import com.alatus.constant.Constants;
import com.alatus.manager.CustomerManager;
import com.alatus.mapper.TCustomerMapper;
import com.alatus.model.TCustomer;
import com.alatus.query.CustomerQuery;
import com.alatus.result.CustomerExcel;
import com.alatus.service.CustomerService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

@Service
public class CustomerServiceImpl implements CustomerService {
    @Resource
    private TCustomerMapper tCustomerMapper;
    @Resource
    private CustomerManager customerManager;

    @Override
    public Boolean convertCustomer(CustomerQuery customerQuery) {
        return customerManager.transferCustomer(customerQuery);
    }

    @Override
    public PageInfo<TCustomer> getCustomerByPage(Integer current) {
//        设置PageHelper和分页情况
        PageHelper.startPage(current, Constants.PAGE_SIZE);
//        查询
        ArrayList<TCustomer> list = tCustomerMapper.selectCustomerByPage();
//        封装分页到PageInfo中
        PageInfo<TCustomer> info = new PageInfo<>(list);
        return info;
    }

    @Override
    public List<CustomerExcel> getCustomerByExcel() {
        List<TCustomer> customers = tCustomerMapper.selectCustomerByExcel();
//        把数据转为CustomerExcel
        List<CustomerExcel> customerExcelList = new ArrayList<>();
        customers.forEach(tCustomer -> {
            CustomerExcel customerExcel = new CustomerExcel();
            //需要一个一个设置，没有办法，因为没法使用BeanUtils复制
            customerExcel.setOwnerName(ObjectUtils.isEmpty(tCustomer.getOwnerDO()) ? Constants.EMPTY : tCustomer.getOwnerDO().getName());
            customerExcel.setActivityName(ObjectUtils.isEmpty(tCustomer.getActivityDO()) ? Constants.EMPTY : tCustomer.getActivityDO().getName());
            customerExcel.setFullName(tCustomer.getClueDO().getFullName());
            customerExcel.setAppellationName(ObjectUtils.isEmpty(tCustomer.getAppellationDO()) ? Constants.EMPTY : tCustomer.getAppellationDO().getTypeValue());
            customerExcel.setPhone(ObjectUtils.isEmpty(tCustomer.getClueDO().getPhone()) ? Constants.EMPTY :tCustomer.getClueDO().getPhone());
            customerExcel.setWeixin(ObjectUtils.isEmpty(tCustomer.getClueDO().getWeixin()) ? Constants.EMPTY :tCustomer.getClueDO().getWeixin());
            customerExcel.setQq(ObjectUtils.isEmpty(tCustomer.getClueDO().getQq()) ? Constants.EMPTY :tCustomer.getClueDO().getQq());
            customerExcel.setEmail(ObjectUtils.isEmpty(tCustomer.getClueDO().getEmail()) ? Constants.EMPTY :tCustomer.getClueDO().getEmail());
            customerExcel.setAge(ObjectUtils.isEmpty(tCustomer.getClueDO().getAge()) ? 0 :tCustomer.getClueDO().getAge());
            customerExcel.setJob(ObjectUtils.isEmpty(tCustomer.getClueDO().getJob()) ? Constants.EMPTY :tCustomer.getClueDO().getJob());
            customerExcel.setYearIncome(ObjectUtils.isEmpty(tCustomer.getClueDO().getYearIncome()) ? new BigDecimal(0) : tCustomer.getClueDO().getYearIncome());
            customerExcel.setAddress(ObjectUtils.isEmpty(tCustomer.getClueDO().getAddress()) ? Constants.EMPTY : tCustomer.getClueDO().getAddress());
            customerExcel.setNeedLoadName(ObjectUtils.isEmpty(tCustomer.getNeedLoanDO().getTypeValue()) ? Constants.EMPTY : tCustomer.getNeedLoanDO().getTypeValue());
            customerExcel.setProductName(ObjectUtils.isEmpty(tCustomer.getIntentionProductDO().getName()) ? Constants.EMPTY : tCustomer.getIntentionProductDO().getName());
            customerExcel.setSourceName(ObjectUtils.isEmpty(tCustomer.getSourceDO().getTypeValue()) ? Constants.EMPTY : tCustomer.getSourceDO().getTypeValue());
            customerExcel.setDescription(tCustomer.getDescription());
            customerExcel.setNextContactTime(tCustomer.getNextContactTime());
            customerExcelList.add(customerExcel);
        });
        return customerExcelList;
    }
}


package com.alatus.mapper;

import com.alatus.model.TCustomer;

import java.util.ArrayList;
import java.util.List;

public interface TCustomerMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(TCustomer record);

    int insertSelective(TCustomer record);

    TCustomer selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(TCustomer record);

    int updateByPrimaryKey(TCustomer record);

    ArrayList<TCustomer> selectCustomerByPage();

    List<TCustomer> selectCustomerByExcel();
}
package com.alatus.mapper;

import com.alatus.model.TCustomer;

import java.util.ArrayList;
import java.util.List;

public interface TCustomerMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(TCustomer record);

    int insertSelective(TCustomer record);

    TCustomer selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(TCustomer record);

    int updateByPrimaryKey(TCustomer record);

    ArrayList<TCustomer> selectCustomerByPage();

    List<TCustomer> selectCustomerByExcel();
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.alatus.mapper.TCustomerMapper">
  <resultMap id="BaseResultMap" type="com.alatus.model.TCustomer">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="clue_id" jdbcType="INTEGER" property="clueId" />
    <result column="product" jdbcType="INTEGER" property="product" />
    <result column="description" jdbcType="VARCHAR" property="description" />
    <result column="next_contact_time" jdbcType="TIMESTAMP" property="nextContactTime" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="create_by" jdbcType="INTEGER" property="createBy" />
    <result column="edit_time" jdbcType="TIMESTAMP" property="editTime" />
    <result column="edit_by" jdbcType="INTEGER" property="editBy" />
  </resultMap>

  <resultMap id="CustomerResultMap" type="com.alatus.model.TCustomer">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="clue_id" jdbcType="INTEGER" property="clueId" />
    <result column="product" jdbcType="INTEGER" property="product" />
    <result column="description" jdbcType="VARCHAR" property="description" />
    <result column="next_contact_time" jdbcType="TIMESTAMP" property="nextContactTime" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="create_by" jdbcType="INTEGER" property="createBy" />
    <result column="edit_time" jdbcType="TIMESTAMP" property="editTime" />
    <result column="edit_by" jdbcType="INTEGER" property="editBy" />
    <!--一对一关联线索-->
    <association property="clueDO" javaType="com.alatus.model.TClue">
      <id column="clueId" jdbcType="INTEGER" property="id" />
      <result column="full_name" jdbcType="VARCHAR" property="fullName" />
      <result column="phone" jdbcType="VARCHAR" property="phone" />
      <result column="weixin" jdbcType="VARCHAR" property="weixin" />
      <result column="qq" jdbcType="VARCHAR" property="qq" />
      <result column="email" jdbcType="VARCHAR" property="email" />
      <result column="age" jdbcType="INTEGER" property="age" />
      <result column="job" jdbcType="VARCHAR" property="job" />
      <result column="year_income" jdbcType="DECIMAL" property="yearIncome" />
      <result column="address" jdbcType="VARCHAR" property="address" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="ownerDO" javaType="com.alatus.model.TUser">
      <id column="ownerId" jdbcType="INTEGER" property="id" />
      <result column="ownerName" jdbcType="VARCHAR" property="name" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="activityDO" javaType="com.alatus.model.TActivity">
      <id column="activityId" jdbcType="INTEGER" property="id" />
      <result column="activityName" jdbcType="VARCHAR" property="name" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="appellationDO" javaType="com.alatus.model.TDicValue">
      <id column="appellationId" jdbcType="INTEGER" property="id" />
      <result column="appellationName" jdbcType="VARCHAR" property="typeValue" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="needLoanDO" javaType="com.alatus.model.TDicValue">
      <id column="needLoanId" jdbcType="INTEGER" property="id" />
      <result column="needLoanName" jdbcType="VARCHAR" property="typeValue" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="intentionStateDO" javaType="com.alatus.model.TDicValue">
      <id column="intentionStateId" jdbcType="INTEGER" property="id" />
      <result column="intentionStateName" jdbcType="VARCHAR" property="typeValue" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="stateDO" javaType="com.alatus.model.TDicValue">
      <id column="stateId" jdbcType="INTEGER" property="id" />
      <result column="stateName" jdbcType="VARCHAR" property="typeValue" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="sourceDO" javaType="com.alatus.model.TDicValue">
      <id column="sourceId" jdbcType="INTEGER" property="id" />
      <result column="sourceName" jdbcType="VARCHAR" property="typeValue" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="intentionProductDO" javaType="com.alatus.model.TProduct">
      <id column="intentionProductId" jdbcType="INTEGER" property="id" />
      <result column="intentionProductName" jdbcType="VARCHAR" property="name" />
    </association>
  </resultMap>

  <sql id="Base_Column_List">
    id, clue_id, product, description, next_contact_time, create_time, create_by, edit_time, 
    edit_by
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from t_customer
    where id = #{id,jdbcType=INTEGER}
  </select>

  <select id="selectCustomerByPage" parameterType="java.lang.Integer" resultMap="CustomerResultMap">
    select
      tct.*,
      tc.id clueId, tc.full_name, tc.phone, tc.weixin,
      tu1.id ownerId, tu1.name ownerName,
      ta.id activityId, ta.name activityName,
      tdv.id appellationId, tdv.type_value appellationName,
      tdv2.id needLoanId, tdv2.type_value needLoanName,
      tdv3.id intentionStateId, tdv3.type_value intentionStateName,
      tdv4.id stateId, tdv4.type_value stateName,
      tdv5.id sourceId, tdv5.type_value sourceName,
      tp.id intentionProductId, tp.name intentionProductName
    from t_customer tct left join t_clue tc on tct.clue_id = tc.id
                        left join t_user tu1 on tc.owner_id = tu1.id
                        left join t_activity ta on tc.activity_id = ta.id
                        left join t_dic_value tdv on tc.appellation = tdv.id
                        left join t_dic_value tdv2 on tc.need_loan = tdv2.id
                        left join t_dic_value tdv3 on tc.intention_state = tdv3.id
                        left join t_dic_value tdv4 on tc.state = tdv4.id
                        left join t_dic_value tdv5 on tc.source = tdv5.id
                        left join t_product tp on tct.product = tp.id
  </select>

    <select id="selectCustomerByExcel" resultMap="CustomerResultMap">
      select
        tct.*,
        tc.id clueId, tc.full_name, tc.phone, tc.weixin,
        tu1.id ownerId, tu1.name ownerName,
        ta.id activityId, ta.name activityName,
        tdv.id appellationId, tdv.type_value appellationName,
        tdv2.id needLoanId, tdv2.type_value needLoanName,
        tdv3.id intentionStateId, tdv3.type_value intentionStateName,
        tdv4.id stateId, tdv4.type_value stateName,
        tdv5.id sourceId, tdv5.type_value sourceName,
        tp.id intentionProductId, tp.name intentionProductName
      from t_customer tct left join t_clue tc on tct.clue_id = tc.id
                          left join t_user tu1 on tc.owner_id = tu1.id
                          left join t_activity ta on tc.activity_id = ta.id
                          left join t_dic_value tdv on tc.appellation = tdv.id
                          left join t_dic_value tdv2 on tc.need_loan = tdv2.id
                          left join t_dic_value tdv3 on tc.intention_state = tdv3.id
                          left join t_dic_value tdv4 on tc.state = tdv4.id
                          left join t_dic_value tdv5 on tc.source = tdv5.id
                          left join t_product tp on tct.product = tp.id
    </select>


    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_customer
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.alatus.model.TCustomer" useGeneratedKeys="true">
    insert into t_customer (clue_id, product, description, 
      next_contact_time, create_time, create_by, 
      edit_time, edit_by)
    values (#{clueId,jdbcType=INTEGER}, #{product,jdbcType=INTEGER}, #{description,jdbcType=VARCHAR}, 
      #{nextContactTime,jdbcType=TIMESTAMP}, #{createTime,jdbcType=TIMESTAMP}, #{createBy,jdbcType=INTEGER}, 
      #{editTime,jdbcType=TIMESTAMP}, #{editBy,jdbcType=INTEGER})
  </insert>
  <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.alatus.model.TCustomer" useGeneratedKeys="true">
    insert into t_customer
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="clueId != null">
        clue_id,
      </if>
      <if test="product != null">
        product,
      </if>
      <if test="description != null">
        description,
      </if>
      <if test="nextContactTime != null">
        next_contact_time,
      </if>
      <if test="createTime != null">
        create_time,
      </if>
      <if test="createBy != null">
        create_by,
      </if>
      <if test="editTime != null">
        edit_time,
      </if>
      <if test="editBy != null">
        edit_by,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="clueId != null">
        #{clueId,jdbcType=INTEGER},
      </if>
      <if test="product != null">
        #{product,jdbcType=INTEGER},
      </if>
      <if test="description != null">
        #{description,jdbcType=VARCHAR},
      </if>
      <if test="nextContactTime != null">
        #{nextContactTime,jdbcType=TIMESTAMP},
      </if>
      <if test="createTime != null">
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="createBy != null">
        #{createBy,jdbcType=INTEGER},
      </if>
      <if test="editTime != null">
        #{editTime,jdbcType=TIMESTAMP},
      </if>
      <if test="editBy != null">
        #{editBy,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.alatus.model.TCustomer">
    update t_customer
    <set>
      <if test="clueId != null">
        clue_id = #{clueId,jdbcType=INTEGER},
      </if>
      <if test="product != null">
        product = #{product,jdbcType=INTEGER},
      </if>
      <if test="description != null">
        description = #{description,jdbcType=VARCHAR},
      </if>
      <if test="nextContactTime != null">
        next_contact_time = #{nextContactTime,jdbcType=TIMESTAMP},
      </if>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="createBy != null">
        create_by = #{createBy,jdbcType=INTEGER},
      </if>
      <if test="editTime != null">
        edit_time = #{editTime,jdbcType=TIMESTAMP},
      </if>
      <if test="editBy != null">
        edit_by = #{editBy,jdbcType=INTEGER},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.alatus.model.TCustomer">
    update t_customer
    set clue_id = #{clueId,jdbcType=INTEGER},
      product = #{product,jdbcType=INTEGER},
      description = #{description,jdbcType=VARCHAR},
      next_contact_time = #{nextContactTime,jdbcType=TIMESTAMP},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      create_by = #{createBy,jdbcType=INTEGER},
      edit_time = #{editTime,jdbcType=TIMESTAMP},
      edit_by = #{editBy,jdbcType=INTEGER}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.alatus.mapper.TCustomerMapper">
  <resultMap id="BaseResultMap" type="com.alatus.model.TCustomer">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="clue_id" jdbcType="INTEGER" property="clueId" />
    <result column="product" jdbcType="INTEGER" property="product" />
    <result column="description" jdbcType="VARCHAR" property="description" />
    <result column="next_contact_time" jdbcType="TIMESTAMP" property="nextContactTime" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="create_by" jdbcType="INTEGER" property="createBy" />
    <result column="edit_time" jdbcType="TIMESTAMP" property="editTime" />
    <result column="edit_by" jdbcType="INTEGER" property="editBy" />
  </resultMap>

  <resultMap id="CustomerResultMap" type="com.alatus.model.TCustomer">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="clue_id" jdbcType="INTEGER" property="clueId" />
    <result column="product" jdbcType="INTEGER" property="product" />
    <result column="description" jdbcType="VARCHAR" property="description" />
    <result column="next_contact_time" jdbcType="TIMESTAMP" property="nextContactTime" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="create_by" jdbcType="INTEGER" property="createBy" />
    <result column="edit_time" jdbcType="TIMESTAMP" property="editTime" />
    <result column="edit_by" jdbcType="INTEGER" property="editBy" />
    <!--一对一关联线索-->
    <association property="clueDO" javaType="com.alatus.model.TClue">
      <id column="clueId" jdbcType="INTEGER" property="id" />
      <result column="full_name" jdbcType="VARCHAR" property="fullName" />
      <result column="phone" jdbcType="VARCHAR" property="phone" />
      <result column="weixin" jdbcType="VARCHAR" property="weixin" />
      <result column="qq" jdbcType="VARCHAR" property="qq" />
      <result column="email" jdbcType="VARCHAR" property="email" />
      <result column="age" jdbcType="INTEGER" property="age" />
      <result column="job" jdbcType="VARCHAR" property="job" />
      <result column="year_income" jdbcType="DECIMAL" property="yearIncome" />
      <result column="address" jdbcType="VARCHAR" property="address" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="ownerDO" javaType="com.alatus.model.TUser">
      <id column="ownerId" jdbcType="INTEGER" property="id" />
      <result column="ownerName" jdbcType="VARCHAR" property="name" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="activityDO" javaType="com.alatus.model.TActivity">
      <id column="activityId" jdbcType="INTEGER" property="id" />
      <result column="activityName" jdbcType="VARCHAR" property="name" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="appellationDO" javaType="com.alatus.model.TDicValue">
      <id column="appellationId" jdbcType="INTEGER" property="id" />
      <result column="appellationName" jdbcType="VARCHAR" property="typeValue" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="needLoanDO" javaType="com.alatus.model.TDicValue">
      <id column="needLoanId" jdbcType="INTEGER" property="id" />
      <result column="needLoanName" jdbcType="VARCHAR" property="typeValue" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="intentionStateDO" javaType="com.alatus.model.TDicValue">
      <id column="intentionStateId" jdbcType="INTEGER" property="id" />
      <result column="intentionStateName" jdbcType="VARCHAR" property="typeValue" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="stateDO" javaType="com.alatus.model.TDicValue">
      <id column="stateId" jdbcType="INTEGER" property="id" />
      <result column="stateName" jdbcType="VARCHAR" property="typeValue" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="sourceDO" javaType="com.alatus.model.TDicValue">
      <id column="sourceId" jdbcType="INTEGER" property="id" />
      <result column="sourceName" jdbcType="VARCHAR" property="typeValue" />
    </association>
    <!--一对一关联查询的映射-->
    <association property="intentionProductDO" javaType="com.alatus.model.TProduct">
      <id column="intentionProductId" jdbcType="INTEGER" property="id" />
      <result column="intentionProductName" jdbcType="VARCHAR" property="name" />
    </association>
  </resultMap>

  <sql id="Base_Column_List">
    id, clue_id, product, description, next_contact_time, create_time, create_by, edit_time, 
    edit_by
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from t_customer
    where id = #{id,jdbcType=INTEGER}
  </select>

  <select id="selectCustomerByPage" parameterType="java.lang.Integer" resultMap="CustomerResultMap">
    select
      tct.*,
      tc.id clueId, tc.full_name, tc.phone, tc.weixin,
      tu1.id ownerId, tu1.name ownerName,
      ta.id activityId, ta.name activityName,
      tdv.id appellationId, tdv.type_value appellationName,
      tdv2.id needLoanId, tdv2.type_value needLoanName,
      tdv3.id intentionStateId, tdv3.type_value intentionStateName,
      tdv4.id stateId, tdv4.type_value stateName,
      tdv5.id sourceId, tdv5.type_value sourceName,
      tp.id intentionProductId, tp.name intentionProductName
    from t_customer tct left join t_clue tc on tct.clue_id = tc.id
                        left join t_user tu1 on tc.owner_id = tu1.id
                        left join t_activity ta on tc.activity_id = ta.id
                        left join t_dic_value tdv on tc.appellation = tdv.id
                        left join t_dic_value tdv2 on tc.need_loan = tdv2.id
                        left join t_dic_value tdv3 on tc.intention_state = tdv3.id
                        left join t_dic_value tdv4 on tc.state = tdv4.id
                        left join t_dic_value tdv5 on tc.source = tdv5.id
                        left join t_product tp on tct.product = tp.id
  </select>

    <select id="selectCustomerByExcel" resultMap="CustomerResultMap">
      select
        tct.*,
        tc.id clueId, tc.full_name, tc.phone, tc.weixin,
        tu1.id ownerId, tu1.name ownerName,
        ta.id activityId, ta.name activityName,
        tdv.id appellationId, tdv.type_value appellationName,
        tdv2.id needLoanId, tdv2.type_value needLoanName,
        tdv3.id intentionStateId, tdv3.type_value intentionStateName,
        tdv4.id stateId, tdv4.type_value stateName,
        tdv5.id sourceId, tdv5.type_value sourceName,
        tp.id intentionProductId, tp.name intentionProductName
      from t_customer tct left join t_clue tc on tct.clue_id = tc.id
                          left join t_user tu1 on tc.owner_id = tu1.id
                          left join t_activity ta on tc.activity_id = ta.id
                          left join t_dic_value tdv on tc.appellation = tdv.id
                          left join t_dic_value tdv2 on tc.need_loan = tdv2.id
                          left join t_dic_value tdv3 on tc.intention_state = tdv3.id
                          left join t_dic_value tdv4 on tc.state = tdv4.id
                          left join t_dic_value tdv5 on tc.source = tdv5.id
                          left join t_product tp on tct.product = tp.id
    </select>


    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_customer
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.alatus.model.TCustomer" useGeneratedKeys="true">
    insert into t_customer (clue_id, product, description, 
      next_contact_time, create_time, create_by, 
      edit_time, edit_by)
    values (#{clueId,jdbcType=INTEGER}, #{product,jdbcType=INTEGER}, #{description,jdbcType=VARCHAR}, 
      #{nextContactTime,jdbcType=TIMESTAMP}, #{createTime,jdbcType=TIMESTAMP}, #{createBy,jdbcType=INTEGER}, 
      #{editTime,jdbcType=TIMESTAMP}, #{editBy,jdbcType=INTEGER})
  </insert>
  <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.alatus.model.TCustomer" useGeneratedKeys="true">
    insert into t_customer
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="clueId != null">
        clue_id,
      </if>
      <if test="product != null">
        product,
      </if>
      <if test="description != null">
        description,
      </if>
      <if test="nextContactTime != null">
        next_contact_time,
      </if>
      <if test="createTime != null">
        create_time,
      </if>
      <if test="createBy != null">
        create_by,
      </if>
      <if test="editTime != null">
        edit_time,
      </if>
      <if test="editBy != null">
        edit_by,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="clueId != null">
        #{clueId,jdbcType=INTEGER},
      </if>
      <if test="product != null">
        #{product,jdbcType=INTEGER},
      </if>
      <if test="description != null">
        #{description,jdbcType=VARCHAR},
      </if>
      <if test="nextContactTime != null">
        #{nextContactTime,jdbcType=TIMESTAMP},
      </if>
      <if test="createTime != null">
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="createBy != null">
        #{createBy,jdbcType=INTEGER},
      </if>
      <if test="editTime != null">
        #{editTime,jdbcType=TIMESTAMP},
      </if>
      <if test="editBy != null">
        #{editBy,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.alatus.model.TCustomer">
    update t_customer
    <set>
      <if test="clueId != null">
        clue_id = #{clueId,jdbcType=INTEGER},
      </if>
      <if test="product != null">
        product = #{product,jdbcType=INTEGER},
      </if>
      <if test="description != null">
        description = #{description,jdbcType=VARCHAR},
      </if>
      <if test="nextContactTime != null">
        next_contact_time = #{nextContactTime,jdbcType=TIMESTAMP},
      </if>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="createBy != null">
        create_by = #{createBy,jdbcType=INTEGER},
      </if>
      <if test="editTime != null">
        edit_time = #{editTime,jdbcType=TIMESTAMP},
      </if>
      <if test="editBy != null">
        edit_by = #{editBy,jdbcType=INTEGER},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.alatus.model.TCustomer">
    update t_customer
    set clue_id = #{clueId,jdbcType=INTEGER},
      product = #{product,jdbcType=INTEGER},
      description = #{description,jdbcType=VARCHAR},
      next_contact_time = #{nextContactTime,jdbcType=TIMESTAMP},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      create_by = #{createBy,jdbcType=INTEGER},
      edit_time = #{editTime,jdbcType=TIMESTAMP},
      edit_by = #{editBy,jdbcType=INTEGER}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>
package com.alatus.result;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

@Data
public class CustomerExcel {

    /**
     * Excel表头的字段如下：
     *
     * 所属人	所属活动	客户姓名	客户称呼	客户手机	客户微信	客户QQ
     * 客户邮箱	客户年龄	客户职业	客户年收入
     * 客户住址	是否贷款	客户产品	客户来源	客户描述	下次联系时间
     */

    @ExcelProperty(value = "所属人")
    private String ownerName;

    @ExcelProperty(value = "所属活动")
    private String activityName;

    @ExcelProperty(value = "客户姓名")
    private String fullName;

    @ExcelProperty(value = "客户称呼")
    private String appellationName;

    @ExcelProperty(value = "客户手机")
    private String phone;

    @ExcelProperty(value = "客户微信")
    private String weixin;

    @ExcelProperty(value = "客户QQ")
    private String qq;

    @ExcelProperty(value = "客户邮箱")
    private String email;

    @ExcelProperty(value = "客户年龄")
    private int age;

    @ExcelProperty(value = "客户职业")
    private String job;

    @ExcelProperty(value = "客户年收入")
    private BigDecimal yearIncome;

    @ExcelProperty(value = "客户住址")
    private String address;

    @ExcelProperty(value = "是否贷款")
    private String needLoadName;

    @ExcelProperty(value = "客户产品")
    private String productName;

    @ExcelProperty(value = "客户来源")
    private String sourceName;

    @ExcelProperty(value = "客户描述")
    private String description;

    @ExcelProperty(value = "下次联系时间")
    private Date nextContactTime;

}

package com.alatus.result;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

@Data
public class CustomerExcel {

    /**
     * Excel表头的字段如下：
     *
     * 所属人  所属活动   客户姓名   客户称呼   客户手机   客户微信   客户QQ
     * 客户邮箱 客户年龄   客户职业   客户年收入
     * 客户住址 是否贷款   客户产品   客户来源   客户描述   下次联系时间
     */

    @ExcelProperty(value = "所属人")
    private String ownerName;

    @ExcelProperty(value = "所属活动")
    private String activityName;

    @ExcelProperty(value = "客户姓名")
    private String fullName;

    @ExcelProperty(value = "客户称呼")
    private String appellationName;

    @ExcelProperty(value = "客户手机")
    private String phone;

    @ExcelProperty(value = "客户微信")
    private String weixin;

    @ExcelProperty(value = "客户QQ")
    private String qq;

    @ExcelProperty(value = "客户邮箱")
    private String email;

    @ExcelProperty(value = "客户年龄")
    private int age;

    @ExcelProperty(value = "客户职业")
    private String job;

    @ExcelProperty(value = "客户年收入")
    private BigDecimal yearIncome;

    @ExcelProperty(value = "客户住址")
    private String address;

    @ExcelProperty(value = "是否贷款")
    private String needLoadName;

    @ExcelProperty(value = "客户产品")
    private String productName;

    @ExcelProperty(value = "客户来源")
    private String sourceName;

    @ExcelProperty(value = "客户描述")
    private String description;

    @ExcelProperty(value = "下次联系时间")
    private Date nextContactTime;

}
旧约Alatus
关注
  • 28
    点赞
  • 踩
  • 21
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
easyexcel-2.2.11.jar
05-10
EasyExcel是阿里巴巴开源的一个Excel处理框架，使用简单、节省内存。节省内存的原理也很简单，在解析Excel时没有将文件数据全部加载到内存当中，而是从磁盘文件中一行行读取。
easyExcel-errorExport:对easyExcel的封装实现对导入错误数据背景标红批注提示导出
05-26
easyExcel的封装，实现导入数据，对错误信息添加批注提示导出 Maven 坐标 <groupId>com.github.duanyashu</groupId> <artifactId>easyExcel-errorExport <version>1.1.1 使用说明 @RequestMapping("/file") @...
Java 中使用 EasyExcel 返回base64给前端
最新发布
Jw_1201的博客
02-27 653
【代码】Java 中使用 EasyExcel 返回base64给前端。
EasyExcel下载EXCEL文件，后台通过流形式输出到前端浏览器下载方式输出
qq_34815027的博客
01-12 2256
String fileName = URLEncoder.encode("导出数据产品完检", "UTF-8").replaceAll("\\+", "%20");//文件名需要这样写，不能在setHeader直接写中文名，否则下载的文件名字为空，只有后缀。//请求头（key,value），请求头可以设置多个key-value对。在点击导出数据）");//导出的Excel是二进制数据类型，所以设置为blob。//请求的参数，json格式，后台要用json格式接收。//返回成功，导出的Excel文件。
Spring boot集成Easyexcel模板导出返回前端示例并解决jar无法找到文件问题
WHB20126的博客
07-06 703
List<OpeningEnb> list = openingEnbService.selectOpeningEnbList(openingEnb); InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("opening/opening.xlsx"); String fileName = "基站开通信息表.xlsx"; respon.
Java使用EasyExcel下载xls、xlsx 出现文件格式与扩展名不匹配(亲测)
gb4215287的博客
04-12 5080
java在浏览器中打开不同格式的文件
POI事件模式解析Excel 2007（一） - XML存储
李平安
11-09 4174
Excel文件XML结构Excel2007是使用XML格式来存储的，一个Excel文件包含多个XML文件 把一个excel文件(test.xlsx)后缀改为.zip,打开之后就可以看到这个Excel2007文件的XML文件结构
根据文件模板实现预览、生成word、pdf、excel（后端-项目）
11-06
本项目着重于根据文件模板实现文件的预览、生成Word、PDF以及Excel，这些都是企业级应用中的关键功能。接下来，我们将深入探讨这些知识点。 首先，文件预览是让用户在不下载文件的情况下，通过浏览器或者特定的...
easyexcel-2.1.4.zip
02-22
在描述中提到的`ExcelUtil4Spzp.java`文件，很可能是项目中自定义的一个工具类，用于封装EasyExcel的常用操作。这样的工具类可以提供如读取Excel、写入Excel等方法，简化代码，提高复用性。例如，可能包含如下方法：...
java导出excel-easyExceljava导出excel-easyExcel
08-02
java导出excel
EasyExcel的导入导出和EasyExcel手动输入表头和表内容
yuanpeij的博客
09-01 5265
EasyExcel 1.导入(数据为json) 1.导入头 public void saveHeadData(PageData pd, MultipartFile file) throws Exception{ //easyExcel 进行保存excel的头数据 EasyExcelHeadListener easyExcelListener = new EasyExcelHeadListener(dynamicExcelMapper, pd);
EasyExcel导出文件格式不匹配
adminwbbmm的博客
07-01 2892
Java使用EasyExcel导出excel文件出现文件格式不匹配问题
EasyExcel快速实现前后端导入导出以及相关注意事项
whitehats的博客
12-25 9163
EasyExcel快速实现前后端导入导出以及相关注意事项 环境： ​ 前端: Ant Design of Vue（UI） + Vue + axios接口调用 ​ 后端:EasyExcel实现Excel表格的读写操作。 目录： ​ 导入Excel文件 ​ 导出Excel文件 导入Excel文件 流程： ​ 下载模板->编写Excel数据文件->触发导入数据->发送axios请求->后端接收数据->数据持久化(数据处理) 模板下载 在导入文件之前需要用户根据自己设定的
EasyExcel技术读写excel表格数据，以树形式数据返回前端（springboot）
weixin_44884861的博客
08-14 3491
导入依赖 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.1</version> </dependency> <!--xls--> <dependency>
项目实战 | Excel导出（二）——返回内容优化
Ben的博客
08-12 801
优化导出返回内容
SpringBoot整合EasyExcel实现前端调用下载Excel，仅作记录。
m0_55627541的博客
01-03 3040
SpringBoot整合EasyExcel实现前端调用下载Excel
纯前端使用SheetJS读取excel 报错Cannot find file [Content_Types].xml in zip
weixin_44258574的博客
10-21 2171
使用方式可参考https://blog.csdn.net/yuzheng7002/article/details/104134246 我这里主要展示使用过程中遇到的一个问题，希望可以帮到你 问题场景 在使用 const importData = XLSX.read(content, {type:'binary'}); 的时候报错Cannot find file [Content_Types].xml in zip 方案一： 网上有说XLSX@0.16.0版本太高，换成@0.14.2版本即可，我没有尝试这
阿里EasyExcel导入导出Excel表格篇（前端JSP，后端SSM）简单明了！（内含源码）
热门推荐
小名同学
03-01 6万+
Echarts之柱状图动态加载数据篇 文件上传下载篇 老规矩，先上效果： 前端使用H-ui修改的界面。 依赖： <!--easyexcel依赖--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</a...
java - easyexcel 实现excel导出下拉多选框 模板
09-04
使用EasyExcel实现excel导出下拉多选框模板的方法如下： 1. 首先，引入EasyExcel依赖。在pom.xml文件中加入以下依赖： ```xml <groupId>com.alibaba</groupId> <artifactId>easyexcel <version>2.2.11 ``` 2....
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值