excel数据导入SQLserver，导入后有比较友好的提示
<s:form action="" method="post" name="importForm" enctype="multipart/form-data"><table width="100%" border="0" align="center" cellpadding="0" cellspacing="1" align="right" width="40%"><font color="red">*</font>请选择导入文件:</td><td align="left"><input name="filePath" type="file" size="30"><input type="button" name="Submit" value="导入数据" onClick="onSubmit();"></td></tr><s:if test="#request.results!=null"><tr><td colspan="2">成功插入的记录的条数：<s:property value="#request.results.get(0)" />条</td></tr><tr><td colspan="2">excel中非法的记录的条数：<s:property value="#request.results.get(1)" />条</td></tr><s:if test="#request.illegalNotes!=null"><tr><td colspan="2">不合法的记录的字段如下所示：<s:iterator value="#request.illegalNotes" status="listStat"><li><s:property /></li></s:iterator></td></tr></s:if> </s:if></table></s:form>
public String importCarModi()throws SystemException{HttpServletRequest request = ServletActionContext.getRequest (); List<Integer> results = new ArrayList<Integer>(); List<String> illegalNotes = new ArrayList<String>();//非法的字段的错误的提示 Map session = ActionContext.getContext().getSession();String operatorCode = (String)session.get(SysConstant.OPERATOR_ID);log.info("filePath="+filePath);if (filePath == null){request.setAttribute("tip", "请选择导入的excle的路径");return Constants.Dispatcher.TO_IMPORT_DATA;}try {carInsModificationFacade.importCarModi(filePath,results,illegalNotes,operatorCode); request.setAttribute("results", results);if (results.get(1)!=0){request.setAttribute("illegalNotes", illegalNotes);}return Constants.Dispatcher.TO_IMPORT_DATA;} catch (FacadeException e) { e.printStackTrace();throw new SystemException("导入非车险批单信息失败!e.message="+ e.getMessage());}} public void importCarModi(String filePath,List<Integer> results,List<String> illegalNotes,String operatorCode) throws FacadeException{ log.info(">>>>>>>>>>根据用户选定的filepath="+filePath+"导入excel文件到数据库开始......"); int legalPolicyNum = 0; int illegalPolicyNum = 0; try{ InputStream ExcelResource = new FileInputStream(filePath);Workbook ExcelWB = Workbook.getWorkbook(ExcelResource);Sheet ExcelRS = ExcelWB.getSheet(0);int rows = ExcelRS.getRows(); if (rows > 0) {for (int i = 1; i < rows; i++) {// 注意要从第二行开始读取excel中的值List<String> illegalStr = new ArrayList<String>();CarInsModificationInfo modification = new CarInsModificationInfo(); String modificationCode = ExcelRS.getCell(1, i).getContents();//批单号//log.info("ModificationCode="+ModificationCode);if (!StringUtils.isBlank(modificationCode)){String carRegEx = "\\w{11}(19|20)\\d{2}P\\d{6}";boolean isCarIns = Pattern.compile(carRegEx,Pattern.CASE_INSENSITIVE).matcher(modificationCode).find();if (isCarIns) {modification.setModificationCode(modificationCode);if (illegalStr.size()==0) {//批单号合法,则开始检查该批单号在数据库中是否存在if (carInsModificationDAO.checkIsExist("CarInsModificationInfo", "modificationCode", modificationCode)) {illegalStr.add("第"+i+"条记录的批单号"+modificationCode+"在数据库中已经存在"); }}} else {illegalStr.add("第"+i+"条记录的批单号:"+modificationCode+"不符合车险批单的规则！");}}else{illegalStr.add("第"+i+"条记录的批单号为空,数据库中该字段的为主键，不能为空！");}String policyCode = ExcelRS.getCell(0, i).getContents();//保单号//检查该保单号在数据库中是否已经存在boolean isExist = carInsModificationDAO.checkIsExist("CarInsPolicy", "policyCode", policyCode);if (StringUtils.isBlank(policyCode)){ illegalStr.add("第"+i+"条记录中的保单号为空，数据库中该字段不能为空！"); }if (!isExist){illegalStr.add("第"+i+"条记录中的保单号为:"+policyCode+"在保单表中不存在！"); }if (!StringUtils.isBlank(policyCode) && isExist) {modification.setPolicyCode(policyCode);}if (!StringUtils.isBlank(ExcelRS.getCell(2, i).getContents()) && illegalStr.size() == 0){String mstateCode = ExcelRS.getCell(2, i).getContents().substring(1,2);//取出批单状态代码modification.setMstateCode(mstateCode);} if (!StringUtils.isBlank(ExcelRS.getCell(3, i).getContents())){String mtypeCode = ExcelRS.getCell(3, i).getContents().substring(1,3);//取出批改类型代码//要检查这个代码在这个代码表中是否存在if(!carInsModificationDAO.codeIsExist("CmodiType",mtypeCode)){illegalStr.add("第"+i+"条记录中的批改类型的代码，在代码表中不存在！");}else{//因为建立了关联，所以要以对象的形式插入CmodiType cmodiType = new CmodiType();cmodiType.setCode(mtypeCode);modification.setCmodiType(cmodiType);}} if (!StringUtils.isBlank(ExcelRS.getCell(4, i).getContents()) && illegalStr.size() == 0){Double insChange = Double.parseDouble(ExcelRS.getCell(4, i).getContents()); //保费变化modification.setInsChange(insChange);} if (!StringUtils.isBlank(ExcelRS.getCell(5, i).getContents()) && illegalStr.size() == 0){Date modifyTime = DateFormater.parseStr2DateTime(ExcelRS.getCell(5, i).getContents(),DateFormater.YY_MM_DD);//签单日期modification.setModifyTime(modifyTime);} if (illegalStr.size() != 0){//如果不为空，则说明该记录中有不规范的字段存在，将其记入非法保单数组 illegalPolicyNum++;illegalNotes.addAll(illegalStr); //将该记录的非法字段的提示记入非法提示字符串}else{//如果为空则说明该记录中的所有的字段都是合法的，将其记入合法的保单对象数组legalPolicyNum++;modification.setMstateCode("5");//将要可以导入数据库的数据的状态设为有效 modification.setCreateTime(new Date()); //导入数据的时间和人即为创建记录的时间和人 modification.setOperatorCode(operatorCode); carInsModificationDAO.saveEntity(modification);}}results.add(legalPolicyNum); //成功插入的记录的条数results.add(illegalPolicyNum);//非法的记录的条数 } }catch (Exception e){throw new FacadeException("取出excel数据失败"+e);} log.info(">>>>>>>>>>根据用户选定的filepath="+filePath+"导入excel文件到数据库完成......"); }