Query Tool to Excel using C# and .NET

Listing 4 - Setting a Column Format in Excel

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

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.