We can use three common file formats to export SQL Server data to Excel:
Only SQL Server Integration Services support export to Microsoft Excel workbook.
SQL Server Express Edition does not allow saving an SSIS package so you can use this way only if you have SQL Server Standard or higher.
Microsoft Excel users can open CSV file the same way as a native Excel file.
So, exporting to CSV files is suitable for most cases, and you can use a simple command line utilities instead of SQL Server Integration Services.
The disadvantage of the exporting to Excel workbooks or CSV files is that users receive new files every time and lose their changes.
XML can be more suitable as a user can import XML file to an Excel workbook once and then refresh the data from new files.
The articles in this section describe three export techniques and contain a working code for a quick start.
Microsoft Excel has features that allow connecting to SQL Server and SQL Azure, importing and refreshing data.
However, Microsoft Excel data features are poor really.
You may add powerful features with Microsoft Excel add-ins.
Without any coding you can:
With a little SQL Server coding you can:
See details here: