Partager via

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

  • Article

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

Method

Usage

GetCell

Gets a single cell, using a row,column coordinate (0,0 will return cell A1)

GetCellA1

Gets a single cell, using the Excel reference notation (“B2” will return, well, cell B2)

GetRange

Gets a range of cells, using row, column, height and width.

GetRangeA1

Gets a range of cells, using Excel reference notations (“B2:C19”)

SetCell

Sets a single cell using a row, column coordinate

SetCellA1

Sets a single cell, using the Excel reference notation

SetRange

Sets a range of cells, using row, column, height and width to determine what the range is.

SetRangeA1

Sets a range of cells using Excel reference notation

Comments

  • Anonymous
    March 16, 2007
    Hi, Is it possible to get all the named ranges from the workbook when it is accessed from excel services. I could use this feature to build a UI dynamically instead of the workbook author setting up the parameters each time a workbook is publised. I have a project where this process is iterative and could be a pain if it is required to set up parameters 5 times everyday.

  • Anonymous
    March 16, 2007
    The comment has been removed

  • Anonymous
    June 26, 2007
    Je suis en train de m'interesser fortement à la partie coding d' "Excel Services". Aprés quelque temps