extjs读取excel

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.youkuaiyun.com/detail/love_java_cc/9389390