将数据从存储过程导出到Excel电子表格 [英] Exporting data from stored procedure to excel spreadsheets

问题描述

 --'Select Job_T_Job_Desc,Job_T_Job_ID from Pmsclient.dbo.PMS_Job_TManager_h 
 --'Where Job_T_Job_ID=''''JON78''''','c:\bcp\Siraj.xls'
alter Procedure [dbo].[Pms_Usp_GetBackupXlsResult1]    
      
(      
 @server Varchar(100),  
 @DynamicSql Varchar(Max),    
 @fullFileName Varchar(1000)    
    
)      
AS      
Begin      
    
--Declare @DynamicSql  Varchar(Max)  
--Set @DynamicSql='select top 5 * from test.dbo.customer'  
  
--Declare @server varchar(500)  
--Set @server='shrigi104\SHRIUNO0301'  
--Select @server  
  
       
Declare @ExcSql Varchar(Max)    
--Set @ExcSql='SELECT * INTO ##MyTempTable FROM OPENROWSET(''SQLNCLI'', 
    ''Server='+@server+';Trusted_Connection=yes;'','''+@DynamicSql+''')'    
Set @ExcSql='SELECT * INTO tempdb.dbo.##MyTempTable FROM OPENROWSET(''SQLNCLI'',
    ''Server='+@server+';UID=SA;PWD=welcome3#'',''SET NOCOUNT ON;SET FMTONLY OFF; '+@DynamicSql+''')'    
      Select (@ExcSql)  
     Exec (@ExcSql)    
     Select (@ExcSql)    
Declare @dbName Varchar(100)   
set @dbName= 'master'         
Declare  @sql varchar(5000)   
set @sql= 'Select * from tempdb.dbo.##MyTempTable'            
--Declare   @fullFileName varchar(100)   
--set @fullFileName= 'C:\bcp\ss.xls'        
Declare @dbName1 Varchar(100)     
if object_id('##TempExportData') is not null        
    drop table ##TempExportData        
if object_id('##TempExportData2') is not null        
    drop table ##TempExportData2    
          
    Select @dbName1=@dbName      
    Select @dbName = 'use ' + @dbName + ';'        
      
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)        
select    @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' +         
        
     substring(@sql, charindex('from', @sql)-1, len(@sql))        
exec(@dbName + @tempSQL)        
Select (@dbName + @tempSQL)        
      
SELECT    @columnNames = COALESCE( @columnNames  + ',', '') + column_name,        
        @columnConvert = COALESCE( @columnConvert  + ',', '') + 'convert(nvarchar(4000),'         
        + column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'        
                             when data_type in ('numeric', 'decimal') then ',128'        
                             when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'        
                             when data_type in ('datetime', 'smalldatetime') then ',120'        
                             else ''        
                        end + ') as ' + column_name        
FROM    tempdb.INFORMATION_SCHEMA.Columns        
WHERE    table_name = '##TempExportData'        
Select @columnNames      
-- execute select query to insert data and column names into new temp table        
SELECT    @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', 
    ''2'' as temp##SortID         
       from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', 
           ''1'') t order by temp##SortID'        
exec (@sql)        
Select @sql      
set   @sql = 'bcp " select * from ##TempExportData2 " queryout "' + @fullFileName + 
    '" -U uno -P uno   -c -T -S '+ @server   
Select @sql      
Exec master..xp_cmdshell @sql       
Drop table ##TempExportData      
Drop table ##TempExportData2      
Drop Table ##MyTempTable    
End

使用上述过程，我可以创建一个Excel文件，也可以使用BCP概念将数据复制到该文件中.但是现在的问题是我需要以单独的方式将值存储在sheet1，sheet2，.....

例如，我需要将第1 100条记录存储在sheet1中，然后将下100条记录存储在sheet2中，等等..

谁能帮助我解决此问题...:confused:

Using the above procedure I am able to create an Excel file and also copied data to it using BCP concept. But now the problem is I need to store the value in sheet1,sheet2,.... in separate manner..

for eg., i need to stored 1st 100 records in sheet1 and next 100 records in sheet2,...so on..

Can anyone help me to resolve this issue...:confused:

推荐答案

创建多个工作表的问题是什么.运行创建表"语句以创建工作表并插入值.

如果遇到这种情况，我将创建一个CLR存储过程来完成此工作，而不是使用OpenRowSet.

查看如何使用MDAC创建表.

使用MDAC和Oledb使用MS Excel(xls/xlsx) [在SQL Server 2005或更高版本中使用CLR对象:第一部分 [

:rose:

What is the problem of creating multiple sheets. Run Create Table statement to create sheets and insert values.

If I was in your situation, I would have created one CLR stored procedure to do this job, rather than using OpenRowSet.

See how I am using creating tables using MDAC.

Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^]

This is from .NET and you can easily convert it to OpenRowSet easily.

Also, you can create CLR stored proc using this article :
Working with CLR Objects in SQL Server 2005 or Higher: Part I[^]

Finally, when you post a question, please remove all comments in code, and use "Code Block" to make us understand code properly. I can see there are lots of lines commented out and many of us will find difficulty to read them.

:rose:

pls建议我使用现有过程...

我们不能使用上面的一个来实现..

pls suggest me with the existing procedure...

can''t we able to achieve it using the above one..

这篇关于将数据从存储过程导出到Excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！