Discussion

reading data from a excel sheet

How to read the data from an excel sheet(apart from file Listner) uploded by the user, and also use these data to populate in a list for further operations..

I've gone through this forum searching the same but cant find the relavent posts solving my issue ...unnable to understand how to use the apache POI or JExcel jar(as mentioned in earlier posts).

thank in advance [:)]

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.

Comments

Keep up to date on this post and subscribe to comments

October 28, 2011 - 8:29am

I am done writing the java code as per my requirement..
now i have a prob writing this code in the activity...

on saving the activity it is throwing errors on the code lines where i've used the poi features.
Placing jar file on the server and setting the class path might be the issue,
plez guide me to use the 3rd party jars in the PRPC.
A walk throuh would be much helpful.[:)]

Thanks

October 30, 2011 - 11:15pm

Hi Sanjeev,

Can you mention the location of the folder in which you have placed POI jars. can you check your code by placing the jar files in \webapps\prweb\WEB-INF\lib\pega folder of your PRPC installation.. let me know the status by doing the above mentioned..[:)]

November 8, 2011 - 9:21am

I've placed the jars in the web-inf/lib folder and also set defaultpath in data-admin-system-settings..
now i can save the activity without any error but when i try running the activity its stating that

error message:

Status fail
Message org/apache/poi/ss/usermodel/Workbook

Caught exception: java.lang.NoClassDefFoundError: org/apache/poi/ss/usermodel/Workbook

what could be the problem [V]
i think jars are properly places as im not getting any errors at compile time(while saving the java method)..is there any thing that is to be done to use the methods at runtime...

November 8, 2011 - 10:37am

Hi Sanjeev,

Can you send me the code which is causing the error to you.

October 21, 2011 - 5:53am

Hi Sanjeev,
Apache POI works with reading data from Excel Sheet. I have tried it and the data is read with out any issues.

I didn't try with JExcel jar.

Regards,
Harsha A.

October 22, 2011 - 6:31am

thanks for the reply ahchaitanya...

can you plez give me a walk-through to do so..
badly in need of it for my proj...ASAP

October 22, 2011 - 3:40pm

Sanjeev just create a object for file reading. Create object for workbook and then for sheet from POI API and then access cells of eacn sheet to fetching data.

Regards,
Harsha A.

November 11, 2011 - 1:23am

inputStream = new 1.java.io.FileInputStream(new java.io.File(excelLoc));
2.if(inputStream != null){
3.myStepPage.putString("NumberOfRows","san2");
4.}
5. org.apache.poi.ss.usermodel.Workbook 6.wb=new org.apache.poi.ss.usermodel.WorkbookFactory().
7.createinputStream);
8.myStepPage.putString("NumberOfRows","san3");..

int his code the "san2"(line3) is stored into NumberORows but not "san3"(line8)
that means controle is not flowing below 6th line so there might be a
a prob with the workBookFactory method....
hw to resolve this :(

November 11, 2011 - 2:41am

Sorry Sanjeev I have no idea about that.. may be some one else could help you out.. :)

November 14, 2011 - 7:53am

[:(]
hey have u tried it using some other way,if so plez let me know..in gr8 need of it...
a walk thro would be helpful

March 7, 2013 - 5:18am

Hi AHCHAITANYA and SANJEEV89 ,

Can you please send me the code which is working for you guys? I need to implement the same requirement.

Thanks in Advance.
Aritra

March 8, 2013 - 10:15am

Hi, I am also getting same error.

com.pega.pegarules.pub.PRRuntimeError: PRRuntimeError 
at com.pega.pegarules.engine.context.base.ThreadRunner.runActivitiesAlt(ThreadRunner.java:635) 
at com.pega.pegarules.engine.context.PRThreadImpl.runActivitiesAlt(PRThreadImpl.java:568) 
at com.pega.pegarules.services.HttpAPI.runActivities(HttpAPI.java:1757) 
at com.pega.pegarules.services.EngineAPI.processRequestInner(EngineAPI.java:325) 
at sun.reflect.GeneratedMethodAccessor64.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
at java.lang.reflect.Method.invoke(Method.java:601) 
at com.pega.pegarules.engine.context.PREnvironment.doWithRequestorLocked(PREnvironment.java:909) 
at com.pega.pegarules.engine.context.PREnvironment.doWithRequestorLocked(PREnvironment.java:674) 
at com.pega.pegarules.services.EngineAPI.processRequest(EngineAPI.java:259) 
at com.pega.pegarules.services.HttpAPI.invoke(HttpAPI.java:481) 
at com.pega.pegarules.etier.impl.EngineImpl._invokeEngine_privact(EngineImpl.java:270) 
at com.pega.pegarules.etier.impl.EngineImpl.invokeEngine(EngineImpl.java:223) 
at com.pega.pegarules.etier.impl.EngineImpl.invokeEngine(EngineImpl.java:204) 
at com.pega.pegarules.priv.context.JNDIEnvironment.invokeEngineInner(JNDIEnvironment.java:274) 
at com.pega.pegarules.priv.context.JNDIEnvironment.invokeEngine(JNDIEnvironment.java:219) 
at com.pega.pegarules.web.impl.WebStandardImpl.makeEtierRequest(WebStandardImpl.java:307) 
at com.pega.pegarules.web.impl.WebStandardImpl.doPost(WebStandardImpl.java:201) 
at sun.reflect.GeneratedMethodAccessor63.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
at java.lang.reflect.Method.invoke(Method.java:601) 
at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethod(PRBootstrap.java:254) 
at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethodPropagatingThrowable(PRBootstrap.java:295) 
at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethod(PRBootstrap.java:344) 
at com.pega.pegarules.internal.web.servlet.WebStandardBoot.doPost(WebStandardBoot.java:93) 
at com.pega.pegarules.internal.web.servlet.WebStandardBoot.doGet(WebStandardBoot.java:83) 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617) 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) 
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.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:470) 
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) 
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) 
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) 
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:291) 
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859) 
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602) 
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489) 
at java.lang.Thread.run(Thread.java:722) 
Caused by: java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException 
at com.pegarules.generated.activity.ra_action_parseandsaveattachment_9d4962a3985b42a8b63b90c192fb5bbe.step3_circum0(ra_action_parseandsaveattachment_9d4962a3985b42a8b63b90c192fb5bbe.java:329) 
at com.pegarules.generated.activity.ra_action_parseandsaveattachment_9d4962a3985b42a8b63b90c192fb5bbe.perform(ra_action_parseandsaveattachment_9d4962a3985b42a8b63b90c192fb5bbe.java:107) 
at com.pega.pegarules.engine.runtime.Executable.doActivity(Executable.java:3076) 
at com.pegarules.generated.activity.sh_action_openattachments_f98c8d337952a0b3d338fbcb101b7dc0.step3_circum0_Data_WorkAttach_File(sh_action_openattachments_f98c8d337952a0b3d338fbcb101b7dc0.java:487) 
at com.pegarules.generated.activity.sh_action_openattachments_f98c8d337952a0b3d338fbcb101b7dc0.perform(sh_action_openattachments_f98c8d337952a0b3d338fbcb101b7dc0.java:131) 
at com.pega.pegarules.engine.runtime.Executable.doActivity(Executable.java:3076) 
at com.pegarules.generated.activity.ra_action_executemaystartactivity_aa9c3015e95f63a3484681bc5833e528.step3_circum0(ra_action_executemaystartactivity_aa9c3015e95f63a3484681bc5833e528.java:450) 
at com.pegarules.generated.activity.ra_action_executemaystartactivity_aa9c3015e95f63a3484681bc5833e528.perform(ra_action_executemaystartactivity_aa9c3015e95f63a3484681bc5833e528.java:118) 
at com.pega.pegarules.engine.runtime.Executable.doActivity(Executable.java:3076) 
at com.pega.pegarules.engine.context.base.ThreadRunner.runActivitiesAlt(ThreadRunner.java:571) 
... 40 more 
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException 
at java.net.URLClassLoader$1.run(URLClassLoader.java:366) 
at java.net.URLClassLoader$1.run(URLClassLoader.java:355) 
at java.security.AccessController.doPrivileged(Native Method) 
at java.net.URLClassLoader.findClass(URLClassLoader.java:354) 
at java.lang.ClassLoader.loadClass(ClassLoader.java:423) 
at java.lang.ClassLoader.loadClass(ClassLoader.java:356) 
... 50 more 
 

 

Please help.

April 15, 2014 - 1:54pm

You need to import below Jar files. Then you can use

 

1. xmlbeans
2. stax-api-1.0.1
3. dom4j
4. poi-ooxml-schemas
5. poi
6. poi-ooxml
 

 

XSSF  used for xlsx
HSSF  used for xls

You can show file browse option and when the user perform upload and file should go to service export.

From there using appachi Poi you need to get that information to clipoboard.

From clipboard you can do anything.

Here is a sample code I used for this.

Here test.xls is already in service export.I am reading it and appending it to TestPage.pxResults page list.

 
try{
    java.io.InputStream myxls = new java.io.FileInputStream("test.xls");
 org.apache.poi.hssf.usermodel.HSSFWorkbook wb     = new org.apache.poi.hssf.usermodel.HSSFWorkbook(myxls);
    org.apache.poi.hssf.usermodel.HSSFSheet sheet = wb.getSheetAt(0);
    for (int j=0; j< sheet.getLastRowNum() + 1; j++) {
        org.apache.poi.hssf.usermodel.HSSFRow row   = sheet.getRow(j);
        org.apache.poi.hssf.usermodel.HSSFCell cell1 = row.getCell(0);
     a=cell1.getStringCellValue();
        org.apache.poi.hssf.usermodel.HSSFCell cell2 = row.getCell(1);
     b=cell2.getStringCellValue();
  org.apache.poi.hssf.usermodel.HSSFCell cell3 = row.getCell(2);
     c=cell3.getStringCellValue();
  org.apache.poi.hssf.usermodel.HSSFCell cell4 = row.getCell(3);
     d=cell4.getStringCellValue();
  ClipboardProperty class1= tools.findPageWithException("TestPage").getProperty(".pxResults(<append>).pxObjClass");
class1.setValue("BIG-Work-Task");
ClipboardProperty ID= tools.findPageWithException("TestPage").getProperty(".pxResults(<last>).ID");
ID.setValue(a);
ClipboardProperty Grade= tools.findPageWithException("TestPage").getProperty(".pxResults(<last>).Grade");
Grade.setValue(b);
ClipboardProperty Age= tools.findPageWithException("TestPage").getProperty(".pxResults(<last>).Age");
Age.setValue(c);
ClipboardProperty Name= tools.findPageWithException("TestPage").getProperty(".pxResults(<last>).Name");
Name.setValue(d);
}
}
  
    
 catch(Exception x)
{
}
 
 
Thanks,
Janaka Perera