学步园

最近项目中需要做导出Excel以及具有复杂表头excel的汇总，其实都不算麻烦，基本上都做过导出Excel，具有复杂表头excel的汇总以前也用Active控件实现了，但是效率都不是太理想。最后在一个群友的提醒下，尝试着用OPENDATASOURCE/DATAROWSET解决，结果发现效率还不错。
首先说一下导出Excel，比如说我要导出一个如下图的excel

 

 首先要做一个只有表头的excel的模版

 

 因为要做的效果里面是是两行为一个完整数据，前4列是合并的。不知道有多少行数据，所以要利用宏了。下面是我写的自动合并行的宏代码

Sub Macro1()
'
' Macro1 Macro
'

'
Dim i As Integer
Dim count As Integer
Dim rownum As Integer
count = Sheet1.UsedRange.Rows.count '这里是获取sheet3中的记录行数

For i = 6 To count Step 2 ‘从第六行开始合并，合并前4列
    Range("A" & i & ":A" & (i + 1) & "").Select
    Range("A" & (i + 1)).Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
     Range("B" & i & ":B" & (i + 1) & "").Select
    Range("B" & (i + 1)).Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
     Range("C" & i & ":C" & (i + 1) & "").Select
    Range("C" & (i + 1)).Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
     Range("D" & i & ":D" & (i + 1) & "").Select
    Range("D" & (i + 1)).Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
Next i

End Sub

Sub auto_open() ’打开excel自动执行，可以再加个方法，在第一次执行完后即删除所有的宏
Call Macro1

End Sub

 

excel模版的准备工作到此就结束了，下面就是导入数据了，导入数据用的是OPENROWSET，

INSERT INTO 
OPENROWSET('Microsoft.JET.OLEDB.4.0','Excel 8.0;Database=F:\\预算执行(文号汇总).xls',[Sheet3$])
select * from #temp

为了方便，我单独写了个存储过程，将数据先插入临时表#temp（因为原来表中的格式很可能和模版是不一样的），最后执行上面的sql语句。到此一个完整的导出excel功能就出来了。
合并具有复杂表头的Excel过程跟上面类似，也是先做一个模版，写好宏（主要是为了控制格式）。合并的过程也放在存储过程中，利用OPENDATASOURCE/DATAROWSET取出要合并的excel数据（注意要排除表头，可以根据模版的表头行数进行判断）插入临时表，再一起导入模版中。

下面说一下，在此次开发过程中主要遇到的问题，首先要启用OPENDATASOURCE/DATAROWSET

exec sp_configure 'show advanced options',1  

reconfigure  

exec sp_configure 'Ad Hoc Distributed Queries',1  

reconfigure  

其次在用OPENDATASOURCE/DATAROWSET操作时要求相应的excel文件必须是关闭的，不然会报 如下类似的错误。

 

 

 其次OPENDATASOURCE/DATAROWSET对excel的操作是不能跨服务器，若想跨服务器使用，需要借助共享文件，sql服务器必须能访问excel所在服务器的共享文件，不然就要在excl所在服务器安装数据库了。

最蛋疼的一个问题就是在WIN2008系统下 不再支持 Microsoft.JET.OLEDB.4.0，必须要下载最新的Microsoft.ACE.OLEDB.12.0，下载完安装后就Microsoft.JET.OLEDB.4.0替换成Microsoft.ACE.OLEDB.12.0即可，如果是64位系统，就要安装64的Microsoft.ACE.OLEDB.12.0，但是64位的Microsoft.ACE.OLEDB.12.0是和32位的office不兼容的，所以在WIN2008x64下要用OPENDATASOURCE/DATAROWSET是不能装office的。最后附下Microsoft.ACE.OLEDB.12.0的下载地址http://www.microsoft.com/zh-cn/download/details.aspx?id=13255

问题补充：用Microsoft.ACE.OLEDB.12.0时读取excel完全没问题，但是插入时有点小不同，必须要这么写insert into
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=c:\ttest.xls;', 'SELECT b FROM [sheet1$]')
select b from table1，具体为啥还没搞明白，先记下