Question Re: Reading Excel file using POI API in Technology Q&A

Reading Excel file using POI API

Former Member — Fri, 23 May 2008 19:50:53 GMT

Hi All,

I have gone through SDN and am following this weblog to read an Excel file using POI api. I am using JSPDynpage.

https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/1405

I basically want to read the contents of an excel file and pass them to a function module.

Following is my code :




public void readfile(){
System.err.println("readfile called");
try{
System.err.println("inside try");
InputStream fs = new FileInputStream("c:/GL/Book3.xls");
//POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("Book3.xls"));
System.err.println("created filesystem");

HSSFWorkbook wb = new HSSFWorkbook(fs);
System.err.println("created workbook");

HSSFSheet sheet = wb.getSheetAt(0);
System.err.println("picked up sheet");

for (Iterator rit = sheet.rowIterator(); rit.hasNext(); ) {
HSSFRow row = (HSSFRow)rit.next();
System.err.println("first FOR printing row :- " + row);
for (Iterator cit = row.cellIterator(); cit.hasNext(); ) {
HSSFCell cell = (HSSFCell)cit.next();
System.err.println("second FOR printing cell :- " + cell.getStringCellValue()+"\t"); 
}
}

}catch(Exception e){
                             System.out.print("Exception Occured " + e.toString()); 
                          }

}

The problem that im facing with the above code is that It prints till the line : System.err.println("inside try"); and after that there is no output.

Any suggestions ?

Re: Reading Excel file using POI API

Former Member — Tue, 27 May 2008 14:07:02 GMT

I think the problem seems to be here :


InputStream fs = new FileInputStream("c:/GL/Book3.xls");

I have attached the excel sheet in my project folder which is below the "dist" folder. It is like this MyProject->Book3.xls

Any Ideas on how I can pass this excel sheet from the above project folder to


InputStream fs = new FileInputStream(".....xls");

Thanks,

Edited by: S.B on May 27, 2008 4:07 PM

Re: Reading Excel file using POI API

Former Member — Tue, 27 May 2008 15:52:10 GMT


> Hi All,
> 
> I have gone through SDN and am following this weblog to read an Excel file using POI api. I am using JSPDynpage.
> 
> https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/1405
> 
> I basically want to read the contents of an excel file and pass them to a function module. 
> 
> Following is my code :
> 
> 
> 

> InputStream fs = new FileInputStream("c:/GL/Book3.xls");
> 
> 
> 
> The problem that im facing with the above code is that It prints till the line : System.err.println("inside try"); and after that there is no output.
> 
> 
> Any suggestions ?

Put the excel file in the folder : /dist/PORTAL-INF/folder_name/

Now use componentRequest.getWebresourcePath()+"folder_name/file_name" to access the file. component request is a readily available variable in jsp and if you need it in javadynpage componentRequest = (IPortalComponentRequest) this.getRequest(); should work.

InputStream fs = new FileInputStream(componentRequest.getWebresourcePath()+"folder_name/file_name");

Placing the excel file in KM or on portal FS and reading from it another way.

Re: Reading Excel file using POI API

Former Member — Tue, 27 May 2008 17:25:22 GMT

Hi Bobby,

This is my code and it still doesnt read beyond - System.err.println("inside try");


public void readfile(){
		System.err.println("america called");
	            try{
				System.err.println("inside try");
				IPortalComponentRequest componentRequest = (IPortalComponentRequest) this.getRequest();
				
				InputStream fs = new FileInputStream(componentRequest.getWebResourcePath()+"resources/Book3.xls");

			//POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("Book3.xls"));
				System.err.println("created filesystem");
				
				HSSFWorkbook wb = new HSSFWorkbook(fs);
				System.err.println("created workbook");
				
				HSSFSheet sheet = wb.getSheetAt(0);
				System.err.println("picked up sheet");
				
				for (Iterator rit = sheet.rowIterator(); rit.hasNext(); ) {
						HSSFRow row = (HSSFRow)rit.next();
						System.err.println("first FOR printing row Avaya1:- " + row);
						for (Iterator cit = row.cellIterator(); cit.hasNext(); ) {
							HSSFCell cell = (HSSFCell)cit.next();
							System.err.println("second FOR printing cell Avaya2:- " + cell.getStringCellValue()+"\t");
					
						}
					}

				
				
				
				
				
	            }catch(Exception e){
	System.out.print("Exception Occured " + e.toString()); 	
	            }

}

It should print me the contents of the Excel file in the log with system.err.println.

Edited by: S.B on May 27, 2008 7:26 PM

Re: Reading Excel file using POI API

Former Member — Tue, 27 May 2008 18:59:24 GMT

This is the error log in the log file when It tries to read the excel file.



at com.sap.engine.services.httpserver.server.io.HttpInputStream.read(HttpInputStream.java:192)
	at java.io.BufferedInputStream.fill(BufferedInputStream.java:183)
	at java.io.BufferedInputStream.read1(BufferedInputStream.java:222)
	at java.io.BufferedInputStream.read(BufferedInputStream.java:277)
	at com.sapportals.portal.prt.util.http.MultiPartParser$PartInputStream.read(MultiPartParser.java:132)
	at java.io.InputStream.read(InputStream.java:89)
	at com.sapportals.portal.prt.util.http.FilePart.<init>(FilePart.java:38)
	at com.sapportals.portal.prt.util.http.MultiPartParser.parseParts(MultiPartParser.java:534)
	at com.sapportals.portal.prt.util.http.MultiPartParser.getFileParts(MultiPartParser.java:477)
	at com.sap.ip.portal.prt.service.facilities.UploadHandler.getFilePart(UploadHandler.java:106)
	at com.sap.ip.portal.admin.portalanywhere.ArchiveUploader.doUpload(ArchiveUploader.java:60)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:324)
	at com.sapportals.portal.prt.component.AbstractPortalComponent.handleRequestEvent(AbstractPortalComponent.java:706)
	at com.sapportals.portal.prt.component.AbstractPortalComponent.handleEvent(AbstractPortalComponent.java:412)
	at com.sapportals.portal.prt.pom.ComponentNode.handleEvent(ComponentNode.java:252)
	at com.sapportals.portal.prt.pom.PortalNode.fireEventOnNode(PortalNode.java:368)
	at com.sapportals.portal.prt.core.PortalRequestManager.runRequestCycle(PortalRequestManager.java:707)
	at com.sapportals.portal.prt.connection.ServletConnection.handleRequest(ServletConnection.java:240)
	at com.sapportals.portal.prt.dispatcher.Dispatcher$doService.run(Dispatcher.java:524)
	at java.security.AccessController.doPrivileged(Native Method)
	at com.sapportals.portal.prt.dispatcher.Dispatcher.service(Dispatcher.java:407)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
	at com.sap.engine.services.servlets_jsp.server.servlet.InvokerServlet.service(InvokerServlet.java:156)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
	at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.runServlet(HttpHandlerImpl.java:401)
	at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.handleRequest(HttpHandlerImpl.java:266)
	at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:386)
	at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:364)
	at com.sap.engine.services.httpserver.server.RequestAnalizer.invokeWebContainer(RequestAnalizer.java:1039)
	at com.sap.engine.services.httpserver.server.RequestAnalizer.handle(RequestAnalizer.java:265)
	at com.sap.engine.services.httpserver.server.Client.handle(Client.java:95)
	at com.sap.engine.services.httpserver.server.Processor.request(Processor.java:175)
	at com.sap.engine.core.service630.context.cluster.session.ApplicationSessionMessageListener.process(ApplicationSessionMessageListener.java:33)
	at com.sap.engine.core.cluster.impl6.session.MessageRunner.run(MessageRunner.java:41)
	at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)
	at java.security.AccessController.doPrivileged(Native Method)
	at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:102)
	at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:172)

Any clues?

Re: Reading Excel file using POI API

Former Member — Tue, 27 May 2008 20:19:24 GMT

I have created a new folder directly under "dist". The folder is called "resources". This folder contains my excel file.

Now when I run the following code,



IPortalComponentRequest componentRequest = (IPortalComponentRequest) this.getRequest();
String FileURL = componentRequest.getWebResourcePath() + "/resources/Book3.xls";
InputStream fs = new FileInputStream(FileURL);
System.err.println("created filesystem");
HSSFWorkbook wb = new HSSFWorkbook(fs);

It throws the exception :


FileNotFoundException Occured java.io.FileNotFoundException: /irj/portalapps/NewJcoTest/resources/Book3.xls (No such file or directory)this.name in bean

but when I pick the same path (http://portalserver:54000//irj/portalapps/NewJcoTest/resources/Book3.xls) and put it in a new browser window, it opens the excel sheet in the brouser.

Any Ideas ?

Re: Reading Excel file using POI API

Former Member — Fri, 30 May 2008 15:34:43 GMT

Use "componentRequest.getPublicResourcePath()"

Thread is closed.

Re: Reading Excel file using POI API

Former Member — Tue, 18 Nov 2008 15:03:09 GMT

IPortalComponentRequest componentRequest = (IPortalComponentRequest) this.getRequest();

String FileURL = componentRequest.getPublicResourcePath()+ "/resources/EmployeeDetails.xls";

InputStream fs = new FileInputStream(FileURL);

HSSFWorkbook wb = new HSSFWorkbook(fs);

I want to read the excel file. only first three lines are excueted. cannot able to create the workbook .can u tell how u achieved this.

Regards,

Shanthakumar