Work with shapes using the Excel JavaScript API
Excel defines shapes as any object that sits on the drawing layer of Excel. That means anything outside of a cell is a shape. This article describes how to use geometric shapes, lines, and images in conjunction with the Shape and ShapeCollection APIs. Charts are covered in their own article, Work with charts using the Excel JavaScript API.
The following image shows shapes which form a thermometer.
Create shapes
Shapes are created through and stored in a worksheet's shape collection (Worksheet.shapes). ShapeCollection has several .add* methods for this purpose. All shapes have names and IDs generated for them when they are added to the collection. These are the name and id properties, respectively. name can be set by your add-in for easy retrieval with the ShapeCollection.getItem(name) method.
The following types of shapes are added using the associated method.
| Shape | Add Method | Signature |
|---|---|---|
| Geometric Shape | addGeometricShape | addGeometricShape(geometricShapeType: Excel.GeometricShapeType): Excel.Shape |
| Image (either JPEG or PNG) | addImage | addImage(base64ImageString: string): Excel.Shape |
| Line | addLine | addLine(startLeft: number, startTop: number, endLeft: number, endTop: number, connectorType?: Excel.ConnectorType): Excel.Shape |
| SVG | addSvg | addSvg(xml: string): Excel.Shape |
| Text Box | addTextBox | addTextBox(text?: string): Excel.Shape |
Geometric shapes
A geometric shape is created with ShapeCollection.addGeometricShape. That method takes a GeometricShapeType enum as an argument.
The following code sample creates a 150x150-pixel rectangle named "Square" that is positioned 100 pixels from the top and left sides of the worksheet.
// This sample creates a rectangle positioned 100 pixels from the top and left sides
// of the worksheet and is 150x150 pixels.
await Excel.run(async (context) => {
let shapes = context.workbook.worksheets.getItem("MyWorksheet").shapes;
let rectangle = shapes.addGeometricShape(Excel.GeometricShapeType.rectangle);
rectangle.left = 100;
rectangle.top = 100;
rectangle.height = 150;
rectangle.width = 150;
rectangle.name = "Square";
await context.sync();
});
Images
JPEG, PNG, and SVG images can be inserted into a worksheet as shapes. The ShapeCollection.addImage method takes a base64-encoded string as an argument. This is either a JPEG or PNG image in string form. ShapeCollection.addSvg also takes in a string, though this argument is XML that defines the graphic.
The following code sample shows an image file being loaded by a FileReader as a string. The string has the metadata "base64," removed before the shape is created.
// This sample creates an image as a Shape object in the worksheet.
let myFile = document.getElementById("selectedFile");
let reader = new FileReader();
reader.onload = (event) => {
Excel.run(function (context) {
let startIndex = reader.result.toString().indexOf("base64,");
let myBase64 = reader.result.toString().substr(startIndex + 7);
let sheet = context.workbook.worksheets.getItem("MyWorksheet");
let image = sheet.shapes.addImage(myBase64);
image.name = "Image";
return context.sync();
}).catch(errorHandlerFunction);
};
// Read in the image file as a data URL.
reader.readAsDataURL(myFile.files[0]);
Lines
A line is created with ShapeCollection.addLine. That method needs the left and top margins of the line's start and end points. It also takes a ConnectorType enum to specify how the line contorts between endpoints. The following code sample creates a straight line on the worksheet.
// This sample creates a straight line from [200,50] to [300,150] on the worksheet.
await Excel.run(async (context) => {
let shapes = context.workbook.worksheets.getItem("MyWorksheet").shapes;
let line = shapes.addLine(200, 50, 300, 150, Excel.ConnectorType.straight);
line.name = "StraightLine";
await context.sync();
});
Lines can be connected to other Shape objects. The connectBeginShape and connectEndShape methods attach the beginning and ending of a line to shapes at the specified connection points. The locations of these points vary by shape, but the Shape.connectionSiteCount can be used to ensure your add-in does not connect to a point that's out-of-bounds. A line is disconnected from any attached shapes using the disconnectBeginShape and disconnectEndShape methods.
The following code sample connects the "MyLine" line to two shapes named "LeftShape" and "RightShape".
// This sample connects a line between two shapes at connection points '0' and '3'.
await Excel.run(async (context) => {
let shapes = context.workbook.worksheets.getItem("MyWorksheet").shapes;
let line = shapes.getItem("MyLine").line;
line.connectBeginShape(shapes.getItem("LeftShape"), 0);
line.connectEndShape(shapes.getItem("RightShape"), 3);
await context.sync();
});
Move and resize shapes
Shapes sit on top of the worksheet. Their placement is defined by the left and top property. These act as margins from worksheet's respective edges, with [0, 0] being the upper-left corner. These can either be set directly or adjusted from their current position with the incrementLeft and incrementTop methods. How much a shape is rotated from the default position is also established in this manner, with the rotation property being the absolute amount and the incrementRotation method adjusting the existing rotation.
A shape's depth relative to other shapes is defined by the zorderPosition property. This is set using the setZOrder method, which takes a ShapeZOrder. setZOrder adjusts the ordering of the current shape relative to the other shapes.
Your add-in has a couple options for changing the height and width of shapes. Setting either the height or width property changes the specified dimension without changing the other dimension. The scaleHeight and scaleWidth adjust the shape's respective dimensions relative to either the current or original size (based on the value of the provided ShapeScaleType). An optional ShapeScaleFrom parameter specifies from where the shape scales (top-left corner, middle, or bottom-right corner). If the lockAspectRatio property is true, the scale methods maintain the shape's current aspect ratio by also adjusting the other dimension.
Note
Direct changes to the height and width properties only affect that property, regardless of the lockAspectRatio property's value.
The following code sample shows a shape being scaled to 1.25 times its original size and rotated 30 degrees.
// In this sample, the shape "Octagon" is rotated 30 degrees clockwise
// and scaled 25% larger, with the upper-left corner remaining in place.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("MyWorksheet");
let shape = sheet.shapes.getItem("Octagon");
shape.incrementRotation(30);
shape.lockAspectRatio = true;
shape.scaleWidth(
1.25,
Excel.ShapeScaleType.currentSize,
Excel.ShapeScaleFrom.scaleFromTopLeft);
await context.sync();
});
Text in shapes
Geometric Shapes can contain text. Shapes have a textFrame property of type TextFrame. The TextFrame object manages the text display options (such as margins and text overflow). TextFrame.textRange is a TextRange object with the text content and font settings.
The following code sample creates a geometric shape named "Wave" with the text "Shape Text". It also adjusts the shape and text colors, as well as sets the text's horizontal alignment to the center.
// This sample creates a light-blue wave shape and adds the purple text "Shape text" to the center.
await Excel.run(async (context) => {
let shapes = context.workbook.worksheets.getItem("MyWorksheet").shapes;
let wave = shapes.addGeometricShape(Excel.GeometricShapeType.wave);
wave.left = 100;
wave.top = 400;
wave.height = 50;
wave.width = 150;
wave.name = "Wave";
wave.fill.setSolidColor("lightblue");
wave.textFrame.textRange.text = "Shape text";
wave.textFrame.textRange.font.color = "purple";
wave.textFrame.horizontalAlignment = Excel.ShapeTextHorizontalAlignment.center;
await context.sync();
});
The addTextBox method of ShapeCollection creates a GeometricShape of type Rectangle with a white background and black text. This is the same as what is created by Excel's Text Box button on the Insert tab. addTextBox takes a string argument to set the text of the TextRange.
The following code sample shows the creation of a text box with the text "Hello!".
// This sample creates a text box with the text "Hello!" and sizes it appropriately.
await Excel.run(async (context) => {
let shapes = context.workbook.worksheets.getItem("MyWorksheet").shapes;
let textbox = shapes.addTextBox("Hello!");
textbox.left = 100;
textbox.top = 100;
textbox.height = 20;
textbox.width = 45;
textbox.name = "Textbox";
await context.sync();
});
Shape groups
Shapes can be grouped together. This allows a user to treat them as a single entity for positioning, sizing, and other related tasks. A ShapeGroup is a type of Shape, so your add-in treats the group as a single shape.
The following code sample shows three shapes being grouped together. The subsequent code sample shows that shape group being moved to the right 50 pixels.
// This sample takes three previously-created shapes ("Square", "Pentagon", and "Octagon")
// and groups them into a single ShapeGroup.
await Excel.run(async (context) => {
let shapes = context.workbook.worksheets.getItem("MyWorksheet").shapes;
let square = shapes.getItem("Square");
let pentagon = shapes.getItem("Pentagon");
let octagon = shapes.getItem("Octagon");
let shapeGroup = shapes.addGroup([square, pentagon, octagon]);
shapeGroup.name = "Group";
console.log("Shapes grouped");
await context.sync();
});
// This sample moves the previously created shape group to the right by 50 pixels.
await Excel.run(async (context) => {
let shapes = context.workbook.worksheets.getItem("MyWorksheet").shapes;
let shapeGroup = shapes.getItem("Group");
shapeGroup.incrementLeft(50);
await context.sync();
});
Important
Individual shapes within the group are referenced through the ShapeGroup.shapes property, which is of type GroupShapeCollection. They are no longer accessible through the worksheet's shape collection after being grouped. As an example, if your worksheet had three shapes and they were all grouped together, the worksheet's shapes.getCount method would return a count of 1.
Export shapes as images
Any Shape object can be converted to an image. Shape.getAsImage returns base64-encoded string. The image's format is specified as a PictureFormat enum passed to getAsImage.
await Excel.run(async (context) => {
let shapes = context.workbook.worksheets.getItem("MyWorksheet").shapes;
let shape = shapes.getItem("Image");
let stringResult = shape.getAsImage(Excel.PictureFormat.png);
await context.sync();
console.log(stringResult.value);
// Instead of logging, your add-in may use the base64-encoded string to save the image as a file or insert it in HTML.
});
Delete shapes
Shapes are removed from the worksheet with the Shape object's delete method. No other metadata is needed.
The following code sample deletes all the shapes from MyWorksheet.
// This deletes all the shapes from "MyWorksheet".
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("MyWorksheet");
let shapes = sheet.shapes;
// We'll load all the shapes in the collection without loading their properties.
shapes.load("items/$none");
await context.sync();
shapes.items.forEach(function (shape) {
shape.delete();
});
await context.sync();
});
See also
Office Add-ins