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

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