C# 通过SqlServer提供openrowset实现最快速度导出数据到excel

/****** Object:  StoredProcedure [dbo].[proc_ExportDataToExcel]    Script Date: 09/25/2019 21:29:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
 将查询结果集直接导出到excel文件中（支持多工作表，65500条为一个工作表）
 通过SQLServer数据库内置对象提高数据导出速率
 exec proc_ExportDataToExcel 'select top 1000 * from Users',0,'UserId','D:/1.xls','用户数据'
 注意：使用此存储过程，数据库登录用户要有sysadmin权限，导出到sql语句中必须提供主键
 (事理 2011.5) 部分代码参考“邹建”写的导出代码
*/
Create PROC [dbo].[proc_ExportDataToExcel]
(
    @SelectSQL nvarchar(4000),--查询语句
    @HasOrderBy bit =0, --sql语句最后是否有Order by
    @PrimaryKey varchar(30),--主键id名称
    @FileSavePath nvarchar(500),--excel文件存放目录如，D:/1.xls
    @SheetName nvarchar(250)=N''--要创建的工作表名,默认为Sheet加索引
)
AS 
BEGIN     
    declare @tableName varchar(55)--随机临时表名称
    declare @pageSize int --excel一个表显示多少条数据
    declare @sql varchar(max),@tempSQL varchar(max)
    declare @errorMsg bit
    set @pageSize=65500

    --生成随机名称，防止多个人同时导出数据问题               
    select @tableName = replace('##ExportDataToExcel'+Convert(varchar(36),newid()),'-','')
    
    --拼接复制表结构的sql语句
    --判断第一个select后面是否有top
    declare @hasTop varchar(10)
    declare @index int
    set @index=charindex(' ',@SelectSQL)
    set @hasTop=lower(ltrim(substring(@SelectSQL,@index+1,10)))
    set @hasTop=ltrim(substring(@hasTop,0,4))
    if(@hastop='top')
        begin --将其它top换成top 0
            set @tempSQL=substring(@SelectSQL,12,len(@SelectSQL)-11)--截取"select top "之后字符串
            set @index=patindex('%[0-9][^0-9]%', @tempSQL)--查询top后最后一个数字位置
            set @pageSize=cast(substring(@tempSQL,0,@index+1) as int)    
            if @pageSize > 65500 set @pageSize=65500        
            set @tempSQL='select top 0 '+substring(@tempSQL,@index+1,len(@tempSQL)-@index)
        end
    else
        begin--在第一个select后面加上top 0            
            set @tempSQL='select top 0 '+substring(@SelectSQL,8,len(@SelectSQL)-7)    
        end    

    --通过查询语句创建用于复制表结构的空临时表
    begin try
        set @sql='select * into '+@tableName+' from ('+@tempSQL+') as temp where 1=0'
        exec (@sql)    
    end try
    begin catch
        raiserror('创建复制表结构的空临时表失败！',16,1)
        return
    end catch;

    --查询总记录数
    declare @recordCount int,@sheetCount int--记录总数和excel中表数
    begin try
        declare @recordCountSQL nvarchar(max)
        declare @position int
        set @position = charindex(reverse('order by'),reverse(@SelectSQL))    
        if @HasOrderBy=1
            begin--去除order by        
                if @position>0
                    begin
                        set @position=len(@SelectSQL)-@position-7
                        set @recordCountSQL='select @i=count(*) from ('+substring(@SelectSQL, 0,@position)+') as temp'
                    end
                else
                    begin
                        raiserror('查询语句最后不包含order by，和参数指定不符合',16,1)
                        return
                    end
            end    
        else
            set @recordCountSQL='select @i=count(*) from ('+@SelectSQL+') as temp'
        execute sp_executesql @recordCountSQL,N'@i int out',@recordCount out
    end try
    begin catch
        raiserror('查询总记录数失败！可能hasOrderBy参数设置错误！',16,1)
        return
    end catch;

    --需要生成的excel工作表数目
    set @sheetCount = ceiling(@recordCount/cast(@pageSize as float))

    --查询表结构
    declare @columnName nvarchar(4000)
    declare @columnName2 nvarchar(4000)
    select @columnName=isnull(@columnName+',','')+SC.name,@columnName2=isnull(@columnName2+',','')+SC.name+' '+ 
    case when ST.name like '%char'
            then case when SC.length>255 then 'memo' else 'text('+cast(SC.length as varchar)+')' end
         when ST.name like '%int' or ST.name='bit' then 'int'
         when ST.name like '%datetime' then 'datetime'
         when ST.name like '%money' then 'money'     
         when ST.name like '%text' then 'memo'
         when ST.name='uniqueidentifier' then 'text(36)'
         else ST.name end
    from tempdb..sysobjects SO,tempdb..syscolumns SC,tempdb..systypes ST 
    where SO.id=SC.id and SO.xtype='U' and SO.status>=0 and SC.xtype=ST.xusertype and SO.name=@tableName
    and ST.name not in('image','sql_variant','varbinary','binary','timestamp')
    order by SC.colorder
    
    --删除用于复制表结构的空临时表
    declare @dropTableSql varchar(200)
    set @dropTableSql='if exists(select * from tempdb..sysobjects where name='''+@tableName+''') drop table '+@tableName
    exec (@dropTableSql)
            
    /*开启sp_oacreate服务,需要sysadmin权限*/
    begin try
        exec sp_configure 'show advanced options', 1
        reconfigure
        exec sp_configure 'ole automation procedures', 1
        reconfigure
        /*开启openrowset服务*/
        exec sp_configure 'show advanced options',1
        reconfigure
        exec sp_configure 'Ad Hoc Distributed Queries',1
        reconfigure
    end try
    begin catch
        raiserror('开启sp_oacreate服务失败，无法导出数据，可能数据库登录帐号没有sysadmin权限',16,1)
        return
    end catch;        

    declare @err int,@out int,@obj int,@src nvarchar(255),@desc nvarchar(255)
    --创建excel数据源，新建excel文件    
    exec @err=sp_oacreate 'adodb.connection',@obj out
    if @err<>0 begin set @errorMsg=1 goto errorMsg end
    set @sql='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES;DATABASE='+@FileSavePath+'"'    
    exec @err=sp_oamethod @obj,'open',null,@sql
    if @err<>0 begin set @errorMsg=1 goto  errorMsg end
    
    --导出数据
    declare @startRow varchar(50)
    declare @sheetIndex int--当前excel表索引    
    declare @SheetNameNow nvarchar(300)    
    declare @orderBy varchar(30)--order by条件        
    if @HasOrderBy=1
        set @orderBy=','+@PrimaryKey
    else
        set @orderBy=' order by '+@PrimaryKey
    set @sheetIndex=@sheetCount    

    begin try
        while @sheetIndex > 0 
        begin
            --excel工作表名
            if @SheetName ='' or @SheetName is null
                set @SheetNameNow='Sheet'+cast((@sheetCount-@sheetIndex+1) as varchar)
            else
                set @SheetNameNow=@SheetName+cast((@sheetCount-@sheetIndex+1) as varchar)

            --在excel文件中创建@SheetNameNow工作表、插入表头
            set @sql='create table ['+@SheetNameNow+']('+@columnName2+')'
            exec @err=sp_oamethod @obj,'execute',@out out,@sql
            if @err<>0 begin set @errorMsg=1 goto errorMsg end
            
            set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;DATABASE='+@FileSavePath+''',['+@SheetNameNow+'$])'                    
            set @startRow = cast(((@sheetCount-@sheetIndex)*@pageSize) as nvarchar(50))

            if(@hastop='top')
                begin
                    set @tempSQL=substring(@SelectSQL,12,len(@SelectSQL)-11)--截取"select top "之后字符串
                    set @index=patindex('%[0-9][^0-9]%', @tempSQL)--查询top后最后一个数字位置
                    if @sheetIndex=@sheetCount
                        set @tempSQL='select top '+cast(@pageSize as varchar)+' '+substring(@tempSQL,@index+1,len(@tempSQL)-@index)    
                    else
                        set @tempSQL='select top '+@startRow+' '+substring(@tempSQL,@index+1,len(@tempSQL)-@index)    
                end
            else
                begin
                    if @sheetIndex=@sheetCount                            
                        set @tempSQL='select top '+cast(@pageSize as varchar)+' '+substring(@SelectSQL,8,len(@SelectSQL)-7)
                    else
                        set @tempSQL='select top '+@startRow+' '+substring(@SelectSQL,8,len(@SelectSQL)-7)
                end

            if @sheetIndex=@sheetCount--第一页
                exec('insert into '+@sql+'('+@columnName+') select '+@columnName+' from ('+@tempSQL+') as temp')                

            if @sheetIndex < @sheetCount--top max分页
                begin
                    exec('insert into '+@sql+'('+@columnName+') select top '+@pageSize+' '+@columnName
                    +' from ('+@SelectSQL+') as temp where '+@PrimaryKey+'>(SELECT MAX('+@PrimaryKey+') FROM ('+@tempSQL+@orderBy+') AS T)')                    
                end   
            set @sheetIndex=@sheetIndex-1
        end
    end try
    begin catch
        raiserror('导出数据失败！',16,1)
        return
    end catch;

    errorMsg:
        begin
            if @errorMsg=1
                begin
                    exec sp_oageterrorinfo 0,@src out,@desc out
                    declare @msg nvarchar(max)
                    select @msg=isnull(@msg,'')+'错误源:'+@src+',错误描述:'+@desc+' '
                    raiserror(@msg,16,1)
                end
        end
    exec sp_oamethod @obj,'close',null--关闭excel连接
    
    /*关闭相关服务    
    begin try
        /*关闭sp_oacreate服务
        exec sp_configure 'show advanced options', 1
        reconfigure
        exec sp_configure 'ole automation procedures',0
        reconfigure */

        /*关闭openrowset服务
        exec sp_configure 'Ad Hoc Distributed Queries',0
        reconfigure
        exec sp_configure 'show advanced options',0
        reconfigure */        
    end try
    begin catch
    end catch; */
END

C#调用代码

        volatile static int outputCount = 0;
        private static object lockOutput = new object();
        /// <summary>
        /// 导出数据到excel文件中，支持3个人同时导出数据，因为导出大量数据时消耗cpu资源高，所以限制3个人同时导出数据，可以根据情况进行调整
        /// </summary>
        /// <param name="selectSQL">查询的sql语句</param>
        /// <param name="hasOrderBy">sql语句最后是否有Order by</param>
        /// <param name="primaryKey">主键</param>
        /// <param name="fileSavePath">文件保存路径，绝对路径</param>
        /// <param name="sheetName">要创建的工作表名,默认为Sheet加索引</param>
        public static void ExportDataToExcel(string selectSQL, bool hasOrderBy, string primaryKey, string fileSavePath, string sheetName)
        {
            if (outputCount <= 3)
            {
                lock (lockOutput)
                {
                    outputCount++;
                }
                if (outputCount <= 3)
                {
                    try
                    {
                        SqlHelper helper = new SqlHelper();
                        helper.CreateStoredProcedureCommand("[proc_ExportDataToExcel]");
                        helper.AddParameter("@SelectSQL", selectSQL);
                        helper.AddParameter("@HasOrderBy", hasOrderBy);
                        helper.AddParameter("@PrimaryKey", primaryKey);
                        helper.AddParameter("@FileSavePath", fileSavePath);
                        helper.AddParameter("@SheetName", sheetName);
                        helper.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        lock (lockOutput)
                        {
                            outputCount--;
                        }
                    }
                }
                else
                {
                    lock (lockOutput)
                    {
                        outputCount--;
                    }
                    throw new Exception("当前已有3人正在导出数据，为节约服务器资源，请稍候再试!");
                }
            }
            else
            {
                throw new Exception("当前已有3人正在导出数据，为节约服务器资源，请稍候再试!");
            }
        }

 string fileSavePath = Server.MapPath("~/TempFile/导出数据/") + "用户名单(" + DateTime.Now.ToShortDateString() + ")" + DateTime.Now.ToFileTime() + ".xls";