Need help on excel file generation through backend

Question

Need help on excel file generation through backend

vikramchander90

Ten Centuries

Points: 1093
More actions
September 8, 2015 at 5:28 am

#311415

Hi,
I have a requirement of generating an "Sales Report" excel file and mailing that file to a particular email id.
I have generated the excel file through bcp command and mail is working fine.
However formatting of excel file is required by end user for eg. Cells to be merged and borders to be given for cells for which i think bcp command will not work.
Is there any command that can be used to achieve this? Can any 1 suggest pls.
If possible with Eg.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 1

Kind of. As you've found out, BCP is not actually capable of exporting "Excel" files. It's only capable of exporting text (and data formatted specifically for SQL Server) which usually takes on the form of TAB or COMMA delimited files.

SQL Server used to have a wonderful tool called sp_MakeWebTask but MS deprecated that and removed it because they think that SSIS is the way to go. What you can do, instead, it to create a formatted HTML output that you can place directly in the email or use BCP to export the HTML result to a file. If you follow the link at https://msdn.microsoft.com/en-us/library/ms190307.aspx, you'll find that example "C" contains a simple example of how to create basic HTML output from a query. Let your imagination run after that. I use the technique for my morning job and disk status reports.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Even with SSIS, I'm not sure that formatting is possible without some code on a Script Task.

Another way to do it is by creating a Macro in Excel and run it on the files.

If you're not familiar with coding for Excel, you can record the macro and use the code generated as a guide for your solution.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

vikramchander90 Ten Centuries Points: 1093 More actions · Answer 3

Hi,

Can any 1 help to find if this solution is possible.

Keep the header as a separate file and use BCP / Openrow set to append data into that file?