HOW TO：變更伺服器活頁簿中的快取資料

發行項
08/12/2011

您不需要執行 Excel，即可變更屬於文件層級專案一部分之 Microsoft Office Excel 活頁簿快取中的資料。如此可以讓您變更儲存於伺服器之 Excel 活頁簿中的資料。

**適用於：**本主題中的資訊適用於 Excel 2007 和 Excel 2010 的文件層級專案。如需詳細資訊，請參閱依 Office 應用程式和專案類型提供的功能。

用來變更資料的程式碼必須位於與使用中文件關聯的主要專案組件之外，例如在 ASP.NET 網頁、主控台應用程式或 Windows Form 應用程式中。

如需如何使用本主題程式碼範例的逐步指示，請參閱逐步解說：變更伺服器上的活頁簿快取資料。

範例

下列程式碼範例會先建立名為 AdventureWorksLTDataSet 的具型別資料集執行個體。接著，程式碼會使用 ServerDocument 類別，存取 Excel 活頁簿中已快取之相同具型別資料集的已填滿執行個體，然後將資料從快取資料集讀入本機資料集。最後，程式碼會修改本機資料集中每列的 ListPrice 值，然後使用 Xml 屬性將變更的資料寫回快取的資料集。

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

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

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