devxlogo

Activate Your Word Documents with VSTO

Activate Your Word Documents with VSTO

he word processor was invented to simplify the preparation of words to be printed on paper. It offered font management, layout management, and the ability to type words onto a soft screen, changing, formatting, and laying them out as you pleased before committing them to a hard-paper copy by printing them out. Word processing revolutionized many industries, and rapidly became the tool of choice for everybody who needed to write something?not just professional authors and publishers. Microsoft Word, although a relative latecomer to the word-processing application genre, took the industry to a new level by popularizing What You See Is What You Get (WYSIWYG) editing?using Windows’ graphical display abilities to let users edit documents in a form nearly identical to the final printed form. This capability caused Microsoft Word use to quickly overtake the older fixed-font DOS word processors.

Despite the advancements in presentation, the idea of an editable document remained fixated on the familiar paper format?even though electronic documents are mutable. Even today, we still produce documents as if we are going to print them, with all the trappings of a printed document, such as appendices, page numbers, and static content. Most of us read our documents within our word processors, but forgo the power of the computing platform when using them. That’s finally about to change. With Visual Studio Tools for Office you can turn your documents into active applications that can change according to the users’ needs.

Take, for example, the concept of a document that reports on sales figures. You’ll typically have a summary at the top of the document and then drill down a little further into detail by producing a graph later in the document that shows the sales figures over time. That’s a static format, but it gets the message across. A successful document will generally leave its audience “wanting more,” so you might generate an accompanying spreadsheet with more detailed figures for interested readers to massage to get additional value.

The obvious next questions are: Why use a separate accompanying application to provide additional value? Why not include that kind of functionality directly in the document itself? Why not enable the document itself to show custom reports and business logic? The readers are (most likely) going to read the document on their computers, so it makes sense to give them the capability to activate the document and get additional data directly, without launching external applications.

That’s where Visual Studio Tools for Office comes in. As you’ve seen in the previous two articles in this series, you can use Outlook as an application platform to generate alerts for your users, and you can build research panes in Excel to bring necessary information to your users without breaking them out of their workflow. This article will show you how you can stop using Word just to generate passive paper-like documents and start using the full power of the computing platform to create active documents with increased value.

The Document
This article shows how to build a simple sales report containing a chart that shows the total value of sales for each day in the reported month?but this document also contains a calendar. When the reader selects a date from the calendar, they get a data grid showing each sale for that particular day and how those sales break down into the total amount for that day in the chart. You can see the document in Figure 1.

Figure 1. The Active Document: Selecting a day from the calendar control fills the DataGrid on the right with data, and shows how the selected day’s sales contributed to the total sales for the month.

To implement this functionality you have a number of options. I used a Web service that returns the sales for a particular day. The active document calls the Web service whenever the user selects a day on the calendar, and renders the results in the DataGrid control. I chose to use a Web service because a document may be widely distributed, and a Web service can be hosted on the Internet, making it available from anywhere. If you were binding directly to a SQL Server database or other data source, getting such wide-spread connectivity would be difficult. This is another (albeit simple) case where designing your applications under the principles of SOA or Software as a Service (SAAS) can have far-reaching effects.

The next sections provide a more detailed look at the applications involved, starting with the Web service, and then exploring the document itself.

The Web Service
I used a simple ASP.NET Web service that wraps the AdventureWorks sample database that ships with SQL Server 2005. Make sure you have SQL Server 2005 and the sample database installed before continuing.

Using Visual Studio, create a new Web service in the usual way, and add a data set to it, by right clicking on the project file within the Solution Explorer, adding a new item and selecting an XSD dataset. Name the dataset Sales.xsd.

Proceed through the wizard pages until it asks you for the SQL to use to populate the dataset, then use this SQL:

SELECT     Sales.SalesOrderHeader.SalesOrderID,               Sales.SalesOrderHeader.OrderDate,            Sales.SalesOrderHeader.CustomerID,            Sales.SalesOrderHeader.SalesPersonID,              SUM(Sales.SalesOrderDetail.LineTotal) AS TotalSalesFROM       Sales.SalesOrderHeader INNER JOIN           Sales.SalesOrderDetail ON               Sales.SalesOrderHeader.SalesOrderID =                Sales.SalesOrderDetail.SalesOrderIDGROUP BY Sales.SalesOrderHeader.SalesOrderID,          Sales.SalesOrderHeader.OrderDate,          Sales.SalesOrderHeader.CustomerID,          Sales.SalesOrderHeader.SalesPersonIDHAVING   (Sales.SalesOrderHeader.OrderDate =             CONVERT(DATETIME, @ParamDate, 102)) 

Note that the query takes a parameter, @ParamDate, (the bold text in the preceding code).

This pulls the sales order ID, order date, customer ID, and sales person ID for all sales in the system for the day specified in the parameter. You could make this a little more friendly by joining more names to get, for example, the Customer name; but for simplicity I just used the IDs.

Assuming you used the name Sales.xsd for the dataset, you can now add a Web method to your Web service that retrieves the data and serializes it as XML back to the caller like this:

public String GetData(string strDate) {   DateTime paramDate = Convert.ToDateTime(strDate);   SalesTableAdapters.SalesOrderDetailTableAdapter da =      new SalesTableAdapters.SalesOrderDetailTableAdapter();   Sales.SalesOrderDetailDataTable dt = da.GetData(paramDate);   return ConvertDataTableToXML(dt);}

The GetData method accepts a string date parameter, casts it to a DateTime object and then uses the object in the call to the data adapter, which in turn sets up the correct value in the SQL call to the database.

GetData calls a ConvertDataTableToXML helper function that serializes the contents of the returned DataTable into an XML string. I find this very useful when passing data tables across Web services because it eases many of the interoperability burdens you can face when using complex data types. On the other end, the client deserializes the XML string into an XML document using a similar helper function. Here’s the code for the serializer helper function:

// Function to convert passed dataset to xml dataprivate string ConvertDataTableToXML(DataTable xmlDS){   MemoryStream stream = null;   XmlTextWriter writer = null;   try   {      stream = new MemoryStream();      // Load the XmlTextReader from the stream      writer = new XmlTextWriter(stream, Encoding.Unicode);      // Write to the file with the WriteXml method.      xmlDS.WriteXml(writer);      int count = (int)stream.Length;      byte[] arr = new byte[count];      stream.Seek(0, SeekOrigin.Begin);      stream.Read(arr, 0, count);      UnicodeEncoding utf = new UnicodeEncoding();      return utf.GetString(arr).Trim();   }   catch   {      return String.Empty;   }   finally   {      if (writer != null) writer.Close();   }}

Run the Web service and take note of the WSDL URI. You’ll use that when building your Word application.

Building the Active Document
Within Visual Studio.NET you can create a new Word Document containing C# code behind using the File ?> New Project dialog, selecting ‘Office’ as the application type and selecting ‘Word Document’ as the template as in Figure 2.

Figure 2. Creating a New Word Document Project: Select Office as the application type and Word Document as the template.

Author’s Note: Make sure that you have no Office documents open when you do create the new project, because if you do, you’ll get an alert asking you to activate scripting?and if you have any open Office documents at that time activation will not succeed.

The newly created project contains your document (for example WordDocument3.doc) and an associated code-behind file called ThisDocument.cs. You can effectively treat the document as a Windows Forms application at this point. Your event handlers and other code will go into ThisDocument.cs. Interestingly, you can also treat Visual Studio.NET as a word processor, because this project type incorporates the Word toolbar at the top of the document; in other words, you can type and lay out the document’s text as if you were using Word directly. Figure 3 shows the document within the Visual Studio editor.

The chart in this document was done in the traditional way; I imported the data into Excel and created a chart, then cut and pasted the image of the chart into the document. You could, as a follow-up exercise, see if you can create a dynamic chart in Word!

With the document open in the editor, you can do more than just type and format text?you can also place controls on it as if it were a Windows form. Go ahead and drag-and-drop a MonthCalendar control and a DataGridView control onto the document. Make the layout similar to Figure 4.

With the controls in place, the problem now becomes a simple case of writing code to handle the users’ interactions with these controls.

Figure 3. Word Processing Inside Visual Studio: Microsoft Word project types let you type and edit text into the document in Visual Studio just as if you were using Word directly.

Before writing the code, don’t forget to add a Web reference to the Web service you created earlier. In this example, the reference (and thus the proxy class that represents it) is called localhost.

When the user selects a day from the calendar, you’ll need to supply a DateChanged event handler for the CalendarMonthView control that calls the Web service and gets the detailed sales list for the selected day. Here’s the code:

private void monthCalendar1_DateChanged(   object sender, DateRangeEventArgs e){  string strDate = monthCalendar1.     SelectionStart.ToShortDateString();  localhost.Service mySvc = new localhost.Service();  String st = mySvc.GetData(strDate);  DataSet ds = ConvertXMLToDataSet(st);  DataTable dt = ds.Tables[0];  dataGridView1.DataSource = dt;  dataGridView1.Refresh();            }

The DateChanged event handler pulls the current date out of the calendar using the SelectionStart property, and calls the GetData Web service method, passing the date as a parameter. The service returns a string containing serialized XML data, which the handler converts into a DataSet using the ConvertXMLToDataSet helper function that you’ll see in a moment. Next it retrieves the first DataTable from the DataSet and binds it to the DataGridView. All this is extremely simple and straightforward.

Figure 4. Adding Controls to the Document: The document acts as if it were a Windows Form, letting you can drag and drop controls onto the design surface in the editor.

Here’s the ConvertXMLToDataSet helper function that deserializes the XML string back into a DataSet:

public DataSet ConvertXMLToDataSet(string xmlData){   StringReader stream = null;   XmlTextReader reader = null;   try   {      DataSet xmlDS = new DataSet();      stream = new StringReader(xmlData);      // Load the XmlTextReader from the stream      reader = new XmlTextReader(stream);      xmlDS.ReadXml(reader);      return xmlDS;   }   catch (Exception ex)   {      string strTest = ex.Message;      return null;   }   finally   {      if (reader != null)         reader.Close();   }}

The result is that when you launch the document, you can view the sales information as a summary by reading the graph, and by selecting dates on the calendar, you can drill down to specific days to see the sales information for that day. The beauty of this application is that it surfaces information that was previously unavailable from within an old-style, paper-oriented “passive” document.

The ubiquity of Office makes it an enormously powerful platform on which you can deliver new applications that add value to your business information and business logic. In this article you saw how to use Microsoft Word to provide active, rich documents to your clients, shortening their workflow and giving them new ways to interact with your data. There isn’t anything drastically new about this?it’s just a new way at looking at how people work. As the majority of us use Office on a daily basis there is a vast realm of possibilities for developers to innovate and make use of the platform in new and interesting ways. This article detailed one simple, yet powerful, use case. If you want to give your company, or yourself, an edge over the competition, the opportunity is there?go out, have fun and start innovating with Office’s rich, powerful application platform!

devx-admin

Share the Post: