个人中心
gpt4 book ai didi

java - 将上传的 Excel 文件中的数据存储在数据库中

转载 作者：行者123 更新时间：2023-11-30 03:04:23 25 4
gpt4 key购买 nike

我有一个代码，通过读取 Excel 文件并将其存储在数据库中来处理上传 Excel 文件。代码如下，它正在编译但出现错误。

请参阅下面的代码:

<!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>

这是错误的堆栈跟踪:

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)

如何解决这个问题？

最佳答案

您不需要转换此内容:

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

进入 ArrayList，它已经返回一个 HSSFCell，所以你只需这样做:

HSSFCell cell = dataHolder.get(i);

关于java - 将上传的 Excel 文件中的数据存储在数据库中，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/35175900/

25 4 0
文章推荐： C++ 新手 : destructor
文章推荐： java - 如何分割输入数据并将其加载到RDD
文章推荐： android - 文本选择句柄似乎没有改变
文章推荐： Android 应用程序不断崩溃与 tabhost
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com