操作excel貌似有两个比较好的开源项目,这里用的是JAVA EXCEL API,参考文章如下
http://www.chinaitpower.com/A200508/2005-08-10/190567.html
首先就下载所需的包了
http://www.andykhan.com/jexcelapi/jexcelapi_2_6_6.tar.gz
解压后,只要里面的一个jxl.jar,加入到工程的路径中。具体的操作和部分API参考文章
http://www-128.ibm.com/developerworks/cn/java/l-javaExcel/index.html
下面是从上传导入的核心代码。
上传页面的jsp如下:
<%
@ page language
=
"
java
"
contentType
=
"
text/html; charset=GBK
"
pageEncoding
=
"
GBK
"
%>
<!
DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"
>
<
html
>
<
head
>
<
script
language
="javascript"
>
function
myFormCheck(theform)
{
if
(theform.theFile.value
==
""
)
{
alert(
"
请点击浏览按钮,选择您要上传的文件!
"
)
theform.theFile.focus;
return
(
false
);
}
else
{
str
=
theform.theFile.value;
strs
=
str.toLowerCase();
lens
=
strs.length;
extname
=
strs.substring(lens
-
4
,lens);
if
(extname
!=
"
.xls
"
)
{
alert(
"
请选择excel文件!
"
)
return
(
false
);
}
}
}
</
script
>
<
meta
http-equiv
="Content-Type"
content
="text/html; charset=GBK"
>
<
title
>
Insert title here
</
title
>
</
head
>
<
body
>
<
form
name
="uploadform"
action
="importdata.do"
enctype
="multipart/form-data"
method
=post
onsubmit
="return myFormCheck(this)"
>
<
input
type
="file"
name
="theFile"
>
<
input
type
="submit"
value
="导入"
>
</
form
>
</
body
>
</
html
>
其中用js从客户端判断了下文件的类型。在服务端同样判断了类型,这是防止某些人绕过js,直接上传非excel文件。
其中调用action importdata.do ,在struts-config.xml配置如下:
<?
xml version="1.0" encoding="UTF-8"
?>
<!
DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.2//EN" "http://struts.apache.org/dtds/struts-config_1_2.dtd"
>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
struts-config
>
<
data-sources
/>
<
form-beans
>
<
form-bean
name
="uploadForm"
type
="com.alihoo.struts.form.UploadForm"
/>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
</
form-beans
>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
global-exceptions
/>
<
global-forwards
>
<
forward
name
="import_data_error"
path
="/importJsp.jsp"
/>
<
forward
name
="success"
path
="/success.jsp"
/>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
</
global-forwards
>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
action-mappings
>
<
action
attribute
="uploadForm"
name
="uploadForm"
path
="/importdata"
scope
="request"
type
="com.alihoo.struts.action.ImportdataAction"
validate
="false"
/>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
</
action-mappings
>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<
message-resources
parameter
="com.alihoo.struts.ApplicationResources"
/>
</
struts-config
>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
因为是上传文件,request应该是得不到数据的,所以用了一个UploadForm,其中只有一个成员FormFile theFile.注意,theFile不是随便命名的,是跟上传页面中<input type="file" name="theFile">的name一样的。其中UploadForm代码如下:
package
com.alihoo.struts.form;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
import
org.apache.struts.action.ActionForm;
import
org.apache.struts.upload.FormFile;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
public
class
UploadForm
extends
ActionForm
...
{
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
private FormFile theFile;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public FormFile getTheFile() ...{
return theFile;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public void setTheFile(FormFile theFile) ...{
this.theFile = theFile;
}
}
为了方面测试,都没分什么dao层之类,全部放在aciton中了。这里数据表有exceltable有三个字段,一个id,int型自增,一个username,一个password都是字符型。这里假设excel的数据也是三个字段的,即已经指定id。一般来说不应在excel指定id,里面指定了id是主键,所以有重复记录的时候就终止了,这里都没判断是否有重复id,太麻烦了。
那个aciton的具体代码如下:
/**/
/*
* Generated by MyEclipse Struts
* Template path: templates/java/JavaClass.vtl
*/
package
com.alihoo.struts.action;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
import
java.io.FileInputStream;
import
java.io.FileNotFoundException;
import
java.io.IOException;
import
java.io.InputStream;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.PreparedStatement;
import
java.sql.SQLException;
import
java.sql.Statement;
import
java.util.List;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
import
javax.servlet.http.HttpServletRequest;
import
javax.servlet.http.HttpServletResponse;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
import
jxl.Cell;
import
jxl.CellType;
import
jxl.NumberCell;
import
jxl.Sheet;
import
jxl.Workbook;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
import
org.apache.struts.action.Action;
import
org.apache.struts.action.ActionForm;
import
org.apache.struts.action.ActionForward;
import
org.apache.struts.action.ActionMapping;
import
org.apache.struts.upload.FormFile;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
import
com.alihoo.struts.form.UploadForm;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
/** */
/**
* MyEclipse Struts
* Creation date: 04-21-2008
*
* XDoclet definition:
* @struts.action path="/importdata" name="uploadForm" scope="request"
*/
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
public
class
ImportdataAction
extends
Action
...
{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//*
* Generated Methods
*/
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* Method execute
* @param mapping
* @param form
* @param request
* @param response
* @return ActionForward
*/
public ActionForward execute(ActionMapping mapping, ActionForm form,
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
HttpServletRequest request, HttpServletResponse response) ...{
UploadForm uploadForm = (UploadForm) form;// TODO Auto-generated method stub
FormFile xlsFile=uploadForm.getTheFile();
System.out.println(xlsFile.getFileName());
System.out.println(xlsFile.getContentType());
System.out.println(xlsFile.getFileSize());
if(!xlsFile.getContentType().equals("application/vnd.ms-excel"))不是excel文件
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
System.out.println("类型不对");
return mapping.findForward("import_data_error");
}
else
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
try ...{
if(!importData(xlsFile.getInputStream()))
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
return mapping.findForward("import_data_error");
}
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
} catch (FileNotFoundException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
return mapping.findForward("import_data_error");
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
} catch (IOException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
return mapping.findForward("import_data_error");
}
return mapping.findForward("success");
}
}
private boolean importData(InputStream is)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//*连接数据库 */
String url = "jdbc:mysql://localhost/exceltest";
String userName = "root";
String password = "850522";
String sql = null;
Connection conn = null;
Statement stmt = null;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
try ...{
Class.forName("com.mysql.jdbc.Driver");
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
} catch (ClassNotFoundException e) ...{
System.err.print("classNotFoundException");
e.printStackTrace();
}
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
try ...{
conn = DriverManager.getConnection(url, userName, password);
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
} catch (SQLException e1) ...{
// TODO Auto-generated catch block
e1.printStackTrace();
}
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//*获得xls数据,并导入*/
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
try...{
jxl.Workbook rwb = Workbook.getWorkbook(is);
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/**//*这里假设有多个sheets,若只有一个,则下面没必要用for,直接get(0)*/
int sheets = rwb.getNumberOfSheets();
for(int i=0;i<sheets;i++)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
Sheet rs = rwb.getSheet(i);
System.out.println("Sheet Name:"+rs.getName());
int columnNum=rs.getColumns();
int rowNum=rs.getRows();
System.out.println("columnNum:"+columnNum);
for(int row=0;row<rowNum;row++)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
PreparedStatement pstmInsert = conn.prepareStatement("insert into exceltable values(?,?,?)");
for(int column=0;column<columnNum;column++)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
Cell crl=rs.getCell(column,row);
if(crl.getType()==CellType.NUMBER)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
System.out.println(column+1);
NumberCell numc = (NumberCell)crl;
pstmInsert.setInt(column+1,(int)numc.getValue());
}
else
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
System.out.println(column+1);
pstmInsert.setString(column+1, crl.getContents());
}
}
System.out.println("************");
pstmInsert.executeUpdate();
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}
rwb.close();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}
catch (Exception e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
e.printStackTrace();
return false;
}
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
finally ...{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
try ...{
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
is.close();/关闭输入流
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
} catch (SQLException e) ...{
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
e.printStackTrace();
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
} catch (IOException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return true;
}
}
差不多就这样吧,不懂q我,哈哈~