您所在位置：主题：[讨论]excel导入远程数据库问题，请高手帮忙
sunwukong
[专家分：0] 发布于 2010-05-20 15:59:00
tomysql.jsp
<%@ page contentType="text/html; charset=GB2312" %>
<html>
<head>
<title>文件上载</title>
</head>
<body>
<form action="exceltoexcel" method="post">
选择文件1:<input type="file" name="excel" />
<input type="submit" value="导入" />
</form>
</body>
</html>
db.java
package tomysql;
import java.sql.*;
public class db {
public Connection connstr = null;
public ResultSet res = null;
public PreparedStatement prase=null;
static private String strUrl = "jdbc:mysql://localhost:3306/wangzhan?useUnicode=true&characterEncoding=gb2312";
static private String strUser = "root";
static private String strPwd = "7820067";
public db()
{
try
{
//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//connstr = DriverManager.getConnection(
//"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=BookStore",
//"sa","7820067");
Class.forName("com.mysql.jdbc.Driver");
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)
{
try{
Statement stmt=connstr.createStatement();
res=stmt.executeQuery(sql);
}
catch(SQLException ex)
{
System.out.println(ex.getMessage());
}
return res;
}
public PreparedStatement dosql(String sql)
{
try
{
prase=connstr.prepareStatement(sql);
}
catch(Exception e)
{
}
return prase;
}
}
excel.java
package tomysql;
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{
//构建Workbook对象, 只读Workbook对象
//直接从本地文件创建Workbook
//从输入流创建Workbook
InputStream is = new FileInputStream(file);
rwb = Workbook.getWorkbook(is);
// String createTime = DateUtil.getDateTime( "yyyy-MM-dd HH:mm ",new Date()).toString();
//Sheet(术语：工作表)就是Excel表格左下角的Sheet1,Sheet2,Sheet3但在程序中
//Sheet的下标是从0开始
//获取第一张Sheet表
Sheet rs = rwb.getSheet(0);
//获取Sheet表中所包含的总列数
// int rsColumns = rs.getColumns();
//获取Sheet表中所包含的总行数
int rsRows = rs.getRows();
//获取指定单元格的对象引用
// rs.getCell(列,行);
for(int i=1;i <rsRows;i++){//如第一行为属性项则从第二行开始取数据(int i=0 ;i <rsRows;i++)
//for(int j=0;j <rsColumns;j++){
//Cell cell = rs.getCell(j,i);
// System.out.print(cell.getContents()+ " ");
// }
//Cell cell = rs.getCell(0,i).getContents()+ " ";
String cell1= rs.getCell(0,i).getContents()+ " ";//序号
String cell7 = rs.getCell(1,i).getContents()+ " ";//公司名称
String cell8 = rs.getCell(1,i).getContents()+ " ";//公司名称
if(cell1!=null&&!cell1.equals( " ")&&cell7!=null&&!cell7.equals( " "))//判断当前行是否为有效行 是插入否找下行
{
mynews ms=new mynews();
ms.setTitle(rs.getCell(0,i).getContents());//1名称
ms.setAuthor(rs.getCell(1,i).getContents());//2法人
ms.setContent(rs.getCell(2,i).getContents());
ls.add(ms);
}
}
}catch(Exception e){
e.printStackTrace();
}
finally{
//操作完成时，关闭对象，释放占用的内存空间
rwb.close();
}
return ls;
}
}
into.java
package tomysql;
import java.util.*;
import java.sql.*;
import java.io.*;
public class into {
String sql="insert into mynews(title,author,content) 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.getContent());
prase.executeUpdate();
jiaqi=true;
}
catch(Exception e)
{
e.printStackTrace();
}
return jiaqi;
}
}
mynews.java
package tomysql;
public class mynews {
private int id;
private String title;
private String author;
private String content;
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
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;
}
}
---------------servlet---------------
tomysqlservlet.java
package tomysql;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class tomysqlservlet extends HttpServlet {
public tomysqlservlet() {
super();
}
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
into in=new into();
excel ex=new excel();
String path=request.getParameter("excel");
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("失败");
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
doGet(request, response);
}
public void init() throws ServletException {
// Put your code here
}
}
我是用这个写的，但是本地上传到远程服务器，说找不到excel文件,应该是需要获取路径什么，求高手帮忙
<%@ page contentType="text/html; charset=GB2312" %>
<html>
<head>
<title>文件上载</title>
</head>
<body>
<form action="exceltoexcel" method="post">
选择文件1:<input type="file" name="excel" />
<input type="submit" value="导入" />
</form>
</body>
</html>
db.java
package tomysql;
import java.sql.*;
public class db {
public Connection connstr = null;
public ResultSet res = null;
public PreparedStatement prase=null;
static private String strUrl = "jdbc:mysql://localhost:3306/wangzhan?useUnicode=true&characterEncoding=gb2312";
static private String strUser = "root";
static private String strPwd = "7820067";
public db()
{
try
{
//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//connstr = DriverManager.getConnection(
//"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=BookStore",
//"sa","7820067");
Class.forName("com.mysql.jdbc.Driver");
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)
{
try{
Statement stmt=connstr.createStatement();
res=stmt.executeQuery(sql);
}
catch(SQLException ex)
{
System.out.println(ex.getMessage());
}
return res;
}
public PreparedStatement dosql(String sql)
{
try
{
prase=connstr.prepareStatement(sql);
}
catch(Exception e)
{
}
return prase;
}
}
excel.java
package tomysql;
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{
//构建Workbook对象, 只读Workbook对象
//直接从本地文件创建Workbook
//从输入流创建Workbook
InputStream is = new FileInputStream(file);
rwb = Workbook.getWorkbook(is);
// String createTime = DateUtil.getDateTime( "yyyy-MM-dd HH:mm ",new Date()).toString();
//Sheet(术语：工作表)就是Excel表格左下角的Sheet1,Sheet2,Sheet3但在程序中
//Sheet的下标是从0开始
//获取第一张Sheet表
Sheet rs = rwb.getSheet(0);
//获取Sheet表中所包含的总列数
// int rsColumns = rs.getColumns();
//获取Sheet表中所包含的总行数
int rsRows = rs.getRows();
//获取指定单元格的对象引用
// rs.getCell(列,行);
for(int i=1;i <rsRows;i++){//如第一行为属性项则从第二行开始取数据(int i=0 ;i <rsRows;i++)
//for(int j=0;j <rsColumns;j++){
//Cell cell = rs.getCell(j,i);
// System.out.print(cell.getContents()+ " ");
// }
//Cell cell = rs.getCell(0,i).getContents()+ " ";
String cell1= rs.getCell(0,i).getContents()+ " ";//序号
String cell7 = rs.getCell(1,i).getContents()+ " ";//公司名称
String cell8 = rs.getCell(1,i).getContents()+ " ";//公司名称
if(cell1!=null&&!cell1.equals( " ")&&cell7!=null&&!cell7.equals( " "))//判断当前行是否为有效行 是插入否找下行
{
mynews ms=new mynews();
ms.setTitle(rs.getCell(0,i).getContents());//1名称
ms.setAuthor(rs.getCell(1,i).getContents());//2法人
ms.setContent(rs.getCell(2,i).getContents());
ls.add(ms);
}
}
}catch(Exception e){
e.printStackTrace();
}
finally{
//操作完成时，关闭对象，释放占用的内存空间
rwb.close();
}
return ls;
}
}
into.java
package tomysql;
import java.util.*;
import java.sql.*;
import java.io.*;
public class into {
String sql="insert into mynews(title,author,content) 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.getContent());
prase.executeUpdate();
jiaqi=true;
}
catch(Exception e)
{
e.printStackTrace();
}
return jiaqi;
}
}
mynews.java
package tomysql;
public class mynews {
private int id;
private String title;
private String author;
private String content;
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
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;
}
}
---------------servlet---------------
tomysqlservlet.java
package tomysql;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class tomysqlservlet extends HttpServlet {
public tomysqlservlet() {
super();
}
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
into in=new into();
excel ex=new excel();
String path=request.getParameter("excel");
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("失败");
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
doGet(request, response);
}
public void init() throws ServletException {
// Put your code here
}
}
我是用这个写的，但是本地上传到远程服务器，说找不到excel文件,应该是需要获取路径什么，求高手帮忙