Walkthrough: Changing Cached Data in a Workbook on a Server

Article
02/04/2013

This walkthrough demonstrates how to modify a dataset that is cached in a Microsoft Office Excel workbook without starting Excel by using the ServerDocument class. This walkthrough provides step-by-step instructions for using the code examples that are provided in How to: Change Cached Data in a Workbook on a Server.

Applies to: The information in this topic applies to document-level projects for Excel 2007 and Excel 2010. For more information, see Features Available by Office Application and Project Type.

This walkthrough illustrates the following tasks:

Defining a dataset that contains data from the AdventureWorksLT database.
Creating instances of the dataset in an Excel workbook project and a console application project.
Creating a ListObject that is bound to the dataset in the workbook, and populating the ListObject with data when the workbook is opened.
Adding the dataset in the workbook to the data cache.
Modifying a column of data in the cached dataset by running code in the console application, without starting Excel.

Although this walkthrough assumes that you are running the code on your development computer, the code demonstrated by this walkthrough can be used on a server that does not have Excel installed.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Working with Settings.

Prerequisites

You need the following components to complete this walkthrough:

An edition of Visual Studio 2010 that includes the Microsoft Office developer tools. For more information, see [Configuring a Computer to Develop Office Solutions](bb398242\(v=vs.100\).md).

Excel 2007 or Excel 2010.
Access to a running instance of Microsoft SQL Server or Microsoft SQL Server Express that has the AdventureWorksLT sample database attached to it. You can download the AdventureWorksLT database from the CodePlex Web site. For more information about attaching a database, see the following topics:
- To attach a database by using SQL Server Management Studio or SQL Server Management Studio Express, see How to: Attach a Database (SQL Server Management Studio).
- To attach a database by using the command line, see How to: Attach a Database File to SQL Server Express.

Creating a Class Library Project That Defines a Dataset

To use the same dataset in an Excel workbook project and a console application, you must define the dataset in a separate assembly that is referenced by both of these projects. For this walkthrough, define the dataset in a class library project.

To create the class library project

Start Visual Studio.
On the File menu, point to New, and then click Project.
In the templates pane, expand Visual C# or Visual Basic, and then click Windows.
In the list of project templates, select Class Library.
In the Name box, type AdventureWorksDataSet.
Click Browse, navigate to your %UserProfile%\My Documents (for Windows XP and earlier) or %UserProfile%\Documents (for Windows Vista) folder, and then click Select Folder.
In the New Project dialog box, ensure that the Create directory for solution check box is not selected.
Click OK.

Visual Studio adds the AdventureWorksDataSet project to Solution Explorer and opens the Class1.cs or Class1.vb code file.
In Solution Explorer, right-click Class1.cs or Class1.vb, and then click Delete. You do not need this file for this walkthrough.

Defining a Dataset in the Class Library Project

Define a typed dataset that contains data from the AdventureWorksLT database for SQL Server 2005. Later in this walkthrough, you will reference this dataset from an Excel workbook project and a console application project.

The dataset is a typed dataset that represents the data in the Product table of the AdventureWorksLT database. For more information about typed datasets, see Working with Datasets in Visual Studio.

To define a typed dataset in the class library project

In Solution Explorer, click the AdventureWorksDataSet project.
On the Data menu, click Add New Data Source.

The Data Source Configuration Wizard opens.
Click Database, and then click Next.
If you have an existing connection to the AdventureWorksLT database, choose this connection and click Next.

Otherwise, click New Connection, and use the Add Connection dialog box to create the new connection. For more information, see How to: Connect to Data in a Database.
In the Save the Connection String to the Application Configuration File page, click Next.
In the Choose Your Database Objects page, expand Tables and select Product (SalesLT).
Click Finish.

The AdventureWorksLTDataSet.xsd file is added to the AdventureWorksDataSet project. This file defines the following items:
- A typed dataset named AdventureWorksLTDataSet. This dataset represents the contents of the Product table in the AdventureWorksLT database.
- A TableAdapter named ProductTableAdapter. This TableAdapter can be used to read and write data in the AdventureWorksLTDataSet. For more information, see TableAdapter Overview.
You will use both of these objects later in this walkthrough.
In Solution Explorer, right-click AdventureWorksDataSet and click Build.

Verify that the project builds without errors.

Creating an Excel Workbook Project

Create an Excel workbook project for the interface to the data. Later in this walkthrough, you will create a ListObject that displays the data, and you will add an instance of the dataset to the data cache in the workbook.

To create the Excel workbook project

In Solution Explorer, right-click the AdventureWorksDataSet solution, point to Add, and then click New Project.
In the templates pane, expand Visual C# or Visual Basic, and then expand Office.
Under the expanded Office node, select the 2007 or 2010 node.
In the list of project templates, select the Excel Workbook project.
In the Name box, type AdventureWorksReport. Do not modify the location.
Click OK.

The Visual Studio Tools for Office Project Wizard opens.
Ensure that Create a new document is selected, and click OK.

Visual Studio opens the AdventureWorksReport workbook in the designer and adds the AdventureWorksReport project to Solution Explorer.

Adding the Dataset to Data Sources in the Excel Workbook Project

Before you can display the dataset in the Excel workbook, you must first add the dataset to data sources in the Excel workbook project.

To add the dataset to the data sources in the Excel workbook project

In Solution Explorer, double-click Sheet1.cs or Sheet1.vb under the AdventureWorksReport project.

The workbook opens in the designer.
On the Data menu, click Add New Data Source.

The Data Source Configuration Wizard opens.
Click Object, and then click Next.
In the Select the Object You Wish to Bind to page, click Add Reference.
On the Projects tab, click AdventureWorksDataSet and then click OK.
Under the AdventureWorksDataSet namespace of the AdventureWorksDataSet assembly, click AdventureWorksLTDataSet and then click Finish.

The Data Sources window opens, and AdventureWorksLTDataSet is added to the list of data sources.

Creating a ListObject That Is Bound to an Instance of the Dataset

To display the dataset in the workbook, create a ListObject that is bound to an instance of the dataset. For more information about binding controls to data, see Binding Data to Controls in Office Solutions.

To create a ListObject that is bound to an instance of the dataset

In the Data Sources window, expand the AdventureWorksLTDataSet node under AdventureWorksDataSet.
Select the Product node, click the drop-down arrow that appears, and select ListObject in the drop-down list.

If the drop-down arrow does not appear, confirm that the workbook is open in the designer.
Drag the Product table to cell A1.

A ListObject control named productListObject is created on the worksheet, starting in cell A1. At the same time, a dataset object named adventureWorksLTDataSet and a BindingSource named productBindingSource are added to the project. The ListObject is bound to the BindingSource, which in turn is bound to the dataset object.

Adding the Dataset to the Data Cache

To enable code outside the Excel workbook project to access the dataset in the workbook, you must add the dataset to the data cache. For more information about the data cache, see Cached Data in Document-Level Customizations and Caching Data.

To add the dataset to the data cache

In the designer, click adventureWorksLTDataSet.
In the Properties window, set the Modifiers property to Public.
Set the CacheInDocument property to True.

Initializing the Dataset in the Workbook

Before you can retrieve the data from the cached dataset by using the console application, you must first populate the cached dataset with data.

To initialize the dataset in the workbook

In Solution Explorer, right-click the Sheet1.cs or Sheet1.vb file and click View Code.

Replace the Sheet1_Startup event handler with the following code. This code uses an instance of the ProductTableAdapter class that is defined in the AdventureWorksDataSet project to fill the cached dataset with data, if it is currently empty.

Private ProductTableAdapter As New  _
    AdventureWorksDataSet.AdventureWorksLTDataSetTableAdapters.ProductTableAdapter()

Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
    If Me.NeedsFill("AdventureWorksLTDataSet") Then
        Me.ProductTableAdapter.Fill(Me.AdventureWorksLTDataSet.Product)
    End If
End Sub

private AdventureWorksDataSet.AdventureWorksLTDataSetTableAdapters.ProductTableAdapter productTableAdapter =
    new AdventureWorksDataSet.AdventureWorksLTDataSetTableAdapters.ProductTableAdapter();

private void Sheet1_Startup(object sender, System.EventArgs e)
{
    if (this.NeedsFill("adventureWorksLTDataSet"))
    {
        this.productTableAdapter.Fill(this.adventureWorksLTDataSet.Product);
    }
}

Checkpoint

Build and run the Excel workbook project to ensure that it compiles and runs without errors. This operation also fills the cached dataset and saves the data in the workbook.

To build and run the project

In Solution Explorer, right-click the AdventureWorksReport project, choose Debug, and then click Start new instance.

The project is built, and the workbook opens in Excel. Verify the following:
- The ListObject fills with data.
- The value in the ListPrice column for the first row of the ListObject is 1431.5. Later in this walkthrough, you will use a console application to modify the values in the ListPrice column.
Save the workbook. Do not modify the file name or the location of the workbook.
Close Excel.

Creating a Console Application Project

Create a console application project to use to modify data in the cached dataset in the workbook.

To create the console application project

In Solution Explorer, right-click the AdventureWorksDataSet solution, point to Add, and then click New Project.
In the Project Types pane, expand Visual C# or Visual Basic, and then click Windows.
In the Templates pane, select Console Application.
In the Name box, type DataWriter. Do not modify the location.
Click OK.

Visual Studio adds the DataWriter project to Solution Explorer and opens the Program.cs or Module1.vb code file.

Changing Data in the Cached Dataset by Using the Console Application

Use the ServerDocument class in the console application to read the data into a local AdventureWorksLTDataSet object, modify this data, and then save it back to the cached dataset.

To change data in the cached dataset

In Solution Explorer, right-click the DataWriter project and click Add Reference.
On the .NET tab, select Microsoft.VisualStudio.Tools.Applications.ServerDocument (if the DataWriter project targets the .NET Framework 4) or Microsoft.VisualStudio.Tools.Applications.ServerDocument.v10.0 (if the DataWriter project targets the .NET Framework 3.5).
Click OK.
In Solution Explorer, right-click the DataWriter project and click Add Reference.
On the Projects tab, select AdventureWorksDataSet, and click OK.
Open the Program.cs or Module1.vb file in the Code Editor.

Add the following using (for C#) or Imports (for Visual Basic) statement to the top of the code file.

Imports Microsoft.VisualStudio.Tools.Applications

using Microsoft.VisualStudio.Tools.Applications;

Add the following code to the Main method. This code declares the following objects:

An instance of the AdventureWorksLTDataSet type that is defined in the AdventureWorksDataSet project.
The path to the AdventureWorksReport workbook in the build folder of the AdventureWorksReport project.
A ServerDocument object to use to access the data cache in the workbook.

Note

The following code assumes that you are using a workbook that has the .xlsx file extension. If the workbook in your project has a different file extension, modify the path as necessary.

Dim productDataSet As New AdventureWorksDataSet.AdventureWorksLTDataSet()
Dim workbookPath As String = System.Environment.GetFolderPath( _
    Environment.SpecialFolder.MyDocuments) & _
    "\AdventureWorksReport\bin\Debug\AdventureWorksReport.xlsx"
Dim serverDocument1 As ServerDocument = Nothing

AdventureWorksDataSet.AdventureWorksLTDataSet productDataSet =
    new AdventureWorksDataSet.AdventureWorksLTDataSet();
string workbookPath = System.Environment.GetFolderPath(
    Environment.SpecialFolder.MyDocuments) +
    @"\AdventureWorksReport\bin\Debug\AdventureWorksReport.xlsx";
ServerDocument serverDocument1 = null;

Add the following code to the Main method, after the code you added in the previous step. This code performs the following tasks:

It uses the CachedData property of the ServerDocument class to access the cached dataset in the workbook.
It reads the data from the cached dataset into the local dataset.
It changes the ListPrice value of each product in the Product table of the dataset.
It saves the changes to the cached dataset in the workbook.

Try
    serverDocument1 = New ServerDocument(workbookPath)
    Dim dataHostItem1 As CachedDataHostItem = _
        serverDocument1.CachedData.HostItems("AdventureWorksReport.Sheet1")
    Dim dataItem1 As CachedDataItem = dataHostItem1.CachedData("AdventureWorksLTDataSet")

    If dataItem1 IsNot Nothing Then
        Console.WriteLine("Before reading data from the cache dataset, the local dataset has " & _
            "{0} rows.", productDataSet.Product.Rows.Count.ToString())

        ' Read the cached data from the worksheet dataset into the local dataset.
        Dim schemaReader As New System.IO.StringReader(dataItem1.Schema)
        Dim xmlReader As New System.IO.StringReader(dataItem1.Xml)
        productDataSet.ReadXmlSchema(schemaReader)
        productDataSet.ReadXml(xmlReader)

        Console.WriteLine("After reading data from the cache dataset, the local dataset has " & _
            "{0} rows.", productDataSet.Product.Rows.Count.ToString())

        ' Modify the prices of each product in the local dataset.
        Dim row As AdventureWorksDataSet.AdventureWorksLTDataSet.ProductRow
        For Each row In productDataSet.Product.Rows
            If row.ProductCategoryID < 20 Then
                row.ListPrice = row.ListPrice + row.ListPrice * 0.1
            Else
                row.ListPrice = row.ListPrice - row.ListPrice * 0.1
            End If
        Next row

        ' Write the modified local dataset to the worksheet dataset using the DiffGram format.
        Dim stringIn As New System.Text.StringBuilder()
        Dim stringOut As New System.IO.StringWriter(stringIn)
        productDataSet.WriteXml(stringOut, System.Data.XmlWriteMode.DiffGram)
        dataItem1.Xml = stringIn.ToString()

        serverDocument1.Save()
        Console.WriteLine("The product prices have been modified.")
    Else
        Console.WriteLine("The data object is not found in the data cache.")
    End If
Catch ex As System.IO.FileNotFoundException
    Console.WriteLine("The specified workbook does not exist.")
Catch ex As System.Xml.XmlException
    Console.WriteLine("The data object has invalid XML information.")
Finally
    If Not (serverDocument1 Is Nothing) Then
        serverDocument1.Close()
    End If
    Console.WriteLine(vbLf & vbLf & "Press Enter to close the application.")
    Console.ReadLine()
End Try

try
{
    serverDocument1 = new ServerDocument(workbookPath);
    CachedDataHostItem dataHostItem1 =
        serverDocument1.CachedData.HostItems["AdventureWorksReport.Sheet1"];
    CachedDataItem dataItem1 = dataHostItem1.CachedData["adventureWorksLTDataSet"];

    if (dataItem1 != null)
    {
        Console.WriteLine("Before reading data from the cache dataset, the local dataset has " +
            "{0} rows.", productDataSet.Product.Rows.Count.ToString());

        // Read the cached data from the worksheet dataset into the local dataset.
        System.IO.StringReader schemaReader = new System.IO.StringReader(dataItem1.Schema);
        System.IO.StringReader xmlReader = new System.IO.StringReader(dataItem1.Xml);
        productDataSet.ReadXmlSchema(schemaReader);
        productDataSet.ReadXml(xmlReader);

        Console.WriteLine("After reading data from the cache dataset, the local dataset has " +
            "{0} rows.", productDataSet.Product.Rows.Count.ToString());

        // Modify the prices of each product in the local dataset.
        foreach (AdventureWorksDataSet.AdventureWorksLTDataSet.ProductRow row in 
                 productDataSet.Product.Rows)
        {
            if (row.ProductCategoryID < 20)
            {
                row.ListPrice = row.ListPrice + (row.ListPrice * (Decimal).10);
            }
            else
            {
                row.ListPrice = row.ListPrice - (row.ListPrice * (Decimal).10);
            }
        }

        // Write the modified local dataset to the worksheet dataset using the DiffGram format.
        System.Text.StringBuilder stringIn = new System.Text.StringBuilder();
        System.IO.StringWriter stringOut = new System.IO.StringWriter(stringIn);
        productDataSet.WriteXml(stringOut, System.Data.XmlWriteMode.DiffGram);
        dataItem1.Xml = stringIn.ToString();

        serverDocument1.Save();
        Console.WriteLine("The product prices have been modified.");
    }
    else
    {
        Console.WriteLine("The data object is not found in the data cache.");
    }
}
catch (System.IO.FileNotFoundException)
{
    Console.WriteLine("The specified workbook does not exist.");
}
catch (System.Xml.XmlException)
{
    Console.WriteLine("The data object has invalid XML information.");
}
finally
{
    if (serverDocument1 != null)
    {
        serverDocument1.Close();
    }

    Console.WriteLine("\n\nPress Enter to close the application.");
    Console.ReadLine();
}

In Solution Explorer, right-click the DataWriter project, point to Debug, and then click Start new instance.

The console application displays messages while it reads the cached dataset into the local dataset, modifies the product prices in the local dataset, and saves the new values to the cached dataset. Press ENTER to close the application.

Testing the Workbook

When you open the workbook, the ListObject now displays the changes you made to the ListPrice column of data in the cached dataset.

To test the workbook

Close the AdventureWorksReport workbook in the Visual Studio designer, if it is still open.
Open the AdventureWorksReport workbook that is in the build folder of the AdventureWorksReport project. By default, the build folder is in one of the following locations:
- %UserProfile%\My Documents\AdventureWorksReport\bin\Debug (for Windows XP and earlier)
- %UserProfile%\Documents\AdventureWorksReport\bin\Debug (for Windows Vista)
Verify that the value in the ListPrice column for the first row of the ListObject is now 1574.65.
Close the workbook.

Next Steps

You can learn more about working with cached data from these topics:

Initializing the data in a cached dataset without starting Excel. For more information, see Walkthrough: Inserting Data into a Workbook on a Server.
Inserting data into the data cache of a Word document as an array of bytes in memory, rather than saved to a hard disk. For more information, see How to: Insert Data in Documents Without Writing to Disk.

Share via