Results 1 to 5 of 5
  1. #1
    sasi.tati is offline Member
    Join Date
    Jul 2010
    Posts
    18
    Rep Power
    0

    Unhappy How to read excel sheet using select statments r else any easy way

    Hi

    can anyone guide me how to read an excel sheet with an select statement, so that i can place the values in variable's and insert into a table in mysql database.

    i am able to read the values with below code but i am unable to get those values in an order means the entire record/row in to diff variables so that i can insert in the table:

    Java Code:
    for(int i =0; i < sheet.getColumns(); i++){
                    for(int j=1; j < sheet.getRows(); j++){
                        Cell cell = sheet.getCell(i, j);
                        System.out.println(cell.getContents());
                    }
                }
    This below code is not working properly it is throwing error: sun.jdbc.odbc.JdbcOdbcConnection cannot be cast to com.mysql.jdbc.Connection
    Java Code:
    public String readexel(String filename, Connection conn) {
            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                System.out.println("" + filename);
                String url = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)}; DBQ=" + filename;
                System.out.println("\n\n\n" + url);
                conn = (Connection) DriverManager.getConnection(url);
    
                System.out.println("");
                stmt = (Statement) conn.createStatement();
    
                String queryl = "select * from [Sheet1$]";
                ResultSet rs = (ResultSet) stmt.executeQuery(queryl);
                while(rs.next())
            {
            email = rs.getString(1)+"";
            firstname = rs.getString(2)+"";
            System.out.println(">>>eamil & firstname is>>>>"+email +""+firstname);
            }
            } catch (Exception e) {
                System.out.println("Exception is" + e);
            }
            return "";
           }
    can anyone guide me how to resolve this issue, i am new to java and i have to use this code in a bean which will be invoked from a jsp.
    Last edited by sasi.tati; 09-24-2010 at 12:11 PM.

  2. #2
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,371
    Blog Entries
    1
    Rep Power
    20

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,225
    Rep Power
    20

    Default

    Use POI or JExcel to read (which looks like what ou're attempting int he first code snippet).

    If it's an insert per row, then read each row, build an object representing that row, and pass that object to your DAO to do the insert.

    Don't treat an Excel sheet as a database...

  4. #4
    sasi.tati is offline Member
    Join Date
    Jul 2010
    Posts
    18
    Rep Power
    0

    Default

    Hi,

    Only this error is coming in the NetBeans: java.lang.ClassCastException: sun.jdbc.odbc.JdbcOdbcConnection cannot be cast to com.mysql.jdbc.Connection

    by using stack trace method this error is coming in the console:
    [Ljava.lang.StackTraceElement;@83cc67

    I am able to read the excel sheet in column wise but i am unable to read in row wise with the first code, but it will be possible in the second code but that thing is throwing exception.

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,225
    Rep Power
    20

    Default

    Both JExcel and POI use rows...not knowing what excel reading package you;re using I can't really help.

    I'll say again, an Excel sheet is not a database.

    If you insist, then you'll need to post the stack trace...that is this:
    Java Code:
            } catch (Exception e) {
                System.out.println("Exception is" + e);
            }
    is not a good idea as you lose loads of info, do e.printStackTtrace() instead.

    You;re also not closing your resources (conn, stmt, rs), which should be done in a finally block.

    From the teeny amount of exception data you've given it looks like somewhere in your code you're using a mysql driver for you odbc driver? Anyway, if you print the stack trace you'll see exactly what's wrong.

Similar Threads

  1. SQL Query resultset into Excel Sheet
    By chandpuri in forum JDBC
    Replies: 8
    Last Post: 01-24-2011, 07:53 PM
  2. How to create excel sheet?
    By kishan in forum Advanced Java
    Replies: 3
    Last Post: 07-13-2010, 02:15 PM
  3. How to read EXCEL Sheet using XML
    By raju.i in forum New To Java
    Replies: 1
    Last Post: 04-19-2010, 01:39 AM
  4. Excel sheet opening in read-only mode
    By swati.jyoti in forum Advanced Java
    Replies: 2
    Last Post: 09-08-2009, 03:08 PM
  5. How to set the AutoFilter to Excel Sheet with Wither POI or JXL
    By lnarayana_boga in forum Advanced Java
    Replies: 0
    Last Post: 01-29-2008, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •