Shapes With Formulas

Whether it's a built-in shape or a custom shape, you can use formulas to change individual properties of the shape.

For example: you can change a built-in shape's background color using the following code: Also, if the formula you link to the text property is a cell reference like "=Sheet1!A1", the text link can be exported to EXCEL.
window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initBuildShapeFormula(spread); initCustomShapeFormula(spread); }; function initBuildShapeFormula(spread) { var sheet = spread.getSheet(0); sheet.name("BuildInShape"); sheet.setArray(0, 0, [ ["Properties", "Values"], ["x", 400], ["y", 20], ["width", 200], ["height", 200], ["angle", 0], ["background color", "orange"], ["background tranparency", 0.5], ["border color", "gray"], ["border width", 2], ["shape text", "BUILt-IN Shape"], ["text font", "15px Georgia"], ["text color", "black"], ]); sheet.setColumnWidth(0, 200); sheet.setColumnWidth(1, 150); sheet.getRange(-1,1,0,1).hAlign(GC.Spread.Sheets.HorizontalAlign.left); var shape1 = sheet.shapes.add("shape1", GC.Spread.Sheets.Shapes.AutoShapeType.rightTriangle, 400, 20, 200, 200); shape1.setFormula("x", "=BuildInShape!B2"); shape1.setFormula("y", "=BuildInShape!B3"); shape1.setFormula("width", "=BuildInShape!B4"); shape1.setFormula("height", "=BuildInShape!B5"); shape1.setFormula("rotate", "=BuildInShape!B6"); shape1.setFormula("text", "=BuildInShape!B11"); shape1.setFormula("style.fill.color", "=BuildInShape!B7"); shape1.setFormula("style.fill.transparency", "=BuildInShape!B8"); shape1.setFormula("style.line.color", "=BuildInShape!B9"); shape1.setFormula("style.line.width", "=BuildInShape!B10"); shape1.setFormula("style.textEffect.font", "=BuildInShape!B12"); shape1.setFormula("style.textEffect.color", "=BuildInShape!B13"); sheet.getRange(0, 0,1,2).font("17px Arial").vAlign(GC.Spread.Sheets.VerticalAlign.center).backColor("gray").foreColor("white"); } 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; } function initCustomShapeFormula(spread) { var sheet = spread.getSheet(1); sheet.name("CustomShape"); sheet.setArray(0, 0, [ ["General Properties"], ["left", 600], ["top", 60], ["width", 400], ["height", 240], ["angle",10], ["background color","#82BC00"], ["background tranparency", 0.3], ["border color", "orange"], ["border width", 3], ["Text Properties"], ["shape text", "CUSTOM Shape"], ["text font", "20px Georgia"], ["text color", "white"], ["margins", 1, 2, 30, 0], ["horizontalAlignment", 1], ["verticalAlignment", 1], ["textDirection", "horizontal"], ["allowTextToOverflowShape", false], ["wrapTextInShape", true], ["Line Properties"], ["line width", 3], ["line style", 5], ["capType", 2], ["joinType", 1], ["beginArrow", 1, 1, 1], ["endArrow", 5, 2, 2], ]); sheet.setColumnWidth(0, 280); sheet.setColumnWidth(1, 100); sheet.setColumnWidth(2, 70); sheet.setColumnWidth(3, 70); sheet.setColumnWidth(4, 70); sheet.getRange(-1,1,0,5).hAlign(GC.Spread.Sheets.HorizontalAlign.left); sheet.setCellType(15, 1, createComboCellType(GC.Spread.Sheets.HorizontalAlign, 3)); sheet.setCellType(16, 1, createComboCellType(GC.Spread.Sheets.VerticalAlign,2)); sheet.setCellType(22, 1, createComboCellType(GC.Spread.Sheets.Shapes.PresetLineDashStyle,10)); sheet.setCellType(23, 1, createComboCellType(GC.Spread.Sheets.Shapes.LineCapStyle,2)); sheet.setCellType(24, 1, createComboCellType(GC.Spread.Sheets.Shapes.LineJoinStyle,2)); sheet.setCellType(25, 1, createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadStyle,5)); sheet.setCellType(26, 1, createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadStyle,5)); sheet.setCellType(25, 2, createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadLength,2)); sheet.setCellType(26, 2, createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadLength,2)); sheet.setCellType(25, 3, createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadWidth,2)); sheet.setCellType(26, 3, createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadWidth,2)); sheet.getRange(0, 0,1,2).font("17px Arial").vAlign(GC.Spread.Sheets.VerticalAlign.center).backColor("gray").foreColor("white"); sheet.getRange(10, 0,1,5).font("17px Arial").vAlign(GC.Spread.Sheets.VerticalAlign.center).backColor("gray").foreColor("white"); sheet.getRange(20, 0,1,4).font("17px Arial").vAlign(GC.Spread.Sheets.VerticalAlign.center).backColor("gray").foreColor("white"); var model = { left: "=CustomShape!B2", top: "=CustomShape!B3", width: "=CustomShape!B4", height: "=CustomShape!B5", angle: "=CustomShape!B6", //"=ROW(BuildInShape!B10)", options: { endPoints: { beginArrow: { type: "=CustomShape!B26", widthType: "=CustomShape!C26", lengthType: "=CustomShape!D26" }, endArrow: { type: "=CustomShape!B27", widthType: "=CustomShape!C27", lengthType: "=CustomShape!D27" } }, fill: { type: 1, // solid fill (now only support solid fill) color: "=CustomShape!B7", transparency: "=CustomShape!B8" }, stroke: { type: 1, // solid fill (now only support solid fill) color: "=CustomShape!B9", transparency: "=CustomShape!B8", width: "=CustomShape!B10", lineStyle: "=CustomShape!B23", capType: "=CustomShape!B24", joinType: "=CustomShape!B25" }, textFormatOptions: { text: "=CustomShape!B12", // "Shape Text", font: "=CustomShape!B13", // "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!B14" }, margins: { left: "=CustomShape!B15", top: "=CustomShape!C15", right: "=CustomShape!D15", bottom: "=CustomShape!E15" }, verticalAlignment: "=CustomShape!B17", // (0: top, 1: center, 2: bottom) horizontalAlignment: "=CustomShape!B16", // (0: left, 1: center, 2: right) textDirection: "=CustomShape!B18", //f "vertical", "rotate90", "rotate270" allowTextToOverflowShape: "=CustomShape!B19", wrapTextInShape: "=CustomShape!B20" } }, 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); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> <div class="options-container"> <div class="option-row"> Try editing the data in the Spread instance to see how it affects the properties of the shape. </div> </div> </div></body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 5px; margin-top: 5px; }