Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Activate Your Word Documents with VSTO

Word processors were originally designed to be the staging area for printed documents, but years ago people began to read and distribute documents online. So why, then, do we still have flat documents burdened by the legacy of the printer? Find out how VSTO can change all of that.




Application Security Testing: An Integral Part of DevOps

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 TotalSales FROM Sales.SalesOrderHeader INNER JOIN Sales.SalesOrderDetail ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID GROUP BY Sales.SalesOrderHeader.SalesOrderID, Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.CustomerID, Sales.SalesOrderHeader.SalesPersonID HAVING (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 data private 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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date