如何：更改服务器上的工作簿中的缓存数据
更新：2007 年 11 月
适用对象 |
|---|
本主题中的信息仅适用于指定的 Visual Studio Tools for Office 项目和 Microsoft Office 版本。 项目类型
Microsoft Office 版本
有关更多信息，请参见按应用程序和项目类型提供的功能。 |
不用运行 Excel，就可以更改属于文档级 Visual Studio Tools for Office 项目的 Microsoft Office Excel 工作簿的缓存中的数据。这样就可以更改存储在服务器上的 Excel 工作簿中的数据。
用来更改数据的代码必须位于与您正在使用的文档关联的主 Visual Studio Tools for Office 项目程序集外部，例如，位于 ASP.NET 网页、控制台应用程序或 Windows 窗体应用程序中。
有关使用本主题中的代码示例的分步说明，请参见演练：更改服务器上的工作簿中的缓存数据。
示例
下面的代码示例首先会创建类型化数据集 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();
}
编译代码
本主题中的代码示例应在有权访问定义类型化数据集的类库项目的控制台应用程序，以及作为 Excel 2003 或 Excel 2007 的文档级自定义项一部分的 Excel 工作簿中使用。有关使用此代码的分步说明，请参见演练：更改服务器上的工作簿中的缓存数据。