Using the Excel Services Web service in Sharepoint 2010

The Excel Services Web service provides a number of methods to interact with data on the server from a spreadsheet. To retrieve data from a spreadsheet programmatically using the Excel services API, follow these steps:

1. Open the Contracts Excel spreadsheet you added to SharePoint in Edit mode, and select all of the Customer and Contract Totals. Create a named range by typing ContractTotals in the Name box (in the upper left-hand corner of the spreadsheet) and then pressing Enter.

2. Save and close the spreadsheet.

3. Open Visual Studio 2010. Click File ➪ New ➪ Windows ➪ Windows Form Application.

4. Add a data grid and two buttons to your Windows Form application. Name the data grid datagrdExcelData, one button btnLoad, and the other button btnExit. The
Text property for btnLoad should be Load, and the Text property for btnExit should be Exit.

5. Right-click References, and click Add Service Reference. Then, in the Service Reference Settings dialog, click Advanced ➪ Add Web Reference. Type in the URL to the Excel Services Web service:

http:///_vti_bin/ExcelService.asmx.

6. Provide a name for the Web service reference (for example, XLWebService) and click Add Reference.

7. Double-click the Exit button and add the following bolded code:

private void btnExit_Click(object sender, EventArgs e)
{
Application.Exit();
}

8. Double-click the Load button and add the following bolded code:

private void btnLoad_Click(object sender, EventArgs e)
{
XLWebService.ExcelService proxy = new XLWebService.ExcelService();
proxy.Credentials = new System.Net.NetworkCredentials(“Administrator”, “pass”);
XLWebService.Status[] wsStatus;
string sheetName = “SheetName”;
string namedRange = “ContractTotals”;
DataTable contractData = new DataTable(“Contract Totals”);
DataColumn compName = contracData.Columns.Add(“Customer”,Type.GetType(“System.String”));
DataColumn contractTotal = contractData.Columns.Add(“Contract Totals”,Type.GetType(“System.String”));
DataRow newRow;
string sessionID = proxy.OpenWorkbook(“http://SPsite/Contracts/Contract_Totals.xlsx”, “en-US”,”en-US”, out wsStatus);
object[] returnData = proxy.GetRangeA1(sessionID, sheetName, namedRange, false, out wsStatus);
for (int I = 1l I < returnData.Length; i++)
{
newRow = contractData.NewRow();
newRow["Customer"] = ((object[])(returnData[i]))[0].ToString();
newRow["Contract Totals"] = “$ ” + ((object[])(returnData[i]))[1].
ToString() + “.00″;
contractData.Rows.Add(newRow);
}
datagrdExcelData.DataSource = contractData;
}}

9. When finished adding the code, press F6 to build the project,and then press F5 to run it.

10. When the application launches, click Load to run the Excel Web Service and load the data from the spreadsheet.