Question

麻烦大侠们、高手们帮我解决excel表导入mysql数据库中，这是我的代码和映射文件，不知道错在哪

excel表见图(表名：excel)
我的数据库:graduates,数据表:mynews,字段:title,author,acontent
新建myBean包
新建db.java连接加载数据库
package myBean;//工作包

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

public class db {//连接mysql数据库
public Connection connstr = null;
public ResultSet res = null;
public PreparedStatement prase = null;
static private String strUrl = "jdbc:mysql://localhost:3306/graduates?useUnicode=true&characterEncoding=gb2312";
static private String strUser = "root";
static private String strPwd = "man891028";
public db()
{
try
{
Class.forName("com.mysql.jdbc.Driver ");
connstr = (Connection) 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 = (Statement) connstr.createStatement();
res = stmt.executeQuery(sql);
return res;
}
public PreparedStatement dosql(String sql) throws Exception{
prase = (PreparedStatement) connstr.prepareStatement(sql);
return prase;
}
}
新建into.java用于将excel数据插入数据库

package myBean;

import java.sql.PreparedStatement;
public class into {//excel数据插入数据库
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;
}
}
新建toMysqlservlet.java 用于jsp页面跳转此界面完成excel导入mysql数据库
package myBean;

import java.io.File;
import java.io.IOException;
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 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");
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("失败");
}
}
}

Answer 1

错了，你是要批量导入吗？
需要下载poi包
首先要先把文件转变成数据流InputStream，然后
try
{
HSSFWorkbook workbook = new HSSFWorkbook(is);
HSSFSheet sheet=workbook.getSheetAt(0);
}
catch(Exception e)
{
e.printStackTrace();
}
if(sheet==null)
{
close(streamIn);
return null;
}

int rows = sheet.getPhysicalNumberOfRows();
if(rows<=1)
{
close(streamIn);
return null;
}
HSSFRow row =null;
DecimalFormat formatter=new DecimalFormat("#");
for(int r = 1;r< rows;r++)
{
}
遍历取出，并保存

追问

直接用jxl包不行么 我就导入一个excel表，先浏览本地的excel上传，通过响应网页上的servlet来读取数据存储到数据库