Create a status report generator using Excel and Outlook: C# sample
Outlook tasks is probably an overlooked feature. Anytime I ask users if they use Outlook tasks it is a small minority that admit to using them. Me? I use them all the time. Typically, I use the tasks as a “master list” of things I need to do. I then use paper to manage my daily tasks.
But, I’ve been thinking… “what if I create a task folder for each project?” That would be a nice way categorizing tasks. This raised the question, what if I wanted to use tasks to track project deliverables as well as to report status to clients? Well, this scenario is the basis of today’s article.
Today, I’ll show you how to create a status report using Excel and Outlook. This add-in sample will run within Excel. The user will have a button to click that starts the report process. Instead of creating a new workbook or worksheet, the add-in will insert the task status report into the existing spreadsheet.
Create the Excel add-in project
First we need to start create a new project for our add-in using Add-in Express for Office and .net. Open Visual Studio (I’m using VS 2013), then create a new ADX COM Add-in project and name it StatusReport (see image below).
Click through the screens of the New Microsoft Office COM Add-in wizard and specify the following:
- Select a programming language: Visual C# Project
- Minimum supported Office Version: Microsoft Office 2010
- Supported Applications: Microsoft Excel
- Strong name key file: Generate new
After you complete the wizard, Visual Studio will create the project and open the AddinModule in design view. This fact makes a great segue to our next task… designing a custom ribbon for our Excel add-in.
Design the custom Excel ribbon
The project requires a simple add-in with a single button. This custom button allows the user to start the status report process. Add an ADXRibbonTab control to the AddinModule and set its caption to TASK STATUS REPORT.
Now add an adxRibbonGroup and an adxRibbonButton control and configure their properties to match those in the screenshot above. I added an icon to the button. This is optional. If you want to add it, just add an ImageList control and import a 32×32 icon into it. Then set this icon as the Image property of the adxRibbonButton.
That’s all the UI we need. The remainder is code.
Write the code
The code for the add-in is split into Outlook functions, helper functions, and button code. We’ll start with the Outlook functions because that is where the code begins to execute.
Outlook functions
GetTaskFolder function
Before we can insert Outlook task data into an Excel spreadsheet, we need to know which Outlook folder contains the data the user desires. This is just what the GetTaskFolder function does. When called, it will display Outlook’s folder selection dialog window.
internal Outlook.MAPIFolder GetTaskFolder () { Outlook.Application ol = new Outlook.Application(); Outlook.NameSpace ns = ol.GetNamespace("MAPI"); Outlook.MAPIFolder fldr = ns.PickFolder(); try { if (fldr.DefaultItemType == Outlook.OlItemType.olTaskItem) { return fldr; } else { return null; } } finally { //Release COM Objects if (ol != null) Marshal.ReleaseComObject(ol); if (ns != null) Marshal.ReleaseComObject(ns); } }
After the user selects a folder, we check to ensure it is a tasks folder. Actually, we check the folder’s default item type is task items. If it is, we return the folder to the calling function. Either way, the calling function needs to check if it received a valid folder (or not).
BuildTaskArray function
A few articles ago, I explained how to insert data into Excel using an array. The BuildTaskArray function is a practical example of how to utilize this strategy. It accepts an Outlook folder as a parameter and proceeds to build an array by looping through each folder item and stuffing task data into an array.
internal string[,] BuildTaskArray(Outlook.MAPIFolder tasksFolder ) { Outlook.Items tasks = tasksFolder.Items; int rowCount = tasks.Count; const int colCount = 9; //I know how many columns I want. This is not dynamic. string[,] taskArray = new string[rowCount+1, colCount]; //INSERT HEADER ROW taskArray[0, 0] = "SUBJECT"; taskArray[0, 1] = "PRIORITY"; taskArray[0, 2] = "START"; taskArray[0, 3] = "DUE"; taskArray[0, 4] = "COMPLETED"; taskArray[0, 5] = "STATUS"; taskArray[0, 6] = "%"; taskArray[0, 7] = "ESTIMATE"; taskArray[0, 8] = "UTILIZED"; for (int i=0; i < rowCount; i++) { //INSERT TASK ROW Outlook.TaskItem t = tasks[i + 1] as Outlook.TaskItem; taskArray[i+1, 0] = t.Subject; taskArray[i+1, 1] = ConvertOLImportanceString(t.Importance); if (t.StartDate.ToShortDateString() != "1/1/4501") taskArray[i + 1, 2] = t.StartDate.ToShortDateString(); if (t.DueDate.ToShortDateString() != "1/1/4501") taskArray[i + 1, 3] = t.DueDate.ToShortDateString(); if (t.DateCompleted.ToShortDateString() != "1/1/4501") taskArray[i + 1, 4] = t.DateCompleted.ToShortDateString(); taskArray[i+1, 5] = ConvertOLStatusToString(t.Status); taskArray[i+1, 6] = t.PercentComplete.ToString(); taskArray[i+1, 7] = Convert.ToString(t.TotalWork/60); taskArray[i+1, 8] = Convert.ToString(t.ActualWork/60); if (t != null) Marshal.ReleaseComObject(t); } if (tasks != null) Marshal.ReleaseComObject(tasks); return taskArray; }
The first row is the header row. This will make things easier later when I turn the inserted data into an Excel table. After creating the header row, the functions loops through each item and match a task property with the correct column in the array. A couple of things to notice are:
- The call to ConvertOLImportanceString. This is a helper method we will discuss later. It does what its name implies and converts this value to a user-friendly string.
- The call to ConvertOLStatusToString. This works the same as above but deals with status values.
- The conversion of date values to strings via the call to .ToShortDateString. It’s a string array so we need to stuff string values.
With the date fields, I decided against creating a helper function. Instead, I check for an “empty” date field on-the-fly. Outlook date fields that contain “1/1/4501” as the value are “empty”.
ALSO TAKE NOTE: This method will only deal with task folders. However, task folders can contain other Outlook item types. This code doesn’t check to ensure it is working with a TaskItem. This is okay, because it is still a sample and my test folder only contains task item. Just keep in mind that you should check your item types in code you plan to release to actual users.
InsertArrayIntoExcel function
After building the task array, we need a method to insert the array into Excel. This function utilizes a passed Range object as the insertion point. To make everything work correctly, InsertArrayIntoExcel resizes the range to be the same size as the passed tasks array.
internal Excel.Range InsertArrayIntoExcel(string[,] tasks, Excel.Range insertRange ) { int rows = tasks.GetUpperBound(0); //Number of rows int columns = tasks.GetUpperBound(1); //Number of columns //Resize the selection to accommodate all rows Excel.Range selectn = ExcelApp.Selection as Excel.Range; selectn = selectn.Resize[rows, columns]; //Set the insertion Range equal to the newly resized selection //Properly Select the range based on the size of the array Excel.Range insertPoint = selectn; insertPoint.Value2 = tasks; return selectn; }
Resizing the range ensures it populates correctly. It easily achieved using the Resize method. Just call it to pass upper bound values of each dimension of the task array. After resizing, I insert the tasks array in the Value2 property and return the range… just in case the calling function wants to do something with it.
Helper functions
Helper functions exist to help the main players. In this Excvel add-in, we have two helpers to handle some.
ConvertOLImportanceString function
Outlook stores a task’s importance values as an integer. The meaning of this integer resides in the OLImportance enumeration.
internal string ConvertOLImportanceString(Outlook.OlImportance priority) { switch (priority) { case Outlook.OlImportance.olImportanceLow: return "Low"; case Outlook.OlImportance.olImportanceNormal: return "Normal"; case Outlook.OlImportance.olImportanceHigh: return "High"; default: return "Not Prioritized"; }
This ConvertOLImportanceString functions takes the passed importance value, converts it, and returns a user-friendly string that will look nice in a status report.
ConvertOLStatusToString function
This method follows the same strategy as ConvertOLImportanceString… except it deals with a task’s status property.
internal string ConvertOLStatusToString(Outlook.OlTaskStatus status) { switch(status) { case Outlook.OlTaskStatus.olTaskComplete: return "Complete"; case Outlook.OlTaskStatus.olTaskDeferred: return "Deferred"; case Outlook.OlTaskStatus.olTaskInProgress: return "In-Progress"; case Outlook.OlTaskStatus.olTaskNotStarted: return "Not Started"; case Outlook.OlTaskStatus.olTaskWaiting: return "Waiting on Client"; default: return "N/A"; } }
CreateTableFromRange function
After inserting the task array into a spreadsheet, it’s a good idea to turn that range into an Excel table. In the Excel object model, a table is called a ListObject.
internal void CreateTableFromRange(Excel.Range range) { //Create the table using the rng's Worksheet property to access the ListObjects collection. Excel.ListObject newTable = range.Worksheet.ListObjects.AddEx( Excel.XlListObjectSourceType.xlSrcRange,range,Excel.XlYesNoGuess.xlYes); //Now, format the table Excel.ListColumn lc = newTable.ListColumns[3]; lc.DataBodyRange.NumberFormat = "mm/dd/yyyy"; lc = newTable.ListColumns[4]; lc.DataBodyRange.NumberFormat = "mm/dd/yyyy"; lc = newTable.ListColumns[5]; lc.DataBodyRange.NumberFormat = "mm/dd/yyyy"; lc = newTable.ListColumns[7]; lc.DataBodyRange.Style = "Percent"; //Hours fields lc = newTable.ListColumns[8]; lc.DataBodyRange.Style = "Comma"; lc = newTable.ListColumns[9]; lc.DataBodyRange.Style="Comma"; //Sort the table newTable.Sort.SortFields.Clear(); //Sort by Due Date (field #4) newTable.Sort.SortFields.Add( newTable.ListColumns[4] as Excel.Range, Excel.XlSortOn.xlSortOnValues, Excel.XlSortOrder.xlAscending); if (newTable != null) Marshal.ReleaseComObject(newTable); if (lc != null) Marshal.ReleaseComObject(lc); if (sheet != null) Marshal.ReleaseComObject(sheet); }
This method sets the formatting for the table as well as its sort order. As an Excel user, I prefer to create tables and this method puts a nice bow on the task insertion process.
Button code – the create report button
Nothing happens in this Excel add-in unless the user clicks our custom button. Here is the code for the button’s click event:
private void btnCreateReport_OnClick(object sender, IRibbonControl control, bool pressed) { TaskMaster(); }
The button calls the TaskMaster method. This is the master method that controls the logic flow of the add-in. It is the lynchpin.
Lynchpin – the TaskMaster method
The TaskMaster calls all of our methods in the correct order to 1) get a task folder from Outlook 2) build an array filled with task data 3) insert the array and 4) format the data as a table.
private void TaskMaster() { Outlook.MAPIFolder fldr = GetTaskFolder(); Excel.Range rng = null; Excel.ListObject taskTable = null; //*Get the task folder if (fldr != null) //If null, don't do anything. { string[,] tasks = BuildTaskArray(fldr); //Build the task arrary rng = ExcelApp.Selection as Excel.Range; rng = InsertArrayIntoExcel(tasks, rng); //Insert the Array into the current selection CreateTableFromRange(rng); //Turn the inserted task data into a table } if (taskTable != null) Marshal.ReleaseComObject(taskTable); if (rng != null) Marshal.ReleaseComObject(rng); if (fldr != null) Marshal.ReleaseComObject(fldr); }
If you followed along and completed all the steps, you are now ready to click Build > Register in Visual Studio. This action builds the add-in and registers it on your system. After you do that, press F5 to run it. When I run the code, I’m able to insert task data into any Excel spreadsheet… like this budget report.
*****
In this Excel sample, I chose not to generate a typical status report. Instead, this add-in inserts task data into an existing spreadsheet. I think it’s a useful scenario.
Available downloads:
This sample Excel add-in was developed using Add-in Express for Office and .net:
Task Status Report add-in for Excel (C#)