ssm之九 批量导入excel到数据库
【摘要】
JAVA就业套餐课：https://edu.csdn.net/combo/detail/1230
本案例承接以前的SSM整合系列，针对Oracle中的Dept表做操作，如果单击浏览，则进行批量导入；单击2则是单个导入。
<%@ page language="java" import="java.util.*" ...
JAVA就业套餐课：https://edu.csdn.net/combo/detail/1230
本案例承接以前的SSM整合系列，针对Oracle中的Dept表做操作，如果单击浏览，则进行批量导入；单击2则是单个导入。
-
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
-
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
-
<%
-
String path = request.getContextPath();
-
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
-
%>
-
-
-
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
-
<html>
-
<head>
-
<base href="<%=basePath%>">
-
-
<title>部门添加</title>
-
-
<meta http-equiv="pragma" content="no-cache">
-
<meta http-equiv="cache-control" content="no-cache">
-
<meta http-equiv="expires" content="0">
-
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
-
<meta http-equiv="description" content="This is my page">
-
<script type="text/javascript" src="easyui/jquery-1.8.3.min.js"></script>
-
<!--
-
<link rel="stylesheet" type="text/css" href="styles.css">
-
-->
-
<script type="text/javascript"><%--
-
function onload(){
-
var temp = '${errormsg}';
-
if(temp!=''){
-
alert(temp);
-
}
-
-
}
-
--%>
-
$(function(){
-
$("#subbtn").click(function(){
-
var deptNo = $(".deptNo").val();
-
if($.trim(deptNo)==''){
-
// alert("部门编号不能为空！");
-
$("#spana").html("部门编号不能为空！");
-
return;
-
}else{
-
if(deptNo.length<2){
-
$("#spana").html("部门编号不能小于2位数！");
-
return;
-
}
-
}
-
var dName = $("#dName").val();
-
if(dName==''){
-
// alert("部门编号不能为空！");
-
$("#spana").html("部门名称不能为空！");
-
return;
-
}
-
var loc = $("#loc").val();
-
if(loc==''){
-
// alert("部门编号不能为空！");
-
$("#spana").html("部门位置不能为空！");
-
return;
-
}
-
$("#form1").submit();
-
});
-
});
-
</script>
-
-
-
</head>
-
-
<body onload="onload()">
-
<div align="center">部门添加 <c:if test="${errormsg}">1111</c:if> <span id="spana" style="color:#ff0000">${errormsg}</span> </div>
-
<form action="dept/deptImp" method="post" enctype="multipart/form-data">
-
<div align="center"> <a href="jsp/deptinfo.xls">模板下载</a> <input type="file" name="mFile"><input type="submit" value="提交"> </div>
-
</form>
-
<form action="dept/add" method="post" id="form1">
-
<table border="1" align="center" width="80%">
-
<tr align="center"><td>部门编号</td><td> <input type="text" name="deptNo" class="deptNo"> </td> </tr>
-
<tr align="center"><td>部门名称</td><td> <input type="text" name="dName" id="dName"> </td> </tr>
-
<tr align="center"><td>部门位置</td><td> <input type="text" name="loc" id="loc"> </td> </tr>
-
<tr align="center"><td colspan="2"> <input type="button" id="subbtn" value="提交"> </td></tr>
-
</table>
-
</form>
-
</body>
-
</html>
批量导入部门的控制器代码:
-
package com.aaa.ssm.controller;
-
-
import java.io.IOException;
-
import java.io.InputStream;
-
import java.util.Enumeration;
-
import java.util.HashMap;
-
import java.util.List;
-
import java.util.Map;
-
-
import javax.servlet.http.HttpServletRequest;
-
-
import jxl.Cell;
-
import jxl.Sheet;
-
import jxl.Workbook;
-
import jxl.read.biff.BiffException;
-
-
import org.springframework.beans.factory.annotation.Autowired;
-
import org.springframework.stereotype.Controller;
-
import org.springframework.ui.Model;
-
import org.springframework.web.bind.annotation.RequestMapping;
-
import org.springframework.web.bind.annotation.RequestParam;
-
import org.springframework.web.bind.annotation.ResponseBody;
-
import org.springframework.web.multipart.MultipartFile;
-
-
import com.aaa.ssm.entity.Dept;
-
import com.aaa.ssm.service.IDeptService;
-
import com.aaa.ssm.util.PageData;
-
import com.aaa.ssm.util.PageUtil;
-
-
/**
-
*@classname:DeptController.java
-
*@discription:部门 控制器,负责与前后台及模型交互
-
*
-
*@author:zhz
-
*@createtime:2017-4-13上午09:09:16
-
*@version:1.0
-
*/
-
@Controller
-
@RequestMapping("/dept")
-
public class DeptController {
-
-
@Autowired
-
private IDeptService service;
-
/**
-
* 部门列表方法
-
* @return
-
*/
-
@RequestMapping("/list")
-
public String list(Model model,Integer pageNo,HttpServletRequest request){
-
/*System.out.println("URI地址："+request.getRequestURI());
-
Enumeration pNames=request.getParameterNames();
-
while(pNames.hasMoreElements()){
-
String name=(String)pNames.nextElement();
-
System.out.println("name:"+name+"="+request.getParameter(name));
-
}*/
-
int pageSize=2;
-
if(pageNo==null){
-
pageNo=1;
-
}
-
Map map =new HashMap();
-
map.put("start", (pageNo-1)*pageSize);
-
map.put("end", pageNo*pageSize+1);
-
PageData pageData = service.getList(map);
-
int count = pageData.getCount();//总数量
-
String pageString = new PageUtil(pageSize, pageNo, count, request).getPageString();
-
model.addAttribute("deptList", pageData.getList());
-
model.addAttribute("pageString", pageString);
-
return "dept/list";
-
}
-
-
/**
-
* 批量导入部门信息
-
* @param mFile
-
* @return
-
* @throws IOException
-
* @throws BiffException
-
*/
-
@RequestMapping("/deptImp")
-
public String deptImport(@RequestParam MultipartFile mFile) throws IOException, BiffException{
-
//获取文件流
-
InputStream inputStream = mFile.getInputStream();
-
//利用JAR提供的Workbook类读取文件
-
Workbook workbook = Workbook.getWorkbook(inputStream);
-
//获取工作薄
-
Sheet sheet = workbook.getSheet(0);
-
//获取总行数
-
int rows = sheet.getRows();
-
//获取总列数
-
int cells = sheet.getColumns();
-
//循环读取数据
-
Dept dept = null;
-
for(int i=1;i<rows;i++){
-
dept = new Dept();
-
Cell[] cell=sheet.getRow(i);
-
dept.setDeptNo(Integer.valueOf(cell[0].getContents()));
-
dept.setdName(cell[1].getContents());
-
dept.setLoc(cell[2].getContents());
-
service.add(dept);
-
/*for(int j=0;j<cells;j++){
-
System.out.println(cell[j].getContents());
-
}*/
-
}
-
return "redirect:list";
-
}
-
/**
-
* 返回DeptJson
-
* @return
-
*/
-
@ResponseBody
-
@RequestMapping("/deptJson")
-
public Object deptJson(){
-
Map map =new HashMap();
-
map.put("start", 0);
-
map.put("end", 100);
-
PageData pageData = service.getList(map);
-
int count = pageData.getCount();//总数量
-
List<Dept> deptList = pageData.getList();
-
Map mapJson =new HashMap();
-
mapJson.put("total", count);
-
mapJson.put("rows", deptList);
-
return mapJson;
-
}
-
/**
-
* 跳转添加方法
-
* @return
-
*/
-
@RequestMapping("/toAdd")
-
public String toAdd(){
-
return "dept/add";
-
}
-
/**
-
* 添加方法
-
* @param dept
-
* @return
-
*/
-
@RequestMapping("/add")
-
public String add(Dept dept,Model model){
-
try {
-
service.add(dept);
-
return "redirect:list";
-
} catch (Exception e) {
-
// TODO Auto-generated catch block
-
e.printStackTrace();
-
model.addAttribute("errormsg", "添加部门失败！");
-
return "dept/add";
-
}
-
}
-
-
/**
-
* 跳转部门更新
-
* @return
-
*/
-
@RequestMapping("/toUpdate")
-
public String toUpdate(Model model,int deptNo){
-
Dept dept = service.getById(deptNo);
-
model.addAttribute("dept", dept);
-
return "dept/update";
-
}
-
-
/**
-
* 部门更新
-
* @param dept
-
* @return
-
*/
-
@RequestMapping("/update")
-
public String update(Dept dept){
-
service.update(dept);
-
return "redirect:list";
-
}
-
-
/**
-
* 部门删除
-
* @return
-
*/
-
@RequestMapping("/del")
-
public String delete(Integer deptNo){
-
service.delete(deptNo);
-
return "redirect:list";
-
}
-
}
/**
* 批量导入部门信息
* @param mFile
* @return
* @throws IOException
* @throws BiffException
*/
@RequestMapping("/deptImp")
public String deptImport(@RequestParam MultipartFile mFile) throws IOException, BiffException{
//获取文件流
InputStream inputStream = mFile.getInputStream();
//利用JAR提供的Workbook类读取文件
Workbook workbook = Workbook.getWorkbook(inputStream);
//获取工作薄
Sheet sheet = workbook.getSheet(0);
//获取总行数
int rows = sheet.getRows();
//获取总列数
int cells = sheet.getColumns();
//循环读取数据
Dept dept = null;
for(int i=1;i<rows;i++){
dept = new Dept();
Cell[] cell=sheet.getRow(i);
dept.setDeptNo(Integer.valueOf(cell[0].getContents()));
dept.setdName(cell[1].getContents());
dept.setLoc(cell[2].getContents());
service.add(dept);
/*for(int j=0;j<cells;j++){
System.out.println(cell[j].getContents());
}*/
}
return "redirect:list";
}
/**
* 返回DeptJson
* @return
*/
@ResponseBody
@RequestMapping("/deptJson")
public Object deptJson(){
Map map =new HashMap();
map.put("start", 0);
map.put("end", 100);
PageData pageData = service.getList(map);
int count = pageData.getCount();//总数量
List<Dept> deptList = pageData.getList();
Map mapJson =new HashMap();
mapJson.put("total", count);
mapJson.put("rows", deptList);
return mapJson;
}
/**
* 跳转添加方法
* @return
*/
@RequestMapping("/toAdd")
public String toAdd(){
return "dept/add";
}
/**
* 添加方法
* @param dept
* @return
*/
@RequestMapping("/add")
public String add(Dept dept,Model model){
try {
service.add(dept);
return "redirect:list";
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
model.addAttribute("errormsg", "添加部门失败！");
return "dept/add";
}
}
/**
* 跳转部门更新
* @return
*/
@RequestMapping("/toUpdate")
public String toUpdate(Model model,int deptNo){
Dept dept = service.getById(deptNo);
model.addAttribute("dept", dept);
return "dept/update";
}
/**
* 部门更新
* @param dept
* @return
*/
@RequestMapping("/update")
public String update(Dept dept){
service.update(dept);
return "redirect:list";
}
/**
* 部门删除
* @return
*/
@RequestMapping("/del")
public String delete(Integer deptNo){
service.delete(deptNo);
return "redirect:list";
}
}
服务层Service的代码:
-
package com.aaa.ssm.service;
-
-
import java.util.List;
-
import java.util.Map;
-
-
import com.aaa.ssm.entity.Dept;
-
import com.aaa.ssm.util.PageData;
-
-
/**
-
*@classname:IDeptService.java
-
*@discription:部门业务类接口
-
*
-
*@author:zhz
-
*@createtime:2017-4-13上午09:06:39
-
*@version:1.0
-
*/
-
public interface IDeptService {
-
-
/**
-
* 根据部门编号获取部门对象
-
* @return
-
*/
-
public Dept getById(int deptNo);
-
/**
-
* 部门列表
-
* @return
-
*/
-
public PageData getList(Map map);
-
/**
-
* 部门添加
-
* @param dept
-
*/
-
public void add(Dept dept);
-
/**
-
* 部门更新
-
* @param dept
-
*/
-
public void update(Dept dept);
-
/**
-
* 部门删除
-
* @param deptNo
-
*/
-
public void delete(int deptNo);
-
-
}
服务层实现类和以前的一样：
-
/**
-
* 部门添加
-
*/
-
public void add(Dept dept) {
-
// TODO Auto-generated method stub
-
dao.add(dept);
-
// System.out.println(1/0);
-
}
扩展篇，该段示例代码只能实现，批量导入dept表中的数据到数据库，如何实现批量如何多个表呢？
文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。
原文链接：aaaedu.blog.csdn.net/article/details/77124646
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）