How to: Convert Objects to Cell Values and Cell Values to Objects

// 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]);
}

' 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

        Worksheet worksheet = workbook.Worksheets[0];
        Cell cell = worksheet.Cells["A1"];
        cell.FillColor = Color.Orange;
        cell.Value = CellValue.FromObject(cell.FillColor, new ColorToNameConverter());
        // ...

class ColorToNameConverter : ICellValueConverter {
    object ICellValueConverter.ConvertToObject(CellValue value) {
        return null;
    }
    CellValue ICellValueConverter.TryConvertFromObject(object value) {
        bool isColor = value.GetType() == typeof(Color);
        if (!isColor)
            return null;
        return ((Color)value).Name;
    }
}

        Dim worksheet As Worksheet = workbook.Worksheets(0)
        Dim cell As Cell = worksheet.Cells("A1")
        cell.FillColor = Color.Orange
        cell.Value = CellValue.FromObject(cell.FillColor, New ColorToNameConverter())
        ' ...

Private Class ColorToNameConverter
    Implements ICellValueConverter

    Private Function ICellValueConverter_ConvertToObject(ByVal value As CellValue) As Object Implements ICellValueConverter.ConvertToObject
        Return Nothing
    End Function
    Private Function ICellValueConverter_TryConvertFromObject(ByVal value As Object) As CellValue Implements ICellValueConverter.TryConvertFromObject
        Dim isColor As Boolean = value.GetType() Is GetType(Color)
        If Not isColor Then
            Return Nothing
        End If
        Return DirectCast(value, Color).Name
    End Function
End Class