SpreadJS 14
Features / Shapes / Shape Property Formulas
In This Topic
    Shape Property Formulas
    In This Topic

    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:

    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.

    Built-in shape with formula

    Using Code

    This example shows how to add a shape with the built-in formula added to the worksheet.

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

    Using Code

    This example shows how to get the formula using the getFormula method and value for an attribute of built-in shape.

    JavaScript
    Copy Code
    // 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

    Custom shape with formula

    Using Code

    This example shows how to add a shape with custom formula added to the worksheet.

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

    Using Code

    This example shows how to get the formula using the getFormula method and value for an attribute of custom shape.

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