<%@page import="java.io.*,java.sql.*,java.util.*,jxl.*, jxl.read.biff.BiffException"%>

<%
ArrayList<String> list1=new ArrayList<String>();
ArrayList<String> list2=new ArrayList<String>();
ArrayList<String> list3=new ArrayList<String>();
    Cell rowData[] = null;
    int rowCount = 0;
    int columnCount = 0;

       WorkbookSettings ws = new WorkbookSettings();
       Workbook workbook = Workbook.getWorkbook(new File("C:/data.xls"), ws);

        Sheet s = workbook.getSheet(0);
        rowCount = s.getRows();
        columnCount = s.getColumns();
        for(int i = 1; i < rowCount; i++){
            rowData = s.getRow(i);
            if(rowData[0].getContents().length() != 0){ 
                for(int j = 0; j < columnCount ;j++){
                 switch(j){
                    case 0:
                        System.out.println("ID: "+rowData[j].getContents());
                        list1.add(rowData[j].getContents());
                        break;
                    case 1:
                        System.out.println("Name: "+rowData[j].getContents());
                        list2.add(rowData[j].getContents());
                        break;
                    case 2:
                        System.out.println("Address: "+rowData[j].getContents());
                        list3.add(rowData[j].getContents());
                        break;
                       }
                    }
                 }
        }
    workbook.close(); 
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/roseindia","root", "root");
    Statement st=conn.createStatement();
        for(int i=0;i<list1.size();i++){
        String id=list1.get(i).toString();
        String name=list2.get(i).toString();
        String address=list3.get(i).toString();
        st.executeUpdate("insert into data(id,name,address) values('"+id+"','"+name+"','"+address+"')");
    }
    out.println("Data is successfully inserted into the database");
%>