使用JDBC-ODBC读取Excel文件

 1 import java.sql.Connection;
 2 import java.sql.DriverManager;
 3 import java.sql.ResultSet;
 4 import java.sql.SQLException;
 5 import java.sql.Statement;
 6 
 7 import org.dom4j.Document;
 8 import org.dom4j.DocumentHelper;
 9 import org.dom4j.Element;
10 
11 /**
12  * 这种读取Excel的方式应该配配置ODBC数据源.
13  * @author CDV-DX7
14  *
15  */
16 public class ExcelUtil {
17     private String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
18     private String dataSourceUrl = "jdbc:odbc:driver={Microsoft Excel Driver (*.xls)};DBQ="; // 不设置数据源
19 
20     public     ExcelUtil(){}
21         
22     public void setDriverName(String driverName){
23         this.driverName = driverName;
24     }
25     
26     /**
27      * 从excel中获取数据,转化为XML
28      * @param excelUrl
29      * @param sheetName
30      * @return
31      * @throws ClassNotFoundException
32      * @throws SQLException
33      */
34     public Document getExcelData(String excelUrl, String sheetName)
35             throws ClassNotFoundException, SQLException {
36         Connection dbConn = null;
37         try {
38             Class.forName(this.driverName);
39             excelUrl = this.dataSourceUrl + excelUrl;
40             dbConn = DriverManager.getConnection(excelUrl, "", "");
41             Statement smt = dbConn.createStatement();
42             ResultSet rs = smt.executeQuery("select * from [" + sheetName + "$]");
43             Document doc = DocumentHelper.createDocument();
44             Element elmRoot = doc.addElement("Tab");
45             while (rs.next()) {
46                 Element elmRow = elmRoot.addElement("Row");
47 
48                 int colNum = rs.getMetaData().getColumnCount();
49                 for (int i = 1; i <= colNum; i++) {
50                     Element elmCol = elmRow.addElement("Col");
51                     elmCol.setText(rs.getString(i));
52                 }
53             }
54         
55             return doc;
56         } finally {
57             dbConn.close();
58         }
59     }
60             
61     public static void main(String[] args) {
62         try{
63             //使用方法示例
64             String excelUrl = "C:\\Users\\CDV-DX7\\Desktop\\节目信息输入数据.xls";
65             String sheetName = "节目列表";
66             ExcelUtil excelutil = new ExcelUtil();
67             excelutil.getExcelData(excelUrl,sheetName);
68         }catch (Exception e){
69             e.printStackTrace();
70             System.out.println("出错啦:"+e.getMessage());
71         }
72     }
73 }