How does getting and setting ranges from Excel Services work

When I discussed the various methods supported by Excel Services for getting and setting data on a work sheet, I did not explain in depth how the functions actually worked.

 

When using the various Range functions, it is important to understand what they expect and what gets returned from them. When asking for a range of cells by using, say, the GetRangeA1() method, you get back an object array (object[] in C#). This is actually a jagged array – each entry in the array you get back will actually be another array of objects representing the cells.

One way to look at it is to picture the array that returns from the method as the rows of the requested range and each row as a collection of cells on that row.

For example, say your workbook contains a simple range of 2x2 cells – say that on the top row in that range you have the numbers 1 and 2 and that on the bottom row of the range you have the numbers 3 and 4.

If you call one of the GetRange() methods and ask for that range, you will get an array with two items in it. The first item will be an array that contains the numbers 1 and 2 and the second item will be an array that contains the numbers 3 and 4.

It is important to note that it is guaranteed that Excel Services will always return the same number of items in each array of cells – if you asked for a 3 rows by 5 columns range, each one of the three row arrays will contain an array with exactly 5 objects in it.

C# sample of iterating over a range

In this sample, I will ask for a 2x2 range from Excel Services and dump it to the console:

 

object[] rows = s.GetRangeA1(sessionId, "Sheet1", "A1:B2", true, out statie);

foreach (object[] row in rows)

{

       Console.WriteLine(String.Format("{0} | {1}", row[0], row[1]));

}

 

As you can see, our loop goes over each of the rows returned and retrieves the two cells it contains.

VB.NET Example of iterating over a range

This time, instead of only asking for two columns, we will ask for four. We will also iterate the cells in each row instead of directly accessing them:

 

rows = s.GetRangeA1(sessionId, "Sheet1", "A1:D2", True, stati)

For Each row As Object() In rows

    For Each o As Object In row

       Console.Write(o + "|")

    Next

    Console.WriteLine()

Next

Setting ranges into an Excel sheet on the server

Using the SetRange family of methods acts the same way, only it is your responsibility to create the appropriate jagged array before making the call.

Comments

• Anonymous
  September 26, 2007
  I need to iterate through a range of cells.  I know the starting cell and the width, but not the height.  Is there any way to find out the dimensions* of the worksheet so I know the correct number to set the Height property to of my RangeCoordinates object?

• By dimensions I mean the range of fillled in cells, similar to: <dimension ref="A1:G26"/> which is stored in xlsx packages.

• Anonymous
  October 01, 2007
  You can use another cell (or better, named range) to do a COUNTIF() on the table and return the amount of cells. A better way to do it is to use paging - reading 100 rows each time until you hit empty cells. s

• Anonymous
  November 04, 2009
  why didnt you supply a proper setrange sample, this is useless

• Anonymous
  November 05, 2009
  I can.. It's a fairly simple call - which part are you having issues with?