java中使用excel导入数据

<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">前端代码：</span>

这里是导入设备数据，然后进行处理；要注意的是 form中enctype的属性值

          <form id="import_form" class="form-horizontal" action="<%=basePath %>revertDevice/readSN" method="post" enctype="multipart/form-data">            <div class="form-group">              <label class="col-sm-3 control-label">EXCEL批量归还设备：</label>              <div class="col-sm-9">                <input type="file" name="file" id="file" data-popover-offset="0,8" required class="form-control" style="width:500px;">              </div>            </div>           <div class="form-group">              <div class="col-sm-6 col-sm-offset-3">                <div class="btn-toolbar">                  <button type="submit" class="btn btn-primary">归还</button>                  <button type="button" onclick="javascript:history.go(-1);"class="btn btn-default">返回</button>                </div>              </div>            </div>          </form>

这里是另外一个excel提交，上面代码中的 revertDevice/readSN涉及到很多业务方面的东西没贴出来，这个也一样可获取到excel的内容，这里只做备忘，具体看自己的代码库

@RequestMapping(value = "/startInsert", method = RequestMethod.POST)public String insert(@RequestParam("file") MultipartFile file, HttpServletRequest req, HttpServletResponse resp, HttpSession session, Model model) throws SQLException, FileNotFoundException, IOException, InterruptedException{System.out.println("得到上传excel请求");AdminUserInfo adminUserInfo = (AdminUserInfo) getSession().getAttribute("User");if (adminUserInfo == null){req.getSession().setAttribute("excelmessage", "请重新登录！");return "redirect:insert";}MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req;SimpleDateFormat dateformat = new SimpleDateFormat("yyyy/MM/dd/HH");String logoPathDir = "/files" + dateformat.format(new Date());// 得到excel保存目录的真实路径String temp = request.getSession().getServletContext().getRealPath(logoPathDir);// 创建文件保存路径文件夹File tempFile = new File(temp);System.out.println("这个是上传文件的真实路径temp：" + temp);MultipartFile multipartFile = multipartRequest.getFile("file");if (!tempFile.exists()){tempFile.mkdirs();}// 构建文件名称String logExcelName = multipartFile.getOriginalFilename();String fileName = temp + File.separator + logExcelName;File files = new File(fileName);try{multipartFile.transferTo(files);}catch (IllegalStateException e){e.printStackTrace();req.getSession().setAttribute("excelmessage", "上传出错！请重试或联系管理员");return "redirect:insert";}catch (IOException e){e.printStackTrace();req.getSession().setAttribute("excelmessage", "上传出错！请重试或联系管理员");return "redirect:insert";}System.out.println("上传完毕.");SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss:SS");TimeZone t = sdf.getTimeZone();t.setRawOffset(0);sdf.setTimeZone(t);Long startTime = System.currentTimeMillis();String[][] result = ExcelUtils.getData(files, 1);int allDatacount = result.length;if (allDatacount == 0){req.getSession().setAttribute("excelmessage", "输入的表格除标题外无数据, 或错误去掉了标题. 请按要求只填充数据.(序号不能为空！)");return "redirect:insert";}int keyEmptyCount = 0;int requiredInvalidCount = 0;int insertFailedCount = 0;int insertOKCount = 0;int updateOKCount = 0;List<String> keyEmptyIDs = new ArrayList<String>();List<String> requiredInvalidIDs = new ArrayList<String>();List<String> insertFailedIDs = new ArrayList<String>();Connection conn = null;PreparedStatement pst = null;PreparedStatement pstupdate = null;try{conn = getDBConn();}catch (ClassNotFoundException e1){e1.printStackTrace();req.getSession().setAttribute("excelmessage", "无法连接数据库！请重试或联系管理员");return "redirect:insert";}try{pst = (PreparedStatement) conn.prepareStatement("insert into DeviceInfo(SN,CardID,deviceColour,distributorName,remark,"+ "supportCountry,modelNumber,frequencyRange,repertoryStatus,"+ "deviceID,creatorUserID,creatorUserName,creatorDate)"+ " values (?,?,?,?,?,?,?,?,?,?,?,?,?)");SimpleDateFormat sdfdate = new SimpleDateFormat();sdfdate.applyPattern("yyyy-MM-dd HH:mm:ss");int pstIndex;for (int i = 0; i < allDatacount; i++){if (result[i][DeviceInfoImportConstants.COL_SN] == ""){keyEmptyCount++;keyEmptyIDs.add(result[i][0]);continue;}pstIndex = 0;pst.setString(++pstIndex, result[i][DeviceInfoImportConstants.COL_SN]);pst.setString(++pstIndex, result[i][DeviceInfoImportConstants.COL_CardID]);pst.setString(++pstIndex, result[i][DeviceInfoImportConstants.COL_deviceColour]);pst.setString(++pstIndex, result[i][DeviceInfoImportConstants.COL_distributorName]);pst.setString(++pstIndex, result[i][DeviceInfoImportConstants.COL_remark]);pst.setString(++pstIndex, result[i][DeviceInfoImportConstants.COL_supportCountry]);pst.setString(++pstIndex, result[i][DeviceInfoImportConstants.COL_modelNumber]);pst.setString(++pstIndex, result[i][DeviceInfoImportConstants.COL_frequencyRange]);pst.setString(++pstIndex, result[i][DeviceInfoImportConstants.COL_repertoryStatus]);pst.setString(++pstIndex, UUID.randomUUID().toString());pst.setString(++pstIndex, adminUserInfo.userID);pst.setString(++pstIndex, adminUserInfo.userName);pst.setString(++pstIndex, sdfdate.format(new Date()).toString());try{pst.execute();insertOKCount++;}catch (SQLException e){  if(e.getErrorCode()==1062){  String updatesql ="UPDATE DeviceInfo SET ";  String cardid="";String devicecolour="";String distributorname="";String remark="";  String supportcountry="";String modelnumber="";String frequencyrange="";String repertoryStatus = "";  String modifyUserID="modifyUserID='"+adminUserInfo.getUserID()+"',";  String modifyDate="modifyDate='"+sdfdate.format(new Date()).toString()+"'";  if(result[i][2].toString()!="")  cardid = "CardID='"+result[i][2].toString()+"',";  if(result[i][3].toString()!="")  devicecolour = "deviceColour='"+result[i][3].toString()+"',";  if(result[i][4].toString()!="")  distributorname = "distributorName='"+result[i][4].toString()+"',";  if(result[i][5].toString()!="")  remark = "remark='"+result[i][5].toString()+"',";  if(result[i][6].toString()!="")  supportcountry = "supportCountry='"+result[i][6].toString()+"',";  if(result[i][7].toString()!="")  modelnumber = "modelNumber='"+result[i][7].toString()+"',";  if(result[i][8].toString()!="")  frequencyrange = "frequencyRange='"+result[i][8].toString()+"',";  if(result[i][9].toString()!="")  repertoryStatus = "repertoryStatus='"+result[i][9].toString()+"',";  updatesql = updatesql+cardid+devicecolour+distributorname+remark+supportcountry+modelnumber+frequencyrange+repertoryStatus+modifyUserID+modifyDate+ " WHERE SN='"+result[i][1].toString()+"'";pstupdate = (PreparedStatement) conn.prepareStatement(updatesql);int puCount = 0;try {puCount = pstupdate.executeUpdate();if(puCount>0){updateOKCount++;//覆盖掉的总数insertOKCount++;//记录到总数Shipment shipm = null;shipm = DeviceLogShipment(result[i][1].toString(), "", "0", "", "", "入库", "新增设备", "");try {ShipmentSer.insert(shipm);//写入出入库记录} catch (Exception e1) {e1.printStackTrace();}}logger.info("需要覆盖的ＳＮ："+result[i][1].toString());} catch (Exception e1) {logger.info("覆盖出错！");e1.printStackTrace();insertFailedCount++;insertFailedIDs.add(result[i][0]);}  }else{e.printStackTrace();insertFailedCount++;insertFailedIDs.add(result[i][0]);  }}}AdminOperate admin = new AdminOperate();admin.setOperateID(UUID.randomUUID().toString());admin.setCreatorUserID(adminUserInfo.userID);admin.setCreatorUserName(adminUserInfo.userName);admin.setOperateContent(multipartFile.getOriginalFilename() + ", 批量导入设备, 成功导入 " + insertOKCount + " 条, 插入失败 " + insertFailedCount + " 条, 无效数据 " + keyEmptyCount + " 条, 原始记录总计 " + allDatacount + " 条."); // 操作内容admin.setOperateMenu("设备管理>批量导入设备");admin.setOperateType("新增");adminOperateSer.insertdata(admin);}catch (SQLException e){e.printStackTrace();req.getSession().setAttribute("excelmessage", "操作数据库出错！请重试或联系管理员");return "redirect:insert";}finally{if (pst != null){pst.close();pst = null;}if (conn != null){conn.close();pst = null;}}Long endTime = System.currentTimeMillis();String usetime = sdf.format(new Date(endTime - startTime));String requiredInvalidIDsString = "";String insertFailedIDsString = "";String keyEmptyIDsString = "";if (requiredInvalidCount > 0){Collections.sort(requiredInvalidIDs);requiredInvalidIDsString = "<br />关键字段缺失的记录序号: ";for (String id : requiredInvalidIDs){requiredInvalidIDsString += id + ", ";}}if (insertFailedCount > 0){Collections.sort(insertFailedIDs);insertFailedIDsString = "<br />插入失败的记录序号: ";for (String id : insertFailedIDs){insertFailedIDsString += id + ", ";}}if (keyEmptyCount > 0){Collections.sort(keyEmptyIDs);keyEmptyIDsString = "<br />无效数据的记录序号(缺少设备SN): ";for (String id : keyEmptyIDs){keyEmptyIDsString += id + ", ";}}requiredInvalidIDsString += keyEmptyIDsString + insertFailedIDsString;if (StringUtils.isNotBlank(requiredInvalidIDsString)){requiredInvalidIDsString = "<br />" + requiredInvalidIDsString;}req.getSession().setAttribute("excelmessage", "《" + multipartFile.getOriginalFilename() + "》<br /><br />成功导入 <b>" + insertOKCount + "</b> 条数据, 共用时：" + usetime+ "<br />原始记录总数：<b>" + allDatacount + "</b><br />无效数据条数：<b>" + keyEmptyCount + "</b><br />插入失败条数：<b>" + insertFailedCount + "</b>"+  "<br />被覆盖数据条数：<b>" + updateOKCount + "</b><br />"+requiredInvalidIDsString);System.out.println("用时：" + usetime);System.out.println("共导入" + allDatacount + "条数据！");return "redirect:insert";}