使用列名将存储过程结果导出到excel [英] Exporting stored procedure result to excel with column name

问题描述

我已将用于存储存储过程输出的代码附加到Excel工作表中.

I have attached the code for storing the stored procedure output to an Excel sheet..

但是我有几个问题，

(1)所有列都显示在excel工作表的第一列中.我不需要，我需要在diff列中显示报告.如何做到这一点.

(1) all the columns are displayed in the first column of the excel sheet..which I don't need, I need to show the report in diff columns.. how to do that..

(2)如果我将main与内部连接一起使用，如何获取另一个表的列名

(2) if I use inner join with the main, how can I get the column names of the other table

任何人都可以帮助我解决上述问题吗?

Can anyone please help me out to solve the above issues?

alter procedure proc_generate_excel_with_columns
(
  @db_name varchar(100),
  @schm_name varchar(100),
  @table_name varchar(100), 
  @file_name varchar(100)
)
as

  --Generate column names as a recordset
  declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)

  select 
     @columns = coalesce(@columns+',', '') + column_name + ' as ' + column_name 
  from 
     information_schema.columns
  where 
     table_name = @table_name

  select 
     @columns = '''' + replace(replace(@columns, ' as ', ''' as '),',',',''')

  --Create a dummy file to have actual data
  select 
    @data_file = substring(@file_name, 1, len(@file_name) - 
                 charindex('\', reverse(@file_name))) + '\data_file.xls'

  --Generate column names in the passed EXCEL file
  set @sql='bcp " select * from (select ' + @columns + ') as t" queryout   
      c:\test.xls  -c -t,  -T -S ' + @@servername

  exec master..xp_cmdshell @sql

  --Generate data in the dummy file
  set @sql = 'bcp "select * from st..VEN_FULLREPORTMASTER 
                   where entry_date =  convert(varchar, getdate() - 3, 105) " 
              queryout  c:\data_file.xls  -c -t,  -T -S' + @@servername

  -- exec master..xp_cmdshell @sql

  -- Copy dummy file to passed EXCEL file
  set @sql = 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''

  exec(@sql)

  ----Delete dummy file 
  set @sql = 'exec master..xp_cmdshell ''del ' + @data_file + ''''
  exec(@sql)

推荐答案

如果可以选择使用SSIS，那么使用数据流任务来完成这项任务将是一项微不足道的任务.

If using SSIS is an option this would be a trivial task to complete using a data flow task.

您可以使用SSIS还是需要纯的tSQL解决方案?

Can you use SSIS or do you need a pure tSQL solution?

这篇关于使用列名将存储过程结果导出到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！