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

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

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