使用MSSQL的OleDB访问接口读取Excel数据

--最近在做一个读取Excel文件数据的程序，使用MSSQL对Excel文件及其他数据模式的接口方法
--使其数据读取及导出都非常方便
--下面讲一下OpenDataSet的使用方法，比较详尽，有需要的人可以参考使用
--数据库异构模式，数据互通有无，非常便利
--Autor: grjs
--Date : 2014-07-27
--使用OpenRowSet或者OpenDataSource读取Excel工作簿数据。

(Excel工作簿数据是二维表模式)
--首先需要了解一下Excel工作簿的一些特性，比如97-2003版的Excel工作簿最大行数约65500,2007及以上的约104000行
--如果在MSSQL2005及更高版本中使用，则可能要将高级功能打开，允许使用访问接口读取外部文件数据
--这是微软对数据库的安全性考虑的，执行以下SQL语句即可开启高级功能
sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries',1;
GO
RECONFIGURE;
GO
--如果要取消权限，则执行下面的SQL语句
sp_configure 'Ad Hoc Distributed Queries',0;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options',0;
GO
RECONFIGURE;
--注意：
--1.开启了这个高级功能，同时访问数据库的用户还必须拥有使用这个访问接口的权限
--2.Excel文件必须存放在MSSQL数据库服务器的本地磁盘路径里，路径不能太深,因为这种访问接口不能直接读取远程共享文件
--3.使用这种访问接口读取Excel文件数据时,不能先打开相同文件名的Excel,因为访问接口不能读取已经打开的Excel 文件
--4.当然MSSQL数据库服务器必须装有Microsoft Office Excel提供Microsoft.Jet.OLEDB.4.0或者Microsoft.ACE.OLEDB.12.0访问接口驱动
--5.Excel表格列名不能太复杂，比如含有小括号，则有可能读取不到这列的数据，同样的工作簿名称也不能太复杂
--下面以一个商品库存资料的表格为例,其格式如下
--编号名称规格条码单位单价数量金额
--P20140728001 牛皮腰带 1.2m 109655862356 条33.32 1000 33320
--P20140728002 鳄鱼皮鞋40码108565203974 双25.3 2000 50600
--P20140728003 T恤L 108520366578 件66.23 1500 99345
--P20140728004 红双喜香烟10包装109652800368 条150.32 2000 300640
--对应HDR=NO的列名，如下
--F1 F2 F3 F4 F5 F6 F7 F8
--1.Excel是97-2003版
--如果是标准的二维表模式，就是从第一列开始第一行是标题，第二行开始是数据，但是这个列的顺序是重新排列的select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=1;DATABASE=D:\test.xls',[Sheet1$])
--指定Excel表格区域读取数据，按原来的列顺序显示
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=1;DA TABASE=D:\test.xls','select * from [Sheet1$A1:R65000]')
--如果是第一个工作簿，则可以省略工作簿名称
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=1;DA TABASE=D:\test.xls','select * from [$A1:R65000]')
--如果工作簿的名称不规范，例如工作簿名称是23sdf-2342df-23ere3，则要用引号括起，如下使用
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=1;DATABASE=D:\test.xls',['23sdf-2342df-23ere3$'])
--2.Excel是2007及更高版，或者操作系统和MSSQL数据库是64bit的,后缀是xls或xlsx的Excel文件都支持
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;IMEX=1;DATABASE=D:\test.xls',Sheet1$) select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;IMEX=1;DA TABASE=D:\test.xls','select * from [$A1:R65000]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;IMEX=1;DATABASE=D:\test.xlsx','select * from [$A1:R104000]')
--OpenRowSet各项参数说明：
--使用的数据访问接口：97-2003版使用'Microsoft.Jet.OLEDB.4.0'，2007及更高版使用'Microsoft.ACE.OLEDB.12.0'(64bit 的系统必须使用高版本的)
--Excel版本：Excel 5.0
--包含标题设置：HDR=YES就是第一行为列标题;HDR=NO即无标题，查询结果为Excel工作簿所有数据,列标题为F1,F2,F3...,列顺序是Excel本来的
--IMEX=1：将字符串与数字混合的列强行转为字符串;IMEX=0自动识别数据类型
--文件路径：DATABASE
--Sheet1$: Excel文件工作簿名称，
--如果有的Excel工作簿名称不规范，导致读取时出现问题，则使用'select * from [$A1:R65000]'这种方式，默认读取第一个工作簿的数据
--$A1:R65000 : 从A1列到R65000列的所有数据
--3.在读取Excel工作簿数据时，有一些问题，比如读取条码，有10个数字组成的条码字符串，但是读取后导入数据表里后，再查询出来就看到变样了
--比如条码109655862356，读取之后变成了1.09656E+11，这样的问题要如何处理呢？
--如上所示，列F4对应的就是条码,如果出现上述问题，则要对条码字段进行类型转换2次，如下使用
select [编号],[名称],[规格],CAST(CAST([条码] AS BIGINT) AS V ARCHAR(20)) as [条码],[单位],[单价],[数量],[金额] from OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=1;DA TABASE=D:\test.xls','select * from [$A1:R65000]')
--或者
select [F1],[F2],[F3],CAST(CAST([F4] AS BIGINT) AS V ARCHAR(20))as [F4],[F5],[F6],[F7],[F8] from OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=NO;IMEX=1;DATABASE=D:\test.xls','select * from [$A2:R65000]')
--4.读取Excel工作簿数据速度的问题，经过测试，遍历整个工作簿的速度显得慢一些，所以这里可以灵活调整一下，就是先计算出工作簿的数据行数和列数，再确定范围读取数据，这样的速度显然是最理想的
--具体参考下面的方法
--4.1确定某列数据不能为空,比如编号([F1])不能为空，获取工作簿的数据行数
--注：HDR=NO是部含列名，也就是第一行，但是要注意末尾行号则要包含第一行
declare @Rows varchar(10),@Start varchar(10)
select @Rows=CAST(count(*) AS V ARCHAR(10)) from OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=NO;IMEX=1;DATABASE=D:\test.xls','select * from [$A2:A65000]')
--注意：使用下面的MSSQL语句获取行数的速度可能很慢,不建议使用
set @Rows=CAST(select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=NO;IMEX=1;DATABASE=D:\test.xls','select count(*) from [$A2:A65000]')) AS V ARCHAR(10))
--4.2获取列数，再通过列数获取对应Excel列的英文字母(这里暂定26个英文字母，超出范围的自己想办法解决),
--如何获取列数及对应的字母，让读者自己去实现，这里只给一点思路
declare @FCol varchar(2),@LCol varchar(2),@ColumnList varchar(2000)
set
@ColumnList='1A,2B,3C,4D,5E,6F,7G,8H,9I,10J,11K,12L,13M,14N,15O,16P,17Q,18R,19S,20T,21U,22V,23W,24X,25Y,26Z'
--@FCol读取表格的起始列字母，@Start从第几行开始读取,@LCol最后一列对应的字母,@Rows最后一行行号
--如果获取到了行数和列对应的字母，那么读取Excel表格数据的速度将提高很多
exec('select * from OpenRowSet(''Microsoft.Jet.OLEDB.4.0'',''Excel 5.0;HDR=NO;IMEX=1;DA TABASE=D:\test.xls'',''select * from [$'+@FCol+@Start+':'+@LCol+@Rows+']'')')
--5.从数据库表读取数据导入到Excel，则直接使用。

如果要按一定格式导入，则自己想办法。

insert into OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=NO;IMEX=1;DATABASE=D:\test.xls',[Sheet1$]) Select * from ATableName
--6.其他的功能需求，只要你愿意开动脑筋去思考，应该都能有很好的解决办法的！。