目录
2、 在applicationContext.xml中增加两个bean对象 1
(2) 在struts.xml中增加importExcleStudent action 5
(3) 在ExcelAction中增加importExcleStudent方法 5
(2) 在struts.xml中增加importExcleTeacher action 13
(3) 在ExcleAction中增加importExcleTeacher 方法 13
其中ExcelAction继承ActionSupport类,继承SessionAware接口
public class ExcleAction extends ActionSupport implements SessionAware {
Map session;
public Map getSession() {
return session;
}
}
2.在applicationContext.xml中增加两个bean对象
<bean id="excle" class="web.action.ExcleAction">
<property name="teacherDao" ref="teacherDaoImp"></property>
<property name="studentDao" ref="studentDaoImp"></property>
</bean>
定义studentDao和teacherDao,并生成get和set函数
private StudentDao studentDao;
private TeacherDao teacherDao;
public TeacherDao getTeacherDao() {
return teacherDao;
}
public void setTeacherDao(TeacherDao teacherDao) {
this.teacherDao = teacherDao;
}
public StudentDao getStudentDao() {
return studentDao;
}
public void setStudentDao(StudentDao studentDao) {
this.studentDao = studentDao;
}
4.学生的导入
<%@page contentType="text/html; charset=UTF-8"%>
<%@taglib uri="/struts-tags" prefix="s"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ page isELIgnored="false"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<%request.setCharacterEncoding("gb2312");%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>导入学生</title>
<link rel="stylesheet" href="jules/css/bootstrap.min.css"
type="text/css" />
<script type="text/javascript" src="jules/js/jquery.min.js"></script>
<script type="text/javascript" src="js/bootstrap.min.js"></script>
<!-- Include the plugin's CSS and JS: -->
<script type="text/javascript" src="js/bootstrap-multiselect.js"></script>
<link href="jules/css/bootstrap.min14ed.css?v=3.3.6" rel="stylesheet">
<link href="jules/css/font-awesome.min93e3.css?v=4.4.0" rel="stylesheet">
<link href="jules/css/animate.min.css" rel="stylesheet">
<link href="jules/css/style.min862f.css?v=4.1.0" rel="stylesheet">
<link href="jules/css/bootstrap.min14ed.css?v=3.3.6" rel="stylesheet">
<link href="jules/css/font-awesome.min93e3.css?v=4.4.0" rel="stylesheet">
<link href="css/bootstrap-datetimepicker.css" rel="stylesheet">
<link href="jules/css/animate.min.css" rel="stylesheet">
<link href="jules/css/plugins/chosen/chosen.css" rel="stylesheet">
<link href="jules/css/style.min862f.css?v=4.1.0" rel="stylesheet">
<link href="jules/css/plugins/iCheck/custom.css" rel="stylesheet">
<link
href="jules/css/plugins/awesome-bootstrap-checkbox/awesome-bootstrap-checkbox.css"
rel="stylesheet">
<link rel="stylesheet" href="css/select2.css" />
<script type="text/javascript" src="js/jquery-1.7.2.js"></script>
<link rel="stylesheet" href="zyupload/skins/zyupload-1.0.0.min.css "
type="text/css">
</head>
<body class="gray-bg">
<div class="wrapper wrapper-content animated fadeInRight article">
<div class="row">
<div class="col-lg-10 col-lg-offset-1">
<div class="ibox">
<div class="ibox-content">
<div class="ibox float-e-margins">
<div class="text-center">
<h5>导入学生</h5>
</div>
<div class="ibox-content">
<form id="file_form" action="importExcleStudent"
enctype="multipart/form-data" method="post">
<div class="form-group">
<label class="col-sm-2 control-label">请选择导入文件</label>
<div class="col-sm-10">
<input type="file" name="importStudent" id="file_input"
class="filename" /> <br /><input type="submit" class="btn btn-primary"
value="确定" id='upFile-btn'>
</div>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<script type="text/javascript" src="js/jquery-1.7.2.js"></script>
<script type="text/javascript" src="js/jquery.form.js"></script>
<script type="text/javascript">
$(function() {
$("#file_form").submit(
function() {
//首先验证文件格式
var fileName = $('#file_input').val();
if (fileName === '') {
alert('请选择文件');
return false;
}
var fileType = (fileName.substring(fileName
.lastIndexOf(".") + 1, fileName.length))
.toLowerCase();
if (fileType !== 'xls') {
alert('文件格式不正确,excel文件!');
return false;
}
$("#file_form").ajaxSubmit({
dataType : "json",
success : function(data, textStatus) {
if (data['s'] === 'OK') {
alert("文件上传成功");
// window.location.href = "https://localhost:8080/SDSYw/excleFile/importExcleStudent.log";
$('#file_input').html("");
console.log('上传文件成功');
} else {
console.log('文件格式错误');
}
return false;
}
});
return false;
});
});
</script>
</body>
</html>
2)在struts.xml中增加importExcleStudent action
在importStudents.jsp中有一个importExcelStudent Action
<package name="excle" extends="json-default">
<action name="importExcleStudent" method="importExcleStudent" class="excle">
<result type="json" name="success">
<param name="root">s</param>
</result>
</action>
</package>
3)在ExcelAction中增加importExcleStudent方法
private JSONObject s;
private int id;
public JSONObject getS() {
return s;
}
public void setS(JSONObject s) {
this.s = s;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
private StudentDao studentDao;
private Timestamp createTime;
private File importStudent;
private String importStudentFileName;
public StudentDao getStudentDao() {
return studentDao;
}
public void setStudentDao(StudentDao studentDao) {
this.studentDao = studentDao;
}
public Timestamp getCreateTime() {
return createTime;
}
public void setCreateTime(Timestamp createTime) {
this.createTime = createTime;
}
public File getImportStudent() {
return importStudent;
}
public void setImportStudent(File importStudent) {
this.importStudent = importStudent;
}
public String getImportStudentFileName() {
return importStudentFileName;
}
public void setImportStudentFileName(String importStudentFileName) {
this.importStudentFileName = importStudentFileName;
}
private static Timestamp getTime() {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//设置日期
try {
Date time = df.parse(df.format(new Date()));
//获取当前的系统时间
return new Timestamp(time.getTime());
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}
private FileWriter createFileWirter(String path) throws IOException{
File file = new File(path);
//设置文件的路径
if (!file.exists()) {//判断文件是否存在
file.createNewFile();
//如果该文件已经存在,则不创建,返回一个false,如果没有,则返回true
System.out.println("创建");
}else{
file.delete();
System.out.println("删除");
}
FileWriter writer = new FileWriter(path, true);
//创建字符输出流对象和已存在的文件相关联,如果文件不存在则新建。
return writer;
}
public String importExcleStudent() throws IOException {
createTime = getTime();
//获取系统当前时间
String path = "/excleFile";
String realpath = ServletActionContext.getServletContext().getRealPath(path);//存储地址
File target = new File(realpath, importStudentFileName);//导入的表的地址
FileWriter writer =createFileWirter(realpath+"/importExcleStudent.log");
if (target.exists()) {
target.delete();
}
try {
FileUtils.copyFile(importStudent, target);
System.out.println("学生信息文件上传成功");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
FileInputStream fi = new FileInputStream(target);//字节输入流,用来访问文件
Workbook rwb = Workbook.getWorkbook(fi);
Sheet rs = rwb.getSheet(0);
//返回工作薄(Workbook)中工作表(Sheet)对象数组
int rows = rs.getRows();// 所有行
int clos = rs.getColumns();// 所有列
// System.out.println(rows+","+clos);
Boolean sex = false;
String stuId, stuName = null, stuSex = null, stuSchool = null, stuCollege = null, stuProfession = null,
stuClass = null, stuClassType = null, stuJobstatus = null, stuTutor = null, stuPhone = null,
stuIdcard = null;
StudentsId stuI = null;
Students student = null;
Students stu = null;
int number=1,j=0;
for (int i = 1; i < rows; i++) {
//System.out.println(i+",,"+rows);
j = 0;
stuId = rs.getCell(j++, i).getContents().trim();
//getCell 返回单元格信息
stuName = rs.getCell(j++, i).getContents();
stuSex = rs.getCell(j++, i).getContents();
stuSchool = rs.getCell(j++, i).getContents();
stuCollege = rs.getCell(j++, i).getContents();
stuProfession = rs.getCell(j++, i).getContents();
stuClass = rs.getCell(j++, i).getContents();
stuClassType = rs.getCell(j++, i).getContents();
stuJobstatus = rs.getCell(j++, i).getContents();
stuTutor = rs.getCell(j++, i).getContents();
stuPhone = rs.getCell(j++, i).getContents();
stuIdcard = rs.getCell(j++, i).getContents();
if (stuSex.equals("男")) {
sex = false;
} else if (stuSex.equals("女")) {
sex = true;
}
stuI = new StudentsId();
stuI.setSStudentId(stuId);
student = null;
student = studentDao.isexist(stuId);
if (student != null) {// 表中存在该学生,则更新
stuI.setSId(student.getId().getSId());
stu = new Students(stuI, stuName, sex, stuSchool, stuCollege, stuProfession, stuClass,
stuClassType, stuJobstatus, stuTutor, stuPhone, stuIdcard,
MD5Util.MD5(stuId.substring(stuId.length() - 6)));
// System.out.println(stuId + "--" + stuId.length() + ">>" + stuId.substring(stuId.length() - 6));
stu.setSCreateTime(student.getSCreateTime());
stu.setSChangeTime(getTime());
studentDao.updatestudent(stu);
writer.write("第"+(number++)+"记录"+stuId+"更新成功\r\n");
} else {// 表中不存在该学生,则添加
System.out.println(stuId + "--" + stuId.length() + ">>" + stuId.substring(stuId.length() - 6));
stu = new Students(stuI, stuName, sex, stuSchool, stuCollege, stuProfession, stuClass,
stuClassType, stuJobstatus, stuTutor, stuPhone, stuIdcard,
MD5Util.MD5(stuId.length()>6?stuId.substring(stuId.length() - 6):("1234567"+stuId).substring(("1234567"+stuId).length() - 6)));
stu.setSCreateTime(createTime);
stu.setSChangeTime(createTime);
stu.setSPicture("https://sdsy.zzjc.edu.cn/SDSYw/image/1.jpg");
studentDao.addstudent(stu);
writer.write("第"+(number++)+"记录"+stuId+"添加成功\r\n");
}
writer.flush();
}
writer.close();
fi.close();
}
catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch(Exception e){
System.out.println(e);
}finally{
}
s = JSONTools.createJsonObject("s", "OK");
return "success";
}
5.导师的导入
<%@page contentType="text/html; charset=UTF-8"%>
<%@taglib uri="/struts-tags" prefix="s"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ page isELIgnored="false"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<%request.setCharacterEncoding("gb2312");%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>导入学生</title>
<link rel="stylesheet" href="jules/css/bootstrap.min.css"
type="text/css" />
<script type="text/javascript" src="jules/js/jquery.min.js"></script>
<script type="text/javascript" src="js/bootstrap.min.js"></script>
<!-- Include the plugin's CSS and JS: -->
<script type="text/javascript" src="js/bootstrap-multiselect.js"></script>
<link href="jules/css/bootstrap.min14ed.css?v=3.3.6" rel="stylesheet">
<link href="jules/css/font-awesome.min93e3.css?v=4.4.0" rel="stylesheet">
<link href="jules/css/animate.min.css" rel="stylesheet">
<link href="jules/css/style.min862f.css?v=4.1.0" rel="stylesheet">
<link href="jules/css/bootstrap.min14ed.css?v=3.3.6" rel="stylesheet">
<link href="jules/css/font-awesome.min93e3.css?v=4.4.0" rel="stylesheet">
<link href="css/bootstrap-datetimepicker.css" rel="stylesheet">
<link href="jules/css/animate.min.css" rel="stylesheet">
<link href="jules/css/plugins/chosen/chosen.css" rel="stylesheet">
<link href="jules/css/style.min862f.css?v=4.1.0" rel="stylesheet">
<link href="jules/css/plugins/iCheck/custom.css" rel="stylesheet">
<link
href="jules/css/plugins/awesome-bootstrap-checkbox/awesome-bootstrap-checkbox.css"
rel="stylesheet">
<link rel="stylesheet" href="css/select2.css" />
<script type="text/javascript" src="js/jquery-1.7.2.js"></script>
<link rel="stylesheet" href="zyupload/skins/zyupload-1.0.0.min.css "
type="text/css">
</head>
<body class="gray-bg">
<div class="wrapper wrapper-content animated fadeInRight article">
<div class="row">
<div class="col-lg-10 col-lg-offset-1">
<div class="ibox">
<div class="ibox-content">
<div class="ibox float-e-margins">
<div class="text-center">
<h5>导入教师</h5>
</div>
<div class="ibox-content">
<form id="file_form" action="importExcleTeacher"
enctype="multipart/form-data" method="post">
<div class="form-group">
<label class="col-sm-2 control-label">请选择导入文件</label>
<div class="col-sm-10">
<input type="file" name="importTeacher" id="file_input"
class="filename" /> <br /><input type="submit" class="btn btn-primary"
value="确定" id='upFile-btn'>
</div>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<script type="text/javascript" src="js/jquery-1.7.2.js"></script>
<script type="text/javascript" src="js/jquery.form.js"></script>
<script type="text/javascript">
$("#clerar").click(function() {
alter("本功能禁用");
//window.location.href ="clearTeachers.action";
});
$(function() {
$("#file_form").submit(
function() {
//首先验证文件格式
var fileName = $('#file_input').val();
if (fileName === '') {
alert('请选择文件');
return false;
}
var fileType = (fileName.substring(fileName
.lastIndexOf(".") + 1, fileName.length))
.toLowerCase();
if (fileType !== 'xls') {
alert('文件格式不正确,excel文件!');
return false;
}
$("#file_form").ajaxSubmit({
dataType : "json",
success : function(data, textStatus) {
if (data['s'] === 'OK') {
alert("文件上传成功");
//window.location.href = "https://sdsy.zzjc.edu.cn/SDSYw/excleFile/importExcleTeacher.log";
$('#file_input').html("");
console.log('上传文件成功');
} else {
console.log('文件格式错误');
}
return false;
}
});
return false;
});
});
</script>
</body>
<!-- Mirrored from www.zi-han.net/theme/hplus/clients.html by HTTrack Website Copier/3.x [XR&CO'2014], Wed, 20 Jan 2016 14:19:44 GMT -->
</html>
2)在struts.xml中增加importExcleTeacher action
在importTeacher.jsp中有importExcelTeacher Action
<action name="importExcleTeacher" method="importExcleTeacher" class="excle">
<result type="json" name="success">
<param name="root">s</param>
</result>
</action>
3)在ExcleAction中增加importExcleTeacher 方法
private TeacherDao teacherDao;
private File importTeacher;
private String importTeacherFileName;
public TeacherDao getTeacherDao() {
return teacherDao;
}
public void setTeacherDao(TeacherDao teacherDao) {
this.teacherDao = teacherDao;
}
public File getImportTeacher() {
return importTeacher;
}
public void setImportTeacher(File importTeacher) {
this.importTeacher = importTeacher;
}
public String getImportTeacherFileName() {
return importTeacherFileName;
}
public void setImportTeacherFileName(String importTeacherFileName) {
this.importTeacherFileName = importTeacherFileName;
}
public String importExcleTeacher() throws IOException {
//导入时间
createTime = getTime();
System.out.println("导入教师");
String path = "/excleFile";
String realpath = ServletActionContext.getServletContext().getRealPath(path);
FileWriter writer =createFileWirter(realpath+"/importExcleTeacher.log");
File target = new File(realpath, importTeacherFileName);
if (target.exists()) {
target.delete();
}
try {
FileUtils.copyFile(importTeacher, target);
System.out.println("教师信息文件上传成功");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
FileInputStream fi = new FileInputStream(target);
Workbook wb = Workbook.getWorkbook(fi);
Sheet rs = wb.getSheet(0);
int rows = rs.getRows();// 行
int clos = rs.getColumns();// 列
System.out.println(rows + "and" + clos);
String tName = null, tId = null, tAddress = null, tTelePhone = null, tEmail = null, tCollege = null,
tPhone = null, tType = null, tdesc = null, tp = null, themeall;
TeacherId teaId = null;
Teacher tea = null;
Teacher teacher = null;
int number=1;
for (int i = 1; i < rows; i++) {
for (int j = 0; j < clos; j += 255) {
tName = rs.getCell(j++, i).getContents();
tId = rs.getCell(j++, i).getContents();
tAddress = rs.getCell(j++, i).getContents();
tTelePhone = rs.getCell(j++, i).getContents();
tEmail = rs.getCell(j++, i).getContents();
tCollege = rs.getCell(j++, i).getContents();
tPhone = rs.getCell(j++, i).getContents();
tType = rs.getCell(j++, i).getContents();
tdesc = rs.getCell(j++, i).getContents();
tp = rs.getCell(j++, i).getContents();
String[] t = tType.split(",|,");
// System.out.println("新增"+tId );
// System.out.println("新增"+tName);
// System.out.println("新增"+tPhone );
// System.out.println("新增"+Integer.parseInt(tTelePhone) );
themeall = "";
tId = tId.trim();
for (int k = 0; k < t.length; k++) {
switch (t[k]) {
case "学习":
themeall += "1,";
break;
case "就业":
themeall += "2,";
break;
case "创业":
themeall += "3,";
break;
case "情感":
themeall += "4,";
break;
case "心理":
themeall += "5,";
break;
case "生活":
themeall += "6,";
break;
case "其他":
themeall += "7,";
break;
default:
break;
}
teaId = new TeacherId();
teaId.setTTeacherId(tId);
tea = null;// 可以不添加,只是为保证tea判断正确
tea = teacherDao.isExist(tId);
if (tea != null) {
// System.out.println("修改");
// System.out.println(tea.getId().getTTeacherId());
teaId.setTId(tea.getId().getTId());
teacher = new Teacher(teaId, MD5Util.MD5(tId), tName, tPhone, Integer.parseInt(tTelePhone),
tEmail, tCollege, tAddress, themeall, tp, tdesc);
teacher.setTFrequency(tea.getTFrequency());
teacher.setTSearchnum(tea.getTSearchnum());
teacher.setTPower(tea.getTPower());
teacher.setTCreateTime(tea.getTCreateTime());
teacher.setTType(tea.getTType());
teacher.setTChangeTime(getTime());
teacherDao.updatepsd(teacher);
writer.write("第"+(number++)+"记录"+tId+"更新成功\r\n");
} else {
teacher = new Teacher(teaId, MD5Util.MD5(tId), tName, tPhone, Integer.parseInt(tTelePhone),
tEmail, tCollege, tAddress, themeall, tp, tdesc);
teacher.setTFrequency(0);
teacher.setTSearchnum(0);
// teacher.setTPicture("https://sdsy.zzjc.edu.cn/SDSYw/image/1.jpg");
teacher.setTPower(1);
teacher.setTType(2);
teacher.setTCreateTime(createTime);
teacher.setTChangeTime(createTime);
teacherDao.addTeacher(teacher);
writer.write("第"+(number++)+"记录"+tId+"添加成功\r\n");
}
writer.flush();
}
}
}
writer.close();
fi.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
s = JSONTools.createJsonObject("s", "OK");
return "success";
}