How to: Get an Entire Workbook or a Snapshot

Last modified: March 24, 2010

Applies to: SharePoint Server 2010

This example shows 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 by using Excel Web Services. Getting the workbook or a snapshot is useful if you want to save a copy of the up-to-date workbook, store it somewhere, send it to someone, and so on.

A snapshot is a workbook generated by Excel Calculation Services, and it represents the current state of the workbook in the Excel Services session. 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. Excel Services opens the Excel file on the server, refreshes data sources, and calculates all Excel formulas. When a user or application requests a snapshot, Excel Services then generates and sends a snapshot back through the Web service API.

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.

You use the Web service's GetWorkbook method to get either the entire workbook or one of the snapshot types. For example, 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.

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

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

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:

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

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

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

The WorkbookType.FullWorkbook option works only if the user has open rights to the file; if the user has view-only rights, the call will fail.

In some cases, your code would need to save the result of a GetWorkbook call. For a discussion about how to save a workbook, see the How to: Save a Workbook example.

For more information about the GetWorkbook method and the WorkbookType enumeration, see the Excel Web Services reference documentation.

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.myServer02 statement 
// to point to the Web service you are referencing.
using GetSnapshot.myServer02;

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

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.

NoteNote

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

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

Community Additions

ADD
Show:
© 2014 Microsoft