excle数据导入到数据库

1. 前台代码

function daoruzcexcel(){		//导入
		var row = grid.getData();
		if(row){
			mini.open({
				url: "../F/file.html",
				title: "导入excle信息",
				width: "324px",
				height: "150px",
				showMaxButton: true, //显示最大化按钮
				targetWindow: window, //页面对象。默认是顶级页面。
				onload: function() {
					var iframe = this.getIFrameEl();
					var data = {
						action: "new"
					};
					iframe.contentWindow.SetData(data);
				},
				ondestroy: function(action) {
					grid2.reload();
				}
			});
		}
	}

1. 上传excel页面代码

function  onOk(){				
	$("#formFJ").attr('action', '../../DRExcle?method=daorugf');
	$("#formFJ").submit();
}

2. 后台代码

if (method.equalsIgnoreCase("daorufbf")) {
			int result = 0;
			FileItemFactory factory = new DiskFileItemFactory();
			ServletFileUpload upload = new ServletFileUpload(factory);
			
			try {
				List items = upload.parseRequest(request);
				InputStream is = null;
				Iterator iter = items.iterator();
				while (iter.hasNext()) {
					FileItem item = (FileItem) iter.next();
					if (!item.isFormField()) {
						is = item.getInputStream();
					}
				}
				Workbook workbook = Workbook.getWorkbook(is);
				Sheet sheet = workbook.getSheet(0);

				// 行数
				int rows = sheet.getRows();
				System.out.println("行数" + rows);
				// 列数
				int columns = sheet.getColumns();
				System.out.println("列数" + columns);

				result = dao.daoru( rows, columns, sheet);
				if (result > 0) {
					out.println("<script type='text/javascript'>");
					out.println(" function CloseWindow(action) {");
					out.println("if (window.CloseOwnerWindow)return window.CloseOwnerWindow(action);");
					out.println("else window.close();}");
					out.println(" function TimeOut (){var timer=null;");
					out.println("timer=setInterval(function() {");
					out.println("CloseWindow('cancel');},100); }");
					out.println("TimeOut (); ");
					out.println(" alert('上传成功!');");
					out.println(" </script> ");
					out.flush();
					out.close();
				} else {
					// 上传失败
					out.println("<script type='text/javascript'>");
					out.println(" function CloseWindow(action) {");
					out.println("if (window.CloseOwnerWindow)return window.CloseOwnerWindow(action);");
					out.println("else window.close();}");
					out.println(" function TimeOut (){var timer=null;");
					out.println("timer=setInterval(function() {");
					out.println("CloseWindow('cancel');},100); }");
					out.println("TimeOut (); ");
					out.println(" alert('上传失败!');");
					out.println(" </script> ");
					out.flush();
					out.close();
				}
			} catch (FileUploadException e) {
				e.printStackTrace();
			} catch (BiffException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

• dao接口代码

int daorufbf(int rows, int columns, Sheet sheet);

• dao的实现层

 public int daorufbf(int rows, int columns, Sheet sheet) {
		int result = 0;
		StringBuffer buffer = new StringBuffer();
		buffer.append("insert into JCPT_CLOUD_FBFGL (ID,NAME,SEX,AGE,ADDRESS,XJ,DH) values(?,?,?,?,?,?,?)");

		for (int i = 0; i < rows; i++) {
			if (i == 0) {// 第一行是属性，不读取
				continue;
			}
			Cell ce0 = ((jxl.Sheet) sheet).getCell(0, i);
			Cell ce1 = ((jxl.Sheet) sheet).getCell(1, i);
			Cell ce2 = ((jxl.Sheet) sheet).getCell(2, i);
			Cell ce3 = ((jxl.Sheet) sheet).getCell(3, i);
			Cell ce4 = ((jxl.Sheet) sheet).getCell(4, i);
			Cell ce5 = ((jxl.Sheet) sheet).getCell(5, i);

			String c0 = ce0.getContents();
			String c1 = ce1.getContents();
			String c2 = ce2.getContents();
			String c3 = ce3.getContents();
			String c4 = ce4.getContents();
			String c5 = ce5.getContents();

			try {
				conn = getdanyiCon();
				ps = conn.prepareStatement(buffer.toString());
				ps.setString(1, UUID.randomUUID().toString());
				ps.setString(2, c0);
				ps.setString(3, c1);
				ps.setString(4, c2);
				ps.setString(5, c3);
				ps.setString(6, c4);
				ps.setString(7, c5);

				result = ps.executeUpdate();

			} catch (SQLException e) {
				System.out.println("sql:" + buffer);
				logger.error(e.getMessage(), e);
			} finally {

			}
		}
		DBHelper.Close(rs, ps, conn);
		return result;
	}