导入excel的前端页面代码:
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="utf-8">
<title>上传监控人员</title>
<th:block th:include="fragments/headerinc :: head"></th:block>
<link rel="stylesheet" th:href="@{/content/home/css/stable.ui.css}">
<script th:src="@{/content/util/jquery/jquery.form.js}" ></script>
</head>
<body class="pop-body">
<div class="pop">
<div class="pop-content">
<div class="box-center">
<div class="nav-tabs-custom nav-main">
<tr>
<td align="left" colspan="3" >
<br/>
<a th:href="@{/content/home/excel/zdjkrydr.xls}"><font color="red">【导入模板下载】</font></a>
<br/>
</td>
</tr>
<form role="form" class="form-horizontal" id="importform" method="post" enctype="multipart/form-data"
th:action= http://localhost:8082/sys/user/importExcel >
<div>
<td>批量导入数据:</td><br/>
<input type="file" class="form-control" name="Identity" id="Identity" style="height: 50px;"/>
<input type="hidden" name="personType" value="0"/>
<input type = 'submit'>
</div>
</form>
</div>
</div>
</div>
</div>
<script type="text/javascript" th:inline="javascript">
//导入上传
function test(){
if ($("#Identity").val() == "") {
alert("请上传文档!");
return false;
}
if (!$("#Identity").val() == "") {
$("#importform").ajaxSubmit(function(data){
if(data.aa == 1){
var msg = "已有" + data.result1 + "的电话号码!";
layer.msg(msg, {icon: 2});
return 1;
}else if(data.aa == 2){
var msg = "手机号不能为空!";
layer.msg(msg, {icon: 2});
return 2;
}else{
var index=parent.layer.getFrameIndex(window.name);
parent.layer.msg('保存成功!', {icon: 1});
parent.layer.close(index);
return 0;
}
})
}
}
</script>
</body>
</html>
后端接口代码:
@RequestMapping(value = "importExcel", method = RequestMethod.POST)
@ResponseBody
public Map<String, Object> importExcel(HttpServletRequest request,
@RequestParam(value = "Identity") MultipartFile file, Integer personType) throws Exception {
Map<String, Object> maps = new HashMap<String, Object>();
FileUtil fileUtil = new FileUtil();
File tmpFile = fileUtil.multipartToFile(request, file);
List<Map> list = new ArrayList<Map>();// 存储数据的集合
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(tmpFile));
HSSFWorkbook wb = new HSSFWorkbook(fs);// 读取上传的文件
HSSFSheet sheet = wb.getSheetAt(0);// 读取第一张表
// 读取标题,放入list,标题在表格的第三行,读取的excel行和列都是从0开始
HSSFRow row = sheet.getRow(0); // 第一行读取(2改0)
List<String> listbiaoti = new ArrayList<String>();
// 循环该标题行的每一列,把值添加到标题的list中
for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
HSSFCell cell = row.getCell(i);
listbiaoti.add(i, cell.toString());
}
// 从第四行开始取正文内容,最大行数为表格的最大行数
for (int j = 1; j <= sheet.getLastRowNum(); j++) { // 第二行读取正文(j=3改j=1)
row = sheet.getRow(j);// 得到每一行
Map<String, String> map = new HashMap<String, String>();// 用于存储一行数据,必须在这里创建
// 循环已经存入的标题的list,做为map集合的KEY
for (int i = 0; i < listbiaoti.size(); i++) {
String cellValue = "";
HSSFCell cell = row.getCell(i);// 得到每一列的值
// 判断是int类还是String类型
if(cell == null){
map.put((String) listbiaoti.get(i), "");
}else{
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
// 解决数字类型转换错误
DecimalFormat df = new DecimalFormat("0");
cellValue = df.format(cell.getNumericCellValue());
map.put((String) listbiaoti.get(i), cellValue);
} else {
// 存入map中
map.put((String) listbiaoti.get(i), cell.toString());
}
}
}
// 将map存到list中
list.add(map);
}
// 迭代Map中的数据,存入表中
SysUser sysUser = null;
for (Map map1 : list) {
sysUser = new SysUser();
String tel = map1.get("手机号").toString();
String[] tels = tel.split("#");
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
sysUser.setCreateTime(Timestamp.valueOf(df.format(new Date())));
if (StringUtils.isNotEmpty(map1.get("用户").toString())) {
sysUser.setId(map1.get("用户").toString());
}
if (StringUtils.isNotEmpty(map1.get("用户名").toString())) {
sysUser.setUsername(map1.get("用户名").toString());
}
if (StringUtils.isNotEmpty(map1.get("姓名").toString())) {
sysUser.setRealName(map1.get("姓名").toString());
}
if (StringUtils.isNotEmpty(map1.get("手机号").toString())) {
sysUser.setPhone(map1.get("手机号").toString());
}
if (StringUtils.isNotEmpty(map1.get("邮箱").toString())) {
sysUser.setEmail(map1.get("邮箱").toString());
} else {
sysUser.setEmail(null);
}
if (StringUtils.isNotEmpty(map1.get("部门").toString())) {
sysUser.setDepartment(map1.get("部门").toString());
}
if (StringUtils.isNotEmpty(map1.get("密码").toString())) {
sysUser.setPassword(map1.get("密码").toString());
}
sysUser.setIsDelete(0);
sysUser.setUserRole(0);
userService.saveOrUpdate(sysUser);
}
// 删除上传的临时文件
if (tmpFile.exists()) {
tmpFile.delete();
}
return maps;
}