上传文件获取后台返回数据功能分两种方法实现

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