Shape Property Formulas

SpreadJS allows users to add built-in shapes and custom shapes with formulas in the worksheets in order to enable modifications in the shape model.

Benefits of using Data-Driven Shapes

This feature allows users to create dynamic shapes whose properties are evaluated through the expressions in a spreadsheet, another shape, or a data source. It enables shape data binding with these expressions.

Let's take a look at a few scenarios where this feature is particularly helpful:

When users want to create a flowchart that can be dynamically updated based on the formulas in a spreadsheet
When users want to maintain a hierarchical chart of their company's employees with the same shape background for employees at the same designation
When users want to update the sales revenue in a shape every time its value is updated in the spreadsheet

In order to add shapes with formula equations, refer to the following tasks:

Adding Built-in Shapes with Formulas

Users can add built-in shapes with formulas in the worksheets. An example of a shape created using built-in formula is shown below.

The following code sample shows how to add a shape with the built-in formula added to the worksheet.

 // Add built-in shape with formula
 window.onload = function ()
{
  var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
  var sheet = spread.getActiveSheet();
  sheet.name("BuiltInShape");
  sheet.setArray(0, 0, [
  ["x", 430], ["y", 25],
  ["width", 280], ["height", 160],
  ["angle", 0], ["background color and tranparency", "green", 0.5],
  ["border color and width", "blue", 0],
  ["shape text", "The demo text for built-in shape"],
  ["text font", "18px Georgia"],
  ["text color", "white"],
  ]);
  sheet.setColumnWidth(0, 220);
  sheet.setColumnWidth(1, 100);
  sheet.setColumnWidth(2, 70);
  sheet.setColumnWidth(3, 70);
  sheet.setColumnWidth(4, 70);
  sheet.setColumnWidth(5, 70);;
  var shape1 = sheet.shapes.add("shape1",
  GC.Spread.Sheets.Shapes.AutoShapeType.oval, 100, 50, 100, 150);
  shape1.x("=BuiltInShape!B1");
  shape1.y("=BuiltInShape!B2");
  shape1.width("=BuiltInShape!B3");
  shape1.height("=BuiltInShape!B4");
  shape1.rotate("=BuiltInShape!B5");
  shape1.text("=BuiltInShape!B8");
  var shape1Style = shape1.style();
  shape1Style.fill.color = "=BuiltInShape!B6";
  shape1Style.fill.transparency = "=BuiltInShape!C6";
  shape1Style.line.color = "=BuiltInShape!B7";
  shape1Style.line.transparency = "=BuiltInShape!C7";
  shape1Style.textEffect.font = "=BuiltInShape!B9";
  shape1Style.textEffect.color = "=BuiltInShape!B10";
  shape1.style(shape1Style);
 };

Users can also get the formula defined for any attribute of the shape as well as the value referenced by the formula for that particular attribute. For example, in the above built-in shape, the formula for the height of shape is defined as "=BuiltInShape!B4" whereas the value of height is defined as 160 in cell B4.

The following code sample shows how to get the formula using the getFormula method and value for an attribute of built-in shape.

// get the activesheet
var activeSheet = spread.getSheet(0);
activeSheet.setArray(0, 0, [
    ["x", 10],
    ["y", 200],
    ["width", 300],
    ["height", 140],
    ["angle", 0],
    ["background color and tranparency", "red", 0.5],
    ["border color and width", "blue", 5],
    ["shape text", "The demo text for cloud shape"],
    ["text font", "15px Georgia"],
    ["text color", "Yellow"],
]);

var shape1 = activeSheet.shapes.add("shape1", GC.Spread.Sheets.Shapes.AutoShapeType.cloud, 50, 200, 100, 150);
var shapeStyle = shape1.style();
shapeStyle.fill.color = '=Sheet1!B6';
shape1.style(shapeStyle);

// Set width and height of shape using setFormula() method
shape1.setFormula("x", "=Sheet1!B1");
shape1.setFormula("y", "=Sheet1!B2");
shape1.setFormula("width", "=Sheet1!B3");
shape1.setFormula("height", "=Sheet1!B4");
shape1.setFormula("rotate", "=Sheet1!B5");
shape1.setFormula("text", "=Sheet1!B8");
shape1.setFormula("style.fill.color", "=Sheet1!B6");
shape1.setFormula("style.fill.transparency", "=Sheet1!C6");
shape1.setFormula("style.line.color", "=Sheet1!B7");
shape1.setFormula("style.line.width", "=Sheet1!C7");
shape1.setFormula("style.textEffect.font", "=Sheet1!B9");
shape1.setFormula("style.textEffect.color", "=Sheet1!B10");

//Use getFormula() method to return the formula used to set width & height of shape
console.log("Formula for Width : " + shape1.getFormula("width"));
console.log("Formula for Height : " + shape1.getFormula("height"));

//Use the attributes of shape to return the value of width and height of shape
console.log("Value of Width: ", shape1.width());
console.log("Value of Height: ", shape1.height());

activeSheet.setColumnWidth(0, 280);
activeSheet.setColumnWidth(1, 100);
for (var i = 2; i < 8; i++)
    activeSheet.setColumnWidth(i, 70);

Adding Custom Shapes with Formulas

Users can add custom shapes with formulas in the worksheets. An example of a shape created using the custom formula equation is shown below

The following code sample shows how to add a shape with custom formula added to the worksheet.

// Add custom shape with formula
window.onload = function ()
{
   var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
   var sheet = spread.getActiveSheet();
   sheet.name("CustomShape");
   sheet.setArray(0, 0, [
       ["left", 480], ["top", 60], ["width", 400], ["height", 240],["angle"],
       ["background color and tranparency", "green", 0.5],
       ["border color and width", "blue", 0],
       ["shape text", "The demo text for custom shape"],
       ["text font", "15px Georgia"],
       ["text color", "red"],
       ["margins", 1, 2, 3, 4],
       ["horizontalAlignment", 1],
       ["verticalAlignment", 1],
       ["textDirection", "horizontal"],
       ["allowTextToOverflowShape", false],
       ["wrapTextInShape", true],
       ["line width", 3],
       ["line style", 5, "capType", 2, "joinType", 1],
       ["endPoints", 1, 1, 1, 5, 2, 2],
   ]);
   sheet.setColumnWidth(0, 280);
   sheet.setColumnWidth(1, 100);
   sheet.setColumnWidth(2, 70);
   sheet.setColumnWidth(3, 70);
   sheet.setColumnWidth(4, 70);
   sheet.setColumnWidth(5, 70);
   sheet.setCellType(11, 1,
   createComboCellType(GC.Spread.Sheets.HorizontalAlign, 2));
   sheet.setCellType(12, 1, createComboCellType(GC.Spread.Sheets.VerticalAlign));
   sheet.setCellType(17, 1,
   createComboCellType(GC.Spread.Sheets.Shapes.PresetLineDashStyle));
   sheet.setCellType(17, 3, createComboCellType(GC.Spread.Sheets.Shapes.LineCapStyle));
   sheet.setCellType(17, 5,
   createComboCellType(GC.Spread.Sheets.Shapes.LineJoinStyle));
   sheet.setCellType(18, 1,
   createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadStyle));
   sheet.setCellType(18, 4,
   createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadStyle));
   sheet.setCellType(18, 2,
   createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadLength));
   sheet.setCellType(18, 5,
   createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadLength));
   sheet.setCellType(18, 3,
   createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadWidth));
   sheet.setCellType(18, 6,
   createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadWidth));
   sheet.setFormula(4, 1, "=ROW(CustomShape!B10)");
var model =
{
    left: "=CustomShape!B1",
    top: "=CustomShape!B2",
    width: "=CustomShape!B3",
    height: "=CustomShape!B4",
    angle: "=CustomShape!B5",
    options: {
        endPoints: {
        beginArrow:
        {
            type: "=CustomShape!B19", widthType:
            "=CustomShape!C19", lengthType: "=CustomShape!D19"
        },
        endArrow: { type: "=CustomShape!E19", widthType:
        "=CustomShape!F19", lengthType: "=CustomShape!G19" }
        },
        fill:
        {
        type: 1, // solid fill (now only support solid fill)
        color: "=CustomShape!B6",
        transparency: "=CustomShape!C6"
        },
        stroke:
        {
        type: 1, // solid fill (now only support solid fill)
        color: "=CustomShape!B7",
        transparency: "=CustomShape!C7",
        width: "=CustomShape!B17",
        lineStyle: "=CustomShape!B18",
        capType: "=CustomShape!D18",
        joinType: "=CustomShape!F18"
        },
        textFormatOptions:
        {
            text: "=CustomShape!B8", // "Shape Text",
            font: "=CustomShape!B9", // "bold 15px Georgia
            fill:
            {
                type: 1, // solid fill (now only support solid fill)
                color: "=CustomShape!B10"
            },
            margins:
            {
                left: "=CustomShape!B11",
                top: "=CustomShape!C11",
                right: "=CustomShape!D11",
                bottom: "=CustomShape!E11"
            },
            verticalAlignment:
            "=CustomShape!B13",  // (0: top, 1: center, 2: bottom)
            horizontalAlignment:
            "=CustomShape!B12", // (0: left, 1: center, 2: right)
            textDirection:
            "=CustomShape!B14", //f "vertical", "rotate90", "rotate270"
            allowTextToOverflowShape: "=CustomShape!B15",
            wrapTextInShape: "=CustomShape!B16"
        }
    },
    variables: {
        xOffset: 40,
        yOffset: 10
    },
    path: [[
            ["M", "=controls.0.x", 0],          // M: move to (x, y)
            ["L", "=width - controls.0.x", 0],  // L: line to (x, y)
            ["L", "=width - 2 * variables.xOffset", "=height"], ["L", "=variables.xOffset", "=height"],
            ["Z"]
        ],  // Z: close path
        [
            ["M", "=width - variables.xOffset", "=variables.yOffset"],
            ["L", "=width", "=variables.yOffset"],
            ["L", "=width", "=height - 4 * variables.yOffset"],
            ["L", "=width - variables.xOffset", "=height"]
        ]
    ],
    controls: [
        {
            x: "=BOUND(0.3*width, 0, false, 0, 0.5*width)", // formula used to provide position and range limitation (here default at position (0, 0.2 * width), and the y range from 0 to 0.5*width)
            y: 0,
            xBehavior: 0,  // 0 if adjust in x (horizontal), otherwise 1
            yBehavior: 1   // 0 if adjust in y (vertical), otherwise 1
        }
    ],
    connectionPoints: [
        {
            x: "=0.5*width",
            y: 0
        },
        {
            x: "=0.5*controls.0.x",
            y: "=0.5*height"
        },
        {
            x: "=0.5*width",
            y: "=1*height"
        },
        {
            x: "=width-0.5*controls.0.x",
            y: "=0.5*height"
        }
    ],
    textRect:
    { left: "=controls.0.x", top: 20, bottom: "=height - 20",
    right: "=width - variables.xOffset" }};
    sheet.shapes.add('shape2', model);
};
function createComboCellType(enumType, max)
{
    var combo = new GC.Spread.Sheets.CellTypes.ComboBox();
    var items = [];
    for (var name in enumType)
    {
        var value = enumType[name];
        if (!max || value <= max)
        {
            items.push
            ({
                text: name,
                value: value
            });
        }
    }
    combo.items(items);
    combo.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value);
    return combo;
}

Note: The Shape API set will accept formulas or values just like the custom option, but will always return the corresponding value. The range reference is required in sheetName!A10 for A1 style and the context-dependence formula is supported (e.g ROW(Sheet1!B30)).

Users can also get the formula defined for any attribute of the shape as well as the value referenced by the formula for that particular attribute. For example, in the above custom shape, the formula for the height of shape is defined as "=CustomShape!B4" whereas the value of height is defined as 240 in cell B4.

The following code sample shows how to get the formula using the getFormula method and value for an attribute of custom shape.

// get the activesheet
var activeSheet = spread.getSheet(0);
activeSheet.name("CustomShape");
// Set Data
activeSheet.setArray(0, 0, [
    ["left", 480],
    ["top", 60],
    ["width", 300],
    ["height", 240],
    ["angle"],
    ["background color and tranparency", "green", 0.5],
    ["border color and width", "blue", 0],
    ["shape text", "The demo text for custom shape"],
    ["text font", "15px Georgia"],
    ["text color", "red"],
    ["margins", 1, 2, 3, 4],
    ["horizontalAlignment", 1],
    ["verticalAlignment", 1],
    ["textDirection", "horizontal"],
    ["allowTextToOverflowShape", false],
    ["wrapTextInShape", true],
    ["line width", 3],
    ["line style", 5, "capType", 2, "joinType", 1],
    ["endPoints", 1, 1, 1, 5, 2, 2],
]);
activeSheet.setFormula(4, 1, "=ROW(CustomShape!B10)");
var model = {
    left: "=CustomShape!B1",
    top: "=CustomShape!B2",
    width: "=CustomShape!B3",
    height: "=CustomShape!B4",
    angle: "=CustomShape!B5",
    options: {
        endPoints: {
            beginArrow: {
                type: "=CustomShape!B19", widthType: "=CustomShape!C19", lengthType: "=CustomShape!D19"
            },
            endArrow: { type: "=CustomShape!E19", widthType: "=CustomShape!F19", lengthType: "=CustomShape!G19" }
        },
        fill: {
            type: 1, // solid fill (now only support solid fill)
            color: "=CustomShape!B6",
            transparency: "=CustomShape!C6"
        },
        stroke: {
            type: 1, // solid fill (now only support solid fill)
            color: "=CustomShape!B7",
            transparency: "=CustomShape!C7",
            width: "=CustomShape!B17",
            lineStyle: "=CustomShape!B18",
            capType: "=CustomShape!D18",
            joinType: "=CustomShape!F18"
        },
        textFormatOptions: {
            text: "=CustomShape!B8", // "Shape Text",
            font: "=CustomShape!B9", // "bold 15px Georgia",  // css font, zoom related code should be update to support it
            fill: {
                type: 1, // solid fill (now only support solid fill)
                color: "=CustomShape!B10"
            },
            margins: {
                left: "=CustomShape!B11",
                top: "=CustomShape!C11",
                right: "=CustomShape!D11",
                bottom: "=CustomShape!E11"
            },
            verticalAlignment: "=CustomShape!B13",  // (0: top, 1: center, 2: bottom)
            horizontalAlignment: "=CustomShape!B12", // (0: left, 1: center, 2: right)
            textDirection: "=CustomShape!B14", //f "vertical", "rotate90", "rotate270"
            allowTextToOverflowShape: "=CustomShape!B15",
            wrapTextInShape: "=CustomShape!B16"
        }
    },
    variables: {
        xOffset: 40,
        yOffset: 10
    },
    path: [[
        ["M", "=controls.0.x", 0],          // M: move to (x, y)
        ["L", "=width - controls.0.x", 0],  // L: line to (x, y)
        ["L", "=width - 2 * variables.xOffset", "=height"], ["L", "=variables.xOffset", "=height"],
        ["Z"]],  // Z: close path
    [
        ["M", "=width - variables.xOffset", "=variables.yOffset"],
        ["L", "=width", "=variables.yOffset"],
        ["L", "=width", "=height - 4 * variables.yOffset"],
        ["L", "=width - variables.xOffset", "=height"]
    ]
    ],
    controls: [
        {
            // formula used to provide position and range limitation (here default at position (0, 0.2 * width),
            //and the y range from 0 to 0.5* width)
            x: "=BOUND(0.3*width, 0, false, 0, 0.5*width)",
            y: 0,
            xBehavior: 0,  // 0 if adjust in x (horizontal), otherwise 1
            yBehavior: 1   // 0 if adjust in y (vertical), otherwise 1
        }
    ],
    connectionPoints: [
        {
            x: "=0.5*width",
            y: 0
        },
        {
            x: "=0.5*controls.0.x",
            y: "=0.5*height"
        },
        {
            x: "=0.5*width",
            y: "=1*height"
        },
        {
            x: "=width-0.5*controls.0.x",
            y: "=0.5*height"
        }
    ],
    textRect: { left: "=controls.0.x", top: 20, bottom: "=height - 20", right: "=width - variables.xOffset" }
};
var shape2 = activeSheet.shapes.add('shape2', model);

//Use getFormula() method to return the formula used to set width and height of custom shape
console.log("Width's Formula is: " + shape2.getFormula("width"));
console.log("Height's Formula is: " + shape2.getFormula("height"));

//Use the attributes of custom shape to return the value of width and height of shape
console.log("Width value ", shape2.width());
console.log("Height value ", shape2.height());


// set column widths
activeSheet.setColumnWidth(0, 210);
activeSheet.setColumnWidth(1, 60);
for (var i = 2; i < 6; i++) {
    activeSheet.setColumnWidth(i, 50);
}