页面读取EXCEL表并将数据导入到Oracle（DWR异步机制，iframe页面嵌套）

现在在做的一个小型项目，目的为了实现js导入并上传excel到数据库的过程（功能简易，并没有考虑复杂的校验，用的POI实现）

struts配置文件（使用页面嵌套的方式）：

<action path="/query*" type="*.ActionZcgl" name="Form*" parameter="query*" scope="request"><forward name="info" path="/zcgl/*Info.jsp" />--功能按钮区域jsp<forward name="list" path="/zcgl/*List.jsp" />--查询结果显示区域jsp</action><action path="/save*" type="*.ActionZcgl" name="Form*" parameter="save*" scope="request"><forward name="success" path="/zcgl/*Info.jsp" /></action>

*Info.jsp：页面采用DWR的异步机制避免文件上传时用户的误操作

--引入DWR

<script type='text/javascript' src='<%=path%>/dwr/engine.js'></script><script type='text/javascript' src='<%=path%>/dwr/util.js'></script>

<form action="save*.do" target="listPage" method="post" id="fileDocUploadForm" enctype="multipart/form-data">

<td colspan="2" height="40" id="btnsc">    <input type="button" name="queryBut" value="查 询" class="blueTr9"  onclick="select_curve('<%=path %>','query*.do')">       <input class="blueTr9"  id="btnSubmit" type="button" height="35" value=" 上 传 "  onclick="fildDocUpload(1,this);" />    <input class="blueTr9"  id="btnCancle" type="button" height="35" value=" 取 消 "  onclick="fildDocUpload(3);" /><span id="submitMsg" style="color: red;"></span></td>

<div align="center">  --页面嵌套--
    <iframe id="listPageId" name="listPage" src="<%=path%>/query*.do?listPage=1" frameborder="0" scrolling="anto" width="100%" 
 onload="height = document.frames(this.name).document.body.scrollHeight" ></iframe>
</div>
方法区：
//文件转移或复制页面和上传页面控制方法function fildDocUpload(flag,butObj){var form = jQuery("#fileDocUploadForm"), selectFile = form.find(":file");if(flag==1){var upload_path = jQuery("input[name='file']").val();var upload = upload_path.lastIndexOf(".");var upload_type = upload_path.substring(upload, upload_path.length).toUpperCase();var isOK = true;if(upload_type =="" ){//判断文件是否存在isOK=false;alert("请选择正确文件路径！")return false;}else if(upload_type != ".XLS"){//判断格式alert("请选择.xls文件")return false;} else {DWREngine.setAsync(false);form.submit();DWREngine.setAsync(true);}} else if(flag==3) {returnValue="false";window.close();}else{var form = jQuery("#fileDocUploadForm");form.submit();}}//获取日期function getRadioObjValue(obj_name){var str = "";jQuery("#tabhead1").find("input[type=radio][name='"+obj_name+"']").each(function(){if(jQuery(this).attr("checked")){str = jQuery(this).val();}});return str;}function select_curve(path,action_value){//新增查询var form1 = document.frames("listPage").document.forms[0];var form0 = document.forms[0];form1.d_date.value = getRadioObjValue("d_date");   form1.target="listPage";form1.action=path + "/"+action_value+"?listPage=1";form1.submit();}
*LIst.jsp:

<form id="form1" action="<%=path%>/*.do?listPage=1" target="listPage" method="post">       <input type="hidden" name="d_date" value="${frm.d_date}" />      <input type="hidden" name="pageNo" value="${frm.pageNo}" />    <input type="hidden" name="rowsPerPage" value="${frm.rowsPerPage}" />

Action部分：

查询及初始化

//获取数据日期List listdataDate = new CommonMethod().queryDateDataList();request.setAttribute("listdataDate", listdataDate);//注释掉页面会没有日期FormZcglcurve frm = (FormZcglcurve) form;        FormUserOperation userOp = Constant.getFormUserOperation(request);if (frm.getRowsPerPage() == 0) {frm.setRowsPerPage(20);}// 判断是到列表页面还是查询页面if (request.getParameter("listPage") != null&& request.getParameter("listPage").equals("1")) {if(frm.getD_date() == null || "".equals(frm.getD_date() )){if(listdataDate != null && listdataDate.size() > 0){Map map =(Map) listdataDate.get(0);String d_date = (String) map.get("D_DATE");frm.setD_date(d_date);}else{String last_month_date=CommonMethod.priorMonthEnd();frm.setD_date(last_month_date);}}// 执行查询列表List<FormZcglcurve> resultList = getBO().queryYieldCurve(frm);request.setAttribute("resultList", resultList);request.setAttribute("frm", frm);return mapping.findForward("list");}else{request.setAttribute("frm", frm);return mapping.findForward("info");}

上传方法

FormZcglcurve frm = (FormZcglcurve)form;Workbook workbook = Workbook.getWorkbook(frm.getFile().getInputStream(), new WorkbookSettings());String d_date = frm.getD_date();// 判断数据导入是否成功.0:成功,1:无数据,2:插入数据库失败int impFlag = 0;String msg = null;try{// 导入数据impFlag = getBO().saveCurveDataByExcel(workbook,form);}catch (Exception e) {impFlag = 2;e.printStackTrace();}workbook.close();switch(impFlag) {case 0 : msg = "导入完毕，数据成功导入！";break;case 1 : msg = "当前excel数据文件中没有可以导入的数据，请先填充文件。";break;case 2 : msg = "当前估值数据导入失败，请检查数据是否正确或者已经存在。";break;}showMessage(response, msg);return null;

DAO层：
public int save*(Workbook workbook,ActionForm form)  //传值的时候要将excel和页面所选的日期都要传过来

上传保存

Sheet  ETSheet= workbook.getSheet(0);//getCell(列，行)int rows=ETSheet.getRows();System.out.println(rows);int columns=ETSheet.getColumns();System.out.println(columns);FormZcglcurve frm=(FormZcglcurve) form;String d_date=frm.getD_date();  //将操作日期更改无论之前日期数据是否存在// 判断数据导入是否成功.0:成功,1:无数据,2:插入数据库失败int flag = 0;if(rows < 2) {// 当前xls表中没有数据System.out.println("当前xls表中没有数据");flag = 1;return flag;}// 需执行的sql列表String sql =  "";// 获取数据，并拼接成sql语句for(int i=1; i<rows; i++) {//默认格式含有表头 ，操作时将表头行舍弃if("".equals(ETSheet.getCell(0,i).getContents().trim())){break;}//取excel表的String curve_name = ETSheet.getCell(0,i).getContents();String s_year_limit = ETSheet.getCell(1,i).getContents();String average_value = ETSheet.getCell(2, i).getContents();// 导入sqljdbcTemplate.update("insert INTO *** (curve_name,s_year_limit,average_value,d_date) "+ " values (?,?,?,to_date ('"+d_date+"','yyyy-MM-dd'))", new Object[] {ETSheet.getCell(0, i).getContents(),ETSheet.getCell(1, i).getContents(),ETSheet.getCell(2, i).getContents()});}return flag;

查询

StringBuffer sql = new StringBuffer();String d_date=form.getD_date();sql.append(" select * from *** t where d_date = to_date ('"+d_date+"','yyyy-MM-dd')");//为对象分页赋总值int rowsCount = jdbcTemplate.queryForInt("select count(1) from ("+sql.toString()+") ");form.setRowsCount(rowsCount);return (List<FormZcglcurve>) jdbcTemplate.queryForListPage(sql.toString(), form.getStartPosition(),form.getRowsPerPage(), FormZcglcurve.class);

封装excel读取的类