/****** 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

        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人正在導出數據，為節約服務器資源，請稍候再試!");
            }
        }