Reading & Writing Data into Excel
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.