xls上传，导入！！！！！利用struts的FormFile，并导入插入数据库

来源：互联网发布：淘宝卖家规则大全2017 编辑：程序博客网时间：2024/05/29 16:03

操作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">
<struts-config>
<data-sources />
<form-beans >
<form-bean name="uploadForm" type="com.alihoo.struts.form.UploadForm" />
</form-beans>
<global-exceptions />
<global-forwards >
<forward name="import_data_error" path="/importJsp.jsp" />
<forward name="success" path="/success.jsp" />
</global-forwards>
<action-mappings >
<action
attribute="uploadForm"
name="uploadForm"
path="/importdata"
scope="request"
type="com.alihoo.struts.action.ImportdataAction"
validate="false" />
</action-mappings>
<message-resources parameter="com.alihoo.struts.ApplicationResources" />
</struts-config>
因为是上传文件，request应该是得不到数据的，所以用了一个UploadForm，其中只有一个成员FormFile theFile.注意，theFile不是随便命名的，是跟上传页面中<input type="file" name="theFile">的name一样的。其中UploadForm代码如下：
package com.alihoo.struts.form;
import org.apache.struts.action.ActionForm;
import org.apache.struts.upload.FormFile;
public class UploadForm extends ActionForm ...{
private FormFile theFile;
public FormFile getTheFile() ...{
return theFile;
}
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;
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;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Cell;
import jxl.CellType;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
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;
import com.alihoo.struts.form.UploadForm;
/** *//**
* MyEclipse Struts
* Creation date: 04-21-2008
*
* XDoclet definition:
* @struts.action path="/importdata" name="uploadForm" scope="request"
*/
public class ImportdataAction extends Action ...{
/**//*
* Generated Methods
*/
/** *//**
* Method execute
* @param mapping
* @param form
* @param request
* @param response
* @return ActionForward
*/
public ActionForward execute(ActionMapping mapping, ActionForm form,
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文件
...{
System.out.println("类型不对");
return mapping.findForward("import_data_error");
}
else
...{
try ...{
if(!importData(xlsFile.getInputStream()))
...{
return mapping.findForward("import_data_error");
}
} catch (FileNotFoundException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
return mapping.findForward("import_data_error");
} 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)
...{
/**//*连接数据库 */
String url = "jdbc:mysql://localhost/exceltest";
String userName = "root";
String password = "850522";
String sql = null;
Connection conn = null;
Statement stmt = null;
try ...{
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) ...{
System.err.print("classNotFoundException");
e.printStackTrace();
}
try ...{
conn = DriverManager.getConnection(url, userName, password);
} catch (SQLException e1) ...{
// TODO Auto-generated catch block
e1.printStackTrace();
}
/**//*获得xls数据,并导入*/
try...{
jxl.Workbook rwb = Workbook.getWorkbook(is);
/**//*这里假设有多个sheets，若只有一个，则下面没必要用for，直接get（0）*/
int sheets = rwb.getNumberOfSheets();
for(int i=0;i<sheets;i++)
...{
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++)
...{
PreparedStatement pstmInsert = conn.prepareStatement("insert into exceltable values(?,?,?)");
for(int column=0;column<columnNum;column++)
...{
Cell crl=rs.getCell(column,row);
if(crl.getType()==CellType.NUMBER)
...{
System.out.println(column+1);
NumberCell numc = (NumberCell)crl;
pstmInsert.setInt(column+1,(int)numc.getValue());
}
else
...{
System.out.println(column+1);
pstmInsert.setString(column+1, crl.getContents());
}
}
System.out.println("************");
pstmInsert.executeUpdate();
}
}
rwb.close();
}
catch (Exception e)
...{
e.printStackTrace();
return false;
}
finally ...{
try ...{
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
is.close();/////////关闭输入流
} catch (SQLException e) ...{
e.printStackTrace();
} catch (IOException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return true;
}
}

阅读全文

0 0