How to Programmatically read data from excel sheet in SharePoint 2010 using excel web services

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?WSDLIf 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 voidbtnLoad_Click(object sender, EventArgs e)
ExcelService proxy = newExcelService();
proxy.Credentials = new System.Net.NetworkCredential ("administrator","abc123#");
XLWebService.Status[] wsStatus;
string sheetName = "Sheet2";
string namedRange = "TotalProject";
DataTable contractData = newDataTable("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


Post a Comment

Popular Posts