script to read file from xls and insert to mysql + java code example

Example 1: 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){}
	}
	}
      }

Example 2: java code to save excel data to mysql

<%@ page language="java" import="java.sql.*" contentType="text/html; charset=ISO-8859-1"  
   pageEncoding="ISO-8859-1"%>  
 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">  
 <%@ page import ="java.util.Date" %>  
 <%@ page import ="java.io.*" %>  
 <%@ page import ="java.io.FileNotFoundException" %>  
 <%@ page import ="java.io.IOException" %>  
 <%@ page import ="java.util.Iterator" %>  
 <%@ page import ="java.util.ArrayList" %>  
 //Apache POI Libraries  
 <%@ page import ="org.apache.poi.hssf.usermodel.HSSFCell" %>  
 <%@ page import ="org.apache.poi.hssf.usermodel.HSSFRow" %>  
 <%@ page import ="org.apache.poi.hssf.usermodel.HSSFSheet" %>  
 <%@ page import ="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>  
 <%@ page import ="org.apache.poi.poifs.filesystem.POIFSFileSystem" %>  
 <html>  
 <head>  
 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">  
 <title>Insert title here</title>  
 </head>  
 <body>  
 <jsp:useBean id="connection" class="DB.DB_Connection" scope="page">  
   <jsp:setProperty name="connection" property="*"/>  
 </jsp:useBean>  
 <%!    
 Connection con;  
 PreparedStatement ps=null;  
 public static ArrayList readExcelFile(String fileName)  
 {  
   /** --Define a ArrayList  
     --Holds ArrayList Of Cells  
    */  
   ArrayList cellArrayLisstHolder = new ArrayList();  
   try{  
   /** Creating Input Stream**/  
     FileInputStream myInput = new FileInputStream(fileName);  
   /** Create a POIFSFileSystem object**/  
   POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);  
   /** Create a workbook using the File System**/  
    HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);  
    /** Get the first sheet from workbook**/  
   HSSFSheet mySheet = myWorkBook.getSheetAt(0);  
   /** We now need something to iterate through the cells.**/  
    Iterator rowIter = mySheet.rowIterator();  
    while(rowIter.hasNext()){  
      HSSFRow myRow = (HSSFRow) rowIter.next();  
      Iterator cellIter = myRow.cellIterator();  
      ArrayList cellStoreArrayList=new ArrayList();  
      while(cellIter.hasNext()){  
        HSSFCell myCell = (HSSFCell) cellIter.next();  
        cellStoreArrayList.add(myCell);  
      }  
      cellArrayLisstHolder.add(cellStoreArrayList);  
    }  
   }catch (Exception e){e.printStackTrace(); }  
   return cellArrayLisstHolder;  
 }%>  
 <%  
 String fileName="testExcel.xls"; //testExcel.xls Excel File name  
 //Read an Excel File and Store in a ArrayList  
 ArrayList dataHolder=readExcelFile(fileName);  
 //Print the data read  
 //printCellDataToConsole(dataHolder);  
 con=connection.getConn();  
 String query="insert into Student values(?,?,?)";  
 ps=con.prepareStatement(query);  
 int count=0;  
 ArrayList cellStoreArrayList=null;  
 //For inserting into database  
 for (int i=1;i < dataHolder.size(); i++) {  
   cellStoreArrayList=(ArrayList)dataHolder.get(i);  
     ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());  
     ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());  
     ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());  
    count= ps.executeUpdate();  
     System.out.print(((HSSFCell)cellStoreArrayList.get(2)).toString() + "t");  
     }  
 //For checking data is inserted or not?  
   if(count>0)  
     { %>  
         Following deatils from Excel file have been inserted in student table of database  
           <table>  
             <tr>  
               <th>Student's Name</th>  
               <th>Class</th>  
               <th>Age</th>  
             </tr>  
     <% for (int i=1;i < dataHolder.size(); i++) {  
   cellStoreArrayList=(ArrayList)dataHolder.get(i);%>  
   <tr>  
     <td><%=((HSSFCell)cellStoreArrayList.get(0)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(1)).toString() %></td>  
     <td><%=((HSSFCell)cellStoreArrayList.get(2)).toString() %></td>  
   </tr>  
     <%}  
    }  
   else  
   {%>  
   <center> Details have not been inserted!!!!!!!!!</center>  
   <%  }  %>  
     </table>  
 </body>  
 </html>

Tags:

Sql Example

Recent Posts