Just get the value of a cell

[Ian]

I'm enjoying the rich function available in the RadSpreadsheet, but as so often with the Telerik framework, it's the simple stuff which I find hard.

Yesterday I spent a few hours trying to discover how to get the value of a cell, where I don't know if the cell has a forumula, or is just a value.

Something like:

myString  = myWorksheet.cell(2,3).value.     (I know this doesn't work - it's just what the code should look like in my head).

So just pick one cell, and return what's visible to the user. I've looked through all the examples, and can't seem to find this one.

 

[Svilen]

Hi, Ian,

My name is Svilen, and I'd be glad to help out with this question!

Getting the value of a cell can be done in several ways.

1. By using the GetValue().Value.RawValue property of the cell. This works for both values and formulas. In the example below, we create a workbook from scratch and add a worksheet. Afterward, we assign the formula =SUM(A2, 3) to cell A1 and the value of 10 to A2:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();

worksheet.Cells[0, 0].SetValue("=SUM(A2, 3)"); // cell A1, equal to cell A2+3 or 13
worksheet.Cells[1, 0].SetValue(10); // cell A2

var rawValueA1 = worksheet.Cells[0, 0].GetValue().Value.RawValue; // returns the string "=SUM(A2, 3)"
var rawValueA2 = worksheet.Cells[1, 0].GetValue().Value.RawValue; // returns the string "10"

Please note that this method returns a string, instead of their evaluated result. Shared below, is a second method of getting both the evaluated result and the formula as a string.

2. This code uses the first two cells created previously:

ICellValue cellValue = worksheet.Cells[0, 0].GetValue().Value; //This interface is implemented by the five supported Cell Value types. 


CellValueFormat format = worksheet.Cells[0, 0].GetFormat().Value; // A specific data format is required, in order to evaluate the resultValue of a formula. In this case we can use the one present in the cell itself.

string valueAsString = cellValue.GetValueAsString(format);   // returns the string "=SUM(A2, 3)" 
string resultValue = cellValue.GetResultValueAsString(format); // returns the string "13"

In both cases, one needs to cast the results to the appropriate data type.

3. Another method of getting the evaluated formula result is listed below:

ICellValue cellValue = worksheet.Cells[0, 0].GetValue().Value; 

 FormulaCellValue formulaValue = cellValue as FormulaCellValue; //This cast allows the use of the GetResultValueAsCellValue() method
 if (formulaValue != null)
 {
          NumberCellValue numberCellValue = formulaValue.GetResultValueAsCellValue() as NumberCellValue; // This cast allows access to the Value property of the specific CellValue. Please make sure to check the appropriate CellValueType needed before casting.
          double result = numberCellValue.Value; // returns double 13
 }

You can read more about the supported Cell Values and their properties here - SpreadProcessing - Cell Value Types.

I hope this helps. Should you have any other questions do not hesitate to ask. Have a great day!

Regards,
Svilen
Progress Telerik

[Ian]

Excellent - thanks a lot - this is exactly what I need!

Perhaps this could go into the documentation for RadSpreadsheet, somewhere near the top where we can all fnid it!

[Svilen]

Hey, Ian,

Glad to hear my message was helpful! I already brought it up with the team and we will soon be refactoring the article I shared in order to improve its findability.

Please do not hesitate to reach out if you need our help again. Have a great day!