同样需要引入poi架包代码示例如下:
/**
*
*将excel导入到MySQL中
*/
//从excel导入到Mysql中 ok
package packagename;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class testreadexcel {
/*this class used to import .xls into MySQL
* 2012.12.12
* */
private static String dbURL = "jdbc:mysql://localhost:3306/test?" +"auotReconnnect=true&useUnicode=true&characterEncoding=utf8";//数据库连接串
private static String userName = "root";//数据库用户名
private static String password = "root";//数据库密码
public static String fileToBeRead= "E:\\MySQL\\MySQL中的数据这里是一个已存在的.xls
// 创建对Excel工作簿文件的引用;
public static void main(String argv[]) {
Connection conn = getConnection();
PreparedStatement ps = null;
try {
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
HSSFSheet sheet = workbook.getSheet("Sheet1");//
int rows=sheet.getPhysicalNumberOfRows();
System.out.println("行数为"+rows);
for (int r = 1; r < rows; r++) {
// 读取左上端单元
HSSFRow row = sheet.getRow(r);//得到逻辑行的下一行信息
if (row != null) {
@SuppressWarnings("unused")
int cells = row.getPhysicalNumberOfCells();//获取有数据的小格的格数
HSSFCell idcell = row.getCell(0);//用idcell记录该单元格
int id = (int) idcell.getNumericCellValue();//用该单元格中的数据返回int
HSSFCell namecell = row.getCell(1);//用namecell存储记录用户名(第一列)的单元格
String name = namecell.getStringCellValue();//获取第一列的单元格的数据
System.out.println("ID:" + id + "/t姓名:" + name);//进行输出
conn.setAutoCommit(false);
ps = conn.prepareStatement("insert into admin_type values(?,?)");
ps.setInt(1, id);
ps.setString(2, name);
ps.addBatch();// 加入批处理
// ps.executeUpdate();不能与addBatch()同时用
ps.executeBatch();
System.out.println("成功了");
}
else
{
break;
}
}
conn.commit();
// 下面可以将查找到的行内容用SQL语句INSERT到oracle
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(dbURL,userName,password);
System.out.println("链接成功");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}