Untitled

<%@ 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" %>
    <%@ page import ="javax.servlet.http.HttpServletRequest"%>
    <%@ 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">
    </head>
    <body>
      <%!
        Connection con;
        PreparedStatement ps=null;
        Statement stmt= null;
        public static ArrayList readExcelFile(String fileName)
        {
           ArrayList cellArrayLisstHolder = new ArrayList();
            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();
                    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 file = request.getParameter("file");
                String fileName="/home/data.xls";
                ArrayList dataHolder=readExcelFile(fileName);
                try
                   {
                   Class.forName("com.mysql.jdbc.Driver");
                    con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql","root","");
                    stmt =con.createStatement();
                   String query="insert into data(address, name) values(?,?)";
                   ps=con.prepareStatement(query);
                   int count=0;
                   ArrayList cellStoreArrayList=null;
                    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());
                     count= ps.executeUpdate();
                   }
                  if(count>0)
                  {
                     %> Following deatils from Excel file have been inserted in student table of database
                     <table>
                     <tr>
                       <th>Address</th>
                       <th>Name</th>
                     </tr>
                     <%
                        for (int j=1;j < dataHolder.size(); j++) {
                         cellStoreArrayList=(ArrayList)dataHolder.get(j);%>
                        <tr>
                           <td><%=((HSSFCell)cellStoreArrayList.get(0)).toString() %></td>
                           <td><%=((HSSFCell)cellStoreArrayList.get(1)).toString() %></td>

                   </tr>
               <%}
                  }
                 else
                    {%>
                 <center> Details have not been inserted!!!!!!!!!</center>

                           <%    }
                        }catch(Exception e)
                     {}%>
                    </table>

                     </body>
                      </html>