利用struts的FormFile上传excel文件，并导入插入数据库

来源：互联网发布：js实现滚动字幕编辑：程序博客网时间：2024/05/22 14:18

操作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 >

</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;

}

差不多就这样吧，不懂q我，哈哈~