Export SQL Data to Microsoft Excel (using Visual Studio 2008, c#)

posted on Monday, August 24, 2009 5:09 PM

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..";

    }

a.) Create Excel document

    /// <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;            

    }

d.) Gets Data by interating over collection of exportInfo’s to retrieve sql statements

    /// <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;

    }

e.) Export using range (1)

    /// <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;

        }

    }

Export by iterating of Cells (2)

    /// <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 

    }

f.) g.) Save and clean up

/// <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();

Title *
Name *	Remember Me?
Email
Url
Comment *