Read Excel data using JSP and update MySQL databse

Follow these steps:

1)Go to the start>>Control Panel>>Administrative Tools>> data sources.

2)Click Add button and select the Driver do Microsoft Excel(*.xls).

3)After selecting the driver, click finish button.

4)Then give Data Source Name,select drive, directory and excel file and click ok button.

5)Your DSN will get created.

6) Then run your jsp code:

<%@page import="java.sql.*"%>
<%
try{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         Connection con = DriverManager.getConnection("jdbc:odbc:excel");
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("select  * from [sheet$]");
         Class.forName("com.mysql.jdbc.Driver");
           Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
           Statement st=conn.createStatement();
         while (rs.next()){
        st.executeUpdate("insert into student(firstname,lastname,email,address) values('"+rs.getString(1)+"','"+rs.getString(2)+"','"+rs.getString(3)+"','"+rs.getString(4)+"')");
        }

        out.println("Data has been read from the excel file and inserted into the database.");  
}
catch(Exception e){
System.out.println(e);
}
        %>

In the above code, we have used sun.jdbc.odbc.JdbcOdbcDriver to read the excel file and excel is our dsn,sheet is our sheet name in the selected excel file. The excel file which you want to read should be selected when creating dsn.