Today, I did a presentation about in what way Office and the upcoming Visual Studio Tools for Office 2005 (VSTO 2005) could play a role in the solutions that are built by the Information Worker unit at Tam Tam.
I tried to start a discussion on this topic by showing some examples. These examples could be used in real life, however in this case they are used for showing the options we have as developers to integrate InfoPath, Word, Excel and Sharepoint using XML Web Services and VSTO 2005.
For this presentation I created a simple case based on employee declarations. In many organizations, an Excel document is printed, filled in, receipts are attached and brought to the finance guy that puts them into a finance system. Now, this would be something that could be more efficient, right? :)
InfoPath to Sharepoint
So, what’s the case here? We have our Sharepoint Portal based intranet and Office 2003 applications such as Word and Excel. With Office 2003, InfoPath was introduced as an XML based application for creating forms and filling them out. Sharepoint supports this format using Form Libraries.
An empty form library
So, InfoPath is a very user friendly tool for filling out forms. Declarations are all about forms, so let’s create a declaration form and put it into Sharepoint. To do something more with the information that is stored in an XML file, I created a web service that posts the InfoPath data into a Sharepoint custom list (“Declaration List”). This web service runs under the Sharepoint virtual server.
[WebMethod]
public void InsertDeclaration(string title, string tammo, DateTime declarationDate, string type, string receiptUrl, double amount, string comment)
{
SPSite mySite = new SPSite("https://[server]/[wss site]");
SPWeb myWeb = mySite.OpenWeb();
myWeb.AllowUnsafeUpdates = true;
SPList myList = myWeb.Lists["Declaration List"];
SPListItem myItem = myList.Items.Add();
myItem["Title"] = title;
myItem["Tammo"] = tammo;
myItem["Date"] = declarationDate;
myItem["Type"] = type;
myItem["Receipt"] = receiptUrl;
myItem["Amount"] = amount;
myItem["Comments"] = comment;
myItem.Update();
}
As you can see, the web service has some parameters that determine the input type (string, DateTime, etc). The good thing about InfoPath is that uses this “schema” to generate the form items based on the web service. So, just create a submit form, connect to the web service for submitting the data. Drag and drop the data source on the form and let InfoPath do the work!
Just drag and drop…
To “finish” it, I mapped to our employee database, to pre-fill the user names. Simple but effective.
Ok, ready to fill a form, click the submit button and the data is posted into the Sharepoint “Declaration List”, including the image of a receipt (that is stored in an image library)
The InfoPath declaration form.
The same item is stored in a Sharepoint list.
Sharepoint in Office Research Pane
In the part above, the data that is posted in an InfoPath form is stored into a Sharepoint list. Now, to do something more with this data, we can use the Word 2003 Research Pane to search trough this data and show it in task pane, where we can use this data in our documents.
To do so, we have to create a research service, which is documented on MSDN as well. This research service basically is had two methods: the registration method and the query method. Use the registration method to register Word to the research service. There is a wizard for building that, but of course this is more fun to build ourselves:
[WebMethod(CacheDuration=60)]
public string Registration(string regXML)
{
MemoryStream ioMemStream = new MemoryStream();
XmlTextWriter myXMLWriter = new XmlTextWriter(ioMemStream, System.Text.Encoding.UTF8);
myXMLWriter.Indentation = 4;
myXMLWriter.IndentChar = ' ';
myXMLWriter.WriteStartDocument();
myXMLWriter.WriteStartElement("ProviderUpdate","urn:Microsoft.Search.Registration.Response");
myXMLWriter.WriteElementString("Status", "SUCCESS");
myXMLWriter.WriteStartElement("Providers");
myXMLWriter.WriteStartElement("Provider");
myXMLWriter.WriteElementString("Message", "Declaration Research Taskpane has been intstalled!");
myXMLWriter.WriteElementString("Id", "{3861168E-3162-4a2d-AF9D-ADBE1F6C0AD1}");
myXMLWriter.WriteElementString("Name", "Tam Tam Declaration Research Taskpane");
myXMLWriter.WriteElementString("QueryPath", "[the webservice url]");
myXMLWriter.WriteElementString("RegistrationPath", "[the web service url]");
myXMLWriter.WriteElementString("Type", "SOAP");
myXMLWriter.WriteStartElement ("Services");
myXMLWriter.WriteStartElement ("Service");
myXMLWriter.WriteElementString("Id", "{363BF043-AE83-49d4-B624-A659F117A4AE}");
myXMLWriter.WriteElementString("Name", "Tam Tam Declaration Research Taskpane");
myXMLWriter.WriteElementString("Description", "Get user declarations!");
myXMLWriter.WriteElementString("Copyright", "All content Copyright (c) 2005 Tam Tam B.V.");
myXMLWriter.WriteElementString("Display", "On");
myXMLWriter.WriteElementString("Category", "[the category]");
myXMLWriter.WriteEndElement();
myXMLWriter.WriteEndElement();
myXMLWriter.WriteEndElement();
myXMLWriter.WriteEndElement();
myXMLWriter.WriteEndElement();
myXMLWriter.WriteEndDocument();
myXMLWriter.Flush();
ioMemStream.Flush();
ioMemStream.Position = 0;
StreamReader iostReader = new StreamReader(ioMemStream);
return iostReader.ReadToEnd().ToString();
}
Now, Office can connect and know how to use the research service. When entering a query, I want to let the service search though the Sharepoint list and returns the data that belongs to name that is given in the query. For example, when I query “Mart”, I want to get all declarations that belong to Mart.
Then after I have the results, I want to have to options: insert the Sharepoint data into my document, or copy it to the clipboard. There are schema’s available for looking up which actions can be performed and how the results are given back in Word.
So, the piece of code that builds the results from Sharepoint looks something like this:
SPSite mySite = new SPSite("[WSS Site URL]");
SPWeb myWeb = mySite.OpenWeb();
SPList myList = myWeb.Lists["Declaration List"];
foreach (SPListItem myItem in myList.Items)
{
if (myItem["Tammo"].ToString().ToLower() == queryTerm.ToLower())
{
myXMLWriter.WriteStartElement("Content", "urn:Microsoft.Search.Response.Content");
myXMLWriter.WriteStartElement("Actions");
myXMLWriter.WriteStartElement("Insert");
myXMLWriter.WriteElementString("Text", "Insert Declaration" );
myXMLWriter.WriteEndElement();
myXMLWriter.WriteStartElement("Copy");
myXMLWriter.WriteElementString("Text", "Copy Declaration" );
myXMLWriter.WriteEndElement();
myXMLWriter.WriteEndElement();
myXMLWriter.WriteElementString("P", "[" + ((DateTime)myItem["Date"]).ToString("yyyy-MM-dd") + "] " + myItem["Title"].ToString() + " (" + myItem["Type"].ToString() + ")" );
myXMLWriter.WriteElementString("P", "by " + myItem["Tammo"].ToString() );
myXMLWriter.WriteElementString("P", myItem["Comments"].ToString() );
myXMLWriter.WriteElementString("P", "Amount: €" + myItem["Amount"].ToString() );
myXMLWriter.WriteStartElement("Image");
myXMLWriter.WriteAttributeString("source", myItem["Receipt"].ToString().Split((",").ToCharArray())[0].ToString() );
myXMLWriter.WriteEndElement();
myXMLWriter.WriteEndElement();
}
}
myWeb.Close();
This code just loops through the list and constructs the xml. Now we can register the web service in Word to do queries.
1) Select the research pane in the task pane
2) Click ‘research options’ and ‘add services’
3) Provide the URL of the service and click ‘add’. Now the service is registered.
4) In the research pane, select the research (in this case ‘Tam Tam Declaration Research Taskpane’)
Now when I search on ‘Mart’ I get my declarations back in the results! Using ‘insert declaration’ I can insert is into my document. Great!
The results and the insertion into the document
This is not new, I know. But it’s fun how to see Word interacting with Sharepoint this way. And of course, in demonstrating office in the Information Workers world, it’s a good example how simple it is to use Sharepoint data in Word.
Sharepoint to Office using VSTO 2005
VSTO, now it is getting really interesting! We have our web service for inserting InfoPath data into Sharepoint, a research service for query Sharepoint data into the research pane in Word. Now, VSTO 2005 is offering a much more rich integration level using managed code and task pane extensions to build applications into your documents.
Note that VSTO 2005 runs on ASP.Net 2.0. I’ve installed an instable version of Visual Studio 2005 BETA 1 to create the demos. However I was spending most of the time on restarting this BETA 1 I created two applications within a few hours.
Sharepoint data into Word
The basic idea of my declaration solution is to do something with the Sharepoint data in Word, like…well, like creating documents that are pre-filled with the declaration data from Sharepoint. Enough reasons to do so, right? ;)
So, I created a task pane extension that has the option to load a web service (by clicking the button) that provides the data for a datagrid, in that same task pane extension. When walkingh through data in this datagrid, the data in the document will change, showing the correct declaration data for printing or saving or whatever I want to do with that. This is how it looks (don’t mind the colors, it’s ugly, I know):
Task pane with the datagrid, and the data in the document
This is a very simple example of how to use the Office task pane extension. But let’s take a look at the code. Visual Studio 2005 can use the Office components to create a, in this case, complete Word environment to program in.
Office in Visual Studio
Right now it is possible to create code in the “code behind” of the document. This solution is creating the task pane extension and loads the data in the datagrid into bookmarks in the document. It also processes the image from an url in Sharepoint to a picture box in the document.
Adding a control (DataSourcePane) to the Actionpane:
private void ThisDocument_Initialize(object sender, System.EventArgs e)
{
dataSources = new DataSourcesPane();
ActionsPane.Controls.Add(dataSources);
}
Connect to web service and load into dataset:
nl.tamtam.intranet.Declarations myDeclarations = new nl.tamtam.intranet.Declarations();
myDeclarations.Url = "[web service url]";
myDeclarations.Credentials = new System.Net.NetworkCredential("[username]", "[password]", "[domain]");
myDataSet = myDeclarations.GetDeclarationDataSet();
And load the data into a BindingSource and bind the bookmarks:
myDataConnector = new BindingSource();
myDataConnector.DataSource = myDataSet.Tables[“Declarations”];
Bookmark1.DataBindings.Add("Text", myDataConnector, Title);
Bookmark2.DataBindings.Add("Text", myDataConnector, Comment );
Bookmark3.DataBindings.Add("Text", myDataConnector,Tammo );
Bookmark4.DataBindings.Add("Text", myDataConnector, Amount);
Bookmark5.DataBindings.Add("Text", myDataConnector, Receipt );
Then now from the task pane, connect the BindingSource to the datagrid:
dataGridView1.DataSource = Globals.ThisDocument.myDataConnector;
The task pane control
To summarize, with a few lines of code, I created a task pane extension, connected a web service to a datagrid in the task pane and then bind the data to bookmarks in a Word document.
Sharepoint data in Excel
Pretty much the same, I reused the same task pane in an Excel. But now, not binded to bookmarks, but to a List, which is a control in Excel. So, the data is bind to the List, now a Chart is automatically generated based on the values in the list.
..with just one line of code:
this.List1.DataSource = myDataConnector;
Conclusion
To summarize, VSTO 2005 and web services make it really easy to do something more with Office documents. In this case, our finance man can dynamically process the declarations that are made with InfoPath, stored in Sharepoint, processed and analyzed in Office. All based on three web services (store declaration, research service and retrieve declaration list) Information Workers can work with real-time data from Sharepoint in Office.
Resources
Office Research Service SDK
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rssdk/html/rsconAboutRSSDK_HV01082263.asp
Research XML schema’s
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rssdk/html/rsReferenceSchemas_HV01082292.asp
VSTO 2005
http://msdn.microsoft.com/office/understanding/vsto/