数据库工具类：
package com.excel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class UtilDao
{
Connection con=null;
String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";
public Connection getConnection()
{
try {
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection(url,"root","root");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
public ResultSet doQuery(String sql)
{
ResultSet rs=null;
if(con==null)
{
this.getConnection();
}
try {
Statement st=con.createStatement();
rs=st.executeQuery(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
public void doUpdate(String sql)
{
if(con==null)
{
this.getConnection();
}
Statement st;
try {
st = con.createStatement();
st.execute(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void doClose()
{
if(con!=null)
{
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
主函数类：
package com.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class ExcelDemo {
/**
* 读取 Xls 文件内容
*/
@SuppressWarnings("unused")
private static String readExcelFile(String path) throws Exception {
// 创建工作簿对象
File excelFile = new File(path);
Workbook workBook = Workbook.getWorkbook(excelFile);
StringBuffer sb = new StringBuffer();
sb.append("insert into `inschool`(`id`,`school`,`year1`,`class`,`edu`,`name`,`num`,`sex`) values ");
// 得到工作簿所有的工作表对象
Sheet[] sheets = workBook.getSheets();
// 遍历所有行
for (int i = 0; i < sheets[0].getRows(); i++)
{
// 得到所有列，在输出列中的内容
Cell[] cells = sheets[0].getRow(i);
for (int j = 0; j < cells.length; j++)
{
if(j==0)
{
sb.append("("+cells[j].getContents().trim()+" , ");
}
if(j==cells.length-1)
{
sb.append("'"+cells[j].getContents().trim()+"'),");
}
else
if(j<cells.length-2)
{
sb.append("'"+cells[j+1].getContents().trim()+"'"+ ", ");
}
}
}
String sql=sb.toString().substring(0,sb.length()-1);
return sql;
}
public static void main(String[] args) throws Exception {
UtilDao dao = new UtilDao();
dao.doUpdate(readExcelFile("D:\\2012年春季学生学号.xls"));
}
}
测试数据库表格：
inschool CREATE TABLE `inschool` (
`id` int(11) NOT NULL,
`school` varchar(200) character set gbk default NULL,
`year1` varchar(20) character set gbk default NULL,
`class` varchar(200) character set gbk default NULL,
`edu` varchar(200) character set gbk default NULL,
`num` varchar(40) character set gbk default NULL,
`sex` varchar(20) character set gbk default NULL,
`name` varchar(200) character set gbk default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
excel内容格式：
| 1 | 西北工业大学 | 2012 | 计算机科学与技术 | 专起本 | zhangsan | 1203222221 | 女 |
| 2 | 西北工业大学 | 2012 | 计算机科学与技术 | 专起本 | 李四 | 1203221222 | 女 |