Win a copy of Escape Velocity: Better Metrics for Agile Teams this week in the Agile and Other Processes forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Paul Clapham
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Frank Carver
  • Junilu Lacar
Saloon Keepers:
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Al Hobbs
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • fred rosenberger

Read Excel from JDBC.

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
I am reading excel file from jdbc as follows.

code:
--------------------------------------------------------------------------------

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);rst.next();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 (rs.next()){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.

Thanks
Vishwanath
 
Rancher
Posts: 43028
76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Welcome to JavaRanch.

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

Let's continue the discussion in this duplicate thread.
 
Nothing up my sleeve ... and ... presto! A tiny ad:
Garden Master Course kickstarter
https://coderanch.com/t/754577/Garden-Master-kickstarter
reply
    Bookmark Topic Watch Topic
  • New Topic