.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
// create a new COM object for excel
_excel = new Excel.ApplicationClass();
// add a new workbook
Excel.Workbook workbook = _excel.Workbooks.Add(Type.Missing);
// make the excel application visible
_excel.Visible = true;
// activate the active worksheet in the workbook
Worksheet ws = (Worksheet)_excel.ActiveSheet;
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());
}
}