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 a COM add-in for Microsoft Excel
• Creating the Excel add-in UI
• Importing SQL Server data to an Excel worksheet
• Importing delimited text file data to an Excel sheet

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.

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.

Finally, select 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.

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.

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<string> GetAllTables(string connectionString)
{
    List<string> returnValue = new List<string>();
    OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder() { ConnectionString = connectionString };
    string _dbName = builder["initial catalog"].ToString();
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        using (OleDbCommand cmd = new OleDbCommand(String.Format("EXEC sp_tables @table_name = '%',@table_qualifier = '{0}',@table_type = \"'Table'\";", _dbName), conn))
        {
            OleDbDataReader dbReader;
            conn.Open();
            dbReader = cmd.ExecuteReader();
            while (dbReader.Read())
            {
                returnValue.Add(String.Format("{0}.{1}", dbReader["TABLE_OWNER"], dbReader["TABLE_NAME"]));
            }
        }
        conn.Close();
    }
    return returnValue;
}

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:

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:

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.

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:

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:

• Part 1: Application and base objects
• Part 2: Customizing the Excel User Interface: What is and isn't customizable
• Part 3: Customizing Excel ribbons and toolbars
• Part 4: Customizing Excel main menu, context menus, and Backstage view
• Part 5: Creating custom task panes for Excel 2013 – 2003
• Part 6: Working with Excel workbooks and worksheets: VB.NET examples
• Part 7: Working with Excel cell values, formulas and formatting: C# samples
• Part 8: Working with Excel charts
• Part 9: Working with Excel tables and ranges
• Part 11: Working with Excel pivot tables: VB.NET code examples