Read data from Excel and insert in to DB and export data from DB to Excel

Hi Friend,

You can read the data from the excel file using JDBC. For this you need to create dsn connection.

Follow these steps to create a dsn connection:

1) Go to the control Panel>>Administrative Tools>>Data Sources.

2)Click Add button and select Driver do Microsoft Excel driver. Click finish button

3)Add Data Source Name and click select workbook button to select the workbook whose data is to be retrieved.

4)Click ok and compile the following code:

import java.sql.*;
import java.util.*;

public class InsertIExcelValueToDatabase{
    public static void main(String[] args)throws Exception{
    ArrayList list1=new ArrayList();
    ArrayList list2=new ArrayList();
    try{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         Connection con = DriverManager.getConnection("jdbc:odbc:excel");
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("select  name, address from [Sheet1$]");
         while (rs.next()) {
            String name = rs.getString("name");
            String address = rs.getString("address");
            list1.add(name);
            list2.add(address);
            System.out.println(name + "  "+address);
        }
         con.close();

         Class.forName("com.mysql.jdbc.Driver");
          Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
         Statement st = conn.createStatement();
         for(int i=0;i<list2.size();i++){
             String nn=list1.get(i).toString();
             String add=list2.get(i).toString();

             st.executeUpdate("insert into data1(name,address) values('"+nn+"','"+add+"')");
              System.out.println(nn+" "+add);
         }
    }
    catch(Exception e){
        System.out.println(e);
    }
       }
        }

Thanks