1 Replies - 908 Views - Last Post: 27 November 2015 - 11:31 AM

#1 j2ee   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 24-November 15

How to insert to mssql database from excel through jsp?

Posted 24 November 2015 - 09:24 PM

Here is my code. I put the excel file at the right location, the result is just "Details have not been inserted!!!!!!!!!" without data insert into database. All details below are setup for my local testing environment:

   <%@ 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">
  <title>Insert title here</title>
  </head>
  <body>
  <%!     
  Connection con;
  PreparedStatement ps=null;
  Statement stmt= null;
   public static final String sDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
 public static final String sCon = "jdbc:odbc:localmsdb";
 public static final String sDBUser = "sa";
 public static final String sDBPass = "hkcorg1234";  
  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 file = request.getParameter("file");

        //String fileName=""+file+""; 
        //testExcel.xls Excel File name
        String fileName="C:\\test\\exceltest.xls";

           //Read an Excel File and Store in a ArrayList
               ArrayList dataHolder=readExcelFile(fileName);

              //Print the data read
               //printCellDataToConsole(dataHolder);

          try
               {


con = DriverManager.getConnection(sCon, sDBUser, sDBPass);
    stmt = con.createStatement();      

             //  Class.forName("com.mysql.jdbc.Driver");
             //   con=DriverManager.getConnection("jdbc:mysql://localhost:3306/abl","root","gaurav");
              //  stmt =con.createStatement();
               String query="insert into  ChargeItem 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()); try { ps.executeUpdate(); count++; } catch(Exception e) { } out.println(((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 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>
                <td><%=((HSSFCell)cellStoreArrayList.get(2)).toString() %></td>
           </tr>
         <%}
              }
             else
                {%>
             <center> Details have not been inserted!!!!!!!!!</center>

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

                 </body>
                  </html>


Is This A Good Question/Topic? 0
  • +

Replies To: How to insert to mssql database from excel through jsp?

#2 croog24   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 42
  • Joined: 04-February 10

Re: How to insert to mssql database from excel through jsp?

Posted 27 November 2015 - 11:31 AM

I would add some logging to see whether or not your objects are properly collecting the data. Example being the cellArrayLisstHolder variable to log, etc. Clearly somewhere the data gets lost (or maybe not read in at all), so you need to do some debugging.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1