How to: Insert Data into a Workbook on a Server
You can insert data into the cache of a Microsoft Office Excel workbook that is part of a document-level Office project without running Excel. This makes it possible to insert data into 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 insert the data must be outside of the project assembly that is associated with the document you are working with, for example in a Console or Windows Forms application.
For step-by-step instructions for using the code example in this topic, see Walkthrough: Inserting Data into a Workbook on a Server.
Example
The following code example first creates an instance of a typed dataset named AdventureWorksLTDataSet and then fills the Product table in the dataset by using a table adapter. Next, the code uses the ServerDocument class to access an instance of the same typed dataset that is cached in an Excel workbook, and then writes the data from the local dataset into the cached dataset by using the SerializeDataInstance method.
Dim productDataSet As New AdventureWorksDataSet.AdventureWorksLTDataSet()
Dim productTableAdapter As _
New AdventureWorksDataSet.AdventureWorksLTDataSetTableAdapters.ProductTableAdapter()
Dim workbookPath As String = System.Environment.GetFolderPath( _
Environment.SpecialFolder.MyDocuments) & _
"\AdventureWorksReport\bin\Debug\AdventureWorksReport.xlsx"
Dim serverDocument1 As ServerDocument = Nothing
Try
productTableAdapter.Fill(productDataSet.Product)
Console.WriteLine("The local dataset is filled.")
serverDocument1 = New ServerDocument(workbookPath)
Dim dataHostItem1 As CachedDataHostItem = _
serverDocument1.CachedData.HostItems("AdventureWorksReport.Sheet1")
Dim dataItem1 As CachedDataItem = dataHostItem1.CachedData("AdventureWorksLTDataSet")
' Initialize the worksheet dataset with the local dataset.
If dataItem1 IsNot Nothing Then
dataItem1.SerializeDataInstance(productDataSet)
serverDocument1.Save()
Console.WriteLine("The data is saved to the data cache.")
Else
Console.WriteLine("The data object is not found in the data cache.")
End If
Catch ex As System.Data.SqlClient.SqlException
Console.WriteLine(ex.Message)
Catch ex As System.IO.FileNotFoundException
Console.WriteLine("The specified workbook does not exist.")
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();
AdventureWorksDataSet.AdventureWorksLTDataSetTableAdapters.ProductTableAdapter productTableAdapter =
new AdventureWorksDataSet.AdventureWorksLTDataSetTableAdapters.ProductTableAdapter();
string workbookPath = System.Environment.GetFolderPath(
Environment.SpecialFolder.MyDocuments) +
@"\AdventureWorksReport\bin\Debug\AdventureWorksReport.xlsx";
ServerDocument serverDocument1 = null;
try
{
productTableAdapter.Fill(productDataSet.Product);
Console.WriteLine("The local dataset is filled.");
serverDocument1 = new ServerDocument(workbookPath);
CachedDataHostItem dataHostItem1 =
serverDocument1.CachedData.HostItems["AdventureWorksReport.Sheet1"];
CachedDataItem dataItem1 = dataHostItem1.CachedData["adventureWorksLTDataSet"];
// Initialize the worksheet dataset with the local dataset.
if (dataItem1 != null)
{
dataItem1.SerializeDataInstance(productDataSet);
serverDocument1.Save();
Console.WriteLine("The data is saved to the data cache.");
Console.ReadLine();
}
else
{
Console.WriteLine("The data object is not found in the data cache.");
}
}
catch (System.Data.SqlClient.SqlException ex)
{
Console.WriteLine(ex.Message);
}
catch (System.IO.FileNotFoundException)
{
Console.WriteLine("The specified workbook does not exist.");
}
finally
{
if (serverDocument1 != null)
{
serverDocument1.Close();
}
Console.WriteLine("\n\nPress Enter to close the application.");
Console.ReadLine();
}
Compiling the Code
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: Inserting Data into a Workbook on a Server.
See Also
Tasks
Walkthrough: Inserting Data into a Workbook on a Server
How to: Insert Data in Documents Without Writing to Disk
How to: Retrieve Cached Data from a Workbook on a Server
How to: Change Cached Data in a Workbook on a Server