Prerequisites - you will need to have Excel installed and you will need the OLEDB driver provided in the Microsoft Access Database Engine (which can be installed here: https://www.microsoft.com/en-gb/download/details.aspx?id=13255).

Last time we looked at creating a simple scorecard in Excel to visualize info, data, & metrics for some entity of interest, such as a customer, potential customer, employee, etc. In that article, the underlying data lived in another Excel workbook (customers.xlsx) and we used a direct data connection in the scorecard template (scorecard_template.xlsx) to easily pull the data.

This time we're going about the same goal through different means. The data will be in the same place, but we're going to handle getting the data and populating it in a copy of the scorecard template via a Windows Forms application in C# using OleDbConnection and ExcelInterop. While contrived & unnecessarily complicated for this use case, the goal of doing this will be to explore building a simple Windows Forms app to handle reading from & writing to Excel, using a BackgroundWorker to perform collection tasks while reporting progress to the user, and keeping our code in meaningful and organized units.

Grab the project from GitHub here to follow along.

The app expects customers.xlsx, scorecard_template.xlsx, and a third workbook - customer_list.xlsx (which will contain the customers to select from our drop-down), all to be located in the C:\Users\Public folder.

The app will work by providing a drop-down menu of customer records to select from. With one selected, a button will be pressed to take the selected customer and generate a scorecard for them. It does this by using a controller (ExcelDAL) with OleDbConnection instances to query data along with the ExcelInterop Application and Workbook classes to write the output to a new file.

We'll have the following classes:

• scorecard_helper_form - this will contain the code behind our form which presents the drop-down and reacts to the button press
• ExcelDAL - this will handle opening and closing connections and retrieving data from our data sources
• MainController - this is one step removed from ExcelDAL - it handles creating and disposing instances of ExcelDAL and calling its methods to open & close connections and return data
• ExcelInterop - this takes care of creating and populating the new scorecard workbook by copying the template and saving it to our output file
• Models - we have a couple of these (both representing a customer):
• Customer - this will merely hold the ID and name of each customer so that they can be selected from our drop-down
• CustomerData - this will have a field for each data point / column in our data sheet (there are 46 of these - this is a big one)

The model classes are very self-explanatory, so we won't discuss them further.

Since we're querying from a workbook, everything can be treated as a string, but beware of cases where you're querying from SQL data types and would need to convert them to C# equivalents in ExcelDAL. I've left one field as a short - Customer.ID - and left a Conversions object conv in ExcelDAL to show how and where this can be done.

Next let's take a look at our data access layer - ExcelDAL. Let's take a look at its members, starting with the instance variables:

• System.IO.StreamWriter errorFile;
• String errorFileLabel;
• String errorFileName;
• DateTime date;
• Conversions conv;
• OleDbConnection conn;

The first 4 fields exist to facilitate writing errors to files in the C:\Users\Public folder (if these are empty by the time we close our connection, the file is deleted). OleDbConnection is our connection to the data source - this is used by the query methods.

Next we'll look at the method signatures:

• public ExcelDAL(string fileLabel)
• This is our constructor; it handles opening our error file for output with a label that's passed in
• public bool isConnectionOpen()
• Checks whether ExcelDAL's OleDbConnection currently exists and is open
• public bool openCustomerListConn()
• This is used if the ExcelDAL's current use is to query the list of customers for selection in the drop-down; it initializes conn to an OleDbConnection to customer_list.xlsx
• public bool openCustomerDataConn()
• This is used if the ExcelDAL's current use is to query customer data for a selected customer to be passed back and saved in an output spreadsheet; it initializes conn to an OleDbConnection to customers.xlsx
• private void openFile(string filename)
• This is called by both "open data connection" methods to open the error file for writing errors to
• internal List getCustomerList()
• Gets the list of customers for selection in the drop-down and returns it to MainController
• internal CustomerData getCustomerData(int customerID)
• Gets data for a selected customer and returns it to MainController as a CustomerData object
• public bool closeConn()
• Closes the connection to the data source

Error files will be text files placed in the C:\Users\Public folder and begin with "errors_" followed by a label string passed to the ExcelDAL constructor, which helps classify where the error occurred. Error file labels currently used in the project are "getCustomerList" and "getCustomerData", both used in ExcelDAL constructor invocations in MainController. These are used when querying customer_list.xlsx for the list of customers from the drop down, and when querying customers.xlsx for customer data respectively. The queries themselves are simple "select *" commands since all the data is in one table in each workbook (having a more robust or interesting query isn't the point of this exercise). All connecting and reading operations are wrapped in try / catch blocks, where exceptions are caught and have their details written to the an error file.

MainController just has two methods:

• public List getCustList()
• public CustomerData getCustomerData(short customerID)

Both of these perform the following steps:

• create an instance of ExcelDAL
• open the ExcelDAL's OleDbConnection to the relevant spreadsheet
• query the data source for data (either a list of customers for the drop-down or the data for a selected customer)
• close the connection
• return the data to scorecard_helper_form.cs

Next let's look at ExcelInterop, starting with its instance variables:

• private Application application;
• private Workbook workbook;
• private Worksheet worksheet;
• private Worksheet mainWorksheet;

These objects represent an Excel application, workbook, & worksheets respectively. The worksheet instance represents the Data tab in the output workbook, where all the data for a customer will be placed. The mainWorksheet is the main scorecard tab, and all that we do with this is to save the customer name to it.

Next we'll look at ExcelInterop's methods:

• public bool CreateWorkbook(string filePath, CustomerData customerData, BackgroundWorker b)
• This is the main method and the only one that's called from outside this class
• public void PopulateScorecard(CustomerData customerData)
• This takes in a CustomerData object and populates worksheet with data from the object
• public void Dispose()
• Releases and disposes of the workbook
• public void save(string filePath)
• This saves workbook to the file path passed in (in C:\Users\Public as currently set up)
• public static void ReleaseComObject(object obj)
• Called by Dispose to release the passed in COM object (either the workbook or worksheet)
• public void showWorkbook()
• Briefly shows the scorecard workbook to the user

With all of the above covered, we'll go through the final piece - scorecard_helper_form.cs. This is the main class for the application.

There is only one significant member of this class, which is MainController mainController. In the scorecard_form() constructor we instantiate mainController and call updateComboBox(). This uses the getCustList() method of mainController to return a List. This list is what populates the drop-down list (customer_select_combobox) that users select from.

This being done, when the user clicks the button they invoke button_Click(object sender, EventArgs e). This does the following:

• Grabs the customer ID & name that the user selected - these are used to query data for the selected customer and give the output file a name using the selected customer
• Creates a BackgroundWorker to handle the data collection & spreadsheet writing tasks, kicked off by calling bw.RunWorkerAsync();

The BackgroundWorker uses the following event handlers:

• ProgressChangedEventHandler
• This is called whenever we call the BackgroundWorker's ReportProgress method
• We call this at various checkpoints in our app's execution to report updates to the user via the form's output text
• RunWorkerCompletedEventHandler
• This is called when our BackgroundWorker completes its tasks - we provide another update to the user here to let them know we're done and the output file has been produced
• DoWorkEventHandler
• This is the main handler to do the actual work that the BackgroundWorker was created to do

In our case, this involves

• Retrieving customer data in a CustomerData object by calling CustomerData customerData = GetCustomerData(customerID, b); (which calls mainController.getCustomerData(customerID))
• If we've successfully retrieved customer data, we create an ExcelInterop instance: ExcelInterop excelInterop = new ExcelInterop();
• And then call excelInterop.CreateWorkbook(fileName, customerData, b) to generate the output file

That's just about it! What we've got can be used as a starting point for similar applications involving connecting to & reading from data sources, writing to spreadsheets, utilizing BackgroundWorker, robust error handling, and organizing code into a controller,a data access layer, and model classes. Enjoy!