Excel导入和导出

java Excel导入和导出数据是非常常用的功能，网上介绍得比较粗糙，所有决定写一个完整的案例，下面都是代码片段，仔细看你就懂了。工具类ExcelUpAndDownUtil网上有

1.html部分

<link rel="stylesheet" href="css/NAME/NAMEManage.css" />

<div id="NAMEManage" class="margin-top-10">
	<div class="row bgk">
		<div class="form-group  col-xs-4">
			<span class="col-xs-4 span "><span class="required">*</span>有效月份</span>
			<div class="col-xs-8">
				<input type="text" class="form-control cursorPointer" placeholder="必填项" id="NAMEManageDate" readonly/>
			</div>
		</div>
		<div class="form-group  col-xs-4">
			<span class="col-xs-4 span" >催员UM</span>
			<div class="col-xs-8 selectCss">
				<input type="text"  id="NAMEUM" style="text-transform:uppercase;">
			</div>
		</div>
		<div class="form-group  col-xs-4">
			<span class="col-xs-5 span" >是否配置业务线</span>
			<div class="col-xs-7 selectCss">
				<input type="text"   id="NAMEManageDrop"  />	
			</div>
		</div>
		<div class="form-group col-xs-12">
			<button type="button" class="btn submit-btn" id="NAMESlect">查询</button>
				<button type="button" class="btn submit-btn" id="NAMEReset" >重置</button>
			<button type="button" class="btn  default-btn" id="NAMEImport">导入</button>
			<button type="button" class="btn  default-btn" id="NAMEExport">导出</button>
			<button type="button" class="btn  default-btn defaltOver" id="NAMESchedule">查看导出进度</button>
		</div>
	</div>
	<div class="row">
		<div class="form-group col-xs-12">
			<button type="button" class="btn restore-btn" id="NAMEDelete"  ><span class="deletePng pngIcon"></span>删除</button>
			<button type="button" class="btn submit-btn" id="NAMESave" ><span class="savePng pngIcon"></span>保存</button>
			<!-- <button type="button" class="btn submit-btn" id="" >还原</button> -->
		</div>
	</div>	
	<div class="row backShadow">
		<table class="table table-striped table-hover" id="NAMEManageTable"></table>
	</div>
	
	<!--文件上传-->
	<div class="modal fade" id="NAMEImportModal" tabindex="-1" role="dialog" aria-hidden="true">
	    <div class="modal-dialog" style="width: 600px; position: absolute; left: 40%; top: 40%;">
	        <div class="modal-content modal-top">
	            <div class="modal-header">
	                <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
	                <header class="title">导入人力清单</header>
	            </div>
	            <div class="modal-body" >
	            	<div class="row">
	            		<span class="col-xs-2 span text-left">请选择文件</span>
	            		<div class="col-xs-10 form-group">
	            			<div class="input-group">
							    <input type="text" id="NAMEManageText" class="form-control">
							    <span class="input-group-btn">
							    	<button class="btn btn-default" type="button" style="overflow:hidden;">
							    	<input type="file" class="inputFile" name="" id="NAMEManageFile" onchange="document.getElementById('NAMEManageText').value = this.value"/>浏览</button>
							    </span>
						    </div>
	            		</div>
	            	</div>
	            	<div class="row paddTop-20">
	            		<div class="col-xs-6">
	            			<a href="#" id="NAMEManageUp" class="importA" style="display: inline;">导入模板下载</a>
	            		</div>
	            		<div class="col-xs-6 text-right form-group">
	            			<button type="button" class="btn submit-btn" id="NAMEManageImport" >导入</button>
	            			<button type="button" class="btn default-btn" data-dismiss="modal" >取消</button>
	            		</div>
	            	</div>
	            </div>
	        </div>
	    </div>
	</div>
	
	<!--文件导出-->
	<div class="modal fade" id="NAMEExportModal" tabindex="-1" role="dialog" aria-hidden="true">
	    <div class="modal-dialog" style="width:838px; position: absolute;margin-left:auto;margin-right:auto;">
	        <div class="modal-content modal-top">
	            <div class="modal-header">
	                <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
	                <header class="title">导出</header>
	            </div>
	            <div class="modal-body" >
	            	<div class="row text-center">
						<div class=" loader-17">
							<div class="css-square square1"></div>
							<div class="css-square square2"></div>
							<div class="css-square square3"></div>
							<div class="css-square square4"></div>
							<div class="css-square square5"></div>
							<div class="css-square square6"></div>
					    </div>
						<span class="required">*</span>
						<span>本页面  <span id="NAMEExportloading" style="font-size: 16px;color: #FF9C74;">
							</span>  秒自动刷新，也可点击 <a href="javascript:;" id="NAMEExportReflesh" style="font-size: 16px;">刷新</a> 进行页面刷新！
						</span>
					</div>
	            	<div class="row">
	            		<table class="table table-striped table-hover" id="NAMEExportTable"></table>
	            	</div>
	            </div>
	        </div>
	    </div>
	</div>
</div>

<script type="text/javascript">
 // 加载入口模块
 seajs.use("./js/NAME/NAMEManage.js",function(NAMEManage){
	 NAMEManage.init($('#NAMEManage'));
 });
</script>

2.js部分

define(function(require, exports, module) {
	// 引入公共模块的js
	var common = require("../common.js");
	var $rootDiv;
	var tiemer;
	// 初始倒计时
	var timeOver = 5;
	var umType;
	var queryParam;
	var datas = 0;
	//处理
	var dataSend=true;
	//校验是否要填um号
	var umRequired=true;
	var commissionMonth;
	exports.init = function(div) {
		$rootDiv = $(common.getContentId());
		datas = 0;
		//设置um是否
		setFlag();
		// 初始化按钮事件
		initEvent();
		// 初始化日期插件
		initDateControl();
		// 初始化下拉控件
		initTable();
		initSelected();
		clearOut();
		//校验UM
		checkData();
       //检测内勤和离职
		checkNL();
	}
	function initTable(url) {
		var validMonths = $rootDiv.find('#NAMEManageDate').val();
		$rootDiv.find("#NAMEManageTable").bootstrapTable('destroy');
		$rootDiv.find("#NAMEManageTable").bootstrapTable('removeAll');
		$rootDiv.find("#NAMEManageTable").bootstrapTable('refresh', {});
		// 加载表格
		$rootDiv.find('#NAMEManageTable').bootstrapTable(
				{
					url : url,
					method : 'post',
					editable :validMonths >= commissionMonth ? true : false,
					sidePagination : "server",
					pagination : true,
					showPaginationSwitch : false,
					pageList: [20,50],
					pageSize : 20,
					pageNumber : 1,
					queryParams : function params(param) {// 传入的参数
						// 有效月份
						var validMonth = $rootDiv.find('#NAMEManageDate')
								.val();
						// um号
						var userUM = $rootDiv.find('#NAMEUM')
								.bootstrapSelect('getValue');
						// 是否配置业务线
						var isConfigBusi = $rootDiv.find('#NAMEManageDrop')
								.bootstrapSelect('getValue');

					queryParam = {
						page : param.pageNumber,
						rows : param.limit,
						validMonth : validMonth,
						userUM : userUM,
						isConfigBusi : isConfigBusi
					};
					return queryParam;
				},
				columns : [
						{
							field : "ck",
							align : "center",
							checkbox : true,
							formatter : function(value, row, index) {
								return index + (1 - 0);
							}
						},
						{
							field : "area",
							title : "分中心",
							align : "center",
							edit : false
						},
						{
							field : "brManagerUM",
							title : "分中心经理UM",
							align : "center",
							edit :  {
								type : "text",
								required :umRequired,
							},
							formatter : function(value, row, index) {
								if(value){
									return value.replace(/&/g, "&")
									.replace(/</g, "&lt;").replace(
											/>/g, "&gt;").replace(/ /g,
											"&nbsp;").replace(/\'/g,
											"&#39;").replace(/\"/g,
											"&quot;");
								}
							}
						},
						{
							field : "areaUM",
							title : "贷后总监UM",
							align : "center",
							edit : {
								type : "text",
								required :umRequired,
							},
							formatter : function(value, row, index) {
								if(value){
									return value.replace(/&/g, "&amp;")
									.replace(/</g, "&lt;").replace(
											/>/g, "&gt;").replace(/ /g,
											"&nbsp;").replace(/\'/g,
											"&#39;").replace(/\"/g,
											"&quot;");
								}
							}
						},
						{
							field : "managerUM",
							title : "贷后部经理UM",
							align : "center",
							edit : {
								type : "text",
								required :umRequired,
							},
							formatter : function(value, row, index) {
								if(value){
									return value.replace(/&/g, "&amp;")
									.replace(/</g, "&lt;").replace(
											/>/g, "&gt;").replace(/ /g,
											"&nbsp;").replace(/\'/g,
											"&#39;").replace(/\"/g,
											"&quot;");
								}
							}
						},
						{
							field : "groupUM",
							title : "贷后经理UM",
							align : "center",
							edit : {
								type : "text",
								required :umRequired,
							},
							formatter : function(value, row, index) {
								if(value){
									return value.replace(/&/g, "&amp;")
									.replace(/</g, "&lt;").replace(
											/>/g, "&gt;").replace(/ /g,
											"&nbsp;").replace(/\'/g,
											"&#39;").replace(/\"/g,
											"&quot;");
								}
							}
						}, {
							field : "userUM",
							title : "催员UM",
							align : "center",
							edit : false
						}, {
							field : "userType",
							title : "催员类型",
							align : "center",
							edit : {
								type : "select",
								data : umType,
								required : true,
							}
						}, {
							field : "entryDate",
							title : "入职日期",
							align : "center",
							edit : false
						},{
							field : "chgInnerDate",
							title : "转内勤时间",
							align : "center",
							edit : {
								type : "date",
								format : 'yyyymmdd',
								maxView: 2,
								minView : 2,
								keyboardNavigation : true,
								show:function(val){
								 var	value = $(this).parent("td").find("input[type=text]").val();
								 $(this).datetimepicker('update',common.toChangDate(value));
								}
						
							}
						}, {
							field : "onJobDays",
							title : "当月出勤天数",
							align : "center",
							edit : false
						},{
							field : "dimissionDate",
							title : "离职日期",
							align : "center",
							edit : {
								type : "date",
								format : 'yyyymmdd',
								maxView: 2,
								minView : 2,
								keyboardNavigation : true,
								show:function(){
									 var	value = $(this).parent("td").find("input[type=text]").val();
									 $(this).datetimepicker('update',common.toChangDate(value));	
									
								}
							}
						}, {
							field : "businessType",
							title : "主业务线代码",
							align : "center",
							edit : false
						}, {
							field : "businessName",
							title : "主业务线名称",
							align : "center",
							edit : false

						}, {
							field : "businessClassify",
							title : "业务分类",
							align : "center",
							edit : false
						}, {
							field : "validMonth",
							title : "有效月份",
							align : "center",
							edit : false
						}, {
							field : "idNAME",
							title : "主键",
							align : "center",
							edit : false,
							visible : false
						}  ],
				responseHandler : function(res) {
					datas = res.total;
					return {
						"total" : res.total == undefined ? 0 : res.total,// 总条数
						"rows" : res.rows == undefined ? [] : res.rows
					// 数据
					};
				}
			});

}

// --------------------------初始化事件
function initEvent() {
	//获取月份
	$.ajax({
		url : '/comprehensiveDeductionListManagement/getValidMonthInDB.do',
		type : "get",
		dataType : 'json',
		async : false,
		success : function(data) {
			if (data.code=="200") {
				commissionMonth = data.body;
			}
		}
	})
	
	common.removeWarn($rootDiv);
	// 绑定查询按钮事件
	$rootDiv.find('#NAMESlect').click(function() {
		NAMESlectEvent();
	});
	//重置按钮
	$rootDiv.find('#NAMEReset').click(function() {
		 $rootDiv.find('#NAMEManageDate').val("");
		 $rootDiv.find('#NAMEUM').bootstrapSelect(
				'setValue', "");
		 $rootDiv.find('#NAMEManageDrop').bootstrapSelect(
					'setValue', "");
	});
	
	// 绑定导入按钮事件
	$rootDiv.find('#NAMEImport').click(function() {
		$rootDiv.find("#NAMEManageText").val("");
		$rootDiv.find("#NAMEManageFile").val("");
		$rootDiv.find("#NAMEImportModal").modal({
			backdrop : false,
			keyboard : false,
			show : true
		});

	});
	// 绑定导出按钮事件
	$rootDiv.find('#NAMEExport').click(function() {
		if (datas == 0) {
			$.messager.alert('提示', "无数据需导出", "info");
			return false;
		}
		$rootDiv.find("#NAMEExportTable").bootstrapTable('destroy');
		$rootDiv.find("#NAMEExportTable").bootstrapTable('removeAll');
		$rootDiv.find("#NAMEExportModal").modal({
			backdrop : false,
			keyboard : false,
			show : true
		});
		// 加载导出表格
		startExportData();
		startRefesh();
	});
	// 绑定查看进度事件
	$rootDiv.find('#NAMESchedule').click(function() {
		$rootDiv.find("#NAMEExportTable").bootstrapTable('destroy');
		$rootDiv.find("#NAMEExportTable").bootstrapTable('removeAll');
		$rootDiv.find("#NAMEExportModal").modal({
			backdrop : false,
			keyboard : false,
			show : true
		});
		$rootDiv.find('#NAMEExportModal')
				.find("header.title").html("导出进度查看");
		// 加载导出表格
		showExportRecord();
		startRefesh();
	});
	// 绑定刷新事件
	$rootDiv.find('#NAMEExportReflesh').click(function() {
		clearInterval(tiemer);
		timeOver = 5;
		showExportRecord();
		startRefesh();
	});
	// 文件上传按钮
	$rootDiv.find('#NAMEManageImport').click(function() {

		var file = $rootDiv.find('#NAMEManageFile');

		if (checkUpFile(file)) {
			uploadDoc();
		} else {
			return;
		}

	});
	// 删除操作
	$rootDiv.find('#NAMEDelete').click(function() {
		deleTableData();
	});
	// 文件下载
	$rootDiv.find('#NAMEManageUp').click(function() {
		var openURL = "NAME/downloadNAMEtemplate.do";
		window.open(openURL, '_self');
	});

	// 文件修改操作保存操作
	$rootDiv.find('#NAMESave').click(function() {
		setEditData();
	});

}

//设置um是否是必填/非必填
function setFlag(){
	$.ajax({
		url : '/NAME/isCheckUMS.do',
		type : "post",
		dataType : "json",
		contentType : "application/json",
		success : function(data) {
			if (data[200] && data[200] =="false") {
				umRequired = false;
			}
		},
		error : function(e) {
		 //$.messager.alert('提示', "网络错误，请重试！！", "info");
		}
	})	
	
	
	
}

function NAMESlectEvent() {
	var NAMEManageDate = $.trim($rootDiv.find('#NAMEManageDate')
			.val());
	if (NAMEManageDate.length == 0) {
		common.addWarn($rootDiv,"#NAMEManageDate");
		//$("#NAMEManageDate").prop("placeholder", "有效月份必填");
		//$.messager.alert('提示', "请选择有效月份");
		return;
	}
	initTable('/listManConfigInfo.do');
}

// 开始导出
function startExportData() {
	// 保存导出记录
	var exportParam = {};
	exportParam.fileType = '3';// 3为人力清单
	$.ajax({
		url : '/export/saveExportRecord.do',
		type : "post",
		dataType : "json",
		contentType : "application/json",
		data : JSON.stringify(exportParam),
		success : function(data) {
			if (data.code == 200) {
				// 导出数据
				exportParam.idKey = data.body;
				//这里的map 可以换成类型。也可以是一个对象。只要在接收的java对象里面有这个属性或者对象就行
				exportParam.map = queryParam; 
				$.ajax({
					url : '/export/exportDataToFile.do',
					type : "post",
					dataType : "json",
					contentType : "application/json",
					data : JSON.stringify(exportParam),
					success : function(data) {
						if (data.code == 200) {
							//展示导出记录
							showExportRecord();
						}
					}
				})
			}
		}
	})

}
// 导出框展示列表
function showExportRecord() {
	var downZipFileEvent = {
		"click .downZipFile" : function(e, value, row, index) {
			common.downloadFileTozip(row.idKey);
		}
	};
	
	$rootDiv.find("#NAMEExportTable").bootstrapTable('refresh', {});
	$rootDiv.find('#NAMEExportTable').bootstrapTable(
		{
			url : "/export/queryExportRecord.do",
			method : 'post',
			sidePagination : "server",
			pagination : true,
			showPaginationSwitch : false,
			pageList : [ 10, 20, 30, 40 ],
			pageSize : 10,
			pageNumber : 1,
			queryParams : function params(param) {
				return {
					page : param.pageNumber+1,
					rows : param.limit,
					fileType : '3'
				};
			},
			columns : [
					{
						field : "index",
						title : "序号",
						align : "center",
						formatter : function(value, row, index) {
							return row.index = index + 1; // 返回行号
						}
					},
					{
						field : "fileName",
						title : "文件名",
						align : "center"
					},
					{
						field : "status",
						title : "状态",
						align : "center",
						formatter : function(value, row, index) {
							switch (value) {
							case '0':
								return '生成中';
								break;
							case '1':
								return '完成';
								break;
							case '2':
								return '失败';
								break;
							case '3':
								return '已失效';
								break;
							default:
								return '---';
							}
						}
					},
					{
						field : "fileUrl",
						title : "操作",
						align : "center",
						formatter : function(value, row, index) {
							if (row.status == '0'
									|| row.status == '2'
									|| row.status == '3') {
								return '---';
							} else {
								return '<a href="javascript:;" class="downZipFile">下载文件</a>'; // 返回行号
							}
						},
						events : downZipFileEvent
					}, {
						field : "idKey",
						title : "主键",
						align : "center",
						visible : false
					} ]
		});
}

// 关闭下载模态框
function hideImportData() {
	$rootDiv.find("#NAMEImportModal").modal('hide');
}

// 初始化日期插件
function initDateControl() {
	// 初始化日期插件
	$rootDiv.find('#NAMEManageDate').fdatepicker({
		format : 'yyyymm',
		startView :3,
		minView :3
	})
}

// 初始化下拉
function initSelected() {
	$rootDiv.find('#NAMEManageDrop').bootstrapSelect({
		editable : false,
		type : "select",
		textField : 'text',// 显示文本字段
		valueField : 'id',// 隐藏文本字段
		emptyText : null,
		data : [  {
			id : 'Y',
			text : '是'
		}, {
			id : 'N',
			text : '否'
		}, ]

	});
	// 催员UM号
	$rootDiv.find('#NAMEUM').bootstrapSelect({
		url : '/getUserUMs.do',
		editable : true,
		textField : 'userums',
		valueField : 'userums',
		onLoadSuccess : function(data) {
			if (data.code = '200') {
				return data.body;
			}
		}
	});
	
	common.selectBlur($rootDiv,"#NAMEUM");
	
	umType = [ {
		'id' : 'ZC',
		'text' : '在催'
	}, {
		'id' : 'LZ',
		'text' : '离职'
	}, {
		'id' : 'ZNQ',
		'text' : '转内勤'
	}, {
		'id' : 'XC',
		'text' : '协催'
	} ];

}
// 清空元素的值
function clearOut() {
	$rootDiv.find('#NAMEExportModal').on('hidden.bs.modal', function() {
		$(this).removeData("bs.modal");
		clearInterval(tiemer);
		timeOver = 5;
		// 清空导入框
	})

}
function startRefesh() {
	$rootDiv.find("#NAMEExportloading").html(timeOver);
	tiemer = setInterval(function() {
		timeOver--;
		if (timeOver == 0) {
			// 刷新列表
			showExportRecord();
			timeOver = 5;
		}
		$rootDiv.find("#NAMEExportloading").html(timeOver);
	}, 1000);

}
// 文件上传
function uploadDoc() {
	var form = new FormData();
	form.append("file",
			document.getElementById("NAMEManageFile").files[0]);
	$.ajax({
		url : 'NAME/uploadNAMEList.do',
		type : "post",
		data : form,
		cache : false,
		processData : false,
		contentType : false,
		success : function(data) {
			if (data.code == "200") {
				hideImportData();
				$.messager.alert("提示", data.message);
				initTable('/listManConfigInfo.do');
			} else if (data.code == "210") {
				$.messager.progress('close');
				$.messager.alert('提示', data.message, "", function() {
					// 关闭弹出框
				});
				/*common.downloadFile('NAME/downloadNAMEError.do?'
						+ new Date().getTime(), "post");*/// get有问题
				var openURL = "NAME/downloadNAMEError.do";
             	 window.open(openURL,'_self');
			} else {
				$.messager.alert("提示", data.message);

			}
		},
		beforeSend : function() {
			$.messager.progress({
				title : '提示',
				msg : '数据保存中，请稍候……',
				text : ''
			});
		},
		complete : function() {
			$.messager.progress('close');
		},
		error : function(e) {
			$.messager.alert('提示', "网络错误，请重试！！", "info");
		}
	});

}

// 导入文件袋 校验备用
function checkUpFile(file) {
	var fileValue = file.val();
	if (file == null || file == undefined || fileValue.length <= 0) {
		$.messager.alert('提示', "请选择要导入的文件", "info");
		return false;
	}
	// 对上传文件的扩展名进行检测
	if (getExpandName(file) != "xls") {
		$.messager.alert('提示', "导入的文件必须是xls文件格式", "info");
		return false;
	}
	var size =file[0].files[0].size;
	if (Number(size) >Number(1024*1024*10)) {
		$.messager.alert('提示', "上传文件不能超过10M");
		return false;
	}
	return true;
}

	// 获取文件前缀名
	function getExpandName(file) {
		var fileName = getFileName(file);
		var expandIndex = fileName.lastIndexOf(".");
		return fileName.substring(expandIndex + 1, fileName.length);
	}
	// 取上传文件名
	function getFileName(file) {
		var fileStr = file.val();
		fileStr = fileStr.toString();
		var nameIndex = fileStr.lastIndexOf("\\");
		return fileStr.substring(nameIndex + 1, fileStr.length);
	}
	function deleTableData() {
		// 获取选中的项的数据
		var rows = $rootDiv.find("#NAMEManageTable").bootstrapTable(
				'getSelections');
		if (rows.length == 0) {
			$.messager.alert('提示', '没有选择删除的数据', 'info');
			return;
		}
//		var validMonths = $rootDiv.find('#NAMEManageDate').val();
//		var flags = validMonths >= commissionMonth ? true : false;
//		if(!flags){
//		  $.messager.alert('提示', '已过当前时间，不能删除数据!');
//		  return;
//		}
		$.messager.confirm("提示", '请确认是否删除选中的数据？', function(r) {
			if (r) {
				// 提交需要删除的数据
				submitData(rows);
			} else {
				return false;
			}
		});

}
// 删除数据
function submitData(parameter) {
	var param = JSON.stringify(parameter);
	$.ajax({
		url : '/delNAMEBatch.do',
		type : 'POST',
		dataType : "json",
		contentType : "application/json",
		data : param,
		success : function(data) {
			if (data.code == 200) {
				initTable('/listManConfigInfo.do');
			} else {
				$.messager.alert('提示', data.message, 'info');
			}
		}
	});
}

// 保存更改的数据
function setEditData() {
	var updated = $rootDiv.find('#NAMEManageTable').bootstrapTable(
			'getModiDatas');
	if (updated.length == 0) {
		$.messager.alert("提示", "没有数据发生改变！");
		return;
	} else {
		submitEditData(updated);
	}

}
function submitEditData(value) {
	var param = JSON.stringify(value);
	$.ajax({
		url : '/updateNAMEAndBusinessInfo.do',
		type : 'POST',
		dataType : "json",
		contentType : "application/json",
		data : param,
		success : function(data) {
			if (data.code == 200) {
				$.messager.alert('提示', data.message, 'info');
				initTable('/listManConfigInfo.do');
			} else {
				$.messager.alert('提示', data.message, 'info');
				NAMESlectEvent();
			}
		}
	});

}

//校验事件
function checkData(){
	$rootDiv.find("#NAMEManageTable").on("change","input[type=text]",function(){
		 var td = $(this).parents("td").eq(0);
		 var tdIndex=  $(this).parents("tr").find("td").index(td);
		 var umType;
		
		 if(tdIndex==3){
			 umType="QUM";
		 }else if(tdIndex==4){
			 umType="BUM"; 
		 }else if(tdIndex==5){
			 umType="ZUM";
		 }else if(tdIndex==2){
			 umType="AUM";
		 }
	     var param={};	
	     param.userUM=$(this).val();
	     param.umType=umType;
	    $.ajax({
				url : 'NAME/isContainUMS.do',
				type : "post",
				dataType : "json",
				contentType : "application/json",
				data : JSON.stringify(param),
				success : function(data) {
					if (data.code && data.code == 201) {
					 $.messager.alert('提示', data.message, 'info');	
					} 
				}
			}) 
	})
}

function  checkNL(){
	$rootDiv.find("#NAMEManageTable").on("change","input[type=text]",function(){
		 var td = $(this).parents("td").eq(0);
		 var entryDate  =td.prev("td").text();
		 var tdIndex=  $(this).parents("tr").find("td").index(td);
		 var  selected = $(this).parents("tr").find("td").eq(7).find("input[type=text]").val();
		 var  selectedVal;
		
		 if(!selected){
	    	 return;
	      }else{
	      for(var i=0;i<umType.length;i++){
	    	  if(umType[i].text==$.trim(selected)){
	    		  selectedVal = umType[i].id;
	    	  }
	      }	  
	      }
		var  dateType=$(this).val();
		 //转内勤
		var dateSign;
		 if(tdIndex==9){
			 dateSign="NQ";
		 }else if(tdIndex==10){//离职日期
			 dateSign="LV"
		 }
		 if(!dateSign){
			return; 
		 }
	     var param={};	
	     param.dateType=dateType;
	     param.dateSign=dateSign;
	     param.userType=selectedVal;
	     param.entryDate=entryDate;
	    $.ajax({
				url :'NAME/checkChgInnerOrDimissionDate.do',
				type : "post",
				dataType : "json",
				contentType : "application/json",
				data : JSON.stringify(param),
				success : function(data) {
					if (data.code && data.code == 201) {
					 $.messager.alert('提示', data.message, 'info');	
					} 
				}
			}) 
	})
}	

function fomatterTime(date) {
    var date = new Date(date);
    Y = date.getFullYear();
    M = (date.getMonth()+1 < 10 ? '0'+(date.getMonth()+1) : date.getMonth()+1);
    D = date.getDate() <10?'0'+date.getDate():date.getDate();
    return Y+M+D;
}	
});

3.java部分
1)导入

@SuppressWarnings("unchecked")
     	@Override
    public ResultInfo xxx(HttpServletResponse httpResponse, HttpServletRequest httpRequest,String loginUM) {
   
    // 响应返回值
    ResultInfo resultInfo = new ResultInfo();
    // 错误信息集合
    List<String> errlist = new ArrayList<>();
    // 默认上传成功
    resultInfo.setCode(ResponseUtil.success_code);
    resultInfo.setMessage("xxx");
    Workbook workbook = null;
    // excel获取的数据
    List<String[]> excelList = null;
    // 从Excel文件读取到的交验成功的xxx
    List<NAMEConfigEntity> NAMEDataList = new ArrayList<>();

    try {
        MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) httpRequest;
        CommonsMultipartFile file = (CommonsMultipartFile) multiRequest.getFile("file");
        // 获取文件名
        String checkFileIsTrue ;
        String filename = file.getOriginalFilename();
  //校验文件格式是否正确 ，需要大写或小写的xls、xlsx
        checkFileIsTrue = ExcelUpAndDownUtil.checkFile(file);

        if (!"true".equals(checkFileIsTrue)) {
            resultInfo.setCode(ResponseUtil.faile_code);
            resultInfo.setMessage(checkFileIsTrue);
            return resultInfo;
        }
        // xxx
        try {
            workbook = ExcelUpAndDownUtil.getWorkBook(file);
        } catch (Exception e1) {
            logger.error("xxx{}",e1.getMessage(),e1);
            resultInfo.setCode(ResponseUtil.faile_code);
            resultInfo.setMessage("xxx！");
            return resultInfo;
        }
//校验目录是否正确，NAMERow是数组类型的目录
        if (workbook != null) {
            if (!ExcelUpAndDownUtil.isDIR(workbook, NAMERow, 0)) {
                resultInfo.setCode(ResponseUtil.faile_code);
                resultInfo.setMessage(filename + "xxx");
                return resultInfo;
            }
        }
        else {
            resultInfo.setMessage("xxx！");
            return resultInfo;
        }

        // xxx

        try {
        	excelList = ExcelUpAndDownUtil.readExcel(workbook, 1, 0);
		} catch (Exception e) {
			logger.error("xxx，exception={}", e.getMessage(), e);
			return new ResultInfo(ResponseUtil.faile_code, e.getMessage(), null);
		}

        if (null == excelList || excelList.isEmpty()) {
            resultInfo.setCode(ResponseUtil.faile_code);
            resultInfo.setMessage(filename + "xxx");
            return resultInfo;
        }
		//上传文件大小限制
        if (excelList.size() > AppConstant.EXCEL_UPLOAD_LIMIT_5W) {
            return new ResultInfo(ResponseUtil.faile_code, "xxxx", null);
        }
        
        // 对一个数据做必要的校验，根据自己情况而定
        String firstValidMonth = excelList.get(0)[12];
        String checkValidMonth = CommonValidateUtils.checkValidMonth(firstValidMonth, 2);
        if (null != checkValidMonth) {
            return new ResultInfo(ResponseUtil.faile_code, checkValidMonth.replaceAll("操作", "导入"), null);
        }
        
        // 重复值校验

        ArrayList<String> umErrlist = new ArrayList<>();
        for (int i = 0; i < excelList.size(); i++) {
            if (!firstValidMonth.equals(excelList.get(i)[12])) {
                resultInfo.setCode(ResponseUtil.faile_code);
                resultInfo.setMessage("xxx");
                return resultInfo;
            }
            for (int j = i + 1; j < excelList.size(); j++) {
                if (excelList.get(i)[5].equalsIgnoreCase(excelList.get(j)[5])) {
                    umErrlist.add("xxx");
                    break;
                }
            }

        }

        Map<String, Object> excelListMap = new HashMap<>() ;
        if (excelList != null && !excelList.isEmpty()) {  
			//这里很重要，相当于对excel每一个单元格的校验，根据自己逻辑编写，这里省略	
            excelListMap = getCheckedNAMEData(excelList);
        }
        errlist = (List<String>) excelListMap.get("errorList");
        
        if (umErrlist.isEmpty() && errlist.isEmpty()) {
            
            NAMEDataList = (List<NAMEConfigEntity>) excelListMap.get("NAMEDateList");
            //进一步校验，比如行与行直接的一些关系
            if (!NAMEDataList.isEmpty()) {
                errlist = checkAreaAndManagerUM(NAMEDataList, errlist);
            }
            // xx
            if (!errlist.isEmpty()) {
                NAMEDataList.clear();
                resultInfo.setCode(ResponseUtil.success_back);
                resultInfo.setMessage("xxx！");
                httpRequest.getSession().setAttribute("uploadNAMEList", errlist);
                return resultInfo;
            }
            if (NAMEDataList.size() == excelList.size()) {
                long start = System.currentTimeMillis();
				//这是最后一步，根据要求先删除数据库的数据。然后再插入数据库，具体操作这里省略
                delOldAndInsertNAMEBatch(NAMEDataList);
                logger.info("总耗时:" + (System.currentTimeMillis() - start) + "毫秒");


                ThreadPoolManager.executorService.execute(() -> {
                    manPositionService.resetManPositionsByValidMonth(firstValidMonth, loginUM);
                });
            }
        }else {
            umErrlist.addAll(errlist);
            resultInfo.setCode(ResponseUtil.success_back);
            resultInfo.setMessage("xxx");
            httpRequest.getSession().setAttribute("uploadNAMEList", umErrlist);
            return resultInfo;
        }
    }catch (Exception e) {
        resultInfo.setCode(ResponseUtil.faile_code);
        resultInfo.setMessage("xxx");
        logger.error("xxxx， exception={}", e.getMessage(), e);
        return resultInfo;
    }
    return resultInfo;

}

2）导入
导入的部分可以做成通用模板，这里分三步，a)由于是通用模板，js需要传一个类型过来，产生一个id，然后根据类型在数据库存入一条数据；
b) 产生一个zip包并下载到本地。c)从本地下载文件到浏览器
a)保存导出记录

@PostMapping("/export/saveExportRecord.do")
	@ResponseBody
	@ApiOperation(value = "保存导出记录接口", notes = "保存导出记录接口")
	public ResultInfo saveExportRecord(HttpServletRequest request, @RequestBody ExportParam exportParam) {
		ResultInfo validateExportParam = validateExportParam(exportParam);
		if (null != validateExportParam) {
			return validateExportParam;
		}
		exportParam.setUserId(getLoginUM());
		return exportDocumenttaskService.insertExportDocuTask(exportParam);
	}
	@Override
	public ResultInfo insertExportDocuTask(ExportParam exportParam) {
		ExportDocumenttask exportDocTask = new ExportDocumenttask();
		String idKey = UUID.getID().replaceAll("-", "");
		exportDocTask.setIdKey(idKey);
		exportDocTask.setFileTotal(0);
		exportDocTask.setUserId(exportParam.getUserId());
		exportDocTask.setFileType(exportParam.getFileType());
		exportDocTask.setCreatedBy(exportParam.getUserId());
		exportDocTask.setUpdatedBy(exportParam.getUserId());
		exportDocTask.setFileName("文件生成中……");
		try {
			exportDocumenttaskMapper.insertExportDocuTask(exportDocTask);
		} catch (Exception e) {
			logger.error("保存导出记录异常，{}", e.getMessage(), e);
			return new ResultInfo(ResponseUtil.faile_code, "保存导出记录异常", null);
		}
		// 用于生成导出文件后更新任务状态
		exportParam.setIdKey(idKey);
		return new ResultInfo(ResponseUtil.success_code, "保存导出记录成功！", idKey);

	}

b)生成导出文件

@PostMapping("/export/exportDataToFile.do") // external/ExportDataToExcel.do
	@ResponseBody
	@ApiOperation(value = "生成导出文件接口", notes = "生成导出文件接口")
	public ResultInfo exportDataToFile(HttpServletRequest request, @RequestBody ExportParam exportParam) {
		ResultInfo validateExportParam = validateExportParam(exportParam);
		if (null != validateExportParam) {
			return validateExportParam;
		}
		if (StringUtils.isBlank(exportParam.getIdKey())) {
			return new ResultInfo(ResponseUtil.param_error_code, "生成文件失败,IDKey不能为空");
		}
		exportParam.setUserId(getLoginUM());
		exportParam.setUserInfo(getLoginInfo());
		return exportDocumenttaskService.handleExportDataToFile(exportParam);
	}
	public static enum EXPORT_TYPE {
		NOFILE("0", "无文件")；
		private String value;
		private String desc;

	private EXPORT_TYPE(String value, String desc) {
		this.value = value;
		this.desc = desc;
	}
	public static EXPORT_TYPE getEnumByValue(String value) {
		if (value == null) {
			return null;
		}
		for (EXPORT_TYPE e : values()) {
			if (e.value.equals(value)) {
				return e;
			}
		}
		return null;
	}
	public static EXPORT_TYPE getEnumByDesc(String desc) {
		if ((desc == null) || "".equals(desc)) {
			return null;
		}
		for (EXPORT_TYPE e : values()) {
			if (e.desc.equals(desc)) {
				return e;
			}
		}
		return null;
	}
	
	
	
String[] bankRankHeaders = {};
String[] bankRankKeys = {};
private Map<String, String> bankRankMap = Maps.newHashMap();
{
    bankRankMap.put("party_a_sort", "number") ;
    bankRankMap.put("sort_total", "number") ;
}

public ResultInfo handleExportDataToFile(ExportParam exportParam) {
	logger.info("-------------------------文件导出-开始--------------------------------");
	String fileName = getExceptionFileName(exportParam);
	Date nowTime = new Date();
	String nowDate = DateUtils.getDate2String(nowTime, DateUtils.sf);
	String zipName = fileName + "_" + DateUtils.getDate2String(nowTime, DateUtils.time_sf);
	String sourcePath = retPath + File.separator + "commZip" + File.separator + nowDate;
	String fileUrl = sourcePath + File.separator + zipName;
	Integer pages = 0;
	boolean success = false;
	try {
		InExportFileDTO inFileDTO = new InExportFileDTO();
		inFileDTO.setFileName(fileName);
		inFileDTO.setZipName(zipName);
		inFileDTO.setSourcePath(sourcePath);
		inFileDTO.setExportParam(exportParam);

		// 匹配导出数据类型
		ResultInfo matchResult = matchExportDataType(exportParam, inFileDTO);
		if (matchResult != null) {
			return matchResult;
		}

		// 封装导出数据并生产导出文件
		pages = fitExportData(inFileDTO);

		// 上传文件
		ExcelUpAndDownUtil.dataUploadToFile(zipName, sourcePath);
		success = true;
	} catch (Exception e) {
		logger.error("文件导出异常，{}", e.getMessage(), e);
		return new ResultInfo(ResponseUtil.faile_code, "文件导出异常", null);
	} finally {

		// 更新导出记录
		try {
			ExportDocumenttask dto = new ExportDocumenttask();
			dto.setIdKey(exportParam.getIdKey());
			if (success) {
				String fileNameDown = zipName + "cm" + exportParam.getIdKey();
				dto.setFileName(zipName);
				dto.setFileUrl(fileUrl + ".zip");
				dto.setFileNameDown(fileNameDown);
				dto.setFileType(exportParam.getFileType());
				dto.setFileTotal(pages - 1);
				dto.setFileFinish(pages - 1);
				dto.setEndDate(new Date());
				dto.setStatus(AppConstant.EXPORT_STATUC.SUCCESS.getValue());
			} else {
				dto.setFileName("文件生成失败");
				dto.setFileTotal(pages > Integer.valueOf(1) ? pages - Integer.valueOf(1) : pages);
				dto.setStatus(AppConstant.EXPORT_STATUC.FAIL.getValue());
			}
			exportDocumenttaskMapper.updateDocumenttask(dto);
		} catch (Exception e) {
			logger.error("更新导出【{}】记录异常", fileName, e);
		}
	}
	logger.info("-------------------------文件导出-完成--------------------------------");
	return new ResultInfo(ResponseUtil.success_code);
}
//匹配类型。这里就写了一个类型
public ResultInfo matchExportDataType(ExportParam exportParam, InExportFileDTO inFileDTO) {
	// 休息休息
	if (AppConstant.EXPORT_TYPE.EXCITATIONRATE.getValue().equals(exportParam.getFileType())) {
		CommRateRuleDto commRateRuleDto = exportParam.getCommRateRuleDto();
		commRateRuleDto.setCommissionType(AppConstant.RATE_RULE_COMMTYPE_ENC);
		int count = commRateRuleMapper.getCommRateRulesCount(commRateRuleDto);
		if (count <= 0) {
			return new ResultInfo(ResponseUtil.success_code, "没有要导出的数据", null);
		}
		inFileDTO.setDataCount(count);
		inFileDTO.setHeaders(encRateHeaders);
		inFileDTO.setHeaderKeys(encRateHeaderKeys);
		inFileDTO.setDataTypeMap(encRateDataTypeMap);
	}
//导出结果集，就是把数据查出来，10000一条一个excel，5万一个
private Integer fitExportData(InExportFileDTO inFileDTO) {
	List<List<String>> dataList = new ArrayList<List<String>>(
			inFileDTO.getDataCount() > 50000 ? 50000 : inFileDTO.getDataCount());
	ExportParam exportParam = inFileDTO.getExportParam();
	List<HashMap<String, Object>> results = new ArrayList<HashMap<String, Object>>(
			inFileDTO.getDataCount() > 50000 ? 50000 : inFileDTO.getDataCount());
	List<HashMap<String, Object>> tempList;
	int rowsNum = inFileDTO.rowsNum();
	Integer pages = 1;
	for (int i = 1; i <= rowsNum; i++) {
		// 查询结果集
		tempList = getEachExportResult(exportParam, i);
		if (tempList == null) {
			break;
		}
		results.addAll(tempList);
		String[] headers = inFileDTO.getHeaders();
		String[] headerKeys = inFileDTO.getHeaderKeys();
		String zipName = inFileDTO.getZipName();
		String sourcePath = inFileDTO.getSourcePath();
		Map<String, String> dataTypeMap = inFileDTO.getDataTypeMap();
		int filenum = inFileDTO.filenum();
		if (pages == filenum) {// 最后一个excel
			if (results.size() == inFileDTO.getDataCount() % 50000 || results.size() == 50000) {
				// 如果集合的数据大小刚好等于（总数量%50000）的值或者数据的大小刚好等于50000说明最后的一批数据已经查询完毕，可以生产最后的excel了
				resultHandle(results, dataList, headerKeys);
				if () {
					logger.info("-------------------------开始生成Excel文件,行数:------------------------------"
							+ results.size());
					ExcelUpAndDownUtil.createExcelBySXFF(zipName, sourcePath, headers, dataList, pages,
							dataTypeMap);
					logger.info("-------------------------生成Excel文件全部完成------------------------------" + i * 10000);
				} else {
					ExcelUpAndDownUtil.createExcel(zipName, sourcePath, headers, dataList, pages, dataTypeMap);
				}
				// pages++;
				results.clear();
				dataList.clear();
			}
		} 
	}
	return pages;
}
public List<HashMap<String, Object>> getEachExportResult(ExportParam exportParam, int each) {
	// XXX
	if (AppConstant.EXPORT_TYPE.EXCITATIONRATE.getValue().equals(exportParam.getFileType())) {
		exportParam.getCommRateRuleDto().setPage(each);
		exportParam.getCommRateRuleDto().setRows(10000);
		return exportDocumenttaskMapper.listRateRules4Export(exportParam.getCommRateRuleDto());
	}
}

C)下载文件到本地

@RequestMapping("/export/checkDownloadFile.do")
	@ResponseBody
	@ApiOperation(value = "检查文件是否存在接口", notes = "检查文件是否存在接口")
	public ResultInfo checkDownloadFile(@RequestParam String idKey, HttpServletRequest request) {
		return exportDocumenttaskService.queryExportDoctaskByIdKey(idKey);
	}
	@Override
	public void downloadFile(String idKey, HttpServletResponse response) {
		if (StringUtils.isBlank(idKey)) {
			logger.warn("主键为空！");
			return;
		}
		ExportDocumenttask dto = exportDocumenttaskMapper.queryExportDoctaskByIdKey(idKey);
		if (dto == null) {
			logger.warn("下载文件失败，数据不存在");
			return;
		}
		String fileUrl = dto.getFileUrl();
		String fileName = dto.getFileName() + ".zip";
		try {
			fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
		} catch (UnsupportedEncodingException e1) {
			logger.error("文件名编码异常", e1);
		}
		response.setContentType("application/download;charset=UTF-8");
		response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
		response.setHeader("Pragma", "public");
		response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");
		File file = new File(fileUrl);
		FileInputStream fileInputStream = null;
		OutputStream os = null;
		try {
			fileInputStream = new FileInputStream(file);
			os = response.getOutputStream();
			byte[] b = new byte[1024];
			int length;
			while ((length = fileInputStream.read(b)) > 0) {
				os.write(b, 0, length);
			}
		} catch (FileNotFoundException e) {
			logger.error("下载文件【{}】 不存在！", fileUrl, e);
		} catch (IOException e) {
			logger.error("io流异常，{}", e.getMessage(), e);
		} finally {
			try {
				if (fileInputStream != null) {
					fileInputStream.close();
				}
				if (os != null) {
					os.close();
				}
			} catch (IOException e) {
				logger.error("io流关闭异常，{}", e.getMessage(), e);
			}
		}
	}