How to: Change Cached Data in a Workbook on a Server

You can change data in the cache of a Microsoft Office Excel workbook that is part of a document-level project without running Excel. This makes it possible to change data in Excel workbooks that are stored 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.

The code to change the data must be outside of the main project assembly that is associated with the document you are working with, for example in an ASP.NET Web page, a console application, or a Windows Forms application.

For step-by-step instructions for using the code example in this topic, see Walkthrough: Changing Cached Data in a Workbook on a Server.

The following code example first creates an instance of a typed dataset named AdventureWorksLTDataSet. Next, the code uses the ServerDocument class to access a filled instance of the same typed dataset that is already cached in an Excel workbook, and then reads the data from the cached dataset into the local dataset. Finally, the code modifies the ListPrice value in each row of the local dataset, and then writes the changed data back into the cached dataset by using the Xml property.


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

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();
}


The code example in this topic is designed to be used with the following applications:

  • A console application that has access to a class library project that defines a typed dataset. The code runs in the console application.

  • An Excel workbook that is part of a document-level customization for Excel. The workbook has a cached dataset named AdventureWorksLTDataSet that contains some data.

For step-by-step instructions for using the code, see Walkthrough: Changing Cached Data in a Workbook on a Server.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft