Question

i am exporting sql data into excel using stored procedure.. while opening the excel few cell values are partially hidden, i want to resize it including the header.. and want to make the header bolder.. plz help me out to solve this issue..

sql stored procedure:

alter procedure usp_example         
 (                
 @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) ,  
   @sql1 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.csv                 


      --Generate column names in the passed EXCEL file                
       set @sql= bcp " select   DATE  , *,  SHIFT   ,  SETUPTIME   from (select  +@columns+ ) as t" queryout   c:Daily_Reportsdaily_machinedescription_report_summary.csv  -c -t,  -T -S   + @@servername                
        exec master..xp_cmdshell @sql               
        print @sql             



     --Generate data in the dummy file        

    set @sql= bcp "SELECT convert(varchar, ENTRY_DATE, 105) as DATE ,machinename as Machine,RunTime,ECTime,ReworkTime as RWTime,IdleTime,DownTime,SHIFT_TYPE as Shift,SetupTime  FROM VentureBI_Prod_Test..view_admin_report ORDER BY segment " queryout   c:Daily_Reportsdata_file.csv  -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)

“entergraph

关于

T.Navin

Error while saving Macro Code:

“entergraph

Answer 1

I guess you are transferring the data as csv (from your code). csv files can not be formatted.

之后,你可以在选择的平方位将焦夫案转换为Xx或xls。

Answer 2

您可以将信息格式插入特别志愿人员文件,这是你所要求的。 CSV档案只能包含数据。 See See See

You could try a different file format that Excel can open, for instance using xml, however that isn t easy and it will require headers and footers that aren t trivial to generate in a proc.

我知道,你只是说,你从一个表板上重新管理间谍,这样你可能不是一种选择,但你可以考虑创建Excel文档,然后管理Excel的皮书,并将结果插入一个预先设计的电子表格。

Answer 3

从我迄今知道的情况来看(希望了解这方面的新情况,尽管如此),我这样做的唯一途径是预先准备前线文件和使用模板。

, ,

Option Explicit 

Sub AutoFitAllColumns() 

    Application.ScreenUpdating = False 

    Dim strWorksheetName As String 
    Dim objWorkBook As Worksheet 

    strWorksheetName = ActiveSheet.Name 

    For Each objWorkBook In ActiveWorkbook.Worksheets 
        On Error Resume Next 
        objWorkBook.Activate 
        Cells.EntireColumn.AutoFit 
    Next objWorkBook

    Sheets(strWorksheetName).Select 
    Application.ScreenUpdating = True 

End Sub 

Private Sub Workbook_Open()
    AutoFitAllColumns
End Sub

确保法典放在你作为模板使用的Excel档案中,然后首先提供该守则的副本。页: 1 我不相信,如果你利用它打开使用Excel的.csv档案,会发生什么情况。你们是否可以尝试?

sql stored procedure:

Error while saving Macro Code:

友情链接