xls导入功能模块

于 2025-02-27 17:36:24 发布
阅读量303 收藏 9
点赞数 5
分类专栏： JAVA 文章标签： 状态模式
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_50623553/article/details/145909839
版权

xls导入功能模块

1. 功能概述

本模块实现了批量导入功能。用户可以通过前端页面选择Excel文件（.xls 或 .xlsx 格式）进行数据导入，系统会将文件中的数据解析并存储到数据库中。导入过程中，系统会对数据进行校验，确保数据的完整性和正确性。

2. 数据流

数据流从前端到后端，依次经过以下层次：
前端：用户通过页面选择文件并触发导入操作。
Controller层：接收前端请求，调用Service层处理逻辑。
Service层：处理业务逻辑，调用Mapper层进行数据库操作。
Mapper层：执行具体的数据库增删改查操作。
Mapper.xml：定义SQL语句，完成数据库操作。**

3. 前端实现

3.1 页面布局

前端使用Element UI组件实现文件上传功能。用户可以通过点击“批量导入”按钮选择文件，并触发导入操作。

<el-row style="margin-top: 1%">
         <div style="height: 5vh">
            <el-button class="el-icon-upload" type="" @click="handleImport">批量导入</el-button>
            <el-button><el-link type="primary" :underline="false" style="font-size:12px;vertical-align: baseline;"
                     @click="importTemplate">下载模板
            </el-link>
            </el-button>
          </div>
</el-row>

<!-- 用户导入对话框 -->
    <el-dialog :title="upload.title" :visible.sync="upload.open" width="400px" append-to-body>
      <treeselect v-model="upload.projectId"
                  placeholder="请选择项目"/>
      <el-upload
        ref="upload"
        :limit="1"
        accept=".xlsx, .xls"
        :headers="upload.headers"
        :action="upload.url"
        :disabled="upload.isUploading"
        :on-progress="handleFileUploadProgress"
        :on-success="handleFileSuccess"
        :auto-upload="false"
        drag
      >
        <i class="el-icon-upload"></i>
        <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
        <div class="el-upload__tip text-center" slot="tip">
          <!-- <div class="el-upload__tip" slot="tip">
            <el-checkbox v-model="upload.updateSupport" /> 是否更新已经存在的用户数据
          </div> -->
          <span>仅允许导入xls、xlsx格式文件。</span>
          <el-link type="primary" :underline="false" style="font-size:12px;vertical-align: baseline;"
                   @click="importTemplate">下载模板
          </el-link>
        </div>
      </el-upload>

      <div slot="footer" class="dialog-footer">
        <el-button type="primary" @click="submitFileForm">确 定</el-button>
        <el-button @click="upload.open = false">取 消</el-button>
      </div>
    </el-dialog>

3.2 数据与方法

前端通过data定义上传参数，并通过methods实现文件上传、下载模板等功能。

data() {
    return {
        // 用户导入参数
        upload: {
        // 是否显示弹出层（用户导入）
        open: false,
        // 弹出层标题（用户导入）
        title: "",
        // 是否禁用上传
        isUploading: false,
        // 是否更新已经存在的用户数据
        updateSupport: 0,
        projectId: null,
        // 设置上传的请求头部
        headers: {Authorization: "Bearer " + getToken()},
        // 上传的地址
        url: process.env.VUE_APP_BASE_API + "people/detect/importData"
        },
    }
  },

methods: {
    /** 下载模板操作 */
    importTemplate() {
      this.download('people/detect/importTemplate', {}, `people_detect_${new Date().getTime()}.xlsx`)
    },
    /** 导入按钮操作 */
    handleImport() {
      this.upload.title = "导入";
      this.upload.open = true;
    },
    // 文件上传中处理
    handleFileUploadProgress(event, file, fileList) {
      this.upload.isUploading = true;
    },
    // 文件上传成功处理
    handleFileSuccess(response, file, fileList) {
      this.upload.open = false;
      this.upload.isUploading = false;
      this.$refs.upload.clearFiles();
      this.$alert("<div style='overflow: auto;overflow-x: hidden;max-height: 70vh;padding: 10px 20px 0;'>" + response.msg + "</div>", "导入结果", { dangerouslyUseHTMLString: true });
      this.getList();
    },
    // 提交上传文件
    submitFileForm() {
      this.$refs.upload.submit();
    }
}

效果：在这里插入图片描述

4. 后端实现

4.1 实体类

实体类RoadPeopleDetect定义了道路人工检测数据的字段，并使用@Excel注解标记字段与Excel列的映射关系。

public class RoadPeopleDetect extends BaseEntity {
    private int id; // 记录ID
    @Excel(name = "道路id") private int roadId; // 道路ID
    @Excel(name = "分局id") private int deptId; // 分局ID
    @Excel(name = "路线名称") private String roadNum; // 路线名称
    @Excel(name = "起点桩号") private String milepostStart; // 起点桩号
    @Excel(name = "终点桩号") private String milepostEnd; // 终点桩号
    @Excel(name = "路线总长度") private int roadSumLength; // 路线总长度
    @Excel(name = "路面宽度") private int roadWidth; // 路面宽度
    @Excel(name = "DR") private double dr; // DR值
    @Excel(name = "PCI") private double pci; // PCI值
    @Excel(name = "损坏大类") private String damageCategory; // 损坏大类
    @Excel(name = "损坏小类") private String damageType; // 损坏小类
    @Excel(name = "损坏程度") private String damageLevel; // 损坏程度
    @Excel(name = "对应权重") private double weightValue; // 对应权重
    @Excel(name = "每百米损坏程度") private String meterDamageLevel; // 每百米损坏程度
    @Excel(name = "累计损坏程度") private String sumDamageLevel; // 累计损坏程度
    @JsonFormat(pattern = "yyyy-MM-dd") @Excel(name = "调查时间", width = 30, dateFormat = "yyyy-MM-dd") private Date surveyDate; // 调查时间
    @Excel(name = "调查方向") private String surveyDirection; // 调查方向
    @Excel(name = "调查人") private String surveyPeople; // 调查人
    @Excel(name = "所属分局") private String maintenanceUnit; // 所属分局
    private String attr1; // 预留字段1
    private String attr2; // 预留字段2
    private Long attr3; // 预留字段3
    private Long attr4; // 预留字段4
    // Getter和Setter方法省略
    // ToString方法省略
}

4.2 Controller层

RoadPeopleDetectController负责接收前端请求，调用Service层处理导入逻辑。

@RestController
@RequestMapping("/people/detect")
public class RoadPeopleDetectController extends BaseController {

    @Autowired
    private IRoadPeopleDetectService roadPeopleDetectService;


    /*导入模板*/
    @PostMapping("/importTemplate")
    public void importTemplate(HttpServletResponse response)
    {
        ExcelUtil<RoadPeopleDetect> util = new ExcelUtil<RoadPeopleDetect>(RoadPeopleDetect.class);

        util.importTemplateExcel(response, "导入数据");
    }

    /*导入*/
    @PostMapping("/importData")
    public AjaxResult importData(MultipartFile file) throws Exception
    {
        ExcelUtil<RoadPeopleDetect> util = new ExcelUtil<RoadPeopleDetect>(RoadPeopleDetect.class);
        List<RoadPeopleDetect> roadPeopleDetectList = util.importExcel(file.getInputStream());

        String message = roadPeopleDetectService.importRoadPeopleDetect(roadPeopleDetectList);
        return success(message);
    }
}

4.3 Service层

public interface IRoadPeopleDetectService {

    //导入
    public String importRoadPeopleDetect(List<RoadPeopleDetect> roadPeopleDetectList);
}

RoadPeopleDetectServiceImpl实现导入逻辑，包括数据校验和数据库操作。

@Service
public class RoadPeopleDetectServiceImpl implements IRoadPeopleDetectService {

    @Autowired
    private RoadPeopleDetectMapper roadPeopleDetectMapper;

    @Transactional // 确保事务提交
    @Override
    public String importRoadPeopleDetect(List<RoadPeopleDetect> roadPeopleDetectList) {
        if (StringUtils.isNull(roadPeopleDetectList) || roadPeopleDetectList.size() == 0) {
            throw new ServiceException("导入入库数据不能为空！");
        }

        int successNum = 0;
        int failureNum = 0;
        StringBuilder successMsg = new StringBuilder();
        StringBuilder failureMsg = new StringBuilder();
        for (RoadPeopleDetect roadPeopleDetect : roadPeopleDetectList) {

            try {
                int count = roadPeopleDetectMapper.countByAllFieldsExceptId(roadPeopleDetect);
                if(count == 0)
                {
                    roadPeopleDetectMapper.insertRoadPeopleDetect(roadPeopleDetect);
                    successNum++;
                    successMsg.append("<br/>" + successNum + "、导入成功");
                    System.out.println("插入后的 ID: " + roadPeopleDetect.getId()); // 打印插入后的 ID

                }
                else {
                    failureNum++;
                    failureMsg.append("<br/>第" + failureNum + "条数据已存在");
                }
            } catch (Exception e) {
                failureNum++;
                String msg = "<br/>" + failureNum + "、导入失败：";
                failureMsg.append(msg + e.getMessage());
                e.printStackTrace(); // 打印堆栈信息
            }
        }
        if (failureNum > 0) {
            failureMsg.insert(0, "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确，错误如下：");
            throw new ServiceException(failureMsg.toString());
//            throw new ServiceException("您导入的表格中有数据格式错误，请修改后重新上传！");
        } else {
            successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");
        }
        System.out.println(roadPeopleDetectList);
        return successMsg.toString();
    }
}

4.4 Mapper层

RoadPeopleDetectMapper定义了数据库操作方法，mapper.xml文件实现了具体的SQL语句。

@Mapper
public interface RoadPeopleDetectMapper {
    public int insertRoadPeopleDetect(RoadPeopleDetect roadPeopleDetect);

//    public int updateRoadPeopleDetect(RoadPeopleDetect roadPeopleDetect);
    //特殊sql：查询表中是否有相同数据
    int countByAllFieldsExceptId(RoadPeopleDetect roadPeopleDetect);
}

mapper.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.ruoyi.road.mapper.RoadPeopleDetectMapper">

    <resultMap type="RoadPeopleDetect" id="RoadPeopleDetectResult">
        <!-- 主键字段 -->
        <result property="id" column="id" />

        <!-- 普通字段 -->
        <result property="roadId" column="road_id" />
        <result property="deptId" column="dept_id" />
        <result property="roadNum" column="road_num" />
        <result property="milepostStart" column="milepost_start" />
        <result property="milepostEnd" column="milepost_end" />
        <result property="roadSumLength" column="road_sum_length" />
        <result property="roadWidth" column="road_width" />
        <result property="dr" column="dr" />
        <result property="pci" column="pci" />
        <result property="damageCategory" column="damage_category" />
        <result property="damageType" column="damage_type" />
        <result property="damageLevel" column="damage_level" />
        <result property="weightValue" column="weight_value" />
        <result property="meterDamageLevel" column="meter_damage_level" />
        <result property="sumDamageLevel" column="sum_damage_level" />
        <result property="surveyDate" column="survey_date" />
        <result property="surveyDirection" column="survey_direction" />
        <result property="surveyPeople" column="survey_people" />
        <result property="attr1" column="attr1" />
        <result property="attr2" column="attr2" />
        <result property="attr3" column="attr3" />
        <result property="attr4" column="attr4" />

        <!-- 新增字段 -->
        <result property="maintenanceUnit" column="maintenance_unit" />
        <result property="mqi" column="mqi" />
        <result property="pqi" column="pqi" />
        <result property="rqi" column="rqi" />
    </resultMap>

    <sql id="selectRoadPeopleDetectVo">
        SELECT
            id, road_id, dept_id, road_num, milepost_start, milepost_end, road_sum_length, road_width,
            dr, pci, damage_category, damage_type, damage_level, weight_value, meter_damage_level,
            sum_damage_level, survey_date, survey_direction, survey_people, attr1, attr2, attr3, attr4,
            maintenance_unit, mqi, pqi, rqi
        FROM road_people_detect_copy1    </sql>


    <select id="selectRoadPeopleDetectList" parameterType="RoadPeopleDetect" resultMap="RoadPeopleDetectResult">
        <include refid="selectRoadPeopleDetectVo"></include>
        <where>
            <!-- 道路ID -->
            <if test="roadId != null"> and road_id = #{roadId}</if>
            <!-- 分局ID -->
            <if test="deptId != null"> and dept_id = #{deptId}</if>
            <!-- 路线名称（模糊查询） -->
            <if test="roadNum != null and roadNum != ''"> and road_num like concat('%', #{roadNum}, '%')</if>
            <!-- 起点桩号 -->
            <if test="milepostStart != null and milepostStart != ''"> and milepost_start = #{milepostStart}</if>
            <!-- 终点桩号 -->
            <if test="milepostEnd != null and milepostEnd != ''"> and milepost_end = #{milepostEnd}</if>
            <!-- 路线总长度 -->
            <if test="roadSumLength != null"> and road_sum_length = #{roadSumLength}</if>
            <!-- 路面宽度 -->
            <if test="roadWidth != null"> and road_width = #{roadWidth}</if>
            <!-- DR值 -->
            <if test="dr != null"> and dr = #{dr}</if>
            <!-- PCI值 -->
            <if test="pci != null"> and pci = #{pci}</if>
            <!-- 损坏大类 -->
            <if test="damageCategory != null and damageCategory != ''"> and damage_category = #{damageCategory}</if>
            <!-- 损坏小类 -->
            <if test="damageType != null and damageType != ''"> and damage_type = #{damageType}</if>
            <!-- 损坏程度 -->
            <if test="damageLevel != null and damageLevel != ''"> and damage_level = #{damageLevel}</if>
            <!-- 对应权重 -->
            <if test="weightValue != null"> and weight_value = #{weightValue}</if>
            <!-- 每百米损坏程度 -->
            <if test="meterDamageLevel != null and meterDamageLevel != ''"> and meter_damage_level = #{meterDamageLevel}</if>
            <!-- 累计损坏程度 -->
            <if test="sumDamageLevel != null and sumDamageLevel != ''"> and sum_damage_level = #{sumDamageLevel}</if>
            <!-- 调查时间 -->
            <if test="surveyDate != null"> and survey_date = #{surveyDate}</if>
            <!-- 调查方向 -->
            <if test="surveyDirection != null and surveyDirection != ''"> and survey_direction = #{surveyDirection}</if>
            <!-- 调查人 -->
            <if test="surveyPeople != null and surveyPeople != ''"> and survey_people = #{surveyPeople}</if>
            <!-- 预留字段1 -->
            <if test="attr1 != null and attr1 != ''"> and attr1 = #{attr1}</if>
            <!-- 预留字段2 -->
            <if test="attr2 != null and attr2 != ''"> and attr2 = #{attr2}</if>
            <!-- 预留字段3 -->
            <if test="attr3 != null"> and attr3 = #{attr3}</if>
            <!-- 预留字段4 -->
            <if test="attr4 != null"> and attr4 = #{attr4}</if>
            <!-- 新增字段 -->
            <if test="maintenanceUnit != null and maintenanceUnit != ''"> and maintenance_unit = #{maintenanceUnit}</if>
            <if test="mqi != null"> and mqi = #{mqi}</if>
            <if test="pqi != null"> and pqi = #{pqi}</if>
            <if test="rqi != null"> and rqi = #{rqi}</if>
        </where>
    </select>


    <insert id="insertRoadPeopleDetect" parameterType="RoadPeopleDetect" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO road_people_detect_copy1
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="roadId != null">road_id,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="roadNum != null">road_num,</if>
            <if test="milepostStart != null">milepost_start,</if>
            <if test="milepostEnd != null">milepost_end,</if>
            <if test="roadSumLength != null">road_sum_length,</if>
            <if test="roadWidth != null">road_width,</if>
            <if test="dr != null">dr,</if>
            <if test="pci != null">pci,</if>
            <if test="damageCategory != null">damage_category,</if>
            <if test="damageType != null">damage_type,</if>
            <if test="damageLevel != null">damage_level,</if>
            <if test="weightValue != null">weight_value,</if>
            <if test="meterDamageLevel != null">meter_damage_level,</if>
            <if test="sumDamageLevel != null">sum_damage_level,</if>
            <if test="surveyDate != null">survey_date,</if>
            <if test="surveyDirection != null">survey_direction,</if>
            <if test="surveyPeople != null">survey_people,</if>
            <if test="attr1 != null">attr1,</if>
            <if test="attr2 != null">attr2,</if>
            <if test="attr3 != null">attr3,</if>
            <if test="attr4 != null">attr4,</if>
            <!-- 新增字段 -->
            <if test="maintenanceUnit != null">maintenance_unit,</if>
            <if test="mqi != null">mqi,</if>
            <if test="pqi != null">pqi,</if>
            <if test="rqi != null">rqi,</if>
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="roadId != null">#{roadId},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="roadNum != null">#{roadNum},</if>
            <if test="milepostStart != null">#{milepostStart},</if>
            <if test="milepostEnd != null">#{milepostEnd},</if>
            <if test="roadSumLength != null">#{roadSumLength},</if>
            <if test="roadWidth != null">#{roadWidth},</if>
            <if test="dr != null">#{dr},</if>
            <if test="pci != null">#{pci},</if>
            <if test="damageCategory != null">#{damageCategory},</if>
            <if test="damageType != null">#{damageType},</if>
            <if test="damageLevel != null">#{damageLevel},</if>
            <if test="weightValue != null">#{weightValue},</if>
            <if test="meterDamageLevel != null">#{meterDamageLevel},</if>
            <if test="sumDamageLevel != null">#{sumDamageLevel},</if>
            <if test="surveyDate != null">#{surveyDate},</if>
            <if test="surveyDirection != null">#{surveyDirection},</if>
            <if test="surveyPeople != null">#{surveyPeople},</if>
            <if test="attr1 != null">#{attr1},</if>
            <if test="attr2 != null">#{attr2},</if>
            <if test="attr3 != null">#{attr3},</if>
            <if test="attr4 != null">#{attr4},</if>
            <!-- 新增字段 -->
            <if test="maintenanceUnit != null">#{maintenanceUnit},</if>
            <if test="mqi != null">#{mqi},</if>
            <if test="pqi != null">#{pqi},</if>
            <if test="rqi != null">#{rqi},</if>
        </trim>
    </insert>

<!--    <update id="updateRoadPeopleDetect" parameterType="RoadPeopleDetect">-->
<!--        UPDATE road_people_detect_copy1-->
<!--        <trim prefix="SET" suffixOverrides=",">-->
<!--            <if test="roadId != null">road_id = #{roadId},</if>-->
<!--            <if test="deptId != null">dept_id = #{deptId},</if>-->
<!--            <if test="roadNum != null">road_num = #{roadNum},</if>-->
<!--            <if test="milepostStart != null">milepost_start = #{milepostStart},</if>-->
<!--            <if test="milepostEnd != null">milepost_end = #{milepostEnd},</if>-->
<!--            <if test="roadSumLength != null">road_sum_length = #{roadSumLength},</if>-->
<!--            <if test="roadWidth != null">road_width = #{roadWidth},</if>-->
<!--            <if test="dr != null">dr = #{dr},</if>-->
<!--            <if test="pci != null">pci = #{pci},</if>-->
<!--            <if test="damageCategory != null">damage_category = #{damageCategory},</if>-->
<!--            <if test="damageType != null">damage_type = #{damageType},</if>-->
<!--            <if test="damageLevel != null">damage_level = #{damageLevel},</if>-->
<!--            <if test="weightValue != null">weight_value = #{weightValue},</if>-->
<!--            <if test="meterDamageLevel != null">meter_damage_level = #{meterDamageLevel},</if>-->
<!--            <if test="sumDamageLevel != null">sum_damage_level = #{sumDamageLevel},</if>-->
<!--            <if test="surveyDate != null">survey_date = #{surveyDate},</if>-->
<!--            <if test="surveyDirection != null">survey_direction = #{surveyDirection},</if>-->
<!--            <if test="surveyPeople != null">survey_people = #{surveyPeople},</if>-->
<!--            <if test="attr1 != null">attr1 = #{attr1},</if>-->
<!--            <if test="attr2 != null">attr2 = #{attr2},</if>-->
<!--            <if test="attr3 != null">attr3 = #{attr3},</if>-->
<!--            <if test="attr4 != null">attr4 = #{attr4},</if>-->
<!--        </trim>-->
<!--        WHERE id = #{id}-->
<!--    </update>-->



    <select id="countByAllFieldsExceptId" parameterType="RoadPeopleDetect" resultType="int">
        SELECT COUNT(*)
        FROM road_people_detect_copy1
        WHERE
            road_id = #{roadId} AND
            dept_id = #{deptId} AND
            road_num = #{roadNum} AND
            milepost_start = #{milepostStart} AND
            milepost_end = #{milepostEnd} AND
            road_sum_length = #{roadSumLength} AND
            road_width = #{roadWidth} AND
            dr = #{dr} AND
            pci = #{pci} AND
            damage_category = #{damageCategory} AND
            damage_type = #{damageType} AND
            damage_level = #{damageLevel} AND
            weight_value = #{weightValue} AND
            meter_damage_level = #{meterDamageLevel} AND
            sum_damage_level = #{sumDamageLevel} AND
            survey_date = #{surveyDate} AND
            survey_direction = #{surveyDirection} AND
            survey_people = #{surveyPeople} AND
            maintenance_unit = #{maintenanceUnit} AND
            mqi = #{mqi} AND
            pqi = #{pqi} AND
            rqi = #{rqi}
    </select>

</mapper>
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值