Range.SetValue(Object) Method

Converts the specified object to the cell value.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v19.1.Core.dll

Declaration

C#

void SetValue(
    object value
)

VB.NET

Sub SetValue(
    value As Object
)

Parameters

Name	Type	Description
value	Object	An object specifying the data to be assigned to the cell value.

Remarks

To assign a value of any basic cell data type to a cell value, you can set the Range.Value property to an object of the corresponding type (String, Int32, Double, DateTime or Boolean). See the How to: Change a Cell or Cell Range Value example.

If you need to assign an object of any type to a cell value, use the SetValue method. It automatically converts the specified object to the CellValue object using the default converter and assigns it to a cell. To use a custom converter instead of the default, use the CellValue.FromObject method. See the How to: Convert Objects to Cell Values and Cell Values to Objects example.

To convert a cell value to a custom object, use the CellValue.ToObject method.

Example

The Range.SetValue and CellValue.FromObject methods allow you to convert objects of different types to SpreadsheetControl-compatible cell values. The CellValue.ToObject method performs an inverse operation - it gets objects from cell values.

The code below demonstrates how to convert cell values of different types to objects and add them to an array, and convert array elements to CellValue objects and assign them to cells. In this example, the default cell value converter is used. However, you can create your own custom converter that implements the ICellValueConverter interface and use it for conversion.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/winforms-spreadsheetcontrol-api-e4655

CellActions.cs

// Add data of different types to cells of the range.
Range sourceRange = worksheet["B1:B3"];
sourceRange[0].Value = "Text";
sourceRange[1].Formula = "=PI()";
sourceRange[2].Value = DateTime.Now;
sourceRange[2].NumberFormat = "d-mmm-yy";

// Get the number of cells in the range.
int cellCount = sourceRange.RowCount * sourceRange.ColumnCount;

// Declare an array to store elements of different types.
object[] array = new object[cellCount];

// Convert cell values to objects and add them to the array.
for (int i = 0; i < cellCount; i++) {
    array[i] = sourceRange[i].Value.ToObject();
}

// Convert array elements to cell values and assign them to cells in the fifth row. 
for (int i = 0; i < array.Length; i++) {
    worksheet.Rows["5"][i + 1].SetValue(array[i]);
    // An alternative way to do this is to use the CellValue.FromObject method.
    // worksheet.Rows["5"][i+1].Value = CellValue.FromObject(array[i]);
}

CellActions.vb

' Add data of different types to cells of the range.
Dim sourceRange As Range = worksheet("B1:B3")
sourceRange(0).Value = "Text"
sourceRange(1).Formula = "=PI()"
sourceRange(2).Value = Date.Now
sourceRange(2).NumberFormat = "d-mmm-yy"

' Get the number of cells in the range.
Dim cellCount As Integer = sourceRange.RowCount * sourceRange.ColumnCount

' Declare an array to store elements of different types.
Dim array(cellCount - 1) As Object

' Convert cell values to objects and add them to the array.
For i As Integer = 0 To cellCount - 1
    array(i) = sourceRange(i).Value.ToObject()
Next i

' Convert array elements to cell values and assign them to cells in the fifth row. 
For i As Integer = 0 To array.Length - 1
    worksheet.Rows("5")(i + 1).SetValue(array(i))
    ' An alternative way to do this is to use the CellValue.FromObject method.
    ' worksheet.Rows["5"][i+1].Value = CellValue.FromObject(array[i]);
Next i

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the SetValue(Object) method.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

wpf-spreadsheetcontrol-api-part-2-e4944/CS/SpreadsheetControl_WPF_API_Part02/SpreadsheetActions/TableActions.cs#L135

{
    sheet.Cells["B3"].SetValue("Chocolade");
    sheet.Cells["B4"].SetValue("Konbu");

winforms-spreadsheetcontrol-api-part-2-e4832/CS/SpreadsheetControl_API_Part02/SpreadsheetActions/TableActions.cs#L136

{
    sheet.Cells["B3"].SetValue("Chocolade");
    sheet.Cells["B4"].SetValue("Konbu");

how-to-format-tables-e4909/CS/TableBuiltInStyleExample/Form1.cs#L105

for (int j = 0; j < sourceTable.Columns.Count; j++) {
    worksheet.Cells[1, j + 1].SetValue(sourceTable.Columns[j].ColumnName);
}

VB.NET

wpf-spreadsheetcontrol-api-part-2-e4944/VB/SpreadsheetControl_WPF_API_Part02/SpreadsheetActions/TableActions.vb#L128

Public Shared Sub GenerateTableData(ByVal sheet As Worksheet)
    sheet.Cells("B3").SetValue("Chocolade")
    sheet.Cells("B4").SetValue("Konbu")

winforms-spreadsheetcontrol-api-part-2-e4832/VB/SpreadsheetControl_API_Part02/SpreadsheetActions/TableActions.vb#L129

Public Shared Sub GenerateTableData(ByVal sheet As Worksheet)
    sheet.Cells("B3").SetValue("Chocolade")
    sheet.Cells("B4").SetValue("Konbu")

how-to-format-tables-e4909/VB/TableBuiltInStyleExample/Form1.vb#L104

For j As Integer = 0 To sourceTable.Columns.Count - 1
    worksheet.Cells(1, j + 1).SetValue(sourceTable.Columns(j).ColumnName)
Next j