Display Your Windows Application Results in Excel Using C#

Author: Lee O. Upton, Innovation & Information Consultants, Inc.

Developers of software applications focus their efforts on how the end-user client will ultimately use the results produced. Most clients have little concern for how the original data are created, loaded (if obtained from exogenous data sources), and manipulated. Rather they are solely interested in the end results, and how they can use them. What is vitally important for clients is how the results are displayed, especially those clients that primarily focus their efforts and resources in using Excel or other spreadsheet applications. We have successfully developed a means to use the functionality of ADO .NET and display the results in Excel workbooks, providing our clients with a powerful application and the familiar use of spreadsheets.

IIC, Inc. recently completed a project which involved an intensive amount of data analysis and manipulation. The goal was to create a forty-year projection of offshore oil and gas exploration, development, reserve additions, and projected production. The projections were based on extensive review of existing data, “equation building” and implementation, and creation of specific algorithms to handle different user inputs and assumptions. Ultimately, the application generated a series of annual outputs, specific to one of twenty-one geographical regions. Although the program used the rich class structure of ADO .NET to access and modify a large portion of the initial data, the client expressed their desire to view the results in either a text or spreadsheet file, as opposed to a database. Given the vast amount of output data, and more importantly the client’s desire to perform subsequent calculations and analysis, we elected to bypass text files, and relied upon COM interoperability to display the output data in Excel. The remainder of this white paper discusses the code I implemented to display the results in Excel workbooks. For simplicity sake, I step into the process at the conclusion of a simulation run, where all of our output arrays are already stored via in-memory arrays.

Step One: Adding the Excel

The first step in introducing the Excel interaction in our program is to build the Excel interoperability wrapper. This will allow compatibility between the .NET and COM components, and allow interaction as if the COM components were .NET components. The Excel interoperability wrapper can be created using the Visual Studio .NET Integrated Development Environment (IDE).

Select the Project tab from the Main menu, and subsequently select Add Reference… from the drop down menu. Select the COM tab of the dialog box and scroll down until you reach the Microsoft Excel 10.0 Object Library line item:

At this juncture, click on the Select button, and the component reference will be added to the Selected Components portion of the dialog. Click OK and the reference will be added to the project. (You can verify this by selecting viewing the References in the solution explorer). Once the reference has been added it is time to get started.

Step Two: Creating the Excel Connection

In the IIC, Inc. project, I elected to create a separate static class that contained only a public static Application object, and a method that creates the Excel connection. The decision to do this was motivated by using the debugger to ensure that the application was launched appropriately.

Here, we start by adding the new class, entitled ExcelClass. It is imperative to include the appropriate “using” statement, to ensure that the class can identify the Excel objects we wish to include:

using System;
using Excel;

We add the “using Excel” statement directly under our “using System” directive. Next, we define our single application object, keep the constructor clear, and add our one static method. The Application object represents the uppermost object in Excel’s object hierarchy.

public static Excel.Application excApp;

public ExcelClass()
{
}
public static void CreateExcelConnection()
{
excApp = new Excel.ApplicationClass();
}

When we call the CreateExcelConnection method, we instantiate the excApp and provide capability to work with this object. Given that the Excel class is a “static” class, we are able to call the method from anywhere in our project. This is exactly what we do when we allow our users the choice to view the outputs from a particular simulation.

Step Three: Launch Excel First Time Around

Once the user has run a simulation, a simple form is displayed that allows them the option of viewing the simulation results in Excel. In the form code, we launch Excel using our previously discussed static class (remember to include the using Excel directive in this class as well!):

public Output()

{
     //
     // Required for Windows Form Designer support
     //
     InitializeComponent();
     Initialize();
}

private void Initialize()
{
     if (Form1.runNumber == 1)
     {
          // create the Excel Connection first time around
          ExcelClass.CreateExcelConnection();
     }
}

In the form’s constructor, we add the Initialize() method to create the Excel application object and launch Excel. In our main form (Form1), we include a static variable that counts the number of simulations (runNumber). Thus, the “if statement” tests to see whether this is our first run. If so, we invoke the static method of ExcelClass and create the excApp object.

Step Four: Testing if the User Closed Excel

In the simplest case, the Output form might contain just two command buttons, Display Data (btnDisplayData) and Exit (btnExit). If the user chooses the Display Data button, we are then required to write out our results, displaying them nicely in Excel. However, the goal of any simulation project is to allow a multitude of simulations to be run and compare the results. Thus, we stress the importance of only launching the Excel application once, and strive to continually add workbooks to the application (as opposed to opening an individual application after each simulation run!). It is feasible that the user may inadvertently close the Excel application, independent of the program. Hence, our first step should be to ensure that the previously created excApp object is open, and if not, create a new instance, as shown in the Display Data event handler:

private void btnDisplayData_Click(object sender, System.EventArgs e)
{
     // Test to see if they accidentally closed the application
     if (ExcelClass.excApp.Visible == false)
     {
          ExcelClass.CreateExcelConnection();
     }

We test whether the application object is open by using the Visible property. If true, our application is open, and we can proceed accordingly. If false, we must create another instance of the excApp via our static ExcelClass method.

Step Five: Create a New Workbook

At this stage, we are ready to write out our results to Excel. However, since each simulation is slightly different (depending on the user inputs and assumptions), we will create a new workbook each time through, in which we will write the results. We begin the process by switching the Cursor to the standard hourglass notation, to let the user know we are performing some behind the scenes manipulations. Next, we create a new workbook (wrapped up in a try box, again, a good error-handling idea):

// Begin by declaring the newWorkBook variable and
// adding it to the Excel Application object: excApp
// If problems, display the erorr message in a dialog and
// close out of the event click
Excel._Workbook newWorkBook;
try
{
     newWorkBook = ExcelClass.excApp.Workbooks.Add(Type.Missing);
}
catch (System.Exception ex)
{
     MessageBox.Show(ex.Message.ToString(), “Excel Error!”, MessageBoxButtons.OK,
MessageBoxIcon.Error);
     this.Cursor = Cursors.Default;
     return;
}

Once we declare our workbook, we instantiate it using the Workbooks.Add method of our application object. It is vital to note the inclusion of the Type.Missing parameter in the Add method. Unlike Visual Basic (both VBA and VB .NET), Visual C# does not support optional parameters, thus forcing the developer to enter values for each optional parameter. The C# developer must enter in values for each parameter, or the Type.Missing placeholder to avoid code errors. This is by no means the only difference between VBA, VBA .NET and C#, but represents the one most germane to the code discussed in this paper.

Step Six: Create the Sheets Object and Begin Data Display

After we create the new workbook, we create a Sheets object to allow us to use an indexer to refer to the different sheets in the workbook. We then select the first sheet in the workbook, using the get_Item accessor method of our newly created sheets object. We then change the name of this sheet from “Sheet1” to “Summary Data,” which has more significant meaning to the user:

Excel.Sheets excSheets = newWorkBook.Worksheets;
Excel.Worksheet ws = (Excel.Worksheet) excSheets.get_Item(“Sheet1”);
// Sheet1 is our summary data so let the user know…
ws.Name = “Summary Data”;

Next, we begin to populate the cells of the workbook with some basic information. Given that each new workbook represents the results of a simulation run, we allow the user to “stamp” each workbook with a name and a date. At this stage, we rely upon Range objects, perhaps the most fundamental Excel object, which allows the user to select and manipulate or change an individual cell or groups of cells. We begin by declaring two ranges, each composed of a single cell. Next, we set the cell value to either a title or the current date, using the Value2 property:

Excel.Range titleRange = (Excel.Range) ws.get_Range(“A1”, Type.Missing);
titleRange.Value2 = Form1.title;
titleRange.Font.Bold = true;

Excel.Range dateRange = (Excel.Range) ws.get_Range(“B1”, Type.Missing);
dateRange.Value2 = System.DateTime.Now.ToString();
dateRange.Font.Bold = true;
dateRange.Columns.AutoFit();

In this case, we declare the titleRange as cell “A1,” and set the value to a user-specified title (Form1.Title). Next, we set the Font.Bold property to true, which bolds the text in the cell. We declare the dateRange as cell “B1,” and set the value to the current date and time using System.DateTime.Now.ToString(). Like the titleRange, we bold the text, but also call the AutoFit method on the Columns property to ensure the user can view the complete cell contents.

Step Seven: Use the Cells Property to Display Data

In the previous step, we demonstrated how to declare a single cell Range object, and display data using the Value2 property. However, there are times when you may want to take advantage of the Cells property of the worksheet to display data. In our program, all of our output arrays consisted of data for 21 areas over 40 years. In fact, each output data array is defined as a 2-D array, 21 rows by 40 columns (or alternatively as a [21,40] matrix). For display purposes, we may only want to write out the projection year labels once, along the top of the spreadsheet. We accomplish this with the Cells property of the worksheet (in this case ‘ws’):

for (int my = 1; my < 41; my++)
{
     ws.Cells[2, my+1] = my;
}

As the code shows, we create a “for loop,” where the ‘my’ variable will increase by one until it hits 40. Each time through, we use the ws.Cells property (defined as ws.Cells[row, column]) to write out the projection year across the top of the worksheet, beginning in cell B2 (corresponding to Cells[2,2]), and proceeding across the sheet. Alternatively, we could have defined a cell range or ranges of single cells, and used the Value2 property.

Step Eight: Writing Out the Data Arrays Using a Helper Function

At this point, we have (?) populated the title, date and projection year cells. We turn our attention to the 21x40 data output arrays. One could simply cycle through the data arrays one by one, using the Cells property to display the data. However, writing out a significant amount of data with the Cells property is not necessarily performance-friendly. Alternatively, we could write out each array by declaring Range objects and setting the Value2 property each time around. We opt for the second, with some modification.

Our program contains 14 data output arrays, each of which has the same bounds and corresponds to the same 21 areas. Rather than write out each name and set each range, then move down the sheet, we create a little helper method to reduce the amount of code we need to write. This enables us to call it each time we want, allowing us to add (or subtract) as many arrays as possible, without hindering our output structure. The key is to define the first row we wish to begin on, and then pass this row, and the pertinent array and associated parameters to our helper method. Not only will our helper method write out the data to Excel, but it will return the next viable row for data to be written:

int initialRow = 3;
int returnRow = 0;

returnRow = DisplayHelp(ws, “Discovered Fields”, initialRow, fieldsFound, 21);
returnRow = DisplayHelp (ws, “Exploratory Wells Drilled”, returnRow, eWells, 21);
returnRow = DisplayHelp (ws, “Reserves Discovered”, returnRow, resDisc, 21);

We define a variable called initialRow, which indicates the Excel spreadsheet row where the uppermost left data cell of the first array will be located. We also declare a variable called return row, which will be the row where the uppermost left data cell of the next data array will be located. We then call our helper method, DisplayHelp, passing in five parameters: our current worksheet ‘ws,’ a title for the current data array, the worksheet row in which the uppermost left data cell will be displayed (initially initialRow, subsequently returnRow), the array itself (e.g., fieldsFound), and finally the number of areas (alternatively the number of rows in each data array).
With the first DisplayHelp method call, we pass in row 3. As you will see from the helper method, DisplayHelp will return the appropriate starting row for the next data array, which is passed in during subsequent DisplayHelp calls. We turn our attention to the creation of the helper method:

Step Nine: Creating the Helper Function

The nuts and bolts of displaying the output arrays in Excel occur in the DisplayHelp method. We begin by accepting the parameters and then manipulating the rows and columns to suit our needs:

private int ExcelDumpSummary(Excel.Worksheet ws, string name, int
startRowNumber, double[,] array, int rows, int displayCheck)
{
     // this moves the start of next “passed in” data to be pushed down so we do
     // not overwrite
     int endRowNumber = startRowNumber + rows;
     int rowNumber = startRowNumber;
     int startColumnNumber = 1;

     // we have 40 years of projections, so we need 41 columns (one for labels)
     int endColumnNumber = startColumnNumber + 40;
     int columnNumber = startColumnNumber;

We begin by defining the bounds of our arrays, while keeping an eye on subsequent data arrays passed during method calls. We accept our startRowNumber (passed in during the method call), define a rowNumber to count row increments, and define startColumnNumber (which will always be one since we write our data left to right). Next, set the end limit of the data array with the endColumnNumber, and again define an incremental variable columnNumber. At this point, we populate our label cells to correspond to each area number, beginning with our user passed array name:

// place the label in the first row number and the first column
ws.Cells[startRowNumber,startColumnNumber] = name;

// We are doing the areas...
// label the rows in column one according to area, incrementing the row number
// before writing
for (int area = 1; area <= 21; area++)
{
     ws.Cells[++rowNumber,startColumnNumber] = area;
}

Here, we use the Cells property to place only the name label, and all of our area labels in the first column. Note we increment the rowNumber variable as we head through our area loop. Before we start writing our data array, we need to move over one column (so as not to overwrite the labels) and return to the initial row (as passed in by the user and set to startRowNumber):

// when we want to write data, we start at the second column, second row (down
// one from the label)
rowNumber = startRowNumber + 1;
columnNumber = startColumnNumber + 1;

Next, we are finally prepared to write out the 21x40 data array that has been passed in (and defined as array), by declaring a range, and the setting the Value2 property:

// we now declare a range, which is a matrix of our values defined by the corners,
// upper left and lower right
Excel.Range range1 = (Excel.Range) ws.get_Range(ws.Cells[rowNumber,columnNumber],
ws.Cells[endRowNumber,endColumnNumber]);

// we fill the range by writing out our array, observing that when the data is written,
// it goes left to right and top to bottom, i.e. row 1, columns 1-40, row 2, columns 1-
// 40, etc., similar to the structure of the two dimensional [area, year] arrays we
// created to store the data!
range1.Value2 = array;
range1.NumberFormat = “#,##0.#0”;

After all the manipulation with the Cells property, you can see how much easier it is to simply define a range equivalent to the size of the array, and simply set the Value2 property! For one final touch, we set the cell format to include two decimal places, and the comma as the thousands separator. Lastly, we must remember that the DisplayHelp method must return an integer corresponding to the start row of the next data array to be passed in. We simply look at the last row, and increment it by two (to allow some space between each data set).

return endRowNumber + 2;

That concludes our helper method!

Step Ten: Some Fluff

At this point, we have created the application, added a new workbook, defined the sheets object, referenced a particular worksheet, used the Cells property to display data, and most importantly, created the helper method and Range objects to easily display our output arrays. Now we move to the important stuff! In our project, we had multiple data arrays written out to different spreadsheets. Personally, I like the user to view the same initial summary sheet. Additionally, I am a big proponent of the freeze panes functionality, especially when we have only one set of labels, but the data spans more than one window length! So, in finishing up this project, we can always start the user off right, by showing them the same worksheet every time, with the panes frozen to preserve our labels:

Excel.Worksheet primary = (Excel.Worksheet) excSheets[1];
primary.Activate();

Excel.Window excWin = (Excel.Window) ExcelClass.excApp.ActiveWindow;

excWin.SplitColumn = 1;
excWin.SplitRow = 2;
excWin.FreezePanes = true;

ExcelClass.excApp.Visible = true;

We first use the sheets object to define our primary sheet and activate it, thus always displaying this sheet. Next, we declare an Excel.Window object (excWin), and set the SplitColumn and SplitRow properties to the appropriate “freeze” point. Finally, we set the excWin.FreezePanes property to true and make sure our application object is visible to the user!

Conclusion

This white paper only scratches the surface of Excel and .NET interoperability. During our project, I found that the amount of literature on Excel and .NET interaction was limited, at a high overview level or specifically geared to completing one task. The most important part of my research was not looking at literature, but simply playing around with the Excel Object Browser and trying different things out in my code. As any developer or programmer will tell you, a lot more is learned from the program not working than when it seems to work flawlessly.