用Java and JDBC-ODBC读写Excel文件

用Java 读写Excel文件

 

背景介绍

在我们日常进行的与java相关的开发中，可能会碰到要把一批特定格式的数据倒入到系统中的情况。举个例子来说，假设我正在作一个公司的员工培训信息管理系统，该系统中要涉及到员工的信息，课程的信息等。考虑到公司人员流动比较频繁，时常会有一个批新的员工加入到这个系统中来，我们在作需求会考虑这样一个功能，就是批量地添加公司员工的信息。而在工作中，几乎全部的类似员工信息的数据都是MS的Excel 文件。那么，现在的问题就是能不能把一个Excel文件倒入到系统中，系统从Excel文件中得到需要的数据呢？参考[1]中就是这样的例子，在这里，通过学习[1]，我也列出了一个类似的例子。

 

思路

其实，用Java读取Excel文件并非难事。对Excel，Access等文件，微软都提供了ODBC驱动，而了解Java的人一定熟悉JDBC，我们的思路就是用JDBC-ODBC Driver来读取Excel文件。

 

步骤一，创建Excel文件

我们就来创建一个员工信息的Excel文件吧。该文件名称就叫Employee，包括5列：ID、Name、Gender、Title和Email。见Figuree 1。 为了能被微软的ODBC Driver识别，Excel文件要满足一定的格式。一般来说，ODBC Driver会把Excel WorkSheet的第一行的列名当成数据库表的对应得列名，而数据库表的名字就是WorkSheet的名字（注意数据库表的名字不是Excel文件的名字）。

 

ID

Name

Gender

Title

Email

1000

Jesse, G.

M

PM

[e-mail]

1001

Kamal

M

SE

[e-mail]

1002

Jenny

F

PA

[e-mail]

1003

…

…

…

…

Figure 1 Employee

 

步骤二，创建ODBC数据源

首先打开“控制面板”，然后是“管理工具”，接着在该目录下选择“数据源”。这时会有一个对话框弹出。见Figure 2。

Figure 2 Data Source Admin

选择“添加”按钮，在选择驱动列表中选择Driver do Microsoft Excel。点击“完成”，出现Figure 3。

Figure 3 Excel Setup

在“Data Source Name”编辑框输入“emp”，“Select Workbook…”选中刚才创建的Excel文件Employee.xls，见Figure 4。注意去掉Read only选项，否则我们只能从该数据源读取数据，而不能插入和修改。点击确定。

Figure 4 Select Workbook

最后emp会出现在“User Data Sources”列表中。见Figure 5。 到此，一个ODBC数据源就建好了。

 

Figure 5 Data Source 2

 

步骤三，编写Excel读写类

下面就让我们来写一段java代码来读取该Excel文件。我们知道该文件中存储着员工的信息，假设我们现在要完成两个工作，一个是根据员工ID，列出该员工的相信信息；一个是向数据库中添加新的记录。

如果我们现在是对Oracle或是MSSQL等常见数据库操作，实现这两个功能的SQL语句马上就可以写出。假设表名为employee，下面的两条SQL语句分别是查询ID为1000的员工记录和插入一条ID为1005的新记录。

 

1) SELECT ID, Name, Gender, Title, Email FROM employee WHERE ID=1000;

2) INSERT INTO employee(ID, Name, Gender, Title, Email)

         VALUES (1005, 'Yanjiang', 'M', 'PM','[e-mail]');

那么对Excel文件，这样写可以吗? 其实查询和读取Excel的SQL，95%以上和上面的是一样的。只需要注意一点，就是我们不能直接使用数据库表名，而是要在其后加上$字符，并把新的名字放到[]中。对于这个例子来说，因为我们的数据表名为employee，所以在SQL语句中我们要写成 [employee$]

 

3) SELECT ID, Name, Gender, Title, Email FROM [employee$] WHERE ID=1000;

4) INSERT INTO [employee$] (ID, Name, Gender, Title, Email)

         VALUES (1005, 'Yanjiang', 'M', 'PM','[e-mail]');

下面就列出了所有的代码。

 

// ExcelAccess.java

import java.sql.Connection;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.ResultSet;

import java.sql.DriverManager;

import java.util.Collection;

import java.util.HashMap;

import java.util.Iterator;

 

public class ExcelAccess {

    /** db connection */

    private Connection c;

    /** db statement */

    private Statement stmnt;   

    /** JDBC driver name */

    private String driver;

    /** url of the db */

    private String url;

    /** username to access the db */

    private String username;

    /** password for the username */

    private String password;

 

    /** default constructor */

    public ExcelAccess() {

        driver = "sun.jdbc.odbc.JdbcOdbcDriver";

        url = "jdbc:odbc:emp";

        username = "";

        password = "";

    }

 

    /**

     * constructor with specified parameters

     */

    public ExcelAccess(String driver, String url,

            String username, String password) {

        this.driver = driver;

        this.url = url;

        this.username = username;

        this.password = password;

    }

 

    /**

     * open the connection

     * @return true if opened, false otherwise

     */

    public boolean openConnection() {

        try {

            Class.forName(driver);

            c = DriverManager.getConnection(url, username,

                    password);

            stmnt = c.createStatement();

        } catch (Exception e) {

            e.printStackTrace();

            return false;

        }

        return true;

    }

 

    /**

     * Function 1: get the emplpyee's information by the emp id

     * @param empid

     * @return the query result

     */

    public HashMap getEmpInfo(int empid) {

        HashMap result = new HashMap();

        if (stmnt == null) {

            return result;

        }

        // how the query looks like

        String query = "SELECT * FROM [employee$] WHERE ID = "

                + empid;

        ResultSet rs;

 

        try {

            // execute the query

           rs = stmnt.executeQuery(query);

            // get the columns' information

            ResultSetMetaData rsmd = rs.getMetaData();

            int numberOfColumns = rsmd.getColumnCount();

 

            String strTemp;

            // store the result to the HashMap

            while (rs.next()) {

                for (int i = 0; i < numberOfColumns; i++) {

                    strTemp = rsmd.getColumnName(i + 1);

                    result.put(strTemp, rs

                            .getString(strTemp));

                }

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return result;

    }

 

    /**

     * Function 2: insert a new record into the database   

     * @return 1 if the record be inserted, 0 otherwise

     */

    public int insertEmpInfo(int id, String name,

            char gender, String title, String email) {

        int result = -1;

        if (stmnt == null)

            return result;

        // generate the query string

        String query = "INSERT INTO [employee$] VALUES(";

        query += id + ", ";

        query += "'" + name + "', ";

        query += "'" + gender + "', ";

        query += "'" + title + "', ";

        query += "'" + email + "'";

        query += " )";

 

        try {

            // execute the query

            result = stmnt.executeUpdate(query);

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return result;

    }

 

    /**

     * close the connection

     */

   public boolean closeConnection() {

        try {

            if (stmnt != null)

                stmnt.close();

            if (c != null)

                c.close();

        } catch (Exception e) {

            System.err.println(e);

            return false;

        }

        return true;

    }

 

    public static void main(String[] args) {

        ExcelAccess ea = new ExcelAccess();

        HashMap res = null;

        // open connection

        if (!ea.openConnection()) {

            System.err.println("open connection err.");

            System.exit(1);

        }

        // get emp# 1000's information

        System.out.println("Get Emp# 1000:");

        res = ea.getEmpInfo(1000);

 

        if (res != null) {

            Collection c = res.keySet();

            // obtain an Iterator

            Iterator i = c.iterator();

            while (i.hasNext()) {

                // obtain a key

                Object key = i.next();

                // obtain the value of the key

                Object value = res.get(key);

                System.out.println(key + "/t" + value);

            }// while ends

        }

 

        // insert a new record into the table

        int ret = ea.insertEmpInfo(1005, "Yanjiang", 'M',

                "PM", "[e-mail]");

        if (ret == 1) {

            System.out.println("emp# 1005 inserting success.");

        }

        // close the connection

        ea.closeConnection();

    }

}

Following is the running result:

Get Emp# 1000:

Email [e-mail]

Gender      M

Title PM

Name  Jesse, G.

ID    1000.0

emp# 1005 inserting success.

总结

到此这篇文章就结束了，这个例子实现了Java读写Excel文件，类似，我们可以去读写dBase、Access文件等。还有就是这个简单的例子充分说明了当初设计JDBC，ODBC等驱动的英明之处。值得一提的是，现在已有了比较成熟的专门操作 EXCEL文件的Java API。Jakarta POI 和 Java Excel API就是其中的两个Open Source项目。感兴趣的可以从网上搜索一下，应该会有很多关于的资料。

 

参考

[1] http://www.javaworld.com/javaworld/javaqa/2001-06/04-qa-0629-excel.html