From 2003, Excel files are just XML documents (as are all office documents)

so you could generate the xls or xlsm file dynamically - its quite a bit of work to set up .xls headers and footers but may be worth it if you need to do a lot of reports. One nice thing about this solution is that you can embed Excel formulas and conditional formatting in the spreadsheet output.1

SSIS is another alternative, but in my experience Excel and SSIS do not play nicely together, especially if you are developing in 32 bit and deploying on 64 bit. There are so many ways it can go wrong and the error messages are even more unhelpful than ususal (which you would have through would be pretty hard :-D)

Pulling data from SQL using the Excel application is probably the simplest and safest solution, provided your network permissions allow it. SSRS reports saved as Excel is another option - and probably the best if you have sharepoint installed.

Perhaps the most pragmatic approach would be to output the SQL results set FOR XML and then import the XML into Excel. If you provide a DTD then Excel is pretty good at parsing XML and producing decent looking reports.

Whatever option you choose, it will require an intermediate-advanced knowlege of the technology stack but is definately worth the investment in time and effort.