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

[sasi.tati]

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:

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

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.

[Eranga]

Can you post the complete error message, stack trace?

[Tolls]

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...

[sasi.tati]

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.

[Tolls]

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:

        } 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.