Pieter van der Westhuizen

Importing data from SQL databases and other sources to Microsoft Excel

A number of people I’ve met had no idea about one of Microsoft Excel’s most powerful features. I’m talking about the ability to add data from external sources to your Excel sheet and manipulate it from there. Of course as a developer this can come in very handy when you want to provide your users with an intuitive and flexible way to access your data and empower them to create their own reports.

In this article I’ll walk you through importing SQL server and text file data into Excel using the Excel Object model and Add-in Express for Office and .net.

Creating the Excel COM add-in

Start by creating a new ADX COM Add-in in Visual Studio (2013, 2012, 2010, 2008 or 2005). The project template can be found under Other Project Types > Extensibility.

Creating a new COM Add-in project in Visual Studio

In the next steps of the “New Microsoft Office COM Add-in” wizard, select your programming language (C#, VB.NET and C++.net are supported) and the minimum version of Office (2000 – 2013) that your add-in will support.

Selecting the programming language and the minimum version of Office that your add-in will support

Finally, select Microsoft Excel from the list of supported applications.

Selecting Microsoft Excel from the list of supported applications

Creating the Excel add-in UI

Next, we need to add functionality for the user to select the database and table with external data they would like to import into Excel. Add a new Advanced Excel Task Pane to your project. The item template can be found under Add-in Express Items > Excel.

Adding an advanced task pane to the Excel add-in project

Add a toolbar with two buttons to the form as well as a list view control which we’ll use to list all the tables in the selected database.

Adding a toolbar with buttons and a list view control which will display the tables in the selected database

Double-click on the Connect to server button inside the Visual Studio designer and add the following to the Click event. The code will display the standard Windows data connection dialog with which the user can select the SQL Server name and database to connect to and list its tables:

private void btnConnect_Click(object sender, EventArgs e)
{
    DataConnectionConfiguration dcs = new DataConnectionConfiguration(null);
    dcs.LoadConfiguration(connectionDialog);
 
    if (DataConnectionDialog.Show(connectionDialog) == DialogResult.OK)
    {
        var tables = SqlUtils.GetAllTables(connectionDialog.ConnectionString);
        OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder()
            { ConnectionString = connectionDialog.ConnectionString };
 
        TreeNode rootNode = new TreeNode(builder["initial catalog"].ToString(), 1, 1);
        TreeNode tablesNode = rootNode.Nodes.Add("Tables", "Tables", 2, 2);
 
        foreach (string table in tables)
        {
            TreeNode tableNode = tablesNode.Nodes.Add(table, table, 3, 3);
            tableNode.Tag = "table";
        }
 
        tvDatabase.Nodes.Add(rootNode);
        tvDatabase.ExpandAll();
    }
}

The SqlUtils class contains a few methods to make it easier to retrieve tables and columns from SQL server. The code for the GetAllTables methods is as follows:

public static List GetAllDatabases(string connectionString)
{
    List returnValue = new List();
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = new SqlCommand("EXEC sp_databases;", conn))
        {
            SqlDataReader dbReader;
            conn.Open();
            dbReader = cmd.ExecuteReader();
            while (dbReader.Read())
            {
                returnValue.Add(dbReader["DATABASE_NAME"].ToString());
            }
        }
        conn.Close();
    }
    return returnValue;
}

Connection properties

Notice that the Data source is set to Microsoft SQL Server (OLE DB), it is very important that we set the connection dialog’s data provider to OLEDB, as this will make it easier when we import data into Excel.

Next, add a new ContextMenuStrip component to the task pane and add two menu items to it:

Adding a new ContextMenuStrip component with two menu items to the task pane

Double-click each of the newly added menu items to generate Click event handlers for each and add the following code to each menu item, respectively:

private void selectTop10ToolStripMenuItem_Click(object sender, EventArgs e)
{
    TreeNode selectedNode = tvDatabase.SelectedNode;
    InsertData(String.Format("Select Top 10 * From {0}", selectedNode.Text));
}
 
private void selectAllRowsToolStripMenuItem_Click(object sender, EventArgs e)
{
    TreeNode selectedNode = tvDatabase.SelectedNode;
    InsertData(String.Format("Select * From {0}", selectedNode.Text));
}

Import SQL Server data to an Excel worksheet

The InsertData method will add the data to the active Excel sheet based on the SQL Select query we’ve passed in as a parameter.

private void InsertData(string sql)
{
    Excel.Application excelApp = null;
    Excel.Worksheet activeSheet = null;
    Excel.Range insertRange = null;
    Excel.ListObjects listObjects = null;
    Excel.ListObject listObject = null;
    Excel.QueryTable queryTable = null;
 
    try
    {
        excelApp = (Excel.Application)this.ExcelAppObj;
        activeSheet = (Excel.Worksheet)excelApp.ActiveSheet;
        insertRange = activeSheet.Range["$A$1"];
        listObjects = activeSheet.ListObjects;
        listObject = listObjects.AddEx(Excel.XlListObjectSourceType.xlSrcExternal, 
            "OLEDB;" + connectionDialog.ConnectionString, Type.Missing,
            Excel.XlYesNoGuess.xlYes, insertRange, Type.Missing);
        queryTable = listObject.QueryTable;
        queryTable.CommandType = Excel.XlCmdType.xlCmdSql;
        queryTable.CommandText = sql;
        queryTable.AdjustColumnWidth = true;
        queryTable.Refresh();
    }
    finally
    {
        if (queryTable != null) Marshal.ReleaseComObject(queryTable);
        if (listObject != null) Marshal.ReleaseComObject(listObject);
        if (listObjects != null) Marshal.ReleaseComObject(listObjects);
        if (activeSheet != null) Marshal.ReleaseComObject(activeSheet);
    }
}

Note, that we prepend “OLEDB;” to the connection string received from the connection dialog. This is required in order for Excel to import the data to the sheet. The final result, should look similar to the following image:

The data imported from the SQL database to Excel 2013

Import delimited text file data to an Excel sheet

Of course, you are able to add different sources of data to Excel other than SQL server. For example, let’s import data from a comma-separated (.csv) file into the active sheet in Excel.
Start by adding a new ribbon tab control to the AddinModule designer surface. We’ll only add one ribbon group and button to it.

Adding a new ribbon tab with a button to the Addin Module designer surface

We’ll prompt the user to select a CSV file to import when they click on the button, to do this we first need to add an OpenFileDialog component to the AddinModule designer surface.
In the “From CSV File” button’s OnClick event handler add the following code:

private void dataFromCSVRibbonButton_OnClick(object sender,
    IRibbonControl control, bool pressed)
{
    Excel.Worksheet activeSheet = null;
    Excel.QueryTables queryTables = null;
    Excel.QueryTable queryTable = null;
    Excel.Range insertRange = null;
 
    try
    {
        openFileDialog.ShowDialog();
        if (!String.IsNullOrEmpty(openFileDialog.FileName))
        {
            activeSheet = (Excel.Worksheet)ExcelApp.ActiveSheet;
            insertRange = activeSheet.Range["$A$1"];
            queryTables = activeSheet.QueryTables;
            queryTable = queryTables.Add(
                "TEXT;" + openFileDialog.FileName, insertRange);
            queryTable.AdjustColumnWidth = true;
            queryTable.TextFileParseType =
                Excel.XlTextParsingType.xlDelimited;
            queryTable.TextFileTextQualifier =
                Excel.XlTextQualifier.xlTextQualifierDoubleQuote;
            queryTable.TextFileCommaDelimiter = true;
            queryTable.Refresh();
        }
    }
    finally
    {
        if (activeSheet != null) Marshal.ReleaseComObject(activeSheet);
        if (insertRange != null) Marshal.ReleaseComObject(insertRange);
        if (queryTable != null) Marshal.ReleaseComObject(queryTable);
        if (queryTables != null) Marshal.ReleaseComObject(queryTables);
    }
}

The result should look like the following image when the user clicks on the “From CSV File” button and selects a comma-separated values file:

The data from a .csv file imported in Excel 2013 on a button click

Thank you for reading. Until next time, keep coding!

Available downloads:

This sample Excel add-in was developed using Add-in Express for Office and .net:
Importing Excel Data add-in (C#)

Excel add-in development in Visual Studio for beginners:

Post a comment

Have any questions? Ask us right now!