Dela via


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

Concepts

Accessing Data in Documents on the Server

DiffGrams (ADO.NET)