20171020java学习总结——execl 批量导入
excel批量导入功能的实现主要是三部分的功能：
1 下载模版
2 数据预览
3 数据导入
工作栏：
</@ifAuthority> <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-excel" plain="true" onclick="penaltyinfodown();">批量导入</a> <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-excel" plain="true" onclick="DownLoad()">批量导入模板下载</a> </div>
模版下载：
<!--模版下载-->
<form id="dwForm" action="${request.contextPath}/TdLawTask/download" method="post">
<input id="fp" name="filepath" value="WEB-INF/upload/files/import/应急专家批量导入.xls" type="hidden" />
</form>
选择文件：
<!--导入 表单-->
<div id="penaltyinfod" class="easyui-dialog" style="padding:10px 20px;" closed="true" buttons="#dlg-buttons-up5">
<form id="penaltyinfo" novalidate method="post" enctype="multipart/form-data" style="float:left">
<div class="fitem">
<label style="width:90px">文件路径：</label>
<input name="file2" id="file2" class="easyui-filebox" style="width:240px" data-options="buttonText:'选择文件',buttonIcon:'icon-search',prompt:'文件路径...'" value="选择文件">
</div>
</form>
</div>
<div id="dlg-buttons-up5">
<a href="javascript:void(0)" onclick="viewdata();" class="easyui-linkbutton" iconCls="icon-search">预览导入数据</a>
</div>
数据预览：
<!-- 预览数据列表-->
<div id="viewdatafm" class="easyui-dialog" closed="true" buttons="#dlg-buttons-up-vwrfm" style="width:800px;height:500px;" >
<table id="vwrtb" class="easyui-datagrid" style="width:auto;height:100%;overflow:hidden;"
striped="true" fit="true" fitColumns="true" scrollbarSize=0
pagination="false" rownumbers="true" singleSelect="true"
data-options="fit:true,border:false,pageList:[20000]" >
<thead>
<tr>
<th data-options="field:'expname',align:'left',halign:'center',width:'20%'" formatter=color>姓名</th>
<th data-options="field:'sex',align:'left',halign:'center',width:'20%'" formatter=color>性别</th>
<th data-options="field:'birth',align:'left',halign:'center',width:'20%'" formatter=color>出生年月</th>
<th data-options="field:'busitype',align:'left',halign:'center',width:'20%'" formatter=color>行业</th>
<th data-options="field:'memberlvl',align:'left',halign:'center',width:'20%'" formatter=color>级别</th>
<th data-options="field:'isagriunit',align:'left',halign:'center',width:'20%'" formatter=color>是否是农业厅单位</th>
<th data-options="field:'orgname',align:'left',halign:'center',width:'20%'" formatter=color>单位</th>
<th data-options="field:'deptname',align:'left',halign:'center',width:'20%'" formatter=color>部门</th>
<th data-options="field:'duty',align:'left',halign:'center',width:'20%'" formatter=color>职务</th>
<th data-options="field:'prolevel',align:'left',halign:'center',width:'20%'" formatter=color>职称</th>
<th data-options="field:'expdomain',align:'left',halign:'center',width:'20%'" formatter=color>专业领域</th>
<th data-options="field:'specialwork',align:'left',halign:'center',width:'20%'" formatter=color>处置特长</th>
<th data-options="field:'workpart',align:'left',halign:'center',width:'20%'" formatter=color>部门担负职责</th>
<th data-options="field:'emergencyduty',align:'left',halign:'center',width:'20%'" formatter=color>应急职责</th>
<th data-options="field:'officeno',align:'left',halign:'center',width:'20%'" formatter=color>办公室电话</th>
<th data-options="field:'phoneno',align:'left',halign:'center',width:'20%'" formatter=color>手机</th>
<th data-options="field:'expdesc',align:'left',halign:'center',width:'20%'" formatter=color>简介</th>
</tr>
</thead>
</table>
</div>
<div id="dlg-buttons-up-vwrfm">
<a href="javascript:void(0)" onclick="implexl();" class="easyui-linkbutton" iconCls="icon-excel">导入数据</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="javascript:$('#viewdatafm').dialog('close')" style="width:90px">取消</a>
</div>
下载模版调用的函数：
function DownLoad(){
$('#dwForm').submit();
}
//批量导入部分
function penaltyinfodown(){
$('#penaltyinfod').dialog('open').dialog('setTitle','导入excel');
$('#penaltyinfo').form('clear');
}
导入数据函数：
// 批量导入
function implexl(){
var filename=$('#file2').filebox('getValue');
if(filename==null || filename=='')
{
showMsg('提示','请选择文件！');
return;
}else{
$.ajax({
type : 'get',
url : '${request.contextPath}/emergencyexpert/loaddata',
dataType : 'json',
async : true,
success : function(data) {
if(data==1){
showMsg('提示','数据有误不允许导入！');
}else{
showMsg('提示','导入成功！');
$('#viewdatafm').dialog('close');
$('#penaltyinfod').dialog('close');
$('#dg').datagrid('reload');
}
}
});
}
}
后台实现：
@ResponseBody @RequestMapping("getfilename") public String getFileName(@RequestParam("file2") MultipartFile file, HttpServletRequest request) { String filePath = ""; String fileName = ""; if (!file.isEmpty()) { if (file.getSize() > 0) filePath = FileUtil.upload(file, request, true); fileName = request.getSession().getServletContext() .getRealPath("/") + filePath; } return fileName; }
@ResponseBody @RequestMapping("viewdata") public JqueryUiJson viewdata(String data, HttpServletRequest request, HttpSession session) { mapflag.put("flag", "0"); if(StrUtil.isBlank(data)){return null;} try { List<String> lis = new ArrayList<String>(Arrays.asList("expname", "sex", "birth","busitype", "memberlvl","isagriunit", "orgname","deptname", "duty", "prolevel","expdomain","specialwork", "workpart","emergencyduty", "officeno", "phoneno","expdesc","org","dept")); //读取excel表中内容 listflag = StrUtil.viewPre(data, lis, 2); //验证必填项 List<String> lisnull = new ArrayList<String>(Arrays.asList("expname","birth")); listflag = StrUtil.dealViewdata(listflag,mapflag,sqlMapper,4,lisnull); //校验日期 List<String> lisdata = new ArrayList<String>(Arrays.asList("birth")); StrUtil.valiDate(listflag, mapflag, lisdata); //校验手机号码 List<String> lisphone = new ArrayList<String>(Arrays.asList("phoneno")); listflag = StrUtil.valiphone(listflag,mapflag,lisphone); //校验电话号码 List<String> telphone = new ArrayList<String>(Arrays.asList("officeno")); listflag = StrUtil.valitelphone(listflag,mapflag,telphone); //验证组织机构的单位 for(int i=0;i<listflag.size();i++){ if(listflag.get(i).get("isagriunit").equals("是")){ if(null != listflag.get(i).get("orgname") && !"".equals(listflag.get(i).get("orgname"))){ String[] strs = listflag.get(i).get("orgname").split("、"); //获取前两位的pid String pid = sqlMapper.selectOne("select pid from sys_monitor_org " + "where name='"+strs[0]+"'",String.class); Map<String, Object> map = sqlMapper.selectOne ("select id,pid from sys_monitor_org " + "where name='"+strs[1]+"'"); String pid2 = map.get("pid").toString(); String id = map.get("id").toString(); //判断前两位填写是否符合格式 if("-1".equals(pid) && "0".equals(pid2) && strs.length > 2){ //从分割的第三项开始比较 for(int t=2;t<strs.length;t++){ //获取id,及其类型 List<Map<String,Object>> lismap2 = sqlMapper.selectList("select id,type from sys_monitor_org " + "where name='"+strs[t]+"' and pid='"+id+"'"); //根据条数是否为空判断数据是否有效 if(null != lismap2 && lismap2.size() == 1){ id = lismap2.get(0).get("id").toString(); //当数据为 if(t == strs.length-1 && "0".equals(lismap2.get(0).get("type").toString())){ listflag.get(i).put("org", id); }else if(t == strs.length-1 && !"0".equals(lismap2.get(0).get("type").toString())){ listflag.get(i).put("orgname", "20error"); mapflag.put("flag", "1"); break; } }else{ listflag.get(i).put("orgname", "20error"); mapflag.put("flag", "1"); break; } } }else{ listflag.get(i).put("orgname", "20error"); mapflag.put("flag", "1"); } } } } //验证组织机构的部门 for(int i=0;i<listflag.size();i++){ if(listflag.get(i).get("isagriunit").equals("是")){ if(null != listflag.get(i).get("deptname") && !"".equals(listflag.get(i).get("deptname"))){ //获取id,及其类型 List<Map<String,Object>> lismap = sqlMapper.selectList("select id from sys_monitor_org " + "where name='"+listflag.get(i).get("deptname")+"' and pid='"+listflag.get(i).get("org")+"' and type='1'"); if(lismap.size() != 1){ listflag.get(i).put("deptname", "21error"); mapflag.put("flag", "1"); }else{ listflag.get(i).put("dept",lismap.get(0).get("id").toString()); } } }else if(listflag.get(i).get("isagriunit").equals("否") && (null != listflag.get(i).get("deptname") && !"".equals(listflag.get(i).get("deptname"))) && (null == listflag.get(i).get("orgname") || "".equals(listflag.get(i).get("orgname")))){ listflag.get(i).put("orgname", "20error"); mapflag.put("flag", "1"); } } JqueryUiJson jqueryUiJson = new JqueryUiJson(ExampleUtil.getPageInfo(listflag).getTotal(), listflag); return jqueryUiJson; } catch (Exception e) { e.printStackTrace(); return null; } }
1 @ResponseBody 2 @RequestMapping("loaddata") 3 public String loaddata(String data, HttpServletRequest request, 4 HttpSession session) { 5 //返回数据标志位 "1"不允许导入，"0"允许导入 6 String flag = "0"; 7 if(StrUtil.isBlank(data)){return ConstantUtil .FAILURE;} 8 if("1".equals(mapflag.get("flag"))){ 9 flag="1"; 10 }else{ 11 //获取数据库中列表 12 List<String> lisda = StrUtil.getColumnNameStr(sqlMapper,"td_emergency_expert"); 13 //替换字典表字段 14 Map<String,String> mapdict = new HashMap<String,String>(); 15 mapdict.put("dict_sex", "sex"); 16 mapdict.put("dict_jobtype", "busitype"); 17 mapdict.put("dict_role_type", "memberlvl"); 18 mapdict.put("dict_yes_no", "isagriunit"); 19 listflag = StrUtil.dealViewdata(listflag, mapflag, sqlMapper, 2,mapdict); 20 //添加数据库中的数据 21 for(int i=0;i<listflag.size();i++){ 22 if(listflag.get(i).get("isagriunit").equals("0")){ 23 listflag.get(i).put("org",listflag.get(i).get("orgname")); 24 listflag.get(i).put("dept",listflag.get(i).get("deptname")); 25 } 26 listflag.get(i).put("expid", StrUtil.geneUUID()); 27 listflag.get(i).put("createuser",session.getAttribute("userId").toString()); 28 listflag.get(i).put("region",session.getAttribute("region").toString()); 29 listflag.get(i).put("createdate",new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())); 30 listflag.get(i).put("photo",""); 31 } 32 sqlMapper.insert(StrUtil.spStr("td_emergency_expert", lisda, listflag)); 33 } 34 return flag; 35 }
浙公网安备 33010602011771号