How to place query results into an Excel file with colored columns?

I need开发者_开发问答 to create a T-SQL job that generates an Excel file. Right now I'm doing it with the sp_makewebtask stored procedure, but it doesn't have support for specifying colors.

EDIT: Crap. I need row level colors in the Excel file.

For your specific task, CLR may be the easiest way to go. I haven't used it for years, though, so I'll offer a few tips on SQL Server Integration Services instead.

SSIS is a big topic, and it has some maddening flaws, but if you frequently need to move data around on a scheduled basis, it is worth the investment to learn it. For one thing, if you have an MS SQL license, SSIS is included.

To feed data from SQL into an Excel file, you'll want to add a tab to your workbook to receive the data. Create a copy of this workbook, with all your formatting and formulae but no data; let's call it "Template". Copy this workbook and we'll call the second file "Working". When your SSIS package runs in the future, it'll modify the Working file but leave the Template untouched.

• Create a new SSIS project with Visual Studio Business Intelligence Studio.
• Under Connection Managers, add a File connection for Template and one for Working. Add a SQL connection for your source database, and an Excel connection for Working. Note that Working appears twice: once as a file, once as an Excel "database".
• In your toolbox, drag a File System Task into your project, and set its properties to copy the Template to the Working file, so you'll start with a clean slate. Be sure to check "Overwrite destination".
• Next, add a Data Flow Task. Within this task, you'll go to the Toolbox and add a Data Flow Source of "OLE DB Source" (or whatever driver you chose for SQL), a Data Flow Transformation "Data Conversion", and a Data Flow Destination "Excel Destination".
• Drag the arrows to connect your SQL source to the Data Conversion step, and connect the Data Conversion step to Excel.
• Open the properties for your SQL source, and set the specific connection to use (which you created in step 2), and select a source table, view, or query.
• Open the Data Conversion step. It'll show the fields you included in the previous step. If you have any CHAR or VARCHAR fields, you'll want to add them to the list shown here, and convert them into Unicode - Excel seems to require this.
• In Excel, map the fields from the previous step to columns in your data tab. Be sure to use the Unicode text fields, and not the original CHAR/VARCHAR fields.

That should cover the basics. You'll probably want to skim a tutorial. If specific things give you trouble, SO can probably help. Good luck!