个人中心
gpt4 book ai didi

mysql - 如何使用jxl将excel表格数据导入mysql数据库？

转载 作者：行者123 更新时间：2023-11-30 00:10:08 25 4
gpt4 key购买 nike

我正在尝试使用 jxl 将数据从 Excel 工作表导入到 mysql 数据库。我需要从本地计算机选择一个文件并读取其内容并存储在数据库中。我制作了一个html页面来选择.XLS类型的文件并向jsp页面发送请求。在jsp页面中我正在执行所有读取和复制操作。下面是jsp页面代码:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@page import="java.io.*,java.sql.*,java.util.*,jxl.*, jxl.read.biff.BiffException"%>
<%@page import="javax.servlet.*" %>
<%@page import="javax.servlet.http.*" %>
 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title></title>
</head>
<body>
<%
        ArrayList<String> list_A=new ArrayList<String>();
        ArrayList<String> list_B=new ArrayList<String>();
        ArrayList<String> list_C=new ArrayList<String>();
        ArrayList<String> list_D=new ArrayList<String>();
        ArrayList<String> list_E=new ArrayList<String>();
        ArrayList<String> list_F=new ArrayList<String>();
        String T=request.getParameter("T");
        String X=request.getParameter("X");
        String V=request.getParameter("V");
        String f_name=request.getParameter("filename");

    Cell rowData[] = null;
    int rowCount = 0;
    int columnCount = 0;        
    String filename="C:/loc/loc1/loc2/"+f_name;
    WorkbookSettings ws = new WorkbookSettings();
    Workbook workbook = Workbook.getWorkbook(new File(filename), ws);
    Sheet s = workbook.getSheet(0);
    rowCount = s.getRows();
    columnCount = s.getColumns();
    String text="";
    for(int i = 1; i < rowCount; i++){
        rowData = s.getRow(i);
        if(rowData[0].getContents().length() != 0){ 
            for(int j = 0; j < columnCount ;j++){
                if(rowData[1].getContents().length()==0){
                text=rowData[0].getContents();  
                }
                else{
             switch(j){
                case 0:

                    list_A.add(rowData[j].getContents());
                    list_B.add(text);
                    break;
                case 1:

                    list_F.add(rowData[j].getContents());
                    break;
                case 2:

                    list_C.add(rowData[j].getContents());
                    break;
                case 3:
                    list_D.add(rowData[j].getContents());
                    break;
                case 4:
                    list_E.add(rowData[j].getContents());
                    break;
                   }

                }
                }
             }
    }
workbook.close(); 
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
PreparedStatement pst1 = null;
ResultSet rs1 = null;
int status=0;
String some="";
String somevalue="";
String somemore="";
String somemorevalue="";    
String evenmore="";
String somethingmore="";
final String db_url = "jdbc:mysql://localhost/mydatabase";
final String user = "I";
final String passwd = "hidden";
try {
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection(db_url, user, passwd);
    pst = conn.prepareStatement("insert into atable(five columns name seperated by comma)values(?,?,?,?,?)");                                    
    for(int i=0;i<list_A.size();i++){
    some=list_A.get(i).toString();
    somevalue=list_B.get(i).toString();
    somemore=list_C.get(i).toString();
    somemorevalue=list_D.get(i).toString();
    evenmore=list_E.get(i).toString();
    somethingmore=list_F.get(i).toString();
    pst.setString(1,some);
    pst.setString(2,somevalue);
    pst.setString(3,some more);
    pst.setString(4,some more value);
    pst.setString(5,even more);
    pst.executeUpdate();
    pst1=conn.prepareStatement("insert into table(column names here seperated by comma)values(?,?)");
    pst1.setString(1,something);
    pst1.setString(2,somethingmore);
    pst1.executeUpdate();

}
}catch(SQLException e){
    if(conn!=null){
        conn.rollback();
        status=1;
        System.out.println("Rolling Back!!!");
    }
    e.printStackTrace();
}
finally{
    try{
        if(pst1!=null){
            pst1.close();
        }
        if(pst!=null){
            pst.close();
        }
        if(conn!=null){
            conn.close();
        }
    }catch(Exception e){
        e.printStackTrace();
    }
}
RequestDispatcher rd=request.getRequestDispatcher("page.jsp");
rd.include(request,response);
if(status==0){
    out.println("Data is successfully inserted into the database");
}
else
{
    out.println("Problem File reading into the database");
}

%>

我得到的错误是:

SEVERE: Servlet.service() for servlet [jsp] in context with path [/project] threw exception [javax.servlet.ServletException: jxl.read.biff.BiffException: Unable to recognize OLE stream] with root cause
jxl.read.biff.BiffException: Unable to recognize OLE stream
    at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
    at jxl.read.biff.File.<init>(File.java:127)
    at jxl.Workbook.getWorkbook(Workbook.java:221)
    at org.apache.jsp.excelimport_jsp._jspService(excelimport_jsp.java:97)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

Please suggest me where I am wrong.?

最佳答案

如果使用windows使用，ODBC数据源。您可以在此处定义 DSN 并将其映射到 Excel 工作表。

现在您只需读取数据并将其写入 mysql 数据库即可。

注意:为了简单起见

  • 从 Excel 工作表中读取数据后，您可以以 sql 格式逐行打印输出。

例如，您的 Excel 工作表如下所示。

ABCXYZPQR

所以，当你使用 pritinin 时，你可以这样做

SOP(插入(....)值('A'，'B'，'C'))

并将此文件另存为 *.sql 并立即在您的 my sql 数据库中运行。

关于mysql - 如何使用jxl将excel表格数据导入mysql数据库？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/24136360/

25 4 0
文章推荐： mysql - 如何从 ubuntu 终端创建 .sql 文件
文章推荐： MySQL 查询域名和帖子编号
文章推荐： java - 按顺序插入多个数组
文章推荐： java - Mybatis批量选择查询-Java
行者123
个人简介

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

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