1

How to send data in Excel file from sp_send_dbmail with column name to be bold using database query in a proper format?

Below code is giving me excel file with column names as:

Document ticket_number status 1000. 200043. Pending

I want column name to be bold (Document ,ticket_number ,status) and the data should be in a tabular form otherwise it is not looking good.

My code is:

 Declare @body nvarchar(max)


        Set @body='<html> <p>hi</p></html>'



        Declare @attach nvarchar(max)

        Set @attach='refund'+'.xls'

        Declare @qry nvarchar(max)

        @qry='select document,ticket,status,agent from tbl'

        ---sending an email 
        Exec msdb.dbo.sp_send_dbmail
        @profile_name='y',
        @recipients='ab@gmail.com',
        @body=@body,
        @body_format='html',
        @query=@qry,
        @subject='db data',
        @attach_query_result_as_file=1,
        @query_attachment_filename=@attach,
        @query_result_header=1,
        @query_result_seperator= '.  ',
        @query_result_no_padding=1;
mahadev dhyani
  • 77
  • 9
  • 1
    It's _very_ difficult to change the excel file format from SQL Server. In fact, I don't think it's an excel file - it's a CSV file that happens to open in excel. If the objective is to send a nice formatted report, you could use a subscription in SSRS. Or you could do some custom T-SQL to generate the attachment as nicely formatted HTML. If the attachment must be nicely formatted excel then you could write some powershell to do it. What is you preference? – Nick.Mc Jul 05 '18 at 06:32
  • Oh another option for extracting and formatting an excel file is SSIS. All of these options require you to install excel somewhere and run nasty formatting code. Is the objective to have a nicely formatted report or is the objective to send a datafile that just looks nice? – Nick.Mc Jul 05 '18 at 06:34
  • @Nick.McDermaid hi nick if it is not possible could you explain using html am i able to change the format of column name to be bold in my excel file – mahadev dhyani Jul 05 '18 at 06:40
  • html and excel are different types of files. You can only have one or the other. We need to establish what you are trying to do. Does the user need to be able to edit the data in the attachment? Or does the user just want to see a pretty report? – Nick.Mc Jul 05 '18 at 06:43
  • user does need to be able to edit the data in attachment.. – mahadev dhyani Jul 05 '18 at 06:51
  • In that case it can’t be html. It has to be csv or xlsx. Csv is what you have now but that won’t let you apply formatting. So xlsx it is. The options I can think of are: 1. Send using SSRS Subscription 2. Export and format from SSIS. Both of these are beyond a simple explanation. I’ll see if I can find any other solutions. – Nick.Mc Jul 05 '18 at 06:57
  • Another option is powershell. Here’s an example that exports as excel. You just need to add code to format and email. http://www.sqlserver-dba.com/2013/05/sql-server-export-to-excel-with-powershell.html – Nick.Mc Jul 05 '18 at 07:04
  • I don't have any need to store any file i just want to trigger the mail to the specific users on daily basis – mahadev dhyani Jul 05 '18 at 09:03
  • If you want to send a formatted excel file, you'll need to temporarily store it somewhere while you are formatting it and before you send it. Then you can delete it afterwards. I'm going to guess that you are not comfortable with any of the technologies that I mentioned (SSIS, SSRS, Powershell) – Nick.Mc Jul 05 '18 at 10:35
  • Could i send multiple sheets in a single excel file by using multiple sql query in dbmail??? – mahadev dhyani Jul 05 '18 at 13:41
  • No. I will say this once more: _sp_send_dbmail does not send excel files_ – Nick.Mc Jul 05 '18 at 13:58
  • Yes i don't have these two options ssrs,ssis Could i send multiple excel sheets in a single excel workbook using multiple sql query results ? – mahadev dhyani Jul 05 '18 at 14:07
  • If you use powershell you probably could. – Nick.Mc Jul 05 '18 at 14:08
  • Thanks @nick could you tell is there any option in sql server to send multiple sheets in a single excel as an email – mahadev dhyani Jul 11 '18 at 17:56
  • You might be able to use ‘sp_OACreate’ but you need to install Excel on the server. It’s not recommended though. – Nick.Mc Jul 11 '18 at 22:39
  • Apart from this is there any solution available i mean as mentioned in code could i put multiple queries to get multiple sheets in single excel workbook. – mahadev dhyani Jul 11 '18 at 23:42
  • Here's another way https://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file/9086889 – Nick.Mc Jul 11 '18 at 23:55
  • Thanks @nick as given sql code i am getting proper result but is there any possibility of autowidth for column name otherwise i need to expand the column?? – mahadev dhyani Jul 13 '18 at 02:02
  • To do that you first need to generate an excel file then you need to edit it using the excel object model. Currently you are not generating an excel file, you are generating a text file. Text files do not have formatting. I could go on and on making suggestions. – Nick.Mc Jul 13 '18 at 02:43
  • thanks @Nick.McDermaid – mahadev dhyani Aug 02 '18 at 17:22

0 Answers0