CODEWITHSUNDEEP
javaexceljsp
hem shah
hem shah

Reputation: 19

Store data in database from an uploaded Excel file

I have a code which handles uploading an Excel file, by reading it and storing it in the database. The code follows, and it is compiling but giving an error.

Please see the code below:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title></title>
        <link href="http://fonts.googleapis.com/css?family=Source+Sans+Pro:200,300,400,600,700,900|Quicksand:400,700|Questrial" rel="stylesheet" />
        <link href="css/default.css" rel="stylesheet" type="text/css" media="all" />
        <link href="css/fonts.css" rel="stylesheet" type="text/css" media="all" />
        <%@ page import="java.sql.*" %>
        <%@ page import="com.oreilly.servlet.*" %> 
        <%@ page import="java.io.*"%>
        <%@ page import="java.util.*"%>
        <%@ page import="com.oreilly.servlet.*" %> 
        <%@page import=" org.apache.poi.hssf.usermodel.*"%>
        <%@ page import ="org.apache.poi.poifs.filesystem.*" %> 
        <%@ page import="org.apache.poi.ss.usermodel.Row" %> 
        <%@ page import ="java.util.Date" %>
        <%@ page import ="java.io.*" %>
    </head>
    <body>
        <div id="header-wrapper">
            <div id="header" class="container">
                <div id="logo">
                    <h1><span class="icon icon-cog"></span><a href="#">MPSTME Exam Portal</a></h1>
                    <div id="menu">
                        <ul>
                            <li><a href="homepage_teacher.html" accesskey="1" title="">Homepage</a></li>
                            <li><a href="#" accesskey="2" title="">New Teacher</a></li>
                            <li><a href="#" accesskey="3" title="">New Student</a></li>
                            <li class="current_page_item"><a href="admin_timetable.jsp" accesskey="5" title="">Upload Timetable</a></li>
                            <li><a href="index.html" accesskey="6" title="">Logout</a></li>
                        </ul>
                    </div>
                </div>
            </div>
        </div>
        <div id="page-wrapper">
            <div id="page" class="container">
                <div class="title">
                    <h2>Upload Timetable</h2>
                </div>
            </div>
        </div>
        <div class="wrapper">
        <div id="three-column" class="container">
        <% 
         MultipartRequest m = new MultipartRequest(request,"/Users/Documents/workspace/exam_portal/WebContent",96163690);
         out.print("uploaded");
         %>
        <%
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/examportal","root","mpstme");
        %>
        <% 
        String filename="/Users/hemshah/Documents/workspace/exam_portal/WebContent/timetable.xls";
        ArrayList dataHolder = readExcelFile(filename);
        String sql = "insert into timetable values(?,?,?,?,?,?)";
        ps = conn.prepareStatement(sql);
        int count = 0;
        ArrayList cellStoreArrayList = null;
        System.out.println("dataHolder.size() = " + dataHolder.size());

        for (int i = 1; i < dataHolder.size(); i++) {  
                System.out.println("dataHolder.size()="+dataHolder.get(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());
                ps.setString(4,((HSSFCell)cellStoreArrayList.get(3)).toString());  
                ps.setString(5,((HSSFCell)cellStoreArrayList.get(4)).toString());  
                ps.setString(6,((HSSFCell)cellStoreArrayList.get(5)).toString());  
                count= ps.executeUpdate();  
               // System.out.print(((HSSFCell)cellStoreArrayList.get(5)).toString() + "\t");  
        }  
        %>
        <%!
        PreparedStatement ps=null;
        public static ArrayList readExcelFile(String filename) {
            ArrayList cellArrayListHolder=new ArrayList();
            ArrayList cellStoreArrayList=new ArrayList();
            ArrayList test = null;

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

            } catch(Exception e) { }

            return cellStoreArrayList;
        }
        %>
        <% if(count>0) { %>  
            Following details from Excel file have been inserted in student table of database  
            <table>  
                <tr>  
                    <th>Subject ID</th>  
                    <th>Subject Name</th>  
                    <th>Faculty</th>  
                    <th>Date</th>  
                    <th>Time</th>  
                    <th>Class</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>  
                        <td><%=((HSSFCell)cellStoreArrayList.get(3)).toString() %></td>  
                        <td><%=((HSSFCell)cellStoreArrayList.get(4)).toString() %></td>  
                        <td><%=((HSSFCell)cellStoreArrayList.get(5)).toString() %></td>
                    </tr>  
                <%} }%> 
            </table> 
        </div>
    </body>
</html>

Here is the stacktrace for the error:

org.apache.jasper.JasperException: An exception occurred processing JSP page /admin_timetable_upload.jsp at line 80

77: for (int i=1;i < dataHolder.size(); i++) 
78: {  
79:         System.out.println("dataHolder.size()="+dataHolder.get(i));
80:        cellStoreArrayList=(ArrayList)dataHolder.get(i);  
81:         ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());  
82:         ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());  
83:         ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());


Stacktrace:
    org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:575)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:477)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:395)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:339)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
root cause

java.lang.ClassCastException: org.apache.poi.hssf.usermodel.HSSFCell cannot be cast to java.util.ArrayList
    org.apache.jsp.admin_005ftimetable_005fupload_jsp._jspService(admin_005ftimetable_005fupload_jsp.java:199)
    org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:439)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:395)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:339)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:731)

How do I solve this?

Upvotes: 1

Views: 758

Answers (1)

Lucas Cegatti
Lucas Cegatti

Reputation: 21

You don't need to cast this:

cellStoreArrayList = (ArrayList) dataHolder.get(i);

into an ArrayList, it already returns an HSSFCell, so you would just do:

HSSFCell cell = dataHolder.get(i);

Upvotes: 2

Related Questions