Query Tool to Excel using C# and .NET

Introduction

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

You might also like...

Comments

About the author

Mike Gold United States

Mike Gold is President of Microgold Software Inc. and Creator of WithClass 2000 a UML Design Tool for C#. In the last few years Mike has consulted for companies such as Merrill Lynch and Chase M...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“You can stand on the shoulders of giants OR a big enough pile of dwarfs, works either way.”