批量导入功能java实现(从前端到后端)
程序员文章站
2022-06-23 11:43:49
上传文件获取后台返回数据功能分两种方法实现1、通过FormData获取文件提交表单，不支持ie8、ie9，只支持ie11及其谷歌、火狐等浏览器。js写法：function uploa(){ var fileDir = $("#file").val(); var suffix = fileDir.substr(fileDir.lastIndexOf(".")); if("" == fileDir){ layer.alert("选择需要导入的Excel文件！");...
上传文件获取后台返回数据功能分两种方法实现
1、通过FormData获取文件提交表单，不支持ie8、ie9，只支持ie11及其谷歌、火狐等浏览器。
js写法：
function uploa(){
var fileDir = $("#file").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
if("" == fileDir){
layer.alert("选择需要导入的Excel文件！");
return false;
}
if(".xls" != suffix && ".xlsx" != suffix ){
layer.alert("选择Excel格式的文件导入！");
return false;
}
var formData = new FormData($("#form1")[0]);//获取表单中的文件
alert(formData);
//alert(formData);
$.ajax({
url:"uploadWin.do",//后台的接口地址
type:"post",//post请求方式
data:formData,//参数
cache: false,
processData: false,
contentType: false,
success:function (data) {
layer.alert(data.flag);
$('#updateArea').dialog('close');
queryList(1);
var obj = document.getElementById('file') ;
obj.outerHTML=obj.outerHTML;
},error:function () {
layer.alert("请重新检查上传模板内容，严格按照字段规范进行填写!");
var obj = document.getElementById('file') ;
obj.outerHTML=obj.outerHTML;
}
})
}
后台写法：
/**
* 批量上传群障告警池号码
* @param request
* @param response
* HttpServletRequest request, HttpServletResponse response
* @return
* @throws Exception
*/
@RequestMapping(value="uploadWin.do",method = RequestMethod.POST)
@ResponseBody
public Map<String, Object> batchUpWinInfoExcel(MultipartFile file, HttpSession session) throws Exception {
String s = portalService.ajaxUploadExcel(file,session);
Map<String, Object> result = new HashMap<>();
result.put("flag",s);
return result;
}
2、通过ajaxSubmit提交表单，支持ie8、ie9及其以上
需要引入：/jquery.form.js
注意从后台获取到字符串返回值时，ie8、ie9、ie11获取到的字符串值不一样，需要代码处理一下
后台注意加返回类型：produces = "text/plain;charset=UTF-8")
ie8返回：{"flag":"请严格按照“群障号码告警池导入规范”填写！一个群障号码只能导入一行数据,请检查第1行数据"}
ie9返回：<pre>{"flag":"请严格按照“群障号码告警池导入规范”填写！一个群障号码只能导入一行数据,请检查第1行数据"}</pre>
ie11返回：<PRE>{"flag":"请严格按照“群障号码告警池导入规范”填写！一个群障号码只能导入一行数据,请检查第1行数据"}</PRE>
js
function upl(){
var fileDir = $("#file").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
if("" == fileDir){
layer.alert("选择需要导入的Excel文件！");
return false;
}
if(".xls" != suffix && ".xlsx" != suffix ){
layer.alert("选择Excel格式的文件导入！");
return false;
}
$('#form1').ajaxSubmit({
type:"post",
url:"uploadWin.do",
data:$('#form1').serialize(),
//dataType:"json",
error:function(data){
layer.alert("请重新检查上传模板内容，严格按照字段规范进行填写!");
var obj = document.getElementById('file') ;
obj.outerHTML=obj.outerHTML;
},
success:function(data){
var data=data.replace('<PRE>','');
data=data.replace('</PRE>','');
data=data.replace('<pre>','');
data=data.replace('</pre>','');
//alert(data);
var parse = JSON.parse(data);
layer.alert(parse.flag);
$('#updateArea').dialog('close');
queryList(1);
var obj = document.getElementById('file') ;
obj.outerHTML=obj.outerHTML;
}
});
}
后台写法：
/**
* 批量上传群障告警池号码
* @param request
* @param response
* HttpServletRequest request, HttpServletResponse response
* @return
* @throws Exception
*/
@RequestMapping(value="uploadWin.do",method = RequestMethod.POST,produces = "text/plain;charset=UTF-8")
@ResponseBody
public String batchUpWinInfoExcel(HttpServletRequest request,HttpServletResponse response, HttpSession session) throws Exception {
String s = portalService.ajaxUploadExcel(request,response,session);
Map<String, Object> result = new HashMap<>();
result.put("flag",s);
return JSON.toJSONString(result);
}
service层
/**
* 告警数据批量导入
* @param request
* @param response
* @return
* @throws Exception
*/
public String ajaxUploadExcel(MultipartFile file, HttpSession session) throws Exception {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("upfile");
if (file.isEmpty()) {
try {
throw new Exception("文件不存在！");
} catch (Exception e) {
e.printStackTrace();
}
}
InputStream in = null;
try {
in = file.getInputStream();
} catch (IOException e) {
e.printStackTrace();
}
List<List<Object>> listob = null;
try {
listob = new ExcelUtils().getBankListByExcel(in, file.getOriginalFilename());
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("导入的数据list" + listob);
//该处可调用service相应方法进行数据保存到数据库中，现只对数据输出
//获取批次
int batch = portalDao.queryNextBatch();
//获取当前工号
AuthInfo authInfo = (AuthInfo) session.getAttribute("authInfo");
String userId = authInfo.getUserId();
List<CspGroupObstalePoolDto> winLists = new ArrayList<>();
List<CspGroupObstalePoolDto> winLists1 = new ArrayList<>();
String acc="";
//批量插入成功量
int a=0;
//s数据返回变量
String s="";
//c异常标识
int c=0;
//b异常行数
int b=0;
if(listob.size()>1000){
return "批量导入条数不能超过1000，请重新导入!";
}else {
for (int i = 0; i < listob.size(); i++) {
List<Object> lo = listob.get(i);
int count = portalDao.queryCountByCallNo(String.valueOf(lo.get(1)));
acc+=String.valueOf(lo.get(1));
if(count>0){
c=1;
s="请严格按照“群障号码告警池导入规范”填写！一个群障号码只能导入一行数据,请检查第"+(i+1)+"行数据";
b=i+1;
winLists1 = except(lo,batch,userId);
break;
}
if(isType(String.valueOf(lo.get(0)))==false){
c=2;
s="请严格按照“群障号码告警池导入规范”填写！群障场景取值范围为1~9,请检查第"+(i+1)+"行数据";
b=i+1;
winLists1 = except(lo,batch,userId);
break;
}
if(isCallNo(String.valueOf(lo.get(1)))==false){
c=3;
s="请严格按照“群障号码告警池导入规范”填写！群障号码填写不规范,请检查第"+(i+1)+"行数据";
b=i+1;
winLists1 = except(lo,batch,userId);
break;
}
if(isArea(String.valueOf(lo.get(2)))==false){
c=4;
s="请严格按照“群障号码告警池导入规范”填写！所属地市填写不规范,请检查第"+(i+1)+"行数据";
b=i+1;
winLists1 = except(lo,batch,userId);
break;
}
if(isDate(String.valueOf(lo.get(3)))==false){
c=5;
s="请严格按照“群障号码告警池导入规范”填写！故障开始时间填写不规范,请检查第"+(i+1)+"行数据";
b=i;
winLists1 = except(lo,batch,userId);
break;
}
if(isDate(String.valueOf(lo.get(4)))==false){
c=6;
s="请严格按照“群障号码告警池导入规范”填写！故障预计结束时间填写不规范,请检查第"+(i+1)+"行数据";
b=i+1;
winLists1 = except(lo,batch,userId);
break;
}
a=a+1;
CspGroupObstalePoolDto vo = new CspGroupObstalePoolDto();
vo.setObstacle_type(String.valueOf(lo.get(0)));// 表格的第一列 注意数据格式需要对应实体类属性
vo.setAccNo(String.valueOf(lo.get(1)));
vo.setArea_code(String.valueOf(lo.get(2)));
vo.setSTARTDATE(String.valueOf(lo.get(3)));
vo.setFREEDATE(String.valueOf(lo.get(4)));
vo.setBatch(batch+1);
vo.setAccUserId(userId);
winLists.add(vo);
System.out.println(vo.toString());
}
try {
if(c==0){
//数据正常批量插入数据
portalDao.insertGroupObstacle(winLists);
CspGroupObstalePoolDto pool = new CspGroupObstalePoolDto();
pool.setAccNo(acc);
pool.setAccUserId(userId);
pool.setMake_flag(2);
pool.setBatch(batch+1);
pool.setFail_count(b);
pool.setSucc_count(a);
//批量插入日志保存
portalDao.insertGroupObstacleLog(pool);
s="文件导入成功，共"+a+"条数据！";
}else{
//数据异常
portalDao.insertGroupObstacleExcep(winLists1);
CspGroupObstalePoolDto pool = new CspGroupObstalePoolDto();
pool.setAccNo(acc);
pool.setAccUserId(userId);
pool.setMake_flag(2);
pool.setBatch(batch+1);
//出错行数
pool.setFail_count(b);
pool.setSucc_count(a);
//批量插入日志保存
portalDao.insertGroupObstacleLog(pool);
}
} catch (Exception e) {
e.printStackTrace();
s="数据插入异常!!!";
}
System.out.println(s);
return s;
}
}
public List<CspGroupObstalePoolDto> except(List<Object> lo,int batch,String userId){
List<CspGroupObstalePoolDto> winLists1 = new ArrayList<>();
CspGroupObstalePoolDto vo = new CspGroupObstalePoolDto();
vo.setObstacle_type(String.valueOf(lo.get(0)));// 表格的第一列 注意数据格式需要对应实体类属性
vo.setAccNo(String.valueOf(lo.get(1)));
vo.setArea_code(String.valueOf(lo.get(2)));
vo.setSTARTDATE(String.valueOf(lo.get(3)));
vo.setFREEDATE(String.valueOf(lo.get(4)));
vo.setBatch(batch+1);
vo.setAccUserId(userId);
winLists1.add(vo);
return winLists1;
}
/**
* 9开头长度三位
* @param param
* @return
*/
public boolean isArea(String param){
String regEx1 ="^(9[0-9]{2})$";
Pattern p = Pattern.compile(regEx1);
Matcher m = p.matcher(param);
return m.find();
}
/**
* 是否是0到9
* @param param
* @return
*/
public boolean isType(String param){
String regEx1 ="^([0-9]{1})$";
Pattern p = Pattern.compile(regEx1);
Matcher m = p.matcher(param);
return m.find();
}
/**
* 是否是手机号码、宽带号码、固话号码
* @param param
* @return
*/
public boolean isCallNo(String param){
String regEx1 ="^([0-9]{11})$";
Pattern p = Pattern.compile(regEx1);
Matcher m = p.matcher(param);
return m.find();
}
/**
* 是否是时间格式
* @param param
* @return
*/
public boolean isDate(String param){
String regEx1 ="^((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29))[\\s]([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$";
Pattern p = Pattern.compile(regEx1);
Matcher m = p.matcher(param);
return m.find();
}
以下是全代码
jsp页面：
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<c:set var="ctx" value="${pageContext.request.contextPath}" />
<!DOCTYPE html>
<html>
<head>
<%-- 如果是360浏览器使用webkit内核，其他浏览器无视此标签 --%>
<meta name="renderer" content="webkit">
<title>群障号码告警池</title>
<%@ include file="/WEB-INF/views/common/taglibs.jsp"%>
<link rel="stylesheet" type="text/css"
href="${ctx}/res/easydropdown/css/easydropdown.csp.css" />
<link rel="stylesheet" type="text/css"
href="${ctx}/res/theme-${uiInfo.theme }/css/iserve/wp/sheetDealCommon.css" />
<link rel="stylesheet" type="text/css"
href="${ctx}/res/theme-${uiInfo.theme }/css/iserve/wp/sheetDealMain.css" />
<link rel="stylesheet" type="text/css"
href="${ctx}/res/theme-${uiInfo.theme }/css/iserve/wp/sheetDealReset.css" />
<link rel="stylesheet" type="text/css"
href="${ctx}/res/theme-${uiInfo.theme }/css/iserve/wp/sheetDealPop.css" />
<link rel="stylesheet" type="text/css"
href="${ctx}/res/theme-${uiInfo.theme }/css/iserve/wp/recordtable.css" />
<link rel="stylesheet" href="${ctx}/res/plugins/GUI-3.0.0/css/rdc.min.css">
<link rel="stylesheet" href="${ctx}/res/plugins/GC-ui1.0.1/css/gui.min.css">
<link rel="stylesheet" type="text/css" href="${ctx}/res/css/cspstyle.css">
<link rel="stylesheet" type="text/css" href="${ctx }/res/css/work-treat.css" />
<script type="text/javascript">
var ctx = '<c:out value="${ctx}"/>';
</script>
<style>
.hid {
display: none;
}
.combo {
background-color: #ffffff;
border-color: #999999;
border-radius: 0
}
.querytab1{
border: 1px solid #ccc;
}
.querytab1 tr{
border: 1px solid #ccc;
}
.querytab1 td{
border: 1px solid #ccc;
}
.sel {
width: 91%;
border: 1px solid #ccc;
height: 28px;
font-size: 18px;
color: #535353;
}
.FixedTitleRow th {
text-align: center;
}
table td {
text-align: center;
}
td img {
width: 20px;
display: inline-block;
vertical-align: middle;
}
/* ie7表格样式设置 */
.common-message .descr-table .table-new th,
.common-message .descr-table .table-new td {
padding: 2px 1px 1px;
width: inherit;
width: auto;
text-align: center;
}
.common-message .descr-table .table-new {
margin-bottom: 5px;
table-layout: auto;
}
.layui-table-box, .layui-table-view {
min-height: 99px;
overflow: hidden;
box-sizing: border-box;
}
.layui-table-header {
*z-index: 2;
*position: absolute;
*top: 0;
*right: 0;
*left: 0;
*overflow: hidden;
}
.layui-table-header table {
border-right: 1px solid #ccc;
}
.layui-table-body {
*position: absolute;
*top: 33px;
*left: 0;
*right: 0;
*bottom: 0;
*overflow: auto;
border-right: 1px solid #ccc;
}
.layui-table-view .layui-form-checkbox {
*position: absolute;
*left: 50%;
*margin-top: 3px;
*margin-left: -9px;
}
.layui-table-page .layui-laypage button,
.layui-table-page .layui-laypage input {
vertical-align: middle;
}
.layui-table-view select[lay-ignore] {
*height: 30px;
*font-size: 14px;
}
#resetform{
height: 26px;
width: 53px;
margin-left: 24px;
color: #fff;
font-size: 12px;
background-color:dodgerblue;
border: none ;
border-radius:4px;
margin-top: -19px;
cursor: pointer;
}
/* #resultTable{border:solid #eee; border-width:1px 0px 0px 1px;} */
/* #resultTable td{border:solid #eee ; border-width:0px 1px 1px 0px; padding:10px 0px;} */
</style>
<!--[if IE 6]>
<script type="text/javascript" src="${ctx}/res/javascript/common/DD_belatedPNG.js"></script>
<script type="text/javascript">
DD_belatedPNG.fix('#downOrUpBtn,.carat');
</script>
<![endif]-->
</head>
<body class="easyui-layout">
<div class="wrap">
<div class="queryWrap">
<div class="queryTitle clearfix">
<span class="left">群障号码告警池</span>
</div>
<form id="queryForm" name="queryForm" method="post" action="#">
<div class="queryCond clearfix" id="slideQueryArea">
<table width="100%" class="querytab">
<tr>
<th>故障开始时间起:</th>
<td><input type="text" id="accTime" class="Wdate"
onfocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss',maxDate:'%y-%M-%d '})"
style="z-index: 19999; width: 89.5%; height: 27px;" /></td>
<th>故障开始时间止:</th>
<td><input type="text" id="endTime" class="Wdate"
onfocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss',maxDate:'%y-%M-%d '})"
style="z-index: 19999; width: 89.5%; height: 27px;" /></td>
<th>群障号码:</th>
<td><input type="text" name="accNo" id="accNo"
class="txtInput" style="width: 89.5%" /></td>
<th>群障场景:</th>
<td><select
id="obstacleType" style="z-index: 19999; width: 90%; height: 28px;font-size:16px;">
<option value="" selected="selected">--请选择--</option>
<c:forEach items="${slTypeList }" var="slType">
<option value="${ slType.dicCode}">${ slType.dicName}</option>
</c:forEach>
</td>
</tr>
<tr>
<th>所属地市:</th>
<td style="z-index: 9; height: 28px;" id="busiAreaTd"><select
id="busiArea" style="z-index: 19999; width: 90%; height: 28px;font-size:16px;">
<option value="" selected="selected">--请选择--</option>
<c:forEach items="${busiAreas }" var="busiArea">
<option value="${ busiArea.areaCode}">${ busiArea.areaName}</option>
</c:forEach>
</select></td>
<th>导入工号:</th>
<td><input type="text" name="accUserId" id="accUserId"
class="txtInput" /></td>
<th>批次:</th>
<td><input type="text" name="batch" id="batch"
class="txtInput" style="width: 89.5%" /></td>
</tr>
</table>
</div>
<div style="text-align:center;margin-top: 17px;">
<th><a href="javascript:void(0);" id="searchBtn" class="btn btn_sp" onclick="queryList(1);" style="margin-right: -4px;"><span class="lf_btn"></span><span class="rf_btn">查询</span></a></th>
<th><input type="reset" value="重置" id="resetform" name ="resetform" style=""/></th>
</div>
</form>
</div>
</div>
<div>
<div id="updateArea" style="display: none;line-height: 5">
<%--
<form method="post" enctype="multipart/form-data" id="form1">
<table>
<tr style="border: 1px solid #ccc;">
<td style="font-size: 18px;">上传文件: </td>
<td style="font-size: 18px;"><input id="upfile" type="file" name="upfile" accept=".xls,.xlsx" /></td>
<td style="font-size: 18px;"><input type="submit" value="提交" onclick="upload()" /></td>
</tr>
</table>
</form>
--%>
<%-- <h1>文件上传</h1> --%>
<form id="form1" method="post" enctype="multipart/form-data">
<table class="querytab1" style="width: 100%;" border="0.8" cellspacing="0" cellpadding="0">
<tr style="line-height:2">
<td style="text-align: left;width: 25%">
<label>请选择要上传的文件:</label>
</td>
<td colspan="3" style="text-align: left;width: 25%">
<input id="file" name="file" type="file" multiple="multiple" accept=".xls,.xlsx"/>
</td>
<td style="width: 25%"></td>
<td style="width: 25%"></td>
</tr>
<tr style="line-height:2;">
<td style="text-align: left;width: 25%">
<label>模板下载:</label>
</td>
<td colspan="3" style="text-align: left;width: 25%">
<a href="${ctx}/doc/groupObstalePool.xls" download="" id="pickUpOrder" style="text-decoration:underline;color: #0f83f8;"><span>群障告警池批量导入模板</span></a>
</td>
<td style="width: 25%"></td>
<td style="width: 25%"></td>
</tr>
<tr style="line-height:2">
<td colspan="4" style="text-align: left;color: blue;">
<span>注意事项：</span><br>
<span>1、请先点击下载页面上的“群障告警池批量导入模板”，删除里面的样例数据，填写少于1000条的群障号码信息后再上传；</span><br>
<span>2、表格中不允许有重复的群障号码数据，也不能包含已经上传到告警池的群障号码，否则上传失败；</span><br>
<span>3、《群障号码告警池导入模板》中有“群障号码告警池导入规范”，请仔细阅读后再进行填写，请勿随意修改模板中的单元格格式，否则可能导致导入失败。</span>
</td>
</tr>
<tr style="line-height:2">
<td colspan="2" style="width: 50%;text-align: right;padding-right: 2px">
<input id="upload" style="width: 50px" name="upload" type="button" value="上传" onclick="upl()">
</td>
<td colspan="2" style="width: 50%;text-align:left;padding-left: 2px">
<input id="close" style="width: 50px" name="close" type="button" value="关闭" onclick="clo()">
</td>
</tr>
</table>
</form>
</div>
</div>
<div class="queryResult" id="result">
<div class="resHead clearfix">
<div class="left">
<div class="actBtn">
<a href="####" class="btnAct hide" id="pickUpOrder"
onclick="importExcel();"><span class="lf_btn"></span><span
class="rf_btn">批量导入</span></a>
</div>
<div class="actBtn">
<a href="####" class="btnAct hide" id="" onclick="deleteObstale()">
<span class="lf_btn"></span><span class="rf_btn">批量删除</span></a>
</div>
</div>
</div>
<div class="queryTable">
<div class="queryTable record-table">
<table id="resultTable" style="width: 100%" border="0.8" cellspacing="0" cellpadding="0">
<tr class="FixedTitleRow">
<th style="width: 2%"><input type="checkbox" name="chkAll" id='chkAll'
onclick="chkAll(this,'datachkall');" /></th>
<th style="width: 10%">序号</th>
<th style="width: 10%">群障场景</th>
<th style="width: 10%">障碍号码</th>
<th style="width: 10%">所属地市</th>
<th style="width: 10%">故障开始时间</th>
<th style="width: 10%">故障预计结束时间</th>
<th style="width: 10%">导入工号</th>
<th style="width: 10%">录入时间</th>
<th style="width: 10%">批次</th>
</tr>
<tbody id="obstableListTbl">
</tbody>
</table>
</div>
<%--分页组件 --%>
<div class="left" id="pagination"></div>
<div class="left" style="margin: 10px 0px 0px 10px">
<span class="pg_obj" style="color: #727171">每页显示<input
style="width: 50px; height: 20px; color: #000" type="text"
class="txtInput" name="rows" value="10">条
</span>
</div>
<div class="right" style="margin: 10px 15px 0px 0px">
<span>找到<b class="redNum" id="totalRecord"></b>条相关记录
</span>
</div>
</div>
</div>
</div>
<script type="text/javascript"
src="${ctx}/res/My97DatePicker/WdatePicker.js"></script>
<script type="text/javascript"
src="${ctx }/res/theme-${uiInfo.theme }/artDialog4.1.7/artDialog.js"></script>
<script type="text/javascript"
src="${ctx}/res/javascript/csp/obstacle/groupObstalePool.js"></script>
<script type="text/javascript" src="${ctx}/res/plugins/jquery.form.js"></script>
<script type="text/javascript" src="${ctx}/res/plugins/layer/layer.js"></script>
<script type="text/javascript" src="${ctx}/res/plugins/layui/layui.js"></script>
<script type="text/javascript" src="${ctx}/res/javascript/iserve/wp/sheetdeal/jquery.ossPaginator.js"></script>
</body>
</html>
js页面
$(function () {
$('#accTime').val(getNowDay() + " 00:00:00");
$('#endTime').val(getNowDay() + " 23:59:59");
queryList(1);
});
function importExcel(){
$('#updateArea').attr('request','add');
$('#updateArea').dialog({
top:null,
title: '群障告警池批量导入',
width: 700,
iconCls : 'icon-edit',
closed: false,
cache: false,
href : '',
modal: true,
shadow:false
});
}
function clo(){
//alert("123");
$('#updateArea').dialog('close');
}
function upl(){
var fileDir = $("#file").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
if("" == fileDir){
layer.alert("选择需要导入的Excel文件！");
return false;
}
if(".xls" != suffix && ".xlsx" != suffix ){
layer.alert("选择Excel格式的文件导入！");
return false;
}
$('#form1').ajaxSubmit({
type:"post",
url:"uploadWin.do",
data:$('#form1').serialize(),
//dataType:"json",
error:function(data){
layer.alert("请重新检查上传模板内容，严格按照字段规范进行填写!");
var obj = document.getElementById('file') ;
obj.outerHTML=obj.outerHTML;
},
success:function(data){
var data=data.replace('<PRE>','');
data=data.replace('</PRE>','');
data=data.replace('<pre>','');
data=data.replace('</pre>','');
//alert(data);
var parse = JSON.parse(data);
layer.alert(parse.flag);
$('#updateArea').dialog('close');
queryList(1);
var obj = document.getElementById('file') ;
obj.outerHTML=obj.outerHTML;
}
});
}
function down(){
$.ajax({
url:ctx+'/portal/download.do',//后台的接口地址
type:"post",//post请求方式
cache: false,
processData: false,
contentType: false,
success:function (data) {
//alert("123");
},error:function () {
layer.alert("操作失败~");
}
})
}
//JS校验form表单信息
function checkData(){
var fileDir = $("#upfile").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
if("" == fileDir){
alert("选择需要导入的Excel文件！");
return false;
}
if(".xls" != suffix && ".xlsx" != suffix ){
alert("选择Excel格式的文件导入！");
return false;
}
return true;
}
/*$(function () {
*!/
/!*
获取form元素，调用其ajaxForm(...)方法
内部的function(data)的data就是后台返回的数据
*!/
$("#form1").ajaxForm(function (data) {
alert(data);
});
});*/
function queryList(page){
$('#obstableListTbl').html("");
//开始时间
var startTime = $.trim($('#accTime').val());
//结束时间
var endTime = $.trim($('#endTime').val());
//群障号码
var accNo = $.trim($('#accNo').val());
//地市
var busiArea = $.trim($('#busiArea').val());
//工号
var accUserId = $.trim($('#accUserId').val());
//批次
var batch = $.trim($('#batch').val());
//批次
var obstacle_type = $.trim($('#obstacleType').val());
$.ajax({
type: 'post',
url: ctx+'/portal/queryObstacleNoList.do',
dataType:"json",//返回数据类型为json时（controller使用@ResponseBody）必须设置返回的数据类型为json
data:{
'page':page,
'rows':$('input[name="rows"]').val(),
StrTIME:startTime,
endDATE:endTime,
batch:batch,
accUserId:accUserId,
area_code:busiArea,
accNo:accNo,
obstacle_type:obstacle_type
},
success:function(data){
if(data.list) {
var str="";
num = data.list.length;
if(num<1){
$("#obstableListTbl").append("<tr><td colspan='10' align='center'>无数据</tr>");
$("#totalRecord").html(data.total);
return;
}
var page = $('.jump').val();
var conut =$("input[name='rows']").val();
var num = 0;
if(page != undefined){
num = (page-1)*conut;
}
$.each(data.list,function(i,row) {
num++;
//alert(row.OPERATOR);
str+='<tr>';
str+='<td><input type="checkbox" slId="'+row.id+'" name="datachkall" class="checkbtn" οnclick="checkBox(this, event);"/></td>';
str+='<td>'+num+'</td>';
str+='<td>'+obstacleType(row.obstacle_type)+'</td>';
str+='<td>'+formatter(row.accNo)+'</td>';
str+='<td>'+convertAreaCity(formatter(row.area_code))+'</td>';
str+='<td>'+formatter(row.STARTDATE)+'</td>';
str+='<td>'+formatter(row.FREEDATE)+'</td>';
str+='<td>'+ row.accUserId+'</td>';
str+='<td>'+formatter(row.RECORDTIME)+'</td>';
str+='<td>'+formatter(row.batch)+'</td>';
str+='</tr>';
});
$("#totalRecord").html(data.total);
$("#obstableListTbl").html(str);
$("#obstableListTbl").data('scList',data.list);
$('#pagination').ossPaginator({
totalrecords: data.total,
recordsperpage: $('input[name="rows"]').val(),
length: 1,
next: '下一页',
prev: '上一页',
first: '首页',
last: '尾页',
initval: page,//初始化哪一页被选中
controlsalways: true,
onchange: function (newPage){
queryList(newPage);
}
});//初始化分页结束
}
}
});
}
//格式化空值
function formatter(value){
if(typeof value == "null" || $.trim(value).length ==0){
return "无";
}else{
return value;
}
}
//获取当前时间yyyy-MM-dd
function getNowDay() {
var date = new Date();
var month = date.getMonth() + 1;
var year = date.getFullYear();
var day = date.getDate();
return year + "-" + fullZeroTime(month) + "-" + fullZeroTime(day);
}
// 填充时间格式0
function fullZeroTime(time) {
return time <= 9 ? "0" + time : time + "";
}
//地市转码
function convertAreaCity(areaCode) {
if (areaCode == "111") {
return "机密";
} else {
return "未知";
}
}
//0：移动服务故障、1：固网/宽带类故障、2：移动服务大面积故障、3：电信电视故障、
// 4：CRM平台故障、5：计费平台故障、6：充值交费不到账、7：*大面积故障、
function obstacleType(flag){
if(flag==0){
return "移动服务故障";
}else if(flag==1){
return "固网/宽带类故障";
}else if(flag==2){
return "移动服务大面积故障";
}else if(flag==3){
return "电信电视故障";
}else if(flag==4){
return "CRM平台故障";
}else if(flag==5){
return "计费平台故障";
}else if(flag==6){
return "充值交费不到账";
}else if(flag==7){
return "*大面积故障";
}else {
return "其他";
}
}
//删除群障告警池号码
function deleteObstale(){
var ids ="(";
$('#obstableListTbl tr .checkbtn:checked').each(function(i){
ids+= $(this).attr('slId');
ids+=",";
});
ids = ids.substring(0,ids.length-1);
ids+=")";
if(ids==")"){
$.messager.alert('重新选择','请至少选择一条！','info');
return;
}
$.messager.confirm("操作提示", "您确定要执行操作吗？", function (data) {
if (data) {
$.ajax({
url:ctx+'/portal/delObstacleNoList.do',
data:{
ids:ids
},
type:'post',
dataType:"json",
success:function(data){
if(data.flag=='success'){
$.messager.alert('删除成功','成功删除','info');
queryList(1);
}
}
})
}
});
}
//全选checkbox
function chkAll(){
if(document.getElementById("chkAll").checked){
$('#obstableListTbl tr .checkbtn').prop('checked',true);
}else{
$('#obstableListTbl tr .checkbtn').prop('checked',false);
}
}
function uploa(){
var fileDir = $("#file").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
if("" == fileDir){
layer.alert("选择需要导入的Excel文件！");
return false;
}
if(".xls" != suffix && ".xlsx" != suffix ){
layer.alert("选择Excel格式的文件导入！");
return false;
}
var formData = new FormData($("#form1")[0]);//获取表单中的文件
alert(formData);
//alert(formData);
$.ajax({
url:"uploadWin.do",//后台的接口地址
type:"post",//post请求方式
data:formData,//参数
cache: false,
processData: false,
contentType: false,
success:function (data) {
layer.alert(data.flag);
$('#updateArea').dialog('close');
queryList(1);
var obj = document.getElementById('file') ;
obj.outerHTML=obj.outerHTML;
},error:function () {
layer.alert("请重新检查上传模板内容，严格按照字段规范进行填写!");
var obj = document.getElementById('file') ;
obj.outerHTML=obj.outerHTML;
}
})
}
后台代码：
/**
* 批量上传群障告警池号码
* @param request
* @param response
* HttpServletRequest request, HttpServletResponse response
* @return
* @throws Exception
*/
@RequestMapping(value="uploadWin.do",method = RequestMethod.POST,produces = "text/plain;charset=UTF-8")
@ResponseBody
public String batchUpWinInfoExcel(HttpServletRequest request,HttpServletResponse response, HttpSession session) throws Exception {
String s = portalService.ajaxUploadExcel(request,response,session);
Map<String, Object> result = new HashMap<>();
result.put("flag",s);
return JSON.toJSONString(result);
}
/**
* 告警数据批量导入
* @param request
* @param response
* @return
* @throws Exception
*/
public String ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response, HttpSession session) throws Exception {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("file");
if (file.isEmpty()) {
try {
throw new Exception("文件不存在！");
} catch (Exception e) {
e.printStackTrace();
}
}
InputStream in = null;
try {
in = file.getInputStream();
} catch (IOException e) {
e.printStackTrace();
}
List<List<Object>> listob = null;
try {
listob = new ExcelUtils().getBankListByExcel(in, file.getOriginalFilename());
} catch (Exception e) {
e.printStackTrace();
}
//System.out.println("导入的数据list" + listob);
//该处可调用service相应方法进行数据保存到数据库中，现只对数据输出
//获取批次
int batch = portalDao.queryNextBatch();
//获取当前工号
AuthInfo authInfo = (AuthInfo) session.getAttribute("authInfo");
String userId = authInfo.getUserId();
List<CspGroupObstalePoolDto> winLists = new ArrayList<>();
List<CspGroupObstalePoolDto> winLists1 = new ArrayList<>();
String acc="";
//批量插入成功量
int a=0;
//s数据返回变量
String s="";
//c异常标识
int c=0;
//b异常行数
int b=0;
if(listob.size()>1000){
return "批量导入条数不能超过1000，请重新导入!";
}else {
for (int i = 0; i < listob.size(); i++) {
List<Object> lo = listob.get(i);
String count = portalDao.queryCountByCallNo(String.valueOf(lo.get(1)));
acc+=String.valueOf(lo.get(1));
if(!"-1".equals(count)){
c=1;
s="请严格按照“群障号码告警池导入规范”填写！一个群障号码只能导入一行数据,请检查第"+(i+1)+"行数据";
b=i+1;
winLists1 = except(lo,batch,userId);
break;
}
if(isType(String.valueOf(lo.get(0)))==false){
c=2;
s="请严格按照“群障号码告警池导入规范”填写！群障场景取值范围为1~9,请检查第"+(i+1)+"行数据";
b=i+1;
winLists1 = except(lo,batch,userId);
break;
}
/*if(isCallNo(String.valueOf(lo.get(1)))==false){
c=3;
s="请严格按照“群障号码告警池导入规范”填写！群障号码填写不规范,请检查第"+(i+1)+"行数据";
b=i+1;
winLists1 = except(lo,batch,userId);
break;
}*/
if(isArea(String.valueOf(lo.get(2)))==false){
c=4;
s="请严格按照“群障号码告警池导入规范”填写！所属地市填写不规范,请检查第"+(i+1)+"行数据";
b=i+1;
winLists1 = except(lo,batch,userId);
break;
}
if(isDate(String.valueOf(lo.get(3)))==false){
c=5;
s="请严格按照“群障号码告警池导入规范”填写！故障开始时间填写不规范,请检查第"+(i+1)+"行数据";
b=i;
winLists1 = except(lo,batch,userId);
break;
}
if(isDate(String.valueOf(lo.get(4)))==false){
c=6;
s="请严格按照“群障号码告警池导入规范”填写！故障预计结束时间填写不规范,请检查第"+(i+1)+"行数据";
b=i+1;
winLists1 = except(lo,batch,userId);
break;
}
a=a+1;
CspGroupObstalePoolDto vo = new CspGroupObstalePoolDto();
vo.setObstacle_type(String.valueOf(lo.get(0)));// 表格的第一列 注意数据格式需要对应实体类属性
vo.setAccNo(String.valueOf(lo.get(1)));
vo.setArea_code(String.valueOf(lo.get(2)));
vo.setSTARTDATE(String.valueOf(lo.get(3)));
vo.setFREEDATE(String.valueOf(lo.get(4)));
vo.setBatch(batch+1);
vo.setAccUserId(userId);
winLists.add(vo);
//System.out.println(vo.toString());
}
try {
if(c==0){
//数据正常批量插入数据
portalDao.insertGroupObstacle(winLists);
CspGroupObstalePoolDto pool = new CspGroupObstalePoolDto();
pool.setAccNo(acc);
pool.setAccUserId(userId);
pool.setMake_flag(2);
pool.setBatch(batch+1);
pool.setFail_count(b);
pool.setSucc_count(a);
//批量插入日志保存
portalDao.insertGroupObstacleLog(pool);
s="文件导入成功，共"+a+"条数据！";
}else{
//数据异常
portalDao.insertGroupObstacleExcep(winLists1);
CspGroupObstalePoolDto pool = new CspGroupObstalePoolDto();
pool.setAccNo(acc);
pool.setAccUserId(userId);
pool.setMake_flag(2);
pool.setBatch(batch+1);
//出错行数
pool.setFail_count(b);
pool.setSucc_count(a);
//批量插入日志保存
portalDao.insertGroupObstacleLog(pool);
}
} catch (Exception e) {
e.printStackTrace();
s="数据插入异常!!!";
}
System.out.println(s);
return s;
}
}
批量导入工具类
package usi.sys.util;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtils {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
/**
* 描述：获取IO流中的数据，组装成List<List<Object>>对象
* @param in,fileName
* @return
* @throws IOException
*/
public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
List<List<Object>> list = null;
//创建Excel工作薄
Workbook work = this.getWorkbook(in,fileName);
if(null == work){
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null; //页数
Row row = null; //行数
Cell cell = null; //列数
list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
int a= work.getNumberOfSheets();
for (int i = 0; i < 1; i++) {
sheet = work.getSheetAt(i);
if(sheet==null){continue;}
//遍历当前sheet中的所有行
int b= sheet.getLastRowNum();
int c= sheet.getFirstRowNum();
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if(row==null||row.getFirstCellNum()==j){continue;}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(this.getValue(cell));
}
list.add(li);
}
}
return list;
}
/**
* 描述：根据文件后缀，自适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
*/
public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(excel2003L.equals(fileType)){
wb = new HSSFWorkbook(inStr); //2003-
}else if(excel2007U.equals(fileType)){
wb = new XSSFWorkbook(inStr); //2007+
}else{
throw new Exception("解析的文件格式有误！");
}
return wb;
}
/**
* 描述：对表格中数值进行格式化
* @param cell
* @return
*/
//解决excel类型问题，获得数值
public String getValue(Cell cell) {
String value = "";
if(null==cell){
return value;
}
switch (cell.getCellType()) {
//数值型
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
//如果是date类型则 ，获取该cell的date值
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
value = format.format(date);;
}else {// 纯数字
BigDecimal big=new BigDecimal(cell.getNumericCellValue());
value = big.toString();
//解决1234.0 去掉后面的.0
if(null!=value&&!"".equals(value.trim())){
String[] item = value.split("[.]");
if(1<item.length&&"0".equals(item[1])){
value=item[0];
}
}
}
break;
//字符串类型
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue().toString();
break;
// 公式类型
case Cell.CELL_TYPE_FORMULA:
//读公式计算值
value = String.valueOf(cell.getNumericCellValue());
if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
value = cell.getStringCellValue().toString();
}
break;
// 布尔类型
case Cell.CELL_TYPE_BOOLEAN:
value = " "+ cell.getBooleanCellValue();
break;
default:
value = cell.getStringCellValue().toString();
}
if("null".endsWith(value.trim())){
value="";
}
return value;
}
}
批量导入模板下载功能
<tr style="line-height:2;">
<td style="text-align: left;width: 25%">
<label>模板下载:</label>
</td>
<td colspan="3" style="text-align: left;width: 25%">
<a href="${ctx}/doc/groupObstalePool.xls" download="" id="pickUpOrder" style="text-decoration:underline;color: #0f83f8;"><span>群障告警池批量导入模板</span></a>
</td>
<td style="width: 25%"></td>
<td style="width: 25%"></td>
</tr>
文件目录结构
效果图：
本文地址：https://blog.csdn.net/qq_37641547/article/details/109259233
上一篇： 正则表达式（regular）知识(整理)
下一篇： 电商模式有哪些类型（电商平台的商业模式）
推荐阅读
-
通过Java实现批量导入数据到SQLServer数据库
-
java实现批量导入.csv文件到mysql数据库
-
Java实现批量导入Excel表格数据到数据库中
-
Java利用MYSQL LOAD DATA LOCAL INFILE实现大批量导入数据到MySQL
-
java实现Excel导入功能(前端+后台)
-
批量导入功能java实现(从前端到后端)
-
通过Java实现批量导入数据到SQLServer数据库
-
java实现批量导入.csv文件到mysql数据库
-
Java利用MYSQL LOAD DATA LOCAL INFILE实现大批量导入数据到MySQL
-
python实现JAVA源代码从ANSI到UTF-8的批量转换方法