How to: Get an Entire Workbook or a Snapshot
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);
Dim workbook() As Byte = xlService.GetWorkbook(sessionId, WorkbookType.FullSnapshot, 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);
Dim workbook() As Byte = xlService.GetWorkbook(sessionId, WorkbookType.FullSnapshot, 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);
Dim workbook() As Byte = xlService.GetWorkbook(sessionId, WorkbookType.FullWorkbook, 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.
Example
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);
}
}
}
}
Imports System
Imports System.IO
Imports System.Text
Imports System.Web.Services.Protocols
' TODO: Change the using GetSnapshot.myServer02 statement
' to point to the Web service you are referencing.
Imports GetSnapshot.myServer02
Namespace GetSnapshot
Friend Class ExcelServicesSnapshot
Shared Sub Main(ByVal args() As String)
Try
If args.Length < 1 Then
Console.Error.WriteLine("Command line arguments should be: GetSnapshot [workbook_path] > [snapshot_filename]")
Return
End If
' Instantiate the Web service and
' create a status array object.
Dim xlService As New ExcelService()
Dim status() As 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.
Dim sessionId As String = xlService.OpenWorkbook(args(0), "en-US", "en-US", status)
Dim workbook() As Byte = xlService.GetWorkbook(sessionId, WorkbookType.PublishedItemsSnapshot, 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.
Dim binaryWriter As New BinaryWriter(Console.OpenStandardOutput())
binaryWriter.Write(workbook)
binaryWriter.Close()
Catch e As SoapException
Console.WriteLine("SOAP Exception Message: {0}", e.Message)
Catch e As Exception
Console.WriteLine("Exception Message: {0}", e.Message)
End Try
End Sub
End Class
End Namespace
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.myServer02; statement to point to the Web service site you are referencing.
See Also
Tasks
Step 1: Creating the Web Service Client Project
Step 2: Adding a Web Reference
Step 3: Accessing the Web Service
Step 4: Building and Testing the Application
Walkthrough: Developing a Custom Application Using Excel Web Services