利用struts的FormFile上傳excel檔案，并導入插入資料庫

鎿嶄綔excel璨屼技鏈変袱涓瘮杈冨ソ鐨勫紑婧愰」鐩紝杩欓噷鐢ㄧ殑鏄疛AVA EXCEL API锛屽弬鑰冩枃绔犲涓?

http://www.chinaitpower.com/A200508/2005-08-10/190567.html

棣栧厛灏變笅杞芥墍闇€鐨勫寘浜?

http://www.andykhan.com/jexcelapi/jexcelapi_2_6_6.tar.gz

瑙ｅ帇鍚庯紝鍙閲岄潰鐨勪竴涓猨xl.jar锛屽姞鍏ュ埌宸ョ▼鐨勮礬寰勪腑銆傚叿浣撶殑鎿嶄綔鍜岄儴鍒咥PI鍙傝€冩枃绔?

http://www-128.ibm.com/developerworks/cn/java/l-javaExcel/index.html

涓嬮潰鏄粠涓婁紶瀵煎叆鐨勬牳蹇冧唬鐮併€?

涓婁紶椤甸潰鐨刯sp濡備笅锛?

<% @聽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 >

鍏朵腑鐢╦s浠庡鎴風鍒ゆ柇浜嗕笅鏂囦歡鐨勭被鍨嬨€傚湪鏈嶅姟绔悓鏍峰垽鏂簡绫誨瀷锛岃繖鏄槻姝㈡煇浜涗漢缁曡繃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搴旇鏄緱涓嶅埌鏁版嵁鐨勶紝鎵€浠ョ敤浜嗕竴涓猆ploadForm锛屽叾涓彧鏈変竴涓垚鍛楩ormFile theFile.娉ㄦ剰锛宼heFile涓嶆槸闅忎究鍛藉悕鐨勶紝鏄窡涓婁紶椤甸潰涓?lt;input type="file" name="theFile">鐨刵ame涓€鏍風殑銆傚叾涓璘ploadForm浠ｇ爜濡備笅锛?

聽

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;

聽聽聽聽}

}

聽

涓轟簡鏂歸潰娴嬭瘯锛岄兘娌″垎浠€涔坉ao灞備箣绫夥紝鍏ㄩ儴鏀懼湪aciton涓簡銆傝繖閲屾暟鎹〃鏈塭xceltable鏈変笁涓瓧娈碉紝涓€涓猧d锛宨nt鍨嬭嚜澧烇紝涓€涓猽sername锛屼竴涓猵assword閮芥槸瀛楃鍨嬨€傝繖閲屽亣璁緀xcel鐨勬暟鎹篃鏄笁涓瓧娈電殑锛屽嵆宸茬粡鎸囧畾id銆備竴鑸潵璇翠笉搴斿湪excel鎸囧畾id锛岄噷闈㈡寚瀹氫簡id鏄富閿紝鎵€浠ユ湁閲嶅璁闆綍鐨勬椂鍊欏氨缁堟浜嗭紝杩欓噷閮芥病鍒ゆ柇鏄惁鏈夐噸澶峣d锛屽お楹葷儲浜嗐€?

閭ｄ釜aciton鐨勫叿浣撲唬鐮佸涓嬶細

聽

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;

public 聽 class 聽ImportdataAction聽 extends 聽Action聽 ... {

聽聽聽聽

聽聽聽聽

聽聽聽聽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();

聽聽聽聽聽聽聽聽}

聽聽聽聽聽聽聽聽

聽聽聽聽聽聽聽聽

聽聽聽聽聽聽聽聽try...{

聽聽聽聽聽聽聽聽聽聽聽聽jxl.Workbook聽rwb聽=聽Workbook.getWorkbook(is);

聽聽聽聽聽聽聽聽聽聽聽聽

聽聽聽聽聽聽聽聽聽聽聽聽

聽聽聽聽聽聽聽聽聽聽聽聽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鎴戯紝鍝堝搱~

聽

聽

聽