File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Java in General and the fly likes Read Excel from JDBC. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Read Excel from JDBC." Watch "Read Excel from JDBC." New topic

Read Excel from JDBC.

Vishwanath Bannur

Joined: Jan 07, 2008
Posts: 8
I am reading excel file from jdbc as follows.


try{Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );con = DriverManager.getConnection("jdbc dbc river={Microsoft Excel Driver (*.xls)};DBQ=" + filename);System.out.println("Get connection");//con = DriverManager.getConnection("jdbc dbc:ExcelTest","","");stmnt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);rst=con.getMetaData().getTables(null, null, "%", null);;String SheetName = rst.getString(3);String query = "Select * from ["+SheetName+"]" ;rs = stmnt.executeQuery( query );ResultSetMetaData rsmd = rs.getMetaData();int ColCount = rsmd.getColumnCount(); if (rs!= null){rs.last();totalRecords= rs.getRow();rs.beforeFirst();System.out.println("Total Records from Excel Spreadsheet" + totalRecords);if(totalRecords > 0){PONum = new String[totalRecords];POVersion = new String[totalRecords];BillingTelNum = new String[totalRecords];while ({String pon = rs.getString(1);if(null!=pon){PONum[i] = pon;}elsePONum[i] = null;String pov = rs.getString(2);if(null!=pov){POVersion[i] = pov;}elsePOVersion[i] = null; String telnum = rs.getString(3);if(null!=telnum){BillingTelNum[i] = telnum.substring(0,telnum.indexOf("."));}elseBillingTelNum[i] = null;}}}catch(SQLException sqle){sqle.getMessage();}catch(Exception e){System.out.println(""+e.toString());}finally{try{rst.close();rs.close();stmnt.close();con.close();}catch(Exception e){System.err.println(e);}


My excel sheet contain 3 cols namely Number, Version, Telephone Num.As shown below
Number version tele num.
1 1234 12345
2 abcd 123788
3 1223 17889898

my java code is reading fine when the data of data type are same in cols.

But its giving nullpointerexception when the data of datatype is different in cols.

For example in version cols contain 2 number values and one string values.
that time its not reading the version values.
Its only working fine version cols contain either all are string values or integer values.But i need to enter both string as well as integer.

Please help me if you know the solution.

Ulf Dittmer

Joined: Mar 22, 2005
Posts: 42965
Welcome to JavaRanch.

Please do not post the same question to multiple forums: CarefullyChooseOneForum

Let's continue the discussion in this duplicate thread.
I agree. Here's the link:
subject: Read Excel from JDBC.
It's not a secret anymore!