Document Solutions for Excel, Java Edition | Document Solutions
Features / Worksheet / Range Operations / Set Custom Objects to a Range
In This Topic
    Set Custom Objects to a Range
    In This Topic

    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


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

    Java
    Copy Code 
    //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.

    Java
    Copy Code 
    // 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));
}

    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)

    Java
    Copy Code 
    // 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");