形状属性公式

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

使用数据驱动形状的好处

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

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

• 当用户要创建流程图时，可以根据表单中的公式进行动态更新。
• 当用户想要以相同的名称为雇员维护具有相同形状背景的公司雇员的层次结构图时。
• 当用户每次在表单中更新其值时，想要更新形状中的销售收入时。

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

• 使用公式添加内置形状
• 使用公式添加自定义形状

使用公式添加内置形状

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

示例代码

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

// 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 方法和内置形状的属性的值获取公式。

// 获取活跃表单
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);

使用公式添加自定义形状

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

示例代码

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

// 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 方法和自定义形状的属性的值获取公式。

// 获取活跃表单
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);
}