[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

Shape Property Links

SpreadJS allows you to bind a shape's properties to a worksheet cell by reference in a formula. When the cell's content changes, the property referenced in the linked shape is updated dynamically.




The setFormula method accepts string parameters for a shape property such as text, height, width, and the referenced formula. It binds the shape’s property to the value according to the formula string.


SpreadJS also allows you to import or export an Excel file that includes formulas referenced to any shape or text box and retains the links connecting the shape to the referred cells.

Note: SpreadJS can only set the cell data to be either link text or normal text through the API.

Use Case Scenario

Consider a use-case scenario where a dyestuff chemical plant produces chemical wastewater as a byproduct every year. The information can be presented visually using shapes by dynamically linking the water units to the height of a cylinder.


This helps to easily convert the raw data to a presentable showcase as illustrated in the GIF below.




The following example code shows how to add a link between a cylinder shape and its corresponding cell. Every cell refers to the height of the cylinder to highlight the amount of wastewater produced.

spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
var sheet = spread.getSheet(0);
sheet.name("BuildInShape");
sheet.setArray(0, 0, [
    ["DyeStuff Chemical Industry Water Wastage Analysis"],
    ["Waste Water / Year", "Million Litre(s)"],
    [2017, 8.1],
    [2018, 9.5],
    [2019, 10.2],
    [2020, 11],
    [2021, 11.6]
]);

var shape1 = sheet.shapes.add("shape1", GC.Spread.Sheets.Shapes.AutoShapeType.can, 150, 100, 70, 50);
shape1.setFormula("height", "=BuildInShape!B3*15");
shape1.setFormula("text", "=BuildInShape!A3");
shape1.setFormula("y", "=221.5-BuildInShape!B3*15");

var shape2 = sheet.shapes.add("shape2", GC.Spread.Sheets.Shapes.AutoShapeType.can, 230, 78, 70, 50);
shape2.setFormula("height", "=BuildInShape!B4*15");
shape2.setFormula("text", "=BuildInShape!A4");
shape2.setFormula("y", "=221.5-BuildInShape!B4*15");

var shape3 = sheet.shapes.add("shape3", GC.Spread.Sheets.Shapes.AutoShapeType.can, 310, 65, 70, 50);
shape3.setFormula("height", "=BuildInShape!B5*15");
shape3.setFormula("text", "=BuildInShape!A5");
shape3.setFormula("y", "=221.5-BuildInShape!B5*15");

var shape4 = sheet.shapes.add("shape4", GC.Spread.Sheets.Shapes.AutoShapeType.can, 390, 52, 70, 50);
shape4.setFormula("height", "=BuildInShape!B6*15");
shape4.setFormula("text", "=BuildInShape!A6");
shape4.setFormula("y", "=221.5-BuildInShape!B6*15");

var shape5 = sheet.shapes.add("shape5", GC.Spread.Sheets.Shapes.AutoShapeType.can, 470, 40, 70, 50);
shape5.setFormula("height", "=BuildInShape!B7*15");
shape5.setFormula("text", "=BuildInShape!A7");
shape5.setFormula("y", "=221.5-BuildInShape!B7*15");