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";

contractData.Rows.Add(newRow);

}

datagridExcelData.DataSource = contractData;

}

Please download the sample code. Sample code contains following files.

Source Code

SharePoint For Developers