How to: Insert Data into a Workbook on a Server

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type Document-level projects Microsoft Office version Excel 2007 Excel 2003 For more information, see Features Available by Application and Project Type.

You can insert data into the cache of a Microsoft Office Excel workbook that is part of a document-level Visual Studio Tools for Office project without running Excel. This makes it possible to insert data into Excel workbooks that are stored on a server.

The code to insert the data must be outside of the main Visual Studio Tools for Office 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 in a Console application that has access to a Class Library project that defines a typed dataset, and an Excel workbook that is part of a document-level customization for Excel 2003 or Excel 2007. 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

Concepts

Accessing Data in Documents on the Server

DiffGrams (ADO.NET)