Java 使用jsp导入excel数据并存入数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23473262/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-13 23:15:18 来源:igfitidea点击:
Import excel data and store it in database using jsp
提问by user3604174
This is my index file
这是我的索引文件
<form action="DataImport" method="post>"
<input name="filename" type="file" size="20">
<input type="submit" value="Import">
DataImport.java(Servlet)
数据导入.java(Servlet)
public class DataImport extends HttpServlet
{
private static final long serialVersionUID = 1L;
public DataImport()
{
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
}
@SuppressWarnings("rawtypes")
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
try
{
String file = request.getParameter("file");
String fileName1=""+file+"";
ArrayList dataHolder = read.readExcelFile(fileName1);
Connection con = EmployeeDao1.con11();
System.out.println("Connection :"+con);
@SuppressWarnings("unused")
Statement sql_statement = con.createStatement();
String jdbc_insert_sql = "INSERT INTO emp4"
+ "(Id,name,salary) VALUES"
+ "(?,?,?)";
PreparedStatement ps = con.prepareStatement(jdbc_insert_sql);
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());
ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());
count= ps.executeUpdate();
}
if(count>0)
{
for (int j=1;j < dataHolder.size(); j++)
{
cellStoreArrayList=(ArrayList)dataHolder.get(j);
System.out.println(((HSSFCell)cellStoreArrayList.get(0)).toString());
System.out.println(((HSSFCell)cellStoreArrayList.get(1)).toString());
System.out.println(((HSSFCell)cellStoreArrayList.get(2)).toString());
}
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
this my read.java class to read the excel file
这是我的 read.java 类来读取 excel 文件
public class read {
@SuppressWarnings({"unchecked", "rawtypes"})
public static ArrayList readExcelFile(String fileName) {
ArrayList cellArrayLisstHolder = new ArrayList();
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("D:\LOGA\Book1.xlsx"));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator cells = row.cellIterator();
ArrayList cellStoreArrayList = new ArrayList();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
cellStoreArrayList.add(cell);
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_STRING) {
HSSFRichTextString strData = cell.getRichStringCellValue();
System.out.println("String data=" + strData.getString());
} else if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
double data = cell.getNumericCellValue();
System.out.println("Numeric data=" + data);
}
}
cellArrayLisstHolder.add(cellStoreArrayList);
}
} catch (Exception e) {
e.printStackTrace();
}
return cellArrayLisstHolder;
}
}
when i try to execute the servlet it shows the following error
当我尝试执行 servlet 时，它显示以下错误
SEVERE: Servlet.service() for servlet DataImport threw exception
java.lang.ClassNotFoundException: org.apache.poi.poifs.filesystem.POIFSFileSystem
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1358)
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1204)
at pack.DataImport.doPost(DataImport.java:47)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at java.lang.Thread.run(Unknown Source)
Can any one solve this problem?
and i have included all my poi and sql jar files, also excel file(Book1.xslx) is in D:\\LOGAonly...
任何人都可以解决这个问题吗？我已经包含了我所有的 poi 和 sql jar 文件，excel 文件（Book1.xslx）也D:\\LOGA仅在...
回答by niiraj874u
It looks like you've forgotten to include the POI library on the CLASSPATH (e.g. It's not referenced by your web app). You can include this lib under WEB-INF/lib (in your WAR archive or in the exploded directory format) or it can sit elsewhere in your app server/web server CLASSPATH
看起来您忘记在 CLASSPATH 中包含 POI 库（例如，您的 Web 应用程序未引用它）。您可以将此库包含在 WEB-INF/lib 下（在您的 WAR 存档或分解目录格式中），或者它可以位于您的应用程序服务器/网络服务器 CLASSPATH 中的其他位置
回答by muhammed aslam
<form action="DataImport" method="post>"
There is small mistake here
这里有一个小错误
the quotes are out of form tag
引号不符合格式标签
method="post>"
just change it
改变它
<form action="DataImport" method="post">
and also change the servlet entry this
并且还要更改 servlet 条目
String file = request.getParameter("file");
to this
对此
String file = request.getParameter("filename");
回答by Debasish Ghosh
This is my solution ....working fine file upload.jsp
这是我的解决方案......工作正常文件upload.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<jsp:useBean id="excel" class="com.ImportExcel"/>
<!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=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="index.jsp" method="post" enctype="multipart/form-data">
<input type="file" name="filename">
<input type="submit" value="Submit">
</form>
</body>
</html>
index.jsp
索引.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<%@ 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="org.apache.poi.xssf.usermodel.*" %>
<%@ 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" %>
<%@ page import="org.apache.poi.ss.usermodel.Cell" %>
<%@ page import ="org.apache.poi.ss.usermodel.Row"%>
<%@ page import="org.apache.poi.ss.usermodel.Sheet" %>
<%@ page import="org.apache.poi.ss.usermodel.Workbook" %>
<%@ page import ="org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import="com.oreilly.servlet.MultipartRequest" %>
<!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=UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
ArrayList cellArrayListHolder = new ArrayList ();
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","root");
String Path="upload";
String absolutepath=getServletContext().getRealPath(Path);
MultipartRequest mr=new MultipartRequest(request,absolutepath);
String filename=mr.getOriginalFileName("filename");
FileInputStream myinput = new FileInputStream(new File(absolutepath+"/"+filename));
Workbook workbook = new XSSFWorkbook(myinput);
/*Create a workbook using myfilesystem */
/* Get the first sheet from workbook */
Sheet firstSheet = workbook.getSheetAt(0);
/*We need to something to iterate through the cell */
Iterator<Row> iterator = firstSheet.iterator();
while (iterator.hasNext())
{
XSSFRow nextRow =(XSSFRow)iterator.next();
ArrayList rowarrylist=new ArrayList();
Iterator<Cell> cellIterator = nextRow.cellIterator();
while (cellIterator.hasNext()) {
XSSFCell cell = (XSSFCell)cellIterator.next();
rowarrylist.add(cell);
}
cellArrayListHolder.add(rowarrylist);
}
out.println(cellArrayListHolder);
ArrayList rowarrylist=null;
PreparedStatement st=con.prepareStatement("INSERT INTO export VALUES (?,?,?,?)");
for(int i=1;i<cellArrayListHolder.size();i++)
{
rowarrylist=(ArrayList)cellArrayListHolder.get(i);
st.setString(1,rowarrylist.get(0)
.toString());
st.setString(2,rowarrylist.get(1).toString());
st.setString(3,rowarrylist.get(2).toString());
st.setString(4,rowarrylist.get(3).toString());
st.executeUpdate();
}
}
catch(Exception e)
{
e.getMessage();
out.println(e.getMessage());
}
%>
</body>
</html>
