1.extjs代码(upload4excel.js)
Ext.onReady(function() {
var form = new Ext.form.FormPanel({
renderTo : Ext.getBody(),
labelAlign : 'left',
bodyStyle : 'text-align:left',
title : '文件上传',
labelWidth : 60,
frame : true,
url : 'userAction_readExcel',// fileUploadServlet
width : '100%',
height : '100%',
fileUpload : true,
html : '<div id="msg" class="tipmsg"></div><div id="msg_fail" class="tipmsg_fail"></div>',
items : [{
xtype : 'textfield',
fieldLabel : '文件名',
name : 'excel',
allowBlank : false,
msgTarget : 'under',
validator : function(value) {
if (value != "") {
var arr = value.split('.');
// xls,xlsx两种格式均可 by Yang
if (arr[arr.length - 1] != 'xls'
&& arr[arr.length - 1] != 'xlsx') {
return '文件不合法,请上传excel文件';
} else {
return true;
}
}
},
inputType : 'file'// 文件类型
}],
buttonAlign : "center",
buttons : [{
text : '上传',
handler : function() {
form.getForm().submit({
success : function(form, response) {
Ext.get("msg_fail").dom.innerHTML = "";
Ext.get("msg").dom.innerHTML = "上传成功";
},
failure : function() {
Ext.get("msg").dom.innerHTML = "";
Ext.get("msg_fail").dom.innerHTML = "文件上传失败";
}
});
}
}]
});
var win = new Ext.Window({
title : '个人信息导入',
resizable : true,
width: '60%',
shadow : true,
modal : false,
closable : true,
items : form
});
win.show();
});
2.userAction_readExcel方法代码
//读取excel文档
public String readExcel() throws Exception {
try {
// 获取客户端上传的excel在服务器上的位置
userService.loadExcelToDB(excel);
// 无异常返回成功
sendMsg2Client(Constants.JSON_SUCCESS);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
// 有异常返回失败
sendMsg2Client(Constants.JSON_FAILURE);
}
return null;
}
3.userService_loadExcelToDB(File excel)方法代码
public void loadExcelToDB(File excel) throws BiffException, IOException {
// TODO Auto-generated method stub
// 业务层处理excel的获取
Workbook workbook = Workbook.getWorkbook(excel);
Sheet sheet = workbook.getSheet(0);
int rows = sheet.getRows();
int columns = sheet.getColumns();
// 存放sheet0的所有数据
ArrayList<ArrayList<String>> sheetData = new ArrayList<ArrayList<String>>(
rows + 1);
for (int row = 1; row < rows; row++) {
// 存放sheet0每一行的数据
ArrayList<String> rowData = new ArrayList<String>(columns + 1);
for (int col = 0; col < columns; col++) {
rowData.add(sheet.getCell(col, row).getContents());
}
sheetData.add(rowData);
}
userDAO.addMembers(sheetData);
}
4.userDAOImpl_addMembers()方法
public void addMembers(ArrayList<ArrayList<String>> excelData) {
// TODO Auto-generated method stub
Session session = getCurrentSession();
String sql_mb = "insert ignore into member values(";
for (int i = 0; i < excelData.size(); i++) {
// 将信息插入到member表
ArrayList<String> rowData = excelData.get(i);
StringBuilder sb = new StringBuilder(sql_mb);
for (int j = 0; j < rowData.size(); j++) {
sb.append("'" + rowData.get(j) + "',");
}
int updateCount_mb = session.createSQLQuery(
sb.substring(0, sb.length() - 1) + ")").executeUpdate();
if (updateCount_mb != 0) {
// 将信息加入到user表
String name = rowData.get(0);// excel第1栏为姓名
String sex = rowData.get(1);// excel第2栏为性别
String birthday = rowData.get(2);// excel第3栏为出生日期
String sql_user = "insert ignore into user(name,sex,birthday) values('"
+ name + "','" + sex + "','" + birthday + "')";
int updateCount_users = session.createSQLQuery(sql_user)
.executeUpdate();
}
}
}
5.数据库表( member,user)字段
Id | Name | Sex | birthday |
name | sex | birthday |
附上源码下载地址:
http://download.csdn.net/detail/love_java_cc/9389390