how to import excel sheet into mysql database code example

Example 1: sql query to import data from excel to mysql

$ mysql -u root -p --local-infile
mysql> create database boatdb;
mysql> use boatdb;

Example 2: java code to save excel data to mysql

package DB;  
 import java.sql.Connection;  
 import java.sql.DriverManager;  
 import java.sql.SQLException;  
 public class DB_Connection {  
   private Connection con;  
    public DB_Connection()  
   {  
          try  
         {  
               String conUrl="jdbc:mysql://localhost:3306/MyTestDb";  
               String userName="root";  
               String pass="root";  
               Class.forName("com.mysql.jdbc.Driver");  
                 con=DriverManager.getConnection(conUrl,userName,pass);  
         }  
         catch(SQLException s)  
         {  
             System.out.println(s);  
         }  
         catch(ClassNotFoundException c)  
         {  
              System.out.println(c);  
         }  
   }  
   public Connection getConn() {  
     return con;  
   }  
   public void setConn(Connection con) {  
     this.con = con;  
   }  
 }

Example 3: java code to save excel data to mysql

import java.io.*;
      import java.sql.*;
      import java.util.*;
      import java.util.regex.*;
      import org.apache.poi.hssf.usermodel.*;
      import org.apache.poi.poifs.filesystem.POIFSFileSystem;
     
      public class Insert {
      public static void main( String [] args ) {
    	String fileName="C:\\File.xls";
    	Vector dataHolder=read(fileName);
    	saveToDatabase(dataHolder);
    }
	public static Vector read(String fileName)    {
    	Vector cellVectorHolder = new Vector();
    	try{
    		FileInputStream myInput = new FileInputStream(fileName);
   	    	POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
            HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
            HSSFSheet mySheet = myWorkBook.getSheetAt(0);
           Iterator rowIter = mySheet.rowIterator(); 
           while(rowIter.hasNext()){
        	  HSSFRow myRow = (HSSFRow) rowIter.next();
        	  Iterator cellIter = myRow.cellIterator();
        	  Vector cellStoreVector=new Vector();
        	  while(cellIter.hasNext()){
        		  HSSFCell myCell = (HSSFCell) cellIter.next();
        		  cellStoreVector.addElement(myCell);
        	  }
        	  cellVectorHolder.addElement(cellStoreVector);
          }
    	}catch (Exception e){e.printStackTrace(); }
    	return cellVectorHolder;
    }
	private static void saveToDatabase(Vector dataHolder) {
        String username="";
		String password="";
		for (int i=0;i<dataHolder.size(); i++){
                   Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
			for (int j=0; j < cellStoreVector.size();j++){
				HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
				String st = myCell.toString();
				 username=st.substring(0,1);
				 password=st.substring(0);
							}
			try{
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
	Statement stat=con.createStatement();
	int k=stat.executeUpdate("insert into login(username,password) value('"+username+"','"+password+"')");
	System.out.println("Data is inserted");
	stat.close();
	con.close();
	}
	catch(Exception e){}
	}
	}
      }