JSP 通过Servlet将excel数据导入SQL_如何servlet插入数据到sql已有主键-CSDN博客

1> 在网上下载jxl.jar,这个JAR包用于Java操作excel,下载后，将这个包复制到工程Webroot下的WEB_INF下的lib中，或是在工程中导入jxl.jar包。

2> 准备excel文件,如图所示:

3> 数据库建表

注意:我使用的是SQL Server 2005,如果你的数据库与我不同，则修改db.java里的常量!!!

数据库名:EducationNET 表名:mynews

id int 主键自动增长

title varchar(50)

author varchar(50)

acontent varchar(50)

4> 写好导入数据JSP

   <form action="tosqlservlet" 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   FileInputStream(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   cell1=rs.getCell(0,i).getContents()+" ";
                String   cell2=rs.getCell(1,i).getContents()+" ";
                String   cell3=rs.getCell(2,i).getContents()+" ";
                   if(cell1!=null&&!cell1.equals(" ")&&cell2!=null&&!cell3.equals(" "))
                   {
                    mynews ms=new mynews();
                    ms.setTitle(rs.getCell(0,i).getContents());
                    ms.setAuthor(rs.getCell(1,i).getContents());
                    ms.setAcontent(rs.getCell(2,i).getContents());
                    ls.add(ms);
                   }
               }
         }catch(Exception   e){
             e.printStackTrace();
         }
         finally{
             rwb.close();
         }
     return ls;
}
}

7> into.java

public 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.java

public 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=new String(request.getParameter("excel").getBytes("ISO8859-1"),"UTF-8");//红色字体改为：gbk即可解决问题
     System.out.println(path);
     File file=new File(path);
     List ls=ex.addCustomerAssign(file);
     Iterator iter=ls.iterator();
     while(iter.hasNext())
     {
      mynews ms=(mynews)iter.next();
      if(in.insertexcel(ms))
       System.out.println("成功");
      else
       System.out.println("失败");
     }
}

运行后，后台会输出成功，打开数据库表，则会看见excel里德数据已经成功导入数据库.

运行前台报错：

解决方法：

web.xml中配置：

HTTP Status 500 -

--------------------------------------------------------------------------------

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

java.lang.NullPointerException
com.hua.excel.excel.addCustomerAssign(excel.java:46)
com.hua.action.tosqlservlet.doPost(tosqlservlet.java:65)
javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

note The full stack trace of the root cause is available in the Apache Tomcat/6.0.35 logs.

--------------------------------------------------------------------------------

Apache Tomcat/6.0.35
张判例 21:04:06
看下后台的信息
海浪 21:04:58
C:\Documents and Settings\Administrator\????\Book1.xls
java.io.FileNotFoundException: C:\Documents and Settings\Administrator\????\Book1.xls (系统找不到指定的路径。)

将excel文件放在盘符中，然后运行。

成功