Introduction
In the trading world, most traders don't know from databases, however, they do know from spreadsheets. Specifically they like Excel spreadsheets. 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. These tools usually allow you to export the data in the form of xml, sql statements, or csv files (comma delimited rows). csv files can be imported into excel, but it's not the ideal solution. Another solution is to use .NET's interoperability feature to pull the data directly into the spreadsheet. This is the tact we took in this article.
Figure 1 - SQL Query directed into Excel
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
.NET and Excel
There are many aspects of this application we can talk about in this article. We will focus specifically on how to talk to Excel from a .NET application. Excel has a rich library of objects that lets you take control of every aspect of Excel. Visual Studio .NET allows us to attach to Excel by building a COM callable wrapper around the Excel COM Object library. The COM Callable Wrapper is automatically generated when you add it as a reference to your Visual Studio Project. For further information on adding Excel as a reference check out my article on how to read an excel spreadsheet.
Once you've added the excel reference, you can treat excel as if it were a set of objects in C#. Below is the code to "construct" and activate an excel spreadsheet.
Listing 1 - Opening a New Excel Spreadsheet from C#
// open a new excel spreadsheet
_excel = new Excel.ApplicationClass(); // create a new COM object for excel
Excel.Workbook workbook = _excel.Workbooks.Add(Type.Missing); // add a new workbook
_excel.Visible = true; // make the excel application visible
Worksheet ws = (Worksheet)_excel.ActiveSheet; // activate the active worksheet in the workbook
ws.Activate();
Now that we opened our spreadsheet, we want to be able to populate the cells at specific locations in the spreadsheet.The ExcelHelper method, AddItemToSpreadsheet, allows us to do that. This method uses the Cells property of the worksheet to set a value at a particular row and column index in the spreadsheet.Note that excel has letters for columns and numbers for rows. The Cells property lets you treat both rows and columns as numbers starting at index = 1.
Listing 2 - Setting a value inside a cell in Excel
public
void AddItemToSpreadsheet(int row, int column, Worksheet ws, string item)
{
((Range)ws.Cells[row, column]).Value2 = item; // set the cell value at a row and column
}
The last thing we need to be able to automate is the formatting of the spreadsheet. Formatting in Excel is a little less obvious as to how it is performed. We'll start with a simple formatting method in ExcelHelper called BoldRow which allows us to bold an entire row in the spreadsheet. Bolding a row, as with most applications, is done through the Font property. We can access the entire row from a single cell through the EntireRow property of the cell. The EntireRow property has a Font property in which we can manipulate the style of the row.
Listing 3 - Setting a Row to Bold in Excel
public
void BoldRow(int row, Worksheet ws)
{
((Range)ws.Cells[row, 1]).EntireRow.Font.Bold = true;
}
As a cell has an EntireRow property to manipulate the entire row the cell is contained, a cell also has an EntireColumn property to manipulate the format of the column.We can change the format of a column using the NumberFormat property of the column shown in Listing 4.
Listing 4 - Setting a Column Format in Excel
public
void FormatColumn(Worksheet ws, int col, string format)
{
((Range)ws.Cells[1, col]).EntireColumn.NumberFormat = format;
}
The EntireColumn property can also be used to set the width of the column or to force the column to Autofit the data as shown in Listing 5.
Listing 5 - Setting a Column Width in Excel
public
void SetColumnWidth(Worksheet ws, int col, int width)
{
((Range)ws.Cells[1, col]).EntireColumn.ColumnWidth = width;
}
// autofit to contents
public void AutoFitColumn(Worksheet ws, int col)
{
((Range)ws.Cells[1, col]).EntireColumn.AutoFit();
}
This program uses an OdbcDataAdapter to extract the data from a database (such as Access) through Odbc into a DataSet. Listing 6 demonstrates how we set the query in the SelectCommand of the Adapter and then fill the DataSet from the Query using the Fill command.
Listing 6 - Querying an ODBC Database and filling the DataSet with the Query Results
private
void PerformQueryIntoDataSet()
{
// set the odbc select command to a query
// contained inside the users query text box
odbcSelectCommand1.CommandText = txtQuery.Text;
_ds = new DataSet();
try
{
// fill the dataset from the query
odbcDataAdapter1.Fill(_ds);
// set the maximum for the progress bar
progressBar1.Maximum = _ds.Tables[0].Rows.Count;
// send the dataset to excel
FillExcelSpreadsheet();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
Once we've filled the DataSet, we are ready to populate the data from the DataSet into Excel.First we cycle through each DataColumn in the DataSet to get the column names for the top row of the spreadsheet. Upon getting the column name, we then bold the text to distinguish it from the data in the column. We can then use the other format methods in ExcelHelper to fit the column header to the column and set the date columns to the appropriate format.
Listing 7 - Reading Columns from Excel into the DataSet
///
<summary>
/// Fills the top row of the excel spreadsheet
/// </summary>
void FillColumnHeaders(Worksheet ws)
{
int colcount = 1;
// go through each column and stick the column name
// in excel
foreach (DataColumn dc in _ds.Tables[0].Columns)
{
string nextItem = dc.ColumnName;
_excel.AddItemToSpreadsheet(1, colcount, ws, nextItem);
// fit the column in excel to the header name
_excel.AutoFitColumn(ws, colcount);
// check for date time data and format
if (dc.DataType == System.Type.GetType("System.DateTime"))
{
// format for date time in excel
_excel.FormatColumn(ws, colcount, "mmm-d-yyyy hh:mm:ss.000");
// set the column width in excel
_excel.SetColumnWidth(ws, colcount, 25);
}
colcount++;
}
// bold the header row
_excel.BoldRow(1, ws);
}
Once we've populated the columns, we are now ready to populate Excel with the data from the DataSet. Listing 8 takes data from each DataRow in the DataSet and places it the excel spreadsheet again using the ExcelHelper class.
Listing 8 - Reading the Data into Excel
///
<summary>
/// Fill the Data from the dataset
/// </summary>
/// <param name="ws"></param>
void FillDataRows(Worksheet ws)
{
int rowcount = 2;
int colcount = 1;
// go through each row of the data set and read the data
foreach (DataRow dr in _ds.Tables[0].Rows)
{
colcount = 1;
object[] items = dr.ItemArray;
// go through each column in the row and read the data
// inside the row
foreach (object o in items)
{
string nextItem = "";
// if the data is date time, format the data
if (o is DateTime)
{
nextItem = ((DateTime)o).ToString("MMM-d-yyyy hh:mm:ss.fff");
}
else
{
nextItem = o.ToString();
}
// add the next data item to the spreadsheet
_excel.AddItemToSpreadsheet(rowcount, colcount, ws, nextItem);
colcount++;
}
// populate the rows filled array, which
// are the parameters for the invoke call
// to the GUI (progress bar and count label)
_rowsFilledArray[0] = rowcount;
// set the progress bar and row count label every 10 rows
if (rowcount % 10 == 0)
{
this.BeginInvoke(SetRowsFilled, _rowsFilledArray);
}
// increment the row count
rowcount++;
}
}
Conclusion
The interoperability feature in .NET gives you nice control into your Office applications. My only complaints are that sometimes its a little complicated meandering your way through the complex COM object hierarchies in the Office applications. Also, the population seems a little bit slow going through the COM automation interface. It might be faster to populate a ListView or DataGrid in .NET for example. The advantage of populating an excel spreadsheet, however, is you automatically get all of the extremely powerful features contained inside the Excel application once the data is populated (graphing, assigning formulas, presentation, etc.). Anyway, if you are going to excel in your programming, you might want to extract your data using C# and .NET.