Excel Jdbc Driver 文档

Excel Jdbc Driver 文档

1. Excel JDBC Driver 的用途

首先，说明xlSQL的用途，在Java系统中通过SQL来操作Excel，就像操作数据库那样，通过jdbc驱动和sql语句即可读取、修改、插入、删除Excel中的数据。

Excel JDBC Driver 的jar包为xlSQL.jar，下面都用xlSQL来代替Excel JDBC Driver。

2. xlSQL 的原理

xlSQL 的原理就是将Excel中的数据转存到hsqldb中，进而可以通过jdbc驱动和sql语句来操作hsqldb中的数据。

2.1. xlSQL 的基本原理

在通过xlSQL建立与Excel的JDBC连接时，实际上是将Excel所在的文件夹映射成一个Database（数据库），将该文件夹下的Excel文件（工作簿）映射成该数据库下的Schema（模式），再将每个Excel文件下的每个sheet（工作单）都映射成该Schema下的一个table（数据库表），映射关系如下图所示：

下面我们可以通过一段代码实例来理解一下这种映射关系在实际使用中的用途：

首先，我们假设在E:\Excel_folder 文件夹下有一个名称为“demo”的excel文件，demo中有一个名称为“xlsqly7”的sheet，下面为我们通过xlSQL访问xlsqly7中的数据的代码（红色标注的代码为重要代码）：

　　String driver = "com.nilostep.xlsql.jdbc.xlDriver";　　Class.forName(driver).newInstance();　　String protocol = "jdbc:nilostep:excel";　　String database = “E:\\Excel_folder”;　　String url = protocol + ":" + database;　　Connection con = DriverManager.getConnection(url);　　Statement stm = con.createStatement();　　　　String sql = "select * from \"demo.xlsqly7\"";　　ResultSet rs = stm.executeQuery(sql);

以上代码中database即为Excel文件所在的文件夹的全路径，它主要用来形成建立JDBC连接的url；

另外，我们可以看出 demo（工作簿） 和 xlsqly7（sheet）则主要用来形成增删改查的sql语句 

2.2. xlSQL 的详细原理

2.1章节中我们所阐述的是xlSQL的基本使用方法，下面我们将介绍其详细原理：

实际上，在使用xlSQL操作Excel文件时，并不是直接访问的Excel中的sheet，而是以hsqldb（在内存中运行的临时数据库）为中介进行访问的。

Ø 首先，在使用xlSQL创建JDBC连接的过程中，实际上是将作为database的文件夹下的所有Excel文件中的每个sheet作为一个单独的table存储到了hsqldb中；

Ø 接下来，操作则分为查询和增删改两种情况：

如果进行的为查询（select）操作时，则是直接执行sql语句从hsqldb中获取符合条件的结果集。

如果进行的为增删改（update、insert、delete）操作，则是先更新hsqldb中相应的Table，最后再根据hsqldb中存储的数据把对应Excel的文件的数据全部更新。

3. xlSQL 的流程

通过2.2章节，我们可以了解，xlSQL在创建Excel JDBC连接时，实际上是先将sheet中的数据存储到hsqldb（临时数据库）中的，接下来我们来逐步了解一下xlSQL在创建Excel JDBC连接时的流程：

用xlSQL 创建Excel JDBC连接的流程如下：

根据上面的流程，在创建Excel JDBC连接时，所调用的源代码中类的方法的主流程如下：

下面将针对以上流程中的重要方法进行讲解：

3.1. xlDriver的connect()方法

connect()方法的执行结果返回java.sql.Connection对象，是创建Excel JDBC连接的主方法

3.2. xlConnection的factory()方法

xlConnection为java.sql.Connection的实现类，其factory()方法被xlDriver类的connect()方法所调用，factory()方法执行后会返回一个xlConnection类的对象。

3.3. xlConnectionMySQL的构造方法

xlConnectionMySQL为xlConnection的子类，其构造方法被xlConnection类的factory()方法调用以返回一个xlConnection类的对象。

xlConnectionMySQL的构造方法中调用了一些格式化sql语句的方法，同时给xlConnectionMySQL类的公有变量赋值。其中调用的最重要的一个方法就是从xlConnection继承过来的startup()方法，该方法的描述见3.4。

3.4. xlConnectionMySQL的startup()方法

startup()方法中实现了Excel的解析以及将解析的数据存储到hsqldb中，该方法中调用了xlDatabaseFactory的create()方法、ASqlFormatter的wCreateSchema()方法、wCreateTable()方法和wInsert()方法。

其中xlDatabaseFactory的create()方法主要用来返回Excel的解析结果。

ASqlFormatter的wCreateSchema()方法用来在hsqldb中根据Excel文件创建schema；

wCreateTable()方法实现了根据Excel文件中的sheet在hsqldb中对应的schema下创建table。

wInsert()方法实现了将Excel文件中的sheet中的数据插入到hsqldb中对应的schema下的table中

4. xlSQL需要改造的地方

4.1. 使其支持中文字段名

xlSQL本身并不支持中文字段名，在解析Excel时对字段名进行了限制，在xlSheet类的readFile（）方法中有如下一段代码：

if (!c[i].getContents().matches("^[A-Za-z0-9._-]{1,30}+$")) {    ret = false;    break;}

以上一段代码对字段名进行了严格的限制，字段名必须符合以下条件：

Ø 大小写的26个英文字母

Ø 0到9的数字

Ø 可包含特殊字符：“.”、“-”、“_”

Ø 最大长度为在30个字符，最小长度为1个字符

很明显，中文的字段名是不符合以上条件的，所以我们需要对上面的代码进行改动，如果我们队字段名没有限制的话，则可以将上面的代码注释掉。如果仍然有限制，可相应的改动上述代码中的正则表达式。

4.2. 允许字段中包含特殊字符

xlSQL本身不支持字段名中包含特殊字符如：！@#￥%……&*（）《》<>{}等等，但是实际中，我们所读取的Excel的字段列中经常会包含一些特殊字符，如果Excel的字段列中包含特殊字符，那么在解析Excel并在hsqldb中创建表的时候就会报错误，错误的原因就是sheet的字段行中包含sql中不允许出现的特殊字符。

下面我们将对源代码进行改造，使其能够允许sheet的列中包含特殊字符。

我们需要修改com.nilostep.xlsql.database.export.ASqlFormatter

将wCreateTable()方法中的如下代码：

sql = sql + co[i] + " " + ty[i];

修改成为：

sql = sql + "\"" + co[i] + "\"" + " " + ty[i];

即可

sql中如果字段名包含特殊字符，将字段名放在双引号中，就会将特殊字符作为字段名所包含的一个字符，就不会报sql格式的错误。

(但是"引号还是不能用)

4.3. 可以指定加载哪些文件

由于xlSQL是把Excel所在的文件夹作为database（数据库），在创建连接时，会去加载该文件夹下的所有Excel文件，这样就会存在一个问题，如果我只需要访问文件夹下的一个Excel文件的数据，但是在加载的时候却要加载所有的文件，既浪费时间又浪费资源，综上，我们需要改造源代码使我们创建Excel JDBC连接的时候，可以指定加载哪些Excel，甚至可以指定加载某个固定Excel中的哪些sheet。

下面我们来分析如何进行源代码的改造：

首先，因为指定加载哪些文件的过程是在创建连接之前进行的，所以，我们需要向Excel的解析函数中传递一个参数，而创建连接的过程中，唯一能向后台传递自定义参数的入口就是DriverManager.getConnection(String url, Properties info)，正好Properties类的和Map相似，可以灵活的以<key,value>的形式将参数进行传递。

Ø 加载指定的文件

修改的地方为com.nilostep.xlsql.database.excel.io.jxlReader类的readWorkbooks（）方法，使其可以接收Properties类的对象作为参数。在Properties类型的参数对象中存储一个想要加载的文件名的List，最后在readWorkbooks（）方法中进行文件名的比较，如果在List中，则加载该Excel，否则，不加载。如果List为空，则加载文件夹下的所有Excel文件。

Ø 加载指定文件的指定sheet

修改的地方为com.nilostep.xlsql.database.excel.xlWorkbook类的readFiles（）方法，修改方式同指定文件的类似。

4.4. 支持指定sheet中的字段行和值的首行

xlSQL默认解析Excel时，是读取第一行的单元格为字段，第二行为值，我们可以将其改造为灵活指定第几行为字段行、第几行为值的首行。

修改的文件为com.nilostep.xlsql.database.excel.xlSheet类的readFile（）方法和com.nilostep.xlsql.jdbc.xlConnection类的startup（）方法中matrix这个二维数组的读取过程。使其可以接收参数Properties info 对象，再从info中获取我们自己指定的字段行和值的首行即可。

Jar包已上传：CSDN资源中。地址：http://download.csdn.net/detail/ailvey/5239090

Demo 地址（CSDN资源中）：http://download.csdn.net/detail/ailvey/9073715