SharePoint2010 Excel Services to read data from excel sheet

To read the data from the excel services.

I have implemented the code that will get the information from the excel file stored in SharePoint Library.
Read the data from excel service. This is simple Window application.
To read the Excel file from SharePoint Site you need to have a full persimmon to access the web services.  

Excel Web services

You can view the Excel Web Services Web Services Description Language (WSDL) page by navigating to the following URL on the server: http://<server>/<customsite>/_vti_bin/excelservice.asmx?WSDL If you do not have a custom site, you can view the WSDL by using the following URL: http://<server>/_vti_bin/excelservice.asmx?WSDL

  private void btnLoad_Click(object sender, EventArgs e)
            ExcelService proxy = new ExcelService();
            proxy.Credentials = new System.Net.NetworkCredential ("administrator", "abc123#");
            XLWebService.Status[] wsStatus;
            string sheetName = "Sheet2";
            string namedRange = "TotalProject";
            DataTable contractData = new DataTable("Total Project");
            DataColumn compName = contractData.Columns.Add("Project", Type.GetType("System.String"));
            DataColumn contractTotal = contractData.Columns.Add("Total Project", Type.GetType("System.String"));
            DataRow newRow;
            string sessionID = proxy.OpenWorkbook("http://home/AnalyticsReports/Total_Project.xlsx", "en-US", "en-US", out wsStatus);
             // this function will take a RangeName
            object[] returnData = proxy.GetRangeA1(sessionID, sheetName, namedRange, false, out wsStatus);

            for (int i = 1; i < returnData.Length; i++)
                newRow = contractData.NewRow();
                newRow["Project"] = ((object[])(returnData[i]))[0].ToString();
                newRow["Total Project"] = "$" + ((object[])(returnData[i]))[1].ToString() + ".00";
            datagridExcelData.DataSource = contractData;
Please download the sample code. Sample code contains following files.
  1. Total_Projects.xlsx
  2. Window Application
Source Code


Popular posts from this blog

SharePoint RPC Protocols Examples Using OWSSVR.DLL

STS CryptographicException Error : Key set does not exist

Query suggestions in SP 2013 Using Rest API (/_api/search/suggest)