将xls中的数据写入数据库
<script> function callUpload() { var fileValue = document.FileUpload.file1.value; if (fileValue == "") { alert("请选择所需上传的文件！"); return false; } showLayer.style.display = "inline"; //count() FileUpload.submit(); }</script><body><formname="FileUpload" method="post" action="uploads" enctype="multipart/form-data" > <table border="0"> <tr> <td nowrap> <div align="left">导入的EXCEL文件(导入的明细会复盖原有明细数据)：</div> </td> </tr> <tr> <td nowrap> <input type="file" class="mybutton" name="file1" size="50" style="border-style: solid; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px"> <input type="submit" class="mybutton" value="导入" name="shangchuan"style="border-style: solid; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px" > </td> </tr> </table> <table width="100%"> <tr> <td> <hr width="100%"> </td> </tr> </table> </form></body>protected void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException { final long MAX_SIZE = 3 * 1024 * 1024;// 设置上传文件最大为 3MStringu_name=""; // 允许上传的文件格式的列表final String[] allowedExt = new String[] { "xls", "jpeg", "gif", "txt", "doc", "docx", "mp3", "wma" };response.setContentType("text/html"); // 设置字符编码为UTF-8, 这样支持汉字显示response.setCharacterEncoding("GBK");//实例化RequestContext对象RequestContext requestContext = new ServletRequestContext(request); if(FileUpload.isMultipartContent(requestContext)){} // 实例化一个硬盘文件工厂,用来配置上传组件ServletFileUploadDiskFileItemFactory dfif = new DiskFileItemFactory(); //上传文件胡原始路径 Stringrealpath = this.getServletContext().getRealPath("/")+"ImagesUploadTemp" ; // 设置存放临时文件的目录dfif.setRepository(new File(realpath));dfif.setSizeThreshold(4096);// 设置上传文件时用于临时存放文件的内存大小,这里是4K.多于的部分将临时存在硬盘 // 用以上工厂实例化上传组件ServletFileUpload sfu = new ServletFileUpload(dfif);System.err.println(" reapath="+this.getServletContext().getRealPath("/")+"ImagesUploadTemp"); // 设置最大上传尺寸sfu.setSizeMax(MAX_SIZE); PrintWriter out = response.getWriter();// 从request得到 所有 上传域的列表List fileList =null; try { fileList = sfu.parseRequest(request); } catch (FileUploadException e) {// 处理文件尺寸过大异常 e.printStackTrace(); if (e instanceof SizeLimitExceededException) { out.println("文件尺寸超过规定大小:" + MAX_SIZE + "字节<p />"); out.println("<a href=\"excelInsert.action\" target=\"_top\">返回</a>"); return; } //e.printStackTrace(); } // 没有文件上传 if (fileList == null || fileList.size() == 0) { out.println("文件大小不能为空，请选择上传文件<p />"); out.println("<a href=\"excelInsert.action\" target=\"_top\">返回</a>"); return; } // 得到所有上传的文件 Iterator fileItr = fileList.iterator(); // 循环处理所有文件 while (fileItr.hasNext()) { FileItem fileItem = null; String path = null; long size = 0; // 得到当前文件 fileItem = (FileItem) fileItr.next(); // 忽略简单form字段而不是上传域的文件域(<input type="text" />等) if (fileItem == null || fileItem.isFormField()) { continue; } // 得到文件的完整路径 path = fileItem.getName(); path = new String(path.getBytes("ISO-8859-1"),"UTF-8"); System.out.println("完整路径="+path); // 得到文件的大小 size = fileItem.getSize(); if ("".equals(path) || size == 0) { out.println("请选择上传文件<p />"); out.println("<a href=\"excelInsert.action\" target=\"_top\">返回</a>"); return; } // 得到去除路径的文件名 String t_name = path.substring(path.lastIndexOf("\\") + 1); // 得到文件的扩展名(无扩展名时将得到全名) String t_ext = t_name.substring(t_name.lastIndexOf(".") + 1); // 拒绝接受规定文件格式之外的文件类型 int allowFlag = 0; int allowedExtCount = allowedExt.length; for (; allowFlag < allowedExtCount; allowFlag++) { if (allowedExt.equals(t_ext)) break; } if (allowFlag == allowedExtCount) { out.println("请上传以下类型的文件<p />"); for (allowFlag = 0; allowFlag < allowedExtCount; allowFlag++) out.println("*." + allowedExt + "&nbsp;&nbsp;&nbsp;"); out.println("<p /><a href=\"excelInsert.action\" target=\"_top\">返回</a>"); return; } long now = System.currentTimeMillis(); // 根据系统时间生成上传后保存的文件名 String prefix = String.valueOf(now); // 保存的最终文件完整路径,保存在web根目录下的ImagesUploaded目录下 u_name = this.getServletContext().getRealPath("/")+"ImagesUploaded"+"\\" + prefix + "." + t_ext; System.out.println("文件上传后的路径！u_name="+u_name); try { // 保存文件 fileItem.write(new File(u_name)); out.println("文件上传成功. 已保存为: " + prefix + "." + t_ext + " &nbsp;&nbsp;文件大小: " + size + "字节<p />"); out.println("<a href=\"excelInsert.action\" target=\"_top\">继续上传</a>"); } catch (Exception e) { e.printStackTrace(); } } u_name = u_name.replace("\\", "/"); System.out.println("u_name =="+u_name); insert(u_name,request,response); /******************这个你把它去掉*****************/ //主要是为了导入成功以后，将它显示到页面上的方法 List list_lxmc = exam.getAllStlxMc(); for(int i=0;i<list_lxmc.size();i++){//System.out.println("类型名称"+list_lxmc.size()+";"+list_lxmc.get(i)); } request.setAttribute("list_lxmc", list_lxmc); List list_allSel = exam.getSelThemeTypeNamesobr_ones();// System.out.println(list_allSel+".............."); request.setAttribute("list_allSel", list_allSel); /***************************/ request.getRequestDispatcher("/admin/thememanage/excelInsert.jsp").forward(request, response);}public void insert(String path,HttpServletRequest request,HttpServletResponse response){try{ //////////// 读取 u_name 文件 并保存/////////// System.out.println("开始时间="+new Date()); java.io.File file = new java.io.File(path);java.io.InputStream inStream = new java.io.FileInputStream(file);HSSFWorkbook wb = new HSSFWorkbook(inStream);//int sheetNum = wb.getNumberOfSheets();int sheetNum = 1; ThemeBean tb = null;ArrayList list = new ArrayList();//为了获得32随机数主键UUIDGenerator uuid=new UUIDGenerator();for (int i = 0; i < sheetNum; i++){HSSFSheet childSheet = wb.getSheetAt(i);int rowNum = childSheet.getLastRowNum() + 1;//HSSFRow rowline = childSheet.getRow(rowNum); //这里我取不到列数的，所以将它写死了int cellNum =6; //System.out.println("列数"+rowline.getLastCellNum());for (int j = 2; j < rowNum; j++){tb = new ThemeBean();HSSFRow row = childSheet.getRow(j); if (row == null)continue; String value = "";for (int k = 0; k < cellNum; k++){ HSSFCell cell = row.getCell((short) k);//System.out.println(cell.getCellType()+"===type");if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING){continue;} //循环取到xls中的值value = String.valueOf(cell.getStringCellValue().trim());//System.out.println("value="+a);if (value == null)value = "";//对取到值 中有特殊符号进行处理（这个方法可选）value = value.replace("'", "");value = value.replace("‘", "");value = value.replace("’", "");switch(k){case 0: tb.setSTTM(value); case 1: tb.setSTKSX1(value);case 2: tb.setSTKSX2(value);case 3: tb.setSTKSX3(value); case 4: tb.setSTKSX4(value); case 5: tb.setRIGHTRESULT(value); } }tb.setSTZT("未使用"); tb.setBZ("备注就省略了");tb.setSTXXID(uuid.getNextValue("STXXID"));tb.setSTBH(uuid.getNextValue("STBH"));//将值放到对象中在 放到list中,为了方便插入数据库list.add(tb); }}try{ThemeAction action= new ThemeAction();//判断这个xls有没有被从复导入String ret = action.upload(list); if(ret!="ok"){request.setAttribute("excel", ret);}System.out.println("结束时间="+new Date()); //我有4000多条数据大概花了1分钟左右的，读xls只花了2秒钟的，插入数据库时间要多点，可能是我的方法写的不好，还请理解，你们也可以用自己更好的方法来代替 }catch(Exception ex){ex.printStackTrace();System.out.println("有异常的");return;}//关闭文件流inStream.close(); //删除临时文件file.delete();}catch(Exception e){e.printStackTrace();}}
页:
[1]