SpringBoot实现导入功能

实体类-->MeetRestaurantArrange

package com.krt.meet.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import com.krt.common.core.base.BaseEntity;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * 餐厅安排实体类
 *
 * @author lf
 * @date 2022-06-07
 */
@ApiModel(value = "会务安排餐厅安排")
@Data
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
@TableName("meet_restaurant_arrange")
public class MeetRestaurantArrange extends BaseEntity {

    /**
     * 会务ID
     */
    @ApiModelProperty(value = "会务ID 关联meet_information表的id")
    private Integer meetId;

    /**
     * 关联人员信息ID
     */
    @ApiModelProperty(value = "关联人员信息ID")
    private Integer restId;

    /**
     * 餐厅名称
     */
    @ApiModelProperty(value = "餐厅名称")
    private String diningName;

    /**
     * 座位号
     */
    @ApiModelProperty(value = "座位号")
    private String seatMember;

    /**
     *餐厅地址
     */
    @ApiModelProperty(value = "餐厅地址")
    private String mapAddress;

    /**
     * 经度
     */
    @ApiModelProperty(value = "经度")
    private String lng;

    /**
     * 纬度
     */
    @ApiModelProperty(value = "纬度")
    private String lat;

    /**
     * 用餐时间
     */
    @ApiModelProperty(value = "用餐时间")
    private Date time;

    /**
     * 批次id
     */
    @ApiModelProperty(value = "批次id")
    private String uuid;

}

需要导入的Excel字段-->ExcelInRestauratVO

package com.krt.meet.VO;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.krt.common.core.annotation.KrtDataMaskingClass;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * 导入餐厅安排数据VO
 *
 * @author lf
 * @version 1.0
 * @date 2022年06月07日
 */
@Data
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
@KrtDataMaskingClass
public class ExcelInRestaurantVO {
    /**
     * 人员姓名
     */
    @Excel(name = "姓名")
    private String personName;

    /**
     * 联系方式
     */
    @Excel(name = "联系方式")
    private String phone;

    /**
     * 餐厅名称
     */
    @Excel(name = "餐厅名称")
    private String diningName;

    /**
     * 座位号
     */
    @Excel(name = "座位号")
    private String seatMember;

    /**
     * 用餐时间
     */
    @Excel(name = "用餐时间")
    private Date time;
}

控制层部分代码-->MraController

    /**
     * 会务安排管理餐厅安排导入
     * @param file
     * @return
     */
    @KrtLog("会务安排管理餐厅安排导入")
    @PostMapping("/api/meet/enrollPerson/excelInRestaurant")
    @ApiOperation(value = "餐厅安排数据导入", notes = "餐厅安排数据导入")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "form", name = "file", value = "文件", dataType = "__file", required = true),
            @ApiImplicitParam(paramType = "form", name = "meetId", value = "对应导入会务id")
    })
    public ReturnBean excelInRestaurant(Integer meetId,@RequestParam("file") MultipartFile file){
        if (file.isEmpty()) {
            return ReturnBean.error("导入文件不能为空");
        }
        String fileName = file.getOriginalFilename();
        if (fileName.indexOf(".xls") == -1 && fileName.indexOf(".xlsx") == -1) {
            return ReturnBean.error("导入文件必须是Excel");
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        params.setNeedSave(false);

        try{
            //读取excel
            List<ExcelInRestaurantVO> excelList = ExcelImportUtil.importExcel(file.getInputStream(),ExcelInRestaurantVO.class,params);
            //保存
            return iMraService.excelInRestaurant(meetId,excelList);
        }catch (Exception e){
            log.debug("导入餐厅安排数据失败",e);
            return ReturnBean.error("导入餐厅安排数据失败");
        }
    }

服务接口层-->IMraService

package com.krt.meet.service;

import com.krt.common.core.base.IBaseService;
import com.krt.common.core.bean.ReturnBean;
import com.krt.meet.VO.ExcelInRestaurantVO;
import com.krt.meet.entity.MeetRestaurantArrange;

import java.util.List;

/**
 * 餐厅安排服务接口层
 *
 * @author lf
 * @date 2022-06-07
 */
public interface IMraService extends IBaseService<MeetRestaurantArrange> {

    ReturnBean excelInRestaurant(Integer meetId,List<ExcelInRestaurantVO> excelList);
}

接口实现层-->MraServiceImpl

package com.krt.meet.service.impl;

import cn.hutool.core.util.IdUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.krt.common.core.base.BaseServiceImpl;
import com.krt.common.core.bean.ReturnBean;
import com.krt.meet.VO.ExcelInRestaurantVO;
import com.krt.meet.entity.EnrollPerson;
import com.krt.meet.entity.MeetRestaurantArrange;
import com.krt.meet.mapper.MraMapper;
import com.krt.meet.service.IEnrollPersonService;
import com.krt.meet.service.IMeetHotelArrangeService;
import com.krt.meet.service.IMraService;
import com.krt.meet.utils.StringUtil;
import lombok.extern.slf4j.Slf4j;
import net.hasor.utils.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

/**
 * 餐厅安排接口实现层
 *
 * @author lf
 * @date 2022-06-07
 */
@Slf4j
@Service
public class MraServiceImpl extends BaseServiceImpl<MraMapper, MeetRestaurantArrange> implements IMraService {

    @Autowired
    private IMeetHotelArrangeService hotelArrangeService;

    @Override
    public ReturnBean excelInRestaurant(Integer meetId,List<ExcelInRestaurantVO> excelList) {
        //生成导入批次
        String uuid = IdUtil.simpleUUID();

        ArrayList<MeetRestaurantArrange> restaurantArrangeList = new ArrayList<>();
        //解析数据,判断导入的姓名及手机号是否为空
        for (int i = 0;i < excelList.size();i++) {
            ExcelInRestaurantVO excelInRestaurantVO = excelList.get(i);
            if (StringUtils.isBlank(excelInRestaurantVO.getPersonName()) || StringUtils.isBlank(excelInRestaurantVO.getPersonName().trim())) {
                return ReturnBean.error("第" + (i + 1) + "行人员姓名为空");
            }
            if (StringUtils.isBlank(excelInRestaurantVO.getPhone()) || StringUtils.isBlank(excelInRestaurantVO.getPhone().trim())) {
                return ReturnBean.error("第" + (i + 1) + "行人员手机号为空");
            }

            /**
             * 将excel导入的数据与meet_enroll_person(手机号及姓名做匹配),查询出id.
             * 查询id的目的：meet_enroll_person的id关联meet_restaurant_arrange的id.
             */
            EnrollPerson enrollPerson = hotelArrangeService.selectPerson(excelInRestaurantVO.getPersonName().trim(), excelInRestaurantVO.getPhone().trim(),meetId);

            if (enrollPerson == null) {
                return ReturnBean.error("存在导入的数据在人员信息中找不到！");
            }

            /**
             * 通过meet_id去中间表meet_restaurant_service查询rest_id,
             * 再通过rest_id关联meet_restaurant的id查询出餐厅名称,判断导入的餐厅名称是否存在.如果不存在,提示！
             */
            List<MeetRestaurantArrange> meetRestaurantArranges = baseMapper.selectByRestName(meetId);
            boolean flag = true;
            for (MeetRestaurantArrange restaurantArrange : meetRestaurantArranges) {
                if (excelInRestaurantVO.getDiningName().equals(restaurantArrange.getDiningName())) {
                    restaurantArrange.setUuid(uuid);
                    restaurantArrange.setRestId(enrollPerson.getId());
                    restaurantArrange.setMeetId(meetId);
                    restaurantArrange.setDiningName(excelInRestaurantVO.getDiningName());
                    restaurantArrange.setSeatMember(StringUtil.replaceBlank(excelInRestaurantVO.getSeatMember()));
                    restaurantArrange.setTime(excelInRestaurantVO.getTime());
                    restaurantArrangeList.add(restaurantArrange);
                    flag = false;
                    break;
                }
            }
            if (flag){
                return ReturnBean.error("第" + (i + 3) +"行导入的餐厅名称不匹配！");
            }
        }

        //保存数据
        if (restaurantArrangeList.size()>0){
            this.saveBatch(restaurantArrangeList);
        }
        return ReturnBean.ok();
    }
}

映射层-->MraMapper

package com.krt.meet.mapper;

import com.krt.common.core.base.BaseMapper;
import com.krt.meet.entity.MeetRestaurantArrange;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * 餐厅安排映射层
 *
 * @author lf
 * @version 1.0
 * @date 2022年06月07日
 */
@Mapper
public interface MraMapper extends BaseMapper<MeetRestaurantArrange> {

    /**
     * 查询餐厅名称
     * @param meetId
     * @return
     */
    List<MeetRestaurantArrange> selectByRestName(Integer meetId);

}

sql-->MraMapper.xml

<?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.krt.meet.mapper.MraMapper">

    <sql id="Base_Column_List">
        t3.dining_name,t3.map_address,t3.lat,t3.lng
    </sql>

    <select id="selectByRestName" resultType="com.krt.meet.entity.MeetRestaurantArrange">
        SELECT
        <include refid="Base_Column_List"/>
        FROM meet_restaurant_service t2
        LEFT JOIN meet_restaurant t3 ON t3.id = t2.restaurant_id
        WHERE
        t2.meet_id = #{meetId}
    </select>
</mapper>