Over the years there have been a number of methods to move SQL data into Microsoft Excel. Using Automation you can call methods and properties that are specific to excel which gives you the greatest flexibility for specifying the location of your data in the workbook. The following are two recent approaches to export Sql Server table row data to an excel workbook.
1.) Using Automation you can use transfer data cell by cell
2.) Transfer data in an array to a range of cells
I created a WPF application with a button "Export" for this code. The click event creates performs the the following actions
a.) Gets a reference to the automation object
b.) Adds a default workbook
c.) Gets the list of tables within the database
d.) Gets data
e.) Exports the data to excel worksheets (there are 2 methods defined (1) range method (2) cell by cell
f.) Saves the excel sheet
g.) Clean up
This blog posts shows two ways to extract data from SQL Server and place into an Excel document. The Range method was much quicker than the cell by cell approach. The code below gets a list of all tables within a particular SQL Database and exports the data to Excel.
I didn’t use was Excel 2007 document format at this. If you have a good post or web link using this technique I would happily reference. Let me know if you would like the Visual Studio project.
a.) Create Excel document
c.) The following method GetTableList() returns a List<ExportInfo> where ExportInfo is a structure that has the Name and SQL to be used for each table to be exported.
d.) Gets Data by interating over collection of exportInfo’s to retrieve sql statements
e.) Export using range (1)
Export by iterating of Cells (2)
f.) g.) Save and clean up