excel导入去重问题

hbyczg

有两个页面需要在EXCEl导入时去掉重复的记录，两个的代码基本一样，但一个能成功实现，另一个总报如下错误，求解。
null
[url=]隐藏详细[/url]
java.lang.reflect.InvocationTargetException at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at com.justep.system.action.Engine.invokeActions(Native Method) at com.justep.system.action.Engine.invokeActions(Unknown Source) at com.justep.business.server.BusinessServer.doAction(Unknown Source) at com.justep.business.server.BusinessServer.doExcute(Unknown Source) at com.justep.business.server.BusinessServer.excute(Unknown Source) at com.justep.business.server.BusinessServer.excute(Unknown Source) at com.justep.business.server.BusinessServerServlet.execService(Unknown Source) at com.justep.business.server.BusinessServerServlet.service(Unknown Source) at javax.servlet.http.HttpServlet.service(HttpServlet.java:723) at com.justep.x.bs.BusinessServerServlet.service(Unknown Source) at javax.servlet.http.HttpServlet.service(HttpServlet.java:723) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489) at java.lang.Thread.run(Thread.java:745) Caused by: java.lang.reflect.InvocationTargetException at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at com.justep.system.action.Engine.invokeAction(Native Method) at com.justep.system.action.Engine.invokeAction(Unknown Source) at com.justep.model.impl.UtilImplementInstance.invokeAction(Unknown Source) at com.justep.system.action.ActionUtils.invokeAction(Unknown Source) at com.justep.excel.ImportGenerator.generate(ImportGenerator.java:121) at Excel.importExcel(Excel.java:88) ... 27 more Caused by: java.lang.NullPointerException at CuostomerProcess.cuostomerProcessAfterImportExcelBeforeAction(CuostomerProcess.java:32) ... 37 more

关闭

hbyczg

控制台提示：
execl导入的数据:编码KH0186在当前Excel表中重复
execl导入的数据:编码KH0320在当前Excel表中重复
2016-12-21 11:31:22 excute stream action, params:{"accept":"application/json","action":"importExcel","actionFlag":"__action_0__","activity":"mainActivity","contentType":"json","executeContext":"","executor":"","parameters":{"config":{"@type":"xml","xml":"<config use-default=\"true\" to=\"DB\" excel-file=\"D:\\BeX5_V3.6\\apache-tomcat\\temp\\48041aa5-72d7-4d0e-8cad-ab5a4214e9da.excel.tmp\" mapping-file=\"D:\\BeX5_V3.6\\apache-tomcat\\temp\\39f6eef9-24f3-4c86-b688-386ac6967405.excel.tmp\"><data><concept name=\"AZ_partner\"><relation name=\"AZ_partner\" type=\"String\"/><relation name=\"calcCheckBox\" type=\"String\"/><relation name=\"fCounty\" type=\"String\"/><relation name=\"fCreatePsnFID\" type=\"String\"/><relation name=\"fCreatePsnFName\" type=\"String\"/><relation name=\"fOwner\" type=\"String\"/><relation name=\"fPostCode\" type=\"String\"/><relation name=\"fShortName\" type=\"String\"/><relation name=\"fbank\" type=\"String\"/><relation name=\"fbankAccount\" type=\"String\"/><relation name=\"fcityCode\" type=\"String\"/><relation name=\"fcustomerType\" type=\"String\"/><relation name=\"fenterprisetype\" type=\"String\"/><relation name=\"findustry\" type=\"String\"/><relation name=\"fintroduceBy\" type=\"String\"/><relation name=\"flnsize\" type=\"String\"/><relation name=\"fpartnerType\" type=\"String\"/><relation name=\"frating\" type=\"String\"/><relation name=\"fsource\" type=\"String\"/><relation name=\"fwebSiteUrl\" type=\"String\"/><relation name=\"sAddress\" type=\"String\"/><relation name=\"sCity\" type=\"String\"/><relation name=\"sCode\" type=\"String\"/><relation name=\"sCreatorDeptID\" type=\"String\"/><relation name=\"sCreatorDeptName\" type=\"String\"/><relation name=\"sCreatorOgnID\" type=\"String\"/><relation name=\"sCreatorOgnName\" type=\"String\"/><relation name=\"sCreatorPersonID\" type=\"String\"/><relation name=\"sCreatorPersonName\" type=\"String\"/><relation name=\"sDescription\" type=\"String\"/><relation name=\"sEmployeesSize\" type=\"Integer\"/><relation name=\"sFax\" type=\"String\"/><relation name=\"sLastWriteTime\" type=\"DateTime\"/><relation name=\"sLastWriterFID\" type=\"String\"/><relation name=\"sLastWriterName\" type=\"String\"/><relation name=\"sMail\" type=\"String\"/><relation name=\"sName\" type=\"String\"/><relation name=\"sPhone\" type=\"String\"/><relation name=\"sProvince\" type=\"String\"/><relation name=\"version\" type=\"Integer\"/></concept></data></config>"},"createActionParam":{"action":"createAZ_partnerAction","activity":"mainActivity","parameters":{"table":{"@type":"table","rows":[],"userdata":{"idColumnDefine":"AZ_partner","idColumnName":"AZ_partner","idColumnType":"String","model":"/AOZESYSTEM/CRM/data","relationAlias":"fCounty,fCreatePsnFID,fCreatePsnFName,fOwner,fPostCode,fShortName,fbank,fbankAccount,fcityCode,fcustomerType,fenterprisetype,findustry,fintroduceBy,flnsize,fpartnerType,frating,fsource,fwebSiteUrl,sAddress,sCity,sCode,sCreatorDeptID,sCreatorDeptName,sCreatorOgnID,sCreatorOgnName,sCreatorPersonID,sCreatorPersonName,sDescription,sEmployeesSize,sFax,sLastWriteTime,sLastWriterFID,sLastWriterName,sMail,sName,sPhone,sProvince,version","relationTypes":"String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,Integer,String,DateTime,String,String,String,String,String,String,Integer","relations":"AZ_partner.fCounty,AZ_partner.fCreatePsnFID,AZ_partner.fCreatePsnFName,AZ_partner.fOwner,AZ_partner.fPostCode,AZ_partner.fShortName,AZ_partner.fbank,AZ_partner.fbankAccount,AZ_partner.fcityCode,AZ_partner.fcustomerType,AZ_partner.fenterprisetype,AZ_partner.findustry,AZ_partner.fintroduceBy,AZ_partner.flnsize,AZ_partner.fpartnerType,AZ_partner.frating,AZ_partner.fsource,AZ_partner.fwebSiteUrl,AZ_partner.sAddress,AZ_partner.sCity,AZ_partner.sCode,AZ_partner.sCreatorDeptID,AZ_partner.sCreatorDeptName,AZ_partner.sCreatorOgnID,AZ_partner.sCreatorOgnName,AZ_partner.sCreatorPersonID,AZ_partner.sCreatorPersonName,AZ_partner.sDescription,AZ_partner.sEmployeesSize,AZ_partner.sFax,AZ_partner.sLastWriteTime,AZ_partner.sLastWriterFID,AZ_partner.sLastWriterName,AZ_partner.sMail,AZ_partner.sName,AZ_partner.sPhone,AZ_partner.sProvince,AZ_partner.version","updateMode":"whereVersion"}}},"process":"/AOZESYSTEM/CRM/process/cuostomer/cuostomerProcess"},"saveActionParam":{"action":"saveAZ_partnerAction","activity":"mainActivity","parameters":{"table":{"@type":"table","rows":[],"userdata":{"idColumnDefine":"AZ_partner","idColumnName":"AZ_partner","idColumnType":"String","model":"/AOZESYSTEM/CRM/data","relationAlias":"fCounty,fCreatePsnFID,fCreatePsnFName,fOwner,fPostCode,fShortName,fbank,fbankAccount,fcityCode,fcustomerType,fenterprisetype,findustry,fintroduceBy,flnsize,fpartnerType,frating,fsource,fwebSiteUrl,sAddress,sCity,sCode,sCreatorDeptID,sCreatorDeptName,sCreatorOgnID,sCreatorOgnName,sCreatorPersonID,sCreatorPersonName,sDescription,sEmployeesSize,sFax,sLastWriteTime,sLastWriterFID,sLastWriterName,sMail,sName,sPhone,sProvince,version","relationTypes":"String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,Integer,String,DateTime,String,String,String,String,String,String,Integer","relations":"AZ_partner.fCounty,AZ_partner.fCreatePsnFID,AZ_partner.fCreatePsnFName,AZ_partner.fOwner,AZ_partner.fPostCode,AZ_partner.fShortName,AZ_partner.fbank,AZ_partner.fbankAccount,AZ_partner.fcityCode,AZ_partner.fcustomerType,AZ_partner.fenterprisetype,AZ_partner.findustry,AZ_partner.fintroduceBy,AZ_partner.flnsize,AZ_partner.fpartnerType,AZ_partner.frating,AZ_partner.fsource,AZ_partner.fwebSiteUrl,AZ_partner.sAddress,AZ_partner.sCity,AZ_partner.sCode,AZ_partner.sCreatorDeptID,AZ_partner.sCreatorDeptName,AZ_partner.sCreatorOgnID,AZ_partner.sCreatorOgnName,AZ_partner.sCreatorPersonID,AZ_partner.sCreatorPersonName,AZ_partner.sDescription,AZ_partner.sEmployeesSize,AZ_partner.sFax,AZ_partner.sLastWriteTime,AZ_partner.sLastWriterFID,AZ_partner.sLastWriterName,AZ_partner.sMail,AZ_partner.sName,AZ_partner.sPhone,AZ_partner.sProvince,AZ_partner.version","sys.count":0,"sys.rowid":"AZ_partner","updateMode":"whereVersion"}}},"process":"/AOZESYSTEM/CRM/process/cuostomer/cuostomerProcess"}},"process":"/AOZESYSTEM/CRM/process/cuostomer/cuostomerProcess"}
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at com.justep.system.action.Engine.invokeActions(Native Method)
        at com.justep.system.action.Engine.invokeActions(Unknown Source)
        at com.justep.business.server.BusinessServer.doAction(Unknown Source)
        at com.justep.business.server.BusinessServer.doExcute(Unknown Source)
        at com.justep.business.server.BusinessServer.excute(Unknown Source)
        at com.justep.business.server.BusinessServer.excute(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.execService(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
        at com.justep.x.bs.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at com.justep.system.action.Engine.invokeAction(Native Method)
        at com.justep.system.action.Engine.invokeAction(Unknown Source)
        at com.justep.model.impl.UtilImplementInstance.invokeAction(Unknown Source)
        at com.justep.system.action.ActionUtils.invokeAction(Unknown Source)
        at com.justep.excel.ImportGenerator.generate(ImportGenerator.java:121)
        at Excel.importExcel(Excel.java:88)
        ... 27 more
Caused by: java.lang.NullPointerException
        at CuostomerProcess.cuostomerProcessAfterImportExcelBeforeAction(CuostomerProcess.java:32)
        ... 37 more

hbyczg

1.         public static void cuostomerProcessAfterImportExcelBeforeAction() {
   
2.                           //  System.out.println("导入前");
   
3.                 Workbook workBook=(Workbook) ModelUtils.getRequestContext().getActionContext().getParameter("excel");
   
4.                 Sheet sheet=workBook.getSheetAt(0);
   
5.                 List list=new ArrayList();//存放excel鉴别列的数据
   
6.                 for(int i=1;i<=sheet.getLastRowNum();i++){
   
7.                         Row row=sheet.getRow(i);
   
8.                         Cell cell=row.getCell(0);//根据excel表中第一列数据进行校验
   
9.                         Cell sName = row.getCell(3);
   
10.                         if(list.contains(cell.getStringCellValue())){
    
11.                                  System.out.println("execl导入的数据:编码"+cell + "在当前Excel表中重复");
    
12.                               
    
13.                                 row.removeCell(cell);//如果list存在和鉴别列相同的数据就删除
    
14.                         }
    
15.                         
    
16.                          String ksql = "select a.sCode , a.sName from AZ_partner a where a.sCode = '"+cell.getStringCellValue()+"' and a.sName = '"+sName.getStringCellValue()+"' ";
    
17.                         // System.out.print(ksql + "execl导入的数据");
    
18.                          Table table = KSQL.select(ksql, null, "/AOZESYSTEM/CRM/data", null);
    
19.                         Iterator it = table.iterator();
    
20.                         if (it.hasNext()) {
    
21.                                  System.out.println("execl导入的数据:编码/版本"+ cell+"/"+sName + "在当前数据库中重复");
    
22.                                 row.removeCell(row.getCell(0));
    
23.                         }else{
    
24.                                 list.add(cell.getStringCellValue());//如果不存在就添加到list
    
25.                         }
    
26.               }  
    
27.         }

复制代码

以上是inportExcelBeforeAction的执行后事件代码。如果不执行上面去重是能导入的，映射XML应没问题。另一个功能的代码与这个一样，只是其中的变量名和列名等有所变化，却能顺利执行。这个不知道错在那里

jishuang

java.lang.NullPointerException at CuostomerProcess.cuostomerProcessAfterImportExcelBeforeAction(CuostomerProcess.java:32)

java代码调试跟一下，空指针了

hbyczg

jishuang 发表于 2016-12-21 14:20
java.lang.NullPointerException at CuostomerProcess.cuostomerProcessAfterImportExcelBeforeAction(Cuos ...

这里的代码调试应怎么做呢？

hbyczg

jishuang 发表于 2016-12-21 14:20
java.lang.NullPointerException at CuostomerProcess.cuostomerProcessAfterImportExcelBeforeAction(Cuos ...

开了调试了，看不懂，能不能远程帮看一下

jishuang

报空指针了，自己加一下是否为空的判断看看啊
这个就是java的标准报错

hbyczg

jishuang 发表于 2016-12-21 17:39
报空指针了，自己加一下是否为空的判断看看啊
这个就是java的标准报错

谢谢结帖，把EXCEL立文件重新做一下就可以导入成功了。

zcgame

hbyczg 发表于 2016-12-21 18:27
谢谢结帖，把EXCEL立文件重新做一下就可以导入成功了。

怎么做到的？