Getting Formatted vs. Unformatted data from Excel Services

  • Article

The GetXXX family of methods in Excel Services API allows you to retrieve data from an opened session on the server. All these methods take a parameter called formatted which determines how Excel Services will return the information that’s in the Excel sheet.

 

I will use the GetCellA1() method  in my examples, but the logic holds true for all of the GetXXX methods.

 

Lets say you enter the value “1%” in A1 on a sheet. Your Excel Client will dutifully display the string 1% in the cell, however, when you use that value in calculations, the number that will actually be used will be 0.01. To support both these cases in Excel Services, we added the formatted parameter to all our GetXXX method calls. In this case, if I make the following call:

object o = s.GetCellA1(sessionId, "Sheet1", "A1", true, out stati);

 

Because we asked for the formatted value, we will always get back the string “1%”. Since all the methods in the GetXXX family return object, it is important to know what one can expect. In this case, when you request a formatted value, you can know for a fact that you will always get back a string, no matter what and so it is safe to write:

 

string o = (string)s.GetCellA1(sessionId, "Sheet1", "A1", true, out stati);

If, on the other hand, you wanted the value that Excel actually uses in A1 for its calculations, you would have passed in false in the formatted parameter. In the case we showed, this would have returned the double value 0.01:

 

object o = s.GetCellA1(sessionId, "Sheet1", "A1", false, out stati);

 

If you now look at o, you would see that it’s actually a boxed double. To get the double out of it, you simply need to cast it to a double.

What data types are supported with unformatted Get requests

Currently, Excel Services supports the following types:

.NET type

Excel representation

Double(double in C#)

Any cell that is internally referenced as a number, except for booleans. This includes integer numbers and dates – they will always come back as “doubles”.

Boolean(bool in C#)

A cell that contains the result of a logical Excel operation or that just contains the string “TRUE” or “FALSE” will return as a Boolean boxed in an object.

String

(string in C#)

A cell that contains a string (or an empty cell) will always come back as a string instance pointed to by the returned object.

 

There's currently an issue we are looking into where the DateTime type is not supported when getting value from cells so the jury is still out on that data type.