读取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;
}
ExcelUtil2 excel = new ExcelUtil2();
List<ExcelImport> ilist = excel.getExcelInfo(file,state);
if(ilist != null && !ilist.isEmpty()){
//不为空的话添加到数据库
for (ExcelImport excelImport : ilist) {
//excelImplMapper.save(excelImport);
最低0.47元/天 解锁文章
224
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
