How to Use Shape Features in a JavaScript Spreadsheet Application

Enhance the appearance and presentation of your data, dashboards, and reports in your Javascript Application using the SpreadJS SHAPES features.

This feature, comprised of both built-in and custom shapes, gives you the power to enhance your spreadsheets and applications with data-driven graphics, flowcharts, Gantt charts, annotations, and all your shapes needs.

SpreadJS includes support for over 180 shapes, including many Excel shapes. SpreadJS also provides more advanced functionality using data-driven shapes. For example, you can dynamically generate the shape properties using data from a database or other data source to create interactive shapes, such as for a manufacturing plant floor.

Above is one example of how different shapes are used in SpreadJS to create great-looking flowcharts used by companies to show steps of the task, process, or workflow.

In this blog, we will walk into how we can make the most of this great feature, how we can add, modify, and customize SpreadJS shapes and use those to add value in the way we visualize the raw data in our javaScript applications.

Shapes Types

Build-in Shapes

You can seamlessly import and export your Excel workbooks with shapes without any feature loss with built-in shapes. The 60+ built-in shapes include basic shapes, arrows, balloons, and action buttons. You can quickly build diagrams and dashboards.

On SpreadJS designer, these shapes are separated into the below categories:

• Lines/Connectors: We can find various lines, arrows, connectors, etc.
• Rectangles: We can find multiple types of rectangles
• Basic Shapes: We can find different basic shapes like triangles, ovals, diamonds, etc.
• Block Arrows: We can find various arrows, including up, down, right, and left arrows
• Equation Shapes: This category contains shapes like plus, minus, multiply, divide, equal, and not equal
• Flowchart: Use shapes to create a unique flowchart
• Stars and Banners: We can find various stars and banner shapes
• Callouts: We can find numerous types of callouts like rectangular callouts, oval callouts, line callouts, etc.

Camera Screenshot

Camera shape is a feature that lets you create a dynamic snapshot of a specified range of cells–making it ideal for creating stunning dashboards. This shape will reflect all of the live changes made in the actual cell range.

It works like a normal shape and includes Excel import/export, grouping/ungrouping, copying/pasting, and cross-sheet support. This benefits observing changes in a spreadsheet, even when those changes aren't currently displayed, such as showing a dashboard or report with data in other sheets.

The camera tool allows you to take a snapshot of any selected range of data, table, or graph and paste it as a linked picture. The pasted image can be formatted using CameraShape API.

var shape = sheet.shapes.addCameraShape("myCameraShape1", "Sheet1!A1:D7", 240, 200); // Cells A!:D& contains the data you want to screenshot.
var shapeStyle = shape.style();
shapeStyle.fill.color = 'pink';
shapeStyle.fill.transparency = 0.5;
shapeStyle.line.color = 'green';
shapeStyle.line.lineStyle = GC.Spread.Sheets.Shapes.PresetLineDashStyle.dashDot;
shapeStyle.line.width = 5;
shape.style(shapeStyle);

Using of Camera Shapes:

• Creating of Neat Dashboards: With just a couple of lines of code, you can forget the headaches that come with getting all the elements sized correctly in your javascript dashboard. With the Camera shape, you can take pictures of various tables and charts and place the images together on a new worksheet
• Substituting Sparklines: With the camera tool, you can take a chart picture, then size the photo to fit into a cell. If the chart data changes, so do the image
• Visually checking Another Worksheet: If you want to track how a chart or a table of data changes based on inputs in a different worksheet, you can use the camera shape
• Pop-up Windows: Use the camera shape as an additional data widow and hide or show it using SpreadJS functions. This is great for dashboards. For example, clicking on a product number will display a product picture

SpreadJS allows the camera shapes to be quickly moved, resized, or rotated. Those can also be grouped or ungrouped with other shapes and copy-pasted from one sheet to another.

Custom Shapes

The most exciting feature of SpreadJS shapes is your ability to create your custom shapes. This brings SpreadJS a level above a spreadsheet. Now, more advanced diagrams and applications can be made. This includes things like seating charts, machine/electronic status monitoring, profit analysis, and organization charts, to name a few.

One of the powerful functions of custom shapes is generating them from data. Similar to charts, custom shape properties can be driven by specific data, whether defined in the code or defined as a cell range reference in the workbook.

This allows users to easily import your data and have shapes created for them in the form of developer-defined diagrams and dashboards.

A perfect example of this is our Car Insurance Sample, which you can read more about in the blog post Build a JavaScript car insurance app. In that sample, the data is in a separate sheet, and the shapes are defined using the data in the sheet:

While there isn't a custom shapes menu for the designer, they can be defined via code. In this fashion, you can define a model with different properties and then apply that model to the shape when added:

var shapeModel = {
    left: "=Sheet1!B7",
    top: "=Sheet1!C7",
    width: "=Sheet1!D7",
    height: "=Sheet1!E7",
    options: {
        fill: {
            type: 1,
            color: startColor,
            transparency: "0.5"
        }
    },
    path: [
        [
            ["A", 100,75,40,0,2*Math.PI],
            ["Z"]
        ]
    ]
};

sheet.shapes.add('MyShape', shapeModel);

There's so much more to be done with SpreadJS shapes than what we've covered here. SpreadJS can be a better fit for more types of applications with the added functionality, proving that SpreadJS is more than just a spreadsheet. Here are some ideas:

• Create a visual, interactive floor plan that allows users to see who sits at a desk or add information about amenities
• Create a production floor plan for a manufacturing facility, and highlight areas experiencing slowdowns or problems
• Create Gantt charts and Org charts

Shapes Customization

Movement and Rotation

You can customize the properties of all shapes using the ShapeBase API:

• allowMove: Gets or sets whether to disable moving the shape
• allowResize: Gets or sets whether to disable resizing the shape
• allowRotate: Gets or sets whether to disable rotating the shape
• showHandle: Gets or sets whether to show the handle of the shape
• isSelected: Gets or sets whether this shape is selected
• width/height: Gets or sets the width or height of the shape
• x/y: Gets or sets the horizontal or vertical location of the shape

For the basic shapes, you can also change the style and the rotation of the shape:

• rotate: Gets or sets the rotated angle of the shape (unit in degree)
• style: Gets or sets the style of the shape

Fill

By using SpreadJS, change the background properties of a shape. You can fill the shape with a color of your choosing and/or change the transparency property to the percentage you want. You can also add special fill effects to the shapes, such as solid fill, gradient fill, picture fill, and texture fill.

let style = shape.style();
style.fill = { type: GC.Spread.Sheets.Shapes.ShapeFillType.none };
//style.fill = { type: GC.Spread.Sheets.Shapes.ShapeFillType.solid, color: 'pink', transparency: 0.5 };
//style.fill = {
//   type: GC.Spread.Sheets.Shapes.GradientFillType.linear,
//   direction: GC.Spread.Sheets.Shapes.LinearGradientFillDirection.linearRight,
//   stops: [ {color: 'blue', position: 0 }, {color: 'pink', position: 1} ]
//};
//style.fill = { src: "data:image/jpeg;base64..." };
//style.fill = { src: "data:image/jpeg;base64...", tilePictureAsTexture: true };
shape.style(style);

Border

With SpreadJS, you can add borders to shapes and apply different formats to these borders, such as color, transparency, width, etc.

//basic shape border
    var rect = sheet.shapes.add('rect', GC.Spread.Sheets.Shapes.AutoShapeType.lineCallout2, 100, 50, 300, 200);
    var shapeStyle = rect.style();
    shapeStyle.line.compoundType = GC.Spread.Sheets.Shapes.CompoundType.thickThin;
    rect.style(shapeStyle);
//connector border
    var line = sheet.shapes.addConnector("myShape2", GC.Spread.Sheets.Shapes.ConnectorType.straight, 220, 120, 300, 120);
    shapeStyle = line.style();
    shapeStyle.line.compoundType = GC.Spread.Sheets.Shapes.CompoundType.triple;
    line.style(shapeStyle);

Note: The border property of a connector shape is essentially the whole background of that shape.

Shape Text

By using textEffect, and textFrame you will have the possibility to format the added text to your liking.

 var rectangle = sheet.shapes.add("Shape1", GC.Spread.Sheets.Shapes.AutoShapeType.rectangle, 50, 50, 100, 100);
   var rectangleStyle = rectangle.style();

   rectangleStyle.textEffect.color = 'yellow';
   rectangleStyle.textEffect.transparency = 0.5;
   rectangleStyle.textEffect.font = '20px Arial';
   rectangleStyle.textFrame.vAlign = GC.Spread.Sheets.VerticalAlign.center;
   rectangleStyle.textFrame.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
   rectangle.style(rectangleStyle);
   rectangle.text('This is a text!');

And if you want to edit the text of a shape, double-click on it.

Besides using the above javascript functions to add and format shapes, you can also use the associated Spread Designer included with the developer license. Refer to the documentation if you need to install the designer.

Below are some instructions on how the shapes are added or formatted in SpreadJS Designer.

In the "Insert" tab, you'll find a "Shapes" drop-down that shows all the available shapes for the user to add:

After having inserted the Shape, SpreadJS allows you to customize it. You can do that by right-clicking and then selecting "Format Shape".

On the designer, after clicking Format Shape, a panel is shown to the right of the sheet, and we can customize the shape. We can assign a color to the shape, a set of properties for the border of that shape, and various other properties like shape size, alignment, position, and movement.

You can right-click the shape and then select Edit Text or double-click inside the shape to add text in shape. SpreadJS gives the possibility to customize this text. The Text Options inside the Format Shape panel allow us to do that. We can assign the color of the text, font, or alignment. The screenshot below shows how the user can change the text properties.

The Format Shape panel can be used for the connector shapes as well. Below two screenshots are showing how we can change the color of the connectors, the end arrow type, width of the connector, etc.:

Other Shapes Enhancements

Grouping

SpreadJS supports individual grouping shapes into a shape group. This group shape is used to manage selecting and editing groups of shapes easily and quickly.

var shape1 = sheet.shapes.add("shape1", GC.Spread.Sheets.Shapes.AutoShapeType.heart, 100, 50, 100, 150);
var shape2 = sheet.shapes.addConnector("shape2", GC.Spread.Sheets.Shapes.ConnectorType.elbow, 200, 50, 300, 200);
var shapes = [shape1, shape2];
var groupShape = sheet.shapes.group(shapes)
sheet.shapes.ungroup(groupShape);

Using the above code, you can group/ungroup multiple shapes. SpreadJS makes it easy to fine-tune these multiple shape positions using shape alignment options, distribution options, or snapping them to other shapes or grids.

Below is the code to do that:

var commandManager = spread.commandManager();
commandManager.execute({
    cmd: 'moveShapesByHAlign', //moveShapesByVAlign, moveShapesByHDistribute, moveShapesByVDistribute
    sheetName: sheet.name(),
    shapeNames: ['rect1','line1'],
    alignment: GC.Spread.Sheets.Shapes.HorizontalAlign.left
});

Hyperlinks

SpreadJS allows you to add hyperlinks to shapes, connector shapes, or group shapes. The hyperlink can be a website URL, email address, or a SpreadJS sheet/cell reference, making it easy to navigate to almost any location.

 var myShape = sheet.shapes.add("shape", GC.Spread.Sheets.Shapes.AutoShapeType.myShape, 100, 50, 100, 150);
   myShape.hyperlink({url: "https://www.grapecity.com", target: 0, tooltip: 'goes to GrapeCity homepage'});

Property Formulas

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

var shape1 = sheet.shapes.add("shape1", GC.Spread.Sheets.Shapes.AutoShapeType.oval, 50, 50, 50, 50);
shape1.setFormula('style.fill.color', '=Sheet1!B2');// fills the shape with the color stated in cell B2
shape1.getFormula('style.fill.color');// returns 'Sheet1!B2'

Using this powerful feature, you can dynamically generate the shape properties using data from a database or other data source, having so many amazing interactive shapes in your JavaScript application.

Shape Selection Pane

Using our Designer component, users can benefit from the new Shape Selection Pane that enables them to quickly select shapes in a sheet, hide them, or show them.

To summarize what you read above, SpreadJS shapes are flexible and can help with changing the appearance and provide some functional and practical applications to your reports and dashboards. In three short points:

• You can use shape layering to build some awesome-looking dashboards
• Create button links using hyperlink functionality combined with shapes
• You can make the content and data within your shape dynamic by referring to dynamic cells within the formula bar

Contact us to learn more | Visit the SpreadJS page