Document Solutions for Excel, Java Edition | Document Solutions
Features / Worksheet / Cell Types / Range Template Cell
In This Topic
    Range Template Cell
    In This Topic

    DsExcel supports Range Template cell type which allows you to specify a cell range in the worksheet which acts as a range template. The range template is considered as a single cell and can be applied to a cell or cell range, as desired. The data into the range template can be loaded from a data source.

    This feature is particularly useful when you want to display some specific ranges of data with identical structures (as displayed in the screenshots below) without having to configure the same style for multiple ranges again and again. 

    The above Range Template when applied to a cell range A1:B2 and is loaded with data from data source looks like below: 

    The following steps must be performed to create a Range Template cell type:

    1. Create a Range Template: Design the layout of Range Template in a worksheet. The template can be bound to data by using setBindingPath property.
    2. Configure Data: Configure a Data source to bind the template.
    3. Create & Apply Range Template cell type: Create a Range Template cell type by using RangeTemplateCellType method and apply it to the desired cell range.

    Refer to the following code to create a Range Template cell type.

    Java
    Copy Code 
    //create a new workbook
Workbook workbook = new Workbook();
        
Workbook.setValueJsonSerializer(new CustomObjectJsonSerializer());

IWorksheet sheet1 = workbook.getActiveSheet();
IWorksheet sheet2 = workbook.getWorksheets().add();

PersonalAssets record1 = new PersonalAssets();
record1.name = "Peyton";
record1.savings = 25000;
record1.shares = 55000;
record1.stocks = 15000;
record1.house = 250000;
record1.bonds = 11000;
record1.car = 7500;

PersonalAssets record2 = new PersonalAssets();
record2.name = "Icey";
record2.savings = 30000;
record2.shares = 45000;
record2.stocks = 25000;
record2.house = 20000;
record2.bonds = 18000;
record2.car = 75000;

PersonalAssets record3 = new PersonalAssets();
record3.name = "Walter";
record3.savings = 20000;
record3.shares = 4000;
record3.stocks = 95000;
record3.house = 30000;
record3.bonds = 10000;
record3.car = 56000;

PersonalAssets record4 = new PersonalAssets();
record4.name = "Chris";
record4.savings = 70000;
record4.shares = 85000;
record4.stocks = 35000;
record4.house = 20000;
record4.bonds = 15000;
record4.car = 45000;

// Set binding path for cell.
sheet2.getRange("A1:C1").merge();
sheet2.getRange("A1:C1").setHorizontalAlignment(HorizontalAlignment.Center);
sheet2.getRange("A1:C1").setVerticalAlignment(VerticalAlignment.Center);

sheet2.getRange("A1").setBindingPath("name");
sheet2.getRange("A1").getFont().setName("Arial");
sheet2.getRange("A1").getFont().setSize(15);
sheet2.getRange("1:1").setRowHeight(30);
sheet2.getRange("A2").setValue("Asset Type");
sheet2.getRange("B2").setValue("Amount");
sheet2.getRange("C2").setValue("Rate");
sheet2.getRange("A3").setValue("Savings");
sheet2.getRange("A3").getInterior().setColor(Color.FromArgb(145, 159, 129));
sheet2.getRange("B3").setBindingPath("savings");
sheet2.getRange("C3").setFormula("=B3/B9");
sheet2.getRange("A4").setValue("Shares");
sheet2.getRange("A4").getInterior().setColor(Color.FromArgb(215, 145, 62));
sheet2.getRange("B4").setBindingPath("shares");
sheet2.getRange("C4").setFormula("=B4/B9");
sheet2.getRange("A5").setValue("Stocks");
sheet2.getRange("A5").getInterior().setColor(Color.FromArgb(206, 167, 34));
sheet2.getRange("B5").setBindingPath("stocks");
sheet2.getRange("C5").setFormula("=B5/B9");
sheet2.getRange("A6").setValue("House");
sheet2.getRange("A6").getInterior().setColor(Color.FromArgb(181, 128, 145));
sheet2.getRange("B6").setBindingPath("house");
sheet2.getRange("C6").setFormula("=B6/B9");
sheet2.getRange("A7").setValue("Bonds");
sheet2.getRange("A7").getInterior().setColor(Color.FromArgb(137, 116, 169));
sheet2.getRange("B7").setBindingPath("bonds");
sheet2.getRange("C7").setFormula("=B7/B9");
sheet2.getRange("A8").setValue("Car");
sheet2.getRange("A8").getInterior().setColor(Color.FromArgb(114, 139, 173));
sheet2.getRange("B8").setBindingPath("car");
sheet2.getRange("C8").setFormula("=B8/B9");
sheet2.getRange("A9").setValue("Total");

sheet2.getRange("B9:C9").merge();
sheet2.getRange("B9:C9").setHorizontalAlignment(HorizontalAlignment.Center);
sheet2.getRange("B9:C9").setNumberFormat("$#,##0_);($#,##0)");
sheet2.getRange("B9:C9").setFormula("=SUM(B3:B8)");

sheet2.getRange("B3:B8").setNumberFormat("$#,##0_);($#,##0)");
sheet2.getRange("C3:C8").setNumberFormat("0.00%");
sheet2.getRange("C3:C8").getFormatConditions().addDatabar();

// Set data source.
sheet1.getRange("A:B").setColumnWidthInPixel(300);
sheet1.getRange("1:2").setRowHeightInPixel(200);
sheet1.getRange("A1").setValue(record1);
sheet1.getRange("B1").setValue(record2);
sheet1.getRange("A2").setValue(record3);
sheet1.getRange("B2").setValue(record4);

// Create a range template celltype
RangeTemplateCellType rangeTemplateCelltype = new RangeTemplateCellType(sheet2);

// Apply cell type to "A1:B2"
sheet1.getRange("A1:B2").setCellType(rangeTemplateCelltype);

//save to an pdf file
workbook.save("AddRangeTemplateCellType.pdf");    
}
public class CustomObjectJsonSerializer implements IJsonSerializer {
    Gson gson = new Gson();
    public final Object deserialize(String json) {
    return this.gson.fromJson(json, JsonElement.class);
}
        
public final String serialize(Object value) {
    return this.gson.toJson(value);
}
}
class PersonalAssets {
public String name;
public int savings;
public int shares;
public int stocks;
public int house;
public int bonds;
public int car;
}


    Limitation

    Excel doesn't support Range Template cell type. Hence, it would be lost after saving to xlsx file.