JAVA Excel数据批量导入到SQL数据库

    今天刚好需要批量将Excel里的数据导入到SQL Server，本来打算用Genero写的，发现官方驱动包找不到SQL Server 2012的驱动，我的天啊，无解！只好用JAVA来写！JAVA通过加载JDBC驱动轻松搞定！除了引用sqljdbc4.jar 库外，还需要引用另外一个库 jxl.jar。这两个库很容易找到下载。

下面直接看源码部分吧，功能很简单，但是需要注意一点：jxl不能处理.xlsx结尾的Excel档，编译会提示发现不了OLE对象的错误。解决的办法是打开.xlsx文档，另存为Excel97-2003 后缀.xls  。这个问题一开始耽误了我不少时间。

import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.ArrayList;import javax.swing.JOptionPane;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;public class Test {public static void main(String[] args) throws SQLException {Object[] obj = null;ArrayList<Object[]> list = new ArrayList<Object[]>();  //定义ArrayList,包含数组类型String filePath = "C:/Users/xxx68099/Desktop/User.list.xls";  //定义Excel文件路径InputStream is = null;Workbook rwb = null;try {is = new FileInputStream(filePath); //定义文本输入流} catch (FileNotFoundException e) {e.printStackTrace();}try {rwb = Workbook.getWorkbook(is); //打开Workbook} catch (BiffException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}Sheet sht = rwb.getSheet("Sheet1"); //打开Workbook中Sheet1int col = sht.getColumns();  //获得Excel列int row = sht.getRows();  //获得Excel行Cell c1 = null;for(int i=0; i<row; i++)  //先将数据按行装入一个一维数组中， 然后将数组逐个加入到ArrayList{obj = new Object[col];for(int j=0; j<col; j++){c1 = sht.getCell(j, i);obj[j] = c1.getContents();}list.add(obj);}//这里再从ArrayList里读出来是没有必要的，可以直接在getCell数据时，加入SQL批处理，减少循环，不过为了说明清晰，我还是这样写String  url;String passname;String password;String driver;Connection con = null;PreparedStatement ps = null;passname = "sa";  //SQL Server登陆账号password = "P@ssw0rd11";//SQL Server登陆密码driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //驱动加载try {Class.forName(driver);} catch (ClassNotFoundException e) {JOptionPane.showMessageDialog(null, "数据库加载失败！");e.printStackTrace();}url = "jdbc:sqlserver://172.xx.xxx.xx"+";"+"DatabaseName=RRS";//URL链接String sql = "INSERT INTO UserLocation (MAIL, NT_ACCOUNT,LOCATION, OFFICE, Checked) VALUES (?, ?, ?, ?, ?)";//String sql = "DELETE FROM UserLocation WHERE MAIL = ? AND NT_ACCOUNT = ? AND LOCATION = ? AND OFFICE = ? AND Checked = ?";con = DriverManager.getConnection(url, passname, password); //获得链接con.setAutoCommit(true); //设置自动提交ps = con.prepareStatement(sql);// SQL预处理obj = new Object[5];  for(int k=0; k<row; k++)   //将ArrayList数据读出来，作为参数，批量INSERT到数据库{obj = list.get(k);ps.setString(1, obj[0].toString());ps.setString(2, obj[1].toString());ps.setString(3, obj[2].toString());ps.setString(4, obj[3].toString());ps.setString(5, obj[4].toString());ps.addBatch();}ps.executeBatch();//批量执行ps.close();con.close(); }}