SpreadJS 14
Features / Cells / Cell Types / Range Template Cell
In This Topic
    Range Template Cell
    In This Topic

    SpreadJS provides support for creating a range celltype that allows users to specify a cell range in the worksheet as a template.

    Usage Scenario

    Range celltype is a very powerful feature that enables users and developers to define a template of cell ranges as a single celltype and apply that template to a cell to load different types of data into the template (including multiple rows and/or columns). Once the celltype is applied to a cell or a cell range, then the range template will use the cell value (or values) as a data source to resolve the binding path of the specified range.

    Using range templates is beneficial especially in the following scenarios:

    How Range Templates Work?

    Creating range cell types and configuring range templates in spreadsheets allows users to modify the display mode of the batch fragments and tweak its appearance simply by changing the template. 

    A range-cell type can be set as a normal celltype (using GC.Spread.Sheets.Style). Users need to create an instance of a range celltype and this object can be used for resolving the data binding path and providing the range layout & cell style information for every cell in the specified range.

    The following images depict the workflow for a range celltype in a spreadsheet:

    Range celltype workflow


    Range celltype workflow 

    The following steps must be performed for creating a range template celltype in the worksheet:

    1. Create a Template - A template can be created by designing a range in the worksheet. To bind data to the template, you can use the setBindingPath() method.
    2. Configure Data - Next, users need to configure a DataSource in the worksheet to be binded to the template.
    3. Create & Apply Range Template Celltype - Now, create a RangeTemplate celltype using a range of cells from TemplateSheet and apply this RangeTemplate celltype in RenderSheet. RangeTemplate celltype can be set using the cellType() method of the CellRange class. Here, a range template celltype is created by resolving the binding path to include the template information (provided in Step 1) and then using the data source (provided in Step 2) to configure the celltype in a range.

    Using Code

    Refer to the following example code for creating and adding range template celltype in the worksheet.

    Copy Code
    // Initializing Spread
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
    // Get the activesheet as renderSheet
    var renderSheet = spread.getActiveSheet();
    // Step 1. Create a new Worksheet as templateSheet
    var templateSheet = new GC.Spread.Sheets.Worksheet();
    // This templateSheet is used to create a template for RangeTemplate CellType
    templateSheet.addSpan(0, 0, 1, 4);
    templateSheet.getCell(0, 0).font("20px Arial").hAlign(GC.Spread.Sheets.HorizontalAlign.center).vAlign(GC.Spread.Sheets.VerticalAlign.center);
    templateSheet.setBindingPath(0, 0, "name");
    templateSheet.setValue(1, 0, "Asset Type");
    templateSheet.setValue(1, 1, "Amount");
    templateSheet.setValue(1, 2, "Diagram");
    templateSheet.setValue(1, 3, "Note");
    templateSheet.setValue(2, 0, "Savings");
    templateSheet.setBindingPath(2, 1, "savings");
    templateSheet.setValue(3, 0, "Shares");
    templateSheet.setBindingPath(3, 1, "shares");
    templateSheet.setValue(4, 0, "Stocks");
    templateSheet.setBindingPath(4, 1, "stocks");
    templateSheet.setValue(5, 0, "House");
    templateSheet.setBindingPath(5, 1, "house");
    templateSheet.setValue(6, 0, "Bonds");
    templateSheet.setBindingPath(6, 1, "bonds");
    templateSheet.setValue(7, 0, "Car");
    templateSheet.setBindingPath(7, 1, "car");
    templateSheet.setFormatter(-1, 1, "$#,##0");
    templateSheet.addSpan(2, 2, 6, 1);
    templateSheet.addSpan(8, 1, 1, 3);
    templateSheet.setValue(8, 0, "Total");
    templateSheet.getCell(8, 0).font("14px Arial");
    templateSheet.setFormula(8, 1, "=SUM(B3:B8)");
    templateSheet.getCell(8, 1).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
    templateSheet.setFormula(2, 2, '=PIESPARKLINE(B3:B8,"#919F81","#D7913E","CEA722","#B58091","#8974A9","#728BAD")');
    templateSheet.getCell(2, 3).backColor("#919F81").formula("=B3/$B$9");
    templateSheet.getCell(3, 3).backColor("#D7913E").formula("=B4/$B$9");
    templateSheet.getCell(4, 3).backColor("#CEA722").formula("=B5/$B$9");
    templateSheet.getCell(5, 3).backColor("#B58091").formula("=B6/$B$9");
    templateSheet.getCell(6, 3).backColor("#8974A9").formula("=B7/$B$9");
    templateSheet.getCell(7, 3).backColor("#728BAD").formula("=B8/$B$9");
    templateSheet.setFormatter(-1, 3, "0.00%");
    templateSheet.setRowHeight(0, 50);
    for (var i = 1; i < 8; i++) {
         templateSheet.setRowHeight(i, 25);
    templateSheet.setColumnWidth(0, 100);
    templateSheet.setColumnWidth(1, 100);
    templateSheet.setColumnWidth(2, 200);
    // Step 2. Configure & Add Data to renderSheet
    // Configure Data
    var data = [{
         "name": "Peyton's Assets",
         "savings": 25000,
         "shares": 55000,
         "stocks": 15000,
         "house": 250000,
         "bonds": 11000,
         "car": 7500
    }, {
         "name": "Icey's Assets",
         "savings": 30000,
         "shares": 45000,
         "stocks": 25000,
         "house": 20000,
         "bonds": 18000,
         "car": 75000
    }, {
         "name": "Walter's Assets",
         "savings": 20000,
         "shares": 4000,
         "stocks": 95000,
         "house": 30000,
         "bonds": 10000,
         "car": 56000
    }, {
         "name": "Chris's Assets",
         "savings": 70000,
         "shares": 85000,
         "stocks": 35000,
         "house": 20000,
         "bonds": 15000,
         "car": 45000
    // Add Data to renderSheet
    renderSheet.setValue(0, 0, data[0]);
    renderSheet.setValue(0, 1, data[1]);
    renderSheet.setValue(1, 0, data[2]);
    renderSheet.setValue(1, 1, data[3]);
    // Step 3. Create RangeTemplate CellType using range of cells[0,0,9,4] from templateSheet
    var rangeTemplateCelltype = new GC.Spread.Sheets.CellTypes.RangeTemplate(templateSheet, 0, 0, 9, 4);
    // Apply RangeTemplate CellType to a range of cells in renderSheet
    renderSheet.getRange(0, 0, 2, 2).cellType(rangeTemplateCelltype);
    renderSheet.setColumnWidth(0, 400);
    renderSheet.setRowHeight(0, 250);
    renderSheet.setColumnWidth(1, 400);
    renderSheet.setRowHeight(1, 250);