Set Custom Objects to a Range

GcExcel allows you to set custom objects or their 1D and 2D arrays to a range by using Value property of IRange interface. Custom objects are not supported for Excel I/O though.

Behavior of Custom Objects with Below Operations

• Json Serialization and Deserialization: Custom objects are discarded while performing Json serialization and deserialization (except for built-in SpreadJS interop types). However, this behavior can be overridden by setting the Workbook.ValueJsonSerializer property.

• Export to PDF, HTML or Image Formats: While exporting worksheets with custom objects to PDF, HTML or image formats, you can use Convert.ToString and then export custom objects as string. If the cell is too narrow to fit the content, ##### is exported.

• Cut and Copy: While performing cut and copy operations, custom objects can be copied or moved to another range, worksheet and workbook. Custom objects are always copied by reference. Some of them are even structures.

• Range.Text: While using Range.Text with custom objects, Convert.ToString method should be used.

• Built-in formulas: While using Built-in formulas, range references are treated as custom objects, including array formula expressions, such as {=A1:D3}. The following behavior is observed while using built-in formulas with custom objects:

  • Range references of custom objects in pattern matching (lookup) formulas are skipped

  • Range references of custom objects in aggregation formulas (mainly SUM*, statistical and database formulas) are skipped if custom data types do not make sense

  • Custom objects in aggregation formulas are accepted if custom data types are acceptable. For example, custom objects are counted in the COUNTA function

  • The below formulas return the specified value:

    • ISERROR: Always returns FALSE

    • TYPE: Returns #VALUE!

    • ERROR.TYPE: Returns #N/A

  • In all other cases, custom objects are treated as #VALUE!

  While using below operators and formulas with custom objects, the mentioned methods should be used:

  
  

  Operators	=	Use Object.Equals
  	<>	Use Not =
  Formulas	EXACT	Use Object.ReferenceEquals
  	TEXT	Use Convert.ToString

The following barcode formulas can handle custom objects:

• BC_CODABAR

• BC_CODE128

• BC_CODE39

• BC_CODE49

• BC_CODE93

• BC_DATAMATRIX

• BC_EAN13

• BC_EAN8

• BC_GS1_128

• BC_PDF417

• BC_QRCODE

Refer to the following example code to set 2D array of custom objects to a range.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

var activeSheet = workbook.ActiveSheet;
IRange a1 = activeSheet.Range["A1"];
var dict = new Dictionary()
{
    {"TempData1", 1},
    {"TempData2", "Temp value 2"},
    {"TempData3", 3},
    {"TempData4", "Temp value 4"}
};
// Set temporary data to a range
a1.Value = dict;

// Display the custom object later
var obj = (IReadOnlyDictionary)a1.Value;
var row = 1;
foreach (var kv in obj)
{
    activeSheet.Range["B" + row].Value = kv.Key;
    activeSheet.Range["C" + row].Value = kv.Value;
    row += 1;
}

// Arrange
activeSheet.Columns.AutoFit();
activeSheet.Columns[0].Hidden = true;

//save to a pdf file
workbook.Save("setcustomrangevalue.pdf");

Refer to the following example code to override JSON serialization behavior.

 // The JSON converter class
class JsonNetConverter : IJsonSerializer
{
    public static JsonNetConverter Instance { get; } = new JsonNetConverter();

    public object Deserialize(string json)
    {
        var jobj = JObject.Parse(json);
        if (jobj.TryGetValue("typeName", out JToken jtok) &&
            jtok is JValue jval &&
            jval.Type == JTokenType.String &&
            (string)jval.Value == typeof(T).Name)
        {
            return JsonConvert.DeserializeObject(json);
        } // End If
        return null;
    }

    public string Serialize(object value)
    {
        var jObj = JObject.FromObject(value);
        jObj.Add("typeName", new JValue(typeof(T).Name));
        return jObj.ToString(Newtonsoft.Json.Formatting.None);
    }
} // End Class ' JsonNetConverter

public void overrideJSON()
{
    // Usage
    Workbook.ValueJsonSerializer = JsonNetConverter.Instance;
}