Introduction

In the trading world, most traders don't know databases, however they do know spreadsheets, specifically Excel. Once data is inside an Excel spreadsheet, traders can massage the data to suit their needs. The question is, how do you get the data from the database into the spreadsheet? One way is to export the data using provided database tools by the database vendor. One way we can do this is to use .NET's interoperability feature to pull the data directly into the spreadsheet.

Design

The design of the Query2Excel application is fairly straightforward. It consists of a form that allows you to enter your connection information and your query. Inside the form is the ExcelHelper class that allows you to talk directly to excel through the interoperability library. The ExcelHelper class has the minimum number of functions needed to take data from the database and place it into Excel. It has an activation function, Activate, to open and activate excel. It also has several formatting functions (FormatColumn, BoldRow, FormatColumnText, etc.) and it has a method called AddItemToSpreadsheet to allow you to add text to a particular cell in the spreadsheet.

Database connection is done completely through ODBC. ODBC (Open Database Connectivity) will allow you to connect to any database that has an ODBC driver associated with it (e.g. Sql Server, MS Access, Sybase, Oracle, MySQL, and most others.)  The Query2Excel application takes advantage of the OdbcConnection, OdbcCommand, DataSet, and OdbcAdapter classes to connect to the database and perform the query.

Figure 2 - Query2Excel Design Reverse Engineered using the WithClass UML Tool