Problem in Reading Excel File from Webdynpro

[Former Member]

Hi,

I am getting errors while reading values from a Excel file from webdynpro.

Below is the code :

public void onActionreadExcel(com.sap.tc.webdynpro.progmodel.api.IWDCustomEvent wdEvent )

{

//@@begin onActionreadExcel(ServerEvent)

String filename=wdContext.currentContextElement().getFileName();

//String filename= "c:
Excel.xls";

wdComponentAPI.getMessageManager().reportSuccess(filename);

Connection c = null;

Statement stmnt = null;

try {

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

c = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + filename);

stmnt = c.createStatement();

String query = "Select * from [a$]" ;

ResultSet rs = stmnt.executeQuery( query );

while( rs.next() )

{

wdComponentAPI.getMessageManager().reportSuccess(rs.getString("Product Code"));

}

}

catch( Exception e ) {

wdComponentAPI.getMessageManager().reportSuccess(e.toString()+"");

}

//@@end

}

I get following error.

java.sql.SQLException: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

Is thsi whole method of reading the excel file from webdynpro is wrong ?

Please help.

Thanks,

Regards,

Aditya

[former_member185086]

Hi

Please go through this [link|; I have provided all codes also how to use the external library insight webdynpro which contains jxl lib.

Best Regards

Satish Kumar

[lokesh_kamana]

Hi,

I think it is not able to find the JDBC ODBC Bridge drivers jar,

Try to craete a external library and use that in DC if your creating an Development component.

If it is a normal Webdynpro application.

Try keeping these jar files in lib folder.

Thanks & Regards,

Lokesh.

[Former Member]

Hi,

Please go through this blog.

/people/nagamohan.devisetty/blog/2006/07/31/reading-excel-sheet-from-java-without-using-any-framework

Regards,

Gayathri.

[Former Member]

Hi Aditya,

You can do in another way ,

As you said the code is working in java"<i>If i execute my code as a stand alone java program it works fine</i>".

So in NWDS create a project with java class and write this code in that ,

along with it get all the data into a collection and return the collection in the java program .

Generate a .jar file and copy the .jar file into lib of the Webdynpro application .and You can retriuve the collection by calling the method in java program.

This is one way you can solve it.

[Former Member]

Dear Srivastsa,

That method cannot be used to read Excel file.

As we have the data of the file in bytes from context.

That method takes filepath as parameter to create the connection.

Thanks,

Regards,

Aditya

[Former Member]

aditya,

It looks ok, and almost identical to things described here: http://www.devx.com/Java/Article/17848

The only thing is that probably "... from [a$]" -- are you sure that you renamed your sheet from Sheet1$ to a$? Could you try same with "... from [Sheet1$]"? Also could you enumerate all fields in "select" clause (instead of *)?

Finally, you can try alernative with XLSQL JDBC driver, see:

https://xlsql.dev.java.net/

http://xlsql.sourceforge.net/

(both links are same project)

Btw, where you get sample for URL in DriverManager.getConnection()? Could you share link to source of this information?

Valery Silaev

EPAM Systems

http://www.NetWeaverTeam.com

[Former Member]

Dear Valery,

The worksheet name is 'a' itself, i purposely renamed it.

Even for Sheet1 worksheet name it doesn't work.

Even after enumerating the field in select clause its not working.

The source for my code is :

If i execute my code as a stand alone java program it works fine. Problem is from reading webdynpro..may be i guess we need to use the method of converting it to inputstream and read or use webdynpro office control ui..i am not sure..

Anways thanks for the reply.

Regards,

Aditya