Tech Blog
Since 2004
Export SQL Data to Microsoft Excel (using Visual Studio 2008, c#)
How to migrate data to Excel.
- April 20, 2015
Rest of the Story:
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.
private void btnExport_Click(object sender, RoutedEventArgs e) {
lblMessage.Content="Export Started..";
WpfApplication.DoEvents();
ApplicationClass app = CreateExcelDocument(false);
//add workbook to excel document
Workbook workbook = app.Workbooks.Add(Type.Missing);
DataSet ds = GetData(GetTableList());
//shown below are 2 method of moving data from sql to excel, only use one of them
//(1)call method to export to worksheet using range functionality (much faster than cell by cell method
Export(ds, ref workbook);
//(2)cell by cell method
//InsertIntoExcel(app, ds);
string fileName = @"C:\Safety3.xlsx";
SaveDoc(workbook, fileName);
CloseExcelDocument(app);
lblMessage.Content = "Finished..";
}
/// <summary>
/// creates excel document
/// </summary>
private ApplicationClass CreateExcelDocument(bool visible) {
ApplicationClass app = null;
try {
app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = visible;
} catch (Exception ex) {
MessageBox.Show(ex.ToString());
}
return app;
}
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.
public class ExportInfo {
public string Name { get; set; }
public string Sql { get; set; }
}
/// <summary>
/// get list of sql tables in database
/// </summary>
/// <returns>list of ExportInfo objects</returns>
private List<ExportInfo> GetTableList() {
System.Data.DataTable tables = new System.Data.DataTable("Tables");
using (SqlConnection connection = new SqlConnection(GetConnectionString())) {
SqlCommand command = connection.CreateCommand();
command.CommandText = "select table_name as Name from INFORMATION_SCHEMA.tables where TABLE_TYPE = 'BASE TABLE'";
connection.Open();
tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
connection.Close();
}
List<ExportInfo> exps = new List<ExportInfo>();
foreach (DataRow row in tables.Rows) {
exps.Add(new ExportInfo(){Name=row[0].ToString(), Sql = String.Format("select top 1 * from {0}",row[0].ToString())});
}
return exps;
}
/// <summary>
/// get data from sql server
/// </summary>
/// <param name="exps"></param>
/// <returns>dataset with return results</returns>
private System.Data.DataSet GetData(List<ExportInfo> exps) {
DataSet ds = new DataSet();
string executeSql = string.Empty;
foreach (ExportInfo exp in exps) {
executeSql += exp.Sql + ";";
}
string connectionString = GetConnectionString();
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter adapter = new SqlDataAdapter(executeSql, conn);
try {
adapter.Fill(ds);
} catch (Exception ex) {
}
int index = 0;
foreach (System.Data.DataTable dt in ds.Tables) {
dt.TableName = exps[index].Name;
index += 1;
}
return ds;
}
/// <summary>
/// Copy data from dataset into workbook
/// </summary>
/// <param name="dataSet"></param>
/// <param name="excelWorkbook"></param>
private void Export(DataSet dataSet, ref Workbook excelWorkbook) {
int sheetIndex = 0;
// build rawData 2 dimensional array with data for each datatable
foreach (System.Data.DataTable dt in dataSet.Tables) {
// Copy the DataTable to an object array
object[,] data = new object[dt.Rows.Count + 1, dt.Columns.Count];
// Copy the column names to the first row of the object array
for (int col = 0; col < dt.Columns.Count; col++) {
data[0, col] = dt.Columns[col].ColumnName;
}
// Copy the values to the object array
for (int col = 0; col < dt.Columns.Count; col++) {
for (int row = 0; row < dt.Rows.Count; row++) {
data[row + 1, col] = dt.Rows[row].ItemArray[col];
}
}
// Calculate the final column letter
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen) {
finalColLetter = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring(
(dt.Columns.Count - 1) % colCharsetLen, 1);
// Create a new Sheet
Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, XlSheetType.xlWorksheet);
excelSheet.Name = dt.TableName; // name new sheet name of table
// Fast data export to Excel
string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);
excelSheet.get_Range(excelRange, Type.Missing).Value2 = data;
// Mark the first row as BOLD
((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
}
}
/// <summary>
/// inserts data into excel row by row
/// </summary>
/// <param name="excel"></param>
/// <param name="ds"></param>
private void InsertIntoExcel(Microsoft.Office.Interop.Excel.Application excel, System.Data.DataSet ds) {
foreach (System.Data.DataTable dt in ds.Tables) {
Microsoft.Office.Interop.Excel.Worksheet theSheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Workbooks[1].Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
theSheet.Name = dt.TableName;
int colIndex = 0;
int rowIndex = 1;
string err = string.Empty;
try {
colIndex = 0;
foreach (DataColumn col in dt.Columns) {
colIndex += 1;
excel.Cells[1, colIndex] = col.ColumnName;
}
rowIndex = 1;
foreach (DataRow row in dt.Rows) {
rowIndex += 1;
colIndex = 0;
foreach (DataColumn col in dt.Columns) {
colIndex += 1;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
} catch (Exception ex) {
err += "RowIndex=" + rowIndex.ToString() + " ColIndex=" + colIndex.ToString();
}
} // next
}
/// <summary>
/// Save workbook to file system
/// </summary>
/// <param name="excelWorkbook"></param>
/// <param name="fileName"></param>
private void SaveDoc(Workbook excelWorkbook, string fileName) {
// Save and Close the Workbook
excelWorkbook.SaveAs(fileName, XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkbook.Close(true, Type.Missing, Type.Missing);
excelWorkbook = null;
}
/// <summary>
/// Clean up and close document
/// </summary>
/// <param name="excelApp"></param>
private void CloseExcelDocument(ApplicationClass excelApp) {
// Release the Application object
excelApp.Quit();
excelApp = null;
// Collect the unreferenced objects
GC.Collect();
GC.WaitForPendingFinalizers();
}