SharePoint Lists In Excel Via VSTO

How to customize a Microsoft Excel worksheet for maintaining list contents from multiple SharePoint sites. How to develop a custom Action Pane for Microsoft Excel to make it compatible for gettting SharePoint site lists and getting the list content.

Windows SharePoint Services (WSS) sites provide a focused space for users to share information to achieve a goal or complete a process. Often these sites are customized by an organization so they meet the exact needs of the type of collaboration the group of users is trying to perform. Usually each site represents a specific instance of the process and has the appropriate lists and libraries to store the necessary information. Though the site services the needs of the group for specific instance of the process, it does very little for the user who may be involved in many of these sites.

Even though each site has the same type of content, the user would be burdened with accessing each site during the day to make content changes. So, a good solution would be to consolidate the content from these similar sites into a single tool for the user. There the user could maintain the content for all of the sites and periodically save her changes back to the corresponding lists in SharePoint sites. Ideally the tool would also provide this information in a way that supports visualization techniques such as charting. In this article we will detail how as a developer, you can extend Microsoft Excel to construct such a tool. Actually, we will provide the list data available to the user event if the user is offline and not able to reach the SharePoint sites.

Some real world examples

Many organizations use SharePoint as a type of case-management system. Case Management, in general terms, means that there is a documented process for which the organization manages many instances. For example, any time a military base gets notified of a distinguished visitor, there is a documented process of what types of actions need to be taken, documents completed, etc. Yet the base could easily have many instances of this process for different visitors at different times. The result is many SharePoint sites. The idea of case management can apply to many other scenarios: A software-consulting shop would provide a development life cycle with many specific project instances actively running. A marketing team would have a collaboration site for each of its campaigns.

Example Overview

Our example sets out to address the scenario in which a user is facing with the challenge of maintaining the same type of content on many sites. For our example we have decided to use a list that contains information about the projects.  For that I’ve created a Project Task list provided in SharePoint Site and added couple of more columns. For every project we will store title, project number, start date, due date and budget. For the deployment of this project list we will create a list definition using SharePoint Solution Generator provided with Visual Studio 2008 Extensions for Window SharePoint Services.


With our example of multiple sites with Projects lists, we will develop an application that gets the information form these different sites into a single place for our user. For that we will customize a Microsoft Excel workbook using custom .NET code. When the user opens the spreadsheet, an action pane will open alongside the document, allowing the user to register the different SharePoint sites and lists she wishes to maintain in the tool. Each registration of site is considered as connection. Internally, in the site’s list we will store the connection name, list’s name, site URL that contains the list and the time of the last modification made to this site’s list. This information will help us communicate with the site through web services and synchronize its data. The synchronization interface provided in the tool enables the user to retrieve all the list items for each of their connections. Internally, the solution will store this data in a DataSet while displaying it in a consolidated view for the use – as a single Excel worksheet.  Then the user will be able to use that worksheet to modify any projects list item, delete project items, or add new project items. By clicking the Synchronize Now button, all the changes made by user are committed to the original SharePoint sites’ lists.

By creating the customized solution on top of Microsoft Excel workbook, we get some additional functionality that improves our tool for the user who is maintaining all of the content, like, displaying data in worksheet, the user can sort and filter the data. Though this is provided in the list’s web part, the worksheet enables the user do to these actions across all the data from all the sites. The most important is, our internal DataSet is saved with the workbook, so the list item data is available to the user event if the user is not connected to the site and is offline. This offline editing capability does provide a powerful alternative to navigate each site and making the changes online.

Example Walkthrough

This walkthrough will guide the major elements of the solution. First, we will explain how a list definition is provided to our SharePoint environment to make sure that each site we want to maintain project information has same projects list. Then we will focus on the construction and development of the tool to allow a user to maintain project information from multiple sites, even offline. The tool will be constructed by extending Microsoft Excel and the walkthrough will guide you the major phases of development. The walkthrough also will explain the designing of a DataSet that will store the list information, the development strategy of detecting network availability and the implementation of action panes to host our tool’s custom interface. Then we will detail how to synchronize the data between the DataSet and lists in SharePoint sites.

Note: To implement the solution, your development machine needs the following:

  • .NET Framework 3.5

  • Visual Studio .NET 2008

  • Visual Studio Tools for Office 2007

  • Office 2007 with primary interop assemblies

Creating the Projects List Definition

Before setting concentration on Excel spreadsheet, we want to take care of the issue of providing SharePoint sites with the capability to maintain information on projects. It is important that each site that store project information has the unique schema. There are two ways to do so: We could create a list template or a list definition. A list template is typically what an end user would create by using the web interface to set up the list and then use its “Save as Template” action in the list’s settings. The result is a file with an STP extension that is stored in site collection’s List Template Gallery. This STP file is stored in content database and also includes the customizations that the user made as well as a reference to the original list definition type. But this opens up some maintenance concerns and really doesn’t solve the need of a new enterprise-wide type of list.


List definitions, are stored on the file system and contain a schema XML file that defines the fields in the list. View information, styles and the forms the user interacts with are also a part of the list definition. As creating list definition is considered as a developer task, it is not in so much practice because of the awkward syntax of XML and CAML (Collaborative Application Markup Language). In this example we will show you how to get the benefits of having your list as a definition with the ease of building a template.


The first step is to select a team site you for development and to create a Projects Task list named Projects. Add some extra columns as shown below using the list’s Settings administration screen.

  • Project Number of type Text

  • Budget of type Currency with two decimal places.

With the list created and modified, we will use the SharePoint Solution Generator tool included in the Visual Studio 2008 Extensions for Window SharePoint Services package. This tool needs to be installed on the WSS or MOSS server since it accesses the object model directly. You can download this from http://www.microsoft.com/downloads/en/details.aspx?familyid=FB9D4B85-DA2A-432E-91FB-D505199C49F6&displaylang=en.
Once installed and successfully launched, it looks list the below image.


The SharePoint Solution Generator will create a list definition from an existing SharePoint list. Follow the below steps through the wizard:
1. Select List Definition and click Next.
2. Select the SharePoint web that contains the Projects list you have created.
3. You will see the set of lists contained within the site. Find your Projects list and check it and click Next.
4. The Solution Generator creates a visual Studio Project that contains the definition. This step asks you to name and select the location. Name the solution ProjectsListDef. The default location is in a SharePoint Definitions folder of your My Documents folder. Below image shows these options.
5.
6. Click Next.
7. On the last screen, click Finish. The SharePoint Solution Generator will capture the list as a definition.

While still working on your MOSS server, open the new created project. There are two settings that need attention. On is in Debug that of project’s properties. Make sure that start action is set to Start Browser with URL and its value is a valid SharePoint web application in your development environment. For second setting, open the ListDefinition.xml file from Project folder from Solution Explorer. Change the value of the Type attribute to 1001. We want this to be a unique number so that we can tell Projects lists apart from other site lists.


You can now right click on the project and choose Deploy. Visual Studio will perform many tasks, including defining your list definition as a SharePoint feature and then packaging it as a solution to be deployed into you environment.
Once the deployment is done, it can be activated in sites within that web application. You should delete the Projects list you created in your development site; it is not an instance of the definition you just deployed. Use the following instructions to activate the Projects feature, which will make your list definition available.

1. Go to Site Settings.
2. Click on the Site Feature link in the Site Administration group.
3. Find the Projects feature and click the Activate button, if the feature is not activated.


As the below image shows, once the feature is activated you will see a new Projects list type on Create page under Custom Lists group. Any site administrator can now choose to maintain projects. We will be able to identity these lists by their type and each list will be the same schema. Make sure you have at least two team sites that have the Projects feature enabled and have instances of the list definition.

Creating the Excel Document Project

Creating an Excel smart document project in Visual Studio is very easy by the project types VSTO adds. Simply start Visual Studio and select to create a new project. Under the Visual C# language node, expand the Office node and locate the Excel Workbook or Excel 2007 Workbook project template. Name the project and solution MaintainProjectsOffline. Once you click Ok, the VSTO Project wizard will ask you if you want to create a new Excel workbook or import an existing one. For this example, however, create a new document named Projects. Once the new project is created, you solution will already have a few files by default. These are visible in the Solution Explorer window of Visual Studio. There will be a Projects.xlsx node that when expanded, will have files for each of the sheets in the Excel workbook. There are three by default. As we will need only Sheet1, in the Excel designer, right-click on a worksheet tab to get the delete option and remove the other worksheets. See how the respective code file is also removed from project. Now rename the Sheet1 to Projects Sheet, using same way. The designer should reflect this change and you should see the name appear in parentheses in the Solution Explorer; however, the code file remains Sheet1.cs. The ThisWorkbook.cs file has no interface and as it is a class in which code can be written for responding to events at the document level. In this file, you will find the handlers like Startup and Shutdown at the workbook scope level.

Designing the DataSet

This tool will allow the user to fetch items in Projects lists from multiple sites and maintain them within the document. This data will be stored in the document and made available even if the user is offline. To facilitate this, we will leverage the smart document’s capability to cache a DataSet in the document. We will use a single DataSet to store all the required data for solution, including the project information, the lists that contained that information and some settings information. Add a new DataSet namd ProjectsData to your solution. Below image show the structure of the DataSet, which includes a Settings DataTable to store settings information, a Lists DataTable to store the sources of the data and a Projects DataTable to store the list information itself.

The Settings DataTable has only two fields: FieldName and FieldValue. The FieldName column is the table’s primary key. Our solution will use the table to store information outside the maintained data. For example, a setting would be the one named LastSynchronizationTime, whose value is the last time the tool successfully synchronized with its lists.

The Lists DataTable stores the locations of the projects lists that the user has requested to maintain within the tool. This DataTable stores name of the list in SharePoint Site, a URL to the site, a timestamp of last modification of list and a string representing the connection name. The connection name is a string value the user will specify when adding the list to the tool. This connection name is configured as Primary Key and unique identifier.

The Projects DataTable stores the project information list items. This has a combination of the connection name and the id of the list item as its primary key. The ListItemID field is an integer that SharePoint uses internally for list items. We then store the columns of data in the project list: Title(string), ProjectNumber(String), StartDate (System.DateTime), EndDate(System.DateTime) and Budget(System.Double). The Projects DataTable has a relation with its parent Lists DataTable. The relation is set up between two ConnectionName fields. The ForeightKeyConstraint is configured to cascade on updates and deletes.

Detecting Internet Connectivity

An important function of any tool that you want to work both online and offline is its ability to detect connectivity status. In this example, this check is made within a ConnectionManager class. This class provides an IsOnline() method that simply returns True if the machine has a valid network connection or False if it doesn’t. Within this method, the class leverages a PInvoke to a function defined in the Windows API named InternetGetConnectedState. This is a part of unmanaged code and contained in wininet library.

public class ConnectionManager
    {
         [DllImport("wininet", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]
        private static extern bool InternetGetConnectedState(ref long dwflags, long dwReserved);

        public static bool IsOnline()
        {
             long dwflags = 0;
            bool WebTest = false;
            WebTest = InternetGetConnectedState(ref dwflags, 0L);
             return WebTest;
        }
     }

Constructing the Actions Pane

Microsoft Office 2003 introduced the concept of panes that would display within the Office application alongside the document. These panes would allow the user to research information, search or see other data within a SharePoint site. With Visual Studio Tools for Office you can build two types of panes yourself: Task Panes are scoped at the application level and can be viewed even if a document is not open. Actions Panes are used within a specific document. Both can be developed via .NET user controls.


The code snippet below is from ThisWorkbook.cs. The three user controls are declared as fields of the workbook. A SetupTaskPane method is called within the workbook’s startup event handler. This setup method establishes the size and position of the actions pane within the application. It then instantiates instances of the controls that will make up our actual action pane’s UI and adds them to the controls collection of the document’s ActionsPane object.

internal TaskPaneHeader _taskPaneHeader;
         internal TaskPaneBody _taskPaneBody;
        internal TaskPaneFooter _taskFooter;

         private void ThisWorkbook_Startup(object sender, System.EventArgs e)
         {
             SetupTaskPane();
         }

         private void SetupTaskPane()
        {
             this.Application.CommandBars["Task Pane"].Width = 350;
             this.Application.CommandBars["Task Pane"].Position = Microsoft.Office.Core.MsoBarPosition.msoBarLeft;

             Globals.Sheet1.Select(true);

            _taskPaneHeader = new TaskPaneHeader();
            _taskPaneBody = new TaskPaneBody();
            _taskFooter = new TaskPaneFooter();

            _taskPaneHeader.Dock = DockStyle.Top;
            _taskFooter.Dock = DockStyle.Bottom;
             this.ActionsPane.Controls.Add(_taskPaneHeader);
             this.ActionsPane.Controls.Add(_taskPaneBody);
             this.ActionsPane.Controls.Add(_taskFooter);
        }

Maintaining the List of Site Connections

The interface that the user will see when opening the tool for the first time allows her to specify which Projects lists from which sites she wishes to manage in the spreadsheet. Each list that is maintained is stored with a connection name. This allows us to uniquely identity lists that may have the same name but were from different sites. Below image will give view the overview of the control. The two main actions user can perform are to add or delete a connection. To delete a connection, we are going to require that the DataSet has no pending changes. This is so that we can successfully remove the items for that connection and accept those changes to DataSet without accepting changes the user may have made. Since we set up the relation to cascade deletions to the child Projects DataTable, this delete action also removes the associated list items. When the removal is complete, the changes to the DataSet are committed so that when we sync at some later time we don’t accidently delete these items from the site.

Adding a new connection to the DataSet involves the user first entering the URL of the SharePoint site that contains the list she wants to maintain. The URL for the site should be in the format of http://portal.sample.com/SiteDirectory/jatintest. Once the URL is entered and user clicks the Go button, the site is examined to find what lists it contains and particularly finding the Projects list definition. The last step of adding a connection to the DataSet is for the user to select one of the project lists of the site and to name the connection. When the user clicks the Add connection button, the selected connection is added to the DataSet. Until the synchronization is not performed, the items of the list are not seen in the tool. To identify the newly added list in the DataSet, we set its LastModified field to DateTime.MinValue.

Implementing the Synchronization UI

The synchronization UI can be brought to forefront by the user clicking the Data button in the actions pane’s header. The interface persists changes the user has made to the project list items back to their respective SharePoint sites, as well as load changes from those lists back into the tool. When the interface is displayed, the last synchronized time is displayed. The interface also informs the user of the number of new lists the user added as new connections. These items need to be retrieved into the tool. Additionally, the interface displays the number of pending changes the user has made to items and that need to be saved back to the sites. These statistics are updated whenever the visibility of the synchronization interface is changed or if the Refresh button is clicked.

This control uses the UpdateSyncStats method to update the values of the number of new lists and the number of changes. The below code snippet shows the method.

private void UpdateSyncStats()
        {
            newListsView = new DataView(projectData.Lists);
            newListsView.RowFilter = "LastModified='" + DateTime.MinValue.ToString() + "'";
            this.lblSyncNewLists.Text = string.Format("{0}: # of new lists to capture", newListsView.Count);
            DataTable changedItems = projectData.Projects.GetChanges();
             if (changedItems != null)
            {
                 this.lblSyncChanges.Text = string.Format("{0}: # of changes you made to data", changedItems.Rows.Count);                 
             }
             else
                 this.lblSyncChanges.Text = string.Format("{0}: # of changes you made to data", 0);
        }

This method determines the number of changes the user has made by calling the GetChanges method of DataSet’s Projects DataTable. If this method returns a DataTable object, then there are pending changes which are need to be saved back to SharePoint sites.

Architecture of the Synchronization Process

The synchronization process is responsible for committing changes, if any, that the user made to project information in the tool and making those same changes to the list items in the SharePoint sites. The process must also fetch any list items that have been modified in the sites’ lists from the last synchronization time. It is easy to understand the process by viewing the entire process as sequence of the steps. Below image show the step required for synchronization process.



Our synchronization process first makes sure the tool is not offline. If a connection is present, it begins to create a batch file for each list to contain the changes the user made to its list items. This batch contains additions, updates and deletions. Once the user made changes are persisted back to the sites, the tool looks for lists that have been modified since the last synchronization. If any are found, the items for that list are refreshed into the DataSet. After all the changes have been done, the tool gets the content for the new connections. Finally, the changes made to the DataSet are committed so that we will be able to identify changes the user makes from this synchronization point. For detailed code of this process please download the example.

Associating and displaying the Data on the Spreadsheet

Displaying the DataSet’s data on the spreadsheet requires less code than the synchronization process. In fact, it requires no code at all. Open the designer of Sheet1. In Visual Studio’s toolbox, locate the ListObject control in the Excel Controls group. This control will allow us to bind a range of cell to the Projects DataTable in the DataSet. Drag it onto the spreadsheet and specify it to occupy the $A$2:$F$2 range. Then use the Properties window to specify a data source. Clicking on the down arrow of this property should display a dialog. Select the projectsData1 DataSet in the Sheet1 List Instances category. Once that property has been set, select the Projects DataTable as DataMember property value. The ListObject should refresh to display the column names for this DataTable. You have now bound the ListObject control so that it will display the items in the Projects DataTable of our DataSet. You spreadsheet should look like below.


Using the Tool

Before running the solution, make sure you close the spreadsheet that’s open in Visual Studio’s designer. Because running the program will launch Excel, which will open the workbook generated in the bin directory of your project. The same spreadsheet can’t be open in VS’s designer and in Excel.
For the test, we have at least two team sites with the Projects list definition available and a few items created already.  Click the Start Debugging button in Visual Studio. This action builds your solution and therefore the workbook. The solution will open more slowly in this case than normal.
Add the two connections for your sites. Remember that the URL should be in the format http://vpc2003:9767/Site1. Use the actions pane’s header to display the synchronization interface. This should tell you that there are two new lists for which you need to get content. Click the Sync Now button and watch the ListObject populate with your site’s content. The result should look like below image.


Now make some changes. Change a title or a project number for one row. You can also highlight one whole new row and right-click to delete it. If you place your cursor at the bottom of the ListObject where there is an asterisk (*), you can define a new row. When specifying a new item, you need to enter in a valid connection name and a unique list-item identifier. Remember that these two fields make up the primary key. After you have made some changes, click Refresh in the synchronization interface; it will update to tell you the number of pending changes. Click the Sync Now button and revisit the lists in the SharePoint sites. Verify that you changes were persisted.
To see the caching capability, close the Excel. When the application asks you if you want to save changes, select Yes. By saving the file, you are persisting the DataSet with the document. Closing the document should end your debug session in Visual Studio. Use Windows Explorer and navigate to your solution’s bin directory. Open the Projects.xlsx file to open in Excel. Notice that the data is already there.

Considering Deployment Options

For document-level projects, an administrator needs to select a deployment model for distributing the customized document as well as the related code-behind assemblies. There are three main models characterized by the location of files. Each has its own benefits and depending on your needs any of them could be used.
The Local/Local deployment mode is one where both the customized document and the assembly are deployed on the end user’s desktop machine. This model is best suited when the solution needs to be available regardless of that machine’s network state.

The Local/Network deployment model involves deploying the document to the user’s computer but making the assembly available through the network. This could be accomplished using a network share or a web server. This is the most common deployment model, as it offers the widest range of benefits. It offers single point of maintenance for the assembly for solutions where those changes are more likely than updates to the document. Also, the user can still continue to work when the network is not available.

The Network/Network deployment model places both the document and the assembly on a network share or web server. For solutions in which change is frequent this is ideal since the document and assembly are in a single location for all users. However, users must need to be connected to the network otherwise the entire solution is unavailable.

Summary
Hope you have enjoyed the walkthrough and hope this will be helpful to you for your development task. In this example, we have not considered the collision detection while synchronizing the changes from DataSet to SharePoint Sites.

Source code

Maintaining offline list content from multiple site in Microsoft Excel

By Jatin Prajapati   Popularity  (2758 Views)
Picture
Biography - Jatin Prajapati
I think, most of the people are interested only in answers so no Biography provided... Want know more just write me at jatin.prajapati.er@gmail.com