Share via

Retrieving an Entire Workbook or a Snapshot of a Workbook in Excel Services

  • Article

Summary:  Learn how to retrieve an entire workbook or a snapshot of a workbook by using Excel Services.

Office Visual How To

Applies to:  Microsoft Office SharePoint Server 2007, Microsoft Office Excel 2007

Siew Moi Khor, Danny Khen, Microsoft Corporation

Joel Krist, Akona Systems

April 2007

Overview

If you want to save a copy of an up-to-date workbook, store it somewhere, send it to someone, and so on, you can retrieve an entire workbook or a snapshot using Excel Web Services. When a user or application requests a snapshot, Excel Services opens the Excel file on the server, refreshes data sources, and calculates all Excel formulas. Excel Services then generates and sends a snapshot back through the Web service API.

Code It

Download the Code Sample

This example illustrates how to get an entire workbook, a snapshot of the entire file, or just a snapshot of the viewable sheets or objects in the file using Excel Web Services.

Use the GetWorkbook method in Excel Web Services to get either the entire workbook or one of the snapshot types.

Retrieving a Snapshot

The following code returns a snapshot of the entire Excel workbook. It uses the WorkbookType.FullSnapshot enumeration as the second argument in the GetWorkbook method.

The GetWorkbook method returns a byte array, in the same Excel file format as the one loaded into the session.

Retrieving a Snapshot of Viewable Items

To get a snapshot of the items that the Excel workbook author selected as viewable when saving the workbook from Excel to the server, use the WorkbookType.PublishedItemsSnapshot enumeration as shown here:

Retrieving an Entire Workbook

To get a snapshot of the entire workbook in its current session state, use the WorkbookType.FullWorkbook enumeration.

The WorkbookType.FullWorkbook and WorkbookType.FullSnapshot options work only if the user has open rights to the file; if the user has view-only rights, the call fails.

For more information about the GetWorkbook method and the WorkbookType enumeration, see the Microsoft.Office.Excel.Server.WebServices reference documentation.

Complete Code

The following program (a console application) receives one command-line argument, which is the path to the workbook on the server. The program calls the Web service to open the workbook on the server and get a snapshot. It then writes it to standard output so that you can redirect it to a new snapshot file.

using System;
using System.IO;
using System.Text;
using System.Web.Services.Protocols;

// TODO: Change the using GetSnapshot.ExcelWebService; directive 
// to point to the Web service you are referencing.
using 
// GetSnapshot.ExcelWebService;

namespace GetSnapshot
{
    class ExcelServicesSnapshot
    {
        static void Main(string[] args)
        {
            try
            {
                if (args.Length < 1)
                {
                    Console.Error.WriteLine("Command line arguments should be: 
                    GetSnapshot [workbook_path] > [snapshot_filename]");
                    return;
                }
                // Instantiate the Web service and 
                // create a status array object.
                ExcelService xlService = new ExcelService();
                Status[] status;
                
                xlService.Timeout = 600000;
                // Set credentials for requests.
                // Use the current user's logon credentials.
                xlService.Credentials =   
                System.Net.CredentialCache.DefaultCredentials;
                 
                // Open the workbook, then call GetWorkbook 
                // and close the session.
                string sessionId = xlService.OpenWorkbook(args[0], 
                "en-US", "en-US", out status);
                byte[] workbook = xlService.GetWorkbook
                (sessionId, WorkbookType.PublishedItemsSnapshot, 
                 out status);

                // byte[] workbook = xlService.GetWorkbook(sessionId, 
                // WorkbookType.FullWorkbook, out status);
                // byte[] workbook = xlService.GetWorkbook(sessionId, 
                // WorkbookType.FullSnapshot, out status);

                // Close the workbook. This also closes the session,     
                // proactively releasing resources on the server. 
                status = xlService.CloseWorkbook(sessionId);

                // Write the resulting Excel file to stdout 
                // as a binary stream.
                BinaryWriter binaryWriter = new BinaryWriter
                (Console.OpenStandardOutput());
                binaryWriter.Write(workbook);
                binaryWriter.Close();
            }
            catch (SoapException e)
            {
                Console.WriteLine("SOAP Exception Message: {0}", e.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception Message: {0}", e.Message);
            }   
        } 
    }
}

Running the application

Use the following command line and arguments to run the GetSnapshot application:

GetSnapshot.exe [workbook_path] > [snapshot_filename]

For example:

C:\>GetSnapshot.exe 
http://myServer02/reports/reports/OriginalWorkbook.xlsx > SnapshotCopy.xlsx

If you use the previous command-line example, the GetSnapshot tool places a new file in the "C:\" directory.

Note

The workbook that you want to get a snapshot of must be in a trusted location.

Robust Programming

Make sure you add a Web reference to an Excel Web Services site you have access to. Change the using GetSnapshot.ExcelWebService; directive to point to the Web service site you are referencing.

Read It

A full workbook represents the current state of the workbook in the Excel Services session. Excel Calculation Services generates the full workbook based on the workbook that the session opened. It reflects all the modifications that have happened to this original workbook during the session: values that went into parameters, external data connections that were refreshed, and formulas that were calculated based on parameter and external data values. It is identical to what a user would get by selecting the Open in Excel command in Excel Web Access.

A snapshot is a workbook generated by Excel Calculation Services, and it represents a "report" or a "result" of the current state; it contains everything you can see in the browser using Excel Web Access (and nothing else), but it is delivered in a workbook file format.

Some snapshots (known as "published item snapshots") contain only those portions of the Excel file that an author selects as viewable when saving the file to the server. Snapshots contain the layout and formats of the original file, and up-to-date values calculated by Excel Calculation Services, but they do not contain Excel formulas or external data connections.

You can acquire a snapshot of a workbook you already saved to the server, even if you do not have the rights to access the actual file on the server.

In some cases, your code needs to save the result of a GetWorkbook call. Excel Web Services is a front-end interface to Excel Services. In general, Excel Services does not create, modify, or save files. It only reads them to start sessions. Therefore, Excel Web Services does not have a method to save a workbook.

To save a workbook, you generally use the API of the destination file store. For example:

  • If you want to save the Excel file to a Microsoft Windows SharePoint Services 3.0 document library, use the Windows SharePoint Services 3.0 object model or Web service.

  • If you want to save the Excel file to a stream, a local file, or a file share, use the Microsoft .NET Framework libraries.

  • If you want to save the Excel file to an HTTP location, use Web Distributed Authoring and Versioning (WebDAV).

Note

WebDAV is also known as the HTTP/DAV protocol or the DAV protocol.

You can use the various Microsoft .NET Framework libraries to save a workbook. The following example shows one of the methods. For a discussion about how to save a workbook, see the How to: Save a Workbook example.

 See It Presentation cover slide

Watch the Video

Length: 10:40 | Size: 12.2 MB | Type: WMV file

Explore It