How to: Save to the Server to Prepare for Programmatic Access

This example shows how to save an Excel workbook to the server to get it ready for the following programmatic access. The steps are:

1. Create a workbook with named ranges.

2. Save the workbook to a trusted SharePoint library location.

   Note

   It is assumed that you have already created a SharePoint document library and made it a trusted location. For more information about this, see How to: Trust a Location and How to: Trust Workbook Locations Using Script.

3. Programmatically specify values for the worksheet, named range, and cell value by using the Excel Web Services SetCellA1 method. The values are passed in as arguments—that is, args [1] and args [2]:

   status = xlServices.SetCellA1(sessionId, String.Empty, args[1], args[2]);
   

You can specify the values of args [1] and args [2] by using a Web form or command line:

GetSnapshot.exe http://MyServer002/MyTrustedDocumentLibrary/TestMyParam.xlsx MyParam 28 > MySnapshot.xlsx 

In this example, args [1] is "MyParam", args [2] is "28" and "GetSnapshot.exe" is the name of the application you create. To find a sample program, see How to: Get an Entire Workbook or a Snapshot.

To create a named range

1. Start Microsoft Office Excel 2007.

2. Rename "Sheet1" to "MyParamSheet".

3. In cell B2, type 20.

4. In cell B3, type =2+B2.

5. Make cell B3 bold.

6. Make cell B2 into a named range. Click Formulas on the menu. Then click cell B2 to select it. On the Formulas menu, click Name a Range. In the New Name dialog box, in the Name box, type MyParam.

7. Save the workbook to a location of your choice on the local drive. Name the workbook "TestMyParam.xlsx".

To save to a SharePoint library

1. Click the Microsoft Office Button, point to Publish, and then click Excel Services.

2. In the Save As dialog box, click Excel Services Options.

3. In the Excel Services Options dialog box, on the Show tab, make sure that Entire Workbook is selected.

4. Click Parameters.

5. Click Add.

6. In the Add Parameters list, you should see "MyParam". Select the "MyParam" check box.

7. Click OK. You should now see "MyParam" in the Parameters list.

8. Click OK.

9. In the Save As dialog box, clear the Open this workbook in my browser after I save check box.

10. In the File name box, type the path to the trusted SharePoint document library where you want to store this workbook. For example, http://MyServer002/MyDocumentLibrary/TestParam.xlsx.

11. Click Save.

To specify values programmatically

1. The signature for the SetCellA1 method in Excel Web Services is:

   public void SetCellA1 (
   string sessionId,
   string sheetName,
   string rangeName,
   Object cellValue,
   Out Status[] status
   )
   

   Set the values for the worksheet, named range, and cell value to the SetCellA1 method as follows:

   // Set a value into a cell.
   status = xlSrv.SetCellA1(sessionId, String.Empty, args[1], args[2]);
   

2. In the preceding code:

   • args [1] is the name of the named range. In this example, it is "MyParam".

   • args [2] is the value you want to set in the cell. The cell where the value will be set is the named range in args [1] called "MyParam".

3. If you are using a command line, you can pass in the arguments as follows:

   GetSnapshot.exe http://MyServer002/MyTrustedDocumentLibrary/TestMyParam.xlsx MyParam 28 > MySnapshot.xlsx

4. If you generate a snapshot of the workbook, you will see the following:

   • Cell B2 (with the named range "MyParam") now has a value you fed through the program, which is "28".

   • Cell B3 has a new calculated value of "30".

   • Cell B3 does not show the original formula, which was "=2+B2".

   • Cell B3 retains its font format, which is bold.

See Also

Tasks

How to: Save from Excel Client to the Server
Walkthrough: Developing a Custom Application Using Excel Web Services
How to: Trust Workbook Locations Using Script

Concepts

Accessing the SOAP API
Loop-Back SOAP Calls and Direct Linking
Excel Services Alerts