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.

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.


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


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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft