How to migrate data to Excel.
- April 20, 2015
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.
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.