Imports System.Data
Imports Microsoft.Office.Interop.Excel
Imports System.IO
Imports Microsoft.VisualStudio.Tools.Applications.Runtime


Partial Class _Default
  Inherits System.Web.UI.Page

  Protected Sub btnBuildReport_Click(ByVal sender As Object, ByVal e As System.EventArgs)
    Handles btnBuildReport.Click

    '//setup connection to the Northwind database and open it
    Dim ds As New System.Data.DataSet
    Dim dt As System.Data.DataTable = ds.Tables.Add("SalesReport")
    Dim da As New OleDb.OleDbDataAdapter
    Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
      Data Source= C:\Inetpub\website\Northwind.mdb; User Id=admin; Password=")

    da.SelectCommand = New OleDb.OleDbCommand("SELECT TOP 200 Employees.Country,
      Employees.LastName, Employees.FirstName, Orders.OrderDate, Orders.OrderID,
   [Order Subtotals].Subtotal AS SaleAmount FROM Employees 
   INNER JOIN (Orders INNER JOIN [Order Subtotals] 
   ON Orders.OrderID=[Order Subtotals].OrderID) 
   ON Employees.EmployeeID=Orders.EmployeeID;")
    da.SelectCommand.Connection = cnn
    da.Fill(dt)

    '//Set the location of the SalesReport.xls template
    Dim master As String = "C:\Inetpub\website\SalesReport.xls"
    
    '//Open the Sales Report Excel File (ExcelWorkbook01.xls)
    Dim wb As New FileStream(master, FileMode.Open, FileAccess.Read)
    Dim rdr As New BinaryReader(wb)
    Dim newFile(CInt(wb.Length)) As Byte
    newFile = rdr.ReadBytes(CInt(wb.Length))
    wb.Close()

    '//Open the XL Sales Report ServerDocument, 
    '//fill it with updated data and return it
    Dim sd As New ServerDocument(newFile, ".XLS")
    '//Set a reference to the data cache items contained in Sheet1
    Dim cdhi As CachedDataHostItem = sd.CachedData.HostItems("SalesReport.Sheet1")

    '//Set report's creation date as today
    Dim reportDate As String = Format(Today(), "short date")

    '//Insert the report data and data set values into the Workbooks data cache
    cdhi.CachedData("reportDate").SerializeDataInstance(reportDate.ToString)
    cdhi.CachedData("salesData").SerializeDataInstance(ds)

    '//Save all changes made to the cached data in the XLS file
    sd.Save()
    '//Update the bytes with the new data and close the XLS template
    newFile = sd.Document
    sd.Close()

    '//Build the response object and serve up the file in the browser
    With Response
      .AppendHeader("Content-disposition", "filename=Report.xls")
      .ContentType = "application/vnd.ms-excel"
      .OutputStream.Write(newFile, 0, newFile.Length)
      .Flush()
      .Close()
    End With
  End Sub
End Class

The button's click event retrieves sales data from the Northwind database and uses it to fill a data set object. With this data in memory, the code then opens the SalesReport.xls file and ready its contents into a byte object. This is done so that the original file is not modified and thus remains blank for the next report generation.

To access the cached data within the Excel workbook, a ServerDocument must be created. The code creates a ServerDocument instance and fills it with the NewFile byte object containing the workbook template. Each cached data item can be accessed via the HostItems collection (which is a child of the CachedData object). The HostItems collection contains all CachedDataHostItem objects stored within the data cache.

Using these objects, the code inserts the report's creation data (reportDate) as well as the retrieved dataset (ds). The code saves the changes to the ServerDocument object and then updates the NewFile byte object to reflect the new version of the SalesReport.xls workbook.

Finally, the code builds out the response object by specify the content type and headers and then sends the workbook to the browser.

Execute the Web page, and click the button to generate the report. The final result is a freshly generated Excel file, containing cached data from the Northwind database.

Summary

Office documents are utilized everyday by the majority of business users and are an ideal client for document-based applications. With the release of Visual Studio 2005 Tools for Office, document-based applications can be extended to include offline capabilities.

In addition, external applications can now use Office data on the server without the need to interact with the Office application object models. The capabilities provided by VSTO greatly extend the reach of Office-based applications in the enterprise at a time when Smart Clients are gaining in popularity.

Ty Anderson runs Cogent Company, a consultancy in Dallas specializing in developing Information Worker solutions with the Microsoft Office System. Ty writes frequently about professional development with the Microsoft Office Platform and has recently written a book, Office Programming 2003: Real World Applications, focused entirely on building business applications with the Microsoft Office System 2003.