How to Create SharePoint Tasks in Excel

Mark Gerow

Law Technology News

11-06-2012

Legal IT professionals know how challenging it can be to persuade busy lawyers to adopt new technologies. Where we might perceive a potential benefit, our colleagues may just see a distraction from their primary responsibility to serve the firm's clients.

This challenge was evident at my firm when we asked our matter teams to manage their tasks using SharePoint. Moving task lists away from the familiar Word documents or Outlook emails to SharePoint offered several benefits, including the ability for multiple team members to update tasks simultaneously without the worry of duplicate or inconsistent versions, built-in alerts and customizable views.

To convince our matter project managers to use SharePoint, we needed to make it as easy as possible — ideally easier than their old method: Enter Excel.

While SharePoint is an excellent tool for many uses, entering and managing certain types of data can be better accomplished through a spreadsheet. To reduce the effort required by those responsible for populating and maintaining matter task lists, we developed a customized Excel workbook that was capable of reading from and writing to a linked SharePoint task list. In doing so we not only reduced the effort required to create SharePoint lists, but also increased task consistency across matters.

This article describes how to create a simple Excel spreadsheet that links to a task list using SharePoint's native web services and Visual Studio Tools for Office (VSTO). For those readers responsible for programming SharePoint solutions, you'll find everything you need to get started. For managers or non-programmers, you'll get a glimpse of what your programmers can create for you using these technologies.

INGREDIENTS

To build this application you'll need Visual Studio 2010, Excel 2010 and access to a SharePoint 2007 or 2010 site containing a task list to which you have at least "Contribute" permissions. In the remainder of this article I'll cover how to reference data in Excel from a C# application, and how to read and write data to SharePoint using the built-in web services. Let's get started.

READING FROM AND WRITING TO EXCEL

When programming against Excel you generally want to read data from, or write data to one or more cells. In Excel terminology a collection of cells is known as a "range," which can be referenced in one of two ways:

• by address (e.g. "A17"); or

• by name, e.g., "TaskData."

Here's an example of code to reference the value in a single cell:

string s = this.Range["A17"].Value.ToString();

Or,

This.Range["A17"].Value = "LAW.COM";

Note the "Value" property, which contains the contents of the cell (if any). In a finished application you would typically either test whether the Value property is null, or wrap the assignment statement in a try/catch block, to ensure empty cells don't cause the program to fail.

When you need to iterate through a group of cells, you use two nested for-loops as in the following example:

     foreach (Excel.Range row in taskData.Rows)
     {
          foreach (Excel.Range cell in row.Cells)
          {
               cell.Interior.Color = unChangedCellColor;
           }
     }

With these basic techniques in hand it's possible to perform all of the programming tasks required to read and write data from/to our worksheet.

UPDATING SHAREPOINT USING WEB SERVICES

The next bit of knowledge required is how to update a SharePoint list using the built-in web services. Use of the web services, rather than the object model, is necessary here because our worksheet will run on the users' computer inside Excel, not on the SharePoint web server.

SharePoint comes with several web services, so the first hurdle is figuring out which of these to use for our purposes. In this case we want to use the "Lists.asmx" web service, and specifically two methods of that service:

• GetListItems() to retrieve data from SharePoint; and

• UpdateListItems() to add or update SharePoint list items.

The code to use these services is a bit convoluted, so I won't provide examples here. We'll get into those details later in this article.

BUILDING THE SOLUTION

With the necessary ingredients at hand, it's time to roll up our sleeves and get started. Specifically, we will:

1. create a new Excel Worksheet Project in Visual Studio;
2. layout and format the worksheet;
3. create a new SharePoint task list if necessary, and add one custom field;
4. add a reference to the needed SharePoint web service;
5. write C# code to create tasks in a SharePoint task list from data in an Excel worksheet;
6. write C# code to update an Excel spreadsheet with data from a SharePoint task list;
7. test and deploy the Visual Studio Tools for Office (VSTO) solution;
8. configure Excel to "trust" the installation location of the deployed worksheet; and
9. install and run the deployed application on a computer.

CREATE A NEW VSTO EXCEL 2010 PROJECT

Step 1: Create a new project in Microsoft Visual Studio 2010. See Figure 1.

Figure 1: Creating a new project. Click image to enlarge.

Step 2: Select the 2010 Excel Workbook project template.

Figure 2: Selecting the VSTO Excel 2010 project template. Click image to enlarge.

Step 3: Choose the option to create a new document, which will add a new Excel worksheet to the project.

Figure 3: Adding a new worksheet to the project. Click image to enlarge.

We now have a VSTO project containing one Excel workbook and several C# code files.

Figure 4: A newly created Excel project. Click image to enlarge.

LAYOUT AND FORMAT THE WORKSHEET

The basic structure of our task-entry worksheet will be as follows:

What's significant about this layout is that the rows and columns are preset in the Excel template. Only the StartDate, EndDate and numeric hours estimate need to be entered. By prefilling all other data we reduce effort and increase consistency across matters, thereby promoting efficiency and better communication by matter teams. By adding formulas to sum the hours we allow the user to perform "what if" estimates before committing changes to SharePoint.

The names in cells D1 to H1 represent names of timekeepers on the matter who will be assigned to the task represented by each cell with a numeric estimated-hours value.

The following screen shot shows the fully formatted worksheet:

Figure 5: The formatted worksheet with sample data. Click image to enlarge.

Note: the range highlighted in yellow in the above screenshot has been given the name "TaskData", and is referenced in the C# program developed later in this article.

Add the "Hours" field to the target task list. The application we are about to create will use a standard SharePoint task list to which one custom field has been added; we need an "Hours" field to capture the estimated number of hours for a given task and professional. This field will be of type "Number" as shown in the following screenshot:

Figure 6: Creating a custom "Hours" field. Click image to enlarge.

ADD A REFERENCE TO THE LISTS.ASMX WEB METHOD.

The "Lists" web service is installed as part of SharePoint, and may be used to read and write data from and to any SharePoint list in the farm. Before we can use this web service, however, we must add a reference to it in our VSTO application. To create the reference, follow these steps:

Step 1: Add a service reference to the project.

Figure 7: Adding a service reference. Click image to enlarge.

Step 2: Click on the "Advanced ..." button.

Figure 8: Selecting the "Advanced" service option. Click image to enlarge.

Step 3: Click on the "Add web Reference ..." button.

Figure 9: Adding a web service reference. Click image to enlarge.

Step 4: Enter a URL to the web service in the form "<url to SharePoint site containing the task list>/_vti_bin/Lists.asmx"

Step 5: Enter a web reference name, then click "Add Reference."

Figure 10: Entering URL to service and a service name. Click image to enlarge.

CREATE METHOD TO WRITE EXCEL DATA TO SHAREPOINT

Now we can get to work writing some code. This code will be written in the "Sheet1.cs" program file of our VSTO solution. First we'll create a method that will execute when the "Upload Tasks" button is clicked. This method will do the following:

1. Loop through each cell in the range of cells containing estimated hours values (E2:H4 in the sample layout), finding cells with valid numeric values.

2. For each value found, read the corresponding values for:

1. Title;
2. StartTime;
3. EndTime;
4. Timekeeper.

3. Check to see if the cell's comment field is empty,

1. If so, add a new task item to the SharePoint list,
2. Else update the existing task indicated by the ID# stored in the comment.

The following code fragment will save one task from Excel to SharePoint: Code_Fragment_1.

After executing the preceding code, the sample worksheet and task list will appear as follows:

Figure 11: Worksheet showing comments after a successful upload. Click image to enlarge.

Figure 12: The populated task list after a successful upload. Click image to enlarge.

CREATE METHOD TO UPDATE EXCEL WITH SHAREPOINT DATA

To allow for the case where updated are made directly to the task list in SharePoint, we also need to be able to retrieve task data from SharePoint and write it into the corresponding cells in our worksheet. To accomplish this we'll use SharePoint's built-in GetListItems() web method. Specifically, we need to:

1. retrieve all tasks from the linked SharePoint task list;
2. loop through each retrieved task; and
3. if the task ID# corresponds to one of the comments of a cell in the data range, replace the value of that cell with the "Hours" value read from SharePoint.

The following code fragment performs these operations: Code_Fragment_2

TEST AND DEPLOY

Visual Studio 2010 makes testing a VSTO application straightforward. Clicking the run button within VS 2010 launches Excel with full debugger support. One challenge, however, is limited ability to inspect the Excel objects because they are not managed COM objects. Therefore, for debugging purposes, it's often useful to store cell values in C# variables so intermediate results can be inspected within the debugger.

Once the application has been tested and debugged, it's time to deploy it. This is accomplished by right-clicking on the project node in the Visual Studio Solution Explorer, then selecting the "Publish" menu option:

From there follow the prompts to write a Setup.exe application and all supporting components to a local or shared folder on your network.

Figure 13: Deploying ("Publishing") the finished solution.

ENABLE A CLIENT COMPUTER TO INSTALL AND RUN THE WORKSHEET

Here's where things can get a bit tricky. By default Excel won't allow you (or any user) to install this application! The reason for this is that Excel, to protect users from potentially malicious add-ins, will prevent them from installing worksheets with VSTO customizations unless the location of those customizations is "trusted." Note: If you only want users to install and run copies of the worksheet from their personal computers, as opposed to running from a network share or a SharePoint library, these steps are not necessary.

To allow Excel to install and run your customized worksheet, follow these steps:

Step 1: Open the Excel Options dialog from Excel 2010 "File" menu.

Figure 14: Configuring trusted locations on a computer.

Step 2: Click on the "Trust Center" tab.

Figure 15: Adding a new trust location.

Step 3: Click on "Allow Trusted Locations on my network (not recommended)."

Step 4: Click on the "Add new location ..." button, and then add a URL pointing to the SharePoint library that will contain the worksheet.

Figure 16: Continue adding a new trust location.

Step 5: Click on the "Subfolders of this location are also trusted."

INSTALL AND RUN THE COMPLETED SOLUTION ON A COMPUTER

The final step is to verify that the application will work on the end-users' computer. To do this, navigate to the folder where you published the solution, then double-click on the Setup.exe program. This should display the installer program. Click on "Install" to add the necessary .NET components to the General Assembly Cache (GAC) of the computer on which you're running the install. Finally, double-click on the Excel workbook to load it. If the "trust" was set correctly in the preceding step, the workbook should load and be ready to use.

Figure 17: Installing the finished solution.

For the complete C# code, see Complete_Code.

IDEAS FOR FURTHER REFINEMENT

You now have a basic VSTO Excel application that can be used to easily create and manage matter tasks in a SharePoint task list, but obviously there are numerous potential refinements. Some worth considering include adding:

• graphs, pivot tables and other Excel objects to analyze task hours;

• fee amounts to provide estimated costs as well as hours; and

• fields to tie in with your firm's financial system.

CONCLUSION

By combining the familiarity and structure of Excel with the flexibility of SharePoint it's possible to craft a solution that enables rapid data entry, supports collaboration, and promotes consistent project management across similar matters. Building such hybrid solutions requires mastering two programming models, SharePoint and VSTO, but the effort will be rewarded with improved end-user satisfaction and adoption.

Moreover, possessing the capability to deploy applications that combine rich desktop client interfaces with SharePoint opens up a new world of possibilities; bringing together the best of Excel, Outlook or Word with SharePoint.