windows下使用jdbc读写excel文件

使用java读写excel的方法不胜枚举,这里介绍的把excel文件当作一个数据库来对待并使用jdbc连接去读写它。还是那句话,存在就是合理的。每种读写excel的方法都有其优点和缺点,都有它所侧重的地方,都有适合它的应用场景。多掌握一种方法可能就意味着多一种选择,可能恰恰有的场景下这种解决方法就是最优的。当然勤于发现,善于发现就能看到更多的好处。jdbc早已经成为一种标准,正是因为它是一种标准,所以你如果掌握了这种标准就意味着你可以用最小的学习成本去学习这一类的东西。比如你只会写sql或者擅长写sql,那么使用sql处理问题就可能事半功倍了。毕竟使用自己不擅长的东西多少有些不适感。在windows下使用sql读写excel仍然存在一些限制,比如说程序最好是32位的,而且对sql语法的支持是相对较弱的。

1)、以jdk1.8 32位为例 以下代码实现了对excel文件(.xlsx)的查询、插入、修改等操作

package com.lbz;

import sun.jdbc.odbc.JdbcOdbcDatabaseMetaData;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

public class Excel{
    private Connection connection;
    public Excel(){
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            //注意32系统与64位系统,此处只能用32位系统
            String path = System.getProperty("excelfilepath");
            boolean useXlsOnly = false;
            String url = "jdbc:odbc:driver={Microsoft Excel Driver (*.xls)};READONLY=false;DBQ="+path;;
            if(!useXlsOnly){
                url = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};READONLY=false;DBQ="+path;
            }

            Properties prop = new Properties();
            prop.put("charSet","gbk");
            prop.put("user","");
            prop.put("password","");
            Connection con = null;
            try {
                con = DriverManager.getConnection(url, prop);//没有用户名和密码的时候直接为空
                JdbcOdbcDatabaseMetaData meta = (JdbcOdbcDatabaseMetaData) con.getMetaData();
                ResultSet resultSet = meta.getTables(null,null,null,null);
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

                List<String> tables = new ArrayList<String>();
                while(resultSet.next()){
                    String tableName = resultSet.getString(3);
                    tables.add(tableName);
                }
                System.out.println(tables);

                //System.out.println(meta.getStringFunctions());
                //System.out.println(meta.getNumericFunctions());
                //System.out.println(meta.getTimeDateFunctions());
            }catch (Exception exc){
                exc.printStackTrace();
            }
            this.connection = con;
        }catch(Exception e) {
            e.printStackTrace();
        }
    }

    public Excel insert(String sql) {
        Statement statement = null;
        try {
            statement = this.connection.createStatement();
            statement.executeUpdate(sql);

            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return this;
    }

    public Excel update(String sql) {
        Statement statement = null;
        try {
            statement = this.connection.createStatement();
            statement.executeUpdate(sql);

            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return this;
    }

    public <T> List<T> select(String sql,Class c) {
        Statement statement = null;
        List<T> list = new ArrayList<T>();
        try {
            statement = this.connection.createStatement();
            ResultSet result = statement.executeQuery(sql);
            Field[] flds = c.getDeclaredFields();
            while (result.next()) {
                T t = (T)c.newInstance();
                for(Field f : flds){
                    if("id".equals(f.getName())){
                        continue;
                    }
                    f.setAccessible(true);
                    f.set(t,result.getString(f.getName()));
                }
                list.add(t);
            }
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }

        return list;
    }

    public Excel ddl(String sql){
        Statement statement = null;
        try {
            statement = this.connection.createStatement();
            statement.executeUpdate(sql);

            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return this;
    }


    public List<Map> selectMap(String sql) {
        Statement statement = null;
        List<Map> list = new ArrayList<>();
        try {
            statement = this.connection.createStatement();
            ResultSet result = statement.executeQuery(sql);
            ResultSetMetaData metaData = result.getMetaData();
            List<String> colList= new ArrayList<>();
            for(int i=0;i<metaData.getColumnCount();i++){
                String colName = metaData.getColumnName(i + 1);
                colList.add(colName);
            }

            while (result.next()) {
                Map<String,String> map = new HashMap<>();
                for(int i = 0;i<colList.size();i++){
                    String colName = colList.get(i);
                    Object v = result.getObject(colName);
                    if(v == null || "null".equals(v)){
                        v = "";
                    }
                    map.put(colName,String.valueOf(v));
                }
                list.add(map);
            }

            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }


    public void releaseConnection(){
        try{
            this.connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



    public static void main(String[] args){
        System.setProperty("excelfilepath","E:/git/StoreHouseEnv/lib/test.xlsx");

        Excel excel = new Excel();
        List<Map> list = excel.selectMap("select * from [Sheet1$]");
        System.out.println(list);
        list = excel.selectMap("select max(len(NAME)) as namelength from [Sheet1$]");
        System.out.println(list);

        for(int i=0;i<5;i++){
            excel.insert("insert into [Sheet1$](ID,NAME) values('222','测试插入')");
        }
        excel.update("update [Sheet1$] set name='pingping'");
        list = excel.selectMap("select max(len(NAME)) as namelength from [Sheet1$]");
        System.out.println(list);
        excel.releaseConnection();
    }
}

2. odbc数据源

很多odbc数据源都是只支持32位的,比如我们常用的oracle数据库,jdbc中我们一般可能用的是"thin"方式连接的,也可以用odbc连接,用odbc连接的话一般找32位的。odbc数据源可以在控制面板中查看。

代码中数据库连接字符串为

jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};READONLY=false;

这里的Excel数据源要结合实际情况 去调整配置,可能需要手动安装。readonly=false表示该excel文件可以被写入,readonly=true表示只读。

office其它的产品比如access数据库的连接字符串基本和excel的相似,修改对应的odbc数据源即可。

3、jdk版本的问题

使用jdk1.7 32位版本的运行上面的代码基本没有什么问题,但是使用jdk1.8 32位的话是需要做一些额外的工作。

找到jdk1.7下的jre\lib\rt.jar 并提取出部分文件夹目录

因为rt.jar本身文件比较大,所有没有全部解压缩,所以上图中使用jar命令解压部分目录

sun\jdbc和sun\security\action

 因为jdk1.8是没有这些目录,所以把这些目录提取出来打成一个额外的jar包进行引用。当然不打jar包也可以引用。

使用 jar cvf sunjdbcodbc.jar sun 命令将sun目录打成一个jar包

除了需要引用上述的类之外,还需要将jdk1.7安装目录下的JdbcOdbc.dll拷到jdk1.8的相同结构目录下,当然还可以用别的方式进行加载。

 4.运行结果