读取excel表格，将数据按期存储到数据库及增删改查操作_excelimport.form1.getexceldata(string excelfilepat-优快云博客

读取excel表格，将数据按期存储到数据库及增删改查操作

项目中做了一个读取excel表格按期存储到到数据库的功能，在这里写一下做一个备忘总结，废话不多说，代码走起！

上传页面 excelimport.html：

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head th:include="include :: header"></head>
<link th:href="@{/css/fileinput.min.css}" href="/static/css/fileinput.min.css" rel="stylesheet"/>
<link th:href="@{/css/bootstrap-select.min.css}" href="/static/css/fileinput.min.css" rel="stylesheet"/>       
    <style>
        .container {
            margin-top: 20px;
        }
        @media (max-width: 768px){
            .db{
                margin-bottom: 10px;
            }
        }

        .btn{
            clear: both;
        }
.form{
    padding: 20px;
}
        .qs{
            clear: both;
            margin-top: 35px;
        }
        .import{
            padding-top: 20px;
        }
        select{
            padding: 10px!important;
            font-siz:18px!important;
        }

    </style>

<body>
<form action="#" th:action="@{/excel/upload}" enctype="multipart/form-data" method="post" class="form">
    <div class="col-md-4">

           <!--<input type="file" class="file" name="file" multiple data-show-upload="true" data-show-caption="true" data-show-preview="false">-->
            <input  type="file" class="file" name="file" multiple data-show-upload="true" data-show-caption="true" data-show-preview="false">

    </div>
    <a th:href="@{/excel/pk.xlsx}"><button type="button" class="btn btn-primary">下载模板</button></a>
    <br/>

   <div class="qs navbar-left ">
        <label>选择期数：</label>
        <select class="selectpicker" name="state" th:with="type=${@dict.getType('sys_normal_disable')}">
        <option value="0">最新一期</option>
        <option th:each="dict : ${list1}" th:text="${dict.text}" th:value="${dict.id}"></option>
    </select>
    </div>
<br>
    <div class="col-md-4 import">
        <button type="submit" class="btn btn-success">开始导入</button>

    </div>


</form>
<div th:include="include :: footer"></div>
<script th:src="@{/js/fileinput.min.js}"></script>
<script th:src="@{/ajax/libs/select/select2.js}"></script>
<script th:inline="javascript"> var ctx = [[@{/}]]; </script>
<script th:inline="javascript">
  $(function(){
   $(".fileinput-upload-button").hide();
      $(".select2-search__field").hide();
  })
</script>
</body>
</html>

实体类 ExcelImport：

/**
 * 需要导入到数据库的实体类
 */
public class ExcelImport extends BaseEntity{
    //id
    private String id;
    //乡镇
    private String xz;
    //公民身份号码
    private String idNumber;
    //姓名
    private String name;
    //劳动能力
    private String ldnl;
    //学历
    private String education;
    //政治面貌
    private String zzmm;
    //健康状况
    private String jkzk;
    //联系电话
    private String phone;
    //居住所在地
    private String jzszd;
    //与户主关系
    private String gx;
    //技能等级
    private String jndj;
    //技术特长
    private String jstc;
    //是否已经就业
    private String sfjy;
    //就业单位
    private String jydw;
    //就业时间
    private Date jysj;
    //月工资收入(元)
    private Double ygzsr;
    //有无就业意愿
    private String ywjyyx;
    //需要政策咨询
    private String xyzzzx;
    //需要招聘信息
    private String xyzpxx;
    //需要职业指导
    private String xyzyzd;
    //就业形式意愿
    private String jrxsyy;
    //就业地区意愿
    private String jydqyy;
    //签订合同意愿
    private String qdhtyy;
    //工种名称
    private String gzmc;
    //月工资收入预期(元)
    private Double ygzsryq;
    //需要参加技能培训
    private String xycjjnpx;
    //需要参加雨露计划
    private String xycjyljh;
    //需要参加“工学一体”
    private String xycjgxyt;
    //需要技能鉴定
    private String xyjnjd;
    //工种名称
    private String gzname;
    //参加脱产培训意愿
    private String cjtcpxyy;
    //培训地点意愿
    private String pxddyy;
    //培训时长意愿
    private String pxscyy;
    //有无创业意愿
    private String ywcyyy;
    //需要参加创业培训
    private String xycjcypx;
    //需要提供创业项目
    private String xytgcyxm;
    //需要创业贷款
    private String xycydk;
    //需要专家服务
    private String xyzjfw;
    //需要创业项目的类别
    private String xycyxmdlb;
    //需要专家的类别
    private String xyzjdlb;
    //脱贫标志
    private String tpbz;
    //脱贫时间
    private Date tpsj;
    //状态
    private Integer state;
}

service接口：

public interface ExcelImportService {

     String readExcelFile(MultipartFile file,Integer state);

     /*查询所有的表格信息*/
     List<ExcelImport> findExcel(ExcelImport excelImport);

     /*查询表格信息详情*/
     ExcelImport findOneExcel(String id);

     /*修改表格信息*/
     int updateExcel(ExcelImport excelImport);

     /*查询所有期数*/
     List<Integer> findAllState();

     /*从excel表中查询每一期和其下的数据条数*/
     List<ExcelNumber> findStateNumber();

     /*添加数据到数据统计数据库*/
     void insertNumber(ExcelNumber number);

     /*从数据统计表中查询每一期和其下的数据条数*/
     List<ExcelNumber> findNumber();
}

service实现类：

import com.ruoyi.system.domain.ExcelImport;
import com.ruoyi.system.domain.ExcelNumber;
import com.ruoyi.system.mapper.ExcelImplMapper;
import com.ruoyi.system.service.ExcelImportService;
import com.ruoyi.system.service.ExcelUtil2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;


@Service
public class ExcelImportServiceImpl implements ExcelImportService {


    @Autowired
    private ExcelImplMapper excelImplMapper;

    @Override
    public void insertNumber(ExcelNumber number) {
        excelImplMapper.insertNumber(number);
    }

    @Override
    public List<ExcelNumber> findNumber() {
        return excelImplMapper.findNumber();
    }

    @Override
    public String readExcelFile(MultipartFile file,Integer state) {
        String result = "";
        //查询所有的期数
        List<Integer> list = excelImplMapper.findAllState();
        for (Integer sta : list) {
            if(sta==state){
                ExcelUtil2 excel = new ExcelUtil2();
                List<ExcelImport> ilist = excel.getExcelInfo(file,state);
                if(ilist != null && !ilist.isEmpty()){
                    //不为空的话添加到数据库
                    for (ExcelImport excelImport : ilist) {
                        //excelImplMapper.save(excelImport);
                        excelImplMapper.addExcel(excelImport);
                    }
                    result = "上传成功";

                    return result;
                }else{
                    result = "上传失败";
                }
            }

        }

        state = excelImplMapper.findState();

        if (state==null || state==0 || state.equals(0)){
            state = 1;
        }else {
            state+=1;
        }