C# Guide - C#, Asp.Net, MVC, LINQ, jQuery and SharePoint Resources

Changing cell value of a Published Excel Workbook using Excel Web services

Excel Web Services can be used to update data to Published Excel Work Sheets, in this post is will go through the steps to update data programatically using Excel Web Services, to a Published Excel worksheet.

1. Open the application from which needs to update data to the Published Excel Worksheet using Excel Web Services.
2. I will use an Asp.Net web application to update the data.
3. Add a Web Reference to the Excel Web Services path.

http://<SiteName>/_vti_bin/excelservice.asmx

In my Server the path is

http://vpc/_vti_bin/excelservice.asmx

4. Name the Web reference as ExcelWebService, VS.Net will create the proxy files to access to Web Service.

5. Create variables to store sheet name & file path
string strSheetName = "Estimation";

string strWorkbookPath = "http://vpc/Docs/Excel%20Sheets/Estimation.xlsx";

In this example we will use the following Spreadsheet “Estimation.xlsx”, published in the “Excel Sheets” Document Library

http://vpc/Docs/Excel%20Sheets/Estimation.xlsx

6. Create and instance of the service and set default credentials.

ExcelWebService.ExcelService objExcel = new ExcelWebService.ExcelService();

objExcel.Credentials = System.Net.CredentialCache.DefaultCredentials;

7. Get the Session ID for the current connection.
string sessionId = objExcel.OpenWorkbook(strWorkbookPath, "en-US", "en-US", out outStatus);

8. Enter the cell values to be updated and click the update button in the Asp.net page.

In this example we will set the Estimated and Actual hours spent for Estimation from the Sheet.
Cells are accessed with a Zero based index.

objExcel.SetCell(sessionId, strSheetName, 2, 2, txtEstimate.Text);

objExcel.SetCell(sessionId, strSheetName, 2, 3, txtActual.Text);

objExcel.CalculateWorkbook(sessionId, ExcelWebService.CalculateType.Recalculate);

9. The values of the cells get updated, and can be checked by reading them back using the GetCell method.

10. In SharePoint 2007, the updated values get lost once the connection gets closed, SharePoint 2010 introduces the following new methods to update the values permanently in the workbook.

OpenWorkbookForEditing()
SaveWorkbook()

Here is the full code
using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.Services.Protocols;

namespace WebServices_ObjectModel

{

public partial class ExcelWebServices : System.Web.UI.Page

{

protected void btnUpdate_Click(object sender, EventArgs e)

{

string strSheetName = "Estimation";

string strWorkbookPath = "http://vpc/Docs/Excel%20Sheets/Estimation.xlsx";

ExcelWebService.ExcelService objExcel = new ExcelWebService.ExcelService();

objExcel.Credentials = System.Net.CredentialCache.DefaultCredentials;

try

{

string sessionId = objExcel.OpenWorkbook(strWorkbookPath, "en-US", "en-US", out outStatus);

// Update values to the WorkBook

objExcel.SetCell(sessionId, strSheetName, 2, 2, txtEstimate.Text);

objExcel.SetCell(sessionId, strSheetName, 2, 3, txtActual.Text);

objExcel.CalculateWorkbook(sessionId, ExcelWebService.CalculateType.Recalculate);

// Read the values Back.

object cellValue = objExcel.GetCell(sessionId, strSheetName, 2, 1, false, out outStatus);

Response.Write("Task: " + cellValue.ToString() + "
");

cellValue = objExcel.GetCell(sessionId, strSheetName, 2, 2, false, out outStatus);

Response.Write("Estimate: " + cellValue.ToString() + "
");

cellValue = objExcel.GetCell(sessionId, strSheetName, 2, 3, false, out outStatus);

Response.Write("Actual: " + cellValue.ToString() + "
");

objExcel.CloseWorkbook(sessionId);

}

catch (SoapException ex)

{

Response.Write("SOAP Exception : " + ex.Message);

}

Output:

Related Posts
Excel Services
Excel Services in SharePoint
Components of Excel Services
Publishing Excel Worksheets using Excel Service
Excel Web Services
Reading data from a Published Excel Workbook using Excel Web services
Updating data to a Published Excel Workbook using Excel Web services

Reading data from a Published Excel Workbook using Excel Web services

Excel Web Services can be used to query data from Published Excel Work Sheets, in this post is will go through the steps to read data programatically using Excel Web Services, from a Published Excel worksheet.

1. Open the application from which needs to read data from the Published Excel Worksheet using Excel Web Services.

2. I will use an Asp.Net web application to read the data.
3. Add a Web Reference to the Excel Web Services path.

http://<SiteName>/_vti_bin/excelservice.asmx

In my Server the path is

http://vpc/_vti_bin/excelservice.asmx

4. Name the Web reference as ExcelWebService, VS.Net will create the proxy files to access to Web Service.

5. Create variables to store sheet name, file path and the Status
string strSheetName = "Estimation";

string strWorkbookPath = "http://vpc/Docs/Excel%20Sheets/Estimation.xlsx";

ExcelWebService.Status[] outStatus;

In this example we will use the following Spreadsheet “Estimation.xlsx”, published in the “Excel Sheets” Document Library

http://vpc/Docs/Excel%20Sheets/Estimation.xlsx

6. Create and instance of the service and set default credentials.
ExcelWebService.ExcelService objExcel = new ExcelWebService.ExcelService();

objExcel.Credentials = System.Net.CredentialCache.DefaultCredentials;

7. Get the Session ID for the current connection.
string sessionId = objExcel.OpenWorkbook(strWorkbookPath, "en-US", "en-US", out outStatus);

8. Get the cell values and display it in the Asp.net page.

          In this example we will get the Estimated and Actual hours spent for Estimation from the Sheet.
          Cells are accessed with a Zero based index.

      object cellValue = objExcel.GetCell(sessionId, strSheetName, 2, 1, false, out       outStatus);

Response.Write("Task: " + cellValue.ToString() + "
");

cellValue = objExcel.GetCell(sessionId, strSheetName, 2, 2, false, out outStatus);

Response.Write("Estimate: " + cellValue.ToString() + "
");

cellValue = objExcel.GetCell(sessionId, strSheetName, 2, 3, false, out outStatus);

Response.Write("Actual: " + cellValue.ToString() + "
");

objExcel.CloseWorkbook(sessionId);

Here is the full code and Output

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.Services.Protocols;

namespace WebServices_ObjectModel

{

public partial class ExcelWebServices : System.Web.UI.Page

{

string strSheetName = "Estimation";

string strWorkbookPath = "http://vpc/Docs/Excel%20Sheets/Estimation.xlsx";

ExcelWebService.Status[] outStatus;

protected void Page_Load(object sender, EventArgs e)

{

ExcelWebService.ExcelService objExcel = new ExcelWebService.ExcelService();

objExcel.Credentials = System.Net.CredentialCache.DefaultCredentials;

try

{

string sessionId = objExcel.OpenWorkbook(strWorkbookPath, "en-US", "en-US", out outStatus);

object cellValue = objExcel.GetCell(sessionId, strSheetName, 2, 1, false, out outStatus);

Response.Write("Task: " + cellValue.ToString() + "
");

cellValue = objExcel.GetCell(sessionId, strSheetName, 2, 2, false, out outStatus);

Response.Write("Estimate: " + cellValue.ToString() + "
");

cellValue = objExcel.GetCell(sessionId, strSheetName, 2, 3, false, out outStatus);

Response.Write("Actual: " + cellValue.ToString() + "
");

objExcel.CloseWorkbook(sessionId);

}

catch (SoapException ex)

{

Response.Write("SOAP Exception : " + ex.Message);

}

Output
Task: Requirements
Estimate: 150
Actual: 175

Excel Web Services

Excel Web Services are a set of Web Service APIs which can be used by developers to access published Excel workbooks, read data from the workbooks, updated data to the workbooks etc.

The Excel Web Services provide a Session object which should be used to make successive calls to the web services, so that SharePoint will be able to identify all requests which come from a single application/service. This Session ID should be passed with all requests which are made from the current connection to maintain state.

Excel Web Services receives the inputs from the user and passes the same to Excel Calculation Services (ECS), ECS is responsible for performing the updates to the published Excel sheets, this interaction is abstracted from the user and happens internally, the user just needs to invoke the appropriate web service methods to query/update the data.

The List of Excel web services is available in the following path in the SharePoint server.

http:////_vti_bin/excelservice.asmx

In my Server the path is

http://vpc/_vti_bin/excelservice.asmx

Where vpc is the default site created by SharePoint 2007

Calculate
CalculateA1
CalculateWorkbook
CancelRequest
CloseWorkbook
GetApiVersion
GetCell
GetCellA1
GetRange
GetRangeA1
GetSessionInformation
GetWorkbook
OpenWorkbook
Refresh
SetCell
SetCellA1
SetRange
SetRangeA1

For details on each of these web methods refer to the following Microsoft’s page.
http://msdn.microsoft.com/en-us/library/ff384274(v=office.12).aspx

Components of Excel Services

The following are the basic components to Excel Services

Excel Web Access (EWA): This is a Web Part which is responsible for publishing worksheets in the SharePoint server, and provide access to the published worksheets to the users in their browsers.

Excel Calculation Services (ECS): This is the calculation component of Excel Services; this is responsible for the data in the published excel worksheets, it takes care of loading the data of the published worksheets, perform calculation on the server side, loading external data, and saving the updated data back to the server.

Excel Web Services (EWS): These are a set of web services, which expose the published worksheets through web service API’s they enable developers to access and update information in the published excel workbooks programmatically.