Information about the various Excel Service API calls to get info from and to Excel Services

In a previous post, I talked about how to work with an Excel Model on the server and promised I would talk about the various method calls that actually set cells on the server or get cells from the server.

The Excel Services API exposes four methods for setting cells in an Excel workbook and four methods for getting cells from an Excel workbook. Each of these eight functions follows a naming convention which (hopefully) makes it easy to remember.

Get vs. Set

Each of the eight functions starts with either Get or Set. I will not insult your intelligence by explaining which one is used for setting cells in a workbook and which for getting cells in a workbook. I am just not that kind of guy. No sir.

Range vs. Cell

Another no-brainer.

The GetCell() /GetCellA1() methods return the value of a single cell. If you try to request more than a single cell (either by passing in a range reference such as “A1:B2” or by giving a named range that is larger than a single cell etc) your method call will fail.

Conversely, the SetCell() /SetCellA1() methods set the value of a single cell.

The methods that have Range in their names (SetRange/SetRangeA1 and GetRange/GetRangeA1) allow you to retrieve or set a range of cells on the server.

A1 vs. no-A1

Methods that have the A1 postfix use a different coordinate system than the ones that do not.

If you wish to use Excel style-reference to cells such as range references (C9, A2:B5 etc) or named ranges, you should use the methods with the A1 post fix. In them you can pass in the name of a sheet and the range you want. Note that if you are asking for a named range that has the scope of a workbook, you do not need to specify the sheet name.

On the other hand, if you want to access an Excel sheet using a numeric coordinate system, you should use the methods that do not have the A1 as postfix.

Summary