SpreadJS 文档
SpreadJS 文档 / 开发者指南 / 特性 / 管理数据可视化和对象 / 形状 / 形状属性公式
在本主题中
    形状属性公式
    在本主题中

    SpreadJS允许用户在工作表中添加带有公式的内置形状和自定义形状,以便在形状模型中进行修改。

    使用数据驱动形状的好处

    此功能允许用户创建动态形状,这些形状的属性通过表单中的表达式,另一个形状或数据源进行评估。 它使形状数据可以与这些表达式绑定。

    让我们看一下该功能特别有用的几种情况:

    为了使用公式方程式添加形状,请参考以下:

    使用公式添加内置形状

    用户可以在工作表中添加带有公式的内置形状。 使用内置公式创建的形状示例如下所示。

    示例代码

    本示例说明如何使用添加到工作表中的内置公式添加形状。

    JavaScript
    复制代码
     // 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);
     };
    

    用户还可以获得为形状的任何属性定义的公式以及该公式为该特定属性所引用的值。 例如,在上述内置形状中,形状高度的公式在单元格B4中定义为“ = BuiltInShape!B4” ,而高度的值定义为160。

    示例代码

    本示例说明如何使用 getFormula 方法和内置形状的属性的值获取公式。

    JavaScript
    复制代码
    // 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);
    

     

    使用公式添加自定义形状

    用户可以在工作表中添加带有公式的自定义形状。 使用自定义公式方程式创建的形状示例如下所示

    示例代码

    此示例显示了如何使用添加到工作表的自定义公式添加形状。

    JavaScript
    复制代码
     // 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;
    }
    

    注意: Shape API集将像自定义选项一样接受公式或值,但将始终返回相应的值。 对于A1样式,在sheetName!A10中需要范围引用,并且支持上下文依赖关系公式(例如ROW(Sheet1!B30)。

    用户还可以获得为形状的任何属性定义的公式以及该公式为该特定属性所引用的值。 例如,在上述自定义形状中,形状高度的公式定义为“ = CustomShape!B4” ,而单元格B4中的高度值定义为240。

    示例代码

    本示例说明如何使用 getFormula 方法和自定义形状的属性的值获取公式。

    JavaScript
    复制代码
    // 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);
    }