Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Important This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here.

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.

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 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.

Community Additions

ADD
Show:
© 2015 Microsoft