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































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 (3)

  1. manasi says:

    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.

  2. Shahar Prish says:

    Hi,

    Excel Services currently does not have these capabilities, however, there’s a somewhat sucky woraround.

    You can use a hidden sheet that contains the names of ranges you are interested in and then use that to get at the information.

  3. Je suis en train de m’interesser fortement à la partie coding d’ "Excel Services". Aprés quelque temps