Thursday, 25 October 2012

Insert Data into database from Excel File


For inserting Excel file data into database, First we have to read the Excel file.Then insert data into database.
            For reading Excel file,we will use Apache POI. Download the POI library from following link:
http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.8-20120326.zip
Extract zip files from this and use following library in your project:
  1. poi-ooxml-schemas-3.8-20120326
  2. poi-3.8-20120326
  3. dom4j-1.6.1
  4. xmlbeans-2.3.0
For MySql connection use mysql-connector-java-5.1.7-bin jar file. Include all these jars into lib folder.
Now  create a java bean for connecting to MySQL database.Its not necessary ,you can also create connection in your jsp page.
     Hierarchy of DB_Connection.java(Java-Bean)





Code  For DB_Connection.java


 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;  
   }  
 }  

Now Code for reading excel file and inserting data of  excel file into MySql table i.e. StudentreadExcel2.jsp

 <%@ 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>  

Structure of Student Table:
Create table Student(name varchar(2),class varchar(20),Age varchar(4));


Output: