1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. go
  4.  
  5. /*
  6.  将查询结果集直接导出到excel文件中(包含表头,别名不能包含\'(\')，通过SQLServer数据库内置对象提高数据导出速率(事理 2011.5) 
  7.  exec proc_ExportDataToExcel \'.\SQL2005\',\'OA\',\'asdfef85\',\'\',\'select  top 10* from SL_User where UserId<500\',\'D:/1.xls\'
  8.  判断xp_cmdshell存储过程是否存在select count(*) from master.dbo.sysobjects where xtype=\'X\' and name=\'xp_cmdshell\'
  9.  注意：使用此存储过程，数据库登录用户要有sysadmin权限，需要创建表AppLock管理xp_cmdshell的开启与关闭
  10.     create table AppLock (Id int not null identity(1,1) primary key,SessionCount int not null,Name varchar(50) not null)
  11.     insert into AppLock values(0,\'proc_ExportDataToExcel\')
  12. */
  13. create PROC [dbo].[proc_ExportDataToExcel]
  14. (
  15.     @server nvarchar(50),--数据库服务名称
  16.     @database nvarchar(50),--数据库名称
  17.     @uid nvarchar(50),--数据库登录用户名
  18.     @pwd varchar(50),--数据库登录密码
  19.     @selectSQL varchar(7000),--查询语句
  20.     @fileSavePath nvarchar(500)--excel文件存放目录如，D:/1.xls
  21. )
  22. AS 
  23. BEGIN       
  24.     declare @errorSum int --记录错误标志   
  25.     declare @sql varchar(8000)
  26.     declare @tableName varchar(55)--随机临时表名称
  27.     declare @tempTableName varchar(55)     
  28.     set @errorSum = 1
  29.  
  30.     --生成随机名称，防止多个人同时导出数据问题               
  31.     select @tableName = replace(\'##ExportDataToExcel\'+Convert(varchar(36),newid()),\'-\',\'\')
  32.     set @tempTableName=@tableName+\'Temp\'
  33.     
  34.     --拼接复制表结构的sql语句
  35.     declare @tempSQL varchar(7000)
  36.     --判断第一个select后面是否有top
  37.     declare @hasTop varchar(10)
  38.     declare @index int
  39.     set @index=charindex(\' \',@selectSQL)
  40.     set @hasTop=lower(ltrim(substring(@selectSQL,@index+1,10)))
  41.     set @hasTop=substring(@hasTop,0,4)
  42.     if(@hastop=\'top \')
  43.         begin
  44.             --将其它top换成top 0
  45.             set @tempSQL=substring(@selectSQL,12,len(@selectSQL)-11)--截取"select top "之后字符串
  46.             set @index=patindex(\'%[0-9][^0-9]%\', @tempSQL)--查询top后最后一个数字位置
  47.             set @tempSQL=\'select top 0 \'+substring(@tempSQL,@index+1,len(@tempSQL)-@index)    
  48.         end
  49.     else
  50.         begin
  51.             --在第一个select后面加上top 1
  52.             set @tempSQL=\'select top 0 \'+substring(@selectSQL,8,len(@selectSQL)-7)
  53.         end
  54.  
  55.     --通过查询语句创建用于复制表结构的空临时表
  56.     begin try
  57.         set @sql=\'select * into \'+@tempTableName+\' from (\'+@tempSQL+\') as temp where 1=0\'        
  58.         exec (@sql)
  59.         set @errorSum = @errorSum+1        
  60.     end try
  61.     begin catch
  62.         raiserror(\'创建复制表结构的空临时表失败！\',16,1)
  63.         return @errorSum
  64.     end catch;
  66.     --查询表结构
  67.     declare @columnName nvarchar(4000)
  68.     declare @columnName2 nvarchar(4000)
  69.     select @columnName=isnull(@columnName+\',\',\'\')+\'\'\'\'+SC.name+\'\'\'\',@columnName2=
  70.     case when ST.name in(\'text\',\'ntext\') then isnull(@columnName2+\',\',\'\')+SC.name
  71.          when ST.name in(\'char\',\'varchar\') then isnull(@columnName2+\',\',\'\')+\'cast(\'+SC.name+\' as varchar(\'+cast((case when SC.length<255 then 255 else SC.length end) as varchar)+\')) \'+SC.name         
  72.          when ST.name in(\'nchar\',\'nvarchar\') then isnull(@columnName2+\',\',\'\')+\'cast(\'+SC.name+\' as nvarchar(\'+cast((case when SC.length<255 then 255 else SC.length end) as varchar)+\')) \'+SC.name          
  73.          else isnull(@columnName2+\',\',\'\')+\'cast(\'+SC.name+\' as varchar(1000)) \'+SC.name end
  74.     from tempdb..sysobjects SO,tempdb..syscolumns SC,tempdb..systypes ST 
  75.     where SO.id=SC.id and SO.xtype=\'U\' and SO.status>=0 and SC.xtype=ST.xusertype and SO.name=@tempTableName
  76.     and ST.name not in(\'image\',\'sql_variant\',\'varbinary\',\'binary\')
  77.     order by SC.colorder
  78.     
  79.     declare @dropTableSql varchar(200)
  80.     begin try        
  81.         --创建全字符串类型的空临时表
  82.         set @sql=\'select * into \'+@tableName+\' from (select \'+@columnName2+\' from \'+@tempTableName+\' where 1=0) as temp\'
  83.         exec (@sql)
  85.         --删除临时空临时表
  86.         set @dropTableSql=\'if exists(select * from tempdb..sysobjects where name=\'\'\'+@tempTableName+\'\'\') drop table \'+@tempTableName
  87.         exec (@dropTableSql)    
  88.         
  89.         --插入列名(表头)
  90.         set @sql=\'insert into \'+@tableName+\' values(\'+@columnName+\')\'
  91.         exec (@sql)    
  92.         
  93.         --插入数据到临时表
  94.         set @sql=\'insert into \'+@tableName+\' select * from (\'+@selectSQL+\') as temp\'
  95.         exec (@sql)    
  96.         set @errorSum = @errorSum+1            
  97.     end try
  98.     begin catch
  99.         raiserror(\'创建数据临时表或往临时表中插入数据失败！\',16,1)
  100.         exec (@dropTableSql)
  101.         return @errorSum
  102.     end catch
  104.     --删除数据临时表
  105.     set @dropTableSql=\'if exists(select * from tempdb..sysobjects where name=\'\'\'+@tableName+\'\'\') drop table \'+@tableName
  106.     --导出数据
  107.     begin try
  108.         declare @sessionCount int
  109.         select @sessionCount=SessionCount from AppLock where [Name]=\'proc_ExportDataToExcel\'
  110.         if @sessionCount=0
  111.         begin
  112.             /*开启xp_cmdshell,数据库登录用户要有sysadmin权限*/        
  113.             begin try            
  114.                 EXEC sp_configure \'show advanced options\', 1
  115.                 RECONFIGURE
  116.                 EXEC sp_configure \'xp_cmdshell\', 1
  117.                 RECONFIGURE        
  118.                 EXEC sp_configure \'show advanced options\', 0
  119.                 RECONFIGURE
  120.             end try
  121.             begin catch
  122.             end catch;
  123.         end
  124.  
  125.         --更新一个表时，默认有排他锁
  126.         update AppLock set SessionCount=SessionCount+1 where [Name]=\'proc_ExportDataToExcel\'
  127.         set @sql=\'master..xp_cmdshell \'\'bcp "select * from \'+@database+\'.dbo.\'+@tableName+\'" queryout "\'+@fileSavePath+\'" -c -S"\'+@server+\'" -U"\'+@uid+\'" -P"\'+@pwd+\'"\'\'\'        
  128.         exec (@sql)
  129.         update AppLock set SessionCount=SessionCount-1 where [Name]=\'proc_ExportDataToExcel\'
  130.         set @errorSum = @errorSum+1
  131.  
  132.         declare @sessionCount2 int
  133.         select @sessionCount2=SessionCount from AppLock where [Name]=\'proc_ExportDataToExcel\'
  134.         if @sessionCount2=0
  135.         begin
  136.             /*关闭xp_cmdshell，加锁使用才能不造成冲突*/
  137.             begin try            
  138.                 EXEC sp_configure \'show advanced options\', 1
  139.                 RECONFIGURE
  140.                 EXEC sp_configure \'xp_cmdshell\', 0
  141.                 RECONFIGURE    
  142.                 EXEC sp_configure \'show advanced options\', 0
  143.                 RECONFIGURE
  144.             end try
  145.             begin catch
  146.             end catch;
  147.         end
  148.     end try
  149.     begin catch  
  150.         exec (@dropTableSql)
  151.         declare @errorMsg nvarchar(4000)
  152.         set @errorMsg=ERROR_MESSAGE()  
  153.         if(@errorMsg is not null)
  154.             raiserror(@errorMsg,16,1)
  155.         return @errorSum
  156.     end catch;
  157.         
  158.     exec (@dropTableSql)  --删除数据临时表      
  159.     return @errorSum
  160. END

//调用

  1.         /// <summary>
  2.         /// 导出数据到excel文件中
  3.         /// </summary>
  4.         /// <param name="selectSQL">select查询sql语句</param>
  5.         /// <param name="fileSavePath">文件保存路径</param>
  6.         /// <returns>返回消息</returns>
  7.         public static void ExportDataToExcel(string selectSQL, string fileSavePath)
  8.         {
  9.             string connectionString = SQLHelper.ConnectionString;
  10.             //获取连接字符串数据库服务器、数据库名称、用户名和密码
  11.             Dictionary<string, string> dic = new Dictionary<string, string>();
  12.             string[] tempArray = connectionString.Split(new char[] { \';\' }, StringSplitOptions.RemoveEmptyEntries);
  13.             foreach (string str in tempArray)
  14.             {
  15.                 string[] temp = str.Split(\'=\');
  16.                 dic.Add(temp[0].Trim().ToLower(), temp[1].Trim());
  17.             }
  19.             string server = dic.Keys.Contains("data source") ? dic["data source"] : dic["server"];
  20.             string database = dic.Keys.Contains("initial catalog") ? dic["initial catalog"] : dic["database"];
  21.             string pwd = string.Empty;
  22.             if (dic.Keys.Contains("password"))
  23.                 pwd = dic["password"];
  24.             else if (dic.Keys.Contains("pwd"))
  25.                 pwd = dic["pwd"];
  26.             string userID = string.Empty;
  27.             if (dic.Keys.Contains("user id"))
  28.                 userID = dic["user id"];
  30.             SQLHelper helper = new SQLHelper();
  31.             helper.InitStoredProcedure("[proc_ExportDataToExcel]");
  32.             helper.AddParameter("@server", server);
  33.             helper.AddParameter("@database", database);
  34.             helper.AddParameter("@uid", userID);
  35.             helper.AddParameter("@pwd", pwd);
  36.             helper.AddParameter("@selectSQL", selectSQL);
  37.             helper.AddParameter("@fileSavePath", fileSavePath);
  38.             helper.AddReturnParameter("@errorSum");
  39.             try
  40.             {
  41.                 helper.ExecuteNonQuery();
  42.                 int result = (int)helper.GetValue("@errorSum");
  43.                 if (result == 1)
  44.                     throw new Exception("创建复制表结构的空临时表失败!");
  45.                 else if (result == 2)
  46.                     throw new Exception("创建临时表或往临时表中插入数据失败!");
  47.                 else if (result == 3)
  48.                     throw new Exception("导出数据失败!");
  49.                 else if (result == 4)////"导出成功!";
  50.                 {
  51.                 }
  52.             }
  53.             catch (Exception ex)
  54.             {
  55.                 throw ex;
  56.             }
  57.         }

第二种

  1. /*有存在1.xls文件，并且设置好了表头
  2.  INSERT INTO OPENDATASOURCE(\'Microsoft.JET.OLEDB.4.0\',   
  3.  \'Extended Properties=Excel 8.0;Data source=d:\1.xls\')...[sheet1$]   
  4.  select 销售数量,销售金额,零售金额,专柜代码,供应商品名称,成本,毛利额,毛利率 from (select sum( quantity )as 销售数量 ,sum( salessum ) as 销售金额,sum( salessum +rebatesum ) as 零售金额,d_product_sale_list.shoppeno as 专柜代码 ,\'(\'+isnull(A_TradeMember.memcode,0) + \')\'+isnull(A_TradeMember.memname,0) as 供应商品名称, sum(isnull(cost,0)+isnull(costtax,0)) as 成本,sum(salessum)-sum( isnull(cost,0) +isnull(costtax,0) ) as 毛利额,case  when sum(salessum)<>0 then (sum(salessum)-sum( isnull(cost,0) +isnull(costtax,0) ))/sum(salessum)else 0 end  as 毛利率  from (SELECT  * FROM d_product_sale_list UNION ALL SELECT  * FROM d_product_sale_list20114) d_product_sale_list , A_TradeMember,a_protocol  where shoppeno is not null and a_protocol.mem_id = A_TradeMember.mem_id  and saledate>=\'2011-4-01\'  and  saledate<=\'2011/4/22 0:00:00\'  group by d_product_sale_list.shoppeno, A_TradeMember.memname,A_TradeMember.memcode) a
  5.  */

 

版权声明：本文为slyzly原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/slyzly/articles/2657424.html