JSP 通过Servlet将excel数据导入SQL

JSP 通过Servlet将excel数据导入SQL2010-08-24 20:38首先声明，这资料是我自己根据网上资料改编，代码有大幅度的改动，是自己所写.在编程过程中，有时候需要将excel里德数据导入到数据库，作为一个新手，这个问题困扰了我很久，最近，我终于找到了一篇还算合适的资料，但是错误很多，前后不接，我看了后，根据这篇资料的思想，模拟做出来一个实例，当然，这只是我做的一个例子，我本来的要求还有很多，要用到框架去做等等。

但是，我会慢慢的修改过来，在此，将这个简单的例子写在博客里，供大家参考:1> 在网上下载jxl.jar,这个JAR包用于Java操作excel,下载后，将这个包复制到工程Webroot下的WEB_INF下的lib中，或是在工程中导入jxl.jar包。

2> 准备excel文件,如图所示:3> 数据库建表注意:我使用的是SQL Server 2005,如果你的数据库与我不同，则修改db.java 里的常量数据库名:EducationNET 表名:mynewsid int 主键自动增长title varchar(50)author varchar(50)acontent varchar(50)4> 写好导入数据JSP<form action="wct" method="post">选择文件1:<input type="file" name="excel" /><input type="submit" value="导入" /></form>5>db.java 用于连接数据库public class db {public Connection connstr = null;public ResultSet res = null;public PreparedStatement prase = null;static private String strUrl ="jdbc:sqlserver://localhost:1433;DataBaseName=EducationNET";static private String strUser = "sa";static private String strPwd = "123";public db(){try{Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");connstr = DriverManager.getConnection(strUrl, strUser, strPwd);}catch (SQLException ex){System.out.println(ex.getMessage() + "路径错误");}catch (ClassNotFoundException ex){System.out.println(ex.getMessage() + "驱动错误");}finally{System.out.println("成功了");}}public ResultSet executeQuery(String sql) throws Exception{ Statement stmt = connstr.createStatement();res = stmt.executeQuery(sql);return res;}public PreparedStatement dosql(String sql) throws Exception{ prase = connstr.prepareStatement(sql);return prase;}}6>excel.java 用于读取excel数据import java.io.File;import java.io.FileInputStream;import java.io.InputStream;import java.util.*;import jxl.Sheet;import jxl.Workbook;public class excel {public List addCustomerAssign(File file){List ls=new ArrayList();jxl.Workbook rwb = null;try{InputStream is = new FileInp utStream(file);rwb = Workbook.getWorkbook(is);Sheet rs = rwb.getSheet(0);int rsRows = rs.getRows();for(int i=1;i <rsRows;i++){//如第一行为属性项则从第二行开始取数据(int i=0 ;i <rsRows;i++)String cell2=rs.getCell(1,i).getCon tents()+" ";String cell3=rs.getCell(2,i).getCon tents()+" ";String cell2=rs.getCell(3,i).getCon tents()+" ";String cell3=rs.getCell(4,i).getCon tents()+" ";String cell2=rs.getCell(5,i).getCon tents()+" ";String cell3=rs.getCell(6,i).getCon tents()+" ";String cell2=rs.getCell(7,i).getCon tents()+" ";String cell3=rs.getCell(8,i).getCon tents()+" ";String cell2=rs.getCell(9,i).getCon tents()+" ";String cell3=rs.getCell(10,i).getCo ntents()+" ";if(cell1!=null&&!cell1.equals(" ")&&cell2!=null&&!cell3.equals(" ")){mynews ms=new mynews();ms.setTitle(rs.getCell(0,i).get Contents());ms.setAuthor(rs.getCell(1,i).ge tContents());ms.setAcontent(rs.getCell(2,i). getContents());ls.add(ms);}}}catch(Exception e){e.printStackTrace();}finally{rwb.close();}return ls;}}7>into.javapublic class into {String sql = "insert into mynews(title,author,acontent) values(?,?,?)"; db data=new db();public PreparedStatement prase = null;public Boolean insertexcel(mynews ms) {Boolean jiaqi = false;try {prase = data.dosql(sql);prase.setString(1, ms.getTitle());prase.setString(2, ms.getAuthor());prase.setString(3, ms.getAcontent());prase.executeUpdate();jiaqi = true;} catch (Exception e) {e.printStackTrace();}return jiaqi;}}8> 实体类mynews.javapublic class mynews {private int id;private String title;private String author;private String acontent;public String getAuthor() {return author;}public void setAuthor(String author) {this.author = author;}public String getAcontent() {return acontent;}public void setAcontent(String acontent) {this.acontent = acontent;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}}9>建立servlet tosqlservlet改动它的doPost方法public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {into in=new into();excel ex=new excel();String path=newString(request.getParameter("excel").getBytes("ISO8859-1"),"UTF-8");System.out.println(path);。