Set Custom Objects to a Range

DsExcel allows you to set custom objects or their 1D and 2D arrays to a range by using setValue method 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	Description
  =	Use Object.Equals
  <>	Use Not =

  Formulas	Description
  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
Workbook workbook = new Workbook();
IWorksheet activeSheet = workbook.getActiveSheet();
IRange a1 = activeSheet.getRange("A1");
HashMap<String, Object> dict = new HashMap<String, Object>();
dict.put("TempData1", 1);
dict.put("TempData2", "Temp value 2");
dict.put("TempData3", 3);
dict.put("TempData4", "Temp value 4");

// Set temporary data to a range
a1.setValue(dict);

// Display the custom object later
HashMap<String, Object> obj = (HashMap<String, Object>)a1.getValue();
int row = 1;
for (Map.Entry<String, Object> kv : obj.entrySet())
{
    activeSheet.getRange("B" + row).setValue(kv.getKey());
    activeSheet.getRange("C" + row).setValue(kv.getValue());
    row += 1;
}

// Arrange
activeSheet.getColumns().autoFit();
activeSheet.getColumns().get(0).setHidden(true);

//save to an pdf file
workbook.save("SetCustomRangeValue.pdf");

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

// The JSON converter class
class GsonConverter<T> implements IJsonSerializer {
    private final Gson _gson = new Gson();
    private final Class<T> _type;
 
    public GsonConverter(Class<T> type) {
        _type = type;
    }
 
    private final GenericStaticFieldValueProvider<GsonConverter<?>> s_instanceProvider =
        new GenericStaticFieldValueProvider<GsonConverter<?>>();
 
    public <T> GsonConverter<T> GetInstance(Class<T> canon) {
        GsonConverter<T> instance = (GsonConverter<T>) s_instanceProvider.getValue(canon);
        if (instance != null) {
            return instance;
        }
        instance = new GsonConverter<T>(canon);
        s_instanceProvider.setValue(canon, instance);
        return instance;
    }
 
    public final Object deserialize(String json) {
        JsonObject jObject = new JsonParser().parse(json).getAsJsonObject();
        String typeName = jObject.get("typeName").getAsString();
        if (typeName.equals(_type.getSimpleName())) {
            jObject.remove("typeName");
            return _gson.fromJson(jObject, _type);
        }
        return null;
    }
 
    public final String serialize(Object value) {
        JsonObject jObject = _gson.toJsonTree(value).getAsJsonObject();
        jObject.addProperty("typeName", _type.getSimpleName());
        return _gson.toJson(jObject);
    }
 
} // End Class ' GsonConverter
 
// Workaround for "Cannot make a static reference to the non-static type T"
class GenericStaticFieldValueProvider<TValue> {
    private final ConcurrentHashMap<Class<?>, TValue> _value = new ConcurrentHashMap<Class<?>, TValue>();
    public TValue getValue(Class<?> canon) {
        return (TValue) _value.get(canon);
    }
    public void setValue(Class<?> canon, TValue value) {
        _value.put(canon, value);
    }
} // End Class
    
public void overrideJSON() {
    // Usage
    Workbook.setValueJsonSerializer(GsonConverter.GetInstance(ValueWithUnit.class));
}

type=note

Note: java.math.BigInteger is treated as custom object instead of java.lang.Double.

Set BigDecimal to a Range

DsExcel allows you to set the BigDecimal values to a range by using IRange.setValue interface method. By default, these values are treated as Double. However, you can also choose to handle the BigDecimal values as a custom object by setting IDataOptions.setBigDecimalAsDouble to false. The interface also provides getBigDecimalAsDouble method to fetch whether a BigDecimal value is treated as Double or custom object.

setBigDecimalAsDouble(true)	setBigDecimalAsDouble(false)

// Create a new workbook
Workbook workbook = new Workbook();
Object[] objects = new Object[] { 
    new BigDecimal("3679523593914784257459000.7512"),
    new BigDecimal("123456789012345678901234567890.45561462"), };
        
// Treat BigDecimal as Custom Object.
workbook.getOptions().getData().setBigDecimalAsDouble(false);
IWorksheet activeSheet = workbook.getActiveSheet();

activeSheet.getRange("A1:A2").setColumnWidth(200);
activeSheet.getRange("A1:A2").setRowHeight(40);
activeSheet.getRange("A1:A2").getFont().setSize(30);
activeSheet.getRange("A1:A2").setValue(objects);

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