Poi-18、导入excel数据

最新推荐文章于 2021-11-04 15:50:28 发布

搞钱自律

最新推荐文章于 2021-11-04 15:50:28 发布

阅读量779

点赞数

分类专栏： # poi 文章标签： poi

本文链接：https://blog.csdn.net/weixin_43472934/article/details/111056216

版权

poi 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

前端代码

<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>Basic DataGrid - jQuery EasyUI Demo</title>
	<link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/default/easyui.css">
	<link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/icon.css">
	<script type="text/javascript" src="jquery-easyui-1.3.3/jquery.min.js"></script>
	<script type="text/javascript" src="jquery-easyui-1.3.3/jquery.easyui.min.js"></script>
	<script type="text/javascript" src="jquery-easyui-1.3.3/locale/easyui-lang-zh_CN.js"></script>
	<script>
		function openUploadFileDialog() {
			$("#dlg2").dialog('open').dialog('setTitle','批量导入用户数据');
		}
		
		function downloadTemplate() {
			window.open('template/userExporTemplate.xls')
		}
		
		function uploadFile() {
			$("#uploadForm").form("submit",{
				success:function(result){
					console.log(result);
					var result=eval('('+result+')');
					console.log('使用eval函数的响应结果：',result);
					if(result.errorMsg){
						$.messager.alert("系统提示",result.errorMsg);
					}else{
						$.messager.alert("系统提示","上传成功");
						$("#dlg2").dialog("close");
						$("#dg").datagrid("reload");
					}
				}
			});
		}
		
	</script>
</head>
<body>
	<table id="dg" title="用户管理" class="easyui-datagrid" style="width:700px;height:365px"
            url="user/list.do"
            toolbar="#toolbar" pagination="true"
            rownumbers="true" fitColumns="true" singleSelect="true">
        <thead>
            <tr>
            	<th field="id" width="50">编号</th>
                <th field="username" width="50">姓名</th>
                <th field="password" width="50">密码</th>
                <th field="age" width="50">年龄</th>
            </tr>
        </thead>
    </table>
    <div id="toolbar">
        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-add" plain="true" onclick="newUser()">添加用户</a>
        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-edit" plain="true" onclick="editUser()">编辑用户</a>
        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-remove" plain="true" onclick="deleteUser()">删除用户</a>
        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" onclick="exportUser()">导出用户</a>
        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export" plain="true" onclick="exportUser2()">使用模板导出用户</a>
        <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-import" plain="true" onclick="openUploadFileDialog()">使用模板导出用户</a>
    </div>
	
	<!-- 批量导入的对话框start-->
		<div id="dlg2" class="easyui-dialog" style="width:400px;height:180px;padding:10px 20px"
            closed="true" buttons="#dlg-buttons2">
        <form id="uploadForm" action="user/import.do" method="post" enctype="multipart/form-data">
        	<table cellspacing="10px;">
        		<tr>
        			<td>下载模板</td>
        			<td><a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-ok" onclick="downloadTemplate()">用户user模板</a></td>
        		</tr>
        		<tr>
        			<td>导入模板</td>
        			<td><input type="file" name="userUploadFile"></td>
        		</tr>
        	</table>
        </form>
	</div>
    
	<div id="dlg-buttons2">
		<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-ok" onclick="uploadFile()">确定导入</a>
		<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="javascript:$('#dlg2').dialog('close')">关闭</a>
	</div>
	
	<!-- 批量导入的对话框end-->
	
</body>
</html>

后台代码

1,控制层

	@RequestMapping("/import")
	public String upload(MultipartFile userUploadFile,HttpServletResponse response)throws Exception{
		InputStream inputStream = userUploadFile.getInputStream();
		POIFSFileSystem fs=new POIFSFileSystem(inputStream);
		HSSFWorkbook wb=new HSSFWorkbook(fs);
		HSSFSheet hssfSheet=wb.getSheetAt(0);  // 获取第一个Sheet页
		if(hssfSheet!=null){
			for(int rowNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){
				HSSFRow hssfRow=hssfSheet.getRow(rowNum);
				if(hssfRow==null){
					continue;
				}
				User user=new User();
				user.setId(Integer.valueOf(ExcelUtil.formatCell(hssfRow.getCell(0)).substring(0, ExcelUtil.formatCell(hssfRow.getCell(0)).indexOf("."))));
				user.setUsername(ExcelUtil.formatCell(hssfRow.getCell(1)));
				user.setPassword(ExcelUtil.formatCell(hssfRow.getCell(2)));
				user.setAge(Integer.valueOf(ExcelUtil.formatCell(hssfRow.getCell(3)).substring(0, ExcelUtil.formatCell(hssfRow.getCell(3)).indexOf("."))));
				userService.insert(user);
			}
		}
		Map<String, Object> map=new HashMap<String, Object>();
		map.put("success", "true");
		return JSON.toJSONString(map);
	}

2,工具类ExcelUtil

public static String formatCell(HSSFCell hssfCell){
		if(hssfCell==null){
			return "";
		}else{
			if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
				return String.valueOf(hssfCell.getBooleanCellValue());
			}else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
				return String.valueOf(hssfCell.getNumericCellValue());
			}else{
				return String.valueOf(hssfCell.getStringCellValue());
			}
		}
	}

遇到的问题

1,使用列表显示姓名，乱码，但springmvc配置了中文乱码过滤器，找了下资料，发现这过滤器，只是过滤请求参数的字符编码格式。响应想通过response.setContentType("application/json;charset=UTF-8");无效。只能在@RequestMapping(value = "/list",produces="application/json;charset=UTF-8")才有用
2,批量导入时，到数据库是乱码。在db.properties文件jdbc.url=jdbc:mysql://localhost:3306/gj1?characterEncoding=utf-8

搞钱自律

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Poi-18、导入excel数据

前端代码<!DOCTYPE html><html><head> <meta charset="UTF-8"> <title>Basic DataGrid - jQuery EasyUI Demo</title> <link rel="stylesheet" type="text/css" href="jquery-easyui-1.3.3/themes/default/easyui.css"> <link
复制链接

扫一扫