import java.io.*;
import java.sql.*;
import java.util.*;
import jxl.Cell; 
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings; 
import jxl.read.biff.BiffException;

public class ReadCSV{
ArrayList<String> list1=new ArrayList<String>();
ArrayList<String> list2=new ArrayList<String>();
ArrayList<String> list3=new ArrayList<String>();

public void getHeadingFromXlsFile(Sheet sheet){
    int columnCount = sheet.getColumns();
    for(int i = 0; i < columnCount; i++){
        System.out.println("Coloumn Count : "+sheet.getCell(i, 0).getContents());
    }
}
public void contentReading()throws Exception{
    WorkbookSettings ws = null;
    Workbook workbook = null;
    Sheet s = null;
    Cell rowData[] = null;
    int rowCount = 0;
    int columnCount = 0;
    int totalSheet = 0;


        ws = new WorkbookSettings();
        ws.setLocale(new Locale("en", "EN"));
        workbook = Workbook.getWorkbook(new File("C:/data.csv"), ws);

        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("Age: "+rowData[j].getContents());
                        list3.add(rowData[j].getContents());
                        break;
                       }
                    }
                  }
        }
    workbook.close(); 
    try{
   Class.forName("com.mysql.jdbc.Driver");
         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 age=list3.get(i).toString();

         st.executeUpdate("insert into student(id,name,age) values('"+id+"','"+name+"','"+age+"')");
         }
        System.out.println("Inserted successfully!");
    }
    catch(Exception e){
    System.out.println(e);
      }

}
    public static void main(String[] args)throws Exception {
    try {
        ReadCSV xlReader = new ReadCSV();
        xlReader.contentReading();
    }
    catch (Exception e) {
        e.printStackTrace();
    }
  }
}