Using the Calculation Power of Excel By Using Excel Services in SharePoint Server 2010

Excel Web Services is one of the programmatic interfaces of Excel Services, a component of SharePoint Server 2010. Using Excel Web Services you can programmatically access and manipulate Microsoft Excel workbooks on SharePoint Server 2010.
One benefit of Excel Web Services is that you can handle Excel workbooks as programmatic objects. This enables you to use the calculation and modeling features of Excel. This frees you from implementing similar functionality within other projects. Typically you create a workbook suited for a particular task and then use Excel Web Services to interact with the workbook programmatically. In this Visual How To, you will learn how to use Excel Web Services with only an empty workbook stored on SharePoint 2010 and a few dozen lines of code. Specifically, you will learn how to use Excel Web Services to create a simple utility for evaluating formulas entered into the Windows Run dialog box that are then shown in a Command Prompt window as shown in Figure 1 and Figure 2.
Figure 1. Windows Run dialog box


Figure 2. Command prompt that displays the result of an Excel calculation



The code in this section shows how to use Excel Web Services to use Excel calculation.
To create this utility, save an empty workbook named Calculator.xlsx to a document library in SharePoint Server 2010. Next, open Visual Studio 2010 and create a new Console application named CommandLineCalculator.
After creating the project in Visual Studio, add a Web Reference to the Excel Web Service.

To add a Web Reference to the Excel Web Services

1. Right-click your project and select Add Service Reference.
2. Click Advanced.
3. Click Add Web Reference.
4. Enter the URL to Excelservice.asmx on your SharePoint Server. For example: http://contoso.intranet.com/_vti_bin/excelservice.asmx.
5. Press Enter. Visual Studio will attempt to query the service and display a list of the operations supported by the service.
6. If you need to, modify the Web Reference name. In the code sample for this Visual How To, the Web Reference is named XL as shown in Figure 3.
   Figure 3. Add Web Reference dialog box
   
7. Click Add Reference.

After you add the Web Reference, delete the existing Main function and replace it with the Main function shown here together with an additional function named Calculate.

C#

Copy

static void Main(string[] args)
{
    if (args.Length > 0)
    {
        string result = Calculate(args[0]);
        Console.WriteLine("Result: {0}", result);
    }
    else
    {
        Console.WriteLine("You didn't supply a formula to evaluate.");
    }

    Console.WriteLine("Press ENTER to quit.");
    Console.ReadLine();
}

static string Calculate(string formula)
{
    string result;

    // Instantiate the web service and make a status array object.
    XL.ExcelService xlService = new XL.ExcelService();
    XL.Status[] outStatus;
            
    // Set path to workbook, worksheet name, and credentials.
    string workbookPath = 
        "http://server_name/Shared%20Documents/Calculator.xlsx";
    
    string sheetName = "Sheet1";
    xlService.Credentials = 
        System.Net.CredentialCache.DefaultCredentials;

    try
    {
        // Open the workbook.
        string sessionId = xlService.OpenWorkbook(
            workbookPath, "en-US", "en-US", out outStatus);

        // Set the formula of cell A1 and then recalculate.
        xlService.SetCell(sessionId, sheetName, 0, 0, formula);

        xlService.CalculateWorkbook(
            sessionId, XL.CalculateType.Recalculate);
                
        // Read the result and close the workbook.
        result = xlService.GetCell(sessionId, 
                                   sheetName, 
                                   0, 
                                   0, 
                                   false, 
                                   out outStatus).ToString();
        
        xlService.CloseWorkbook(sessionId);
    }
    catch (Exception e)
    {
        result = "Exception: " + e.Message;
    }
    return result;
}

After you build and test the project, the final step is to associate the executable with a shortcut so that you can run it from the Windows Run dialog box. To do this, add a registry entry to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths.
To associate the executable with the text XL, add a key named XL.exe. Next, set the default value equal to the path of the CommandLineCalculator executable created when you built your project.

Excel Web Services is the Excel Services component that provides programmatic access to workbooks on SharePoint Server 2010. Applications can call Excel Web Services to calculate, set, and extract values and formulas from workbooks, and to refresh external data connections. By using Excel Web Services, you can incorporate server-side workbook logic into an application, automate the updating of Excel workbooks, and create application-specific user interfaces around server-side Excel calculation.
Excel Services also provides other programmatic alternatives for working with server-based workbooks. For example, there is a REST API and a ECMAScript (JavaScript, JScript) Object Model. Excel Web Services is unique from these alternatives because it is the only way to modify formulas inside a range. As demonstrated in this Visual How To, this ability to modify formulas at run time lets programmers use the Excel calculation engine from inside other applications.
The CommandLineCalculator demonstrated in this Visual How To allows you to enter an Excel formula as an argument and then uses Excel Services to evaluate the argument and calculate the result. Consequently the program assumes that the argument that was passed into the Main function is a string that contains the formula to evaluate using Excel Services. Consequently, the first thing is to verify that at least one argument is provided to the function. If there is an argument, the Main function merely passes it along to the Calculate function.
The Calculate function handles initializing the web service, opening the workbook, sending the formula to be evaluated to the workbook, recalculating the workbook, reading the result, and then closing the workbook. Be aware that the workbook path should reflect the location of the workbook named Calculate.xlsx.
When you are working with Excel Web Services, methods that are specific to a workbook require a string argument named Session ID. The session ID that is used by Excel Web Services to associate a method call with a specific in-memory workbook within Excel Services. This makes sense when you consider that Excel Services runs on a server and therefore may have multiple copies of the same workbook open in memory.
Remember that when it refers to a particular range or cell via row and column coordinates, Excel Services uses a zero-based convention. Depending on your perspective this may be slightly different than you expect. Developers with lots of experience developing Excel client solutions are used to a one-based convention for specifying rows and columns.
The CommandLineCalculator is a great example of how you can use the power of Excel within other applications without having a client-side dependency on Excel.