How to authorize excel cells when copying data from SQL server

Question

I am exporting sql data to excel using a stored procedure. when opening excel, several cell values ​​are partially hidden, I want to resize it, including the title. and want to make the headline bolder .. plz help me solve this problem.

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_Reports\daily_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_Reports\data_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) 

Hello,

T.Navin

Error saving macro code:

Answer 1

I assume you are passing data as csv (from your code). CSV files cannot be formatted.

After that, you can convert the csv file to xlsx or xls into your programming language.

Answer 2

You cannot paste formatting information into a CSV file that you need. A CSV file can only contain data. Cm

http://office.microsoft.com/en-us/excel-help/excel-formatting-and-features-that-are-not-transferred-to-other-file-formats-HP010014105.aspx?CTT=5&origin= HP010099725 # BM4

You can try a different file format that Excel can open, for example, using xml, however this is not easy, and this will require headers and footers that are not trivial to generate in proc.

I know that you just said that you run sp from the scheduler, so this may not be for you, but you might think about creating an Excel file and then running proc from Excel and paste the results into a pre-formatted table.

Answer 3

From what I know so far (wanting to learn something new in this regard, though), the only way to do this is to pre-format the excel file and use templates.

However, you can also run this if using macros is not a problem:

 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 

Make sure the code is in the excel file that you use as the template, then first make a copy of it. Thing is .. I'm not sure what will happen if you use it to open CSV files with excel. Maybe you can try?