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.

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:

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:

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:

This technology is now available for our custom development services only. Based on the Add-in Express for Office core, it is designed for building custom-tailored Office add-ins with far less coding than you usually have to do. Plus, it includes all Add-in Express features such as True RAD, visual designers, Outlook view and form regions, etc.

Get the best platform for building version-neutral, fast and easy deployable plug-ins by using Add-in Express projects templates, visual designers, components and wizards in combination with a perfect Delphi compiler.

This is an extension for Visual Studio that allows developers to quickly create WiX-based setup projects in a familiar Visual Studio way.

The Designer for WiX Toolset lets you forget the plain Windows Installer XML and concentrate on your deployment logic. It integrates several editors with the Visual Studio IDE and provides a set of vdproj designers to configure the file system, registry, user interface, custom actions, launch conditions and more for your setup projects.

The innovative technology for customizing Outlook views and forms. It is included in all Add-in Express for Office products and can be used to extend Outlook views, e-mail, task and appointment windows, To-Do bar, Reading and Navigation panes with your own custom sub-panes.

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in
the United States and/or other countries. All other trademarks are property of their respective owners.