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